group by custom date table

[hansdebadde]

I am using SQL 2008 and am new to it. I want to group a table tbl_hrs which has two fields:
dtm_tcdate and int_hrs
However i want to group by financial periods (13 per yr). I was thinking the easiest long term solution would be to create another table: tbl_fincanceperiods that held these dates (dtm_financeperiod) and then group the other table by these values. However some of the dtm_tcdates don't equal the dtm_financeperiod but are between them. For example if the dates for dtm_financeperiod were January 1, 2012 and January 15, 2012 but the dtm_tcdate was January 5, 2012 i would want it in the January 1,2012 group and have its int_hrs summed with all the other values for that group.
How would I go about writing this query so it can be grouped as such and the hrs summed? I hope this makes sense. Thanks in advance

 

[Qik3Coder]

Create a function that takes your date and returns something that sorts correctly suchas '201201' or '201213', then Order BY or GROUP BY that value.



You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

 

[hansdebadde]

How do i join the two tables and not lose some of the tbl_hrs values that don't exactly match the finance periods?

 

[gmmastros]

I assume each finance period is exactly 4 weeks (28 days) long, right?

If so, this query should work for you:

Select	tbl_financePeriods.dtm_FinancePeriod,
        Sum(tbl_hrs.int_hrs) As TotalHours
From    tbl_hrs
        Inner Join tbl_financePeriods
          On tbl_hrs.dtm_tcdate >= tbl_financePeriods.dtm_financePeriod
          And tbl_hrs.dtm_tcdate < DateAdd(Day, 28, tbl_financePeriods.dtm_financePeriod)
Group By tbl_financePeriods.dtm_FinancePeriod
Order By tbl_financePeriods.dtm_FinancePeriod

Notice that the join between the 2 tables has 2 conditions. Greater than or equal to start date and less than startdate + 28 days.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hansdebadde]

that is awesome, make sense. If i made another column tbl_financePeriods.dtm_financePeriodEnd
it should work if i do this?

Select	tbl_financePeriods.dtm_FinancePeriod,
        Sum(tbl_hrs.int_hrs) As TotalHours
From    tbl_hrs
        Inner Join tbl_financePeriods
          On tbl_hrs.dtm_tcdate >= tbl_financePeriods.dtm_financePeriod
          And tbl_hrs.dtm_tcdate < tbl_financePeriods.dtm_financePeriodEnd
Group By tbl_financePeriods.dtm_FinancePeriod
Order By tbl_financePeriods.dtm_FinancePeriod

 

[gmmastros]

Yes.

Having the end date in the table makes it easier to adjust later (if the need should arise).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom