Prev: remote database needs prefix database in sql
Next: 5th business day.
From: Allen on 19 Mar 2010 16:58
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.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Started with SQLite and Visual Studio
http://www.eggheadcafe.com/tutorials/aspnet/20f7912e-6fa7-40eb-b31b-b6f46d4f2c6a/get-started-with-sqlite-a.aspx
 | 
Pages: 1
Prev: remote database needs prefix database in sql
Next: 5th business day.