Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Daily and weekly totals in SQL query

Posted on 2014-09-27
7
Medium Priority
?
1,735 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 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 Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

580 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