July 20, 2009 at 8:26 pm
Can anyone please help me out with below requirement
tbl Name
DefectJobKey EquipmentKey JobNo DefectStatus TotalNoOfDefect
DefectLoggedDate DefectFinishDate
Query need to populate the status and no of day defect logged in specified time, note it has to be incremental but need to count only the start and finish date to get no of days worked on defects inclusive status change between the time period when defect is rectifying.
Logic
DefectLogged DefectFinishDate
12/06/2009 8/07/2009
Day1 2 3 4 5 6
Her is the logic when we select any given date it has to give net defect status ,defect completed, defect work in progress and defect not done. Below is the query which we wrote to identify the date how it will create entry in table for each day. Now the issue is get only net defect logged that particular time it shouldn't count all the entries.
SELECT D.EquipmentKey
,P.DimPeriodKey
,'On Work Order' AS DefectStatus
FROM dbo.tblRefDefectJob D
CROSS JOIN dbo.tblDimPeriod P
WHERE
D.DefectLoggedDate <= P.DimPeriodKey
AND
D.DefectFinishDate > P.DimPeriodKey
AND
DefectStatus IN ('Completed', 'Not Done')
UNION ALL
-- lets get the completed day now
SELECT D.EquipmentKey
,DefectFinishDate
,DefectStatus
FROM dbo.tblRefDefectJob D
WHERE
DefectStatus IN ('Completed', 'Not Done')
UNION ALL
SELECT D.EquipmentKey
,P.DimPeriodKey
,'On Work Order' AS DefectStatus
FROM dbo.tblRefDefectJob D
CROSS JOIN dbo.tblDimPeriod P
WHERE
D.DefectLoggedDate <= P.DimPeriodKey
AND
GETDATE() -1 > P.DimPeriodKey
AND
DefectStatus IN ('On Work Order')
July 21, 2009 at 12:12 am
can u please provide some sample data and explain
July 21, 2009 at 1:10 am
Hi there,
Please find attached copy of sample data and requirment.
Thanks,
Dreamslogic
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply