Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

Update field in order

I have one table which contains one or multiple entries with dates.
I have another table that has ONE field for ONE date.

I am running a query that will take the date from the first table and copy it over to the second table. When there is only one record on the first table its a one to one relation, so no issue there, but if there is more than one record on the first table when I run the queries then the second table first is updated with the first value and then when the query continues it is updated with the second value.

I need to copy the most recent date, which sometimes may be the first record and not the second.

For example. Let's say they are school dates. The first table has the dates in which a person graduated, so they may have 01/01/2000 and then 05/03/2003 and then 09/18/1998
The second table has a field to have the most recent graduation date. I hope I start to make sense, you can see where this is going.

Below are all my scripts, which will copy records from table A to table B, they need to match a certain ID, but the first scripts may have a more recent date and the scripts that follow will overwrite whatever the scripts at the top did. So I need to somehow modify this queries and perhaps have one query that will update the target field with the most recent of the dates on table 1.

table 1 is: BlueDotAdditionalDocs
date field 1 is: a.Filedon

table 2 is: Users
date field 2 is: prioritydate

-- copy exp date of FB-2A Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 593




-- copy exp date of FB-2B Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 608



-- copy exp date of FB-3 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 609



-- copy exp date of FB-4 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 610



-- copy exp date of EB-1 OR Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 611



-- copy exp date of EB-1 EA Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 612



-- copy exp date of EB-1 MM Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 613




-- copy exp date of EB-2 NIW Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 614




-- copy exp date of EB-2 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 615




-- copy exp date of EB-3 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 616




-- copy exp date of EB-4 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 617




-- copy exp date of EB-5 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 618




-- copy exp date of Sched A Workers Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 619




-- copy exp date of Other Workers Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 620




-- copy exp date of Religious Workers Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 621



-- copy exp date of Targeted EA/RC Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 622



-- copy exp date of FB-1 Priority Date

update u
set prioritydate = a.Filedon
from users u join BlueDotAdditionalDocs a
on u.userid = a.userid
where a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 624

Open in new window


 I tried the query below but I am not sure if this idea would work and also I get an error in the order clause.

-- Update dates by most recent -- 

-- Update dates by most recent -- 

UPDATE  u
SET     prioritydate = a.Filedon
FROM    Users u
        JOIN BlueDotAdditionalDocs a ON u.UserId = a.UserId
WHERE   a.docsection = 'contact'
        AND a.Docurrent = 1 AND a.filedon IS NOT NULL
        AND ( a.CatalogId = 593
              OR a.CatalogId = 608
              OR a.CatalogId = 609
              OR a.CatalogId = 610
              OR a.CatalogId = 611
              OR a.CatalogId = 612
              OR a.CatalogId = 613
              OR a.CatalogId = 614
              OR a.CatalogId = 615
              OR a.CatalogId = 616
              OR a.CatalogId = 617
              OR a.CatalogId = 618
              OR a.CatalogId = 619
              OR a.CatalogId = 620
              OR a.CatalogId = 621
              OR a.CatalogId = 622
              OR a.CatalogId = 624
            )
			
			ORDER BY a.filedon ASC;

Open in new window

Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India image

I am trying to work out something for you. What version of SQL Server are you using (2008 or 2012 and above)?
Here's a prototype that has been tested on SQL 2008 and 2012.

Basically, the crux of the approach is the use of the CTE and the ROW_NUMBER() windowing function. What I ask the ROW_NUMBER() to do is to number the rows in the descending order of the Catalog Id for each user. Idea is to assign a row number of 1 to the max. catalog Id available for a particular user.

Let me know if it works, or if you need any further assistance.

DECLARE @users TABLE (UserId       INT  NOT NULL,
                      prioritydate DATE     NULL
                     );

DECLARE @BlueDotAdditionalDocs TABLE (Id         INT         NOT NULL IDENTITY (1,1),
                                      UserId     INT         NOT NULL,
                                      CatalogId  INT             NULL,
                                      Docurrent  BIT             NULL,
                                      docsection VARCHAR(20)     NULL,
                                      filedon    DATE            NULL
                                     );

--Some Users
INSERT INTO @users (UserId)
VALUES (1), (2), (3), (5), (8);

