April 16, 2020 at 3:03 pm
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?
April 16, 2020 at 5:06 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply