Calculate sum data based on days and weeks in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate sum data based on days and weeks in SQL

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-05-05 : 15:45:56

I have clients they stores data in days or weeks bases. But client has only option to store data either in days or week. see below example


ClientID     period      hours
1           2010-04-19  8.04
1           2010-04-20  6.24
1           2010-04-21  8.26
1           2010-04-22  7.94
1           2010-04-23  22.43
1           2010-04-24  22.99
2           2010-12-19  130.67
2           2010-12-26  159.26
2           2011-01-02  113.59
2           2011-01-09  12.66
2           2011-01-16  22.34
2           2011-01-23  11.35

Now I have to sum hours based client id period for last 52 weeks to 27 weeks and 27 weeks to present.

Example: ClientId =1 from last 52 weeks to 27 weeks total hours = 150 ClientId =1 from last 27 weeks to today total hours = 200

ClientId =2 from last 52 weeks to 27 weeks total hours = 350 ClientId =2 from last 27 weeks to today total hours = 250.

I need this in T-SQL. It is confusing me. Please some one write me a query.

There is another table for Clients time level like this.


Client   time_level
1       Day
2       Week

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 16:29:33

The main thing you need to resolve here is the definition of a week. If you simply say that, starting today and going back to 7*26 days is what you consider as the 26 weeks, then you can do it as follows:

select
   clientId,
   SUM(case when period >= DATEADD(dd,datediff(dd,0,getdate()),-7*26) then hours end) as WeeksNowTo27,
   sum(case when period < DATEADD(dd,datediff(dd,0,getdate()),-7*26) 
      and period >= DATEADD(dd,datediff(dd,0,getdate()),-7*52)  then hours end) as Weeks26To52
from
   YourTable
group by
   clientId

But, that may not exactly be what you need for two reasons:

1. Your week may not be defined as I explained above. You may be using ISO week for example.

2. For those clients that report hours weekly, how do you make the decision whether to include the 26th week. If the report date is 26 weeks + 1 day ago, do you want to include it in the current half-year report, or the previous half-year report??

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2011-05-05 : 17:26:49

1. Your week may not be defined as I explained above. You may be using ISO week for example.
Yes it is.

2. For those clients that report hours weekly, how do you make the decision whether to include the 26th week. If the report date is 26 weeks + 1 day ago, do you want to include it in the current half-year report, or the previous half-year report??

I want current half-year.

I will get client time_level from clients table


@time_level = select time_level from client where clientid=1
case 'Day'
         -To Do
case 'week'
         -To Do

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 18:44:55

Since the hours table has the period as a date, I don't think you need to use the time level table at all. Run the query that I posted earlier, compare the results for one or two client id's with what you calculate manually. If the results don't agree, it may be off by a week or a day depending on the time level - which we can fix.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources