PDA

Vollständige Version anzeigen : T SQL Aufgabe


strausto
11.11.2007, 18:54
Aufgabe

tblPeriod


2007-01-01 00:00:00
2007-02-01 00:00:00
2007-03-01 00:00:00
2007-04-01 00:00:00
2007-05-01 00:00:00
2007-06-01 00:00:00
2007-07-01 00:00:00
2007-08-01 00:00:00
2007-09-01 00:00:00
2007-10-01 00:00:00
2007-11-01 00:00:00
2007-12-01 00:00:00


tblCustomerTurnover

customer period turnover
A1 2007-01-01 00:00:00 100,50
A1 2007-05-01 00:00:00 212,35
A1 2007-12-01 00:00:00 100,00
B2 2007-02-01 00:00:00 987,34
B2 2007-03-01 00:00:00 23,87
B2 2007-06-01 00:00:00 456,87


gewünschtes Resultat

period customer (Kein Spaltenname)
2007-01-01 00:00:00 A1 100,50
2007-02-01 00:00:00 A1 0,00
2007-03-01 00:00:00 A1 0,00
2007-04-01 00:00:00 A1 0,00
2007-05-01 00:00:00 A1 212,35
2007-06-01 00:00:00 A1 0,00
2007-07-01 00:00:00 A1 0,00
2007-08-01 00:00:00 A1 0,00
2007-09-01 00:00:00 A1 0,00
2007-10-01 00:00:00 A1 0,00
2007-11-01 00:00:00 A1 0,00
2007-12-01 00:00:00 A1 100,00
2007-01-01 00:00:00 B2 0,00
2007-02-01 00:00:00 B2 987,34
2007-03-01 00:00:00 B2 23,87
2007-04-01 00:00:00 B2 0,00
2007-05-01 00:00:00 B2 0,00
2007-06-01 00:00:00 B2 456,87
2007-07-01 00:00:00 B2 0,00
2007-08-01 00:00:00 B2 0,00
2007-09-01 00:00:00 B2 0,00
2007-10-01 00:00:00 B2 0,00
2007-11-01 00:00:00 B2 0,00
2007-12-01 00:00:00 B2 0,00



use tempdb
go
if not object_id( 'tblPeriod' ) is null
drop table tblPeriod
create table tblPeriod ( Period smalldatetime not null primary key)
go
declare @i int
set @i = 0
while @i <12
begin
insert tblPeriod select dateadd(m,@i,'20070101')
set @i = @i + 1
end
go
select * from tblPeriod
go
if not object_id( 'tblCustomerTurnover ' ) is null
drop table tblCustomerTurnover
create table tblCustomerTurnover ( customer varchar(2) not null,
period smalldatetime not null,
turnover money not null )
go
insert tblCustomerTurnover select 'A1','20070101',100.50
insert tblCustomerTurnover select 'A1','20070501',212.35
insert tblCustomerTurnover select 'A1','20071201',100.00
insert tblCustomerTurnover select 'B2','20070201',987.34
insert tblCustomerTurnover select 'B2','20070301',23.87
insert tblCustomerTurnover select 'B2','20070601',456.87
go
select * from tblCustomerTurnover
go

Josef P.
12.11.2007, 10:42
Im Anhang ist noch eine Ac00-mdb mit den Daten, damit jene - die nur Access nutzen - nicht mit der Datenerzeugung aufgehalten werden. ;)

letts
12.11.2007, 13:40
Hallo,

hier meine Variante.

use tempdb
go
if not object_id( 'tblPeriod' ) is null
drop table tblPeriod
create table tblPeriod ( Period smalldatetime not null primary key)
go
declare @i int
set @i = 0
while @i <12
begin
insert tblPeriod select dateadd(m,@i,'20070101')
set @i = @i + 1
end

go
if not object_id( 'tblCustomerTurnover ' ) is null
drop table tblCustomerTurnover
create table tblCustomerTurnover ( customer varchar(2) not null,
period smalldatetime not null,
turnover money not null )
go
insert tblCustomerTurnover select 'A1','20070101',100.50
insert tblCustomerTurnover select 'A1','20070501',212.35
insert tblCustomerTurnover select 'A1','20071201',100.00
insert tblCustomerTurnover select 'B2','20070201',987.34
insert tblCustomerTurnover select 'B2','20070301',23.87
insert tblCustomerTurnover select 'B2','20070601',456.87
go



