cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cursor

Author

Topic

mana
Posting Yak Master

102 Posts

Posted - 2014-08-25 : 03:25:12

Hello

I wrote the foollowing cursor code but it takes too long.
can you guide me how i can write it without cursor?
thank you

ALTER PROCEDURE [dbo].[usp_AMPPU_Teamleader]
AS
BEGIN
SET NOCOUNT ON;
--
DECLARE @Auftrag VARCHAR(50), @fehlercode VARCHAR(50), @Zeit_Fehler_Ein DATETIME,
@Teamleader_Von_Adresse VARCHAR(50), @Label_Von_Adresse VARCHAR(50), @Teamleader_Prüfplatz VARCHAR(50),
@Label_Prüfplatz VARCHAR(50), @Teamleader_Station VARCHAR(50), @Label_Station VARCHAR(50),
@Von_Adresse VARCHAR(50), @Prüfplatz VARCHAR(50), @Station VARCHAR(50),
@Teamleader VARCHAR(50), @Today DATE;
SET @Today = CASE WHEN LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 19) <= LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 10) + ' 04:00:00' THEN LEFT(CONVERT(NVARCHAR, GETDATE()-1, 120), 10)
ELSE LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 10) END;
--
DECLARE cr_Teamleader CURSOR FOR
SELECT Auftrag, Fehlercode, Zeit_Fehler_EIN, Von_Adresse, Prüfplatz, Station
FROM AMPPU_Alle_Fehlteile
WHERE Teamleader IS NULL
--AND LEFT(CONVERT(NVARCHAR, Zeit_Fehler_EIN, 120), 10) = @Today;
--
OPEN cr_Teamleader
FETCH NEXT FROM cr_Teamleader
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @Von_Adresse, @Prüfplatz, @Station
--
WHILE @@FETCH_STATUS = 0
BEGIN
--
SELECT @Teamleader_Von_Adresse = Von_Adresse,
@Label_Von_Adresse = Label
FROM AMPPU_Teamleader_Von_Adresse
WHERE Von_Adresse = LEFT(@Von_Adresse,3)
OR Von_Adresse = @Von_Adresse;
--
SELECT @Teamleader_Prüfplatz = Prüfplatz,
@Label_Prüfplatz = Label
FROM AMPPU_Teamleader_Prüfplatz
WHERE Prüfplatz = @Prüfplatz;
--
SELECT @Teamleader_Station = Station,
@Label_Station = Label
FROM AMPPU_Teamleader_Station
WHERE Station = @Station;

--
SET @Teamleader = (SELECT CASE WHEN (LEFT(@Von_Adresse,3) = LEFT(@Teamleader_Von_Adresse,3) OR @Von_Adresse = @Teamleader_Von_Adresse) THEN @Label_Von_Adresse
WHEN @Prüfplatz = @Teamleader_Prüfplatz THEN
(SELECT CASE WHEN @Label_Prüfplatz IS NULL THEN @Label_Station ELSE @Label_Prüfplatz END)
END Teamleader);
--
--SELECT @Teamleader_Von_Adresse,
-- @Label_Von_Adresse,
-- @Teamleader_Prüfplatz,
-- @Label_Prüfplatz,
-- @Teamleader_Station,
-- @Label_Station,
-- @Teamleader as Teamleader,
-- @Auftrag,
-- @fehlercode,
-- @Zeit_Fehler_Ein
--

--SELECT Auftrag,Fehlercode,Zeit_Fehler_EIN,Teamleader from
--AMPPU_Alle_Fehlteile
--WHERE Auftrag = @Auftrag
--AND Fehlercode = @fehlercode
--AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);

--if @Teamleader IS NULL
-- SELECT @Teamleader,Auftrag,Fehlercode,Zeit_Fehler_EIN,Teamleader from
--AMPPU_Alle_Fehlteile
--WHERE Auftrag = @Auftrag
-- AND Fehlercode = @fehlercode
-- AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);

UPDATE AMPPU_Alle_Fehlteile
SET Teamleader = @Teamleader
WHERE CURRENT OF cr_Teamleader
-- WHERE Auftrag = @Auftrag
-- AND Fehlercode = @fehlercode
-- AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);
--
FETCH NEXT FROM cr_Teamleader
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @Von_Adresse, @Prüfplatz, @Station
--
END
--
CLOSE cr_Teamleader
DEALLOCATE cr_Teamleader
--
END

mana
Posting Yak Master

102 Posts

Posted - 2014-08-25 : 05:02:11

