November 1, 2023 at 2:49 pm
I have some date variables which are used in the SP but one of the main table is going away and I am trying to come up with a solution so I can get the correct date. I haven't been successful so I thought I'd ask experts.
Below is the original code
declare @asofdate datetime
declare @priorasofdate datetime
declare @dayspickup int
declare @AT datetime
declate @ATD datetime
set @asofdate = ISNULL(@Asofdate, (select max(asofdate) from DB1..table where asofdate < convert(10), getdate(), 112)))
set @dayspickup = 1
set @priorasofdate = @asofdate WHILE @dayspickup > 1
Begin
set @priorasofdate = DATEADD(d, -1, @priorasofdate)
set @dayspickup = @dayspickup - 1
WHILE DATENAME(WEEKDAY, @priorasofdate) in ('Saturday','Sunday')
OR
@priorasofdate in (Select date from Holiday)
SET @priorasofdate = DATEADD(D, -1, @priorasofdate)
END
Set @AT = (select max(date) from calendar where date < @priorasofdate)
Set @ATD = (select max(date) from calendar where date < @AT)
Date column in the calendar table contains a current date in 'YYYY-MM-DD 00:00:00:000' format
and this table is going away.
So I thought I'd do something like but it didn't work. I get Null values for @AT and @Atd
declare @asofdate datetime
declare @priorasofdate datetime
declare @dayspickup int
declare @AT datetime
declate @ATD datetime
declare @mydate datetime
SET @myDate = CONVERT(VARCHAR(19), @myDate, 120) + ' 00:00:00.000'
set @asofdate = ISNULL(@Asofdate, (select max(asofdate) from DB1..table where asofdate < convert(10), getdate(), 112)))
set @dayspickup = 1
set @priorasofdate = @asofdate WHILE @dayspickup > 1
Begin
set @priorasofdate = DATEADD(d, -1, @priorasofdate)
set @dayspickup = @dayspickup - 1
WHILE DATENAME(WEEKDAY, @priorasofdate) in ('Saturday','Sunday')
OR
@priorasofdate in (Select date from Holiday)
SET @priorasofdate = DATEADD(D, -1, @priorasofdate)
END
Set @AT = (select @mydate where @mydate < @priorasofdate)
Set @ATD = not sure what I do here < @AT)
I also created a table currentdate which only has 1 record (current date) and did something like this but still NULL values
Set @AT = (select cuurentdate from cuurentdate where cuurentdate < @priorasofdate)
Set @ATD = (select cuurentdate from cuurentdate where cuurentdate < @AT)
Any help is highly appreciated.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
November 1, 2023 at 3:11 pm
Is there any chance that you can provide some sample data (in the usual CREATE TABLE / INSERT form) and simply tell us what you would like to see as a result, based on that sample data?
Code like SELECT X FROM X WHERE X < Y is confusing and makes my head ache.
Also, please note the spelling of 'current'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 1, 2023 at 4:12 pm
From ChatGPT:
It seems that you need a workaround for the "calendar" table that is going away. In your updated code, you tried using @mydate and a "currentdate" table without populating @mydate and properly setting the value in the "currentdate" table. Also, there's a typo in declaring the "currentdate" table name. To get @AT and @Atd values properly, you can try the following code:
-- Declare all variables
declare @asofdate datetime
declare @priorasofdate datetime
declare @dayspickup int
declare @AT datetime
declare @ATD datetime
declare @today datetime
-- Set the current date with '00:00:00.000'
SET @today = CAST(CAST(GETDATE() AS DATE) AS DATETIME)
set @asofdate = ISNULL(@Asofdate, (select max(asofdate) from DB1..table where asofdate < convert(10), getdate(), 112)))
set @dayspickup = 1
set @priorasofdate = @asofdate WHILE @dayspickup > 1
Begin
set @priorasofdate = DATEADD(d, -1, @priorasofdate)
set @dayspickup = @dayspickup - 1
WHILE DATENAME(WEEKDAY, @priorasofdate) in ('Saturday','Sunday')
OR
@priorasofdate in (Select date from Holiday)
SET @priorasofdate = DATEADD(D, -1, @priorasofdate)
END
-- Get @AT value
DECLARE @tempAT dateTime = @priorasofdate
WHILE @tempAT >= '1900-01-01'
BEGIN
IF DATENAME(WEEKDAY, @tempAT) NOT IN ('Saturday', 'Sunday') AND @tempAT NOT IN (SELECT date FROM Holiday)
BEGIN
SET @AT = @tempAT
BREAK
END
SET @tempAT = DATEADD(D, -1, @tempAT)
END
-- Get @ATD value
DECLARE @tempATD dateTime = @AT
WHILE @tempATD >= '1900-01-01'
BEGIN
IF DATENAME(WEEKDAY, @tempATD) NOT IN ('Saturday', 'Sunday') AND @tempATD NOT IN (SELECT date FROM Holiday)
BEGIN
SET @ATD = @tempATD
BREAK
END
SET @tempATD = DATEADD(D, -1, @tempATD)
END
-- The result should be in @AT and @ATD variables
This code assigns the current date with time '00:00:00.000' to @today variable. It keeps the original logic for calculating @asofdate and @priorasofdate. Then, it uses separate loops for finding @AT and @Atd values that exclude weekends and holidays.
There are some issues in your original code which could cause it not to work as intended:
You have a typo when declaring the variable @ATD.
You wrote "declate" instead of "declare".
calendar table:In your original code, the 'calendar' table seems to be providing dates, but you mention this table is going away, which could lead to issues with the @AT and @Atd calculations.
You mentioned the date column in the 'calendar' table is stored as 'YYYY-MM-DD 00:00:00:000'. If the table is going away, this format information is irrelevant, and you should not have any dependency with the old table.
November 1, 2023 at 7:02 pm
Not that the code is unreadable or anything like that but consider documenting your production code so people don't have to try to figure out what the intent of the code is.
Also, having a WHILE loop is going to be a performance and resource issue as will the text-based date conversions.
You've been around for a while, now... if you'd provide some "Readily Consumable" test data and what the output should look like for that test data, we might be able to help you with all of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply