· 7 years ago · Feb 06, 2019, 09:08 PM
1+---------------------+---------------+
2| Creation Timestamp | Numeric Value |
3+---------------------+---------------+
4| 2017-02-01 09:15:00 | 100.00 |
5| 2017-02-01 11:10:00 | 150.00 |
6| 2017-02-03 10:25:00 | 80.00 |
7+---------------------+---------------+
8
9SELECT
10 TRUNC_TO_DAY([Creation Timestamp]),
11 COUNT(*),
12 SUM([Numeric Value])
13FROM
14 [MyTable]
15GROUP BY
16 TRUNC_TO_DAY([Creation Timestamp])
17
18+------------+---+--------+
19| 2017-02-01 | 2 | 250.00 |
20| 2017-02-03 | 1 | 80.00 |
21+------------+---+--------+
22
23+------------+---+--------+
24| 2017-02-01 | 2 | 250.00 |
25| 2017-02-02 | 0 | 0.00 |
26| 2017-02-03 | 1 | 80.00 |
27+------------+---+--------+
28
29DATEADD(DAY, DATEDIFF(DAY, 0, ?), 0)
30
31-- some topics:
32-- how to check if a temp table exists?
33-- create a random datetime
34-- create a random money
35-- checksum
36-- newid
37-- create a computed column
38-- tally table
39-- sp_executesql with parameters
40-- cte
41-- getting rid of duplicates
42
43
44use tempdb
45go
46
47
48IF OBJECT_ID('tempdb..#foo') IS NOT NULL
49 drop table #foo
50
51IF OBJECT_ID('tempdb..#bar') IS NOT NULL
52 drop table #bar
53
54-- creates the temp tables
55
56create table #foo (
57 creation datetime not null,
58 value money)
59go
60
61create table #bar (dt date primary key clustered)
62go
63
64
65
66-- insert 1000 rows into the table
67-- there might be cases when we get a duplicate value but rarely
68
69SET NOCOUNT ON
70INSERT INTO #FOO(CREATION,VALUE)
71SELECT DATEADD(MILLISECOND, ABS(CHECKSUM(NEWID()) * 45000.98 % 3650), DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2001-01-01')) AS RANDOM_DATE,
72 ABS(CHECKSUM(NEWID()) * 45000.98 % 3650) AS RANDOM_MONEY
73GO 1000
74
75
76--- add a date column to the #foo table so that we can use it for joints later on
77ALTER TABLE #FOO
78ADD DT AS CONVERT(DATE,CREATION)
79PERSISTED
80GO
81
82-- add a clustered index on the dt column
83CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT)
84GO
85
86--get the min date and the max date to build a tally table
87
88declare @min datetime
89declare @max datetime
90DECLARE @TOTAL INT
91
92DECLARE @sql nvarchar(4000),
93 @paramlist nvarchar(4000)
94
95select @total=datediff(day,[min],[max]),
96 @min = [min],
97 @max = [max]
98from (
99 select min (creation) as [min],
100 max(creation) as [max]
101 from #foo ) x
102
103
104--convert @min to date
105--using a tally table adds all dates to the end
106--save them into table #bar
107
108select [min]=@min,
109 [max]=@max,
110 [total]=@TOTAL
111
112SELECT @SQL = '
113;WITH cteTally
114 AS (SELECT TOP 500000
115 ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
116 FROM
117 sys.all_columns AS AC CROSS JOIN
118 sys.all_columns AS AC2 )
119
120SELECT TOP ( ' + CAST (@TOTAL AS VARCHAR) + ' ) DATEADD(DAY,N-1,CONVERT(DATE,@MIN)) AS DT FROM cteTally '
121
122SELECT @paramlist = '@min datetime'
123
124
125INSERT INTO #bar
126exec sp_executesql @statement=@sql,@paramlist=@paramlist,@min=@min
127
128
129-- by now we have the tables and the data to do our query
130
131-- just having a look at the table here
132SELECT *
133FROM #BAR B
134LEFT OUTER JOIN #FOO F
135ON B.dt = F.DT
136
137-- prepares all the calculations that we need
138WITH RADHE AS (
139SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
140 THE_DATE=B.dt,
141 THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END ,
142 THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0)
143
144FROM #BAR B
145LEFT OUTER JOIN #FOO F
146ON B.dt = F.dt
147)
148
149--get rid of the duplicates and present the result
150SELECT
151THE_DATE,
152THE_NUMBER_OF_RECORDS_ON_THIS_DAY,
153THE_TOTAL_VALUE_FOR_THE_DAY
154FROM RADHE
155WHERE THE_ROW = 1
156--OPTION (RECOMPILE,MERGE JOIN)
157
158
159--- drops the temp tables
160IF OBJECT_ID('tempdb..#foo') IS NOT NULL
161 drop table #foo
162
163IF OBJECT_ID('tempdb..#bar') IS NOT NULL
164 drop table #bar