Query Request

  • Can anyone please help me out with below requirement

    tbl Name

    DefectJobKeyEquipmentKeyJobNoDefectStatus TotalNoOfDefect

    DefectLoggedDateDefectFinishDate

    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

    Day123456

    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')

  • can u please provide some sample data and explain

  • 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