CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
user1695981
user1695981

Reputation: 89

Get net amount by date with SQL Server using Left join

I'm trying to get the net amount by day, using a CTE and my purchases table. The following gives me an error which calendar.DateValue is not in group by clause. I'm using a CTE because I can't think of any other way of getting date values of the range. o.date is a datetime populated with GETDATE() upon the order placement, so it contains time as well.

WITH calendar AS
(
  SELECT CAST('2013-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    calendar
  WHERE   DateValue < '2013-12-31'
)
SELECT
    SUM(o.amount) AS total_amount,
    c.DateValue 
FROM 
    calendar c 
LEFT JOIN 
    dbo.orders o ON c.DateValue = o.date
OPTION(MAXRECURSION 0)

Desired result:

DateValue   | total_amount
------------|-------------
2013-01-01  | 219.00
2013-01-02  | 397.00

Upvotes: 2

Views: 266

Answers (2)

Szymon
Szymon

Reputation: 43023

You can just cast your o.[date] as DATE type to get the date part only (no time).

WITH calendar AS
(
  SELECT CAST('2013-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    calendar
  WHERE   DateValue < '2013-12-31'
)
SELECT
    X.total_amount,
    c.DateValue 
FROM 
    calendar c
LEFT JOIN
(
    SELECT CAST(o.[date] AS DATE) AS order_date, SUM(o.amount) AS total_amount
    FROM dbo.orders o
    WHERE o.[date] >= '2013-01-01' AND o.[date] <= '2013-12-31 23:59:59'
    GROUP BY CAST(o.[date] AS DATE)
) X
ON X.order_date = c.DateValue

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10274

Try this:

WITH calendar AS
(
  SELECT CAST('2013-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    calendar
  WHERE   DateValue < '2013-12-31'
)
SELECT
    SUM(o.amount) AS total_amount,   
    c.DateValue 
FROM 
    calendar c 
LEFT JOIN 
    dbo.orders o ON c.DateValue = cast (o.[date] as date)
group by c.DateValue 
OPTION(MAXRECURSION 0)

Upvotes: 1

Related Questions