--Some Documents, valid and invalid
INSERT @BlueDotAdditionalDocs (UserId, CatalogId, Docurrent, docsection, filedon)
VALUES (1, 593, 1,   'contact', '2016-01-01'), --Effective
       (1, 609, 0,   'contact', '2016-01-25'),
       (1, 618, 1, 'something', '2016-02-02'),
       (2, 593, 1,   'contact', '2016-02-01'),
       (2, 609, 0,   'contact', '2016-02-25'),
       (2, 618, 1,   'contact', '2016-03-02'), --Effective
       (3, 593, 1,   'contact', '2016-02-10'),
       (3, 609, 1,   'contact', '2016-02-15'),
       (3, 618, 1,   'contact', '2016-03-20'), --Effective
       (5, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 01)
       (5, 617, 1,   'contact', '2016-06-15'),
       (5, 610, 1,   'contact', '2016-05-20'),
       (8, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 02)
       (8, 617, 1,   'contact', '2016-09-15'), --NOTE: Ignored due to lower CatalogId
       (8, 610, 1,   'contact', '2016-09-10');

/* Debug Point */
--SELECT a.UserId,
--        a.CatalogId,
--        a.Docurrent,
--        a.docsection,
--        a.filedon,
--        ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
--FROM @BlueDotAdditionalDocs AS a
--WHERE a.docsection = 'contact'
--    AND a.Docurrent = 1 ;


--Perform the update
;WITH BlueDotAdditionalDocsByPriority (UserId,
                                       filedon,
                                       PriorityOrder
                                      )
AS (
    SELECT a.UserId,
           --a.CatalogId,
           --a.Docurrent,
           --a.docsection,
           a.filedon,
           ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
    FROM @BlueDotAdditionalDocs AS a
    WHERE a.docsection = 'contact'
      AND a.Docurrent = 1
   )
UPDATE u
SET u.prioritydate = bdap.filedon
FROM @users AS u
INNER JOIN BlueDotAdditionalDocsByPriority AS bdap ON u.UserId = bdap.UserId
WHERE bdap.PriorityOrder = 1;

--Check the update
SELECT u.UserId, 
       u.prioritydate
FROM @users AS u;

/* RESULTS
UserId      prioritydate
----------- ------------
1           2016-01-01
2           2016-03-02
3           2016-03-20
5           2016-08-10
8           2016-08-10
*/

Open in new window

Avatar of Aleks

ASKER

I am using 2008R2. I will test this. Seems more complex than I previously anticipated :$
Thanks. Actually a simpler approach just came to mind. I'm on my evening commute, but will prototype it later this evening and let you know.
Avatar of Aleks

ASKER

Thx
UPDATE u
SET prioritydate = b.Filedon
FROM users u
CROSS APPLY (
    SELECT TOP (1) *
    FROM BlueDotAdditionalDocs bdad
    WHERE bdad.userid = u.userid
    ORDER BY bdad.Filedon DESC
) AS b
WHERE a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 593

Btw, an index on  BlueDotAdditionalDocs keyed on ( userid, Filedon ) could speed up this query considerably.
Avatar of Aleks

ASKER

How about all the other IDs?
This is a one time data import.
Sorry, I should have explicitly stated it, but you should specify a join or a list of ids in the CROSS APPLY query.  I also didn't have time before to provide specific details.  But now I can:

IF OBJECT_ID('tempdb.dbo.#doc_criteria') IS NOT NULL
    DROP TABLE #doc_criteria
SELECT TOP (0) docsection, Docurrent, CatalogID, CAST('' AS varchar(200)) AS description
INTO #doc_critera
FROM BlueDotAdditionalDocs

CREATE UNIQUE CLUSTERED INDEX doc_criteria__CL ON #doc_criteria ( docsection, CatalogID, Docurrent ) WITH ( FILLFACTOR = 100 );

INSERT INTO #doc_criteria
VALUES ('contact', 1, 593, 'FB-2A Priority Date'),
       ('contact', 1, 608, 'FB-2B Priority Date'),
       ('contact', 1, 609, 'FB-3 Priority Date'),
       ('contact', 1, 610, 'FB-4 Priority Date'),
       ('contact', 1, 611, 'EB-1 OR Priority Date'),
       ('contact', 1, 612, 'EB-1 EA Priority Date') --,...add_rest_of_valid_doc_values_here...

UPDATE u
SET prioritydate = b.Filedon
FROM users u
CROSS APPLY (
    SELECT TOP (1) *
    FROM BlueDotAdditionalDocs bdad
    INNER JOIN #doc_criteria dc ON
        dc.docsection = bdad.docsection AND
        dc.Docurrent = bdad.Docurrent AND
        dc.CatalogID = bdad.CatalogID
    WHERE bdad = u.userid
    ORDER BY bdad.Filedon DESC
) AS b
The simpler approach I was talking about was using trivial/classic aggregation operators to identify the "filedon" date in effect for any given user. Once this set is available, updating the users table with the date takes a simple join.

The script below has both the options in it. It uses temporary objects and hence you can run it on your development environment as much as you like - with your custom test data to cross-verify.

