June 16, 2008 at 4:50 am
I have a BIG need to figure out an update script that will search through table MODS and checks for END_DATES that have been changed at the second level (ie. 11393.021) and the change has not taken effect down through the "like" project below it (ie. 11393.021.01,11393.021.02,11393.021.03...). The script will check all Project# at the second level to see if this is the case and make the change...
Can any one help me at all please?
Thanks in Advance.
*** ORIGINAL ***
Project Start Date End Date
11393
11393.021 01/01/07 05/01/09
11393.021.0101/01/07 12/31/09
11393.021.0201/01/07 12/31/09
11393.021.0301/01/07 12/31/09
11393.021.0401/01/07 12/31/09
11555
11555.191 08/01/07 09/01/09
11555.191.0108/01/07 09/01/09
11555.191.0208/01/07 09/01/09
11555.191.0308/01/07 09/01/09
11555.191.0408/01/07 09/01/09
*** AFTER SCRIPT ***
Project Start Date End Date
11393
11393.021 01/01/07 05/01/09
11393.021.0101/01/07 05/01/09
11393.021.0201/01/07 05/01/09
11393.021.0301/01/07 05/01/09
11393.021.0401/01/07 05/01/09
11555
11555.191 08/01/07 09/01/09
11555.191.0108/01/07 09/01/09
11555.191.0208/01/07 09/01/09
11555.191.0308/01/07 09/01/09
11555.191.0408/01/07 09/01/09
June 17, 2008 at 7:01 am
I don't understand what you want to change?
First of all you need to change the end date to include the timestamp, I would do a change to convert(VARCHAR(20), enddate, 121), it will give you YYYY-MM-DD hh:mi:ss:mmm so you know the time change in second interval.
June 17, 2008 at 7:08 am
Hello, I am sorry for the confusion.
Basically the script will run every night, it will look at the end_dates of the second level ie.
Project Start Date End Date
11393.021 01/01/07 05/01/09
it will compare it to all levels below ie.
11393.021.01 01/01/07 12/31/09
11393.021.02 01/01/07 12/31/09
11393.021.03 01/01/07 12/31/09
11393.021.04 01/01/07 12/31/09
if all the levels below are different, then make those dates the same as the level two date ie.
11393.021 01/01/07 05/01/09
11393.021.01 01/01/07 05/01/09
11393.021.02 01/01/07 05/01/09
11393.021.03 01/01/07 05/01/09
11393.021.04 01/01/07 05/01/09
Does that help any?
June 17, 2008 at 8:27 am
I'm thinking along the lines of the following:
UPDATE projects_table
SET END_DATE = A.END_DATE
FROM projects_table AS A LEFT OUTER JOIN projects_table AS B
ON B.project LIKE A.project+'%' AND
A.project<>B.project
WHERE A.END_DATE<>B.END_DATE
Someone will have to check my logic - I'm not where I can test this at the moment. Perhaps it will at least foster the right idea?
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
December 1, 2011 at 4:59 pm
I had a similar need for this query and it worked, thanks so much!
December 1, 2011 at 10:10 pm
This thread is 3 years old, but I'm glad my reply was of some value, as the prior poster never re-posted. The more we can find useful items here on the forum, the more valuable it becomes to all of us, and it's always helpful to know if the proposed solution works, as that helps sharpen one's own thinking process, and often opens the brain to new ideas and ways of thinking about things.
Steve
(aka sgmunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
December 2, 2011 at 5:45 am
I really didn't look at the date. I searched for syntax that I needed and yours worked. I wanted to say thanks! I needed to take a value from a temp table and update another table for multiple records containing variable data. Here is my stmt, it works great, finished testing last night.
UPDATE [MyHR_App_AIPGESS].[dbo].[aip_Goal_Package]
SET aip_Goal_Package.Goal_Package_Applied_Credit = [#AIP_PACKAGE_SCORES].package_score
FROM [#tempdb].[#aip_package_scores]
LEFT OUTER JOIN [MyHR_App_AIPGESS].[dbo].[aip_Goal_Package]
ON [#AIP_PACKAGE_SCORES].Goal_Package_ID = aip_Goal_Package.Goal_Package_ID
WHERE (aip_Goal_Package.Goal_Package_Status_ID = 8
OR aip_Goal_Package.Goal_Package_Status_ID = 12)
December 2, 2011 at 6:20 am
Your thanks are appreciated. Something else that may help you with SQL coding is to start using table aliases instead of fully qualifying your table names at every reference. It's also a good idea to use meaningful aliases, rather than simply an A and B or X and Y. In your specific example, it would read more easily by aliasing the permanent table as AGP, and the temp table as TAPS. Additionally, indentation of code is truly useful for helping clarify exactly what's taking place. When you post code, try to remember to use the code block symbols so the forum can do what you see here, which is how I would format your query:
UPDATE AGP
SET AGP.Goal_Package_Applied_Credit = TAPS.package_score
FROM [#tempdb].[#aip_package_scores] AS TAPS
LEFT OUTER JOIN [MyHR_App_AIPGESS].[dbo].[aip_Goal_Package] AS AGP
ON TAPS.Goal_Package_ID = AGP.Goal_Package_ID
WHERE AGP.Goal_Package_Status_ID IN (8, 12)
Steve
(aka sgmunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply