Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL function
I need a fucntion that takes in three variables and checks three tables
 
@LNumber varchar(max),
@OType varchar(max),
@CDate datetime
 
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)
 
If @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 1
if @CDate  < T1.Date
return "No"
 
if @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 1
if@ CDate  > T1.Date
return "Yes"
------------------------------------------------------------------------
If @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate  < T1.Date
return "No"
 
if @LNumber = T1.Number
if @OType = 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate > T1.Date
return "No"
------------------------------------------------------------------------------
If @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 1
if @CDate < T1.Date
return "No"
 
if @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 1
If @CDate > T1.Date
return "Yes"
-------------------------------------------------
If @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate < T1.Date
return "No"
 
if @LNumber = T1.Number
if @OType != 'LTD' or 'PLC'
if T1.CTPS = 0
if @CDate > T1.Date
return "Yes"
--------------------------------------------------
If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 0
If @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
 
If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 0
If @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
 
------------------------------------------------------
If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 1
If @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"
 
If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 1
If @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return 
 
------------------------------------------------------
If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"
 
if @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
--------------------------------------------------
If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 0
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"
 
if @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 0
if @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
--------------------------------------------------
Posted 10-Jul-13 4:48am
Edited 10-Jul-13 5:00am
v2
Comments
ryanb31 at 10-Jul-13 10:05am
   
What's the question?
Love Toys at 10-Jul-13 10:11am
   
I am very new to sql functions and the help i have found on line is no help so wanted someone to help me build a fucnction that takes in the above three variables and return the answer when each of the different criteria is met i.e: If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 1
If @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
ryanb31 at 10-Jul-13 10:14am
   
In SQL management studio, right click functions, and choose New Multi-statement table valued function. Or create a Stored Procedure the same way. It will create the template for you.
Love Toys at 10-Jul-13 10:17am
   
no help what so ever
Love Toys at 10-Jul-13 10:17am
   
its syntax im after and how it would be wrote
ryanb31 at 10-Jul-13 10:19am
   
That's my point. If you right click and click New, it gives you the syntax.
ThePhantomUpvoter at 10-Jul-13 10:07am
   
I have no idea what this is. But I know what it is not. It is not a question or description of a problem.
Love Toys at 10-Jul-13 10:12am
   
I am very new to sql functions and the help i have found on line is no help so wanted someone to help me build a fucnction that takes in the above three variables and return the answer when each of the different criteria is met i.e: If @LNumber = T2.Number
If @OType = 'LTD' or 'PLC'
If T2.CTPS = 1
If @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
ThePhantomUpvoter at 10-Jul-13 10:29am
   
This is in no way "help me build" anything. This is "gimme code". If you want help then show us what you have tried and explain where you are stuck.
Love Toys at 10-Jul-13 10:43am
   
if i give you what i have done so far i would be giving out table names and columns held within that table not good and boss not liking that so cant and would take me far too long to change all the table names and column names here is the case statement i have built so far but boss suggested an if else statement.
 
WHEN ((((((@LNumber = [T1].[Number]) AND @OrganisationType = 'PLC') OR @OrganisationType = 'LTD') AND [T1].[Date] IS NOT NULL) AND @ContractDate < [T1].[Date]) AND [T1].[CTPS] = 0) THEN 'No'
WHEN ((((((@LNumber = [T1].[Number]) AND @OrganisationType = 'PLC') OR @OrganisationType = 'LTD') AND [T1].[Date] IS NOT NULL) AND @ContractDate < [T1].[Date]) AND [T1].[CTPS] = 1) THEN 'No'
WHEN ((((((@LNumber = [T1].[Number]) AND @OrganisationType != 'PLC') OR @OrganisationType != 'LTD') AND [T1].[Date] IS NOT NULL) AND @ContractDate < [T1].[Date]) AND [T1].[CTPS] = 0) THEN 'No'
WHEN ((((((@LNumber = [T1].[Number]) AND @OrganisationType != 'PLC') OR @OrganisationType != 'LTD') AND [T1].[Date] IS NOT NULL) AND @ContractDate < [T1].[Date]) AND [T1].[CTPS] = 1) THEN 'No'
WHEN ((((((@LNumber = [T1].[Number]) AND @OrganisationType = 'PLC') OR @OrganisationType = 'LTD') AND [T1].[Date] IS NOT NULL) AND @ContractDate > [T1].[Date]) AND [T1].[CTPS] = 0) THEN 'No'
WHEN ((((((@LNumber = [T1].[Number]) AND @OrganisationType != 'PLC') OR @OrganisationType != 'LTD') AND [T1].[Date] IS NOT NULL) AND @ContractDate > [T1].[Date]) AND [T1].[CTPS] = 1) THEN 'Yes'
WHEN ((((((@LNumber = [T1].[Number]) AND @OrganisationType != 'PLC') OR @OrganisationType != 'LTD') AND [T1].[Date] IS NOT NULL) AND @ContractDate > [T1].[Date]) AND [T1].[CTPS] = 0) THEN 'Yes'

