0

I apologize in advance for formatting within this post. I am new to posting on Stack Exchange and still finding my way around.

I have a query returning a data set which is ranking customers by sales qty & dollar amount. The rank number is being calculated off of both the year & the month. I am trying to create a new column that associates just the rank from the last month & current year. Ultimately this column will be used as a primary grouping level for the entire data set.

I have been trying to go about this using a CASE statement but i am not sure that is the direction i should be going. My ELSE below is really a place holder for the output of the current rank associated with the customer that I actually want.

In my Sample of current data the MoYrRnk column shows the lines which output as desired.

Is a CASE statement a good way to get to what i am showing in my desired out put below?
IF no how should I go about getting a query return more in line with my Sample Desired Output?

This is what i have so far:

Declare @prmCurrYear int = Datepart(yy, Getdate())
      , @prmLastMonth int =  Datepart(MM, Getdate())-1
Case 
        When sls.Year = @prmCurrYear And sls.mo_num = @prmLastMonth Then rnk.Rnk
        Else 99999
    End MoYrRnk

Sample data current:

| MoYrRnk  | CustNo | Rnk | year | mo_num | Amount     |  
|----------|--------|-----|------|--------|------------|  
| 99999    | Cust_1 | 100 | 2019 | 1      | 22462.38   |  
| 99999    | Cust_1 | 85  | 2020 | 1      | 92950.92   |  
| 99999    | Cust_1 | 85  | 2020 | 2      | 129807.16  |  
| 99999    | Cust_1 | 100 | 2019 | 2      | 83422.75   |  
| 99999    | Cust_1 | 100 | 2019 | 3      | 78469.38   |  
| 85       | Cust_1 | 85  | 2020 | 3      | 28171.03   |  
| 99999    | Cust_2 | 1   | 2020 | 1      | 820598.94  |  
| 99999    | Cust_2 | 2   | 2019 | 1      | 908632.63  |  
| 99999    | Cust_2 | 2   | 2019 | 2      | 654689     |  
| 99999    | Cust_2 | 1   | 2020 | 2      | 667239.21  |  
| 1        | Cust_2 | 1   | 2020 | 3      | 1053866.43 |  
| 99999    | Cust_2 | 2   | 2019 | 3      | 735532.61  |  
| 99999    | Cust_3 | 3   | 2019 | 1      | 782990.74  |  
| 99999    | Cust_3 | 6   | 2020 | 1      | 644845.2   |  
| 99999    | Cust_3 | 6   | 2020 | 2      | 488458     |  
| 99999    | Cust_3 | 3   | 2019 | 2      | 548259.85  |  
| 99999    | Cust_3 | 3   | 2019 | 3      | 591954.48  |  
| 6        | Cust_3 | 6   | 2020 | 3      | 329686.73  |

Sample desired output:

| MoYrRnk  | CustNo | Rnk | year | mo_num | Amount     |  
|----------|--------|-----|------|--------|------------|  
| 85       | Cust_1 | 100 | 2019 | 1      | 22462.38   |  
| 85       | Cust_1 | 85  | 2020 | 1      | 92950.92   |  
| 85       | Cust_1 | 85  | 2020 | 2      | 129807.16  |  
| 85       | Cust_1 | 100 | 2019 | 2      | 83422.75   |  
| 85       | Cust_1 | 100 | 2019 | 3      | 78469.38   |  
| 85       | Cust_1 | 85  | 2020 | 3      | 28171.03   |  
| 1        | Cust_2 | 1   | 2020 | 1      | 820598.94  |  
| 1        | Cust_2 | 2   | 2019 | 1      | 908632.63  |  
| 1        | Cust_2 | 2   | 2019 | 2      | 654689     |  
| 1        | Cust_2 | 1   | 2020 | 2      | 667239.21  |  
| 1        | Cust_2 | 1   | 2020 | 3      | 1053866.43 |  
| 6        | Cust_2 | 2   | 2019 | 3      | 735532.61  |  
| 6        | Cust_3 | 3   | 2019 | 1      | 782990.74  |  
| 6        | Cust_3 | 6   | 2020 | 1      | 644845.2   |  
| 6        | Cust_3 | 6   | 2020 | 2      | 488458     |  
| 6        | Cust_3 | 3   | 2019 | 2      | 548259.85  |  
| 6        | Cust_3 | 3   | 2019 | 3      | 591954.48  |  
| 6        | Cust_3 | 6   | 2020 | 3      | 329686.73  |

Full Ranking Query:

Declare 
    @prmCurrYear int = Datepart(yy, Getdate())
    , @prmLastYear int = Datepart(yy, Getdate())-1
    , @prmLastMonth int =  Datepart(MM, Getdate())-1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Select 

        Case 
            When mosls.year1 = @prmCurrYear And mosls.mo_num = @prmLastMonth Then rnk.rnkPounds
            Else 99999
        End CurrMoYearRnk

        ,mosls.CustNo, mosls.CustName, rnkPounds, rnk.rnkAmount
        , mosls.year1, mosls.mo_num, mosls.mo_name ,mosls.Pounds, mosls.Amount




    --  , rnk.Pounds YTDPounds, rnk.Amount YTDAmount
