Daily and weekly totals in SQL query

Hi Experts.

I am trying to write a SQL query that generates daily OT, _and_ weekly OT. Daily and weekly is fine if I have them in two separate queries, but if possible I'd like the result out in one query.

Daily OT is given when worked more than 9 hours, weekly OT is given when worked more than 40 hours in one iso_week.

This query gives me daily, and I am a bit stuck as of how to get the weekly OT in there. Preferably only on the last day of the iso_week (Which could be any day, depending on which days an employee works)

select (datename(dw,vpt.applydate)) as dayofweek, 
DATEPART(ISO_WEEK,vpt.applydate) as weekno,vpt.personnum, 
convert(varchar(10),vpt.applydate,104) as applydate, SUM(vpt.timeinseconds/3600.00)as workedhours,
--Summarize daily hours  greater that 9 for daily OT ---
case when sum(vpt.timeinseconds/3600.00) > 9.00 
then sum(vpt.timeinseconds/3600.00)- 9.00 
else 0.00  
end
as Daily_OT

from VP_TOTALS vpt
where vpt.PERSONNUM=70001
and vpt.PAYCODENAME='@workedhours'
and vpt.APPLYDATE between DATEDIFF(y,getdate(),-1) and GETDATE()
group by vpt.personnum, vpt.personfullname, vpt.applydate--, vpt.timeinseconds
order by vpt.applydate asc

Open in new window


generates something like this

dayofweek	weekno	personnum	applydate	workedhours	Daily_OT
Monday		37	70001		08.09.2014	11.5000000	2.5000000
Tuesday		37	70001		09.09.2014	11.7500000	2.7500000
Wednesday	37	70001		10.09.2014	11.7500000	2.7500000
Thursday	37	70001		11.09.2014	9.5000000	0.5000000
Friday		37	70001		12.09.2014	4.0000000	0.0000000
Saturday	37	70001		13.09.2014	3.0000000	0.0000000
Monday		38	70001		15.09.2014	6.7500000	0.0000000
Tuesday		38	70001		16.09.2014	5.2500000	0.0000000
Wednesday	38	70001		17.09.2014	6.5000000	0.0000000
Thursday	38	70001		18.09.2014	10.5000000	1.5000000
Friday		38	70001		19.09.2014	8.5000000	0.0000000
Saturday	38	70001		20.09.2014	1.0000000	0.0000000
Sunday		38	70001		21.09.2014	1.0166666	0.0000000

Open in new window


Anyone have a suggestion on how to get weekly sum into this query?

Thanks!
intimenordicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Please provide these:

1 the query for a week
2 sample data from table VP_TOTALS (so we can insert some records)
3 The expected result (what should the output look like)
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
sum(Daily_OT) Over(PARTITION BY weekno, personnum Order by applydate) as Weekly_OT

Open in new window


Will get you a cumulative weekly total to date
0
intimenordicAuthor Commented:
PortletPaul, this is my query for weekly workedhours. Getting the OT is simple once I have combined these queries.

select 
CONVERT(varchar(4), DATEPART(YEAR,vpt.applydate) ) + '-' + convert(varchar(2),DATEPART(ISO_WEEK,vpt.applydate))
as weekno,
vpt.personnum, 
SUM(vpt.timeinseconds/3600.00)as workedhours

from VP_TOTALS vpt
where vpt.PERSONNUM=70001
and vpt.PAYCODENAME='@workedhours'
and vpt.APPLYDATE between '2014-09-08' and '2014-09-21'
group by   CONVERT(varchar(4), DATEPART(YEAR,vpt.applydate) ) + '-' + convert(varchar(2),DATEPART(ISO_WEEK,vpt.applydate)), vpt.PERSONNUM

Open in new window



and here's sample data from vp_totals

applydate			PERSONNUM	timeinseconds	paycodename
2014-09-08 00:00:00.000		70001		14400		@workedhours
2014-09-08 00:00:00.000		70001		27000		@workedhours
2014-09-09 00:00:00.000		70001		14400		@workedhours
2014-09-09 00:00:00.000		70001		27900		@workedhours
2014-09-10 00:00:00.000		70001		14400		@workedhours
2014-09-10 00:00:00.000		70001		27900		@workedhours
2014-09-11 00:00:00.000		70001		14400		@workedhours
2014-09-11 00:00:00.000		70001		19800		@workedhours
2014-09-12 00:00:00.000		70001		14400		@workedhours
2014-09-13 00:00:00.000		70001		10800		@workedhours
2014-09-15 00:00:00.000		70001		14400		@workedhours
2014-09-15 00:00:00.000		70001		9900		@workedhours
2014-09-16 00:00:00.000		70001		18900		@workedhours
2014-09-17 00:00:00.000		70001		14400		@workedhours
2014-09-17 00:00:00.000		70001		9000		@workedhours
2014-09-18 00:00:00.000		70001		14400		@workedhours
2014-09-18 00:00:00.000		70001		23400		@workedhours
2014-09-19 00:00:00.000		70001		14400		@workedhours
2014-09-19 00:00:00.000		70001		16200		@workedhours
2014-09-20 00:00:00.000		70001		3600		@workedhours
2014-09-21 00:00:00.000		70001		3660		@workedhours

Open in new window


Wanted output ( I'm not picky if anyone have a better idea)

dayofweek	weekno	personnum	applydate	workedhours	Daily_OT       Weekly hours
Monday		37	70001		08.09.2014	11.5000000	2.5000000
Tuesday		37	70001		09.09.2014	11.7500000	2.7500000
Wednesday	37	70001		10.09.2014	11.7500000	2.7500000
Thursday	37	70001		11.09.2014	9.5000000	0.5000000
Friday		37	70001		12.09.2014	4.0000000	0.0000000
Saturday	37	70001		13.09.2014	3.0000000	0.0000000      51.5000000
Monday		38	70001		15.09.2014	6.7500000	0.0000000
Tuesday		38	70001		16.09.2014	5.2500000	0.0000000
Wednesday	38	70001		17.09.2014	6.5000000	0.0000000
Thursday	38	70001		18.09.2014	10.5000000	1.5000000
Friday		38	70001		19.09.2014	8.5000000	0.0000000
Saturday	38	70001		20.09.2014	1.0000000	0.0000000
Sunday		38	70001		21.09.2014	1.0166666	0.0000000      39.5166666

Open in new window


Philip Burton: I did try the Partition approach yesterday, but I had issues with the order by clause.
'Daily_OT' and 'weekno' are calculated fields from my query, and not fields from vp_totals.

Thanks,
Bjorn
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
I'm not keen at all on "report formatting" in SQL, personally I would just repeat the weekly total for each row, but the following outputs the weekly total each Saturday, but if they don't work on a Saturday you are in trouble.
SELECT
      dayofweek
    , weekno
    , personnum
    , CONVERT(varchar(10), applydate, 104) AS applydate
    , workedhours
    , Daily_OT
    , CASE WHEN dayofweek = 'Saturday' THEN SUM(workedhours) OVER (PARTITION BY personnum, weekno) END AS [Weekly hours]
FROM (
      SELECT
            (DATENAME(dw, vpt.applydate))            AS dayofweek
          , DATEPART(ISO_WEEK, vpt.applydate)        AS weekno
          , vpt.personnum
          , vpt.applydate
          , SUM(vpt.timeinseconds / 3600.00)         AS workedhours

            --Summarize daily hours  greater that 9 for daily OT ---
          , CASE WHEN SUM(vpt.timeinseconds / 3600.00) > 9.00 THEN SUM(vpt.timeinseconds / 3600.00) - 9.00 ELSE 0.00 END AS Daily_OT

      FROM VP_TOTALS vpt
      WHERE vpt.PERSONNUM = 70001
            AND vpt.PAYCODENAME = '@workedhours'
            AND vpt.APPLYDATE BETWEEN DATEDIFF(y, GETDATE(), -1) AND GETDATE()
      GROUP BY
            vpt.personnum
          , vpt.personfullname
          , vpt.applydate
        --, vpt.timeinseconds
      ) derived
ORDER BY
      applydate ASC
;

Open in new window

To output the weekly total for each row just remove the case expression portion of row 8

see: http://sqlfiddle.com/#!3/607b1/6
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> Philip Burton: I did try the Partition approach yesterday, but I had issues with the order by clause.
'Daily_OT' and 'weekno' are calculated fields from my query, and not fields from vp_totals.

Then you need to use a CTE,

I.e.

with myTable as (         Insert your code here without the Order By statement.  )

Select *,     use the partition approach here      
From myTable
Order by applydate 

Open in new window

0
PortletPaulfreelancerCommented:
>>"'Daily_OT' and 'weekno' are calculated fields from my query, and not fields from vp_totals."

A derived table solves this (as already demonstrated)
0
intimenordicAuthor Commented:
Thanks! This will do the trick .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.