Link to home
Start Free TrialLog in
Avatar of mburk1968
mburk1968Flag for United States of America

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

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;

Open in new window

Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

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
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mburk1968

ASKER

Thank You