Need help with the logic

Question

Need help with the logic

SSCertifiable

Points: 6609

November 1, 2023 at 2:49 pm

#4319071

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]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 2

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:

Typo in the variable declaration:
You have a typo when declaring the variable @ATD.
You wrote "declate" instead of "declare".
Using the 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.
The format of date column in the calendar table:
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.

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)