Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
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
Updated 11-Jul-13 7:31am
v5

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.
 
Share this answer
 
v3
Comments
Love Toys 11-Jul-13 8:50am    
it’s a new topic not same as last question? and i need help turning this code i have produced into a function don’t need pointing at some description of how to do it as none of it seems relevant to what i am looking to do and also most of it presumes you are an SQL expert when I’m not I’m very new to this language have C++ knowledge and other programming languages but SQL i cannot seem to get the logic in my head right
CHill60 11-Jul-13 13:28pm    
The last link should have been enough to demonstrate how to create a function and return a table of results (implied by your select * etc). If you only want to return the Yes/No bit then the MSDN example is simpler. I've updated my solution with details of what to do.
CHill60 11-Jul-13 13:35pm    
Now that I've formatted your code I've spotted another problem. You have a mixture of AND and OR within the same brackets so your results will wrong. For example WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 1) THEN 'Yes' should either be WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date] AND ( [Name] = 'PLC' OR [Name] = 'LTD' ) AND CTPS = 1) THEN 'Yes' [spot the extra brackets () ]or as I suggested in response to your previous question ... WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date] AND [Name] IN ('PLC','LTD') AND CTPS = 1) THEN 'Yes'
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900