August 14, 2015 at 2:54 pm
Hi there,
Could you please help with some improvements from the below function? Basically, I'm using the function to get the dups from source table 'MyTable' but I'm not positive with CASE statement logic.
CREATE FUNCTION [dbo].[fn_GetDupsFeeds](
@ContractNbr int,
@DOCId int,
@FeedTime int,
@ServiceTypeId varchar(10),
@FlightStart smalldatetime,
@FlightEnd smalldatetime = null,
@IsMonday int,
@IsTuesday int,
@IsWednesday int,
@IsThursday int,
@IsFriday int,
@IsSaturday int,
@IsSunday int
)
RETURNS @DupFeeds TABLE (
DOCId int NOT NULL,
SimulcastGrpId int NULL,
FeedTime int NOT NULL,
ServiceTypeId varchar(10) NULL,
ContractNbr int NOT NULL,
isMonday int,
isTuesday int,
isWednesday int,
isThursday int,
isFriday int,
isSaturday int,
isSunday int
) AS
BEGIN
DECLARE @Today smalldatetime
SELECT @Today = CURRENT_TIMESTAMP
INSERT @DupFeeds(
DOCId
,SimulcastGrpId
,FeedTime
,ServiceTypeId
,ContractNbr
,isMonday
,isTuesday
,isWednesday
,isThursday
,isFriday
,isSaturday
,isSunday
)
SELECT DOCId
,SimulcastGrpId
,FeedTime
,ServiceTypeId
,ContractNbr
,isMonday = CASE WHEN 1 = @isMonday AND @isMonday = isMonday THEN 1 ELSE 0 END
,isTuesday = CASE WHEN 1 = @isTuesday AND @isTuesday = isTuesday THEN 1 ELSE 0 END
,isWednesday = CASE WHEN 1 = @isWednesday AND @isWednesday = isWednesday THEN 1 ELSE 0 END
,isThursday = CASE WHEN 1 = @isThursday AND @isThursday = isThursday THEN 1 ELSE 0 END
,isFriday = CASE WHEN 1 = @isFriday AND @isFriday = isFriday THEN 1 ELSE 0 END
,isSaturday = CASE WHEN 1 = @isSaturday AND @isSaturday = isSaturday THEN 1 ELSE 0 END
,isSunday = CASE WHEN 1 = @isSunday AND @isSunday = isSunday THEN 1 ELSE 0 END
FROM MyTable
WHERE FeedTime = @FeedTime
AND @ServiceTypeId = ServiceTypeId
AND @ContractNbr = ContractNbr
AND DOCId != @DOCId
AND ((1=@isMonday AND IsMonday = isnull(@isMonday, 0))
OR (1=@isTuesday AND IsTuesday = isnull(@isTuesday, 0))
OR (1=@isWednesday AND IsWednesday = isnull(@isWednesday, 0))
OR (1=@isThursday AND IsThursday = isnull(@isThursday, 0))
OR (1=@isFriday AND IsFriday = isnull(@isFriday, 0))
OR (1=@isSaturday AND IsSaturday = isnull(@isSaturday, 0))
OR (1=@isSunday AND IsSunday = isnull(@isSunday, 0)))
RETURN
END
Data in source table 'MyTable':
DOCId DOCRevNum AffiliateId ContractNbr ServiceTypeId FeedTime IsMonday IsTuesday IsWednesday IsThursday IsFriday IsSaturday IsSunday SimulcastGrpId
------- --------- ----------- ----------- ------------- ----------- -------- --------- ----------- ---------- -------- ---------- -------- --------------
1 1 3 4 MTRSRC 630 1 1 1 1 1 0 0 NULL
2 2 3 4 MTRSRC 645 1 1 1 1 1 0 0 NULL
3 3 3 4 MTRSRC 730 1 1 1 1 1 0 0 NULL
Thanks in advance!
August 15, 2015 at 9:06 am
1) We need create table script and sample data to help with a query.
2) Why use integers for what looks to be a Boolean (isDay)? VERY inefficient data type choice on the table (assuming those fields are ints).
3) Your use of Multi-Statement Table Valued Function here scares me. If you join to this you are totally screwed - BAD things happen with Scalar and mTVFs!!
4) Without some form of grouping I don't see how a set-based query can identify duplicate records.
5) You declare @today, give it a value, then never use it. Inefficient coding.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 17, 2015 at 6:38 am
So you're trying to use the function to define the function? For example, the isFriday is defined by checking the value of isFriday. Would love to see a data set that shows what you are trying to get working.
August 17, 2015 at 9:51 am
Edit: An inline table-valued function is much more efficient, and your code is easily converted to one, as below:
CREATE FUNCTION [dbo].[fn_GetDupsFeeds](
@ContractNbr int,
@DOCId int,
@FeedTime int,
@ServiceTypeId varchar(10),
@FlightStart smalldatetime,
@FlightEnd smalldatetime = null,
@IsMonday int,
@IsTuesday int,
@IsWednesday int,
@IsThursday int,
@IsFriday int,
@IsSaturday int,
@IsSunday int
)
RETURNS TABLE AS
RETURN (
SELECT DOCId
,SimulcastGrpId
,FeedTime
,ServiceTypeId
,ContractNbr
,isMonday = CASE WHEN 1 = @isMonday AND @isMonday = isMonday THEN 1 ELSE 0 END
,isTuesday = CASE WHEN 1 = @isTuesday AND @isTuesday = isTuesday THEN 1 ELSE 0 END
,isWednesday = CASE WHEN 1 = @isWednesday AND @isWednesday = isWednesday THEN 1 ELSE 0 END
,isThursday = CASE WHEN 1 = @isThursday AND @isThursday = isThursday THEN 1 ELSE 0 END
,isFriday = CASE WHEN 1 = @isFriday AND @isFriday = isFriday THEN 1 ELSE 0 END
,isSaturday = CASE WHEN 1 = @isSaturday AND @isSaturday = isSaturday THEN 1 ELSE 0 END
,isSunday = CASE WHEN 1 = @isSunday AND @isSunday = isSunday THEN 1 ELSE 0 END
FROM MyTable
WHERE FeedTime = @FeedTime
AND @ServiceTypeId = ServiceTypeId
AND @ContractNbr = ContractNbr
AND DOCId != @DOCId
AND ((1=@isMonday AND IsMonday = isnull(@isMonday, 0))
OR (1=@isTuesday AND IsTuesday = isnull(@isTuesday, 0))
OR (1=@isWednesday AND IsWednesday = isnull(@isWednesday, 0))
OR (1=@isThursday AND IsThursday = isnull(@isThursday, 0))
OR (1=@isFriday AND IsFriday = isnull(@isFriday, 0))
OR (1=@isSaturday AND IsSaturday = isnull(@isSaturday, 0))
OR (1=@isSunday AND IsSunday = isnull(@isSunday, 0)))
)
GO --end of function
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 17, 2015 at 6:24 pm
Thank you, I will follow your advices.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply