SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update Based on Value from Another Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MCusuma1
Starting Member

13 Posts

Posted - 03/06/2013 :  13:32:00  Show Profile  Reply with Quote
I need to create a query that will set an Autonumber on a selection based on a value from another table.

Basically what I am doing is assigning a sequence of inspections to a technician in a table (CPTestPointInspection) based on a table containing the technician's ID codes (ASSIGNED_TECHS).

So, there would be two loops: one going through the technician table and getting the ID's from the TECH_ID field, and one going through the inspection table assigning the inspections from 1 to n based on a WHERE clause stating the ASSIGNED_USER field = TECH_ID field from the technician table. Once all active inspections for that technician have been assigned, it would move on the the next technician code in the tech table and begin again at 1.

I have this done in ArcPy using a searchcursor and updatecursor(this is done on an esri SDE table) but it takes far too long and I would like to re-work it for SQL so it would run much quicker. I have been trying for hours with no luck, any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/06/2013 :  13:38:10  Show Profile  Reply with Quote
sounds like this to me

INSERT CPTestPointInspection
SELECT t.TECH_ID,i.Inspection
FROM ASSIGNED_TECHS t
INNER JOIN Inspections i
ON i.ASSIGNED_USER  = t.TECH_ID 


i've assumed column names so make sure you use correct columns names in your query

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

Go to Top of Page

MCusuma1
Starting Member

13 Posts

Posted - 03/06/2013 :  14:14:00  Show Profile  Reply with Quote
ASSIGNED_USER is a field in CPTestPointInspection that is used to join to the ASSIGNED_TECHS table. The ASSIGNED_TECHS table is created just prior to this query where it pulls all current techs out of CPTestPointInspection (that part is done).



So let's say there are two techs in the ASSIGNED_TECHS Table, Tech1 and Tech2:

If there are 31 pending inspections for Tech1 in CPTestPointInspection, (based on WHERE CPTestPointInspection.ASSIGNED_USER = ASSIGNED_TECHS.Tech1) it would assign a value of 1 to 31 in a field called SEQ_NO for Tech1 in CPTestPointInspection (sorted in ascending order by structure number).


Likewise, if there are 52 pending inspections for Tech2 (based on WHERE CPTestPointInspection.ASSIGNED_USER = ASSIGNED_TECHS.Tech2) it would assign values of 1 to 52 in SEQ_NO for Tech2 sorted by Structure Number.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/06/2013 :  23:21:42  Show Profile  Reply with Quote
for generatinmg sequence number you need this

SELECT t.TECH_ID,i.Inspection,ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.Inspection) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN Inspections i
ON i.ASSIGNED_USER  = t.TECH_ID


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

Go to Top of Page

MCusuma1
Starting Member

13 Posts

Posted - 03/07/2013 :  12:28:14  Show Profile  Reply with Quote
Thank you, this worked well. I am now trying to implement this into an update query. I have:


UPDATE CPTESTPOINTINSPECTION_SYSTM
SET SEQ_NO = ROW_NUMBER() OVER (PARTITION BY ASSIGNED_TECHS.TECH_ID ORDER BY CPTESTPOINTINSPECTION_SYSTM.GAS_CORR_STRUCT_NO)
FROM CPTESTPOINTINSPECTION_SYSTM
INNER JOIN ASSIGNED_TECHS
ON CPTESTPOINTINSPECTION_SYSTM.ASSIGNED_USER = ASSIGNED_TECHS.TECH_ID

However this is telling me that

"Msg 4108, Level 15, State 1, Line 4
Windowed functions can only appear in the SELECT or ORDER BY clauses."

Edited by - MCusuma1 on 03/07/2013 12:28:32
Go to Top of Page

MCusuma1
Starting Member

13 Posts

Posted - 03/07/2013 :  14:00:49  Show Profile  Reply with Quote
Never mind I got it:

UPDATE CPTESTPOINTINSPECTION_SYSTM
SET CPTESTPOINTINSPECTION_SYSTM.SEQ_NO = seq

FROM
(
SELECT t.TECH_ID,i.GAS_CORR_STRUCT_NO, INSPECTIONDATE, SEQ_NO, ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.GAS_CORR_STRUCT_NO) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN CPTESTPOINTINSPECTION_SYSTM i
ON i.ASSIGNED_USER = t.TECH_ID
) CPTESTPOINTINSPECTION_SYSTM
WHERE CPTESTPOINTINSPECTION_SYSTM.INSPECTIONDATE IS NULL
Go to Top of Page

MCusuma1
Starting Member

13 Posts

Posted - 04/12/2013 :  10:28:12  Show Profile  Reply with Quote
Hi, this is still not working correctly...it is not populating the table from 1 to n based on the number of structures per assigned tech, it is populating them based on some row number equivalent. I am now trying to do it with cursors, so far I have:



DECLARE @id VARCHAR(10)
DECLARE @stop int
SET @STOP = 0

DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [TECH_ID] AS 'ID' 
    FROM [dbo].[Assigned_Techs]
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @id
		
		UPDATE CPTESTPOINTINSPECTION_RECHECK
		SET STOP = @STOP + 1
		SET SEQ_NO
		ORDER BY GAS_CORR_STRUCT_NUM
		WHERE CURRENT of myCursor



    FETCH NEXT FROM myCursor INTO @id

END

CLOSE myCursor
DEALLOCATE myCursor


But this is not working.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/12/2013 :  12:13:20  Show Profile  Reply with Quote
it should be

UPDATE CPTESTPOINTINSPECTION_SYSTM
SET CPTESTPOINTINSPECTION_SYSTM.SEQ_NO = seq

FROM 
(
SELECT t.TECH_ID,i.GAS_CORR_STRUCT_NO, INSPECTIONDATE, SEQ_NO, ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.GAS_CORR_STRUCT_NO) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN CPTESTPOINTINSPECTION_SYSTM i
ON i.ASSIGNED_USER = t.TECH_ID
WHERE CPTESTPOINTINSPECTION_SYSTM.INSPECTIONDATE IS NULL
) CPTESTPOINTINSPECTION_SYSTM




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MCusuma1
Starting Member

13 Posts

Posted - 04/15/2013 :  08:14:19  Show Profile  Reply with Quote
I tried this and it gave me

"Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "CPTESTPOINTINSPECTION_SYSTM.INSPECTIONDATE" could not be bound."
Go to Top of Page

MCusuma1
Starting Member

13 Posts

Posted - 04/15/2013 :  08:41:33  Show Profile  Reply with Quote
I got it, was an easy fix. Had to add an "is not null" for structure number to the where clause and get rid of the pretext for inspectiondate:

USE psegGDB
UPDATE CPTESTPOINTINSPECTION_SYSTM
SET CPTESTPOINTINSPECTION_SYSTM.SEQ_NO = seq

FROM
(
SELECT t.TECH_ID,i.GAS_CORR_STRUCT_NO, INSPECTIONDATE, SEQ_NO, ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.GAS_CORR_STRUCT_NO) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN CPTESTPOINTINSPECTION_SYSTM i
ON i.ASSIGNED_USER = t.TECH_ID
WHERE INSPECTIONDATE IS NULL
AND GAS_CORR_STRUCT_NO IS NOT NULL
) CPTESTPOINTINSPECTION_SYSTM
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/15/2013 :  12:28:42  Show Profile  Reply with Quote
cool
glad that you got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000