putting another column

[haymen.roth.3]

i have written this query to show total working time and overtime of an employee on particular date (if he has came) otherwise it doesn't show record for a person if his INTIME and TIMEOUT are empty but i don't want this now, i want if for a particular date person's INtime and OutTime are empty then put 00:00 in his intime, outtime, totalworkingtime, overtime.

e.g.

EmplID  EmplName ShiftID intime Outtime totalworking overtime  dateVisited
`0000001 John     S001    00:00  00:00   00:00:       00:00     2013-12-01`

Query:

with times as (

 SELECT    t1.EmplID
            , t3.EmplName
            , min(t1.RecTime) AS InTime
            , max(t2.RecTime) AS [TimeOut]
            , t4.ShiftId as ShiftID
            , t4.StAtdTime as ShStartTime
            , t4.EndAtdTime as ShEndTime
            , cast(min(t1.RecTime) as datetime) AS InTimeSub
            , cast(max(t2.RecTime) as datetime) AS TimeOutSub
            , t1.RecDate AS [DateVisited]
    FROM  AtdRecord t1 
    INNER JOIN 
          AtdRecord t2 
    ON    t1.EmplID = t2.EmplID 
    AND   t1.RecDate = t2.RecDate
    AND   t1.RecTime < t2.RecTime
    inner join 
          HrEmployee t3 
    ON    t3.EmplID = t1.EmplID 
    inner join AtdShiftSect t4
    ON t3.ShiftId = t4.ShiftId
    group by 
              t1.EmplID
            , t3.EmplName
            , t1.RecDate
            , t4.ShiftId 
            , t4.StAtdTime 
            , t4.EndAtdTime
    )
    SELECT 
     EmplID
    ,EmplName
    ,ShiftId As ShiftID
    ,InTime
    ,[TimeOut]
    ,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) TotalWorkingTime
    ,[DateVisited]
    ,CASE WHEN [InTime] IS NOT NULL AND [TimeOut] IS NOT NULL THEN
         CONVERT(char(5),CASE WHEN  CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S002' Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),CAST([TimeOutSub] AS DATETIME)),0), 108),5) 
                              WHEN  CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S001' Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),  CAST([TimeOutSub] AS DATETIME)),0), 108),5) 
          ELSE '00:00' END, 108) 
     ELSE 'ABSENT' END AS OverTime
     FROM times  order by EmplID, ShiftID, DateVisited

[darkagn]

You can use ISNULL or COALESCE to get the first non-null parameter as the result. The ISNULL function takes only two parameters and returns the first non-null as the result, or null if both are null. COALESCE takes a list of parameters and returns the first non-null result, or null if all parameters are null. MS recommend that ISNULL be used when there is only two but COALESCE when there are more than two rather than nested ISNULL statements for performance reasons.

OK, that said, here is how I would re-write your SQL statement to manage what you need.

WITH Times AS
(
    -- leave this part the same
)
SELECT
     EmplID
    ,EmplName
    ,ShiftId As ShiftID
    ,convert(char(5),cast(ISNULL(InTime, '00:00') as time), 108) AS InTime
    , -- and so on

So here for the InTime, we are returning InTime when it is not null, or we are returning '00:00' when it is, then casting it to a Time field and then converting it to a char field. I'll leave you to work out the others.

Hope this helps :)

[haymen.roth.3]

no no, i want if e.g. person doesn't come on particular day (e.g. sunday) then there would be obviously no InTime and EndTime so in that case it should his empid, empName etc and should put 00:00 in time columns like overtime, intime, workingtime etc but problem is that date is only entered when Intime is available but how Intime can be entered when it's holiday :(

[haymen.roth.3]

any help please ?

[Rahul47]

Few questions for you:
1) How exactly are you entering data ? Through a GUI (VB.NET or something like that ?)

2) You want to extract data ( SELECT ) ? OR you want to enter data (INSERT) ?

3) Are there any other tables RELATED in this query ? If so provide information about that too.

[haymen.roth.3]

1. Basically from biometric device passes through asp.net MVC code. Calling a store procedure only.
2. Extracting data only
3. yes, i mentioned those tables in query.

[haymen.roth.3]

hello ?