Need help turning this into a function

Question

1.00/5 (2 votes)

See more:

This is my attempt and have been asked to turn into a function but i have looked online and can not find anything that helps me do this can anyone help please?

SQL

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]

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

Posted 11-Jul-13 2:25am

Love Toys

Updated 11-Jul-13 7:31am

CHill60

v5

Add a Solution

2 solutions

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-11T02:39:00

You're confusing me by posting new questions on the same topic! SQL Function that checks if a cdate is before or after a tdate[^]

~~MSDN Create Function Documentation[^]~~ [Edit - just spotted this one in Solution 1]

Fairly indepth treatise on functions[^]

And the one possibly of most use as you seem to want to return a lot of data would be this codeproject article Using Table-Valued Functions in SQL Server[^]

[EDIT - translating the link that shows how to do this...]
You need to write the script to create a function as follows (change the names appropriately)
I've made some assumptions about what you want returned

CREATE FUNCTION MyFunction(@StartDate DATE, @EndDate DATE)
RETURNS @MyResults TABLE (
	ContractID int NOT NULL,
 	LineNumber int NOT NULL,
	ContractDate date NULL,
	[Date] date NULL,
        o.Name varchar(100) NULL,
	[File] varchar(500) NULL,
 	DateAdded date NULL,
	DateRemoved date NULL,
	CTPS varchar(100) NULL,
	[Branch Name] varchar(100) NULL,
        YesNO varchar(3) NOT NULL
) 
AS
BEGIN
	-- remove DECLARE @StartDate DATE = '01/04/2013'
	-- remove DECLARE @EndDate DATE = '30/04/2013'

/* insert the rest of your code here up to 

SELECT *
 ,[OnTPS] = CASE
 WHEN ([Date] IS NOT NULL AND [ContractDate] < [Date]) THEN 'No'
 WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date]) THEN 'Yes' 

*/

INSERT INTO @MyResults

/* Now insert the rest of your code from 
SELECT *
 ,[OnTPS] = CASE
 WHEN ([Date] IS NOT NULL AND [ContractDate] < [Date]) THEN 'No'
 WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date]) THEN 'Yes' 

... etc 
*/

Then at the end put

RETURN;
END;

You may need to tidy up the NULL / NOT NULL declarations according to your own requirements and I'm not able to test this for syntax errors at the moment.

Run the script to create the function.

NOTE - in your sql you have SELECT ... INTO [TPS] but later you use
FROM [Temp].[dbo].[TPS] t - these are not necessarily the same table. I would suggest explicitly using Temp.dbo.TPS OR #TMP to avoid any issues there.

Finally to call the function use something like

SELECT * FROM MyFunction(Date1, Date2);

Acknowledgement to Mika Wendelius for his article Using Table-Valued Functions in SQL Server[^] from which this solution is largely copied.

bbirajdar · Answer 2 · 2013-07-11T02:30:00

Solution 1

http://msdn.microsoft.com/en-us/library/ms186755.aspx[^]

Posted 11-Jul-13 2:30am

bbirajdar

Comments

Love Toys 11-Jul-13 8:34am

lol very funny already looked at that and it means nothing to me can not understand anything MSDN has to offer totally unhelpful it should be called not msdn help

bbirajdar 11-Jul-13 8:43am

If I am able to understand your question well, then this is what you need