2

I have several tables with data like this:

+---------------------+---------------+
| Creation Timestamp  | Numeric Value |
+---------------------+---------------+
| 2017-02-01 09:15:00 |        100.00 |
| 2017-02-01 11:10:00 |        150.00 |
| 2017-02-03 10:25:00 |         80.00 |
+---------------------+---------------+

and I need to query data for a chart, so I came up with this:

SELECT
    TRUNC_TO_DAY([Creation Timestamp]),
    COUNT(*),
    SUM([Numeric Value])
FROM
    [MyTable]
GROUP BY
    TRUNC_TO_DAY([Creation Timestamp])

which returns this results:

+------------+---+--------+
| 2017-02-01 | 2 | 250.00 |
| 2017-02-03 | 1 |  80.00 |
+------------+---+--------+

which are correct, by I need to fill the gaps too, like this:

+------------+---+--------+
| 2017-02-01 | 2 | 250.00 |
| 2017-02-02 | 0 |   0.00 |
| 2017-02-03 | 1 |  80.00 |
+------------+---+--------+

Some restrictions:

  • I can't create additional (real) tables nor views;
  • I've already implemented truncation functions, like this:

In SQL Server, function TRUNC_TO_DAY:

DATEADD(DAY, DATEDIFF(DAY, 0, ?), 0)
  • the query must work with different truncation unit (year, month, day, hour, minute);
  • the value to fill the gap too must be configurable (i.e. 0 or NULL).

I'm looking for a SQL Server solution primarily, but then I need to port it also to MySQL so any suggestion on that too would be very appreciated.

1
  • Trunc_to_day: Simply DATE(...). MySQL won't like the brackets. See if both servers can get into an ansi mode.
    – Rick James
    Feb 18, 2017 at 2:28

1 Answer 1

1

I have got something that I believe solves the problem, however I have not worked with dynamic truncation:

the query must work with different truncation unit (year, month, day, hour, minute);

Basically I generate a table with random date and random values, and from that table #foo, I create the report with the help of table #bar.

I have only done partition by date, not year, month or hour or minute, but I guess it could follow the same logic.

everything else should be fine.

-- some topics:
-- how to check if a temp table exists?
-- create a random datetime
-- create a random money
-- checksum
-- newid
-- create a computed column
-- tally table
-- sp_executesql with parameters
-- cte
-- getting rid of duplicates


use tempdb
go


IF OBJECT_ID('tempdb..#foo') IS NOT NULL
    drop table #foo

IF OBJECT_ID('tempdb..#bar') IS NOT NULL
    drop table #bar

-- creates the temp tables

create table #foo (
        creation datetime not null,
        value money)
go

create table #bar (dt date primary key clustered)
go



-- insert 1000 rows into the table
-- there might be cases when we get a duplicate value but rarely

SET NOCOUNT ON
INSERT INTO #FOO(CREATION,VALUE)
SELECT DATEADD(MILLISECOND, ABS(CHECKSUM(NEWID()) * 45000.98 % 3650), DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2001-01-01')) AS RANDOM_DATE,
       ABS(CHECKSUM(NEWID()) * 45000.98 % 3650) AS RANDOM_MONEY
GO 1000


--- add a date column to the #foo table so that we can use it for joints later on
ALTER TABLE #FOO
ADD DT AS CONVERT(DATE,CREATION)
PERSISTED
GO

-- add a clustered index on the dt column
CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT)
GO

--get the min date and the max date to build a tally table

declare @min datetime
declare @max datetime
DECLARE @TOTAL INT 

DECLARE @sql        nvarchar(4000),                                
        @paramlist  nvarchar(4000)   

select @total=datediff(day,[min],[max]),
       @min = [min],
       @max = [max]
from (
        select min (creation) as [min],
               max(creation) as [max]
        from #foo ) x


--convert @min to date
--using a tally table adds all dates to the end
--save them into table #bar

select [min]=@min, 
       [max]=@max,
       [total]=@TOTAL

SELECT @SQL = '
;WITH    cteTally
          AS (SELECT TOP 500000
                ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
              FROM
                sys.all_columns AS AC CROSS JOIN
                sys.all_columns AS AC2  )

SELECT TOP ( ' + CAST (@TOTAL AS VARCHAR) + ' ) DATEADD(DAY,N-1,CONVERT(DATE,@MIN)) AS DT FROM cteTally '

SELECT @paramlist = '@min  datetime' 


INSERT INTO #bar
exec sp_executesql @statement=@sql,@paramlist=@paramlist,@min=@min


-- by now we have the tables and the data to do our query

-- just having a look at the table here
SELECT *
FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.DT

-- prepares all the calculations that we need
WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt,
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0)

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)

--get rid of the duplicates and present the result
SELECT 
THE_DATE,
THE_NUMBER_OF_RECORDS_ON_THIS_DAY,
THE_TOTAL_VALUE_FOR_THE_DAY
FROM RADHE
WHERE THE_ROW = 1
--OPTION (RECOMPILE,MERGE JOIN)


--- drops the temp tables
IF OBJECT_ID('tempdb..#foo') IS NOT NULL
    drop table #foo

IF OBJECT_ID('tempdb..#bar') IS NOT NULL
    drop table #bar

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.