DECLARE @users TABLE (UserId       INT  NOT NULL,
                      prioritydate DATE     NULL
                     );

DECLARE @BlueDotAdditionalDocs TABLE (Id         INT         NOT NULL IDENTITY (1,1),
                                      UserId     INT         NOT NULL,
                                      CatalogId  INT             NULL,
                                      Docurrent  BIT             NULL,
                                      docsection VARCHAR(20)     NULL,
                                      filedon    DATE            NULL
                                     );

--Some Users
INSERT INTO @users (UserId)
VALUES (1), (2), (3), (5), (8);

--Some Documents, valid and invalid
INSERT @BlueDotAdditionalDocs (UserId, CatalogId, Docurrent, docsection, filedon)
VALUES (1, 593, 1,   'contact', '2016-01-01'), --Effective
       (1, 609, 0,   'contact', '2016-01-25'),
       (1, 618, 1, 'something', '2016-02-02'),
       (2, 593, 1,   'contact', '2016-02-01'),
       (2, 609, 0,   'contact', '2016-02-25'),
       (2, 618, 1,   'contact', '2016-03-02'), --Effective
       (3, 593, 1,   'contact', '2016-02-10'),
       (3, 609, 1,   'contact', '2016-02-15'),
       (3, 618, 1,   'contact', '2016-03-20'), --Effective
       (5, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 01)
       (5, 617, 1,   'contact', '2016-06-15'),
       (5, 610, 1,   'contact', '2016-05-20'),
       (8, 624, 1,   'contact', '2016-08-10'), --Effective (Special case 02)
       (8, 617, 1,   'contact', '2016-09-15'), --NOTE: Ignored due to lower CatalogId
       (8, 610, 1,   'contact', '2016-09-10');


/** OPTION 01: Using Windowing Function to identify effective "filedon" date **/

/* Debug Point */
--SELECT a.UserId,
--        a.CatalogId,
--        a.Docurrent,
--        a.docsection,
--        a.filedon,
--        ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
--FROM @BlueDotAdditionalDocs AS a
--WHERE a.docsection = 'contact'
--    AND a.Docurrent = 1 ;


--Perform the update
;WITH BlueDotAdditionalDocsByPriority (UserId,
                                       filedon,
                                       PriorityOrder
                                      )
AS (
    SELECT a.UserId,
           --a.CatalogId,
           --a.Docurrent,
           --a.docsection,
           a.filedon,
           ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
    FROM @BlueDotAdditionalDocs AS a
    WHERE a.docsection = 'contact'
      AND a.Docurrent = 1
   )
UPDATE u
SET u.prioritydate = bdap.filedon
FROM @users AS u
INNER JOIN BlueDotAdditionalDocsByPriority AS bdap ON u.UserId = bdap.UserId
WHERE bdap.PriorityOrder = 1;

--Check the update
SELECT u.UserId, 
       u.prioritydate
FROM @users AS u;

/* RESULTS
UserId      prioritydate
----------- ------------
1           2016-01-01
2           2016-03-02
3           2016-03-20
5           2016-08-10
8           2016-08-10
*/


/** OPTION 02: Use classic aggregations to identify effective "filedon" date **/
--Reset the test data
UPDATE u
SET u.prioritydate = NULL
FROM @users AS u;

;WITH LastCatalogInEffectByUser (UserId, CatalogIdInEffect)
AS (SELECT a.UserId,
		   MAX(a.CatalogId) AS CatalogIdInEffect
	FROM @BlueDotAdditionalDocs AS a
	WHERE a.docsection = 'contact'
	  AND a.Docurrent = 1
	GROUP BY a.UserId
	)
UPDATE u
SET u.prioritydate = a.filedon
FROM @users AS u
INNER JOIN @BlueDotAdditionalDocs AS a ON u.UserId = a.UserId
INNER JOIN LastCatalogInEffectByUser AS lceu ON a.UserId = lceu.UserId
                                            AND a.CatalogId = lceu.CatalogIdInEffect;


--Check the update
SELECT u.UserId, 
       u.prioritydate
FROM @users AS u;

/* RESULTS
UserId      prioritydate
----------- ------------
1           2016-01-01
2           2016-03-02
3           2016-03-20
5           2016-08-10
8           2016-08-10
*/

Open in new window

Avatar of Aleks

ASKER

I ran the above and got a lot of errors, some had hardcoded numbers so I am a bit confused. Is there a way to clean it up so that I can just run the part of the query that should copy the dates?
Avatar of Aleks

ASKER

From Scotts code it doesn't account for all the other IDs

