| Author |
 Topic  |
|
|
MCusuma1
Starting Member
11 Posts |
 Posted - 03/06/2013 : 13:32:00
|
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
48076 Posts |
Posted - 03/06/2013 : 13:38:10
|
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/
|
 |
|
|
MCusuma1
Starting Member
11 Posts |
Posted - 03/06/2013 : 14:14:00
|
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.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 03/06/2013 : 23:21:42
|
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/
|
|
|
|
MCusuma1
Starting Member
11 Posts |
Posted - 03/07/2013 : 12:28:14
|
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 |
|
|
|
MCusuma1
Starting Member
11 Posts |
Posted - 03/07/2013 : 14:00:49
|
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 |
|
|
|
MCusuma1
Starting Member
11 Posts |
Posted - 04/12/2013 : 10:28:12
|
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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 04/12/2013 : 12:13:20
|
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
|
|
|
|
MCusuma1
Starting Member
11 Posts |
Posted - 04/15/2013 : 08:14:19
|
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." |
|
|
|
MCusuma1
Starting Member
11 Posts |
Posted - 04/15/2013 : 08:41:33
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
|
| |
Topic |
|
Update Based on Value from Another Table
