Get data per Customer and per Date

Question

How will I get the all the date even though there is no order between two dates?

Sample Table

OrderID  | Date      | CusID
33942    | 6-21-2014 | 6005
34059    | 6-20-2014 | 4003
53333    | 6-23-2014 | 6005
59234    | 6-23-2014 | 4003

How I could have this result?

CusID   | Date      | OrderID |
4003    | 6-20-2004 | 34059   | 
4003    | 6-21-2004 | null    | 
4003    | 6-22-2004 | null    | 
4003    | 6-23-2004 | 59234   | 
6005    | 6-20-2004 | null    | 
6005    | 6-21-2004 | 33942   | 
6005    | 6-22-2004 | null    | 
6005    | 6-23-2004 | 53333   |

This what I did so far.

I create a calendar table

CREATE TABLE #Calendar
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(Year, -1, @EndDate)


WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO #Calendar
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

Then here is my query but does not give me the needed result

Select t.CusID, c.CalendarDate, t.OrderID 
From #Calendar c 
left outer join
#temp t
ON
CONVERT(VARCHAR(10), c.CalendarDate, 112) = CONVERT(VARCHAR(10), t.Date, 112)
Where c.CalendarDate Between '6-20-2014' and '6-23-2014'
Order By t.Name

score 1 · Answer 1 · edited May 23 '17 at 12:40

You also need a table with customers. The customers table needs to be CROSS JOINed with the calendar table to get the list of all possible dates per customer.

This SQL Fiddle demonstrates the query that gets the desired result. Please note that your database schema may have a table with customers, and you may not need a customers CTE I have created. Just use your existing customers table to CROSS JOIN it with the calendar table.

Also, as you use SQL Server 2008, it is about time you've learned CTE. It is much easier to use them than the temp tables. However, do not overuse them. Do not use them when performance is very important. Please read this question to better understand when to use CTE vs Temp tables

Another note: when truncating times from the datetime columns do not use CONVERT(VARCHAR(10), c.CalendarDate, 112). It is about the slowest possible method. Read this for more info

MS SQL Server 2008 Schema Setup:

CREATE TABLE Table1
    ([OrderID] int, [Date] DATETIME, [CusID] int)
;

INSERT INTO Table1
    ([OrderID], [Date], [CusID])
VALUES
    (33942, '6-21-2014', 6005),
    (34059, '6-20-2014', 4003),
    (53333, '6-23-2014', 6005),
    (59234, '6-23-2014', 4003)
;

Query 1:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(Year, -1, @EndDate)

;with calendar as (
  SELECT CAST(@StartDate AS DATE) CalendarDate
  UNION ALL 
  SELECT DATEADD(day, 1, CalendarDate) FROM calendar
  WHERE CalendarDate < @EndDate
  ),
customers as (
  SELECT DISTINCT CusID FROM Table1
  )
SELECT a.CusID, c.CalendarDate, OrderID
FROM customers a CROSS JOIN Calendar c 
LEFT JOIN Table1 t
ON CAST(c.CalendarDate AS DATE) = CAST(t.[Date] AS DATE) AND a.CusID = t.CusID
WHERE CAST(c.CalendarDate AS DATE) BETWEEN '6-20-2014' and '6-23-2014'
ORDER BY 1, 2
OPTION (MAXRECURSION 0)

Results:

| CUSID | CALENDARDATE | ORDERID |
|-------|--------------|---------|
|  4003 |   2014-06-20 |   34059 |
|  4003 |   2014-06-21 |  (null) |
|  4003 |   2014-06-22 |  (null) |
|  4003 |   2014-06-23 |   59234 |
|  6005 |   2014-06-20 |  (null) |
|  6005 |   2014-06-21 |   33942 |
|  6005 |   2014-06-22 |  (null) |
|  6005 |   2014-06-23 |   53333 |

Get data per Customer and per Date

1 Answers1

Linked

Related