asked on

conditional date check

i closed this inquiry too early. bhess1 directed me well, and I have chosen how to move forward... I'm just having a hard time getting it together. very simply said, i need revise my procedure to only allow updates of the given symbol to occur once daily. i have created a LastRun table just to keep track of previously executed updates. LastRun has only 3 fields: Date, Symbol, Amount

in the proc, i just need to check the max(date) of @symbol in LastRun. if it's within the current 24 hrs, i issue a message an dot perform the updated. if it's outside of the current day, i perform the update, and update the LastRun.date attribute to the current time. this is pretty important, any assistance truly is appreciated.

this is the old proc:
create proc procname
(
@symbol varchar(32),
@adjust decimal(18,8)
)
AS
UPDATE table
SET price = price + @adjust
WHERE timefield = (convert(char(10),getdate()-1,101)) + ' 17:00'
AND symbol = @symbol

this is the new proc:
Create proc dbo.PROCNAME
(
@symbol varchar(32),
@adjust decimal(18,8)
)
AS

DECLARE @CurrDt smalldatetime
DECLARE @LastRun smalldatetime

SET @CurrDt = Convert(varchar(10), current_timestamp, 101)

SELECT @LastRun = MAX(AdjDate) <<<<<<most recently inserted symbol date
FROM LastRun
WHERE Symbol = @symbol

IF (SELECT DATEDIFF(day,@currDt,@LastRun)) <=1
PRINT 'The given symbol has already been adjusted today. '
ELSE
IF (SELECT DATEDIFF(day,@currDt,@LastRun)) >1 <<not today
UPDATE table
SET price = price + @adjust
WHERE timefield = (convert(char(10),getdate()-1,101)) + ' 17:00'
AND symbol = @symbol

UPDATE LastRun
SET AdjDate = getdate()
WHERE Symbol = @symbol

dbaSQL

ASKER

is anyone able to advise?

Jim Horn

Exactly what part of this is not working?

>to only allow updates of the given symbol to occur once daily
afaik it is not possible to deny any update based on a timeframe like this...

dbaSQL

ASKER

well, my lastrun update wasn't being performed, nor was the price adjustment being made when date older than today. i am trying to change it to this, what do you think?

Create proc dbo.procname
(
@symbol varchar(32),
@adjust decimal(18,8)
)
AS

IF (SELECT MAX(AdjDate) FROM LastRun WHERE symbol = @symbol) >= left(getdate()-0, 11)
BEGIN
PRINT 'The given symbol has already been adjusted today. '
END
ELSE
BEGIN
UPDATE table
SET price = price + @adjust
WHERE timefield = (convert(char(10),getdate()-1,101)) + ' 17:00'
AND symbol = @symbol

UPDATE dbo.LastRun
SET AdjDate = GETDATE()
WHERE AdjSymbol = @symbol
END

GO

Lowfatspread

more like...

