CODEWITHSUNDEEP
sqlsql-server
Ayman
Ayman

Reputation: 75

Select Case in SQL Statement

I have the below SQL query that returns the following output for employee time ATTENDANCE.

Note when InOut = '0' it means in, and if InOut = '1' it means out.

Query:

SELECT
    DATEPART(mi, LogTime) AS [InMin],
    OutletName as [InOutletName]
FROM 
    [dbo].[AccessLog]
INNER JOIN 
    dbo.Outlets ON dbo.Outlets.OutletCode = dbo.AccessLog.TerminalID
WHERE 
    DATEPART(HOUR, LogTime) = '15'
    AND InOut = '0'
    AND CAST(LogDate AS date) = '2016-12-01'

Output:

InMin   InOutletName
--------------------
47      GJ-SH1

The output I am looking for in to get OutMin, OutOutletName and this can be applied when InOut = '1'

Desired output:

InMin   InOutletName OutMin OutOutletName
-----------------------------------------
47      GJ-SH1       10     GJ-SH1

Upvotes: 0

Views: 117

Answers (3)

Maverick Sachin
Maverick Sachin

Reputation: 883

I didn't get a chance to try it out in my SSMS, but required query should be something like this -

SELECT
    Case When InOut = '0' Then DATEPART(mi, LogTime) End AS [InMin],
    Case When InOut = '0' Then OutletName End as [InOutletName],
    Case When InOut = '1' Then DATEPART(mi, LogTime) End AS [OutMin],
    Case When InOut = '1' Then OutletName End as [OutOutletName]
FROM 
    [dbo].[AccessLog]
INNER JOIN 
    dbo.Outlets ON dbo.Outlets.OutletCode = dbo.AccessLog.TerminalID
WHERE 
    DATEPART(HOUR, LogTime) = '15'
    AND CAST(LogDate AS date) = '2016-12-01';

Upvotes: 1

alaa_sayegh
alaa_sayegh

Reputation: 2211

I just wrote it on the fly, not sure about the result but you can try it. It may help:

    ;with InQuery as (SELECT
    DATEPART(mi, LogTime) AS [InMin],
    OutletName as [InOutletName]
    FROM [dbo].[AccessLog]
    INNER JOIN dbo.Outlets
    ON dbo.Outlets.OutletCode = dbo.AccessLog.TerminalID
    WHERE DATEPART(HOUR, LogTime) = '15'
    AND InOut = '0'
    AND CAST(LogDate AS date) = '2016-12-01'),
    OutQuery As (SELECT
    DATEPART(mi, LogTime) AS [OutMin],
    OutletName as [OutOutletName]
    FROM [dbo].[AccessLog]
    INNER JOIN dbo.Outlets
    ON dbo.Outlets.OutletCode = dbo.AccessLog.TerminalID
    WHERE DATEPART(HOUR, LogTime) = '15'
    AND InOut = '1'
    AND CAST(LogDate AS date) = '2016-12-01')

    select iq.*, oq.* from InQuery iq, OutQuery oq

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

A simple solution would be to join the AccessLog table twice:

SELECT
        DATEPART(mi, ali.LogTime) AS [InMin],
        DATEPART(mi, alo.LogTime) AS [OutMin],
        OutletName as [InOutletName]
FROM dbo.Outlets
INNER JOIN [dbo].[AccessLog] ali
ON dbo.Outlets.OutletCode = ali.TerminalID
LEFT JOIN [dbo].[AccessLog] alo
ON dbo.Outlets.OutletCode = al0.TerminalID AND CAST(ali.LogDate AS date) = CAST(alo.LogDate AS date) AND alo.InOut = '1'
WHERE DATEPART(HOUR, LogTime) = '15'
AND ali.InOut = '0'
AND CAST(ali.LogDate AS date) = '2016-12-01'

Note that for the AccessLog representing the out date I've used a left join, so that you will also get Outlet names that hasn't logged out yet.

Upvotes: 0

Related Questions