help with grouping by dates
help with grouping by dates
(OP)
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.
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.





RE: help with grouping by dates
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
RE: help with grouping by dates
====
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
RE: help with grouping by dates
RE: help with grouping by dates
RE: help with grouping by dates
RE: help with grouping by dates
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
RE: help with grouping by dates
FROM SalesTable INNER JOIN DateTable ON SalesTable.SaleDate = DateTable.SaleDate GROUP BY DateTable.FiscalMonth ... What do you think?
RE: help with grouping by dates
Bill
RE: help with grouping by dates
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!