Michael Michael - 21 days ago 9
SQL Question

SQL Server : datediff from last week

I'm using SQL Server 2012 and have a statement that returns these records for the current week:

Employee , Sun , Mon , Tues, Wed, Thurs, Fri, Sat
--------------------------------------------------
Smith 5 8 5 4 8 4 0
Burt 7 1 8 5 4 4 7


This is my SQL and the week starts on a Sun. How do I change so it shows the previous week?

SELECT
ReproofMidLineBy AS Employee,
COUNT( CASE WHEN DATENAME( dw, ReproofMidLineDate )= 'Sunday' THEN 1 END ) AS Sun,
COUNT( CASE WHEN DATENAME( dw, ReproofMidLineDate )= 'Monday' THEN 1 END ) AS Mon,
COUNT( CASE WHEN DATENAME( dw, ReproofMidLineDate )= 'Tuesday' THEN 1 END ) AS Tues,
COUNT( CASE WHEN DATENAME( dw, ReproofMidLineDate )= 'Wednesday' THEN 1 END ) AS Wed,
COUNT( CASE WHEN DATENAME( dw, ReproofMidLineDate )= 'Thursday' THEN 1 END ) AS Thurs,
COUNT( CASE WHEN DATENAME( dw, ReproofMidLineDate )= 'Friday' THEN 1 END ) AS Fri,
COUNT( CASE WHEN DATENAME( dw, ReproofMidLineDate )= 'Saturday' THEN 1 END ) AS Sat,
COUNT(*) AS TOTAL
FROM
dbo.tblJobLog
WHERE
(ReproofMidLineDate BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)
AND DATEADD(DAY, DATEDIFF(DAY, 6, GETDATE()- 1) / 7 * 7 + 7, 6)
)
GROUP BY
ReproofMidLineBy

Answer

Replace your where clause with this:

    WHERE
(
    ReproofMidLineDate BETWEEN DATEADD(WEEK, -1, DATEADD(
        DAY,
        DATEDIFF(
            DAY,
            0,
            GETDATE()
        )/ 7 * 7,
        0))


    AND DATEADD(WEEK, -1, DATEADD(
        DAY,
        DATEDIFF(
            DAY,
            6,
            GETDATE()- 1
        )/ 7 * 7 + 7,
        6
    ))
)