CODEWITHSUNDEEP
sql-servercommon-table-expression
wesley
wesley

Reputation: 103

SQL Server : how to use variable values from CTE in WHERE clause?

First of all please correct me if my title are not specific/clear enough.

I have use the following code to generate the start dates and end dates ：

DECLARE @start_date date, @end_date date;
SET @start_date = '2016-07-01';

with dates as
(
    select 
        @start_date AS startDate, 
        DATEADD(DAY, 6, @start_date) AS endDate

    union all

    select 
        DATEADD(DAY, 7, startDate) AS startDate, 
        DATEADD(DAY, 7, endDate) AS endDate
    from    
        dates
    where   
        startDate < '2017-03-31'
)
select * from dates

Below is part of the output from above query ：

+------------+------------+
| startDate  | endDate    |
+------------+------------+
| 2016-07-01 | 2016-07-07 |
| 2016-07-08 | 2016-07-14 |
| 2016-07-15 | 2016-07-21 |
| 2016-07-22 | 2016-07-28 |
| 2016-07-29 | 2016-08-04 |
+------------+------------+

Now I have another table named sales, which have 3 columns sales_id,sales_date and sales_amount as below :

+----------+------------+--------------+
| sales_ID | sales_date | sales_amount |
+----------+------------+--------------+
| 1        | 2016-07-04 | 10           |
| 2        | 2016-07-06 | 20           |
| 3        | 2016-07-13 | 30           |
| 4        | 2016-07-19 | 15           |
| 5        | 2016-07-21 | 20           |
| 6        | 2016-07-25 | 25           |
| 7        | 2016-07-26 | 40           |
| 8        | 2016-07-29 | 20           |
| 9        | 2016-08-01 | 30           |
| 10       | 2016-08-02 | 30           |
| 11       | 2016-08-03 | 40           |
+----------+------------+--------------+

How can I create the query to show the total sales amount of each week (which is between each startDate and endDate from the first table)? I suppose I will need to use a recursive query with WHERE clause to check if the dates are in between startDate and endDate but I cant find a working example.

Here are my expected result (the startDate and endDate are the records from the first table) :

+------------+------------+--------------+
| startDate  | endDate    | sales_amount |
+------------+------------+--------------+
| 2016-07-01 | 2016-07-07 | 30           |
| 2016-07-08 | 2016-07-14 | 30           |
| 2016-07-15 | 2016-07-21 | 35           |
| 2016-07-22 | 2016-07-28 | 65           |
| 2016-07-29 | 2016-08-04 | 120          |
+------------+------------+--------------+

Thank you!

Upvotes: 1

Views: 912

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Your final Select (after the cte) should be something like this

Select D.*
      ,Sales_Amount = sum(Sales) 
 From dates D
 Join Sales S on (S.sales_date between D.startDate and D.endDate)
 Group By D.startDate,D.endDate
 Order By D.startDate

EDIT: You could use a Left Join if you want to see missing dates from Sales

Upvotes: 1

Related Questions