Multiple date range SUM query 1

[kpetree10]

I've got a table that contains orders that have shipped. What I'm trying to do is sum the total price of all the orders with a back order quantity within three date ranges, this week, last week, and two weeks ago and group them by customer number. This is my query so far...

SET DATEFIRST 1
DECLARE @TodayDayOfWeek INT
DECLARE @EndOf2Week DateTime
DECLARE @StartOf2Week DateTime
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
DECLARE @EndOfCurrentWeek DateTime
DECLARE @StartOfCurrentWeek DateTime
SET @TodayDayOfWeek = datepart(dw, GetDate())
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())
SET @EndOf2Week = DATEADD(dd, -@TodayDayOfWeek+7, GetDate())
SET @StartOf2Week = DATEADD(dd, -(@TodayDayOfWeek+13), GetDate())
SET @EndOfCurrentWeek = dateadd(day, +6, dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate()))
SET @StartOfCurrentWeek = dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate())
select cus_no,(Select SUM(qty_bkord*unit_price) from  oelinhst_sql 
where shipped_dt between CONVERT(VARCHAR, @StartOfCurrentWeek,7)AND CONVERT(VARCHAR, @EndOfCurrentWeek+1,7)) AS CurrentWk_Bkord_Amt,
(Select SUM(qty_bkord*unit_price) from  oelinhst_sql
where shipped_dt between CONVERT(VARCHAR, @StartOfPrevWeek,7)AND CONVERT(VARCHAR, @EndOfPrevWeek+1,7)) AS PrevWk_Bkord_Amt,
(select SUM(qty_bkord*unit_price) from  oelinhst_sql
where shipped_dt between CONVERT(VARCHAR, @StartOf2Week,7)AND CONVERT(VARCHAR, @EndOf2Week+1,7)) AS TwoWk_Bkord_Amt 
from oelinhst_sql where qty_bkord > 0 group by cus_no

However when I run this it sums all the orders in the date range and puts the same value for each customer. Does anybody have an idea on how I can get the total for just that particular customer?

 

[markros]

Instead of using multiple subqueries, try instead:

select cus_no,SUM(case when shipped_dt between @StartOfCurrentWeek AND @EndOfCurrentWeek then qty_bkord*unit_price else 0 end) AS CurrentWk_Bkord_Amt,
SUM(case when shipped_dt between @StartOfPrevWeek AND @EndOfPrevWeek then qty_bkord*unit_price else 0 end) AS PrevWk_Bkord_Amt,
etc.
from oelinhst_sql where qty_bkord > 0
 group by cus_no

PluralSight Learning Library

 

[kpetree10]

Thanks markros!