CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
Evil rising
Evil rising

Reputation: 442

getting records for current month only

i have written this query to pick timeout, time in, Date visited and total overtime for an employee, that's ok but problem is that i want to get record for current month only. It actually picks for all month mean from day he has joined organization till today but i want for this month only.

Query:

WITH times
        AS (
            SELECT t1.EmplID
                ,t3.EmplName
                ,min(t1.RecTime) AS InTime
                ,max(t2.RecTime) AS [TimeOut]
                ,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
            GROUP BY t1.EmplID
                ,t3.EmplName
                ,t1.RecDate
            )
        SELECT EmplID
            ,EmplName
            ,InTime
            ,[TimeOut]
            ,[DateVisited]
            ,convert(CHAR(5), cast([TimeOutSub] - InTimeSub AS TIME), 108) totaltime
            ,CONVERT(CHAR(5), CASE 
                    WHEN CAST([TimeOutSub] AS DATETIME) >= '18:00'
                        AND EmplId NOT IN (
                            5
                            ,43
                            ,11
                            ,40
                            ,46
                            ,42
                            ,31
                            )
                        THEN LEFT(CONVERT(VARCHAR(12), DATEADD(ms, DATEDIFF(ms, CAST('18:00' AS DATETIME), CAST([TimeOutSub] AS DATETIME)), 0), 108), 5)
                    WHEN CAST([TimeOutSub] AS DATETIME) >= '17:00'
                        AND EmplId IN (
                            5
                            ,43
                            ,40
                            ,46
                            ,42
                            ,31
                            )
                        THEN LEFT(CONVERT(VARCHAR(12), DATEADD(ms, DATEDIFF(ms, CAST('17:00' AS DATETIME), CAST([TimeOutSub] AS DATETIME)), 0), 108), 5)
                    ELSE '00:00'
                    END, 108) AS OVERTIME
        FROM times

Upvotes: 1

Views: 9602

Answers (4)

Mahaveer Jangid
Mahaveer Jangid

Reputation: 376

This is very Simple, Just get month of both of dates which dates you want to compare but both dates should be in date format or you have to cast in date format

get month of any date MONTH() method

  MONTH('2019/02/22')

it will return only month of this date, just pass date in month method.

if your date in varchar or string format than you should cast in date format

  CAST('2019/02/22'AS DATE)

Complete Example Is

  MONTH(CAST(YourDate AS DATE)) = MONTH(GETDATE())

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

This will find start and end of current month:

select dateadd(m, datediff(m, 0, GetDate()), 0),
       dateadd(m, datediff(m, -1, GetDate()), 0)

So basically add 

WHERE t1.RecDate >= dateadd(m, datediff(m, 0, GetDate()), 0)
  AND t1.RecDate < dateadd(m, datediff(m, -1, GetDate()), 0)

This solution will allow use of index and basically perform faster than the month(recdate) and year(recdate) suggestions

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You can use Date functions

SO you need to add this in your where conditions

 WHERE MONTH(t1.RecDate) = MONTH(GetDate())
            AND
            YEAR(t1.RecDate) = YEAR(GetDate())

Try like this

WITH times
        AS (
            SELECT t1.EmplID
                ,t3.EmplName
                ,min(t1.RecTime) AS InTime
                ,max(t2.RecTime) AS [TimeOut]
                ,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
            WHERE MONTH(t1.RecDate) = MONTH(GetDate())
            AND
            YEAR(t1.RecDate) = YEAR(GetDate())
            GROUP BY t1.EmplID
                ,t3.EmplName
                ,t1.RecDate
            )
        SELECT EmplID
            ,EmplName
            ,InTime
            ,[TimeOut]
            ,[DateVisited]
            ,convert(CHAR(5), cast([TimeOutSub] - InTimeSub AS TIME), 108) totaltime
            ,CONVERT(CHAR(5), CASE 
                    WHEN CAST([TimeOutSub] AS DATETIME) >= '18:00'
                        AND EmplId NOT IN (
                            5
                            ,43
                            ,11
                            ,40
                            ,46
                            ,42
                            ,31
                            )
                        THEN LEFT(CONVERT(VARCHAR(12), DATEADD(ms, DATEDIFF(ms, CAST('18:00' AS DATETIME), CAST([TimeOutSub] AS DATETIME)), 0), 108), 5)
                    WHEN CAST([TimeOutSub] AS DATETIME) >= '17:00'
                        AND EmplId IN (
                            5
                            ,43
                            ,40
                            ,46
                            ,42
                            ,31
                            )
                        THEN LEFT(CONVERT(VARCHAR(12), DATEADD(ms, DATEDIFF(ms, CAST('17:00' AS DATETIME), CAST([TimeOutSub] AS DATETIME)), 0), 108), 5)
                    ELSE '00:00'
                    END, 108) AS OVERTIME
        FROM times

Upvotes: 2

Raj
Raj

Reputation: 10843

Change your CTE to this:

WITH times
        AS (
            SELECT t1.EmplID
                ,t3.EmplName
                ,min(t1.RecTime) AS InTime
                ,max(t2.RecTime) AS [TimeOut]
                ,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
            WHERE MONTH(t1.RecDate)=MONTH(Getdate())
              AND YEAR (t1.RecDate)=YEAR(Getdate())
            GROUP BY t1.EmplID
                ,t3.EmplName
                ,t1.RecDate
            )

Please note that if you run this on the first of any month, you will get data only for that day and not for the previous month.

Upvotes: 1

Related Questions