but
do you reaally want to "Print" a return statement...
does table contain multiple rows per symbol?
what are you doing with timefield...
(do you really need the new last run table if you've already got timefield?)

Create proc dbo.PROCNAME
(
@symbol varchar(32),
@adjust decimal(18,8)
)
AS
Set nocount on
DECLARE @CurrDt smalldatetime
DECLARE @LastRun smalldatetime

SET @CurrDt = Convert(varchar(10), current_timestamp, 101)

SELECT @LastRun = MAX(AdjDate) <<<<<<most recently inserted symbol date
FROM LastRun
WHERE Symbol = @symbol

IF (SELECT DATEDIFF(day,@currDt,@LastRun)) <=1
PRINT 'The given symbol has already been adjusted today. '
ELSE
IF (SELECT DATEDIFF(day,@currDt,@LastRun)) >1 <<not today
begin
UPDATE table
SET price = price + @adjust
, timefield = (convert(char(10),currdt-1,101)) + ' 17:00'
WHERE symbol = @symbol

UPDATE LastRun
SET AdjDate = currdt
WHERE Symbol = @symbol
end

return
go

dbaSQL

ASKER

>>(do you really need the new last run table if you've already got timefield?)
unfortunately, yes, lowfat. i said precisely the same, but i am being pushed to do this history table

if symbol is there (LastRun table), current day, update nothing, issue a msg .... 'the given symbol has already been adjusted today.'

if symbol is not there (LastRun table), current day, update price in the data table AND write audit of this update to LastRun table. (date, symbol, amount)

lowfat, are you able to advise?

Brendt Hess

With the new parameters for what you need to do, let me see what I can do.

First, let's use a SELECT to return the notification. Or, better yet, use an integer RETURN value and interpret the return value in the application code that calls this.

Then, let's change the previous query to use a historical log.

First, let's revise the table so that it reflects the new values:

CREATE TABLE SymbolUpdateLog (
Symbol varchar(32) NOT NULL,
UpdateDate smalldatetime NOT NULL,
Adjust decimal(18,8) NOT NULL)
)

CREATE UNIQUE INDEX ix_SymbolDate
ON SymbolUpdateRunLog (Symbol, UpdateDate)

Now, let's revise the proc to do what you need:

Create proc dbo.procname
(
@symbol varchar(32),
@adjust decimal(18,8)
)
AS

DECLARE @CurrDt SmallDateTime

SET @CurrDt = Convert(varchar(10), current_timestamp, 101)

IF (SELECT MAX(AdjDate) FROM SymbolUpdateLog WHERE symbol = @symbol) >= @CurrDt
BEGIN
RETURN 1 -- Already updated
-- SELECT 'The given symbol has already been adjusted today.' AS Status -- Return Status as recordset
-- PRINT 'The given symbol has already been adjusted today. '
END
ELSE
BEGIN
UPDATE table
SET price = price + @adjust
WHERE timefield = (convert(char(10),getdate()-1,101)) + ' 17:00' -- is this a datetime? Or varchar?
AND symbol = @symbol

INSERT INTO SymbolUpdateLog (Symbol, UpdateDate, Adjust)
VALUES (@Symbol, @CurrDt, @adjust)

-- SELECT 'Update Successful' AS Status
RETURN 0
END
GO

Marc Allain

.
.
.
IF (SELECT DATEDIFF(day,@currDt,@LastRun)) >1 <<not today
UPDATE table
SET price = price + @adjust
WHERE

-- do you need this in the WHERE clause of your update? Is there more than one record for each symbol in 'Table' , and if so, do any match this criteria?
timefield = (convert(char(10),getdate()-1,101)) + ' 17:00'

AND symbol = @symbol

Marc

dbaSQL

ASKER

bhess, i was just now writing in the closed inquiry about this one. i know it's not ideal to work two inquiries, same topic.

is this too simplified:

IF EXISTS (SELECT 1 FROM dbo.LastRun WHERE adjsymbol = @symbol AND adjDate >= left(getdate()-0, 11))
BEGIN
PRINT 'The given symbol has already been adjusted today. '
END
ELSE
BEGIN
UPDATE table
SET price = price + @adjust
WHERE timefield = (convert(char(10),getdate()-1,101)) + ' 17:00'
AND symbol = @symbol

INSERT dbo.LastRun (AdjDate, AdjSymbol,AdjAmount)
SELECT CURRENT_TIMESTAMP,@symbol,@adjust

END

Brendt Hess

No, it isn't too simplified. It looks as if it does everything you need.

Don't forget an index on AdjSymbol + AdjDate in the log file. And you might consider adding a field to save the user that performed the update. As long as you are logging all calls to this, save that bit of info as well. Then, return your status such that, if the update is complete already, you return the username and datetime of the update, e.g.:

IF EXISTS (SELECT * FROM dbo.LastRun WHERE adjsymbol = @symbol AND adjDate >= left(getdate(), 11))
BEGIN
DECLARE @Updated smalldatetime
DECLARE @UpdatedBy varchar(128)

SELECT TOP 1 @Updated = AdjDate,
@UpdatedBy = AdjBy
FROM dbo.LastRun
WHERE adjsymbol = @symbol
AND adjDate >= left(getdate(), 11
ORDER BY AdjDate Desc

-- This should really be a SELECT statement, so the record can be consumed by the calling application.
PRINT 'The given symbol has already been adjusted today by ' + @UpdatedBy + ' at ' + Convert(varchar(20), @Updated, 100) + '.'
END

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

dbaSQL

ASKER

select instead of print, yes?

Brendt Hess

Yes, and you should probably consider returning a Success value, so the application always has data to consume. For a SELECT, this would look like:

SELECT 'The given symbol has already been adjusted today by ' + @UpdatedBy + ' at ' + Convert(varchar(20), @Updated, 100) + '.' AS StatusMsg

Brendt Hess

Success could be as simple as:

SELECT 'Done' As StatusMsg

dbaSQL

ASKER

thank you, bhess. i think i just let this one go a little nuts on me. maybe time to step back from the sql table for the day.

thanks again.
i do appreciate your input

Brendt Hess

Take a break. It sounds like you earned it.