March 21, 2017 at 2:03 am
March 21, 2017 at 2:54 am
Hi,
This does not look like MS SQL does it?
Maybe the wrong forum?
Regards,
Hans van Dam
March 21, 2017 at 2:59 am
Please provide us with CREATE TABLE scripts for the 2 tables, as well as INSERT statements with sample data, and the expected results.
March 21, 2017 at 3:15 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy