Prev: single quotes issue
Next: Composite Index Creation Needed SQL 2008?
From: Gert-Jan Strik on 28 Jul 2010 16:14
Final + 1 (still untested):

Declare @cutoff datetime
Set @cutoff = (
SELECT TOP 1 "date"
FROM (
SELECT TOP 3 "date"
FROM Calendar
WHERE "date" > DATEADD(day, -10, CURRENT_TIMESTAMP)
AND "date" < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND business_day = 'Y'
ORDER BY "date" DESC
) T
ORDER BY "date"
)

SELECT T1.*
FROM #Temp T1
JOIN Calendar
ON "date" = EnterDt -- assuming EnterDt does not contain time portion
WHERE EnterDt >= @cutoff
AND EnterDt <= CURRENT_TIMESTAMP
AND business_day = 'Y'

Good luck,
Gert-Jan
From: Tom Cooper on 28 Jul 2010 17:06
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 28 Jul 2010 17:27

Celkooooo.

Don't bother, you're NO help and always criticize.

"--CELKO--" wrote:

> The language is SQL, not Sequel. The Sequel language was an IBM
> product decades ago. It is pronounced as separate letter, as per the
> usual ISO convention.
>
> Your table had no key and did not use the proper date formats. Why is
> the loan number declared as CHAR(8) then entered as an integer? I
> cannot think of a single industry standard identifier except the IBAN
> that is variable length. Is more like what you were trying to post?
>
> CREATE TABLE Loans
> (loan_nbr CHAR(8) NOT NULL PRIMARY KEY,
> entry_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);
>
> /* did you know about the ANSI syntax that SQL Server finally got?
> YOu can do the table building in one statement now*/
>
> INSERT INTO Loans (loan_nbr, entry_date)
> VALUES ('00012345', '2010-07-26'),
> ('00012346', '2010-07-24'),
> ('00012347', '2010-07-23'),
> ('00012348', '2010-07-22'),
> ('00012349', '2010-07-21'),
> ('00012350', '2010-07-20'),
> ('00012351', '2010-07-19'),
> ('00012352', '2010-07-18'),
> ('00012353', '2010-07-27'),
> ('00012354', '2010-07-25'),
> ('00012356', '2010-07-28'),
> ('00012357', '2010-07-29');
>
> >> Business Rules: Return all the rows within the last 3 business days include Sat as business day and Exclude Sun. <<
>
> You create a calendar table with a Julianized day number for business
> days. This lets you handle holidays easily. Google this group -- I
> recently re-posted a note about it.
>
> What you have is not working because of holidays.
>
> But even then, it is poor SQL. The use of the old Sybase era
> getdate() should be the ANSI/ISO Standard CURRENT_TIMESTAMP instead.
> Likewise, the use of single quotes on user data element names is
> proprietary. Why did you use CONVERT() to get strings to compare
> temporal data? Think about it. SQL handles temporal data; this is not
> COBOL or BASIC.
>
> Your mindset is still back in procedural codign and you have not moved
> on to declarative coding. SQL is a database language, not an
> application language.
> .
>
From: Erland Sommarskog on 28 Jul 2010 18:09
Tom Cooper (tomcooper(a)comcast.net) writes:
> 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.

An easier approach is to use datename - at least as long as you can assume
that the current language is some variety of English.

But, yes, a calendar table is better. Suddenly there is a new reqiurement
that Thursdays in odd weeks should not be counted or whatever.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: LN on 28 Jul 2010 23:05
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  |  Next  |  Last
Pages: 1 2 3
Prev: single quotes issue
Next: Composite Index Creation Needed SQL 2008?