i have syntax of function, totally useless how put conditions code (sql) return answer.
i have 3 tables,
t1.number varchar(max) t1.date datetime t1.ctps int t2.number varchar(max) t2.addeddate date t2.removeddate date t2.ctps int t3.otype varchar(max) t3.lnumber varchar(max) and 3 variables,
@lnumber varchar(max), @otype varchar(max), @cdate datetime i need function take linenumber go table 1 check if number in there if needs grab otype name , if ltd or plc needs check if cpts flag set 1 if needs check contractdate against t1.date if contrcatdate before t1.date return "no" else if contractdate later t1.date return "yes"
here attemp no good, friend suggested using if else staement in function no idea how implement either lol
any appreciated
if object_id(n'tps_checker', n'fn') not null drop function tps_checker ; go set ansi_nulls on go set quoted_identifier on go -- ============================================= -- author: <author,,name> -- create date: <create date, ,> -- description: <description, ,> --@linenumber = u.cli, @organisationtype = o.name, @contractdate = c.contractdate -- ============================================= create function tps_checker (@linenumber varchar(max), @contractdate datetime, @organisationtype varchar) returns varchar(4) begin declare @answer varchar(4) select @answer = case when ((((((@linenumber = [u].[cli]) , @organisationtype = 'plc') or @organisationtype = 'ltd') , [u].[date] not null) , @contractdate < [u].[date]) , [u].[ctps] = 0) 'no' when ((((((@linenumber = [u].[cli]) , @organisationtype = 'plc') or @organisationtype = 'ltd') , [u].[date] not null) , @contractdate < [u].[date]) , [u].[ctps] = 1) 'no' when ((((((@linenumber = [u].[cli]) , @organisationtype != 'plc') or @organisationtype != 'ltd') , [u].[date] not null) , @contractdate < [u].[date]) , [u].[ctps] = 0) 'no' when ((((((@linenumber = [u].[cli]) , @organisationtype != 'plc') or @organisationtype != 'ltd') , [u].[date] not null) , @contractdate < [u].[date]) , [u].[ctps] = 1) 'no' when ((((((@linenumber = [u].[cli]) , @organisationtype = 'plc') or @organisationtype = 'ltd') , [u].[date] not null) , @contractdate > [u].[date]) , [u].[ctps] = 0) 'no' when ((((((@linenumber = [u].[cli]) , @organisationtype != 'plc') or @organisationtype != 'ltd') , [u].[date] not null) , @contractdate > [u].[date]) , [u].[ctps] = 1) 'yes' when ((((((@linenumber = [u].[cli]) , @organisationtype != 'plc') or @organisationtype != 'ltd') , [u].[date] not null) , @contractdate > [u].[date]) , [u].[ctps] = 0) 'yes' end [contract] c inner join [account] on [c].[accountfk] = [a].[accountid] inner join [profile] p on [p].[profileid] = [a].[profilefk] inner join [line] l on [l].[contractfk] = [contractid] left join [deal] d on [c].[contractid] = [d].[contractfk] inner join ( select branchtypefk, branchid, name [branch] b )b on [d].[branchfk] = [b].[branchid] , branchtypefk in (1,2,4) inner join [organisationtype] o on [p].[organisationtypefk] = [o].[organisationtypeid] left join [lookup] u on u.cli = l.linenumber left join history h on h.cli = l.linenumber return @answer -- return result of function return @answer end select top 100 [corporate\hargreavesd].[tps_checker1](u.cli ,c.contractdate,o.name) answer [contract] c inner join [account] on [c].[accountfk] = [a].[accountid] inner join [profile] p on [p].[profileid] = [a].[profilefk] inner join [line] l on [l].[contractfk] = [contractid] left join [deal] d on [c].[contractid] = [d].[contractfk] inner join ( select branchtypefk, branchid, name [branch] b )b on [d].[branchfk] = [b].[branchid] , branchtypefk in (1,2,4) inner join [organisationtype] o on [p].[organisationtypefk] = [o].[organisationtypeid] left join [lookup] u on u.cli = l.linenumber left join history h on h.cli = l.linenumber this first attempt not in function
declare @startdate date = '01/04/2013' declare @enddate date = '30/04/2013' select contractid ,linenumber ,contractdate ,[date] ,o.name ,u.[file] ,dateadded ,dateremoved ,u.ctps ,b.name [branch name] end [tps] [contract] c inner join [account] on [c].[accountfk] = [a].[accountid] inner join [profile] p on [p].[profileid] = [a].[profilefk] inner join [line] l on [l].[contractfk] = [contractid] left join [deal] d on [c].[contractid] = [d].[contractfk] inner join ( select branchtypefk, branchid, name [branch] b )b on [d].[branchfk] = [b].[branchid] , branchtypefk in (1,2,4) inner join [organisationtype] o on [p].[organisationtypefk] = [o].[organisationtypeid] left join ( select [cli], [date] [ldate], [date], [ctps], [file] = 'lookup' [lookup] tl )u on u.cli = l.linenumber left join ( select [cli], [dateadded], [dateremoved], [ctps], [file] = 'history' history h )h on h.cli = l.linenumber contractdate between @startdate , @enddate select * ,[ontps] = case when ([date] not null , [contractdate] < [date]) 'no' when ([date] not null , [contractdate] > [date]) 'yes' when ([date] null , [contractdate] between [dateadded] , [dateremoved]) 'yes' when ([date] null , [contractdate] not between [dateadded] , [dateremoved]) 'no' when ([date] null , [contractdate] between [dateadded] , [dateremoved] , [name] = 'plc' or [name] = 'ltd' , ctps = 1) 'yes' when ([date] null , [contractdate] between [dateadded] , [dateremoved] , [name] = 'plc' or [name] = 'ltd' , ctps = 0) 'no' when ([date] not null , [contractdate] > [date] , [name] = 'plc' or [name] = 'ltd' , ctps = 1) 'yes' when ([date] not null , [contractdate] > [date] , [name] = 'plc' or [name] = 'ltd' , ctps = 0) 'no' end [temp].[dbo].[tps] t
your main query can simplified slightly, still giving same result (i think - without test data it's tricky tell):
select @answer = case when (@organisationtype = 'plc' or @organisationtype = 'ltd') , @contractdate < [u].[date] , [u].[ctps] = 0 'no' when (@organisationtype = 'plc' or @organisationtype = 'ltd') , @contractdate < [u].[date] , [u].[ctps] = 1 'no' when (@organisationtype != 'plc' or @organisationtype != 'ltd') , @contractdate < [u].[date] , [u].[ctps] = 0 'no' when (@organisationtype != 'plc' or @organisationtype != 'ltd') , @contractdate < [u].[date] , [u].[ctps] = 1 'no' when (@organisationtype = 'plc' or @organisationtype = 'ltd') , @contractdate > [u].[date] , [u].[ctps] = 0 'no' when (@organisationtype != 'plc' or @organisationtype != 'ltd') , @contractdate > [u].[date] , [u].[ctps] = 1 'yes' when (@organisationtype != 'plc' or @organisationtype != 'ltd') , @contractdate > [u].[date] , [u].[ctps] = 0 'yes' end [contract] c inner join [account] on [c].[accountfk] = [a].[accountid] inner join [profile] p on [p].[profileid] = [a].[profilefk] inner join [organisationtype] o on [p].[organisationtypefk] = [o].[organisationtypeid] inner join [line] l on [l].[contractfk] = [c].[contractid] inner join [lookup] u on u.cli = l.linenumber left join [deal] d on [c].[contractid] = [d].[contractfk] inner join [branch] b on [d].[branchfk] = [b].[branchid] , branchtypefk in (1,2,4) [u].[cli] = @linenumber , [u].[date] not null you're returning @answer twice @ bottom of function, , there situations in @answer may still null; may deliberate. other that, if meets business requirements , performs @ acceptable speed there's no real driver further rewrites of function.
Comments
Post a Comment