I have the following cursor. But it takes too long and can you help me how i can write it without cursor??

thank you for your help

ALTER PROCEDURE [dbo].[usp_AMPPU_Alle_Fehlteile]
AS
BEGIN
SET NOCOUNT ON;
--
DECLARE @Today DATE = GETDATE();
--
-- Update fields which got values at a later point in time!
--
BEGIN
DECLARE @Auftrag VARCHAR(50), @fehlercode VARCHAR(50), @Zeit_Fehler_Ein DATETIME, @Zeit_Fehler_Aus DATETIME,
@NA_durch VARCHAR(50), @Bemerkung_Austrag VARCHAR(100), @NA_d_Kost VARCHAR(50), @4M_Analyse VARCHAR(50),
@OKTOSHIP DATETIME, @BV_05 DATETIME, @Reparatur_hr DECIMAL(5,2), @On_Line_Repair NVARCHAR(3);
--
DECLARE cr_SecondUpdate CURSOR FOR
--SELECT a.Auftrag, a.Fehlercode, a.Zeit_Fehler_EIN, a.Zeit_Fehler_AUS, a.NA_durch,
-- a.Bemerkung_Austrag, a.NA_d_KoSt, a.[4M_Analyse], a.OKTOSHIP, LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10),
-- ROUND(a.Reparatur_min/60,2) AS Reparatur_hr
--FROM AQIs.dbo.Alle_Fehlerdaten a
-- RIGHT OUTER JOIN AMPPU_Alle_Fehlteile b
-- ON a.Auftrag = b.Auftrag
-- AND a.Fehlercode = b.Fehlercode
-- AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
--WHERE (a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS OR (b.Zeit_Fehler_AUS IS NULL AND a.Zeit_Fehler_AUS IS NOT NULL))
-- OR (a.NA_durch <> b.NA_durch OR (b.NA_durch IS NULL AND a.NA_durch IS NOT NULL))
-- OR (a.Bemerkung_Austrag <> b.Bemerkung_Austrag OR (b.Bemerkung_Austrag IS NULL AND a.Bemerkung_Austrag IS NOT NULL))
-- OR (a.NA_d_KoSt <> b.NA_d_KoSt OR (b.NA_d_KoSt IS NULL AND a.NA_d_KoSt IS NOT NULL))
-- OR (a.[4M_Analyse] <> b.[4M_Analyse] OR (b.[4M_Analyse] IS NULL AND a.[4M_Analyse] IS NOT NULL))
-- OR (ROUND(a.Reparatur_min/60,2) <> CONVERT(FLOAT,b.Reparatur_hr) OR (CONVERT(FLOAT,b.Reparatur_hr) IS NULL AND ROUND(a.Reparatur_min/60,2) IS NOT NULL))
-- OR (a.OKTOSHIP <> b.OKTOSHIP OR (b.OKTOSHIP IS NULL AND a.OKTOSHIP IS NOT NULL))
-- OR (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
-- OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));
--
--
SELECT a.Auftrag, a.Fehlercode, a.Zeit_Fehler_EIN, a.Zeit_Fehler_AUS, a.NA_durch,
a.Bemerkung_Austrag, a.NA_d_KoSt, a.[4M_Analyse], a.OKTOSHIP, LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10),
ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) AS Reparatur_hr
FROM AQIs.dbo.Alle_Fehlerdaten a
RIGHT OUTER JOIN AMPPU_Alle_Fehlteile b
ON a.Auftrag = b.Auftrag
AND a.Fehlercode = b.Fehlercode
AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
WHERE (a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS
OR (b.Zeit_Fehler_AUS IS NULL AND a.Zeit_Fehler_AUS IS NOT NULL))
OR (a.NA_durch <> b.NA_durch OR (b.NA_durch IS NULL AND a.NA_durch IS NOT NULL))
OR (a.Bemerkung_Austrag <> b.Bemerkung_Austrag OR (b.Bemerkung_Austrag IS NULL AND a.Bemerkung_Austrag IS NOT NULL))
OR (a.NA_d_KoSt <> b.NA_d_KoSt OR (b.NA_d_KoSt IS NULL AND a.NA_d_KoSt IS NOT NULL))
OR (a.[4M_Analyse] <> b.[4M_Analyse] OR (b.[4M_Analyse] IS NULL AND a.[4M_Analyse] IS NOT NULL))
OR (ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) <> CONVERT(FLOAT,b.Reparatur_hr) OR (CONVERT(FLOAT,b.Reparatur_hr) IS NULL AND ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) IS NOT NULL))
OR (a.OKTOSHIP <> b.OKTOSHIP OR (b.OKTOSHIP IS NULL AND a.OKTOSHIP IS NOT NULL))
OR (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));
--
OPEN cr_SecondUpdate
FETCH NEXT FROM cr_SecondUpdate
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_Ein, @Zeit_Fehler_Aus, @NA_durch,
@Bemerkung_Austrag, @NA_d_Kost, @4M_Analyse, @OKTOSHIP, @BV_05, @Reparatur_hr
--
WHILE @@FETCH_STATUS = 0
BEGIN
--
BEGIN
--
SET @On_Line_Repair = CASE WHEN @Zeit_Fehler_Aus IS NULL THEN 'No'
WHEN @BV_05 >= @Zeit_Fehler_Aus THEN 'Yes'
ELSE 'No' END;
--
UPDATE AMPPU_Alle_Fehlteile
SET Zeit_Fehler_AUS = @Zeit_Fehler_Aus, NA_durch = @NA_durch, Bemerkung_Austrag = @Bemerkung_Austrag,
NA_d_KoSt = @NA_d_Kost, [4M_Analyse] = @4M_Analyse, Reparatur_hr = @Reparatur_hr,
OKTOSHIP = @OKTOSHIP, On_Line_Repair = @On_Line_Repair, Offline_date = @BV_05
WHERE Auftrag = @Auftrag
AND Fehlercode = @fehlercode
AND Zeit_Fehler_EIN = @Zeit_Fehler_Ein;
END
--
FETCH NEXT FROM cr_SecondUpdate
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_Ein, @Zeit_Fehler_Aus, @NA_durch,
@Bemerkung_Austrag, @NA_d_Kost, @4M_Analyse, @OKTOSHIP, @BV_05, @Reparatur_hr
--
END
--
CLOSE cr_SecondUpdate
DEALLOCATE cr_SecondUpdate
--
END
--
END

quote:
Originally posted by mana

Hello

I wrote the foollowing cursor code but it takes too long.
can you guide me how i can write it without cursor?
thank you

ALTER PROCEDURE [dbo].[usp_AMPPU_Teamleader]
AS
BEGIN
SET NOCOUNT ON;
--
DECLARE @Auftrag VARCHAR(50), @fehlercode VARCHAR(50), @Zeit_Fehler_Ein DATETIME,
@Teamleader_Von_Adresse VARCHAR(50), @Label_Von_Adresse VARCHAR(50), @Teamleader_Prüfplatz VARCHAR(50),
@Label_Prüfplatz VARCHAR(50), @Teamleader_Station VARCHAR(50), @Label_Station VARCHAR(50),
@Von_Adresse VARCHAR(50), @Prüfplatz VARCHAR(50), @Station VARCHAR(50),
@Teamleader VARCHAR(50), @Today DATE;
SET @Today = CASE WHEN LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 19) <= LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 10) + ' 04:00:00' THEN LEFT(CONVERT(NVARCHAR, GETDATE()-1, 120), 10)
ELSE LEFT(CONVERT(NVARCHAR, GETDATE(), 120), 10) END;
--
DECLARE cr_Teamleader CURSOR FOR
SELECT Auftrag, Fehlercode, Zeit_Fehler_EIN, Von_Adresse, Prüfplatz, Station
FROM AMPPU_Alle_Fehlteile
WHERE Teamleader IS NULL
--AND LEFT(CONVERT(NVARCHAR, Zeit_Fehler_EIN, 120), 10) = @Today;
--
OPEN cr_Teamleader
FETCH NEXT FROM cr_Teamleader
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @Von_Adresse, @Prüfplatz, @Station
--
WHILE @@FETCH_STATUS = 0
BEGIN
--
SELECT @Teamleader_Von_Adresse = Von_Adresse,
@Label_Von_Adresse = Label
FROM AMPPU_Teamleader_Von_Adresse
WHERE Von_Adresse = LEFT(@Von_Adresse,3)
OR Von_Adresse = @Von_Adresse;
--
SELECT @Teamleader_Prüfplatz = Prüfplatz,
@Label_Prüfplatz = Label
FROM AMPPU_Teamleader_Prüfplatz
WHERE Prüfplatz = @Prüfplatz;
--
SELECT @Teamleader_Station = Station,
@Label_Station = Label
FROM AMPPU_Teamleader_Station
WHERE Station = @Station;

