SQL Function that checks if a cdate is before or after a tdate

Question

1.00/5 (1 vote)

See more:

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)

VB

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 3:48am

Love Toys

Updated 10-Jul-13 4:00am

v2

Add a Solution

Comments

ZurdoDev 10-Jul-13 10:05am

What's the question?

Love Toys 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

ZurdoDev 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 10-Jul-13 10:17am

no help what so ever

Love Toys 10-Jul-13 10:17am

its syntax im after and how it would be wrote

ZurdoDev 10-Jul-13 10:19am

That's my point. If you right click and click New, it gives you the syntax.

[no name] 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 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"

[no name] 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 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 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2013-07-10T07:29:00

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

VB

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

VB

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

Posted 10-Jul-13 7:29am

CHill60

Comments

Love Toys 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 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?