From (
    Select sls.CustNo, sls.year1
        , Rank() Over(Partition by sls.year1 Order by sls.Pounds Desc) rnkPounds
        , Rank() Over(Partition by sls.year1 Order by sls.Amount Desc) rnkAmount
        --, Pounds
        --, Amount

    From (

            Select CustNo, dd.year1, Sum(Pounds) Pounds, Sum(sls.Amount) Amount  
            From (

                Select CustNo, [Posting Date], Sum(Pounds) Pounds, Sum(Amount) Amount  
                From (
                    Select 
                    sih.[Sell-to Customer No_] CustNo
                    ,  sih.[Posting Date]
                    , Sum(sil.[Qty Lbs]) Pounds
                    , Sum(sil.Amount) Amount
                    From [MyDataBase$Sales Invoice Header] sih
                        Inner Join [MyDataBase$Sales Invoice Line] sil   On sih.No_ = sil.[Document No_]
                    Where 1=1
                    Group by 
                        sih.[Sell-to Customer No_] 
                        ,  sih.[Posting Date]

                    Union All 

                    Select 
                        sih.[Sell-to Customer No_] CustNo
                        , sih.[Posting Date]
                        , Sum(sil.[Qty Lbs]) * -1
                        , Sum(sil.Amount)*-1 Amount
                    From [MyDataBase$Sales Cr_Memo Header] sih
                        Inner Join [MyDataBase$Sales Cr_Memo Line]  sil  On sih.No_ = sil.[Document No_]
                    Where 1=1
                    Group by 
                        sih.[Sell-to Customer No_]
                        , sih.[Posting Date] 
                    ) sls
            Group By CustNo, [Posting Date] 
            ) sls 
    Inner Join Helper.dbo.dim_dates dd On sls.[Posting Date] = dd.date1
    Where dd.mo_num <= @prmLastMonth --YTD
    Group By CustNo, dd.year1
    ) sls 
)rnk

Inner Join (

            Select CustNo, cust.Name CustName
                , year1, mo_num, mo_name ,Pounds, sls.Amount

            From (

                Select CustNo, dd.year1, dd.mo_num , dd.mo_name  , Sum(Pounds) Pounds, Sum(sls.Amount) Amount  
                From (

                    Select CustNo, [Posting Date], Sum(Pounds) Pounds, Sum(Amount) Amount  
                    From (
                        Select 
                        sih.[Sell-to Customer No_] CustNo
                        ,  sih.[Posting Date]
                        , Sum(sil.[Qty Lbs]) Pounds
                        , Sum(sil.Amount) Amount
                        From [MyDataBase$Sales Invoice Header] sih
                        Inner Join [MyDataBase$Sales Invoice Line] sil   On sih.No_ = sil.[Document No_]
                        Where 1=1
                        Group by 
                            sih.[Sell-to Customer No_] 
                            ,  sih.[Posting Date]

                        Union All 

                        Select 
                            sih.[Sell-to Customer No_] CustNo
                            , sih.[Posting Date]
                            , Sum(sil.[Qty Lbs]) * -1
                            , Sum(sil.Amount)*-1 Amount
                        From [MyDataBase$Sales Cr_Memo Header] sih
                            Inner Join [MyDataBase$Sales Cr_Memo Line]  sil  On sih.No_ = sil.[Document No_]
                        Where 1=1
                        Group by 
                            sih.[Sell-to Customer No_]
                            , sih.[Posting Date] 
                        ) sls
                Group By CustNo, [Posting Date] 
                ) sls 
            Inner Join Helper.dbo.dim_dates dd On sls.[Posting Date] = dd.date1
            Where dd.mo_num <= @prmLastMonth --YTD
            Group By CustNo, dd.year1, dd.mo_num, dd.mo_name
            ) sls
        Inner Join [MyDataBase$Customer] cust On sls.CustNo = cust.No_ 
) mosls On mosls.CustNo = rnk.CustNo And mosls.year1 = rnk.year1
Where 1=1

    And mosls.[year1] Between @prmLastYear And @prmCurrYear 

Order by rnkPounds, mo_num
Fmanin
  • 519
  • 1
  • 12
  • 25
Shae
  • 1
  • 2
  • 1
    CASE is a function/expression in SQL, not a statement. It has to appear within a query of some kind. Your example show it being used after a DECLARE statement which doesn’t work? Still not sure what you are trying to do... – RBarryYoung Apr 06 '20 at 19:50
  • Hmm, you’ll probably need a to change the first column to return a sub query of you current query. This is hard to explain/show without a starting example query to work from. – RBarryYoung Apr 06 '20 at 19:55
  • I have updated my original question with the full query i am working with. I have thought about trying to return a sub query, but afraid of getting into recursion given my full queries current nesting. I'll have a go at going down that path. – Shae Apr 06 '20 at 20:26
  • @RBarryYoung Thanks for the suggestion. Nesting in a subquery to return the current rank value worked. I recognize this has a horrible query execution plan, so refinement of the ranking query is still needed. However, this will suffice for the moment for a once a month report. – Shae Apr 06 '20 at 21:19
  • 1
    You might want to look at [`DateAdd`](https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15) for subtracting a month. (Just add `-1` `month`.) I don't think `Datepart(MM, Getdate())-1` will do what you want in January. You would be better off getting the current date _once_ and using `Year()` and `Month()` to get the parts you need, That avoids the fun when this runs near midnight and uses two different dates in the calculations. – HABO Apr 06 '20 at 21:40
  • @HABO Excellent suggestion & thank you. You are absolutely correct. I wasn't even thinking ahead to the year to year crossover yet. This report began as an adhoc request, but for a long semi longterm deployment it will definitely need to account for a constant rolling calendar year. – Shae Apr 13 '20 at 16:45

0 Answers0