help with grouping by dates 2

[johnchilds]

How can i group information of tables by date dd/JAN/yy.
I want to group financial info for every month but i can't seem to figure it out unless i go the long haul and group together 12 views which i don't want to do,

please help me, thank you.

 

[BML]

johnchilds,

Can you provide a brief sample of the data contained in the table? I'm not sure I understand the structure. Also, do you want to group by date for only January, or do you want to group together all January info regardless of the specific day or year?

Thanks!

BML

 

[rush]

If you want to group the results by each month starting at Jan, try: Order by DATEPART("Mm", {Datefield} )

====
The SQL Server DATEPART() function returns a portion of a SQL Server datetime field.

The syntax of the SQL Server DATEPART() function is:

DATEPART(portion, datetime)

where datetime is name of a SQL Server datetime field and portion is one of the following:

Ms for Milliseconds
Yy for Year
Qq for Quarter of the Year
Mm for Month
Dy for the Day of the Year
Dd for Day of the Month
Wk for Week
Dw for the Day of the Week
Hh for Hour
Mi for Minute
Ss for Second

 

[perrygordon]

I have a related question. Is there an easy way to group by financial month, such as Dec 22 - Jan 21, next group would be Jan 22 - March 21?

 

[rush]

You can Group by the Datepart function on each month instead of using it to 'Order By' and Order by Date. That should work for you.

 

[perrygordon]

Thank you for the reply, but perhaps I was not clear. Maybe an example would help. Let's say I have a table of sales information. One record per day, and the number of sales for that day. If I group by DatePart month, I can see the total number of sales for the entire month. Like so ... SELECT DATEPART(Mm, saleDate), SUM(numOfSales) .. FROM salesTable .. GROUP BY DatePart(Mm, saleDate). The problem is that each group will show total sales from the 1st of the month, to the last day of that month. I would like to see totals (group by) from the 21st of the month, to the 22nd of the next month. I read that this can be done with analysis services by creating my own time dimention, or I could also create a seperate table of dates and which fiscal month they fall into, join to the table and group on that instead, but I was hoping I didn't have to go that route, as I try to keep my queries as slim as possible. I was just wondering if there was an easy way I didn't know about. Any ideas? Thanks for your help.

 

[rush]

Sorry, I read the earlier post and replied to it by mistake.

To group by date ranges might be possible but my first thought is that it would be messy and get quite large. Some systems have a limit on the size of the query string.

Adding a field that contains the Fiscal Month in the table would be the simplest way. You could automate the population of this field with a series of Update queries. The where clause would update the field = 'January' where date > 12/25 and < 1/24. Then the 'group by' would work.

If I think of anything else I will try to post it.

Bill

 

[perrygordon]

Thanks Bill. That is a good idea. I think in my situation it would be better to create the seperate table. My sales table has millions of records so that extra field would take up more space. If I go with the seperate table of dates and corresponding fiscal months, I would only need one record (2 coulums) for each 365 days of the year, I'm only working with a year's worth of data, I could set it up and not have to think about it anymore. It may also come in handy with other non-sales related queries, and would then save even more space (because I'm not adding the extra field to each table where I want to use the special date grouping). The only drawback is that I have to join to one more table which might slow my queries down ... SELECT DateTable.FiscalMonth, SUM(SalesTable.NumOfSales)
FROM SalesTable INNER JOIN DateTable ON SalesTable.SaleDate = DateTable.SaleDate GROUP BY DateTable.FiscalMonth ... What do you think?

 

[rush]

Any additional function slows things down. The additional time usually isn't noticable, however. In your case is isn't that large of a table. So it should not be significant. The only way to know for sure is to try it.

Bill

 

[BML]

You could try a UNION query, but then you'd have to manually program each of the fiscal months, like so:

SELECT 'JAN' AS MONTH, FIELD FROM TABLE WHERE DATE BETWEEN DATE1 AND DATE2 GROUP BY MONTH
UNION
SELECT 'FEB' AS MONTH, FIELD FROM TABLE WHERE DATE BETWEEN DATE1 AND DATE2 GROUP BY MONTH

etc.

Hope this helps!