如何根据星期几运行不同的查询 [英] How to run a different query based on the day of week

问题描述

我有一个使用 Windows 调度程序自动运行的程序.该程序运行一个使用昨天日期的存储过程，对我们的数据库运行查询以提取前一天的结果.此查询排除了全天的特定时间段，因为我们不关心这些结果.但是，我们周日有不同的时间，并且想更改周日的查询......如果是周日"，有没有办法进行不同的查询?下面是我的存储过程:

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)

周日 @DTExclude From 是 6:30 而 @dtexcludeThru 是 7:15

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

推荐答案

让我们做更多的数学计算，将所有内容保留为日期时间(或整数)，而不是使用字符串:

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

使用:

column >= @dtFrom and column < @dtToday

这篇关于如何根据星期几运行不同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！