Solved

Daily and weekly totals in SQL query

Posted on 2014-09-27
7
1,189 Views
Last Modified: 2014-09-28
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!
0
Comment
Question by:intimenordic
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:intimenordic
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
Comment Utility
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 150 total points
Comment Utility
>> 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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"'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
 

Author Closing Comment

by:intimenordic
Comment Utility
Thanks! This will do the trick .
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now