SQL select everything in date range with multiple values for each date

Question

I've tried searching but I'm not entirely sure how to word it so apologies in advance if this has already been answered elsewhere.

I am trying to select Everything between a date range (Including where there is no value for that date) for mutiple values. I havent really explained very weel but below is an example of the ouput I am after.

Date    Campaign    Channel Transaction
01/01/2015  a   Online  Sale
01/01/2015  b   Online  Sale
01/01/2015  c   Online  Sale
01/01/2015  a   Online  Cancellation
01/01/2015  b   Online  Cancellation
01/01/2015  c   Online  Cancellation
01/01/2015  a   Offline Sale
01/01/2015  b   Offline Sale
01/01/2015  c   Offline Sale
01/01/2015  a   Offline Cancellation
01/01/2015  b   Offline Cancellation
01/01/2015  c   Offline Cancellation
02/01/2015  a   Online  Sale
02/01/2015  b   Online  Sale
02/01/2015  c   Online  Sale
02/01/2015  a   Online  Cancellation
02/01/2015  b   Online  Cancellation
02/01/2015  c   Online  Cancellation
02/01/2015  a   Offline Sale
02/01/2015  b   Offline Sale
02/01/2015  c   Offline Sale
02/01/2015  a   Offline Cancellation
02/01/2015  b   Offline Cancellation
02/01/2015  c   Offline Cancellation

I am able to select every day between a date range however cannot work out how to select every day with every outcome even if no results are returned.

Edit - Added solution I found

OK, So I have a solution which seems to be working for me. There may be a better way of doing this and any suggestions are appreciated but here is what I am now doing

Declare @CC TABLE (Campaign varchar(50))
DECLARE @Channel Table (Channel Varchar(255))
DECLARE @TYPE TABLE (Transaction_Type varchar(255))
Insert Into @CC (Campaign) Values ('1')
Insert Into @CC (Campaign) Values ('2')
Insert Into @Channel (Channel) Values ('Online')
Insert Into @Channel (Channel) Values ('Inbound')
Insert Into @TYPE (Transaction_Type) Values ('Sale')
Insert Into @TYPE (Transaction_Type) Values ('Cancellation')
DECLARE 
@StartDate DATE = Dateadd(YY,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), 
@EndDate DATE = Getdate() 
SELECT 
DATEADD(DAY, nbr - 1, @StartDate) as Dates,
Campaign, 
Channel, 
Transaction_Type 
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr  
FROM sys.columns c) nbrs,
@CC, @Channel, @TYPE WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
Order by Dates asc, Campaign asc, Channel asc, Transaction_Type asc

I have currently used the following code to create a static table. This will be something that will be needed again so thought made sense to have a static table. `CREATE TABLE _Dates ( d DATE, PRIMARY KEY (d) ) DECLARE @dIncr DATE = '2000-01-01' DECLARE @dEnd DATE = '2100-01-01' WHILE ( @dIncr < @dEnd ) BEGIN INSERT INTO _Dates (d) VALUES( @dIncr ) SELECT @dIncr = DATEADD(DAY, 1, @dIncr ) END` — Jamie Joy, Dec 09 '14 at 12:26

mxix · Answer 1 · 2014-12-10T14:02:33.580

1

You can do it with a CTE.

  DECLARE
    @start date = '1900-01-01',
    @end date = '1900-01-10';

with dateRange (myDate) AS (
    select @start myDate
    UNION ALL
    select DATEADD(DAY,1,myDate) from dateRange
    where
        myDate <= @end
)
select 
    * 
from dateRange D
inner join yourTable T on
    D.myDate = T.[Date]
OPTION (MAXRECURSION 365)

With the full outer joins

DECLARE
    @start date = '1900-01-01',
    @end date = '1900-01-10';

with dateRange (myDate) AS (
    select @start myDate
    UNION ALL
    select DATEADD(DAY,1,myDate) from dateRange
    where
        myDate <= @end
)
select 
    * 
from dateRange D
full outer join @CC C ON
    1=1
full outer join @Channel CH ON
    1=1
full outer join @TYPE Ty ON
    1=1
left join yourTable T on
    D.myDate = T.[Date] and
    C.Campaign = T.[Campaign] and
    CH.Channel = T.[Channel] and
    Ty.[Transaction_Type] = T.[Transaction_Type]
OPTION (MAXRECURSION 365)

edited Dec 10 '14 at 14:02

answered Dec 09 '14 at 12:09

mxix

3,539
1
16
23

Was just busy with that. +1 for you – Jaques Dec 09 '14 at 12:09
This is what I'm currently using to get the date values returned. I cannot work out how to return the date for every possible outcome though. e.g I need 1st jan to return for Every Campaign, every possible Transaction type and every Sales Channel. Do i need a to create the table with a line for every outcome and join this to my dataset? – Jamie Joy Dec 09 '14 at 12:20
full outer join with your campaign table and only then join your results – mxix Dec 09 '14 at 19:53

score 0 · Answer 2 · edited May 23 '17 at 12:22

With pure SQL (and without recursive CTEs), I'm afraid you will need a numbers table for that.

The basic idea will be to:

determine n, the number of dates between your start and your end dates,
select the number 1 to n from your numbers table, and
add the numbers (as days) to the start date.

This should yield a list of dates from your start to your end date, which you can outer join to your original result set to "fill the gaps".

score 0 · Answer 3 · answered Dec 09 '14 at 12:07

I am using Table Valued Function that returns all dates in a range (here)..

You could do small changes (like renaming, datetime -> date, remove char column) to fit your needs.

Create Function [dbo].[IntervalaDatumi]
(
    @DatumsNo DateTime,
    @DatumsLidz DateTime
)
Returns @tab Table
    (
        Datums DateTime,
        DatumsChar char(10)
    )
As
Begin
    Declare @Dienas int
    Declare @i int
    Set @Dienas = DateDiff(Day, @DatumsNo, @DatumsLidz)

    Set @i = 0;
    While (@Dienas > @i)
    Begin
        Insert Into @tab(Datums, DatumsChar)
            Values (DateAdd(Day, @i, @DatumsNo), Convert(Char(10), DateAdd(Day, @i, @DatumsNo), 104))
        Set @i = @i + 1
    End
    return
End

SQL select everything in date range with multiple values for each date

3 Answers3