CODEWITHSUNDEEP
sqlsql-server
Jackal
Jackal

Reputation: 3521

SQL Get Data from everyday from a date range

I have to build a chart where it shows data per day based on multiple filters ( week, month, custom date range ).

The problem is that on my database i only have the date from when the records are created, which means 

where Date >= @start AND Date <= @start

will not return rows for days without records.

I would like it to return a row with result of 0 for those days but I have no idea how to do this in SQL

Data example

2019-09-13 12:00:43.7761868
2019-09-13 12:01:20.8275512
2019-09-13 21:28:59.3311800
2019-09-13 21:32:12.8601389
2019-09-14 04:37:25.6108193
2019-09-16 13:34:39.7449688
2019-09-16 14:06:18.0098897
2019-09-16 14:59:22.4131463
2019-09-16 15:00:38.8086073
2019-09-17 05:45:06.9280049
2019-09-17 05:45:50.1708972
2019-09-17 13:50:23.9503997
2019-09-17 13:55:13.1940063
2019-09-17 18:40:26.3523765
2019-09-17 18:41:08.2932910
2019-09-18 03:48:59.2092904
2019-09-18 03:49:41.0802705
2019-09-18 06:10:47.0732559
2019-09-18 06:11:30.1833908

note there is no records on the date

2019-09-15

the intended result would be

2019-09-13 
2019-09-14 
2019-09-15
2019-09-16 
2019-09-17 
2019-09-18

Upvotes: 0

Views: 2368

Answers (2)

Deepshikha
Deepshikha

Reputation: 10284

--Pseudo code for creating a Calendar table using a Common Table Expression (CTE) 
--and Custom date range

DECLARE @StartDate datetime,@EndDate datetime

SET @StartDate = '2019-09-13'
SET @EndDate = '2019-09-18'

;WITH CalendarDates(DATEPARAM) AS
(
    SELECT @StartDate AS datetime
    UNION ALL
    SELECT DATEADD(DAY, 1, DATEPARAM)
    FROM CalendarDates
    WHERE DATEPARAM < @EndDate 
) 
SELECT CD.DATEPARAM ,count(convert(date,T.testdate)) as records_on_theday
FROM @T T
Right outer join CalendarDates CD on CD.DATEPARAM = convert(date,T.testdate)
Group By convert(date,T.testdate),CD.DATEPARAM

Sample Code Here..

In the code example above let's assume the search is based on a given start and end date (custom date range filter). So the CalendarDates CTE will give all the dates between this range. The Right Outer Join with the source table will give the count of records you want as final output. Hope this helps!!

Upvotes: 1

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

For Year 

 SELECT * FROM  YourTableName WHERE DATEPART(YEAR, Date_ColumnName) = @Year

For Month

SELECT * FROM  YourTableName WHERE DATEPART(MONTH, Date_ColumnName) = @Month

For Day

  SELECT * FROM  YourTableName WHERE DATEPART(DAY, Date_ColumnName) = @Day

Custom Date Range 

SELECT * FROM YourTableName
WHERE 
convert(DATETIME,Date_ColumnName)  
BETWEEN Convert(DATETIME,CASE WHEN isnull(@FromDate,'')='' THEN Date_ColumnName
ELSE isnull(@FromDate,'') END)                     
AND Convert(DATETIME, CASE WHEN isnull(@ToDate,'')='' THEN Date_ColumnName 
ELSE isnull(@ToDate,'') END)

Note:- You can try this all Query...null part is also Handle in Custom Date Range.....i have already tested it out...

Upvotes: 1

Related Questions