You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
560 lines
19 KiB
560 lines
19 KiB
USE [Vertragsverwaltung]
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt6_sik] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
Create FUNCTION [dbo].[lc_rpt6_sik]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@inTagen VARCHAR(255)
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
|
|
AS
|
|
begin
|
|
DECLARE @von DATETIME
|
|
DECLARE @it integer
|
|
DECLARE @bis datetime
|
|
SET @it=@intagen
|
|
IF @intagen <> 0 BEGIN
|
|
SET @von=DATEADD(DAY,@it,GETDATE())
|
|
SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
|
|
END
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp1
|
|
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt INNER JOIN
|
|
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
AND dbo.LC_Produkt_Phase.Datum >=@von
|
|
ORDER BY Produkt
|
|
|
|
end
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp1
|
|
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt INNER JOIN
|
|
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
AND dbo.LC_Produkt_Phase.Datum >=@bis
|
|
ORDER BY Produkt
|
|
end
|
|
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt5_sik] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
Create FUNCTION [dbo].[lc_rpt5_sik]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@inTagen varchar(255)
|
|
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
|
|
AS
|
|
begin
|
|
DECLARE @von DATETIME
|
|
DECLARE @it integer
|
|
DECLARE @bis datetime
|
|
SET @it=@intagen
|
|
IF @intagen <> 0 BEGIN
|
|
SET @von=DATEADD(DAY,@it,GETDATE())
|
|
SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
|
|
END
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp1 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase
|
|
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
AND dbo.LC_Produkt_Phase.Datum >=@von
|
|
|
|
END
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp1 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase
|
|
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
AND dbo.LC_Produkt_Phase.Datum >=@von
|
|
end
|
|
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt6] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[lc_rpt6]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@inTagen VARCHAR(255)
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
|
|
AS
|
|
begin
|
|
DECLARE @von DATETIME
|
|
DECLARE @it integer
|
|
DECLARE @bis datetime
|
|
SET @it=@intagen
|
|
IF @intagen <> 0 BEGIN
|
|
SET @von=DATEADD(DAY,@it,GETDATE())
|
|
SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
|
|
END
|
|
|
|
DECLARE @tmp2 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr INT, Phase VARCHAR(255), datum DATETIME, skey INT IDENTITY)
|
|
DECLARE @tmp3 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr int, phase VARCHAR(255), datumvon DATETIME, datumbis DATETIME, diff INT, skey INT )
|
|
|
|
INSERT @tmp2
|
|
SELECT TOP (100) PERCENT dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.lc_produkt.bezeichnung AS Produkt, dbo.LC_Produkt_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt_Phase INNER JOIN
|
|
dbo.LC_Produkt ON dbo.LC_Produkt_Phase.LC_ProduktNr = dbo.LC_Produkt.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
ORDER BY dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.LC_Produkt_Phase.Datum DESC
|
|
|
|
INSERT @tmp3
|
|
SELECT t1.lc_ProduktNr, t1.produkt, t1.lc_phasenr, t1.Phase , t1.datum AS DatumVon, x.datum AS DatumBis, ISNULL(DATEDIFF(DAY,t1.datum,x.datum),0) AS Diff, t1.skey
|
|
FROM @tmp2 t1
|
|
OUTER APPLY (
|
|
SELECT TOP 1 datum FROM @tmp2 t2 WHERE t2.skey < t1.skey ORDER BY t2.skey desc) x
|
|
ORDER BY lc_ProduktNr, t1.datum asc
|
|
|
|
UPDATE @tmp3 SET diff=0 WHERE diff < 0
|
|
|
|
DELETE FROM @tmp3 WHERE diff=0
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp1
|
|
SELECT DISTINCT LC_Produktnr, Produkt, lc_phasenr, datumvon
|
|
FROM @tmp3
|
|
WHERE @von BETWEEN datumvon AND datumbis
|
|
ORDER BY Produkt
|
|
|
|
end
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp1
|
|
SELECT DISTINCT LC_Produktnr, Produkt, lc_phasenr, datumvon
|
|
FROM @tmp3
|
|
WHERE @von BETWEEN datumvon AND datumbis
|
|
|
|
AND lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
ORDER BY Produkt
|
|
|
|
end
|
|
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt5] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[lc_rpt5]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@inTagen varchar(255)
|
|
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
|
|
AS
|
|
begin
|
|
DECLARE @von DATETIME
|
|
DECLARE @it integer
|
|
DECLARE @bis datetime
|
|
SET @it=@intagen
|
|
IF @intagen <> 0 BEGIN
|
|
SET @von=DATEADD(DAY,@it,GETDATE())
|
|
SET @von=DATEADD(dd, DATEDIFF(dd, 0, @von), 0)
|
|
END
|
|
|
|
DECLARE @tmp2 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr INT, Phase VARCHAR(255), datum DATETIME, skey INT IDENTITY)
|
|
DECLARE @tmp3 TABLE (lc_Produktnr INT, Produkt VARCHAR(255), lc_phasenr int, phase VARCHAR(255), datumvon DATETIME, datumbis DATETIME, diff INT, skey INT )
|
|
|
|
INSERT @tmp2
|
|
SELECT TOP (100) PERCENT dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.lc_produkt.bezeichnung AS Produkt, dbo.LC_Produkt_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt_Phase INNER JOIN
|
|
dbo.LC_Produkt ON dbo.LC_Produkt_Phase.LC_ProduktNr = dbo.LC_Produkt.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
ORDER BY dbo.LC_Produkt_Phase.LC_ProduktNr, dbo.LC_Produkt_Phase.Datum DESC
|
|
|
|
INSERT @tmp3
|
|
SELECT t1.lc_ProduktNr, t1.produkt, t1.lc_phasenr, t1.Phase , t1.datum AS DatumVon, x.datum AS DatumBis, ISNULL(DATEDIFF(DAY,t1.datum,x.datum),0) AS Diff, t1.skey
|
|
FROM @tmp2 t1
|
|
OUTER APPLY (
|
|
SELECT TOP 1 datum FROM @tmp2 t2 WHERE t2.skey < t1.skey ORDER BY t2.skey desc) x
|
|
ORDER BY lc_ProduktNr, t1.datum asc
|
|
|
|
UPDATE @tmp3 SET diff=0 WHERE diff < 0
|
|
DELETE FROM @tmp3 WHERE diff=0
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp1
|
|
SELECT DISTINCT LC_PhaseNr, Phase AS Phase FROM @tmp3
|
|
WHERE @von BETWEEN datumvon AND datumbis
|
|
END
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp1
|
|
SELECT DISTINCT LC_PhaseNr, Phase AS Phase FROM @tmp3
|
|
WHERE @von BETWEEN datumvon AND datumbis AND lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
end
|
|
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt4] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[lc_rpt4]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@von varchar(255),
|
|
@bis varchar(255),
|
|
@produkttypnr int
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
|
|
AS
|
|
begin
|
|
|
|
IF @von='' SET @von='01.01.1900 '
|
|
IF @bis='' SET @bis='31.12.4712'
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp1
|
|
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt INNER JOIN
|
|
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
|
|
|
|
ORDER BY dbo.LC_Produkt_Phase.Datum,Produkt
|
|
|
|
end
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp1
|
|
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt INNER JOIN
|
|
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
|
|
|
|
ORDER BY dbo.LC_Produkt_Phase.Datum,Produkt
|
|
end
|
|
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt3] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[lc_rpt3]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@von varchar(255),
|
|
@bis varchar(255),
|
|
@produkttypnr int
|
|
|
|
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
|
|
AS
|
|
begin
|
|
|
|
declare @tmp2 TABLE (LC_Phasenr INT, Phase VARCHAR(255), datum datetime)
|
|
declare @tmp3 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
|
|
|
|
|
|
IF @von='' SET @von='01.01.1900 '
|
|
IF @bis='' SET @bis='31.12.4712'
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
|
|
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
|
|
ORDER BY lc_produkt_phase.datum
|
|
|
|
END
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
|
|
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
and dbo.lc_phase.lc_phasenr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
|
|
ORDER BY lc_produkt_phase.datum
|
|
end
|
|
|
|
INSERT @tmp3
|
|
SELECT TOP (100) PERCENT LC_Phasenr, Phase
|
|
FROM @tmp2
|
|
GROUP BY LC_Phasenr, Phase
|
|
ORDER BY MIN(datum)
|
|
|
|
DECLARE @phase VARCHAR(255)
|
|
DECLARE @i INT
|
|
DECLARE @zwphase VARCHAR(255)
|
|
SET @zwphase=''
|
|
SET @i=0
|
|
DECLARE xc CURSOR FOR
|
|
SELECT phase FROM @tmp3
|
|
OPEN xc
|
|
FETCH NEXT FROM xc INTO @phase
|
|
WHILE @@FETCH_STATUS=0 BEGIN
|
|
IF @zwphase<>@phase BEGIN
|
|
SET @i=@i+1
|
|
SET @zwphase=@phase
|
|
END
|
|
UPDATE @tmp3 SET phase=STR(@i)+' '+@phase WHERE phase=@phase
|
|
FETCH NEXT FROM xc INTO @phase
|
|
END
|
|
CLOSE xc
|
|
deallocate xc
|
|
INSERT @tmp1 SELECT lc_phasenr, phase FROM @tmp3
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt2] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[lc_rpt2]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@von varchar(255),
|
|
@bis varchar(255),
|
|
@produkttypnr int
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Produktnr INT, Produkt VARCHAR(255), LC_PhaseNr INT, datum DATETIME)
|
|
AS
|
|
begin
|
|
|
|
IF @von='' SET @von='01.01.1900 '
|
|
IF @bis='' SET @bis='31.12.4712'
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp1
|
|
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt INNER JOIN
|
|
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
ORDER BY Produkt
|
|
|
|
end
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp1
|
|
SELECT distinct dbo.LC_Produkt.LC_ProduktNr, dbo.LC_Produkt.Bezeichnung AS Produkt, dbo.LC_Phase.LC_PhaseNr, dbo.LC_Produkt_Phase.Datum
|
|
FROM dbo.LC_Produkt INNER JOIN
|
|
dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
and dbo.lc_produkt.lc_produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
ORDER BY Produkt
|
|
end
|
|
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt1] Script Date: 02.05.2015 09:43:18 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[lc_rpt1]
|
|
(
|
|
@produkte VARCHAR(255),
|
|
@von varchar(255),
|
|
@bis varchar(255),
|
|
@produkttypnr int
|
|
|
|
|
|
)
|
|
RETURNS @tmp1 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
|
|
AS
|
|
begin
|
|
|
|
declare @tmp2 TABLE (LC_Phasenr INT, Phase VARCHAR(255), datum datetime)
|
|
declare @tmp3 TABLE (LC_Phasenr INT, Phase VARCHAR(255))
|
|
|
|
|
|
IF @von='' SET @von='01.01.1900 '
|
|
IF @bis='' SET @bis='31.12.4712'
|
|
|
|
IF @produkte='' BEGIN
|
|
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
|
|
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
|
|
ORDER BY lc_produkt_phase.datum
|
|
|
|
END
|
|
IF @produkte<>'' BEGIN
|
|
insert into @tmp2 SELECT DISTINCT dbo.LC_Phase.LC_PhaseNr, dbo.LC_Phase.Bezeichnung AS Phase, lc_produkt_phase.datum
|
|
FROM dbo.LC_Produkt INNER JOIN dbo.LC_Produkt_Phase ON dbo.LC_Produkt.LC_ProduktNr = dbo.LC_Produkt_Phase.LC_ProduktNr INNER JOIN
|
|
dbo.LC_Phase ON dbo.LC_Produkt_Phase.LC_PhaseNr = dbo.LC_Phase.LC_PhaseNr
|
|
WHERE (dbo.LC_Phase.Aktiv = 1) AND (dbo.LC_Produkt_Phase.Aktiv = 1) AND (dbo.LC_Produkt.Aktiv = 1) AND (dbo.LC_Produkt.StatusNr = 1)
|
|
and dbo.LC_Produkt_Phase.LC_Produktnr IN (SELECT item FROM dbo.fnkt_split(@produkte,',',1))
|
|
AND dbo.LC_Produkt_Phase.Datum BETWEEN @von AND @bis
|
|
AND (@produkttypnr=-1 OR @produkttypnr = lc_produkt.ProdukttypNr)
|
|
ORDER BY lc_produkt_phase.datum
|
|
end
|
|
|
|
INSERT @tmp3
|
|
SELECT TOP (100) PERCENT LC_Phasenr, Phase
|
|
FROM @tmp2
|
|
GROUP BY LC_Phasenr, Phase
|
|
ORDER BY MIN(datum)
|
|
|
|
DECLARE @phase VARCHAR(255)
|
|
DECLARE @i INT
|
|
DECLARE @zwphase VARCHAR(255)
|
|
SET @zwphase=''
|
|
SET @i=0
|
|
DECLARE xc CURSOR FOR
|
|
SELECT phase FROM @tmp3
|
|
OPEN xc
|
|
FETCH NEXT FROM xc INTO @phase
|
|
WHILE @@FETCH_STATUS=0 BEGIN
|
|
IF @zwphase<>@phase BEGIN
|
|
SET @i=@i+1
|
|
SET @zwphase=@phase
|
|
END
|
|
UPDATE @tmp3 SET phase=STR(@i)+' '+@phase WHERE phase=@phase
|
|
FETCH NEXT FROM xc INTO @phase
|
|
END
|
|
CLOSE xc
|
|
deallocate xc
|
|
INSERT @tmp1 SELECT lc_phasenr, phase FROM @tmp3
|
|
|
|
|
|
RETURN
|
|
end
|
|
|
|
|
|
|
|
GO
|
|
|