--**************************************
--
-- Name: Using Case Statements to Empowe
-- r your Betweens
-- Description:To demonstrate selection
-- queries using Case statements to adjust
-- bewtween clauses range based on situatio
-- nal needs.
-- By: James Travis
--
-- Assumes:The case statement is one of
-- the greatest items in SQL as it will all
-- ow you to do conditional testing of vari
-- able objects and return the specific ite
-- ms a person needs with less user input.
-- Many people use case statements in their
-- stored procedures but many don't realiz
-- e the potential they can gain from them
-- inside queries to help manipulate the da
-- ta. Here in this example I will use time
-- reporting to control how specific data
-- is reported on based on the current syst
-- em time of the server. (Note: I add 2 mi
-- lliseconds 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 betw
-- een statment. By adding 2 I ensure I sta
-- rt at .001 and subtracting 1 and end at
-- .000 and get no overlap in my result set
-- . But depending on the date you store th
-- is may not be needed in you situation.)
--
--This code is copyrighted and has-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/xq/ASP/txtCod
-- eId.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)))
|
