Computed Column Help - 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

General SQL Server Forums

New to SQL Server Programming

Computed Column Help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-09 : 10:07:13

I am manually updating a column called targetdate in our db using the below logic providing bunch of reqno

how to create a new computed column in the existing customerinfo table and have this logic update the new column rather than me doing it manually

Please advise

Manual logic that i have been using

SET NOCOUNT ON
GO 


DECLARE @reqno bigint,@targetdate datetime,@tat int


DECLARE db_cursor CURSOR FOR 
SELECT RequestId,case
When Availabledt is null then  approvedt
When Availabledt > approvedt then availabledt
When Availabledt < approvedt then approvedt
ELSE approvedt END,TAT from CustomerInfo where reqno in (1134567)


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @reqno,@targetdate,@tat


WHILE @@FETCH_STATUS = 0   
BEGIN 

DECLARE @n as int
    DECLARE @date2 AS DateTime
    SET @n = @tat
    SET @date2 = @targetdate
    WHILE (@n > 0)
    BEGIN
        SET @date2 = DATEADD(day,1,@date2)
        WHILE DATEPART(dw,@date2) IN (1,7)
        BEGIN
            SET @date2 = DATEADD(day,1,@date2)
        END
        SET @n = @n - 1
    END
    UPDATE customerinfo
SET targetdate = @date2 where reqno = @requestno
     
     --do work here    
    
     
FETCH NEXT FROM db_cursor INTO @reqno,@targetdate,@tat
END


CLOSE db_cursor   
DEALLOCATE db_cursor

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:04:12

can you explain the logic you're using in loop for date generation in words?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-09 : 12:12:20

in this first loop

when condition n(tat)> 0 is true the targetdate or date2 variable is being updated by adding a day to it

In the second loop
when date2 day is 1 or 7 then it gets incremented by 1 more day

Hope my explanation made sense

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:24:54

why do you need a loop for that? wont below suffice?


UPDATE c
SET c.targetdate = DATEADD(dd,DATEDIFF(wk,c1.TargetDt,c1.FinalDt)*2,c1.FinalDt)
FROM customerinfo c
CROSS APPLY (SELECT case
When Availabledt is null then  approvedt
When Availabledt > approvedt then availabledt
When Availabledt < approvedt then approvedt
ELSE approvedt END AS TargetDt,
DATEADD(dd,TAT,case
When Availabledt is null then  approvedt
When Availabledt > approvedt then availabledt
When Availabledt < approvedt then approvedt
ELSE approvedt END) AS FinalDt
FROM customerinfo 
WHERE requestid= c.requestid
)c1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-09 : 12:57:17

testing the logic you gave me now.I will get back after doing some thorogh testing

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-09 : 16:13:54

it worked great.How do i use this logic for creating a new column in the customerinfo table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 21:47:53

what has this to do with creation of column? you already have a column targetdate isnt it which you're trying to update by above statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-12 : 09:01:16

we already have a column targetdt in the customerinfo table and i have to do manual updates to this column peridically
having a separate column which will automatcially update when ever there is a change in approvedt,avaiabledt,tat would elimate the manulaupdates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 09:56:51

quote:
Originally posted by jim_jim

we already have a column targetdt in the customerinfo table and i have to do manual updates to this column peridically
having a separate column which will automatcially update when ever there is a change in approvedt,avaiabledt,tat would elimate the manulaupdates

for adding new columns use syntax


ALTER TABLE <tablename> ADD <columnname> <datatype>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-12 : 10:27:28

how to make the column use the logic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 10:35:15

quote:
Originally posted by jim_jim

how to make the column use the logic

column use what logic?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-12 : 11:12:45

The new column(targetdt1) that i wanted to add to the existing table customerinfo should be a computed column which should use the logic you have provided.Instead of me running the equery everytime to update the existing targetdt column

UPDATE c
SET c.targetdate = DATEADD(dd,DATEDIFF(wk,c1.TargetDt,c1.FinalDt)*2,c1.FinalDt)
FROM customerinfo c
CROSS APPLY (SELECT case
When Availabledt is null then approvedt
When Availabledt > approvedt then availabledt
When Availabledt < approvedt then approvedt
ELSE approvedt END AS TargetDt,
DATEADD(dd,TAT,case
When Availabledt is null then approvedt
When Availabledt > approvedt then availabledt
When Availabledt < approvedt then approvedt
ELSE approvedt END) AS FinalDt
FROM customerinfo
WHERE requestid= c.requestid
)c1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 11:27:19

ok. then best thing you can do is to create a udf for this logic like


CREATE FUNCTION GetTargetDate
(
@RequestID int)
RETURNS datetime
AS
BEGIN
DECLARE @TargetDate datetime

SELECT @TargetDate = DATEADD(dd,DATEDIFF(wk,c1.TargetDt,c1.FinalDt)*2,c1.FinalDt)
FROM(SELECT case
When Availabledt is null then approvedt
When Availabledt > approvedt then availabledt
When Availabledt < approvedt then approvedt
ELSE approvedt END AS TargetDt,
DATEADD(dd,TAT,case
When Availabledt is null then approvedt
When Availabledt > approvedt then availabledt
When Availabledt < approvedt then approvedt
ELSE approvedt END) AS FinalDt
FROM customerinfo 
WHERE requestid= @RequestID
)t

RETURN @TargetDate 
END


then use it like


ALTER TABLE <tablename> ADD <columnname>  AS dbo.GetTargetDate(requestid)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-12 : 15:04:18

in my testing i found below

this worked perfectly for requests which have a single record in the customerinfo table but when there are more than 1 record for a single reqest with differant values for tat,approvedt and availabledt then the logic is failing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 15:09:55

show me a data sample where it fails. post data and explain please

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-13 : 09:03:58

I created a new column(targetdate) in customerinfo table which uses the new function and below are the results for a request in cusomterinfo table

reqno tat approvedt availabledt targetdate
146957 10 03/13/2012 04/14/2011 05/28/2012
146957 20 03/13/2012 05/14/2011 05/28/2012
146957 30 03/13/2012 06/14/2011 05/28/2012
146957 40 03/13/2012 07/14/2011 05/28/2012
146957 50 03/13/2012 08/14/2011 05/28/2012
146957 60 03/13/2012 09/14/2011 05/28/2012

All the targetdates are showing as 5/28

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-13 : 09:19:56

I added rptdesc in my cursor logic and got the update to work correctly.How to add rptdesc in the function logic you have provided

SET NOCOUNT ON
GO

DECLARE @requestid bigint,@targetdt datetime,@tat int,@rptdesc int

DECLARE db_cursor CURSOR FOR
SELECT RequestId,case
When DateAvailable is null then uwapprovedt
When DateAvailable > uwapprovedt then dateavailable
When DateAvailable < uwapprovedt then uwapprovedt
ELSE uwapprovedt END,TAT,rptdesc from selected_report where requestid in ()

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @requestid,@targetdt,@tat,@rptdesc

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @n as int
DECLARE @date2 AS DateTime
SET @n = @tat
SET @date2 = @targetdt
WHILE (@n > 0)
BEGIN
SET @date2 = DATEADD(day,1,@date2)
WHILE DATEPART(dw,@date2) IN (1,7)
BEGIN
SET @date2 = DATEADD(day,1,@date2)
END
SET @n = @n - 1
END
UPDATE SELECTED_REPORT
SET targetdt = @date2 where requestid = @requestid and rptdesc = @rptdesc

--do work here

FETCH NEXT FROM db_cursor INTO @requestid,@targetdt,@tat,@rptdesc
END

CLOSE db_cursor
DEALLOCATE db_cursor

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-03-13 : 10:38:37

Thank you so much Visakh
I altered the function logic and got what i want

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:06:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources