function - Why is my new SQL view/query ignoring my business rules? -
this complicated issue, apologize in advance. i've been doing lot of analysis , research on issue, , can't find answers.
in case relevant, i'm using sql server 2008 r2.
i created scalar value function works according business rules, when used in stored procedure takes 17 minutes run. also, it's r.b.a.r. annoying heck out of me.
as result, i'm trying write view replace it. view i've written , joined stored procedure takes 7 seconds run. problem it's not obeying business rules.
i'll post both original function, plus new view, first i'm going try explain business rules before hand, because whole thing messy.
if sale completed, return "completed".
if sales number not completed, sales person did complete sale, return "saved".
if no sales person able complete sale:
a. , customer cancelled sale, return "cancelled"
b. 1 sales person attempted make sale, return "missed"
c. more 1 sales person attempted make sale, both missed:
i. if 1 of sales people "owned" customer account, make call number "missed" , other associated call numbers "not missed"
ii. if none of sales people owned customer account, add "missed" sales person received order first.
last note: each customer order/sale has unifying "callnumber", list of 1 or more "salesnumbers", each attempt sales person fulfill order.
here's function
alter function [dbo].[findmissedvssaved] ( @salesnumber int, @salesdate nvarchar(10) ) returns nvarchar(20) begin
declare @status nvarchar(20) declare @year int set @year = year(@salesdate) -- section used determine "owner" of customer is; used double misses
declare @salesperson nvarchar(6) declare @salestype int set @salestype = (select salestype mydb.dbo.calls salesnumber=@salesnumber , salesdate = @salesdate) set @salesperson = case when @salestype in (8, 12) isnull((select top (1) b.salespersonname mydb.dbo.calls t join mydb.dbo.call_sources cs on t.callsource = cs.code join accountassignment csa on cs.code = csa.mydbaccountassignmentid join salesperson b on csa.assignedsalespersonid = b.salespersonid t.salesdate = @salesdate , t.salesnumber = @salesnumber), 'no assigned sales person') when @salestype in (9, 10, 13, 14) isnull((select top (1) b.salespersonname mydb.dbo.calls t join mydb.dbo.customer f on t.ofac = f.code join salesperson b on f.mydbsales_person = b.mydbsales_person t.salesdate = @salesdate , t.salesnumber = @salesnumber), 'no assigned sales person') else 'error' end declare @missedsalesperson nvarchar(6) set @missedsalesperson = (select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code t.salesnumber=@salesnumber , t.salesdate=@salesdate) -- pulls unique callnumber these records
declare @callnumber nvarchar(15) set @callnumber = ( select top (1) callnumber mydb.dbo.calls salesnumber = @salesnumber , salesdate = @salesdate) --figures out if sale made , made sale
declare @savedsalesperson nvarchar(8) set @savedsalesperson = (select top (1) ui.salespersonid --distinct(ui.salespersonid ) mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code year(salesdate) = @year , callnumber=@callnumber , salesnumber <> @salesnumber , salemade = 7 , salestype in (8, 9, 10, 12, 13, 14)) --this final section sets status returned particular salesnumber
set @status = case -- call number passed in made sale when exists (select top (1) salemade mydb.dbo.calls year(salesdate) = @year , callnumber=@callnumber , salesnumber = @salesnumber , salemade = 7) 'completed' --the call number passed in did not make sale sales person able accept call customer cancelled when exists(select top (1) cxlreason mydb.dbo.calls year(salesdate) = @year , callnumber=@callnumber , salesnumber <> @salesnumber , cxlreason in (3, 6, 7, 8, 13, 27, 32, 33, 36, 37, 44, 46) , salestype in (8, 9, 10, 12, 13, 14)) 'cancelled' --another sales person made sale i.e. save when exists( select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code year(salesdate) = @year , callnumber=@callnumber , salesnumber <> @salesnumber , salemade = 7 , salestype in (8, 9, 10, 12, 13, 14)) 'saved ' + @savedsalesperson --when has misc15 timestamp on it, doesn't meet saved criteria when (select top (1) tmt.misc15 mydb.dbo.calls t left outer join mydb.dbo.trip_misc_times tmt on t.salesdate = tmt.salesdate , t.job = tmt.job t.salesnumber = @salesnumber , year(t.salesdate) = @year) not null 'other' --the miss should go on sales person owns customer account, if attempted sale when @missedsalesperson = @salesperson 'missed' --for cases when sales person attempted make sale , s/he unable complete when not exists(select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code year(salesdate) = @year , callnumber=@callnumber , salesnumber <> @salesnumber , salestype in (8, 9, 10, 12, 13, 14)) 'missed' --looking salesperson might have attempted sale when @missedsalesperson <> @salesperson , @salesperson <> 'no assigned sales person' case --another sales person attempted transport , customer cancelled when exists(select top (1) salesnumber mydb.dbo.calls salesdate=@salesdate , callnumber = @callnumber , salesnumber <> @salesnumber , cxlreason in (3, 6, 7, 8, 13, 27, 32, 33, 36, 37, 46) , salestype in (8, 9, 10, 12, 13, 14)) 'not missed' --owner sales person attempted , missed when exists(select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code year(salesdate) = @year , callnumber=@callnumber , salesnumber <> @salesnumber , ui.salespersonid = @salesperson , salestype in (8, 9, 10, 12, 13, 14)) 'not missed' --another sales person attempted sale sales person call given call first when exists(select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code year(salesdate) = @year , callnumber=@callnumber , salesnumber <> @salesnumber , ui.salespersonid <> @salesperson , salestype in (8, 9, 10, 12, 13, 14)) , @missedsalesperson = (select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code t.salesdate = @salesdate , t.callnumber = @callnumber , t.salestype in (8, 9, 10, 12, 13, 14) order t.calltime) 'missed' --another sales person attempted sale , sales person call/transaction not called first when exists(select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code year(salesdate) = @year , callnumber=@callnumber , salesnumber <> @salesnumber , ui.salespersonid <> @salesperson , salestype in (8, 9, 10, 12, 13, 14)) , @missedsalesperson <> (select top (1) ui.salespersonid mydb.dbo.calls t join mydb.dbo.vehicle_schedule vs on t.schrecno = vs.schrecno join mydb.dbo.salespeople ui on vs.unit_code = ui.code t.salesdate = @salesdate , t.callnumber = @callnumber , t.salestype in (8, 9, 10, 12, 13, 14) order t.calltime) 'not missed' --anything missed else 'err' end end return @status here view wrote replace mess above:
---- main data (select t.salesdate, t.callnumber, t.salesnumber, cr.descr cancelreason, t.salemade, f.mydbsales_person ofacacctowner, b2.salespersonnumber csacctowner, cr.code cancelcode, ui.salespersonid salespersonthatmissed, tmt.misc15, t.calldate + ' ' + t.calltime calldate, t.salestype, accountowner = case when t.salestype in (8, 12) b.salespersonnumber when t.salestype in (9, 10, 13, 14) , len(f.mydbsales_person) < 2 'lf 0' + cast(f.mydbsales_person nvarchar(1)) when t.salestype in (9, 10, 13, 14) , len(f.mydbsales_person) >= 2 'lf ' + cast(f.mydbsales_person nvarchar(2)) end mydb.dbo.calls t left join mydb.dbo.cancellation_reasons cr on t.cxlreason = cr.code left join mydb.dbo.vehicle_schedule vs on t.schrecno=vs.schrecno left join mydb.dbo.customers f on t.ofac = f.code left join mydb.dbo.call_sources cs on t.callsource = cs.code left join mydb.dbo.salestypes p on t.salestype=p.code left join mydb.dbo.trip_misc_times tmt on t.job=tmt.job , t.salesdate=tmt.salesdate left join mydb.dbo.salesperson_ids ui on vs.salesperson_code=ui.code left join salespeople b on ui.salespersonid = b.salespersonnumber , b.isactive = 1 left join accountassignment csa on cs.code = csa.zollcallsourceid left join salespeople b2 on csa.assignedsalespersonid = b2.salespersonid t.salestype in (1, 2, 8, 9, 10, 12, 13, 14, 18) , t.salesnumber>0 , ui.salespersonid not 'lf 00%' , ui.salespersonid not 'iaa%' ), ---- checks completed b ( select t.callnumber, t.salemade, t.salestype, ui.salespersonid, t.salesnumber, t.calldate + ' ' + t.calltime calldate --cast(t.calltime + ' ' + t.calldate datetime2(1)) calldate mydb.dbo.calls t left join mydb.dbo.vehicle_schedule vs on t.schrecno=vs.schrecno left join mydb.dbo.salesperson_ids ui on vs.salesperson_code=ui.code salestype in (8, 9, 10, 12, 13, 14) , t.salemade=7 , t.salesnumber>0 , ui.salespersonid not 'lf 00%' , ui.salespersonid not 'iaa%'), ---- checks other missed c ( select distinct (c2.salesnumber), c1.callnumber, c2.salespersonid, c1.callc1 calldate--, c2.cancelreason, c2.cancelcode (select --top (1) --> top 1 retuns more rows, not less min(t.calldate + ' ' + t.calltime) callc1, t.callnumber mydb.dbo.calls t left join mydb.dbo.cancellation_reasons cr on t.cxlreason = cr.code left join mydb.dbo.vehicle_schedule vs on t.schrecno=vs.schrecno left join mydb.dbo.salesperson_ids ui on vs.salesperson_code=ui.code salestype in (8, 9, 10, 12, 13, 14) , t.salemade <> 7 , t.salesnumber > 0 , ui.salespersonid not 'lf 00%' , ui.salespersonid not 'iaa%' , cr.code in (14, 16, 17, 18, 19, 21, 22, 23, 24, 25, 30, 31, 38 , 40, 41, 42, 43, 45) group t.callnumber) c1 join (select t.callnumber, t.salemade, t.salestype, ui.salespersonid, t.salesnumber, t.calldate + ' ' + t.calltime calldate, cr.descr cancelreason, cr.code cancelcode mydb.dbo.calls t left join mydb.dbo.cancellation_reasons cr on t.cxlreason = cr.code left join mydb.dbo.vehicle_schedule vs on t.schrecno=vs.schrecno left join mydb.dbo.salesperson_ids ui on vs.salesperson_code=ui.code salestype in (8, 9, 10, 12, 13, 14) , t.salemade <> 7 , t.salesnumber>0 , ui.salespersonid not 'lf 00%' , ui.salespersonid not 'iaa%' , cr.code in (14, 16, 17, 18, 19, 21, 22, 23, 24, 25, 30, 31, 38 , 40, 41, 42, 43, 45) ) c2 on c1.callnumber = c2.callnumber , c1.callc1 = c2.calldate), ----checks cancelled d ( select t.callnumber, t.salemade, t.salestype, ui.salespersonid, t.salesnumber, t.calldate + ' ' + t.calltime calldate, cr.descr cancelreason, cr.code cancelcode mydb.dbo.calls t left join mydb.dbo.cancellation_reasons cr on t.cxlreason = cr.code left join mydb.dbo.vehicle_schedule vs on t.schrecno=vs.schrecno left join mydb.dbo.salesperson_ids ui on vs.salesperson_code=ui.code salestype in (8, 9, 10, 12, 13, 14) , t.salemade <> 7 , t.salesnumber>0 , ui.salespersonid not 'lf 00%' , ui.salespersonid not 'iaa%' , cr.code in (3, 6, 7, 8, 13, 27, 32, 33, 36, 37, 46)) select distinct(a.salesdate), a.salesnumber, a.accountowner, transportstatus = case when a.salemade = 7 'completed' when a.cancelreason = '<none>' 'completed' when a.cancelcode in (3, 6, 7, 8, 13, 27, 32, 33, 36, 37, 46) 'cancelled' when a.cancelcode in (-1, 1, 2, 4, 5, 9, 10, 11, 12, 15, 20, 26, 28, 29, 34, 35, 39, 44) 'ignore' when b.salesnumber not null , a.salestype in (8, 9, 10, 12, 13, 14) 'saved ' + b.salespersonid when a.misc15 not null 'not missed' when a.salestype in (1, 2, 18) , (a.salemade <> 7 or a.cancelreason <> '<none>') '(missed) ' + a.cancelreason when d.salesnumber not null 'not missed' when c.salesnumber <> a.salesnumber , c.salespersonid = a.accountowner 'not missed' when c.salesnumber <> a.salesnumber , a.salespersonthatmissed <> a.accountowner 'not missed' when a.salespersonthatmissed = a.accountowner '(missed) ' + a.cancelreason when c.salesnumber = a.salesnumber '(missed) ' + a.cancelreason when c.salesnumber null , b.salesnumber null , d.salesnumber null '(missed) ' + a.cancelreason else 'err' end left outer join b on a.callnumber = b.callnumber left outer join c on a.callnumber = c.callnumber left outer join d on a.callnumber = d.callnumber salesdate >= '2012-01-01'
i figured out.
i had not considered possible permutations of problem, such, view not handling business rules should have.
i ended adding cte (although, i'm not entirely sure these count ctes) , further logic case statement @ end.
Comments
Post a Comment