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
 General SQL Server Forums
 New to SQL Server Programming
 cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mana
Yak Posting Veteran

86 Posts

Posted - 08/25/2014 :  03:25:12  Show Profile  Reply with Quote
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
Yak Posting Veteran

86 Posts

Posted - 08/25/2014 :  05:02:11  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/25/2014 :  10:02:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 08/25/2014 :  12:18:25  Show Profile  Reply with Quote
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
       )
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/25/2014 :  15:04:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

mana
Yak Posting Veteran

86 Posts

Posted - 08/26/2014 :  04:22:13  Show Profile  Reply with Quote
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

Go to Top of Page

mana
Yak Posting Veteran

86 Posts

Posted - 08/26/2014 :  04:22:53  Show Profile  Reply with Quote

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
       )


Go to Top of Page

mana
Yak Posting Veteran

86 Posts

Posted - 08/26/2014 :  04:23:42  Show Profile  Reply with Quote

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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/26/2014 :  12:50:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please note there is a logical difference between my response and bitsmed.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.2 seconds. Powered By: Snitz Forums 2000