SQL to display hourly count including zero

• mikek 50666

  Right there with Babe

  Points: 779

  More actions

  March 21, 2017 at 2:03 am

  #151085

  I have been struggling with this for 2 days now and need some help I am trying to display hourly production rate for a machine including hours where 0 parts are made. I have been looking and the answer seems to be to do with creating a second table containing the hours and doing a left outer join on this, but I seem to be doing something wrong and cannot get it to work. The below is using a second table with idhours column which contains 24 rows with numbers 0-23 however it seems to be multiplying the count and still not returning the zero rows SELECT DATE_FORMAT(MACHINE_TIMESTART, '%H') as `Hour_of_MACHINE_TIMESTART`, COUNT(*) as `Count` FROM `production_data` left outer join hours on idhours where date(MACHINE_TIMESTART)=date('2017-03-14') GROUP BY `Hour_of_MACHINE_TIMESTART` ORDER BY `Hour_of_MACHINE_TIMESTART` ASC        If I leave out the inner join then the results are correct but not showing the zero hours.

• Hans van Dam

  Mr or Mrs. 500

  Points: 536

  More actions

  March 21, 2017 at 2:54 am

  #1934121

  Hi,

  This does not look like MS SQL does it?

  Maybe the wrong forum?

  Regards,

  Hans van Dam

• DesNorton

  SSC-Insane

  Points: 24511

  More actions

  March 21, 2017 at 2:59 am

  #1934123

  Please provide us with CREATE TABLE scripts for the 2 tables, as well as INSERT statements with sample data, and the expected results.

  ---

  How to post data/code on a forum to get the best help.

• DesNorton

  SSC-Insane

  Points: 24511

  More actions

  March 21, 2017 at 3:15 am

  #1934125

  The format of your query appears to ba MySQL format.

  In T-SQL, we can do it like this ...

  Create some sample data
  
CREATE TABLE #production_data (
  MACHINE_TIMESTART DATETIME NOT NULL
, SomeIntData   INT  NOT NULL
);

  SET NOCOUNT ON;

  DECLARE @SAMPLE_SIZE BIGINT = 1000;

  DECLARE
  @MinInt   INT = 10
  , @MaxInt   INT = 500
  , @StartDate  DATE = '2017-03-12'
  , @EndDate  DATE = '2017-03-15';

  WITH  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
 , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5)
INSERT INTO #production_data ( MACHINE_TIMESTART, SomeIntData )
SELECT
  MACHINE_TIMESTART = RAND(CHECKSUM(NEWID()))*DATEDIFF(DD,@StartDate,@EndDate)+CONVERT(DATETIME,@StartDate)
, SomeIntData   = ABS(CHECKSUM(NEWID())%(@MaxInt-@MinInt))+@MinInt
FROM NUMS AS NM
OPTION (RECOMPILE);
GO


  Get the required data
  
DECLARE @ReportDate DATETIME = '2017-03-14';

  WITH  T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
 , NUMS(N) AS (SELECT TOP(24) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS N FROM T T1,T T2)
SELECT NM.N AS [Hour_of_MACHINE_TIMESTART]
  , COUNT(*) AS [Count]
FROM NUMS AS NM
LEFT JOIN #production_data AS d
 ON NM.N = DATEPART(HH, d.MACHINE_TIMESTART)
AND d.MACHINE_TIMESTART >= @ReportDate
AND d.MACHINE_TIMESTART < DATEADD(DD, DATEDIFF(DD, 0, @ReportDate)+1, 0)
GROUP BY NM.N
ORDER BY NM.N;


  ---

  How to post data/code on a forum to get the best help.