Need help with sequel statement.
in [Programming SQL]
|
From: Tom Cooper on 28 Jul 2010 23:47 The reason that WHERE EnterDt >= CONVERT(CHAR(8), DATEADD(day, CASE WHEN DATEDIFF(day, '19000101', CURRENT_TIMESTAMP) % 7 <= 2 THEN -4 ELSE -3 END, CURRENT_TIMESTAMP), 112) works can be seen by examing it from the inside out. The most interior part is DATEDIFF(day, '19000101', CURRENT_TIMESTAMP) which gives you the number of days from Jan 1, 1900 to the current day. So, for example, today is, as I write this, Wednesday, July 28, 2010. And DATEDIFF(day, '19000101', CURRENT_TIMESTAMP) returns 40,385. The next step is to take the remainder when dividing by 7. 40, 385 divided by 7 gives 5769 and a remainder of 2. If today had been a Monday, then the number of days would be an exact multiple of 7 and the remainder would be 0. All Tuesdays leave a remainder of 1, Wednesdays 2, Thursdays 3, etc thru Sundays have a remainder of 6. So if today is Monday, Tuesday, or Wednesday, the remainder is <=2 otherwise it is > 2.. The next step is CASE WHEN <that remainder we just calculated> <= 2 THEN -4 ELSE -3 END. So now we have -4 if today is Monday, Tuesday, or Wednesday, -3 otherwise. The next step is DATEADD(day, <either -4 or -3 from the above calculation>, CURRENT_TIMESTAMP). So now we have subtracted either 4 days or 3 days from the current date and time. However, this has both a date and a time value, and you want to strip the time value out and get only the date. So we do CONVERT(CHAR(8),<the day and time we just calculated above>, 112) which gives us the date without a time either 3 or 4 days ago. Finally we do WHERE EnterDt >= <the date we just calculated> which gives us all rows with an EnterDt >= that date. Tom "LN" <LN(a)discussions.microsoft.com> wrote in message news:2E2640D6-63B8-4527-8989-7880C6124517(a)microsoft.com... > Thank you so much for your help. I agreed create a calendar table a good > idea for this case but our DBA lead doesn't want to create it and I try to > convience him that it's good practice to used a Calender table for many > other > things but no luch and I just want to get it done. Below is my query > using a > Calendar table but your is working great. Now, I try to understand this > piece of the puzzle. > > CONVERT(CHAR(8), DATEADD(day, CASE WHEN DATEDIFF(day, '19000101', > CURRENT_TIMESTAMP) % 7 <= 2 THEN -4 ELSE -3 END > > > --Using a calender table. > > --Calendar table. > dt isWeekday Y Q D DW MonthName DayName > ---------- --------- ----------- ---- ---- ---- --------- --------- > 07/01/2010 1 2010 3 1 5 July Thursday > 07/02/2010 1 2010 3 2 6 July Friday > 07/03/2010 0 2010 3 3 7 July Saturday > 07/04/2010 0 2010 3 4 1 July Sunday > 07/05/2010 1 2010 3 5 2 July Monday > 07/06/2010 1 2010 3 6 3 July Tuesday > 07/07/2010 1 2010 3 7 4 July Wednesday > 07/08/2010 1 2010 3 8 5 July Thursday > 07/09/2010 1 2010 3 9 6 July Friday > 07/10/2010 0 2010 3 10 7 July Saturday > 07/11/2010 0 2010 3 11 1 July Sunday > 07/12/2010 1 2010 3 12 2 July Monday > 07/13/2010 1 2010 3 13 3 July Tuesday > 07/14/2010 1 2010 3 14 4 July Wednesday > 07/15/2010 1 2010 3 15 5 July Thursday > 07/16/2010 1 2010 3 16 6 July Friday > 07/17/2010 0 2010 3 17 7 July Saturday > 07/18/2010 0 2010 3 18 1 July Sunday > 07/19/2010 1 2010 3 19 2 July Monday > 07/20/2010 1 2010 3 20 3 July Tuesday > 07/21/2010 1 2010 3 21 4 July Wednesday > 07/22/2010 1 2010 3 22 5 July Thursday > 07/23/2010 1 2010 3 23 6 July Friday > 07/24/2010 0 2010 3 24 7 July Saturday > 07/25/2010 0 2010 3 25 1 July Sunday > 07/26/2010 1 2010 3 26 2 July Monday > 07/27/2010 1 2010 3 27 3 July Tuesday > 07/28/2010 1 2010 3 28 4 July Wednesday > 07/29/2010 1 2010 3 29 5 July Thursday > 07/30/2010 1 2010 3 30 6 July Friday > 07/31/2010 0 2010 3 31 7 July Saturday > > (31 row(s) affected) > > DECLARE @Today AS DATETIME > SET @Today = DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) > SELECT @Today > > SELECT a.* > FROM #Temp AS a > JOIN Calendar AS b > ON a.EnterDt = b.dt > WHERE CONVERT(CHAR(8),b.dt, 112) >= > CASE > WHEN (DATENAME (weekday, GETDATE()) IN ('Monday', 'Tuesday', > 'Wednesday')) THEN CONVERT(CHAR(8), DATEADD(dd,-4, @Today), 112) > WHEN (DATENAME (weekday, GETDATE()) IN ('Thursday', 'Friday', > 'Saturday', 'Sunday')) THEN CONVERT(CHAR(8), DATEADD(dd,-3, @Today), 112) > END > AND CONVERT(CHAR(8),b.dt, 112) <= > CASE > WHEN (DATENAME (weekday, GETDATE()) IN ('Monday', 'Tuesday', > 'Wednesday')) THEN CONVERT(CHAR(8), GETDATE(), 112) > WHEN (DATENAME (weekday, GETDATE()) IN ('Thursday', 'Friday', > 'Saturday', 'Sunday')) THEN CONVERT(CHAR(8), GETDATE(), 112) > END > ORDER BY EnterDt ASC; > GO > > SELECT DATENAME (weekday, GETDATE()) > > "Tom Cooper" wrote: > >> I wholeheartedly second (or third?) Gert-Jan's and Joe's recommendation >> to >> create a calendar table and use it for this sort of query. See >> http://www.aspfaq.com/show.asp?id=2519 >> for a description of how to create a calendar table and some of their >> many >> uses. >> >> But even without a calendar table, it is possible to simplify the query >> while making it more bullet-proof and possibly improving performance. >> >> First, be very careful using DATEPART(WEEKDAY... to determine the day of >> the >> week. The value returned changes depending on the value of @@DateFirst. >> So >> if you use it, you should check the value of @@DateFirst to know how to >> interpet the results. A better way to determine the data of the week is >> to >> take the number of days between a given date and look at the remainder >> when >> dividing by 7 (you get the remainder in SQL by using the % operator). In >> the code below, I use Jan 1, 1900 which was a Monday. Then if the >> remainder >> and of the difference between Jan 1, 1900 and the date I am testing when >> divided by 7 is 0, the date I am testing must be a Monday, if it's 1, >> then a >> Tuesday, etc. This way of detemining the day of the week works no matter >> what the settings of your server and connection are. >> >> Second, avoid using functions on columns in your tables in a WHERE or ON >> clause. Doing that can prevent SQL from using indexes on your table and >> make your queries run slower. Try to rewrite the WHERE or ON clause so >> that >> the function use os on constants or variables, not table columns. >> >> Finally, you can do this in one query using a CASE, you don't need two >> queries with an IF statement. >> >> So, if you don't have a calendar table, you want something like >> >> SELECT * >> FROM #Temp AS a >> WHERE EnterDt >= CONVERT(CHAR(8), DATEADD(day, CASE WHEN DATEDIFF(day, >> '19000101', CURRENT_TIMESTAMP) % 7 <= 2 THEN -4 ELSE -3 END, >> CURRENT_TIMESTAMP), 112) >> AND EnterDt < CONVERT(CHAR(8), DATEADD(day, 1, CURRENT_TIMESTAMP), 112) >> ORDER BY EnterDt ASC; >> >> Tom >> >> "LN" <LN(a)discussions.microsoft.com> wrote in message >> news:29026E81-E3FB-47A9-B110-39F64ADEB5F1(a)microsoft.com... >> > Give the table and business rules below, how can I get desired result >> > showing >> > below. >> > I have a work around but I wonder if there is a better way to construct >> > this >> > query. >> > >> > Thank you very much in advance for your help. >> > >> > >> > --SQL 2005 >> > IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL >> > DROP TABLE #Temp >> > GO >> > CREATE TABLE #Temp >> > ( >> > LoanNum VARCHAR(8) NULL, >> > EnterDt DATETIME >> > ) >> > GO >> > >> > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12345, '07/26/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12346, '07/24/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12347, '07/23/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12348, '07/22/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12349, '07/21/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12350, '07/20/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12351, '07/19/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12352, '07/18/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12353, '07/27/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12354, '07/25/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12356, '07/28/2010') >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) >> > VALUES (12357, '07/29/2010') >> > go >> > >> > SELECT * >> > FROM #Temp >> > ORDER BY EnterDt ASC; >> > GO >> > >> > -- Business Rules: Return all the rows within the last 3 business days >> > include Sat as >> > business day and Exclude Sun. >> > >> > -- Case1: Give today is 07/29 Thur. How can I get the desire results >> > below. >> > >> > LoanNum EnterDt >> > -------- ----------------------- >> > 12345 2010-07-26 00:00:00.000 >> > 12353 2010-07-27 00:00:00.000 >> > 12356 2010-07-28 00:00:00.000 >> > 12357 2010-07-29 00:00:00.000 >> > >> > -- Case2: Give today is 07/26 Mon. How can I get the desire results >> > below. >> > LoanNum EnterDt >> > -------- ----------------------- >> > 12348 2010-07-22 00:00:00.000 >> > 12347 2010-07-23 00:00:00.000 >> > 12346 2010-07-24 00:00:00.000 >> > 12354 2010-07-25 00:00:00.000 >> > 12345 2010-07-26 00:00:00.000 >> > >> > >> > -- It's working but wonder there is a better way to write this. >> > DECLARE @Day TINYINT >> > SET @Day = DATEPART(WEEKDAY, CURRENT_TIMESTAMP) -- Return weekday 2, 3, >> > 4: >> > Mon, Tue, Wed. >> > >> > SELECT GETDATE() AS 'Today', @Day >> > >> > IF (@Day IN (2, 3, 4)) -- Mon, Tue, Wed >> > BEGIN >> > SELECT * >> > FROM #Temp AS a >> > WHERE (CONVERT(CHAR(8), EnterDt, 112) >= CONVERT(CHAR(8), >> > Dateadd(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -4), 112) -- >> > substract >> > 4 >> > days. >> > AND CONVERT(CHAR(8), EnterDt, 112) <= CONVERT(CHAR(8), >> > DATEADD(DAY, 0, {fn CURDATE()}), 112) ) -- return today. >> > ORDER BY EnterDt ASC >> > END >> > ELSE >> > BEGIN >> > SELECT * >> > FROM #Temp AS a >> > WHERE (CONVERT(CHAR(8), EnterDt, 112) >= CONVERT(CHAR(8), >> > Dateadd(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -3), 112) -- >> > substract >> > 3 >> > days. >> > AND CONVERT(CHAR(8), EnterDt, 112) <= CONVERT(CHAR(8), >> > DATEADD(DAY, 0, {fn CURDATE()}), 112) ) -- return today. >> > ORDER BY EnterDt ASC >> > END; >> >> . >>
From: LN on 29 Jul 2010 11:26 I break your query out and play with it plus the explanation make it easy to understand. Again, your help is greatly appreciate and I learn something new on this Forum everyday. Thank you. L "Tom Cooper" wrote: > The reason that > > WHERE EnterDt >= CONVERT(CHAR(8), DATEADD(day, CASE WHEN DATEDIFF(day, > '19000101', CURRENT_TIMESTAMP) % 7 <= 2 THEN -4 ELSE -3 END, > CURRENT_TIMESTAMP), 112) > > works can be seen by examing it from the inside out. The most interior part > is > DATEDIFF(day, '19000101', CURRENT_TIMESTAMP) > which gives you the number of days from Jan 1, 1900 to the current day. So, > for example, today is, as I write this, Wednesday, July 28, 2010. And > DATEDIFF(day, '19000101', CURRENT_TIMESTAMP) returns 40,385. > > The next step is to take the remainder when dividing by 7. 40, 385 divided > by 7 gives 5769 and a remainder of 2. If today had been a Monday, then the > number of days would be an exact multiple of 7 and the remainder would be 0. > All Tuesdays leave a remainder of 1, Wednesdays 2, Thursdays 3, etc thru > Sundays have a remainder of 6. So if today is Monday, Tuesday, or > Wednesday, the remainder is <=2 otherwise it is > 2.. > > The next step is > CASE WHEN <that remainder we just calculated> <= 2 THEN -4 ELSE -3 END. So > now we have -4 if today is Monday, Tuesday, or Wednesday, -3 otherwise. > > The next step is > DATEADD(day, <either -4 or -3 from the above calculation>, > CURRENT_TIMESTAMP). So now we have subtracted either 4 days or 3 days from > the current date and time. However, this has both a date and a time value, > and you want to strip the time value out and get only the date. So we do > > CONVERT(CHAR(8),<the day and time we just calculated above>, 112) which > gives us the date without a time either 3 or 4 days ago. > > Finally we do > WHERE EnterDt >= <the date we just calculated> which gives us all rows with > an EnterDt >= that date. > > Tom > > "LN" <LN(a)discussions.microsoft.com> wrote in message > news:2E2640D6-63B8-4527-8989-7880C6124517(a)microsoft.com... > > Thank you so much for your help. I agreed create a calendar table a good > > idea for this case but our DBA lead doesn't want to create it and I try to > > convience him that it's good practice to used a Calender table for many > > other > > things but no luch and I just want to get it done. Below is my query > > using a > > Calendar table but your is working great. Now, I try to understand this > > piece of the puzzle. > > > > CONVERT(CHAR(8), DATEADD(day, CASE WHEN DATEDIFF(day, '19000101', > > CURRENT_TIMESTAMP) % 7 <= 2 THEN -4 ELSE -3 END > > > > > > --Using a calender table. > > > > --Calendar table. > > dt isWeekday Y Q D DW MonthName DayName > > ---------- --------- ----------- ---- ---- ---- --------- --------- > > 07/01/2010 1 2010 3 1 5 July Thursday > > 07/02/2010 1 2010 3 2 6 July Friday > > 07/03/2010 0 2010 3 3 7 July Saturday > > 07/04/2010 0 2010 3 4 1 July Sunday > > 07/05/2010 1 2010 3 5 2 July Monday > > 07/06/2010 1 2010 3 6 3 July Tuesday > > 07/07/2010 1 2010 3 7 4 July Wednesday > > 07/08/2010 1 2010 3 8 5 July Thursday > > 07/09/2010 1 2010 3 9 6 July Friday > > 07/10/2010 0 2010 3 10 7 July Saturday > > 07/11/2010 0 2010 3 11 1 July Sunday > > 07/12/2010 1 2010 3 12 2 July Monday > > 07/13/2010 1 2010 3 13 3 July Tuesday > > 07/14/2010 1 2010 3 14 4 July Wednesday > > 07/15/2010 1 2010 3 15 5 July Thursday > > 07/16/2010 1 2010 3 16 6 July Friday > > 07/17/2010 0 2010 3 17 7 July Saturday > > 07/18/2010 0 2010 3 18 1 July Sunday > > 07/19/2010 1 2010 3 19 2 July Monday > > 07/20/2010 1 2010 3 20 3 July Tuesday > > 07/21/2010 1 2010 3 21 4 July Wednesday > > 07/22/2010 1 2010 3 22 5 July Thursday > > 07/23/2010 1 2010 3 23 6 July Friday > > 07/24/2010 0 2010 3 24 7 July Saturday > > 07/25/2010 0 2010 3 25 1 July Sunday > > 07/26/2010 1 2010 3 26 2 July Monday > > 07/27/2010 1 2010 3 27 3 July Tuesday > > 07/28/2010 1 2010 3 28 4 July Wednesday > > 07/29/2010 1 2010 3 29 5 July Thursday > > 07/30/2010 1 2010 3 30 6 July Friday > > 07/31/2010 0 2010 3 31 7 July Saturday > > > > (31 row(s) affected) > > > > DECLARE @Today AS DATETIME > > SET @Today = DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) > > SELECT @Today > > > > SELECT a.* > > FROM #Temp AS a > > JOIN Calendar AS b > > ON a.EnterDt = b.dt > > WHERE CONVERT(CHAR(8),b.dt, 112) >= > > CASE > > WHEN (DATENAME (weekday, GETDATE()) IN ('Monday', 'Tuesday', > > 'Wednesday')) THEN CONVERT(CHAR(8), DATEADD(dd,-4, @Today), 112) > > WHEN (DATENAME (weekday, GETDATE()) IN ('Thursday', 'Friday', > > 'Saturday', 'Sunday')) THEN CONVERT(CHAR(8), DATEADD(dd,-3, @Today), 112) > > END > > AND CONVERT(CHAR(8),b.dt, 112) <= > > CASE > > WHEN (DATENAME (weekday, GETDATE()) IN ('Monday', 'Tuesday', > > 'Wednesday')) THEN CONVERT(CHAR(8), GETDATE(), 112) > > WHEN (DATENAME (weekday, GETDATE()) IN ('Thursday', 'Friday', > > 'Saturday', 'Sunday')) THEN CONVERT(CHAR(8), GETDATE(), 112) > > END > > ORDER BY EnterDt ASC; > > GO > > > > SELECT DATENAME (weekday, GETDATE()) > > > > "Tom Cooper" wrote: > > > >> I wholeheartedly second (or third?) Gert-Jan's and Joe's recommendation > >> to > >> create a calendar table and use it for this sort of query. See > >> http://www.aspfaq.com/show.asp?id=2519 > >> for a description of how to create a calendar table and some of their > >> many > >> uses. > >> > >> But even without a calendar table, it is possible to simplify the query > >> while making it more bullet-proof and possibly improving performance. > >> > >> First, be very careful using DATEPART(WEEKDAY... to determine the day of > >> the > >> week. The value returned changes depending on the value of @@DateFirst. > >> So > >> if you use it, you should check the value of @@DateFirst to know how to > >> interpet the results. A better way to determine the data of the week is > >> to > >> take the number of days between a given date and look at the remainder > >> when > >> dividing by 7 (you get the remainder in SQL by using the % operator). In > >> the code below, I use Jan 1, 1900 which was a Monday. Then if the > >> remainder > >> and of the difference between Jan 1, 1900 and the date I am testing when > >> divided by 7 is 0, the date I am testing must be a Monday, if it's 1, > >> then a > >> Tuesday, etc. This way of detemining the day of the week works no matter > >> what the settings of your server and connection are. > >> > >> Second, avoid using functions on columns in your tables in a WHERE or ON > >> clause. Doing that can prevent SQL from using indexes on your table and > >> make your queries run slower. Try to rewrite the WHERE or ON clause so > >> that > >> the function use os on constants or variables, not table columns. > >> > >> Finally, you can do this in one query using a CASE, you don't need two > >> queries with an IF statement. > >> > >> So, if you don't have a calendar table, you want something like > >> > >> SELECT * > >> FROM #Temp AS a > >> WHERE EnterDt >= CONVERT(CHAR(8), DATEADD(day, CASE WHEN DATEDIFF(day, > >> '19000101', CURRENT_TIMESTAMP) % 7 <= 2 THEN -4 ELSE -3 END, > >> CURRENT_TIMESTAMP), 112) > >> AND EnterDt < CONVERT(CHAR(8), DATEADD(day, 1, CURRENT_TIMESTAMP), 112) > >> ORDER BY EnterDt ASC; > >> > >> Tom > >> > >> "LN" <LN(a)discussions.microsoft.com> wrote in message > >> news:29026E81-E3FB-47A9-B110-39F64ADEB5F1(a)microsoft.com... > >> > Give the table and business rules below, how can I get desired result > >> > showing > >> > below. > >> > I have a work around but I wonder if there is a better way to construct > >> > this > >> > query. > >> > > >> > Thank you very much in advance for your help. > >> > > >> > > >> > --SQL 2005 > >> > IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL > >> > DROP TABLE #Temp > >> > GO > >> > CREATE TABLE #Temp > >> > ( > >> > LoanNum VARCHAR(8) NULL, > >> > EnterDt DATETIME > >> > ) > >> > GO > >> > > >> > > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12345, '07/26/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12346, '07/24/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12347, '07/23/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12348, '07/22/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12349, '07/21/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12350, '07/20/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12351, '07/19/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12352, '07/18/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12353, '07/27/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12354, '07/25/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12356, '07/28/2010') > >> > INSERT INTO dbo.#Temp (LoanNum, EnterDt) > >> > VALUES (12357, '07/29/2010') > >> > go > >> > > >> > SELECT * > >> > FROM #Temp > >> > ORDER BY EnterDt ASC; > >> > GO > >> > > >> > -- Business Rules: Return all the rows within the last 3 business days > >> > include Sat as > >> > business day and Exclude Sun. > >> > > >> > -- Case1: Give today is 07/29 Thur. How can I get the desire results > >> > below. > >> > > >> > LoanNum EnterDt > >> > -------- ----------------------- > >> > 12345 2010-07-26 00:00:00.000 > >> > 12353 2010-07-27 00:00:00.000 > >> > 12356 2010-07-28 00:00:00.000 > >> > 12357 2010-07-29 00:00:00.000 > >> > > >> > -- Case2: Give today is 07/26 Mon. How can I get the desire results > >> > below. > >> > LoanNum EnterDt > >> > -------- ----------------------- > >> > 12348 2010-07-22 00:00:00.000 > >> > 12347 2010-07-23 00:00:00.000 > >> > 12346 2010-07-24 00:00:00.000 > >> > 12354 2010-07-25 00:00:00.000 > >> > 12345 2010-07-26 00:00:00.000 > >> > > >> > > >> > -- It's working but wonder there is a better way to write this. > >> > DECLARE @Day TINYINT > >> > SET @Day = DATEPART(WEEKDAY, CURRENT_TIMESTAMP) -- Return weekday 2, 3, > >> > 4: > >> > Mon, Tue, Wed. > >> > > >> > SELECT GETDATE() AS 'Today', @Day > >> > > >> > IF (@Day IN (2, 3, 4)) -- Mon, Tue, Wed > >> > BEGIN > >> > SELECT * > >> > FROM #Temp AS a > >> > WHERE (CONVERT(CHAR(8), EnterDt, 112) >= CONVERT(CHAR(8), > >> > Dateadd(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -4), 112) -- > >> > substract > >> > 4 > >> > days. > >> > AND CONVERT(CHAR(8), EnterDt, 112) <= CONVERT(CHAR(8), > >> > DATEADD(DAY, 0, {fn CURDATE()}), 112) ) -- return today. > >> > ORDER BY EnterDt ASC > >> > END > >> > ELSE > >> > BEGIN > >> > SELECT * > >> > FROM #Temp AS a > >> > WHERE (CONVERT(CHAR(8), EnterDt, 112) >= CONVERT(CHAR(8), > >> > Dateadd(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -3), 112) -- > >> > substract > >> > 3 > >> > days. > >> > AND CONVERT(CHAR(8), EnterDt, 112) <= CONVERT(CHAR(8), > >> > DATEADD(DAY, 0, {fn CURDATE()}), 112) ) -- return today. > >> > ORDER BY EnterDt ASC > >> > END; > >> > >> . > >> > > . >
First
|
Prev
|
Pages: 1 2 3 Prev: single quotes issue Next: Composite Index Creation Needed SQL 2008? |