Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL function
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?
 
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 3:25am
Edited 11-Jul-13 8:31am
CHill6067K
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
v3
Comments
Love Toys at 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 at 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 at 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'
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
Love Toys at 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
aspnet -i at 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)

  Print Answers RSS
0 OriginalGriff 480
1 Maciej Los 290
2 Richard MacCutchan 225
3 BillWoodruff 185
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,764
1 Sergey Alexandrovich Kryukov 7,437
2 DamithSL 5,639
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web01 | 2.8.1411028.1 | Last Updated 11 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