5th business day.
in [Programming SQL]
|
From: Allen on 19 Mar 2010 17:00 5th business day. This is goofy but I needed something that didn't use loops (for a user friendly system called ARGOS that just uses stock SQL) and I didn't want to add a function. So I run the report every day and add this to the where statement. Sorry about the formatting (from cut paste). If you don't get it, make up a dummy calendar with example months starting the 1st on each successive day. Then mark when the 5th business day is... you'll see the pattern. Do the same for July/Sept where the date nearest the 4th is off for July the 1st Monday of the month for Sept. WHERE 'Y' = CASE WHEN TRIM (TO_CHAR (SYSDATE, 'MONTH')) NOT IN ('JULY', 'SEPTEMBER') AND ( (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN ('FRIDAY') AND TO_CHAR (SYSDATE, 'dd') IN ('05', '06', '07') OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY') AND TO_CHAR (SYSDATE, 'dd') IN ('07')))) THEN 'Y' WHEN (TRIM (TO_CHAR (SYSDATE, 'MONTH')) IN ('JULY', 'SEPTEMBER') AND ( (TRIM (TO_CHAR (SYSDATE, 'DAY')) = 'MONDAY' AND TO_CHAR (SYSDATE, 'dd') IN ('08', '09', '10')) OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY') AND TO_CHAR (SYSDATE, 'dd') IN ('08')))) THEN 'Y' ELSE 'N' END Terri wrote: 5th business day of the month 07-Sep-07 I have a calendar table but need the logic to determine if it is the 5th business of the month. I want to set @FifthBusinessDay to either Y or N. Can someone help? DECLARE @CurrentDate datetime DECLARE @FifthBusinessDay char(1) CREATE TABLE #Calendar ( CalDate datetime NOT NULL , BusinessDay char (1) ) INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/28/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/29/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/30/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/31/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/1/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/2/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/3/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/4/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/5/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/6/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/7/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/8/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/9/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/10/2007','Y') SELECT * FROM #Calendar SET @CurrentDate = getdate() DROP TABLE #Calendar Previous Posts In This Thread: On Friday, September 07, 2007 11:31 AM Terri wrote: 5th business day of the month I have a calendar table but need the logic to determine if it is the 5th business of the month. I want to set @FifthBusinessDay to either Y or N. Can someone help? DECLARE @CurrentDate datetime DECLARE @FifthBusinessDay char(1) CREATE TABLE #Calendar ( CalDate datetime NOT NULL , BusinessDay char (1) ) INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/28/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/29/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/30/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('8/31/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/1/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/2/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/3/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/4/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/5/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/6/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/7/2007','Y') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/8/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/9/2007','N') INSERT INTO #Calendar (CalDate,BusinessDay) VALUES ('9/10/2007','Y') SELECT * FROM #Calendar SET @CurrentDate = getdate() DROP TABLE #Calendar On Friday, September 07, 2007 12:04 PM Scott Morris wrote: Re: 5th business day of the month "Terri" <terri(a)cybernets.com> wrote in message news:fbrqsj$6lf$1(a)reader2.nmix.net... Logically, the fifth business date is the last row (by date) of the first 5 business dates of the month. Translating into pseudo-sql: select top 1 from (select top 5 order by ... asc) order by ... desc That should be enough to get you started. On Friday, September 07, 2007 12:13 PM Roy Harvey wrote: IF (SELECT COUNT(*) FROM #Calendar as A WHERE A. IF (SELECT COUNT(*) FROM #Calendar as A WHERE A.CalDate <= @CurrentDate AND A.CalDate >= DATEADD(day, 1 + DATEDIFF(day,0,@CurrentDate) - DATEPART(day,@CurrentDate), 0) AND BusinessDay = 'Y') = 5 SET @FifthBusinessDay = 'Y' ELSE SET @FifthBusinessDay = 'N' Roy Harvey Beacon Falls, CT On Fri, 7 Sep 2007 09:31:31 -0600, "Terri" <terri(a)cybernets.com> wrote: On Friday, September 07, 2007 3:53 PM Terri wrote: Thanks Roy! Thanks Roy! On Friday, September 07, 2007 9:44 PM --CELKO-- wrote: Wrong approach; do not use BOOLEAN flags in SQL. Wrong approach; do not use BOOLEAN flags in SQL. Instead Julianize the business days within the Calendar (which should be a permanent table). Find the Julian number for the first of the month; add 5 and report the minimum date. Goolg ethis group for more details. On Friday, March 19, 2010 4:58 PM Allen Whipps wrote: 5th business day with stock SQL/no function/no loops. 5th business day. This is goofy but I needed something that didn't use loops (for a user friendly system called ARGOS that just uses stock SQL) and I didn't want to add a function. So I run the report every day and add this to the where statement. Sorry about the formatting (from cut paste). If you don't get it, make up a dummy calendar with example months starting the 1st on each successive day. Then mark when the 5th business day is... you'll see the pattern. Do the same for July/Sept where the date nearest the 4th is off for July the 1st Monday of the month for Sept. WHERE 'Y' = CASE WHEN TRIM (TO_CHAR (SYSDATE, 'MONTH')) NOT IN ('JULY', 'SEPTEMBER') AND ( (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN ('FRIDAY') AND TO_CHAR (SYSDATE, 'dd') IN ('05', '06', '07') OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY') AND TO_CHAR (SYSDATE, 'dd') IN ('07')))) THEN 'Y' WHEN (TRIM (TO_CHAR (SYSDATE, 'MONTH')) IN ('JULY', 'SEPTEMBER') AND ( (TRIM (TO_CHAR (SYSDATE, 'DAY')) = 'MONDAY' AND TO_CHAR (SYSDATE, 'dd') IN ('08', '09', '10')) OR (TRIM (TO_CHAR (SYSDATE, 'DAY')) IN ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY') AND TO_CHAR (SYSDATE, 'dd') IN ('08')))) THEN 'Y' ELSE 'N' END Submitted via EggHeadCafe - Software Developer Portal of Choice WPF Circular Progress Indicator http://www.eggheadcafe.com/tutorials/aspnet/4d89b4cb-ba59-4362-ab0a-cc047643fd42/wpf-circular-progress-ind.aspx
|
Pages: 1 Prev: 5th business day with stock SQL/no function/no loops. Next: test |