If not object_id( 'tblJoinDaten' ) is null
DROP TABLE tblJoinDaten
go

CREATE TABLE tblJoinDaten ( customer varchar(2) not null,
period smalldatetime not null)
go

DECLARE @DatMin smalldatetime,
@DatMax smalldatetime,
@GrpCustomer varchar(2),
@intDays int

Set @DatMin = (Select Min(period) from tblCustomerTurnover)
Set @DatMax = (Select Max(period) from tblCustomerTurnover)
Set @intDays = Datediff(month,@DatMin,@DatMax)


Declare crsCustomer Cursor For
Select customer from tblCustomerTurnover
Group By customer

Open crsCustomer
FETCH NEXT FROM crsCustomer
INTO @GrpCustomer
WHILE @@FETCH_STATUS = 0
BEGIN

declare @i int
set @i = 0
while @i <= @intDays
begin

Insert tblJoinDaten( period, customer)
Select Dateadd(month,@i,@Datmin),@GrpCustomer
set @i = @i + 1
end

FETCH NEXT FROM crsCustomer
INTO @GrpCustomer
END
CLOSE crsCustomer
DEALLOCATE crsCustomer

SELECT t1.period,
t1.customer,
t2.turnover

FROM tblJoinDaten t1 LEFT OUTER JOIN
tblCustomerTurnover t2 ON
t1.period = t2.period and
t1.customer = t2.customer

strausto
13.11.2007, 00:14
@letts

Guter Ansatz - versuche es einmal ohne den Cursor, wenn Du noch Lust hast (Es geht auch mit einem(!) SQL Statement ohne weitere Tabellen)

strausto
13.11.2007, 00:30
Danke Josef!

Ich bin mal gespannt wer dass in Access löst...

Aber ich bin guter Dinge dass Du auch diesmal eine Lösung findest!!

letts
13.11.2007, 11:49
Hallo Torsten,

da ich nicht weiss, wie ich die fehlenden Daten( pl. Datum) in einem(!) SQL Statement generieren kann, habe ich den Weg über den Cursor und Hilfstabelle genommen.
Bin aber gespannt, was da wieder für ein SQL Konstrukt zustande kommen wird :boah:

Josef P.
13.11.2007, 12:13
Ein Teil der Lösung sieht bei mir so aus:
select
P.Period, C.customer
FROM
tblPeriod P,
(select distinct customer FROM tblCustomerTurnover) C

Josef P.
13.11.2007, 20:49
Meine Lösung sieht so aus:
SELECT
X.Period, X.customer, ISNULL(CT.Turnover,0)
FROM
(
select
P.Period, C.customer
FROM
tblPeriod P,
(select distinct customer FROM tblCustomerTurnover) C
) X
LEFT JOIN
tblCustomerTurnover AS CT
ON CT.customer = X.Customer AND CT.Period = X.Period
ORDER BY 2, 1


Vielleicht noch eine kleine Ergänzung zu dieser Aufgabe (damit Torsten auch ein wenig rätseln kann. ;)):
Nehmen wir einmal an, es gibt Werte die in einem bestimmten Zeitraum anfallen und dann entsprechend auf die Monate verteilt werden sollen
Beispiel:
Kunde: A1
Start: 01.01.2007
Ende: 10.02.2007
Wert: 100
=> von diesen 100 fallen 75,6 auf den Jänner und 24,4 auf den Februar.

abgeänderte Tabelle:
if not object_id( 'tblCustomerTurnover2' ) is null
drop table tblCustomerTurnover2
create table tblCustomerTurnover2 (
customer varchar(2) not null,
startDate smalldatetime not null,
endDate smalldatetime not null,
turnover money not null )
go
insert tblCustomerTurnover2 select 'A1','20070101','20070210',100
insert tblCustomerTurnover2 select 'A1','20070420','20070510',200
insert tblCustomerTurnover2 select 'A1','20071101','20071231',150
insert tblCustomerTurnover2 select 'B2','20070201','20070315',900
insert tblCustomerTurnover2 select 'B2','20070301','20070320',40
insert tblCustomerTurnover2 select 'B2','20070601','20070831',92
go
Anm.: Überlappungen und Laufzeiten über mehrere Monate sind beabsichtigt. ;)

gewünschtes Resultat:
2007-01-01 00:00:00 A1 75.6097
2007-02-01 00:00:00 A1 24.3902
2007-03-01 00:00:00 A1 NULL
2007-04-01 00:00:00 A1 104.7619
2007-05-01 00:00:00 A1 95.2380
2007-06-01 00:00:00 A1 NULL
2007-07-01 00:00:00 A1 NULL
2007-08-01 00:00:00 A1 NULL
2007-09-01 00:00:00 A1 NULL
2007-10-01 00:00:00 A1 NULL
2007-11-01 00:00:00 A1 73.7704
2007-12-01 00:00:00 A1 76.2295
2007-01-01 00:00:00 B2 NULL
2007-02-01 00:00:00 B2 586.0465
2007-03-01 00:00:00 B2 353.9534
2007-04-01 00:00:00 B2 NULL
2007-05-01 00:00:00 B2 NULL
2007-06-01 00:00:00 B2 30.0000
2007-07-01 00:00:00 B2 31.0000
2007-08-01 00:00:00 B2 31.0000
2007-09-01 00:00:00 B2 NULL
2007-10-01 00:00:00 B2 NULL
2007-11-01 00:00:00 B2 NULL
2007-12-01 00:00:00 B2 NULL

strausto
13.11.2007, 23:59
Sehr gute Lösung Josef!
Und für Access geeignet - so hatte ich das auch ;)
Wofür ein kartesisches Produkt so gut ist.

Auf dem SQL Server würde ich um das kartesische Produkt bewusst anzuzeigen einen Cross Join setzen, den es in Access AFAIK leider nicht gibt.



select
Period = vwPeriodCustomer.Period,
Customer = vwPeriodCustomer.Customer,
Turnover = IsNull(tblCustomerTurnover.Turnover,0)
from
(select
tblPeriod.Period,
vwCustomer.Customer
from
(select distinct
tblCustomerTurnover.customer
from
tblCustomerTurnover) vwCustomer
Cross join
tblPeriod) vwPeriodCustomer
left join
tblCustomerTurnover
on
tblCustomerTurnover.customer = vwPeriodCustomer.customer
AND tblCustomerTurnover.period = vwPeriodCustomer.period
order by
2,1


Danke für Deine Aufgabe,ich werde mir das morgen mal ansehen ...

Josef P.
14.11.2007, 21:00
Danke für Deine Aufgabe,ich werde mir das morgen mal ansehen ...
Ich hoffe, du musst wenigstens ein wenig grübeln. ;)

Im Anhang ist die Tabelle der erweiterten Aufgabe für die Access/Jet-Ratefüchse.

strausto
19.11.2007, 18:01
Hi Josef,

anbei meine Lösung! War wirklich sehr schwierig ;) Danke nochmals für die Aufgabe!

SELECT
PERIOD = PERIOD,
CUSTOMER = VWCUSTOMERPERIOD.CUSTOMER,
NEWTURNOVER = SUM(
TURNOVER / (DATEDIFF(D,STARTDATE,ENDDATE) + 1) *
CASE
WHEN PERIOD = (DATEADD(D,-DAY(STARTDATE),STARTDATE)) + 1 AND PERIOD < ((DATEADD(D,-DAY(ENDDATE),ENDDATE)) + 1) THEN
DATEDIFF(D,STARTDATE,DATEADD(M,1,(DATEADD(D,-DAY(STARTDATE),STARTDATE)))) + 1
WHEN PERIOD = (DATEADD(D,-DAY(STARTDATE),STARTDATE)) + 1 AND PERIOD = ((DATEADD(D,-DAY(ENDDATE),ENDDATE)) + 1) THEN
(DATEDIFF(D,(ENDDATE-DAY(ENDDATE)+1),ENDDATE) + 1)
WHEN PERIOD > (DATEADD(D,-DAY(STARTDATE),STARTDATE)) + 1 AND PERIOD < ((DATEADD(D,-DAY(ENDDATE),ENDDATE)) + 1) THEN
DATEDIFF(D,PERIOD,( DATEADD(M,1,PERIOD) - 1)) + 1
WHEN PERIOD > (DATEADD(D,-DAY(STARTDATE),STARTDATE)) + 1 AND PERIOD = ((DATEADD(D,-DAY(ENDDATE),ENDDATE)) + 1) THEN
DATEDIFF(D,PERIOD,ENDDATE) + 1
END
)
FROM
(
SELECT
TBLPERIOD.*,
VWCUSTOMER.*
FROM
(
SELECT
DISTINCT CUSTOMER
FROM
TBLCUSTOMERTURNOVER2
) VWCUSTOMER
CROSS JOIN
TBLPERIOD
) VWCUSTOMERPERIOD
LEFT JOIN
TBLCUSTOMERTURNOVER2 T
ON
VWCUSTOMERPERIOD.PERIOD BETWEEN (DATEADD(D,-DAY(STARTDATE),STARTDATE)) + 1
AND (DATEADD(D,-DAY(ENDDATE),ENDDATE)) + 1
AND T.CUSTOMER = VWCUSTOMERPERIOD.CUSTOMER
GROUP BY
PERIOD,
VWCUSTOMERPERIOD.CUSTOMER


PERIOD CUSTOMER NEWTURNOVER
2007-01-01 00:00:00 A1 75,609
2007-02-01 00:00:00 A1 24,39
2007-03-01 00:00:00 A1 NULL
2007-04-01 00:00:00 A1 104,7618
2007-05-01 00:00:00 A1 95,238
2007-06-01 00:00:00 A1 NULL
2007-07-01 00:00:00 A1 NULL
2007-08-01 00:00:00 A1 NULL
2007-09-01 00:00:00 A1 NULL
2007-10-01 00:00:00 A1 NULL
2007-11-01 00:00:00 A1 73,77
2007-12-01 00:00:00 A1 76,229
2007-01-01 00:00:00 B2 NULL
2007-02-01 00:00:00 B2 586,0456
2007-03-01 00:00:00 B2 353,953
2007-04-01 00:00:00 B2 NULL
2007-05-01 00:00:00 B2 NULL
2007-06-01 00:00:00 B2 30,00
2007-07-01 00:00:00 B2 31,00
2007-08-01 00:00:00 B2 31,00
2007-09-01 00:00:00 B2 NULL
2007-10-01 00:00:00 B2 NULL
2007-11-01 00:00:00 B2 NULL
2007-12-01 00:00:00 B2 NULL

Josef P.
19.11.2007, 18:56
Und wieder einmal lösten wir die Problemstellung unterschiedlich. ;)

Meine Lösung für T-SQL:
SELECT
X.Period,
X.customer,
Sum(
CT.Turnover
* (
datediff(d,CT.startDate, CT.endDate) + 1
- CASE WHEN (X.Period > CT.startDate) THEN datediff(d, CT.startDate, X.Period) else 0 END
- CASE WHEN (DATEADD(mm,1,X.Period) < CT.endDate) THEN datediff(d, DATEADD(mm,1,X.Period), CT.endDate) + 1 else 0 END
)
/ (datediff(d,CT.startDate, CT.endDate) + 1)
)
FROM
(
select
P.Period, C.customer
FROM
tblPeriod P,
(select distinct customer FROM tblCustomerTurnover2) C
) X
LEFT JOIN
tblCustomerTurnover2 AS CT
ON CT.customer = X.Customer AND CT.endDate >= X.Period AND CT.startDate < DATEADD(mm,1,X.Period)
Group By
X.Period,
X.customer
ORDER BY 2, 1

und für Jet-SQL:
SELECT
X.Period, X.customer, Sum(CT.Turnover
* (
datediff("d",CT.startDate, CT.endDate) + 1
- iif (X.Period > CT.startDate, datediff("d", CT.startDate, X.Period),0)
- iif(DATEADD("m",1,X.Period) < CT.endDate, datediff("d", DATEADD("m",1,X.Period), CT.endDate) + 1, 0)
)
/ (datediff("d",CT.startDate, CT.endDate) + 1)) AS Ergebnis
FROM
[select
P.Period, C.customer
FROM
tblPeriod P,
(select distinct customer FROM tblCustomerTurnover2) C
]. AS X
LEFT JOIN tblCustomerTurnover2 AS CT
ON (CT.customer = X.Customer) AND (CT.endDate >= X.Period) AND (CT.startDate < DATEADD("m",1,X.Period))
GROUP BY
X.Period, X.customer
ORDER BY
2, 1;

strausto
20.11.2007, 09:09
@Josef
Nun denke ich schon seit Tagen über eine neue Herausforderung nach. Ich wusste ja nicht dass wir auf diesem Niveau einsteigen...