How to query chart data in a database agnostic way?

Question

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.

Answer 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