Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

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 |
share|improve this answer
    
I, too am a fan of CTEs. I agree they can greatly clarify the layout of code. They have different performance characteristics to temporary tables, however, and each should be used where appropriate. –  Michael Green Jun 25 at 0:58
    
Thanks Michael. I have updated the answer to warn the OP about the difference between CTE and Temp tables with a link to a question that discusses the usage of each of them –  cha Jun 25 at 1:16
    
While better than a loop, a recursive CTE is not much faster. Why not have a permanent calendar table? sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1 sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2 sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3 <-- pay particular attention to the perf results of the recursive CTEs in parts 1 & 2 –  Aaron Bertrand Jun 25 at 3:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.