×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

help with grouping by dates
2

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.

RE: help with grouping by dates

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

RE: help with grouping by dates

2
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

RE: help with grouping by dates

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?

RE: help with grouping by dates

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.

RE: help with grouping by dates

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.

RE: help with grouping by dates

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

RE: help with grouping by dates

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?

RE: help with grouping by dates

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

RE: help with grouping by dates

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources