Hallo Steffan,
ich versuchs mal.
Die View auf der diese spezielle Abfrage erstellt werden soll sieht so aus:
SELECT Auftragsnr, Artikelname, Anzahl, Anz_Miettage, Preis_DM, Artikeltyp, LS_Datum, LS_Rück_Datum, Plan_LS_Datum, Plan_Rück_Datum
FROM dbo.Auftrag_Einzelpos_Artikel
WHERE (Auftragsnr > 40000) AND ((DATEPART(year, LS_Datum) >= 2014) OR (DATEPART(year, LS_Rück_Datum) >= 2014)) AND (Artikeltyp <> '9') AND (Plan_LS_Datum IS NOT NULL) AND (Plan_Rück_Datum IS NOT NULL)
UNION
SELECT Auftragsnr, Artikelname, Anzahl, Anz_Miettage, Preis_DM, Artikeltyp, LS_Datum, LS_Rück_Datum, Plan_LS_Datum, Plan_Rück_Datum
FROM dbo.View_Auftrag_Einzelpos_Artikel_Archiv
WHERE (Auftragsnr > 40000) AND ((DATEPART(year, LS_Datum) >= 2014) OR (DATEPART(year, LS_Rück_Datum) >= 2014)) AND (Artikeltyp <> '9') AND (Plan_LS_Datum IS NOT NULL) AND (Plan_Rück_Datum IS NOT NULL)
Die Daten: (Bitte nicht an DM stören, die DB ist rel. alt)
AuftrNR; Artikelname; Anzahl; Anz_Miettage ; Preis_DM; Artikeltyp; LS_Datum; LS_Rück_Datum; Plan_LS-Datum; Plan_Rück_Datum
| 68780 |
HMI-Verlängerungskabel 575/M8/1,2/1,8 KW ARRI 7m |
2 |
2,5 |
5,0000 |
L |
31.03.2014 00:00:00 |
07.04.2014 15:00:01 |
31.03.2014 09:00:00 |
04.04.2014 08:00:00 |
| 68780 |
HMI-Verlängerungskabel 9/6KW ARRI 10m |
5 |
2,5 |
0,0000 |
F |
31.03.2014 00:00:00 |
04.04.2014 14:35:12 |
31.03.2014 09:00:00 |
04.04.2014 08:00:00 |
Diese Daten werden mit einer stored proc gelesen, ein paar Daten berechnet und dann in eine Statistik-Tabelle gespeichert.
Die Zieltabelle sieht so aus:
CREATE TABLE [dbo].[Statistik_Artikel](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Artikelname_d] [nvarchar](62) NOT NULL,
[Anzahl] [int] NULL,
[Bestand] [int] NULL,
[AnzahlFremd] [int] NULL,
[Preis] [money] NULL,
[Wert_Versicherung] [money] NULL,
[Wert_Anschaffung] [money] NULL,
[Summe_Miettage] [int] NULL,
[Anzahl_Miettage] [real] NULL,
[Auslastung] [real] NULL,
[Fremdquote] [real] NULL,
[AnzArtikelUnterwegs] [int] NULL,
[Jahr] [int] NULL,
[Monat] [int] NULL,
CONSTRAINT [PK_Statistik_Artikel] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
und meine stored proc so:
CREATE PROCEDURE [dbo].[StatistikArtikelUpdate]
@Jahr int,
@Monat int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @Jahrtage int;
set @Jahrtage = 365;
declare @Entkäfern bit;
set @Entkäfern = 0;
declare @dtMonatsanfang datetime;
declare @dtMonatsende datetime;
declare @AnzTageMonat int;
declare @Tag1 datetime;
declare @Tag2 datetime;
declare @Art nvarchar(62);
declare @Bestand int;
declare @WertVers money;
declare @WertAnsch money;
declare @Anzahl int;
declare @Preis money;
declare @SumMiet int;
declare @SumMiet1 int;
declare @AnzMiet real;
declare @Auslastung real;
declare @Fremd real;
declare @AnzFremd int;
declare @AnzUnterwegs int;
declare @Datum1 datetime;
declare @Datum2 datetime;
declare @Auftr int;
declare @Anz int;
declare @ID bigint;
----------------------
set @Art = '';
set @Bestand = -1;
set @WertVers = -1;
set @WertAnsch = -1;
----------------------
declare cur cursor for
SELECT DISTINCT Artikelname_d, Wert_Versicherung, Wert_Anschaffung, Bestand
FROM Artikel;
open cur;
fetch next from cur
into @Art, @WertVers, @WertAnsch, @Bestand;
select @dtMonatsanfang = CAST(STR(@Jahr * 10000 + @Monat * 100 + 1) AS DATETIME)
select @dtMonatsende = CONVERT(VARCHAR(12), DATEADD(MONTH,DATEDIFF(MONTH,0,@dtMonatsanfang),30), 104)
select @AnzTageMonat = 32 - day(@dtMonatsanfang - day(@dtMonatsanfang) + 32);
while @@fetch_status = 0
begin
set @Anzahl = -1;
--set @Anzahl1 = -1;
--set @Anzahl2 = -1;
set @Preis = -1;
--set @Preis1 = -1;
--set @Preis2 = -1;
set @SumMiet = -1;
set @SumMiet1 = -1;
--set @SumMiet2 = -1;
set @AnzMiet = -1;
--set @AnzMiet1 = -1;
--set @AnzMiet2 = -1;
set @Auslastung = -1;
set @Fremd = -1;
set @AnzFremd = -1;
--set @AnzFremd1 = -1;
--set @AnzFremd2 = -1;
set @AnzUnterwegs = -1;
--zum debuggen benötigt
if @Art = N'Velvet 2 Netzteil'
set @Entkäfern = 1;
if @Entkäfern = 1
begin
print '*********';
print 'Jahr: ' + cast(@Jahr as nvarchar);
print 'Monat: ' + cast(@Monat as nvarchar);
print '@dtMonatsanfang: ' + cast(@dtmonatsanfang as nvarchar);
print '@dtMonatsende: ' + cast(@dtMonatsende as nvarchar);
print 'Anzahl Tage im Monat: ' + cast(@AnzTageMonat as nvarchar);
end
if @Entkäfern = 1
begin
print 'Artikelname: ' + @Art;
print 'Bestand: ' + cast(@Bestand as nvarchar);
end
--@Anzahl
SELECT @Anzahl = ISNULL(SUM(ISNULL(Anzahl, 0)), 0)
FROM dbo.View_StatistikArtikel -- Auftrag_Einzelpos_Artikel
WHERE (Auftragsnr > 40000) AND ((DATEPART(year, LS_Datum) = @Jahr) AND (DATEPART(month, LS_Datum) = @Monat) OR (DATEPART(year, LS_Rück_Datum) = @Jahr) AND (DATEPART(month, LS_Rück_Datum) = @Monat)) AND (LS_Rück_Datum is not null) AND (Artikelname = @Art) AND (Anzahl > 0) AND (Artikeltyp <> 'V');
--set @Anzahl = ISNULL(@Anzahl1, 0) --+ ISNULL(@Anzahl2, 0);
if @Entkäfern = 1
begin
--print 'Anzahl1 = ' + cast(@Anzahl1 as nvarchar);
--print 'Anzahl2 = ' + cast(@Anzahl2 as nvarchar);
print 'Anzahl = ' + cast(@Anzahl as nvarchar);
print '---'
end
--@Preis
SELECT @Preis = ISNULL(SUM(ISNULL(Preis_DM, 0)), 0)
FROM dbo.View_StatistikArtikel -- Auftrag_Einzelpos_Artikel
WHERE (Auftragsnr > 40000) AND ((DATEPART(year, LS_Datum) = @Jahr) AND (DATEPART(month, LS_Datum) = @Monat) OR (DATEPART(year, LS_Rück_Datum) = @Jahr) AND (DATEPART(month, LS_Rück_Datum) = @Monat)) AND (LS_Rück_Datum is not null) AND (Artikelname = @Art) AND (Anzahl > 0) AND (Artikeltyp <> 'V');
--set @Preis = ISNULL(@Preis1, 0) --+ ISNULL(@Preis2, 0);
if @Entkäfern = 1
begin
--print 'Preis1 = ' + cast(@Preis1 as nvarchar);
--print 'Preis2 = ' + cast(@Preis2 as nvarchar);
print 'Preis = ' + cast(@Preis as nvarchar);
print '---'
end
--@AnzMiet
SELECT @AnzMiet = ISNULL(SUM(ISNULL(Anz_Miettage, 0)), 0)
FROM dbo.View_StatistikArtikel -- Auftrag_Einzelpos_Artikel
WHERE (Auftragsnr > 40000) AND ((DATEPART(year, LS_Datum) = @Jahr) AND (DATEPART(month, LS_Datum) = @Monat) OR (DATEPART(year, LS_Rück_Datum) = @Jahr) AND (DATEPART(month, LS_Rück_Datum) = @Monat)) AND (LS_Rück_Datum is not null) AND (Artikelname = @Art) AND (Anzahl > 0) AND (Artikeltyp <> 'V');
--set @AnzMiet = ISNULL(@AnzMiet1, 0) --+ ISNULL(@AnzMiet2, 0);
if @Entkäfern = 1
begin
--print 'AnzMiet1 = ' + cast(@AnzMiet1 as nvarchar);
--print 'AnzMiet2 = ' + cast(@AnzMiet2 as nvarchar);
print 'AnzMiet = ' + cast(@AnzMiet as nvarchar);
print '---'
end
--@SumMiet
if @Entkäfern = 1
begin
print '-> Schleife über die SumMiet';
end
declare curSumMiet cursor for
select Auftragsnr, Anzahl, LS_Datum, LS_Rück_Datum
FROM dbo.View_StatistikArtikel
WHERE (Auftragsnr > 40000) AND ((DATEPART(year, LS_Datum) = @Jahr) AND (DATEPART(month, LS_Datum) = @Monat) OR (DATEPART(year, LS_Rück_Datum) = @Jahr) AND (DATEPART(month, LS_Rück_Datum) = @Monat)) AND (LS_Rück_Datum is not null) AND (Artikelname = @Art) AND (Anzahl > 0) AND (Artikeltyp <> 'V');
set @SumMiet1 = 0;
open curSumMiet;
fetch next from curSumMiet
into @Auftr, @Anz, @Datum1, @Datum2;
while @@fetch_status = 0
begin
if @Datum1 < @dtMonatsanfang
set @Tag1 = @dtmonatsanfang;
else
set @Tag1 = @Datum1;
if @Datum2 > @dtMonatsende
set @Tag2 = @dtMonatsende;
else
set @Tag2 = @Datum2;
set @SumMiet1 = @SumMiet1 + (@Anz * (DATEDIFF(day, @Tag1, @Tag2) + 1))
if @Entkäfern = 1
begin
print 'Auftragsnr: ' + cast(@Auftr as nvarchar);
print 'Anzahl: ' + cast(@Anz as nvarchar);
print 'von Tag ' + convert(nvarchar, @Tag1, 104) + ' bis Tag ' + convert(nvarchar, @Tag2, 104)
print 'sind ' + cast((DATEDIFF(day, @Tag1, @Tag2) + 1) as nvarchar) + ' Tage'
print 'macht ' + cast((@Anz * (DATEDIFF(day, @Tag1, @Tag2) + 1)) as nvarchar) + ' Tage'
end
fetch next from curSumMiet
into @Auftr, @Anz, @Datum1, @Datum2;
end
close curSumMiet;
deallocate curSumMiet;
set @SumMiet = ISNULL(@SumMiet1, 0) --+ ISNULL(@SumMiet2, 0);
if @Entkäfern = 1
begin
--print 'SumMiet1 = ' + cast(@SumMiet1 as nvarchar);
--print 'SumMiet2 = ' + cast(@SumMiet2 as nvarchar);
print 'SumMiet = ' + cast(@SumMiet as nvarchar);
print '---'
end
--@Bestand
if @Bestand > 0
begin
set @Auslastung = (CAST(@SumMiet as real) * 100.0) / (CAST(@Bestand as real) * CAST(@AnzTageMonat as real));
end
if @Entkäfern = 1
begin
print '(CAST(@SumMiet as real) * 100.0): ' + cast((CAST(@SumMiet as real) * 100.0) as nvarchar);
print '(CAST(@Bestand as real) * CAST(@AnzTageMonat as real)): '-- + cast((CAST(@Bestand as real) * CAST(@AnzTageMonat as real)) as nvarchar);
print '(' + CAST(CAST(@Bestand as real) as nvarchar) + ' * ' + CAST(CAST(@AnzTageMonat as real) as nvarchar) + '): ' + cast((CAST(@Bestand as real) * CAST(@AnzTageMonat as real)) as nvarchar);
print 'Auslastung = ' + cast(@Auslastung as nvarchar);
print '---'
end
--@AnzFremd
SELECT DISTINCT @AnzFremd = ISNULL(SUM(ISNULL(Anzahl, 0)), 0)
FROM dbo.View_StatistikArtikel -- Auftrag_Einzelpos_Artikel
WHERE ((DATEPART(year, LS_Datum) = @Jahr) AND (DATEPART(month, LS_Datum) = @Monat) OR (DATEPART(year, LS_Rück_Datum) = @Jahr) AND (DATEPART(month, LS_Rück_Datum) = @Monat)) AND (LS_Rück_Datum is not null) AND (Artikeltyp = N'F') AND (Auftragsnr > 40000) AND (Artikelname = @Art) AND (Anzahl > 0)
--set @AnzFremd = ISNULL(@AnzFremd1, 0) --+ ISNULL(@AnzFremd2, 0);
if @Entkäfern = 1
begin
--print 'AnzFremd1 = ' + cast(@AnzFremd1 as nvarchar);
--print 'AnzFremd2 = ' + cast(@AnzFremd2 as nvarchar);
print 'AnzFremd = ' + cast(@AnzFremd as nvarchar);
print '---'
end
set @Fremd = -1; --@AnzFremd;
--@AnzUnterwegs
SELECT DISTINCT @AnzUnterwegs = ISNULL(SUM(ISNULL(Anzahl, 0)), 0)
FROM dbo.View_StatistikArtikel -- Auftrag_Einzelpos_Artikel
WHERE (DATEPART(year, LS_Datum) = @Jahr) AND (DATEPART(month, LS_Datum) = @Monat) AND (LS_Rück_Datum is null) AND (Auftragsnr > 40000) AND (Artikelname = @Art) AND (Anzahl > 0) AND (Artikeltyp <> 'V');
---
set @ID = -1;
select @ID = isnull(ID, -1) from dbo.Statistik_Artikel where (Artikelname_d = @Art) AND (Jahr = @Jahr) and (Monat = @Monat);
--print '@Art = ' + @Art;
--print '@Jahr = ' + cast(@Jahr as nvarchar);
--print '@Monat = ' + cast(@Monat as nvarchar);
--print '@ID = ' + cast(@ID as nvarchar);
if @ID = -1
begin
insert into dbo.Statistik_Artikel(Artikelname_d, Preis, Wert_Versicherung, Wert_Anschaffung, Summe_Miettage, Anzahl_Miettage, Auslastung, Fremdquote, Jahr, Monat, Anzahl, Bestand, AnzahlFremd, AnzArtikelUnterwegs)
values(@Art, @Preis, @WertVers, @WertAnsch, @SumMiet, @AnzMiet, @Auslastung, @Fremd, @Jahr, @Monat, @Anzahl, @Bestand, @AnzFremd, @AnzUnterwegs);
--print 'inserted';
end
else
begin
update dbo.Statistik_Artikel
set Preis = @Preis,
Wert_Versicherung = @WertVers,
Wert_Anschaffung = @WertAnsch,
Summe_Miettage = @SumMiet,
Anzahl_Miettage = @AnzMiet,
Auslastung = @Auslastung,
Fremdquote = @Fremd,
Bestand = @Bestand,
AnzahlFremd = @AnzFremd,
AnzArtikelUnterwegs = @AnzUnterwegs
where ID = @ID;
--print 'updated';
end
fetch next from cur
into @Art, @WertVers, @WertAnsch, @Bestand;
end --ENDE Cursour-Schleife
close cur;
deallocate cur;
END
An dieser Stelle "set @Fremd = -1;" brauche ich jetzt irgendeine Funktion / Abfrage / o.ä. die mir angibt, wie viele Fremdartikel ich ein einem Tag maximal habe.
Ich kann es irgendwie nicht besser ausdrücken, aber hoffe, dass es jetzt etwas verständlicher ist.
