Update record in same table 1

[sanctified]

I have a table which self-joins to itself by the following fields: SiteID and MergeID

So I will have:

SiteID MergeID StartDate Notice MergeStatus
127 128 01/12/2006 7
128 127 02/12/2006 7
My asp application will pass in the siteid for one of the records. I will need to update the MergeStatus field using the following definition: If the duedate (startdate - notice) > today then set the MergeStatus to 1 for the OTHER record i.e the mergeid that matches the sitedid being passed in otherwise set the mergestaus for the siteid being passed in to 2.
Any ideas how to form the SP?

Regards

 

[bborissov]

What is Notice? Days, Months, Years, Minutes, Seconds? (assuming days)
Also what type is StartDate - DateTime or something else?
(assumnig DateTime w/o care of TimePortion)

I am not sure if I undestrood you questin right, but try this:

CREATE TABLE #test (SiteId Int, MergeId Int, StartDate datetime, Notice int, MergeStatus Int)
INSERT INTO #Test VALUES (128, 127, '20061201', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060701', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060501', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060401', 7, 0)

DECLARE @SiteId int
DECLARE @toDay datetime
-- get rid of Time portion in GetDate()
SET @today = CAST(CONVERT(varchar(10), GetDate(), 101) as DateTime)
SET @SiteId = 127
UPDATE #test SET MergeStatus = 
                   CASE WHEN DateAdd(dd, -Notice, StartDate) > @today THEN 1
                   ELSE 2 END
       WHERE (MergeID = @siteId)
SELECT * from #Test
drop table #test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

Borislave,
Your assumptions are correct and you've understood the question correctly. Looking at the syntax, I think this will work.

Thanks very much!!

 

[sanctified]

Hi Borislav
By implementing the code, I've realised that there is something missing. BTW There will only ever be TWO merged records as shown below:


So I will have:

SiteID MergeID StartDate Notice MergeStatus
127 128 01/12/2006 7 1
128 127 02/12/2006 7 2
My asp application will pass in the siteid for one of the records. So for example @siteID 127.
Algorithm
if (startdate - notice) > today for record where siteid = @siteid then set mergestatus to 2 where MERGEID = @siteid

if (startdate - notice) < = today for record where siteid = @siteid, then set mergestatus to 1 where siteid = @siteid.

The mergestatus for BOTH records should be updated. I would expect to see a 1 or 2 in either records


I tried doing a nested CASE statement but kept getting errors. The key is I need 2 different where clause in the CASE statement - where MERGEID = @siteid OR where siteid = @siteid I can't find an example of this.

Thanks

 

[bborissov]

What happens to records when MergeId = @SiteId and
(startdate - notice) <= today?
And other records when SiteId = @SiteId and (startdate - notice) > today?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

I was thinking that too. Set them to the opposites (for now). It's the users requirements which they have not specified what needs to happen here.

 

[sanctified]

It's the syntax I'm after - for multiple WHERE clauses in a CASE statement.

Thanks

 

[bborissov]

CREATE TABLE #test (SiteId Int, MergeId Int, StartDate datetime, Notice int, MergeStatus Int)
INSERT INTO #Test VALUES (128, 127, '20061201', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060701', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060501', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060401', 7, 0)

DECLARE @SiteId int
DECLARE @toDay datetime
-- get rid of Time portion in GetDate()
SET @today = CAST(CONVERT(varchar(10), GetDate(), 101) as DateTime)
SET @SiteId = 127
UPDATE #test SET MergeStatus =
                   CASE WHEN DateAdd(dd, -Notice, StartDate) > @today AND SiteId = @siteId THEN 1
                   CASE WHEN DateAdd(dd, -Notice, StartDate) > @today AND MergeId = @siteId THEN 2
                   ELSE -MergeStatus END
       WHERE (MergeID = @siteId OR SiteId = @siteId)
SELECT * from #Test
drop table #test
[code]
(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

I'll give that a go and let you know how I get on.

Thanks for your help Borislav

 

[sanctified]

Hi Borislav,
I get incorrect syntax near the keyword CASE for the second CASE statement.
cheers

 

[bborissov]

Sorry it must be:

CREATE TABLE #test (SiteId Int, MergeId Int, StartDate datetime, Notice int, MergeStatus Int)
INSERT INTO #Test VALUES (128, 127, '20061201', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060701', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060501', 7, 0)
INSERT INTO #Test VALUES (128, 127, '20060401', 7, 0)

DECLARE @SiteId int
DECLARE @toDay datetime
-- get rid of Time portion in GetDate()
SET @today = CAST(CONVERT(varchar(10), GetDate(), 101) as DateTime)
SET @SiteId = 127
UPDATE #test SET MergeStatus =
                   CASE WHEN DateAdd(dd, -Notice, StartDate) > @today AND SiteId = @siteId  THEN 1
                        WHEN DateAdd(dd, -Notice, StartDate) > @today AND MergeId = @siteId THEN 2
                   ELSE -MergeStatus END
       WHERE (MergeID = @siteId OR SiteId = @siteId)
SELECT * from #Test
drop table #test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

I'll give that a go.
Thanks for the speedy response!!

 

[sanctified]

It's still only updating one of the records.
Here's the SP in it's entirety.


CREATE PROCEDURE dbo.pbay_LoadMergeConsent
@myID int = 0
AS

declare @rows int
declare @error int

declare @suspensionstart datetime
declare @USRN varchar(12)
declare @Notice int

select @suspensionstart = s.SuspensionStart,
@USRN = s.usrn,
@Notice = rd.refvalue

from Suspension su inner join
SiteData s on su.SuspensionID = s.SuspensionID
INNER JOIN RefData rd ON s.NotReq = rd.RefCode
where s.SiteID = @myID or s.Mergeid = @myID
and s.Archived = 'N'

select @error=@@error,@rows=@@rowcount

if @error <> 0
begin
return @error
end

if @rows = 0
begin
return 'No rows'
end

UPDATE sitedata
SET MergeStatus =
CASE WHEN DateAdd(dd, -@Notice, @suspensionStart) >getdate() AND SiteId = @myID and usrn = @USRN THEN 1
WHEN DateAdd(dd, -@Notice, @suspensionStart) > getdate() AND MergeId = @myID and usrn=@USRN THEN 2
ELSE 1 END
WHERE (MergeID = @myId or siteid = @myID)

 

[bborissov]

When you run:

DECLARE @myId int
SET @myId = (some value you usualy pass to SP)
select s.SuspensionStart,
       s.usrn,
      rd.refvalue
from Suspension su
    inner join SiteData s on su.SuspensionID = s.SuspensionID
    INNER JOIN RefData rd ON s.NotReq = rd.RefCode
where s.SiteID = @myID or s.Mergeid = @myID
and s.Archived = 'N'

how many records you get?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

One record.

 

[sanctified]

Ooops no two records are returned. Apologies

 

[bborissov]

Than change this select to returns only one value. I am not sure what are the exact values of
@suspensionstart,
@USRN,
@Notice

must be from last selected record but I am not sure.
You could check them if you do this

DECLARE @myID int
declare @suspensionstart datetime
declare @USRN varchar(12)
declare @Notice int

SET @myId = (some value you usualy pass to SP)

select @suspensionstart = s.SuspensionStart,
       @USRN = s.usrn,
      @Notice = rd.refvalue
from Suspension su inner join
    SiteData s on su.SuspensionID = s.SuspensionID
INNER JOIN RefData rd ON s.NotReq = rd.RefCode
where s.SiteID = @myID or s.Mergeid = @myID
and s.Archived = 'N'

print @Notice
print @USRN
print @suspensionStart

And see if values of these variableas are correct.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

Initially, I had where s.SiteID = @myID (which returns ONE record) I added or s.Mergeid = @myID so that it will return the two records I want to update. I've now changed it back to just where s.SiteID = @myID so that it returns ONE record as you suggets.

The record being returned is the record where the siteID =@myID. @myID is paased in from my asp page. So yes, the last selected record. SiteID is a unique field.

Thanks Borislav

 

[bborissov]

You are welcome

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanctified]

It's still not updating BOTH records.
This is the criteria in english.
if today > due date for date 1 then proceed with print sign on due date for date 2
if today = or < due date for date 1, then print sign on due date for date 1 but do not print sign on due date for date 2.
Variables:
Due Date = Suspension Start - Notice
Date 1 refers to the due date of the record passed in(where sitedid=@myid)
Date 2 refers to the due date of the other matched record (where mergeid - @myid)
Merge Status field Value:
Value 0 – no merge DEFAULT
Value 1 – merged early date
Value 2 – merged later date.
The 2 records to be updated will be returned by the following mergeid = @myid OR siteid=@myid.

I think we're nearly there...
Thanks