UPDATE u
SET prioritydate = b.Filedon
FROM users u
CROSS APPLY (
    SELECT TOP (1) *
    FROM BlueDotAdditionalDocs bdad
    WHERE bdad.userid = u.userid
    ORDER BY bdad.Filedon DESC
) AS b
WHERE a.docsection = 'contact'
and a.Docurrent = 1
and a.CatalogID = 593

Open in new window

If possible, can you please share the errors?
Avatar of Aleks

ASKER

Well there is code that inserts data you entered, such as:


--Some Documents, valid and invalid
INSERT @BlueDotAdditionalDocs (UserId, CatalogId, Docurrent, docsection, filedon)
VALUES (1, 593, 1,   'contact', '2016-01-01'), --Effective
       (1, 609, 0,   'contact', '2016-01-25'),
       (1, 618, 1, 'something', '2016-02-02'),
       (2, 593, 1,   'contact', '2016-02-01'),
       (2, 609, 0,   'contact', '2016-02-25'),
       (2, 618, 1,   'contact', '2016-03-02'), --Effective

Those are not values from my database so I did not run a lot of stuff in that script.
Yes, the script I had is intended to be a demo script that needs to be adapted to your database design. Instead of tables, I used table variables so that I could populate them with test data and run some tests.

In the queries below, I have removed the use of table variables and used physical tables (with names from your original example in the question).

Option 01: Using Windowing functions:
/** OPTION 01: Using Windowing Function to identify effective "filedon" date **/
--Perform the update
;WITH BlueDotAdditionalDocsByPriority (UserId,
                                       filedon,
                                       PriorityOrder
                                      )
AS (
    SELECT a.UserId,
           --a.CatalogId,
           --a.Docurrent,
           --a.docsection,
           a.filedon,
           ROW_NUMBER() OVER (PARTITION BY a.UserId ORDER BY a.CatalogId DESC) AS PriorityOrder
    FROM dbo.BlueDotAdditionalDocs AS a
    WHERE a.docsection = 'contact'
      AND a.Docurrent = 1
   )
UPDATE u
SET u.prioritydate = bdap.filedon
FROM dbo.users AS u
INNER JOIN BlueDotAdditionalDocsByPriority AS bdap ON u.UserId = bdap.UserId
WHERE bdap.PriorityOrder = 1;

Open in new window


Option 02: Conventional method to identify effective "filedon" date:
;WITH LastCatalogInEffectByUser (UserId, CatalogIdInEffect)
AS (SELECT a.UserId,
		   MAX(a.CatalogId) AS CatalogIdInEffect
	FROM dbo.BlueDotAdditionalDocs AS a
	WHERE a.docsection = 'contact'
	  AND a.Docurrent = 1
	GROUP BY a.UserId
	)
UPDATE u
SET u.prioritydate = a.filedon
FROM dbo.users AS u
INNER JOIN dbo.BlueDotAdditionalDocs AS a ON u.UserId = a.UserId
INNER JOIN LastCatalogInEffectByUser AS lceu ON a.UserId = lceu.UserId
                                            AND a.CatalogId = lceu.CatalogIdInEffect;

Open in new window

Avatar of Aleks

ASKER

I dont see the ID's of the specific catalogs that should be used. there are other ID's that are not relevant.

This is my initial query which includes ONLY the IDs that should be compared.

UPDATE  u
SET     prioritydate = a.Filedon
FROM    Users u
        JOIN BlueDotAdditionalDocs a ON u.UserId = a.UserId
WHERE   a.docsection = 'contact'
        AND a.Docurrent = 1 AND a.filedon IS NOT NULL
        AND ( a.CatalogId = 593
              OR a.CatalogId = 608
              OR a.CatalogId = 609
              OR a.CatalogId = 610
              OR a.CatalogId = 611
              OR a.CatalogId = 612
              OR a.CatalogId = 613
              OR a.CatalogId = 614
              OR a.CatalogId = 615
              OR a.CatalogId = 616
              OR a.CatalogId = 617
              OR a.CatalogId = 618
              OR a.CatalogId = 619
              OR a.CatalogId = 620
              OR a.CatalogId = 621
              OR a.CatalogId = 622
              OR a.CatalogId = 624
            )
			
			ORDER BY a.filedon DESC;

Open in new window

From Scotts code it doesn't account for all the other IDs

Look at the updated code I posted.  If you want to use a list of conditions, you'll have to specify all of them.  To me, the best and most flexible way to do that is via a table, rather than hard-coding a list of values.
Avatar of Aleks

ASKER

The table has more IDs that's why i specified them
Avatar of Aleks

ASKER

?
ASKER CERTIFIED SOLUTION
Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

Used the last query. Seemed to work with no errors. User will check on results tomorrow.