Please visit our sponsor
UNKNOWN --************************************** -- Name: Using Case Statements to Empower your Betweens -- Description:To demonstrate selection queries using Case statements to adjust bewtween clauses range based on situational needs. -- By: James Travis -- -- -- Inputs:None -- -- Returns:None -- --Assumes:The case statement is one of the greatest items in SQL as it will allow you to do conditional testing of variable objects and return the specific items a person needs with less user input. Many people use case statements in their stored procedures but many don't realize the potential they can gain from them inside queries to help manipulate the data. Here in this example I will use time reporting to control how specific data is reported on based on the current system time of the server. (Note: I add 2 milliseconds to the start time and subract 1 from the end time due to the fact SQL Server will pull the first second befor and after a given time anyway in a between statment. By adding 2 I ensure I start at .001 and subtracting 1 and end at .000 and get no overlap in my result set. But depending on the date you store this may not be needed in you situation.) -- --Side Effects:None --This code is copyrighted and has limited warranties. --Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.365/lngWId.5/qx/vb/scripts/ShowCode.htm --for details. --************************************** SELECT * FROM tblNameGoesHere WHERE ([DateTimeFldGoesHere] BETWEEN DATEADD(ms,2,DATEADD(hh,-1,(CASE WHEN (DATEPART(mi, GETDATE()) BETWEEN 0 AND 14) THEN DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE()) WHEN (DATEPART(mi, GETDATE()) BETWEEN 15 AND 29) THEN DATEADD(mi,15,DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE())) WHEN (DATEPART(mi, GETDATE()) BETWEEN 30 AND 44) THEN DATEADD(mi,30,DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE())) ELSE DATEADD(mi,45,DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE())) END))) AND DATEADD(ms,-1,(CASE WHEN (DATEPART(mi, GETDATE()) BETWEEN 0 AND 14) THEN DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE()) WHEN (DATEPART(mi, GETDATE()) BETWEEN 15 AND 29) THEN DATEADD(mi,15,DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE())) WHEN (DATEPART(mi, GETDATE()) BETWEEN 30 AND 44) THEN DATEADD(mi,30,DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE())) ELSE DATEADD(mi,45,DATEADD(mi,-DATEPART(mi,GETDATE()),GETDATE())) END)))