Solved

Daily and weekly totals in SQL query

Posted on 2014-09-27
7
1,435 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Accepted Solution

by:
PortletPaul earned 350 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 150 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 Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

729 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