dynamic SQL help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

dynamic SQL help

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2013-08-16 : 11:52:22

Hi,

I have the following report where a different where criteria is run depending on the current day the report is run. The report works great, but its kind of a mess with all the IF Statements. Is there anyway, I can make this shorter by using dynamic SQL? I tried looking at some links, but cant really get it right. Below is simplified versino of my report.

DECLARE @dateName AS VARCHAR(100)

SET @dateName = ( SELECT DATENAME(dw, GETDATE()) -- stores current day in variable @dateName
)

--IF DAY IS IN 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday' LOOK BACK 1 DAY FROM THE TIMESTAMP COLUMN
IF @dateName IN ( 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday' )
BEGIN

SELECT column1 ,
column2 ,
column3
FROM table1
WHERE TIMESTAMP >= DATEADD(d, -1, GETDATE())

END

--IF THE DAY IS MONDAY, LOOK BACK 3 DAYS FROM THE TIMESTAMP COLUMN
IF @dateName IN ( 'Monday' )
BEGIN

SELECT column1 ,
column2 ,
column3
FROM table1
WHERE TIMESTAMP >= DATEADD(d, -2, GETDATE())

END

--If Day is Sunday, look back 3 days from timestamp
IF @dateName IN ( 'Sunday' )
BEGIN

SELECT column1 ,
column2 ,
column3
FROM table1
WHERE TIMESTAMP >= DATEADD(d, -3, GETDATE())

END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-16 : 12:08:11

You can certainly use dynamic SQL to accomplish this, but for more than one reason, use of dynamic SQL is discouraged. You can accomplish what you want to do using a case expression like shown below.

-- 0 = Monday, 1 = Tuesday, ... 6 = Sunday
DECLARE @daysToSubtract INT;
SELECT @daysToSubtract = 
	CASE  DATEDIFF(dd,0,GETDATE())%7 
		WHEN 0 THEN 3
		WHEN 6 THEN 2
		ELSE 1 
	END

By the way, don't you want to subtract 3 days when it is monday, 2 days when it is sunday and 1 day otherwise? Your text and code does not seem to match.

joe8079
Posting Yak Master

127 Posts

Posted - 2013-08-16 : 12:13:32

Thanks James, I'll give that case statement a shot.
Basically, when the day is Monday, I only want records that have a timestamp for friday and when its sunday, I only want records with a timestamp of friday so thats why I have the where clause set up that way. Hopefully, this will work. I'll find out on Monday when I run it.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-16 : 13:54:01

Having to find the prior weekday is a very common problem in all kinds of businesses. For example, in the financial industry, most exchanges are closed on Saturday and Sunday in the Western world. So when you look for the prior trading day, you look for the prior weekday. Usually the approach I posted earlier works well in that case.

Sometimes you need to account for holidays as well - Easter, Thanksgiving, Christmas etc. If you need to do that also, then a better (and perhaps the only reliable) approach would be to use a calendar table that stores the holdiays (and weekends).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources