mburk1968
asked on
Modify SQL Query to Select records between two dates
I have a SQL query that sums totals based on the current year and dumps those totals in to monthly buckets. For the sake of this question I only have Jan listed in the query however my output is from Jan thru Dec. I now need to modify the query to accept two Parameters. @BeginCancelDate and @EndCancelDate.
So I need to select all records where Ship_Date Or Start Date as in the case statement below is between the date range of my parameters above. The field value of the Parameters are ORD.End_Date
Then I need to output those totals like I'm currently doing. I should mention that I'm using SQL 2012
So I need to select all records where Ship_Date Or Start Date as in the case statement below is between the date range of my parameters above. The field value of the Parameters are ORD.End_Date
Then I need to output those totals like I'm currently doing. I should mention that I'm using SQL 2012
SELECT ORD.customer ,
CST.cust_name ,
C.group_code4 AS SCALE ,
ORD.lbl_code ,
SUM(CONVERT(FLOAT, CASE WHEN ( MONTH(CASE WHEN ( ORD.ship_date > 01
/ 01 / 1900 )
THEN ( ORD.ship_date )
ELSE ( ORD.start_date )
END) = 1
AND YEAR(CASE WHEN ( ORD.ship_date > 01
/ 01 / 1900 )
THEN ( ORD.ship_date )
ELSE ( ORD.start_date )
END) = DATEPART(YEAR,
GETDATE())
)
THEN ( ORD.Size_Qty * ORD.price
- CASE WHEN ( ISNULL(DSC.disc_perc, 0) <> 0 )
THEN ( ( ORD.Size_Qty
* ORD.price )
* ( DSC.disc_perc / 100 ) )
ELSE ( 0 )
END )
ELSE ( 0 )
END)) AS JAN
FROM [KLL All Order Detail With Style Data - Broken Down PPK Dtl] AS ORD
WITH ( NOLOCK )
INNER JOIN zzxcustr AS CST ON ORD.customer = CST.customer
LEFT OUTER JOIN zzxdiscr AS DSC ON ORD.discount = DSC.discount
LEFT OUTER JOIN KLL_Cust.dbo.KLLSSXREF AS SCL ON ORD.Size_Desc = SCL.Size
INNER JOIN zzxscolr AS C ON ORD.division = C.division
AND ORD.style = C.style
AND ORD.color_code = C.color_code
AND ORD.lbl_code = C.lbl_code
AND ORD.dimension = C.dimension
WHERE ( YEAR(CASE WHEN ( ORD.ship_date > 01 / 01 / 1900 )
THEN ( ORD.ship_date )
ELSE ( ORD.start_date )
END) BETWEEN DATEPART(YEAR, GETDATE()) - 1
AND DATEPART(YEAR, GETDATE()) )
AND ( ORD.line_status IN ( 'I', 'O', 'P' ) )
AND ( ORD.division = 'KLL' )
GROUP BY ORD.customer ,
CST.cust_name ,
C.group_code4 ,
ORD.lbl_code;
Are you able to provide DDL and a small sample data set?
why not group by month and then add a sum over partition by year ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You