CODEWITHSUNDEEP
sqlsql-server-2008
amart47
amart47

Reputation: 95

query criteria based on current day (sat/sun should return friday's value)

I'm trying to figure out how to return daily results throughout the week while keeping Friday's results visible over the weekend. My current code isn't doing the trick - do I have to mess with arrays?

SELECT     ROUND(COUNT(ClosedDate) / 10, 0) * 10 [Previous Day Sales]
FROM         PartsSales
WHERE     (MONTH(ClosedDate) = MONTH(GETDATE())) AND (YEAR(ClosedDate) =     YEAR(GETDATE())) AND (DAY(ClosedDate) = **case** DAY(GETDATE()) 
when 1 then 1 
when 2 then 2
when 3 then 3
when 4 then 4
when 5 then 5
when 6 then 5
when 7 then 5 end

Upvotes: 1

Views: 38

Answers (1)

RToyo
RToyo

Reputation: 2877

You can use datepart to find the current day of the week (1 = Sunday, 2 = Monday, etc), and then you can use dateadd to go back to Friday's date. If you convert getdate() to a date, then you'll always have the "midnight" at the begining of that day. 

dateadd(
    day,
    case datepart(dw, getdate())
        when 1 then -- Sunday
            -2
        when 7 then -- Saturday
            -1
        else        -- Any other day
            0
    end,
    convert(date, getdate())
)

Is your ClosedDate a datetime datatype? You can make much better use of your indexes by checking for a date range, rather than pulling the dateparts out (using year/month/day). Below is an example with a lot of resused code in the where clause. Of course, if it is just a date datatype, you don't even need a range, since you are calculating the date.

Below is an example. It would be better if you used variables (if you're building this into a stored proc), or perhaps a derived/CTE table. I've kept things verbose for clarity.

SELECT     
    ROUND(COUNT(ClosedDate) / 10, 0) * 10 [Previous Day Sales]
FROM         
    PartsSales
WHERE
    ClosedDate between 
        -- Today/Friday's date:
        dateadd(
            day,
            case datepart(dw, getdate())
                when 1 then -- Sunday
                    -2
                when 7 then -- Saturday
                    -1
                else        -- Any other day
                    0
            end,
            convert(date, getdate())
        )
        and
        -- Add 1 day to the "Today/Friday" date. 
        -- This is the same logic as above, except wrapped in an extra dateadd to add 1 day to it.
        dateadd(
            day,
            1,
            dateadd(
                day,
                case datepart(dw, getdate())
                    when 1 then -- Sunday
                        -2
                    when 7 then -- Saturday
                        -1
                    else        -- Any other day
                        0
                end,
                convert(date, getdate())
            )
        )

Upvotes: 2

Related Questions