Show date as default column value

Transact-SQL
1

Hi,

I want to show the date as default value in query result when no results returned from the database.
Tried my best to get the result but when there is no data on particular date the row not appeared with default date.
Please go through the below query and expected result attachment and do the need full.

SELECT Convert(Date, GetDate()) AS JobDate, PLH.ShiftId, PLM.RunningMachine, PLF.FirstCastingMachine, PLH.TotalOperator, PLH.TotalTrainee, PLA.MedicalLeave, PLA.OffDayLeave, PLA.MisleadLeave, PLA.AnnualLeave, PLA.OthersLeave
FROM ProductionLogHeader AS PLH
LEFT OUTER JOIN ProductionLogAbsentDetails AS PLA ON PLH.Uniid = PLA.LogId
LEFT JOIN (SELECT LogId, COUNT(MachineId) AS RunningMachine FROM ProductionLogRunningMachine GROUP BY LogId) AS PLM ON PLH.Uniid = PLM.LogId
LEFT JOIN (Select LogId, Count(MachineId) As FirstCastingMachine FROM ProductionLogFirstCastingMachine GROUP BY LogId) AS PLF ON PLH.Uniid = PLF.LogId
WHERE JobDate = Convert(Date,GETDATE()) AND ShiftId = 2
UNION ALL
SELECT Convert(Date, DATEADD(DD, DATEDIFF(DY, 0, GETDATE()), -1)) AS JobDate, PLH.ShiftId, PLM.RunningMachine, PLF.FirstCastingMachine, PLH.TotalOperator, PLH.TotalTrainee, PLA.MedicalLeave, PLA.OffDayLeave, PLA.MisleadLeave, PLA.AnnualLeave, PLA.OthersLeave
FROM ProductionLogHeader AS PLH
LEFT OUTER JOIN ProductionLogAbsentDetails AS PLA ON PLH.Uniid = PLA.LogId
LEFT JOIN (SELECT LogId, COUNT(MachineId) AS RunningMachine FROM ProductionLogRunningMachine GROUP BY LogId) AS PLM ON PLH.Uniid = PLM.LogId
LEFT JOIN (Select LogId, Count(MachineId) As FirstCastingMachine FROM ProductionLogFirstCastingMachine GROUP BY LogId) AS PLF ON PLH.Uniid = PLF.LogId
WHERE JobDate = DATEADD(DD, DATEDIFF(DY, 0, GETDATE()), -1) AND ShiftId = 3

ProductionLog.jpg935×90 26.6 KB

2

Maybe this will get you closer to a solution:

SELECT T.JobDate
      ,T.ShiftId
      ,PLM.RunningMachine
      ,PLF.FirstCastingMachine
      ,PLH.TotalOperator
      ,PLH.TotalTrainee
      ,PLA.MedicalLeave
      ,PLA.OffDayLeave
      ,PLA.MisleadLeave
      ,PLA.AnnualLeave
      ,PLA.OthersLeave
  FROM (          SELECT CAST(CURRENT_TIMESTAMP AS Date) AS JobDate
                        ,2 AS ShiftId
        UNION ALL SELECT CAST(DATEADD(DAY, -1, CURRENT_TIMESTAMP) AS Date) AS JobDate
                        ,3 AS ShiftId
       ) AS T
       INNER JOIN ProductionLogHeader AS PLH
               ON PLH.JobDate=T.JobDate
              AND PLH.ShiftId=T.ShiftId
       LEFT OUTER JOIN ProductionLogAbsentDetails AS PLA
                    ON PLA.LogId = PLH.Uniid
       LEFT OUTER JOIN (SELECT LogId
                              ,COUNT(MachineId) AS RunningMachine
                          FROM ProductionLogRunningMachine
                         GROUP BY LogId
                       ) AS PLM
                    ON PLM.LogId = PLH.Uniid
       LEFT OUTER JOIN (Select LogId
                              ,Count(MachineId) As FirstCastingMachine
                          FROM ProductionLogFirstCastingMachine
                         GROUP BY LogId
                       ) AS PLF
                    ON PLF.LogId = PLH.Uniid
;