August 30, 2010 at 2:15 pm
I have an issue that is driving me crazy, I would assume there is some way to do this but I am having an extremely hard time trying to find out the answer. So the issue is this, for an environmental protection database I have two tables (highly simplified but good enough for demonstrative purposes) One table is time cards, which has columns [TimeCardID],[Contaminant],[AreaDate],[Reading],[ProtectionTypeID]. The other table is a list of protection factors that can be applied to shield a employee from specific contaminants, which are only valid for a specific date range, else no protection is assumed to be used. This table has the columns [ProtectionTypeID],[Contaminant],[StartDate],[EndDate],[Factor].
It can be assumed that multiple records for a specific contaminant and protection type can exist in the table, but the start and end date for a protection factor for a specific contaminant can not over lap. That is you can only have 1 protection factor at any given date or none at all.
I need to find a way to multiply the reading value of the time card by the appropriate factor if it exists, or multiply it by 1 if no usible factor can be found. I can't seem to do this without duplicating records on the Timecards table. Any suggestions?
SAMPLE DATA:
DECLARE @TimeCards TABLE
(
ProtectionEquipTypeID INT,
TimeCardID INT,
Contaminant nvarchar(20),
AreaDate DateTime,
Reading float
)
DECLARE @ProtectionFactors TABLE
(
ProtectionEquipTypeID INT,
Contaminant nvarchar(20),
StartDate DateTime,
EndDate DateTime,
Factor float
)
INSERT INTO @TimeCards (TimeCardID,ProtectionEquipTypeID,Contaminant,AreaDate,Reading)
SELECT 100,1,'Alpha','2010-01-15',0.026 UNION ALL
SELECT 100,1,'Gamma','2010-01-15',0.026 UNION ALL
SELECT 101,2,'Alpha','2010-02-15',0.026 UNION ALL
SELECT 101,2,'Gamma','2010-02-15',0.026 UNION ALL
SELECT 102,3,'Alpha','2010-03-15',0.026 UNION ALL
SELECT 102,3,'Gamma','2010-03-15',0.026
INSERT INTO @ProtectionFactors (ProtectionEquipTypeID,Contaminant,StartDate,EndDate,Factor)
SELECT 2,'Alpha','2010-01-01','2011-01-01',10000UNION ALL
SELECT 2,'Alpha','2009-01-01','2009-12-31',10000UNION ALL
SELECT 2,'Gamma','2010-01-01','2010-02-01',100000000000
-- ############ My attempt at a solution #################
/*
Im not gonna lie, i've got nothing, I figure somehow I should use a sub query in the select combined
with a bit of case logic and a TOP 1 condition so it only returns one value per timecard record but
I can't get this to work right below is my other attempt with joining that didnt work
*/
SELECT
TC.ProtectionEquipTypeID,
TC.TimeCardID,
TC.Contaminant,
TC.AreaDate,
TC.Reading,
OriginalFactorVal = PF.Factor,
FactorUSed = (CASE WHEN PF.Factor IS NULL then 1 else PF.Factor END),
FactoredResult = TC.Reading * (CASE WHEN PF.Factor IS NULL then 1 else PF.Factor END)
FROM
@Timecards TC LEFT JOIN @ProtectionFactors PF
ON TC.ProtectionEquipTypeID = PF.ProtectionEquipTypeID
AND TC.Contaminant = PF.Contaminant
WHERE
(CASE WHEN (TC.AreaDate BETWEEN PF.StartDate AND PF.EndDate) OR (PF.Contaminant IS NULL) then 1 else 0 end) = 1
The only issue here is that if a factor exists for a timecard but has no date range that will work, then that record is no included at all.. So we can see that time card #101 for gamma has no been included, because its protection factor for that date is not in the table.
August 30, 2010 at 2:25 pm
So I guess its easy to see if a protection factor doesnt exist at all, you would just get back a null. The real problem is when multiple protection factors exist and your time card date doesnt fall in either protection factor ranges, how do you return a single value?
Perhaps I should be using a SELECT Distinct then and let the case of assigning a 1 happen twice essentiall duplicating the record, which would then later be removed by the SELECT Distinct?
So I wouldn't use my CASE statement in the where clause at all, just in the assignment of a protection factor to be used. It would be assumed that time cards can't be duplicated here anyway.
August 30, 2010 at 2:31 pm
Excellent start.
One question: Based on the sample data provided, what should the result set look like?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 2:41 pm
Remove your where clause completely - this is filtering the result set.
Make your from clause:
FROM
@Timecards TC LEFT JOIN @ProtectionFactors PF
ON TC.ProtectionEquipTypeID = PF.ProtectionEquipTypeID
AND TC.Contaminant = PF.Contaminant
AND TC.AreaDate BETWEEN PF.StartDate and PF.EndDate
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 5:13 pm
WayneS (8/30/2010)
Remove your where clause completely - this is filtering the result set.
I figured it out! Thank god, sorry I burdened you with this, sometimes I just need to think out loud. I did it with a subquery. I was unaware you could return nulls from a sub query as I rarely used them.
Doing it as you state would cause a record to be thrown away if it didnt have an associated protection factor start and end date associated with it. I guess I explained it bad, your solution makes sense in what I gave, or maybe it does work, I'm pretty tired today ha.
The result set should be:
100 - Gamma - 0.026
101 - Gamma - 260
102 - Gamma - 0.026
100 - Alpha - 0.026
101 - Alpha - 260
102 - Alpha - 0.026
This is what I have used to produce it, where the null factors would be converted to 1's:
DECLARE @Timecards TABLE
(
ProtectionEquipTypeID INT,
TimeCardID INT,
Contaminant nvarchar(20),
AreaDate DateTime,
Reading float
)
DECLARE @ProtectionFactors TABLE
(
ProtectionEquipTypeID INT,
Contaminant nvarchar(20),
StartDate DateTime,
EndDate DateTime,
Factor float
)
INSERT INTO @Timecards (TimeCardID,ProtectionEquipTypeID,Contaminant,AreaDate,Reading)
SELECT 100,1,'Alpha','2010-01-15',0.026 UNION ALL
SELECT 100,1,'Gamma','2010-01-15',0.026 UNION ALL
SELECT 101,2,'Alpha','2010-02-15',0.026 UNION ALL
SELECT 101,2,'Gamma','2010-02-15',0.026 UNION ALL
SELECT 102,3,'Alpha','2010-03-15',0.026 UNION ALL
SELECT 102,3,'Gamma','2010-03-15',0.026
INSERT INTO @ProtectionFactors (ProtectionEquipTypeID,Contaminant,StartDate,EndDate,Factor)
SELECT 2,'Alpha','2010-01-01','2011-01-01',10000UNION ALL
SELECT 2,'Alpha','2009-01-01','2009-12-31',10000UNION ALL
SELECT 2,'Gamma','2010-01-01','2010-02-01',100000000000
SELECT
TC.ProtectionEquipTypeID,
TC.TimeCardID,
TC.Contaminant,
TC.AreaDate,
TC.Reading,
Factor = (SELECT TOP 1 Factor FROM @ProtectionFactors PF WHERE TC.ProtectionEquipTypeID = PF.ProtectionEquipTypeID AND TC.AreaDate BETWEEN PF.StartDate AND PF.EndDate )
FROM
@Timecards TC
August 30, 2010 at 5:53 pm
loki1049 (8/30/2010)
Doing it as you state would cause a record to be thrown away if it didnt have an associated protection factor start and end date associated with it.
No it wouldn't. You're using a LEFT OUTER JOIN - if a record doesn't exist for the matching conditions specified in the @ProtectionFactors table, then the @ProtectionFactors table returns a NULL for all of the fields for the record in the @TimeCards table.
But, putting the date match in the WHERE clause was doing exactly this.
The result set should be:
100 - Gamma - 0.026
101 - Gamma - 260
102 - Gamma - 0.026
100 - Alpha - 0.026
101 - Alpha - 260
102 - Alpha - 0.026
This is exactly what I got from your sample data - well, there were a few extra columns.
1100Alpha2010-01-15 00:00:00.0000.026NULL10.026
1100Gamma2010-01-15 00:00:00.0000.026NULL10.026
2101Alpha2010-02-15 00:00:00.0000.0261000010000260
2101Gamma2010-02-15 00:00:00.0000.026NULL10.026
3102Alpha2010-03-15 00:00:00.0000.026NULL10.026
3102Gamma2010-03-15 00:00:00.0000.026NULL10.026
FYI, the final select statement that I used is:
SELECT
TC.ProtectionEquipTypeID,
TC.TimeCardID,
TC.Contaminant,
TC.AreaDate,
TC.Reading,
OriginalFactorVal = PF.Factor,
FactorUSed = (CASE WHEN PF.Factor IS NULL then 1 else PF.Factor END),
FactoredResult = TC.Reading * (CASE WHEN PF.Factor IS NULL then 1 else PF.Factor END)
FROM
@Timecards TC LEFT JOIN @ProtectionFactors PF
ON TC.ProtectionEquipTypeID = PF.ProtectionEquipTypeID
AND TC.Contaminant = PF.Contaminant
AND TC.AreaDate BETWEEN PF.StartDate AND PF.EndDate
FYI, this will run better than the correlated subquery that you are now using.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply