Find production per hour - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find production per hour

Author

Topic

ctbfalcon
Starting Member

1 Post

Posted - 2014-06-19 : 11:14:55

I have a table which each record is a part being made. I want to break up the total rows by each hour given a start time and continue until 8 hours later. Each record has a date field. I was hoping to do this in one sql statement.

I was hoping to have something like this:
Hour1 Hour2 Hour3 Hour4 Hour5 etc
266 356 345 123 865 etc

I had something like this for getting one of the hours

select count(*) as Hour1 from (Select * FROM dbo.prod where fdEnterDate BETWEEN DATEADD(hh, 0, '2014-06-18 08:00:00')
AND DATEADD(hh, 1, '2014-06-18 08:00:00')) AS Derivedtb

So i started with that and came to this
thinking i could get the differece from the hours 2-8 and the total to get hour 1.

SELECT
COUNT (*) AS Hour2_8,
(
SELECT
COUNT (fdEnterDate) AS total
FROM
dbo.tb080T2ATurnTable AS tt
WHERE
fdEnterDate BETWEEN DATEADD(hh, 0, '2014-06-18 08:00:00')
AND DATEADD(hh, 8, '2014-06-18 08:00:00')
) AS Total
FROM
(
SELECT
fdEnterDate AS hour1Start
FROM
dbo.tb080T2ATurnTable AS tt
WHERE
fdEnterDate >= '2014-06-18 08:00:00'
) AS Derivedtb
WHERE
Derivedtb.hour1Start BETWEEN DATEADD(hh, 1, '2014-06-18 08:00:00')
AND DATEADD(hh, 8, '2014-06-18 08:00:00')

I'm not sure im on the write track.
Ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-19 : 13:34:59

[code]SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101', fdEnterDate), '19000101') AS theHour,
COUNT(*) AS theCount
FROM dbo.Prod
WHERE fdEnterDate >= '20140601'
AND fdEnterDate < '20140701'
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, '19000101', fdEnterDate), '19000101')
ORDER BY DATEADD(HOUR, DATEDIFF(HOUR, '19000101', fdEnterDate), '19000101');[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources