Explanation of SQL
This SQL marks all active artwork groups as Produced. It will also create the history entries as well. SQL Bridge is required for this query to work correctly.
Risk of Data Corruption if Run Improperly
HIGH. This query modifies data; therefore, you should make a backup of the database prior to running this query.
SQL
DECLARE @GroupsToChange TABLE(ArtworkGroupID INT PRIMARY KEY, ArtworkGroupClassTypeID INT, SeqID INT, OldStatusID INT, PlayerID INT); DECLARE @NewHistory TABLE(ID INT PRIMARY KEY, ClassTypeID INT, SeqID INT, ArtworkGroupID INT, FromStatusID INT, ToStatusID INT, StatusDT DATETIME, PlayerID INT); INSERT INTO @GroupsToChange SELECT ID, ClassTypeID, SeqID+1, StatusID, (SELECT TOP 1 ID FROM ArtworkPlayer WHERE TransHeaderID = ArtworkGroup.TransHeaderID ORDER BY CASE WHEN EmployeeID IS NULL THEN 1 ELSE 0 END, EmployeeID, ContactID) FROM ArtworkGroup WHERE StatusID < 7 ; DECLARE @GroupCount INT; DECLARE @FirstHistoryID INT; SET @GroupCount = (SELECT COUNT(1) FROM @GroupsToChange WHERE PlayerID IS NOT NULL); SET @FirstHistoryID = (SELECT dbo.csf_chapi_nextid(30302, @GroupCount)); DECLARE @ModifiedDate DATETIME; SET @ModifiedDate = GETDATE(); UPDATE AG SET SeqID = T.SeqID, StatusID = 7, StatusDT = @ModifiedDate FROM ArtworkGroup AG JOIN @GroupsToChange T ON T.ArtworkGroupID = AG.ID WHERE ID IN (SELECT ArtworkGroupID FROM @GroupsToChange) ; INSERT INTO @NewHistory (ID, ClassTypeID, SeqID, ArtworkGroupID, FromStatusID, ToStatusID, StatusDT, PlayerID) SELECT @FirstHistoryID + ROW_NUMBER() OVER (ORDER BY ArtworkGroupID)-1, 30302, 0, ArtworkGroupID, OldStatusID, 7, @ModifiedDate, PlayerID FROM @GroupsToChange WHERE PlayerID IS NOT NULL ; INSERT INTO ArtworkGroupStatusHistory (ID, ClassTypeID, SeqID, ArtworkGroupID, FromStatusID, ToStatusID, StatusDT, PlayerID) SELECT * FROM @NewHistory ; DECLARE @MyCursor CURSOR; DECLARE @ID INT; DECLARE @ClassTypeID INT; DECLARE @SeqID INT; SET @MyCursor = CURSOR FOR SELECT ArtworkGroupID, ArtworkGroupClassTypeID, SeqID FROM @GroupsToChange OPEN @MyCursor FETCH NEXT FROM @MyCursor INTO @ID, @ClassTypeID, @SeqID WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.csf_chapi_refresh @ID, @ClassTypeID, @SeqID; FETCH NEXT FROM @MyCursor INTO @ID, @ClassTypeID, @SeqID END; CLOSE @MyCursor ; SET @MyCursor = CURSOR FOR SELECT ID, ClassTypeID, SeqID FROM @NewHistory OPEN @MyCursor FETCH NEXT FROM @MyCursor INTO @ID, @ClassTypeID, @SeqID WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.csf_chapi_refresh @ID, @ClassTypeID, @SeqID; FETCH NEXT FROM @MyCursor INTO @ID, @ClassTypeID, @SeqID END; CLOSE @MyCursor ; DEALLOCATE @MyCursor;
Version Information
- Entered : 12/02/2016
- Version : 6.0+
You could leave a comment if you were logged in.