sales between dates from different table

• spin

  Hall of Fame

  Points: 3274

  More actions

  November 30, 2018 at 2:02 am

  #380952

  Hi

  I am hoping someone can shed some light on this for me. I'm trying to get the sales units for a give date range taken from another table. The tables are as follows:

  
create table promotions
(
    [PromoId] int identity(1,1)
    ,[PromoCode] nvarchar(6)
    ,[Account] nvarchar(6)
    ,[ProductCode] nvarchar(6)
    ,[PromoUnits] numeric(18,0)
    ,[BuyInStart] date
    ,[BuyInEnd] date
)

  insert into promotions ([PromoCode], [Account],[ProductCode],[PromoUnits],[BuyInStart],[BuyInEnd])
select 'PAF001', 'AC0001', 'SKU101', 5000, '2018-01-01', '2018-01-31' union
select 'PAF001', 'AC0001', 'SKU102', 2500, '2018-01-01', '2018-01-31' union
select 'PAF002', 'AC0002', 'SKU101', 3200, '2018-01-29', '2018-02-06' union
select 'PAF002', 'AC0002', 'SKU104', 6000, '2018-01-29', '2018-02-06'

  create table sales
(
    [Account] nvarchar(6)
    ,[ProductCode] nvarchar(6)
    ,[SalesUnits] numeric(18,0)
    ,[InvoiceDate] date
)

  insert into sales ([Account],[ProductCode],[SalesUnits],[InvoiceDate])
select 'AC0001', 'SKU101', 744.00 , '2018-01-01' union
select 'AC0001', 'SKU101', 912.00 , '2018-02-01' union
select 'AC0001', 'SKU102', 372.00 , '2018-01-01' union
select 'AC0001', 'SKU102', 294.00 , '2018-02-01' union
select 'AC0001', 'SKU104', 234.00 , '2018-01-01' union
select 'AC0001', 'SKU104', 264.00 , '2018-02-01' union
select 'AC0002', 'SKU101', 72.00 , '2018-01-01' union
select 'AC0002', 'SKU101', 120.00 , '2018-02-01' union
select 'AC0002', 'SKU102', 36.00 , '2018-01-01' union
select 'AC0002', 'SKU104', 18.00 , '2018-01-01'

  .. and the resulting view i'm looking to build is something like this:

  
select p.PromoCode, p.Account, p.ProductCode, p.PromoUnits, p.BuyInStart, p.BuyInEnd, sum(s.SalesUnits) SalesUnits
from promotions p
left join sales s on p.Account = s.Account and p.ProductCode = s.ProductCode and [InvoiceDate] between BuyInStart and BuyInEnd
group by p.PromoCode, p.Account, p.ProductCode, p.PromoUnits, p.BuyInStart, p.BuyInEnd


  ..which seems to work ok, however, it feels like it might get a bit clunky when the data sets are large. Is there a better way than group by all the columns in the promotion table?

  Thanks

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 30, 2018 at 2:15 am

  #2015060

  I'm not sure there's any more efficient way than that.  You'll probably need indexes on the columns in the GROUP BY clause and in the join predicates as the number of rows starts to increase.

  John

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  November 30, 2018 at 3:29 am

  #2015070

  spin - Friday, November 30, 2018 2:02 AM

  Hi

  I am hoping someone can shed some light on this for me. I'm trying to get the sales units for a give date range taken from another table. The tables are as follows:

  
create table promotions
(
    [PromoId] int identity(1,1)
    ,[PromoCode] nvarchar(6)
    ,[Account] nvarchar(6)
    ,[ProductCode] nvarchar(6)
    ,[PromoUnits] numeric(18,0)
    ,[BuyInStart] date
    ,[BuyInEnd] date
)

  insert into promotions ([PromoCode], [Account],[ProductCode],[PromoUnits],[BuyInStart],[BuyInEnd])
select 'PAF001', 'AC0001', 'SKU101', 5000, '2018-01-01', '2018-01-31' union
select 'PAF001', 'AC0001', 'SKU102', 2500, '2018-01-01', '2018-01-31' union
select 'PAF002', 'AC0002', 'SKU101', 3200, '2018-01-29', '2018-02-06' union
select 'PAF002', 'AC0002', 'SKU104', 6000, '2018-01-29', '2018-02-06'

  create table sales
(
    [Account] nvarchar(6)
    ,[ProductCode] nvarchar(6)
    ,[SalesUnits] numeric(18,0)
    ,[InvoiceDate] date
)

  insert into sales ([Account],[ProductCode],[SalesUnits],[InvoiceDate])
select 'AC0001', 'SKU101', 744.00 , '2018-01-01' union
select 'AC0001', 'SKU101', 912.00 , '2018-02-01' union
select 'AC0001', 'SKU102', 372.00 , '2018-01-01' union
select 'AC0001', 'SKU102', 294.00 , '2018-02-01' union
select 'AC0001', 'SKU104', 234.00 , '2018-01-01' union
select 'AC0001', 'SKU104', 264.00 , '2018-02-01' union
select 'AC0002', 'SKU101', 72.00 , '2018-01-01' union
select 'AC0002', 'SKU101', 120.00 , '2018-02-01' union
select 'AC0002', 'SKU102', 36.00 , '2018-01-01' union
select 'AC0002', 'SKU104', 18.00 , '2018-01-01'

  .. and the resulting view i'm looking to build is something like this:

  
select p.PromoCode, p.Account, p.ProductCode, p.PromoUnits, p.BuyInStart, p.BuyInEnd, sum(s.SalesUnits) SalesUnits
from promotions p
left join sales s on p.Account = s.Account and p.ProductCode = s.ProductCode and [InvoiceDate] between BuyInStart and BuyInEnd
group by p.PromoCode, p.Account, p.ProductCode, p.PromoUnits, p.BuyInStart, p.BuyInEnd

  ..which seems to work ok, however, it feels like it might get a bit clunky when the data sets are large. Is there a better way than group by all the columns in the promotion table?

  Thanks

  As john suggested you can't  change much except between statement .Try John suggestion along with below query
  

  SELECT p.promocode,         p.account,         p.productcode,         p.promounits,         p.buyinstart,         p.buyinend,         Sum(s.salesunits) SalesUnits  FROM   promotions p         LEFT JOIN sales s                ON p.account = s.account                   AND p.productcode = s.productcode                   AND ( [invoicedate] >= buyinstart     /* excluded between statement */                        AND [invoicedate] <= buyinend )  GROUP  BY p.promocode,            p.account,            p.productcode,

  Saravanan