WHEN (@LNumber = [T2].[Number] AND @OrganisationType = 'PLC' OR @OrganisationType = 'LTD' AND @ContractDate BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 0) THEN 'No'
WHEN (@LNumber = [T2].[Number] AND @OrganisationType = 'PLC' OR @OrganisationType = 'LTD' AND @ContractDate BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 1) THEN 'Yes'
WHEN (@LNumber = [T2].[Number] AND @OrganisationType != 'PLC' OR @OrganisationType != 'LTD' AND @ContractDate BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 1) THEN 'Yes'
WHEN (@LNumber = [T2].[Number] AND @OrganisationType != 'PLC' OR @OrganisationType != 'LTD' AND @ContractDate BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 0) THEN 'Yes'
WHEN (@LNumber = [T2].[Number] AND @OrganisationType = 'PLC' OR @OrganisationType = 'LTD' AND @ContractDate NOT BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 1) THEN 'No'
WHEN (@LNumber = [T2].[Number] AND @OrganisationType = 'PLC' OR @OrganisationType = 'LTD' AND @ContractDate NOT BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 0) THEN 'No'
WHEN (@LNumber = [T2].[Number] AND @OrganisationType != 'PLC' OR @OrganisationType != 'LTD' AND @ContractDate NOT BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 1) THEN 'No'
WHEN (@LNumber = [T2].[Number] AND @OrganisationType != 'PLC' OR @OrganisationType != 'LTD' AND @ContractDate NOT BETWEEN [DateAdded] AND [DateRemoved] AND [T2].[CTPS] = 0) THEN 'No'
END
Love Toys at 10-Jul-13 10:45am
   
it keeps returning the answer from the very last staement, and can not get it to work any help would be very much Appreciated

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Any code with that many open and close brackets is going to be a nightmare to untangle!
First thing you need to do is simplify your conditions ...
For example if @OType != 'LTD' or 'PLC' could be better expressed as
if @OType NOT IN ('LTD','PLC')
Next @CDate can only be BETWEEN T2.DateAdded AND T2.DateRemoved or not - there are no other options. So you can simplify
If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes"
 
if @LNumber = T2.Number
if @OType NOT IN ('LTD','PLC')
if T2.CTPS = 1
if @CDate NOT BETWEEN T2.DateAdded AND T2.DateRemoved
return "No"
To become
If @LNumber = T2.Number
if @OType != 'LTD' or 'PLC'
if T2.CTPS = 1
if @CDate BETWEEN T2.DateAdded AND T2.DateRemoved
return "Yes" else return "No"
Finally, consider building a "Truth Table" (google it if you're not sure) to look at all of your conditions - sometimes you will spot patterns that make the whole thing simpler and easier to read. You may also spot the conditions you haven't catered for ... such as what to do if @CDate == T1.Date and the fact you have a return which returns nothing at all.
 
Give that a go and hopefully you'll be able to solve the problem yourself
  Permalink  
Comments
Love Toys at 11-Jul-13 8:11am
   
THIS IS MY ORIGINAL QUERY I BUILT BUT NEED IT CHANGING INTO A 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
INTO [TPS]
FROM [Contract] c
INNER JOIN [Account] a
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
FROM [Branch] b
)b
ON [d].[BranchFK] = [b].[BranchID]
AND BranchTypeFK IN (1,2,4)
INNER JOIN [OrganisationType] o
ON [p].[OrganisationTypeFK] = [o].[OrganisationTypeID]
LEFT JOIN (
SELECT [CLI], [Date] [lDate], [Date], [CTPS], [File] = 'Lookup'
FROM [Lookup] tl
)u
ON u.CLI = l.LineNumber
LEFT JOIN (
SELECT [CLI], [DateAdded], [DateRemoved], [CTPS], [File] = 'History'
FROM History h
)h
ON h.CLI = l.LineNumber
WHERE ContractDate BETWEEN @StartDate AND @EndDate
 
SELECT *
,[OnTPS] = CASE
WHEN ([Date] IS NOT NULL AND [ContractDate] < [Date]) THEN 'No'
WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date]) THEN 'Yes'

WHEN ([Date] IS NULL AND [ContractDate] BETWEEN [DateAdded] AND [DateRemoved]) THEN 'Yes'
WHEN ([Date] IS NULL AND [ContractDate] NOT BETWEEN [DateAdded] AND [DateRemoved]) THEN 'No'

WHEN ([Date] IS NULL AND [ContractDate] BETWEEN [DateAdded] AND [DateRemoved] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 1) THEN 'Yes'
WHEN ([Date] IS NULL AND [ContractDate] BETWEEN [DateAdded] AND [DateRemoved] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 0) THEN 'No'
 
WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 1) THEN 'Yes'
WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 0) THEN 'No'
END
FROM [Temp].[dbo].[TPS] t
CHill60 at 11-Jul-13 8:28am
   
So are you saying you are now getting the results you expect in all instances but want to turn this into a function ready to implement for real?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 295
1 OriginalGriff 274
2 Shweta N Mishra 216
3 PIEBALDconsult 184
4 CPallini 155
0 OriginalGriff 7,630
1 Sergey Alexandrovich Kryukov 7,022
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,525


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 10 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100