?
Solved

Daily and weekly totals in SQL query

Posted on 2014-09-27
7
Medium Priority
?
1,518 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40347970
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
ID: 40348284
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
ID: 40348341
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 1400 total points
ID: 40348562
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 600 total points
ID: 40348823
>> 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 49

Expert Comment

by:PortletPaul
ID: 40349024
>>"'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
ID: 40349309
Thanks! This will do the trick .
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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