SQL to display hourly count including zero


  • 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.

  • Hi,

    This does not look like MS SQL does it?

    Maybe the wrong forum?

    Regards,

    Hans van Dam

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

  • 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;

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

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