sql server - Get Average amount of Months between sales in each row in SQL -
name customer stockcode description 3/01/2013 4/01/2013 5/01/2013 6/01/2013 7/01/2013 8/01/2013 9/01/2013 10/01/2013 11/01/2013 12/01/2013 1/01/2014 2/01/2014 3/01/2014 4/01/2014 5/01/2014 6/01/2014 7/01/2014 8/01/2014 9/01/2014 10/01/2014 11/01/2014 12/01/2014 1/01/2015 2/01/2015 3/01/2015 aag ice cream 564 243702 4/1 gal ja-rtu pineapple null null null 274.40 null null 313.60 null null null null null null null null 392.00 null null null null null null null null null aag ice cream 564 243701 4/1 gal ja-rtu strawberry null null null 660.00 null null null 660.00 null null null null null null null 660.00 null null null null null null null null null aag ice cream 564 248050 4/1 gal-rtu choc syrup null null null 534.00 null null 534.00 null null null null null null null null 534.00 null null null null null null null null null i need determine average amount of months between sales per row. if have 3 nulls sale , 4 nulls , sale , 4 nulls , sale on row can come 3.6 months(4 rounded) on average customer buy item during last 24 months.so if see 5 month elapse today without sale can flag customer on row.(customer/stockcode combo.)
other style - direct query no pivot.
select customer,stockcode,sum(invoicevalue) amount ,month(trndate) [month],year(trndate) [year] arsalesmove trndate between dateadd(year,-2,getdate()) , getdate() group customer,stockcode,month(trndate),year(trndate) order customer,stockcode,year(trndate),month(trndate) customer stockcode amount month year 0000023 850802 3542.40 5 2013 0000023 850802 0 6 2013 0000023 850802 0 7 2013 0000023 850802 0 8 2013 0000023 850802 2361.60 9 2013 0000023 850802 0 10 2013 0000023 850802 0 11 2013 0000023 850802 0 12 2013 0000023 850802 2361.60 1 2014 0000023 850802 0 2 2014 0000023 850802 0 3 2014 0000023 850802 0 4 2014 0000023 850802 4723.20 5 2014 0000023 850802 0 6 2014 0000023 850802 0 7 2014 0000023 850802 3542.40 8 2014 0000023 850802 0 9 2014 0000023 850802 0 10 2014 0000023 850802 0 11 2014 0000023 850802 2361.60 12 2014 0000023 850802 0 1 2015 0000023 850802 0 2 2015 0000023 850802 0 3 2015 create table [dbo].[arsalesmove]( [customer] [char](7) not null, [trndate] [datetime] not null, [register] [decimal](5, 0) not null, [summaryline] [decimal](5, 0) not null, [detailline] [decimal](5, 0) not null, [invoice] [char](6) null, [stockcode] [char](30) null, [warehouse] [char](2) null, [invoiceqty] [decimal](10, 3) null, [invoicevalue] [decimal](14, 2) null, [costvalue] [decimal](14, 2) null, [documenttype] [char](1) null, [branch] [char](2) null, [salesperson] [char](3) null, [bin] [char](6) null, [ordertype] [char](2) null, [area] [char](2) null, [productclass] [char](4) null, [taxcode] [char](1) null, [taxvalue] [decimal](14, 2) null, [discvalue] [decimal](14, 2) null, [taxcodefst] [char](1) null, [taxvaluefst] [decimal](14, 2) null, [salesorder] [char](6) null, [customerponumber] [char](30) null, [trnyear] [decimal](4, 0) null, [trnmonth] [decimal](2, 0) null, [forinvoicevalue] [decimal](14, 2) null, [invoicecurrency] [char](3) null, [salesorderline] [decimal](4, 0) null, [timestamp] [timestamp] null, constraint [arsalesmovekey] primary key clustered ( [customer] asc, [trndate] asc, [register] asc, [summaryline] asc, [detailline] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]
what did create groupid field, populated value invoicevalue not equal 0:
set @counter = 0 update @tempsales set @counter = tt.groupid = @counter + 1 @tempsales tt case when tt.invoicevalue <> 0 1 else 0 end = 1 that left me null values invoicevalue null filled null:
select t1.id,t1.trndate, t1.customer,t1.stockcode,t1.invoicevalue, (--fill null values... select t2.groupid @tempsales t2 t2.id = ( select max(t3.id) @tempsales t3 t3.id <= t1.id , groupid not null ) ) groupid @tempsales t1 next counted groupid , group groupid.
then got average of counts , date of last sale max(trndate), , created flag customer/stockcode combinations exceed average months between sales.
case when monthssincelastsale > zeroavg + 1 1 else 0 end flag - and here in stored procedure...
alter proc getzeromonthsreport @customer varchar(50), @stockcode varchar(50) declare @tempsales table (id int identity (1,1) not null primary key, trndate datetime, customer nvarchar(50),stockcode nvarchar(50), invoicevalue decimal, groupid int) declare @temp table (id int identity (1,1) not null primary key, trndate datetime, customer nvarchar(50),stockcode nvarchar(50), invoicevalue decimal, groupid int) declare @counter int insert @temp (trndate, customer,stockcode, invoicevalue) select convert(varchar(50),month(trndate)) + '/01/' + convert(varchar(50),year(trndate)) trndate , rtrim(customer),rtrim(stockcode), sum(invoicevalue) dbo.arsalesmove s with(nolock) trndate between dateadd(year,-2,getdate()) , getdate() , rtrim(customer)+rtrim(stockcode) not in ( select rtrim(customer)+rtrim(stockcode) dbo.arsalesexclusion with(nolock)) , rtrim(customer)<>'999' , rtrim(stockcode)<>'704033'--wood pallets , rtrim(customer)in (select distinct rtrim(customer) arsalesmove trndate between dateadd(year,-2,getdate()) , getdate()) , rtrim(stockcode)in (select distinct rtrim(stockcode) arsalesmove trndate between dateadd(year,-2,getdate()) , getdate()) group customer,stockcode,month(trndate),year(trndate) order customer,stockcode,trndate ;with enum ( select customer,stockcode, startdate = convert(datetime, convert(varchar(50),month(dateadd(year,-2,getdate()) )) + '/01/' + convert(varchar(50),year(dateadd(year,-2,getdate()) ))), -- min(trndate), enddate = getdate()-- max(trndate) @temp group customer,stockcode union select customer,stockcode, dateadd(month, 1, startdate), enddate enum startdate < enddate ) insert @tempsales (trndate, customer,stockcode,invoicevalue,groupid) select e.startdate, e.customer, e.stockcode, invoicevalue = isnull(t.invoicevalue, 0), groupid enum e left join @temp t on e.startdate = t.trndate , e.stockcode = t.stockcode , e.customer = t.customer order e.customer,e.stockcode, e.startdate set @counter = 0 update @tempsales set @counter = tt.groupid = @counter + 1 @tempsales tt case when tt.invoicevalue <> 0 1 else 0 end = 1 select (select name arcustomer rtrim(customer) = d.customer) name, d.customer,d.stockcode, (select [description] invmaster rtrim(stockcode) = d.stockcode) [description], d.lastsaledate,d.monthssincelastsale,d.zeroavg,d.flag ( select c.customer,c.stockcode,c.lastsaledate,c.monthssincelastsale,c.zeroavg,c.flag ( select b.customer,b.stockcode,b.lastsaledate,b.monthssincelastsale,b.zeroavg,case when monthssincelastsale > zeroavg + 1 1 else 0 end flag --add 1 month cusion.. ( select a.customer,a.stockcode, avg(a.zerocount) zeroavg, (select count(trndate) monthssincelastsale @tempsales customer = a.customer , stockcode = a.stockcode , trndate between (select max(trndate) @tempsales tt customer = a.customer , stockcode = a.stockcode , invoicevalue <> 0) , getdate()) monthssincelastsale, (select max(trndate) @tempsales tt customer = a.customer , stockcode = a.stockcode , invoicevalue<> 0) lastsaledate ( select count(z.groupid) zerocount,z.customer,z.stockcode,z.groupid ( select x.id,x.trndate, x.customer,x.stockcode,x.invoicevalue,isnull(x.groupid,0) groupid ( select t1.id,t1.trndate, t1.customer,t1.stockcode,t1.invoicevalue, (--fill null values... select t2.groupid @tempsales t2 t2.id = ( select max(t3.id) @tempsales t3 t3.id <= t1.id , groupid not null ) ) groupid @tempsales t1 ) x ) z z.invoicevalue = 0 group z.groupid,z.customer,z.stockcode ) group customer,stockcode ) b b.lastsaledate not null ) c c.flag = 1 ) d order d.customer,d.stockcode
Comments
Post a Comment