Another Date Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Another Date Question

Author

Topic

texasweb
Starting Member

11 Posts

Posted - 2007-11-06 : 13:54:40

I want to write a simple, if possible, piece of code that will move from one date to another, depending on the day of week, a holiday and yesterday. Let me splain....

If someone enters a date of 11-25-2007, the statement needs to figure that the compnay does not work on sunday, so subtract a day. Since that will also be another non operating day, subtract another day.

Now the fun part. Since the statment will now have the date on 11/23/2007, it will see from the query that it is a holiday. So Subtract another day until you get to the first operational day that is not 1,7, or a holiday.

I use a table that includes the DOW, date, holiday indicator, week of year, quarter, and operational indicator.

My statement does start with datefirst = 1. Setting Sunday to the first day.

The if statement i started on is becoming huge. Has anyone worked with this type of date and date formulation before?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 13:56:56

Yes.

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-06 : 19:14:33

quote:
Originally posted by Peso

Yes.

E 12°55'05.25"
N 56°04'39.16"

texasweb
Starting Member

11 Posts

Posted - 2007-11-07 : 05:50:28

ok. Would anyone help me with writing a query that is short and concise?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 06:19:43

[code]SELECT MAX(Date)
FROM Table1
WHERE Date <= @theInitialDate
AND HolidayIndicator = 0
AND OperationalIndicator BETWEEN 2 AND 6[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 06:27:22

[code]SELECT MAX(Date)
FROM Table1
WHERE Date <= @theInitialDate
AND HolidayIndicator = 0
AND OperationalIndicator = 1
AND DOW BETWEEN 2 AND 6[/code]I think you get the drift by now...

E 12°55'05.25"
N 56°04'39.16"

texasweb
Starting Member

11 Posts

Posted - 2007-11-07 : 06:49:49

Peso,

--REGULAR WORK DAY
IF (SELECT UPS_HOL_IR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE)='0'
--CHECKS FOR SATURDAY
BEGIN
IF (SELECT DOW_NR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE) = '7'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-1,@testdate))
END
--CHECKS FOR SUNDAY
IF (SELECT DOW_NR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE) = '1'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-2,@testdate))
END
--CHECKS FOR MONDAY
IF (SELECT DOW_NR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE) = '2'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-3,@testdate))
END
--RETURNS YESTERDAY
ELSE
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-1,@testdate))
END
END
--HOLIDAY
IF (SELECT UPS_HOL_IR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE)='1'
--CHECKS FOR SATURDAY THEN CHECKS FOR THE DATE RETURNED TO SEE IF IT IS A HOLIDAY
BEGIN
IF (SELECT DOW_NR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE) = '7'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-1,@testdate))
END
IF (SELECT UPS_HOL_IR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE)='1'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-1,@testdate))
END
--CHECKS FOR SUNDAY THEN CHECKS FOR THE DATE RETURNED TO SEE IF IT IS A HOLIDAY
IF (SELECT DOW_NR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE)='1'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-1,@testdate))
END
IF (SELECT UPS_HOL_IR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE) = '1'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-1,@testdate))
END
IF (SELECT DOW_NR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE) = '1'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-2,@testdate))
END

--CHECKS FOR MONDAY THEN CHECKS FOR THE DATE RETURNED TO SEE IF IT IS A HOLIDAY
IF (SELECT DOW_NR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE) = '2'
BEGIN
SET @TESTDATE = (SELECT dateadd(d,-3,@testdate))
END
END

SELECT UPS_HOL_IR FROM DMIS0013.DBO.TUPSOPD_OPERDAYS WHERE DAT_VAL_DT = @TESTDATE

This is the code that i have grown to hate. Does this make sense???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 07:05:57

No. Use the technique I posted earlier.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 07:09:39

[code]SELECT MAX(DAT_VAL_DT)
FROM DMIS0013.DBO.TUPSOPD_OPERDAYS
WHERE DAT_VAL_DT <= @TestDate
AND UPS_HOL_IR = 0
AND DOW_NR BETWEEN 2 AND 6[/code]

E 12°55'05.25"
N 56°04'39.16"

texasweb
Starting Member

11 Posts

Posted - 2007-11-07 : 07:15:13

Peso, thanks for the quick reply, here are the horns of the yak i am gored on. When i use your query, the same date as presented, is returned. If i set @testdate to 11/26, it comes back as 11/26. With my lengthy if statement, it returns 11/21, what i need. If the user goes to the report page, on the 26th, there would be no data, and the weekend is 4 days long. I know taht this complicates, but that is what i am facing.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 07:29:00

Replace "<=" with "<" ?

Do you have all days of the year in table DMIS0013.DBO.TUPSOPD_OPERDAYS ?

E 12°55'05.25"
N 56°04'39.16"

texasweb
Starting Member

11 Posts

Posted - 2007-11-07 : 07:34:23

St. Peso, Thank you. I will light a candle for you......THANK YOU that did it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources