Recursive update - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Recursive update

Author

Topic

jj6052703
Starting Member

11 Posts

Posted - 2014-05-07 : 09:26:06

I have table 1, processlocations,(Linked to table2 by processlocationID) fields UpdateDays Yes/no, CurrentCycleField Name of Field to use in Date calcs, DestField name of field to enter result, TotalDays Field name to use in calcs for total days in system.(these are names of field to use in productlist table to get the date it was entered to this location

I have table 2
ProductList, All products are moving through processlocations, here we have date fields (names in table 1) where I want to calculate how long product has been in this process location and save number of days in to (DestField) form above.

What I am trying to perform is---
Get row from Table1 Processlocation where UpdateCycledays = yes
Use CurrentCycleField , TotalDaysField, DestField to

Loop through table2 for products at this processlocation, then
DateDiff(Day, CurrentCycleField, GETDATE(), for both current cycle and total days, then save result to DestField in product table.

Is the CTE the best way to go? if so can you help me get this laid out?

Thank you

Jeff D Jackson

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-07 : 11:23:31

Can you post sample data (in a consumable format) and expected output? Here are some links that can help you prepare that:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jj6052703
Starting Member

11 Posts

Posted - 2014-05-08 : 13:29:55

CREATE TABLE [dbo].[ProcessLocations](
[ID] [uniqueidentifier] NULL,
[ProcessLocationID] [int] IDENTITY(1,1) NOT NULL,
[UpdateCycleDays] [varchar](50) NULL,
[CurrentCycleSourceField] [varchar](50) NULL,
[UpdateDestField] [varchar](50) NULL,
[TotalDaysField] [varchar](50) NULL,
CONSTRAINT [PK_ProcessLocations] PRIMARY KEY CLUSTERED
(
[ProcessLocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ProcessLocations] ADD CONSTRAINT [DF_ProcessLocations_ID] DEFAULT (newid()) FOR [ID]
GO

ALTER TABLE [dbo].[ProcessLocations] ADD CONSTRAINT [DF_ProcessLocations_UpdateCycleDays] DEFAULT ('No') FOR [UpdateCycleDays]
GO

ALTER TABLE [dbo].[ProcessLocations] ADD CONSTRAINT [DF_ProcessLocations_UpdateCycleField] DEFAULT ('None') FOR [CurrentCycleSourceField]
GO

ALTER TABLE [dbo].[ProcessLocations] ADD CONSTRAINT [DF_ProcessLocations_UpdateDestField] DEFAULT ('None') FOR [UpdateDestField]
GO

ALTER TABLE [dbo].[ProcessLocations] ADD CONSTRAINT [DF_ProcessLocations_Location] DEFAULT ('None') FOR [TotalDaysField]
GO

INSERT INTO ProcessLocations (UpdateCycleDays, CurrentCycleSourceField, UpdateDestField, TotalDaysField)
VALUES ('Yes', 'Phase1StartDate', CurrentDaysPhase1, Phase1StartDate')

INSERT INTO ProcessLocations (UpdateCycleDays, CurrentCycleSourceField, UpdateDestField, TotalDaysField)
VALUES ('Yes', 'Phase2StartDate', CurrentDaysPhase2, Phase1StartDate')

CREATE TABLE [dbo].[ProductList](
[ID] [uniqueidentifier] NULL,
[ProcessLocationID] [int] NULL,
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Phase1StartDate] [datetime] NULL,
[Phase2StartDate] [datetime] NULL,
[Phase3StartDate] [datetime] NULL,
[TotalDays] [int] NULL,
[CurrentDaysPhase1] [numeric](18, 0) NULL,
[CurrentDaysPhase2] [numeric](18, 0) NULL,
[CurrentDaysPhase3] [numeric](18, 0) NULL,

Insert Into ProductList (Phase1StartDate) VALUES (GETDATE())

Insert Into ProductList (Phase2StartDate) VALUES (GETDATE())

Jeff D Jackson

jj6052703
Starting Member

11 Posts

Posted - 2014-05-08 : 13:42:06

The processlocationID is the driving force here. What I want to accomplish is..
For each processlocationID , get CurrentCycleSourceField Data=Phase1StartDate, get TotalDaysField Data=Phase1StartDate, get UpdateDestField Data= CurrentDaysPhase1

Loop through all products at processlocationID, Get CurrentCycleSourceField = Phase1StartDate pulled from above, DateDiff(Day,CurrentCycleSourceField, GetDate()) and update result to UpdateDestField=CurrentDaysPhase1, and the same for TotalDays Field

Short Version
For each processlocationid loop through products, use fieldnames saved in processlocations. to get start dates and calculate Days in this cycle)ProcessLocation, and then also calculate total days in system. Current cycle and total days fields in products contain result.

Jeff D Jackson

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-08 : 13:48:51

If I understand you correctly, the ProcessLocations table has the name of a column or columns in another table? If that is the case, I'd suggest that you have a bad database design and if you can fix it now, I'd do that. If not, then you are going to have to use Dynamic SQL in order to do what you want to do.
http://www.sommarskog.se/dynamic_sql.html

jj6052703
Starting Member

11 Posts

Posted - 2014-05-08 : 17:48:21

Well I will chalk up the "Bad design" comment on lack of understanding of the roots of the need here to create this query.
Short version is that now when they add a new process location, the existing stored procedures are all hard coded on case basis.
If processlocation = 4 Set xxxx = DateDiff(HardcodedField, today)

So when a new process location is added these stored procedures have to be updated manually for each case. Which sucked. Five mile long stored procedures too, messy and needed to be automated.

This all behind the scenes to any user, nothing is forward facing so the dynamic sql makes sense here. As I mentioned we already had two sucky stored procedures, one to update just for the individual processlocation which is a page the user deals with the products from. I rewrote that one, interestingly enough with Dynamic sql and it works flawlessly.
Drop in a new process location and no more requirement to hard code that case. Its just picked up by the field defintions in process location. Yaay. Add a new process, days update, no re coding required.

The one in question here needs to update for all process locations where updatecycledays=yes
The extra level here had me wondering what box this best fit in, Dynamic, correlated, cte, etc etc
These are the areas I am trying to learn in so I am not yet always sure which solution should be applied to an issue.
I think you have confirmed I was on the right track with dynamic sql. Just wasn't sure it applied here with the second level of adding in and running based on processlocation table setting updatecycledays=yes

Here is the dynamic query I wrote for the one currently working.

SET @Query = ' Update PlantList SET ' + @DestField + ' = DATEDIFF(DAY, ' + @CurrentCycleField + ', GETDATE()), TotalDays = DATEDIFF(DAY, ' + @TotalDaysField + ', GETDATE()), CurrentCycleDays = DATEDIFF(DAY, ' + @CurrentCycleField +', GETDATE()) WHERE ProcessLocationID = ' + CAST( @Location as nvarchar)

to update for all process locations would I need to Select from processlocations where updatecycledays='yes' and have update as subquery?

a little guidance on tying the 2 together would be appreciated.

Thanks

Jeff D Jackson

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-05-09 : 09:29:05

quote:
Originally posted by jj6052703

Well I will chalk up the "Bad design" comment on lack of understanding of the roots of the need here to create this query.

And i will chalk it up to http://en.wikipedia.org/wiki/First_normal_form

The database being as it is (looks too late to change it), you are stuck with Dynamic SQL.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-09 : 11:41:26

quote:
Originally posted by jj6052703

Well I will chalk up the "Bad design" comment on lack of understanding of the roots of the need here to create this query.

Unfortunately, I did understand. I was just hoping that I was misunderstanding. If the person(s) whom architected that database doesn't already have mal practice insurance, you might suggest that they pick some up.

If there is still time to change the schema, let us know. We might be able to give you some pointers in the right direction.

jj6052703
Starting Member

11 Posts

Posted - 2014-05-09 : 16:15:27

and if your an idiot, its too late also. Sadly you don't understand your just an ass.

Jeff D Jackson

jj6052703
Starting Member

11 Posts

Posted - 2014-05-09 : 16:16:38

no need to pretend you can help anyone when your obviously and 8 yr old cow tipper. Good Day Ass

Jeff D Jackson

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-09 : 17:16:56

LOL. I'm guessing you are the one that design the database then?