SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 dynamic SQL help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

joe8079
Posting Yak Master

USA
127 Posts

Posted - 08/16/2013 :  11:52:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 08/16/2013 :  12:08:11  Show Profile  Reply with Quote
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.
Go to Top of Page

joe8079
Posting Yak Master

USA
127 Posts

Posted - 08/16/2013 :  12:13:32  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 08/16/2013 :  13:54:01  Show Profile  Reply with Quote
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).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000