sum of sales 2 dates

• jham55

  SSC Enthusiast

  Points: 115

  More actions

  April 16, 2020 at 3:03 pm

  #3743471

  if object_id( 'tempdb..#anchorj') is not null begin drop table #anchorj end

  create table #anchorj ( ID varchar(10),anchor sales date date)

  User ID Sales Date

  S12 3/12/2015

  S13 4/5/2017

  S14 2/15/2016

  S15 1/1/2017

  S16 5/4/2014

  S17 3/24/2017

  S18 2/6/2015

  S19 4/18/2017

  S20 6/2/2015

  S21 7/5/2016

  S22 8/2/2015

  S23 8/2/2018

  Select Distinct

  User ID, sum ( sales_amt) as totalspend, sales date, DateADD( year,1,sales date) as year1date

  from #anchorj

  left join sales detail v ON (v.ID= User ID)

  Group by

  ID, SalesDate

   

  Ideally I would like ( UserID,Sales Date, Year1date, Sum of sales between dates) This query had gotten me close but still isnt where I need it. Any thoughts?

• Steve Collins

  SSCrazy

  Points: 2169

  More actions

  April 16, 2020 at 5:06 pm

  #3743514

  drop table if exists #anchorj;
  create table #anchorj(
    UserId        varchar(10),
    SalesDate     date,
    SalesAmount   int);
  
  insert #anchorj(UserId, SalesDate, SalesAmount) values
  ('S12', '3/12/2015', round(rand()*100,0)),
  ('S12', '3/14/2018', round(rand()*100,0)),
  ('S13', '4/5/2017', round(rand()*100,0)),
  ('S14', '2/15/2016', round(rand()*100,0)),
  ('S14', '2/16/2015', round(rand()*100,0)),
  ('S14', '2/17/2018', round(rand()*100,0)),
  ('S14', '2/18/2017', round(rand()*100,0)),
  ('S15', '1/1/2017', round(rand()*100,0)),
  ('S16', '5/4/2014', round(rand()*100,0)),
  ('S16', '5/5/2017', round(rand()*100,0)),
  ('S17', '3/24/2017', round(rand()*100,0)),
  ('S18', '2/6/2015', round(rand()*100,0)),
  ('S19', '4/18/2017', round(rand()*100,0)),
  ('S20', '6/2/2015', round(rand()*100,0)),
  ('S21', '7/5/2016', round(rand()*100,0)),
  ('S22', '8/2/2015', round(rand()*100,0)),
  ('S22', '8/3/2015', round(rand()*100,0)),
  ('S22', '8/4/2017', round(rand()*100,0)),
  ('S22', '8/5/2018', round(rand()*100,0)),
  ('S23', '8/2/2018', round(rand()*100,0));
  --select * from #anchorj; /* it worked but the date format should be YYYY-MM-DD or YYYYMMDD */
  
  with enr_dt_cte(UserId, min_SalesDate) as (
      select UserId, min(SalesDate) from #anchorj group by UserId)
  select
    a.UserId,
    datefromparts(year(a.SalesDate), month(a.SalesDate), 1) SalesMonth,
    sum(a.SalesAmount) Total,
    sum(iif(d.year_group=0, a.SalesAmount, 0)) [Year 1],
    sum(iif(d.year_group=1, a.SalesAmount, 0)) [Year 2],
    sum(iif(d.year_group=2, a.SalesAmount, 0)) [Year 3],
    sum(iif(d.year_group>2, a.SalesAmount, 0)) [Year 4+]
  from
    #anchorj a
   join
    enr_dt_cte edc on a.UserId=edc.UserId
   cross apply
    (values (datediff(year, edc.min_SalesDate, a.SalesDate))) as d(year_group)
  group by
    a.UserId,
    datefromparts(year(a.SalesDate), month(a.SalesDate), 1);
  

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können