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.
68 lines
5.0 KiB
68 lines
5.0 KiB
USE [Vertragsverwaltung_20160404]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt4] Script Date: 02.12.2016 09:08:53 ******/
|
|
DROP FUNCTION [dbo].[lc_rpt4]
|
|
GO
|
|
/****** Object: UserDefinedFunction [dbo].[lc_rpt4] Script Date: 02.12.2016 09:08:55 ******/
|
|
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
|