select query to get data based on day start 7 AM and next day end 7 AM

Question

Post reply

select query to get data based on day start 7 AM and next day end 7 AM

jaype-365633

SSC-Addicted

Points: 499
More actions
February 22, 2010 at 1:51 am

#217095

Hi, i am having table in it two columns are intime and outtime with datatype datetime, null which stores the date and time based on IST, i need to get the records for entire month from 7 AM to 7 AM on daily basis
the query which i am using is
SELECT datepart(D, OUTTIME) AS 'Date', SUM(UPLOADJOBS)
AS 'Total Jobs' FROM LIVE_JOBS where CHECKINTIME between '2010-02-01 07:00:00' and '2010-03-01 06:59:59'
GROUP BY fileid, DATEPART(D, OUTTIME), SID
ORDER BY 'Date' DESC
which gives over all report, need it on breaking 7 AM to next day 7 AM which should count as 1 day
Please help me on this

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Subtracting 7 hrs should work

Heres one option, untested

Select fileid,dateadd(hh,-7,OutTime) as Date,

sum(UploadJobs) as LiveJobs

FROM LIVE_JOBS

where CHECKINTIME between '2010-02-01 07:00:00' and '2010-03-01 06:59:59'

GROUP BY fileid,dateadd(hh,-7,OutTime)

order by 1,2 desc

Clear Sky SQL
My Blog[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I'm going to guess that Dave's solution will work, but, in my opinion, the best way to solve problems like this long-term is to create a calendar table that defines the work day. In your example you could have a calendar table that has columns:

Date ProductionDateStart ProductionDateEnd

1/1/2010 1/1/2010 07:00:00 1/2/2010 06:59:59

Then you can join on that table and your query looks something like this:

SELECT

C.Date,

SUM(LJ.UPLOADJOBS) AS 'Total Jobs'

FROM

dbo.LIVE_JOBS AS LJ JOIN

dbo.calendar AS C ON

LJ.OutTime BETWEEN C.ProductionDateStart AND C.ProductionDateEnd

where

C.Date BETWEEN '2010-02-01' AND '2010-02-28'

GROUP BY

fileid,

C.Date,

SID

ORDER BY

C.Date DESC

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

jaype-365633 SSC-Addicted Points: 499 More actions · Answer 3

I had created the new table and executed the query but i am not gettting the records able to see only column names.

the query which i had executed is

select C.DATE, SUM(JP.LINECOUNT) as 'Total Lines' from JOB_DETAILS as JP

join dbo.Calendar as C on JP.READYDATETIME between

C.PDE and C.PDS group by TRUSTID,C.DATE, JP.LINECOUNT

attached the screenshot of calendar table

Please help