Get last 7 hours of data with 0's

Question

Get last 7 hours of data with 0's

FastRider30

SSC Veteran

Points: 293
More actions
February 11, 2011 at 1:40 pm

#231283

Hi all,
I need to build a graphic that shows the last 7 hours of production.
I can get the information I need doing:
SELECT CONVERT(VARCHAR(10),CYCLE_TIMESTAMP,101) AS [DATE], DATEPART(HH, CYCLE_TIMESTAMP) AS [HOUR], COUNT(CYCLE_TIMESTAMP) AS [PARTS]
FROM TBL_CYCLE_PARTS
WHERE MACHINE_ID = 2 AND JOB_ID = 200 AND STATUS = 'GOOD'
AND CYCLE_TIMESTAMP > DATEADD(HH,-8,GETDATE())
GROUP BY CONVERT(VARCHAR(10),CYCLE_TIMESTAMP,101), DATEPART(HH, CYCLE_TIMESTAMP)
ORDER BY CONVERT(VARCHAR(10),CYCLE_TIMESTAMP,101)
The problem here is I need to show 0 (zero) if no production is done at a specific hour.
Something like this:
Now is: 02/11/2011 15:30:00
My output would be:
Date Hour Parts
--------------------
02/11/2001 8 4
02/11/2001 9 6
02/11/2001 10 5
02/11/2001 11 0
02/11/2001 12 0
02/11/2001 13 2
02/11/2001 14 8
02/11/2001 15 7
Thank you all for your help.
F.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

instead of selecting from that table TBL_CYCLE_PARTS,

you instead have to select from some sort of calendar table, with all possible times, and join THAT to your TBL_CYCLE_PARTS;

this will work *i think*

llet me know if that is working or not:

SELECT

CONVERT(VARCHAR(10),CYCLE_TIMESTAMP,101) AS [DATE],

POSSIBLEHOURS.[HOUR],

COUNT(CYCLE_TIMESTAMP) AS [PARTS]

FROM (

SELECT

TOP 24 ROW_NUMBER() OVER (ORDER BY name) AS [HOUR]

FROM sys.columns

) POSSIBLEHOURS

LEFT OUTER JOIN

TBL_CYCLE_PARTS

ON POSSIBLEHOURS.[HOUR] = DATEPART(HH, CYCLE_TIMESTAMP)

WHERE MACHINE_ID = 2

AND JOB_ID = 200

AND STATUS = 'GOOD'

AND CYCLE_TIMESTAMP > DATEADD(HH,-8,GETDATE())

GROUP BY CONVERT(VARCHAR(10),CYCLE_TIMESTAMP,101), DATEPART(HH, CYCLE_TIMESTAMP)

ORDER BY CONVERT(VARCHAR(10),CYCLE_TIMESTAMP,101)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

In the absence of a preexisting table, you can generate a sequential list of hours very simply by means of a tally table.

;with hoursTbl as (

select dateadd(hour,N-1,cast('Feb 10 2011' as datetime)) as xHour

from dbo.Tally

where N <= 2401

)

select * from hoursTbl

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

FastRider30 SSC Veteran Points: 293 More actions · Answer 3

Thank you guys...

I was able to solve the issue using Lowell's approach!

Thanks again.