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

Answers


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.


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


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

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

Need Your Help

Add UIImage or UILabel to navigationItems

ios uilabel uinavigationbar uibarbuttonitem uinavigationitem

In my app, I have to customize my navigation bar depending on the page I'm on. In some views, I need to add some UIButton, but also some UIImage (for the logo that is not centered) or some UILabel....

REST Api documentation generation

rest maven jboss resteasy

Is there any decent plugin which integrates with maven to generate REST API documentation for RestEasy ?