How to run a different query based on the day of w

问题:

I have a program that autoruns with Windows scheduler. This program runs a stored procedure which uses Yesterdays date, to run a query against our database to pull results from the previous day. This query EXCLUDES a specific time period throughout the day as we do not care about those results. However, we have different hours on Sunday, and would like to change the query for Sunday... Is there a way to query differently if it's a "Sunday"? Below is my stored procedure:

CREATE PROCEDURE [dbo].[sp_Open_Close_Report_1] AS
declare   @dtNow         datetime ,   @dtToday       datetime ,   @dtFrom        datetime ,   @dtThru        datetime ,   @dtExcludeFrom datetime ,   @dtExcludeThru datetime, @dtExcludeEOD datetime 
set @dtNow         = getdate() 
set @dtToday       = convert(datetime,convert(varchar,@dtNow,112),112) 
set @dtFrom        = dateadd(day,-1,@dtToday) -- start-of-day yesterday 
set @dtThru        = dateadd(ms,-3,@dtToday)  -- end-of-day yesterday (e.g., 2012-06-17 23:59:59.997) 
set @dtExcludeFrom = convert(datetime, convert(char(10),@dtFrom,120) + ' 05:30:00.000' , 120 ) 
set @dtExcludeThru = convert(datetime, convert(char(10),@dtFrom,120) + ' 06:15:00.000' , 120 ) 
set @dtExcludeEOD = convert(datetime, convert(char(10),@dtFrom,120) + ' 08:00:00.000' , 120 ) 


SELECT Store_Id , DM_Corp_Received_Date, Register_Transaction_Type 
FROM Register_Till_Count_Tb 
WHERE (Register_Transaction_Type    =  'SOD' and store_ID = '12345'
AND DM_Corp_Received_date     between @dtFrom        
and @dtThru   
AND DM_Corp_Received_date not between @dtExcludeFrom 
and @dtExcludeThru) or (Register_Transaction_Type = 'EOD' and Store_ID='12345'
AND DM_Corp_Received_date Between @dtFrom and @dtThru)

On Sunday the @DTExclude From will be 6:30 and @dtexcludeThru will be 7:15

回答1:

Lets do more of the maths keeping everything as datetimes (or ints) rather than mucking about with strings:

declare   @dtNow         datetime ,   @dtToday       datetime ,   @dtFrom        datetime ,   @dtThru        datetime ,   @dtExcludeFrom datetime ,   @dtExcludeThru datetime, @dtExcludeEOD datetime 
set @dtNow         = getdate() 
set @dtToday       = DATEADD(day,DATEDIFF(day,0,@dtNow),0)
set @dtFrom        = dateadd(day,-1,@dtToday)

declare @Sunday int
set @Sunday = CASE WHEN DATEPART(weekday,@dtFrom) = DATEPART(weekday,'20120805') THEN 1 ELSE 0 END
set @dtExcludeFrom = DATEADD(minute,330 + @Sunday * 60,@dtFrom)
set @dtExcludeThru = DATEADD(minute,375 + @Sunday * 60,@dtFrom)
set @dtExcludeEOD = DATEADD(minute,480,@dtFrom)

And, in your query, rather than having:

column between @dtFrom and @dtThru

use:

column >= @dtFrom and column < @dtToday

回答2:

Take a look at the datename and datepart functions. You can use an if statement to perform different logic based on what the day is. When run on a Sunday, they will yield these results:

select datename(dw, getdate()) -- Sunday
select datepart(dw, getdate()) -- 1