Solved

Update field in order

Posted on 2016-09-16
21
108 Views
Last Modified: 2016-09-20
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

0
Comment
Question by:amucinobluedot
  • 10
  • 7
  • 4
21 Comments
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41801552
I am trying to work out something for you. What version of SQL Server are you using (2008 or 2012 and above)?
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41801610
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

0
 

Author Comment

by:amucinobluedot
ID: 41801694
I am using 2008R2. I will test this. Seems more complex than I previously anticipated :$
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41801746
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.
0
 

Author Comment

by:amucinobluedot
ID: 41801753
Thx
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41801851
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.
0
 

Author Comment

by:amucinobluedot
ID: 41801872
How about all the other IDs?
This is a one time data import.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41801949
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
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41802027
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

0
 

Author Comment

by:amucinobluedot
ID: 41804095
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?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:amucinobluedot
ID: 41804096
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

0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41804260
If possible, can you please share the errors?
0
 

Author Comment

by:amucinobluedot
ID: 41804659
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.
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41804906
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

0
 

Author Comment

by:amucinobluedot
ID: 41804985
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

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41805080
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.
0
 

Author Comment

by:amucinobluedot
ID: 41805081
The table has more IDs that's why i specified them
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41805094
0
 

Author Comment

by:amucinobluedot
ID: 41805112
?
0
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 41805504
Not being familiar with your system, I had left room for changes as necessary. Now that I know you have additional catalogs, here are the updated options with specific CatalogIds added to the WHERE clause.

Option 01:
/** 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
      AND a.filedon IS NOT NULL
      AND a.CatalogId IN (593, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 624)
   )
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:
;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
      AND a.filedon IS NOT NULL
      AND a.CatalogId IN (593, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 624)
	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

0
 

Author Closing Comment

by:amucinobluedot
ID: 41807465
Used the last query. Seemed to work with no errors. User will check on results tomorrow.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now