--
SET @Teamleader = (SELECT CASE WHEN (LEFT(@Von_Adresse,3) = LEFT(@Teamleader_Von_Adresse,3) OR @Von_Adresse = @Teamleader_Von_Adresse) THEN @Label_Von_Adresse
WHEN @Prüfplatz = @Teamleader_Prüfplatz THEN
(SELECT CASE WHEN @Label_Prüfplatz IS NULL THEN @Label_Station ELSE @Label_Prüfplatz END)
END Teamleader);
--
--SELECT @Teamleader_Von_Adresse,
-- @Label_Von_Adresse,
-- @Teamleader_Prüfplatz,
-- @Label_Prüfplatz,
-- @Teamleader_Station,
-- @Label_Station,
-- @Teamleader as Teamleader,
-- @Auftrag,
-- @fehlercode,
-- @Zeit_Fehler_Ein
--

--SELECT Auftrag,Fehlercode,Zeit_Fehler_EIN,Teamleader from
--AMPPU_Alle_Fehlteile
--WHERE Auftrag = @Auftrag
--AND Fehlercode = @fehlercode
--AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);

--if @Teamleader IS NULL
-- SELECT @Teamleader,Auftrag,Fehlercode,Zeit_Fehler_EIN,Teamleader from
--AMPPU_Alle_Fehlteile
--WHERE Auftrag = @Auftrag
-- AND Fehlercode = @fehlercode
-- AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);

UPDATE AMPPU_Alle_Fehlteile
SET Teamleader = @Teamleader
WHERE CURRENT OF cr_Teamleader
-- WHERE Auftrag = @Auftrag
-- AND Fehlercode = @fehlercode
-- AND Zeit_Fehler_EIN = convert(datetime,@Zeit_Fehler_Ein);
--
FETCH NEXT FROM cr_Teamleader
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_EIN, @Von_Adresse, @Prüfplatz, @Station
--
END
--
CLOSE cr_Teamleader
DEALLOCATE cr_Teamleader
--
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-08-25 : 10:02:02

This should be equivalent for your first post:

ALTER PROCEDURE [dbo].[usp_AMPPU_Teamleader] AS
SET NOCOUNT ON;

UPDATE AAF SET Teamleader = ATVA.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Von_Adresse ATVA ON AAF.Von_Adresse=ATVA.Von_Adresse
WHERE AAF.Teamleader IS NULL

UPDATE AAF SET Teamleader = ATVA.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Von_Adresse ATVA ON LEFT(AAF.Von_Adresse,3)=LEFT(ATVA.Von_Adresse,3)
WHERE AAF.Teamleader IS NULL

UPDATE AAF SET Teamleader = ATP.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Prüfplatz ATP ON AAF.Prüfplatz=ATP.Prüfplatz
WHERE AAF.Teamleader IS NULL

UPDATE AAF SET Teamleader = ATS.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Station ATS ON AAF.Station=ATS.Station
WHERE AAF.Teamleader IS NULL

edit: fixed some typos

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-25 : 12:18:25

I belive this might Work for the second update (haven't tested syntax as I don't have access to my database server at the moment):

UPDATE AMPPU_Alle_Fehlteile
   SET Zeit_Fehler_AUS = a.Zeit_Fehler_AUS
      ,NA_durch = a.NA_durch
      ,Bemerkung_Austrag = a.Bemerkung_Austrag
      ,NA_d_KoSt = a.NA_d_KoSt
      ,[4M_Analyse] = a.[4M_Analyse]
      ,Reparatur_hr = ROUND((a.Reparatur_min+ISNULL(a.ReparaturFolge_min,0))/60,2)
      ,OKTOSHIP = a.OKTOSHIP
      ,On_Line_Repair = CASE
                           WHEN a.Zeit_Fehler_AUS IS NULL
                           THEN 'No'
                           WHEN LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10)>=a.Zeit_Fehler_AUS
                           THEN 'Yes'
                           ELSE 'No'
                        END
      ,Offline_date = LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10)
  FROM AQIs.dbo.Alle_Fehlerdaten a
       RIGHT OUTER JOIN AMPPU_Alle_Fehlteile b
                     ON a.Auftrag = b.Auftrag
                    AND a.Fehlercode = b.Fehlercode
                    AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
 WHERE AMPPU_Alle_Fehlteile.Auftrag = a.Auftrag
   AND AMPPU_Alle_Fehlteile.Fehlercode = a.Fehlercode
   AND AMPPU_Alle_Fehlteile.Zeit_Fehler_EIN = a.Zeit_Fehler_EIN
   AND (a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS 
    OR  a.NA_durch <> b.NA_durch
    OR  a.Bemerkung_Austrag <> b.Bemerkung_Austrag
    OR  a.NA_d_KoSt <> b.NA_d_KoSt
    OR  a.[4M_Analyse] <> b.[4M_Analyse]
    OR  ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) <> CONVERT(FLOAT,b.Reparatur_hr)
    OR  a.OKTOSHIP <> b.OKTOSHIP
    OR  a.[BV-05] <> b.[BV-05]
    OR  LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date
       )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-25 : 15:04:02

