CODEWITHSUNDEEP
sqlt-sql
Stephanus DJ
Stephanus DJ

Reputation: 109

Adding in zeroed rows for years without data T-SQL

I have a large dataset with sales data that looks something like

***Category - Salesperson - Customer - Year - Jun - Jul .....***
Summary - Candice - R.Zeek - 2016 - $100 - $10
Summary - Candice - R.Zeek - 2017 - $40 - $50
Shirts - Candice - R.Zeek - 2016 - $80 - $10
Shirts - Candice - R.Zeek - 2017 - $40 - $50
Pants - Candice - R.Zeek - 2016 - $20 - $0

The problem is that there is no 2017 line for pants in this example. Since all the sales were in Shirts for 2017.

This has led to a scenario where if a salesperson doesn't have any sales data for a specific Category/Salesperson/Customer/Year combination then it doesn't have a line.

This makes the sales report look bad since different salespersons will have a different number of rows. I want uniformity so how do I get a line for 2017 in the example where the sales for Jun, Jul are just null or 0.

To add on to this post. The following 2 queries

Select distinct Year from @FinalResultTable

Select distinct CategorySequence, SalesPersonIdFromCustomer, CustomerNumber, Year from @FinalResultTable

Produces 1. The number of distinct years. In my case 2015, 2016, 2017, 2018. And 2. The sales data that I have that needs the additional rows

Year

2015

2016

2017

2018

CategorySequence SalesPersonIdFromCustomer CustomerNumber Year

1 SP000032 C000703 2016

1 SP000032 C000703 2017

1 SP000032 C000710 2016

1 SP000032 C000710 2017

1 SP000032 C000710 2018

Edit 2.

I added the following code

Insert into @FinalResultTable
    Select  S1.CategorySequence, SRT.CategoryDescription, S2.SalesPersonIdFromCustomer, SRT.SalesPersonName, S3.CustomerNumber, SRT.CustomerName, SRT.CustomerAddress, SRT.PercentageOrAmount,
            S4.Year, SRT.Jun, SRT.Jul, SRT.Aug, SRT.Sep, SRT.Oct, SRT.Nov, SRT.Dec, SRT.Jan, SRT.Feb, SRT.Mar, SRT.Apr, SRT.May,
            Coalesce(SRT.Jun,0) + Coalesce(SRT.Jul,0) + Coalesce(SRT.Aug,0) + Coalesce(SRT.Sep,0) + Coalesce(SRT.Oct,0) + Coalesce(SRT.Nov,0) + Coalesce(SRT.Dec,0) + 
            Coalesce(SRT.Jan,0) + Coalesce(SRT.Feb,0) + Coalesce(SRT.Mar,0) + Coalesce(SRT.Apr,0) + Coalesce(SRT.May,0)
    From        (select distinct CategorySequence from @SelectResultTable) S1
    Cross join  (select distinct SalesPersonIdFromCustomer from @SelectResultTable) S2
    Cross join  (select distinct CustomerNumber from @SelectResultTable) S3
    Cross join  (select distinct year from @SelectResultTable) S4
    Left join   @SelectResultTable SRT on 
        SRT.CategorySequence = S1.CategorySequence and
        SRT.SalesPersonIdFromCustomer = S2.SalesPersonIdFromCustomer and
        SRT.CustomerNumber = S3.CustomerNumber and
        SRT.Year = S4.Year

Select * from @FinalResultTable
    Order by SalesPersonIdFromCustomer, CustomerNumber, CategorySequence, PercentageOrAmount, Year 

Return

It works but the problem is that All the SRT columns (For example Category description) are all Null. So do I need to do an update and traverse the table again to get all the information or can i modify the insert to handle that?

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can generate the rows using cross join and then join in the information that exists:

select c.Category, sp.Salesperson, t.Customer, y.Year
       t.Jun, t.Jul 
from (select distinct salesperson from t) sp cross join
     (select distinct category c from t) c cross join
     (select distinct year from t) as y left join
     t
     on t.salesperson = sp.salesperson and t.category = c.category and t.year = y.year;

If you want zeroes instead of NULLs, then use COALESCE(t.Jun, 0) as Jun` and so on.

Upvotes: 1

Related Questions