Need help with sequel statement.
in [Programming SQL]
|
From: LN on 28 Jul 2010 14:25 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: Gert-Jan Strik on 28 Jul 2010 16:06 The easiest way is to create a Calendar table. In this table, you insert one row for each day for any year that might be queried. Have a column that indicates whether it is a business day or not. This makes it possible to also mark national holidays as not a business day, etc. Then run a query like this: Declare @cutoff datetime Set @cutoff = ( SELECT TOP 1 "date" FROM ( SELECT TOP 3 "date" FROM Calendar WHERE "date" <= DATEADD(day, -4, CURRENT_TIMESTAMP) ORDER BY "date" ) T ORDER BY "date" DESC ) SELECT * FROM #Temp WHERE EnterDt >= @cutoff AND EnterDt <= CURRENT_TIMESTAMP -- Gert-Jan
From: Gert-Jan Strik on 28 Jul 2010 16:07 Sorry, the first part should be: Set @cutoff = ( SELECT TOP 1 "date" FROM ( SELECT TOP 3 "date" FROM Calendar WHERE "date" <= DATEADD(day, -4, CURRENT_TIMESTAMP) AND business_day = 'Y' ORDER BY "date" ) T ORDER BY "date" DESC ) -- Gert-Jan Gert-Jan Strik wrote: > > The easiest way is to create a Calendar table. In this table, you insert > one row for each day for any year that might be queried. Have a column > that indicates whether it is a business day or not. This makes it > possible to also mark national holidays as not a business day, etc. > > Then run a query like this: > > Declare @cutoff datetime > Set @cutoff = ( > SELECT TOP 1 "date" > FROM ( > SELECT TOP 3 "date" > FROM Calendar > WHERE "date" <= DATEADD(day, -4, CURRENT_TIMESTAMP) > ORDER BY "date" > ) T > ORDER BY "date" DESC > ) > > SELECT * > FROM #Temp > WHERE EnterDt >= @cutoff > AND EnterDt <= CURRENT_TIMESTAMP > > -- > Gert-Jan
From: --CELKO-- on 28 Jul 2010 16:07 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: Gert-Jan Strik on 28 Jul 2010 16:11
Hmm, I keep correcting myself. Last attempt: 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" ) -- Gert-jan Gert-Jan Strik wrote: > > The easiest way is to create a Calendar table. In this table, you insert > one row for each day for any year that might be queried. Have a column > that indicates whether it is a business day or not. This makes it > possible to also mark national holidays as not a business day, etc. > > Then run a query like this: > > Declare @cutoff datetime > Set @cutoff = ( > SELECT TOP 1 "date" > FROM ( > SELECT TOP 3 "date" > FROM Calendar > WHERE "date" <= DATEADD(day, -4, CURRENT_TIMESTAMP) > ORDER BY "date" > ) T > ORDER BY "date" DESC > ) > > SELECT * > FROM #Temp > WHERE EnterDt >= @cutoff > AND EnterDt <= CURRENT_TIMESTAMP > > -- > Gert-Jan |