Second post

UPDATE		b
SET		b.On_Line_Repair =	CASE
						WHEN a.Zeit_Fehler_AUS IS NULL THEN 'No'
						WHEN LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) >= a.Zeit_Fehler_AUS THEN 'Yes'
						ELSE 'No'
					END,
		b.Zeit_Fehler_AUS = a.Zeit_Fehler_AUS,
		b.NA_durch = a.NA_durch,
		b.Bemerkung_Austrag = a.Bemerkung_Austrag,
		b.NA_d_KoSt = a.NA_d_KoSt,
		b.[4M_Analyse] = a.[4M_Analyse],
		b.OKTOSHIP = a.OKTOSHIP,
		b.Offline_date = LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10),
		b.Reparatur_hr = ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0)) / 60, 2)
FROM		AQIs.dbo.Alle_Fehlerdaten AS a
RIGHT JOIN	AMPPU_Alle_Fehlteile AS b ON a.Auftrag = b.Auftrag
			AND a.Fehlercode = b.Fehlercode
			AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
WHERE		(a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS OR (b.Zeit_Fehler_AUS IS NULL AND a.Zeit_Fehler_AUS IS NOT NULL))
		OR (a.NA_durch <> b.NA_durch OR (b.NA_durch IS NULL AND a.NA_durch IS NOT NULL))
		OR (a.Bemerkung_Austrag <> b.Bemerkung_Austrag OR (b.Bemerkung_Austrag IS NULL AND a.Bemerkung_Austrag IS NOT NULL))
		OR (a.NA_d_KoSt <> b.NA_d_KoSt OR (b.NA_d_KoSt IS NULL AND a.NA_d_KoSt IS NOT NULL))
		OR (a.[4M_Analyse] <> b.[4M_Analyse] OR (b.[4M_Analyse] IS NULL AND a.[4M_Analyse] IS NOT NULL))
		OR (ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0)) / 60, 2) <> CONVERT(FLOAT, b.Reparatur_hr) OR (CONVERT(FLOAT, b.Reparatur_hr) IS NULL AND ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0)) / 60, 2) IS NOT NULL))
		OR (a.OKTOSHIP <> b.OKTOSHIP OR (b.OKTOSHIP IS NULL AND a.OKTOSHIP IS NOT NULL))
		OR (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
		OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mana
Posting Yak Master

102 Posts

Posted - 2014-08-26 : 04:22:13

thank you for your help

quote:

Originally posted by robvolk

This should be equivalent for your first post:

ALTER PROCEDURE [dbo].[usp_AMPPU_Teamleader] AS
SET NOCOUNT ON;

UPDATE AAF SET Teamleader = ATVA.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Von_Adresse ATVA ON AAF.Von_Adresse=ATVA.Von_Adresse
WHERE AAF.Teamleader IS NULL

UPDATE AAF SET Teamleader = ATVA.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Von_Adresse ATVA ON LEFT(AAF.Von_Adresse,3)=LEFT(ATVA.Von_Adresse,3)
WHERE AAF.Teamleader IS NULL

UPDATE AAF SET Teamleader = ATP.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Prüfplatz ATP ON AAF.Prüfplatz=ATP.Prüfplatz
WHERE AAF.Teamleader IS NULL

UPDATE AAF SET Teamleader = ATS.Label
FROM AMPPU_Alle_Fehlteile AAF
INNER JOIN AMPPU_Teamleader_Station ATS ON AAF.Station=ATS.Station
WHERE AAF.Teamleader IS NULL

edit: fixed some typos

mana
Posting Yak Master

102 Posts

Posted - 2014-08-26 : 04:22:53

thank you for the response

quote:

Originally posted by bitsmed

I belive this might Work for the second update (haven't tested syntax as I don't have access to my database server at the moment):

UPDATE AMPPU_Alle_Fehlteile
   SET Zeit_Fehler_AUS = a.Zeit_Fehler_AUS
      ,NA_durch = a.NA_durch
      ,Bemerkung_Austrag = a.Bemerkung_Austrag
      ,NA_d_KoSt = a.NA_d_KoSt
      ,[4M_Analyse] = a.[4M_Analyse]
      ,Reparatur_hr = ROUND((a.Reparatur_min+ISNULL(a.ReparaturFolge_min,0))/60,2)
      ,OKTOSHIP = a.OKTOSHIP
      ,On_Line_Repair = CASE
                           WHEN a.Zeit_Fehler_AUS IS NULL
                           THEN 'No'
                           WHEN LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10)>=a.Zeit_Fehler_AUS
                           THEN 'Yes'
                           ELSE 'No'
                        END
      ,Offline_date = LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10)
  FROM AQIs.dbo.Alle_Fehlerdaten a
       RIGHT OUTER JOIN AMPPU_Alle_Fehlteile b
                     ON a.Auftrag = b.Auftrag
                    AND a.Fehlercode = b.Fehlercode
                    AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
 WHERE AMPPU_Alle_Fehlteile.Auftrag = a.Auftrag
   AND AMPPU_Alle_Fehlteile.Fehlercode = a.Fehlercode
   AND AMPPU_Alle_Fehlteile.Zeit_Fehler_EIN = a.Zeit_Fehler_EIN
   AND (a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS 
    OR  a.NA_durch <> b.NA_durch
    OR  a.Bemerkung_Austrag <> b.Bemerkung_Austrag
    OR  a.NA_d_KoSt <> b.NA_d_KoSt
    OR  a.[4M_Analyse] <> b.[4M_Analyse]
    OR  ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) <> CONVERT(FLOAT,b.Reparatur_hr)
    OR  a.OKTOSHIP <> b.OKTOSHIP
    OR  a.[BV-05] <> b.[BV-05]
    OR  LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date
       )

mana
Posting Yak Master

102 Posts

Posted - 2014-08-26 : 04:23:42

thank you for your response

quote:

Originally posted by SwePeso

Second post

UPDATE		b
SET		b.On_Line_Repair =	CASE
						WHEN a.Zeit_Fehler_AUS IS NULL THEN 'No'
						WHEN LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) >= a.Zeit_Fehler_AUS THEN 'Yes'
						ELSE 'No'
					END,
		b.Zeit_Fehler_AUS = a.Zeit_Fehler_AUS,
		b.NA_durch = a.NA_durch,
		b.Bemerkung_Austrag = a.Bemerkung_Austrag,
		b.NA_d_KoSt = a.NA_d_KoSt,
		b.[4M_Analyse] = a.[4M_Analyse],
		b.OKTOSHIP = a.OKTOSHIP,
		b.Offline_date = LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10),
		b.Reparatur_hr = ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0)) / 60, 2)
FROM		AQIs.dbo.Alle_Fehlerdaten AS a
RIGHT JOIN	AMPPU_Alle_Fehlteile AS b ON a.Auftrag = b.Auftrag
			AND a.Fehlercode = b.Fehlercode
			AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
WHERE		(a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS OR (b.Zeit_Fehler_AUS IS NULL AND a.Zeit_Fehler_AUS IS NOT NULL))
		OR (a.NA_durch <> b.NA_durch OR (b.NA_durch IS NULL AND a.NA_durch IS NOT NULL))
		OR (a.Bemerkung_Austrag <> b.Bemerkung_Austrag OR (b.Bemerkung_Austrag IS NULL AND a.Bemerkung_Austrag IS NOT NULL))
		OR (a.NA_d_KoSt <> b.NA_d_KoSt OR (b.NA_d_KoSt IS NULL AND a.NA_d_KoSt IS NOT NULL))
		OR (a.[4M_Analyse] <> b.[4M_Analyse] OR (b.[4M_Analyse] IS NULL AND a.[4M_Analyse] IS NOT NULL))
		OR (ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0)) / 60, 2) <> CONVERT(FLOAT, b.Reparatur_hr) OR (CONVERT(FLOAT, b.Reparatur_hr) IS NULL AND ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0)) / 60, 2) IS NOT NULL))
		OR (a.OKTOSHIP <> b.OKTOSHIP OR (b.OKTOSHIP IS NULL AND a.OKTOSHIP IS NOT NULL))
		OR (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
		OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-26 : 12:50:38

Please note there is a logical difference between my response and bitsmed.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources