none
schwierige Zuordnung zweier Tabellen RRS feed

  • Frage

  • Ich habe kürzlich hier schon mal ein ähnliches Problem geschildert. Jetzt ist es aber noch schwieriger:

    Wir schicken Listen raus an eine andere Firma zum Bearbeiten (= Requesttabelle, #req) und bekommen sie regelmäßig zurück, aber aus technischen Gründen in anderer Form (= Resulttabelle, #res). Ich habe ein paar Beispiele zum direkten Ausprobieren zusammengestellt:

    Create Table #Req (IDReq int not NULL Primary Key, DNo int, sendDate datetime);
    Create Table #Res (IDRes int not NULL Primary Key, IDReq int, DNo int, sendDate datetime, resultDate datetime);

    insert into #req (IDReq, DNo, sendDate)
    select 2090, 14192, '2012-06-01 11:17:00.000' UNION
    select 4870, 10412, '2012-06-11 21:18:00.000' UNION
    select 9521, 14192, '2012-06-12 20:17:00.000' UNION
    select 23802, 22847, '2012-07-04 10:43:00.000' UNION
    select 34097, 14192, '2012-10-18 08:37:00.000';

    insert into #res (IDRes, DNo, resultDate)
    select 465,    14192, '2012-06-11 19:36:00.000' UNION
    select 983,    14192, '2012-06-11 19:37:00.000' UNION
    select 5267, 14192,    '2012-06-23 22:49:00.000' UNION
    select 14792, 10412, '2012-07-13 10:46:00.000' UNION
    select 19839, 22847, '2012-07-19 10:46:00.000' UNION
    select 35712, 14192, '2012-11-09 09:16:00.000';

    select DNo, IDReq, sendDate from #req order by DNo, IDReq
    select DNo,IDRes, IDReq, SendDate, resultDate  from #res order by DNo, IDRes

    (In den Originaltabellen hängen selbstverständlich noch andere Felder dran, die spielen aber für mein Problem keine Rolle) So, ich muss jetzt regelmäßig in Tabelle #res die Ausgangszeile aus #req zuordnen, damit ich weiß. woher der resultiert. Es gelten folgende Regeln: 

    Auf einen Datensatz aus #req können 0 bis ca. 10 Datensätze in #res resultieren.

    Entscheidend sind die Felder DNo, SendDate und ResultDate

    Pro DNo in #req gilt die Zuordnung, dass das Resultdate in #res  größer als das SendDate, aber kleiner als SendDate des nächsten SendDate -Wertes sein muss.

    Die Aufgabe ist es, #res mit dem zugehörigen SendDate zu aktualisieren. Alternativ kann man auch die entsprechende IDRes aktualisieren.Das Ergebnis muss dann so aussehen:

    DNo IDRes IDReq SendDate resultDate
    10412 14792 4870 2012-06-11 21:18:00.000 2012-07-13 10:46:00.000
    14192 465 2090 2012-06-01 11:17:00.000 2012-06-11 19:36:00.000
    14192 983 2090 2012-06-01 11:17:00.000 2012-06-11 19:37:00.000
    14192 5267 9521 2012-06-12 20:17:00.000 2012-06-23 22:49:00.000
    14192 35712 34097 2012-10-18 08:37:00.000 2012-11-09 09:16:00.000
    22847 19839 23802 2012-07-04 10:43:00.000 2012-07-19 10:46:00.000

    Ich hoffe, das war einigermaßen verständlich. Dazu habe ich schon einiges auspropbiert, z.B. über

    Select * from #res inner join
    #req on #req.DNo = #res.DNo
    where #res.resultDate > #req.SendDate

    kam aber leider nicht wirklich weiter. Geht das überhaupt via klasischem SQL?

    Donnerstag, 14. März 2013 14:29

Antworten

  • Hallo Klaus-Dieter,

    eine Lösung könnte so aussehen:

    use tempdb go create table dbo.Request ( RequestID int not null primary key, DNo int not null, SendDate datetime not null ) go create table dbo.Result ( ResultID int not null primary key, RequestID int null, DNo int not null, SendDate datetime null, ResultDate datetime not null ) go insert into dbo.Request(RequestID, DNo, SendDate) values (2090, 14192, '2012-06-01 11:17:00.000') ,(4870, 10412, '2012-06-11 21:18:00.000') ,(9521, 14192, '2012-06-12 20:17:00.000') ,(23802, 22847, '2012-07-04 10:43:00.000') ,(34097, 14192, '2012-10-18 08:37:00.000') go insert into dbo.Result(ResultID, DNo, ResultDate) values (465, 14192, '2012-06-11 19:36:00.000') ,(983, 14192, '2012-06-11 19:37:00.000') ,(5267, 14192, '2012-06-23 22:49:00.000') ,(14792, 10412, '2012-07-13 10:46:00.000') ,(19839, 22847, '2012-07-19 10:46:00.000') ,(35712, 14192, '2012-11-09 09:16:00.000') go ;with req as ( select row_number() over(order by DNo, SendDate) AS RowNumber,* from dbo.Request ), t as ( select r1.DNo,r1.RequestID,r1.SendDate SendDateFrom, r2.SendDate SendDateTo from req as r1 left join req r2 on r1.DNo=r2.DNo and r1.RowNumber = r2.RowNumber-1 ) select rq.DNo, rq.SendDateFrom as SendDate, rs.ResultDate from t as rq left join dbo.Result rs on rq.DNo = rs.DNo and

    (rs.ResultDate > rq.SendDateFrom and
    (rs.ResultDate <= rq.SendDateTo or rq.SendDateTo is null))

    go drop table dbo.Request, dbo.Result go


    DNo	SendDate	        ResultDate
    10412	2012-06-11 21:18:00.000	2012-07-13 10:46:00.000
    14192	2012-06-01 11:17:00.000	2012-06-11 19:36:00.000
    14192	2012-06-01 11:17:00.000	2012-06-11 19:37:00.000
    14192	2012-06-12 20:17:00.000	2012-06-23 22:49:00.000
    14192	2012-10-18 08:37:00.000	2012-11-09 09:16:00.000
    22847	2012-07-04 10:43:00.000	2012-07-19 10:46:00.000

    • Bearbeitet Yury Iwtschenko Freitag, 15. März 2013 21:00 between durch >< = ersetzt
    • Als Antwort vorgeschlagen Elmar BoyeEditor Mittwoch, 20. März 2013 15:47
    • Als Antwort markiert KlausDieter L Freitag, 22. März 2013 14:46
    Freitag, 15. März 2013 20:49

Alle Antworten

  • Da mir das Problem ganz schön unter den Nägeln brennt, habe ich noch einiges überlegt. OK, ich habe die letze SQL-Anweisung etwas aufgehübscht:

    Select  ROW_NUMBER() OVER(PARTITION BY #req.DNo ORDER BY #req.DNo, IDRes, #req.IDReq) AS RowNo,
            #req.DNo, IDRes, #res.IDReq, #req.IDReq, #res.Senddate, #req.Senddate, resultdate
    from #res inner join
    #req on #req.DNo = #res.DNo
    where #res.resultDate > #req.SendDate
    --and #req.DNo = 14192
    order by #req.DNo, IDRes, #req.IDReq

    Aufgabe ist es, die richtigen Kombinationen zu finden. Die erste und letzte Zeile sind eindeutig und man kann deshalb das Problen auf DNo = 14192 reduzieren. Auch kann man auf die Datumsfelder verzichten, somit bleibt sowas übrig:

    Select ROW_NUMBER() OVER(PARTITION BY #req.DNo ORDER BY #req.DNo, IDRes, #req.IDReq) AS RowNo,
            #req.DNo, IDRes, #req.IDReq
    from #res inner join
    #req on #req.DNo = #res.DNo
    where #res.resultDate > #req.SendDate
    and #req.DNo = 14192
    order by #req.DNo, IDRes, #req.IDReq

    Daraus resultiert:

    RowNo    DNo    IDRes    IDReq
    1    14192    465    2090
    2    14192    983    2090
    3    14192    5267    2090
    4    14192    5267    9521
    5    14192    35712    2090
    6    14192    35712    9521
    7    14192    35712    34097

    Die Zeile 5 fällt auf jeden Fall weg, da IDReq kleiner als der drüberstehende Wert ist.

    In Zeile 1 und 2 ist IDres unterschiedlich, somit sind beide Zeilen korrekt.

    In Zeile 3 und 4 ist IDRes gleich, somit ist der mit der größeren ID, also Zeile 4 gültig.

    Für die restlichen Zeilen mit IDRes = 35712 gilt das gleiche: der höchste Wert ist korrekt

    Somit bleiben diese Zeilen übrig:

    RowNo    DNo    IDRes    IDReq
    1    14192    465    2090
    2    14192    983    2090
    4    14192    5267    9521
    7    14192    35712    34097

    Mit so einer Auwahl kann man problemlos in Tabelle #res mit IDResq und Senddate aktualisieren. Aber wie kann man die beschriebene Logik in SQL umsetzen?

    Ich wäre sehr dankbar, wenn mir jemand da weiterhilft.

    Gruß Klaus-Dieter

    Freitag, 15. März 2013 13:31
  • Hallo Klaus-Dieter,

    eine Lösung könnte so aussehen:

    use tempdb go create table dbo.Request ( RequestID int not null primary key, DNo int not null, SendDate datetime not null ) go create table dbo.Result ( ResultID int not null primary key, RequestID int null, DNo int not null, SendDate datetime null, ResultDate datetime not null ) go insert into dbo.Request(RequestID, DNo, SendDate) values (2090, 14192, '2012-06-01 11:17:00.000') ,(4870, 10412, '2012-06-11 21:18:00.000') ,(9521, 14192, '2012-06-12 20:17:00.000') ,(23802, 22847, '2012-07-04 10:43:00.000') ,(34097, 14192, '2012-10-18 08:37:00.000') go insert into dbo.Result(ResultID, DNo, ResultDate) values (465, 14192, '2012-06-11 19:36:00.000') ,(983, 14192, '2012-06-11 19:37:00.000') ,(5267, 14192, '2012-06-23 22:49:00.000') ,(14792, 10412, '2012-07-13 10:46:00.000') ,(19839, 22847, '2012-07-19 10:46:00.000') ,(35712, 14192, '2012-11-09 09:16:00.000') go ;with req as ( select row_number() over(order by DNo, SendDate) AS RowNumber,* from dbo.Request ), t as ( select r1.DNo,r1.RequestID,r1.SendDate SendDateFrom, r2.SendDate SendDateTo from req as r1 left join req r2 on r1.DNo=r2.DNo and r1.RowNumber = r2.RowNumber-1 ) select rq.DNo, rq.SendDateFrom as SendDate, rs.ResultDate from t as rq left join dbo.Result rs on rq.DNo = rs.DNo and

    (rs.ResultDate > rq.SendDateFrom and
    (rs.ResultDate <= rq.SendDateTo or rq.SendDateTo is null))

    go drop table dbo.Request, dbo.Result go


    DNo	SendDate	        ResultDate
    10412	2012-06-11 21:18:00.000	2012-07-13 10:46:00.000
    14192	2012-06-01 11:17:00.000	2012-06-11 19:36:00.000
    14192	2012-06-01 11:17:00.000	2012-06-11 19:37:00.000
    14192	2012-06-12 20:17:00.000	2012-06-23 22:49:00.000
    14192	2012-10-18 08:37:00.000	2012-11-09 09:16:00.000
    22847	2012-07-04 10:43:00.000	2012-07-19 10:46:00.000

    • Bearbeitet Yury Iwtschenko Freitag, 15. März 2013 21:00 between durch >< = ersetzt
    • Als Antwort vorgeschlagen Elmar BoyeEditor Mittwoch, 20. März 2013 15:47
    • Als Antwort markiert KlausDieter L Freitag, 22. März 2013 14:46
    Freitag, 15. März 2013 20:49
  • Hi Yuri,

    recht herzlichen Dank für deine Hilfe - ja, so geht es!

    Ich gebe aber zu, dass ich Probleme mit derart verschachtelten Ausdrücken habe und nicht alles verstanden habe. Normalerweise löse ich solche komplexen Ausdrücke indem ich alles in Einzelpakete zerlege, z.B.  als Temp-Tabellen (Performance spielt keine große Rolle, denn es geht nur um gelegentlich zu ziehende Auswertungen). Aber das geht hier wohl nicht.

    Aber wie auch immer, jetzt weiß ich nicht wie ich den with-Ausduck weiterverarbeiten kann, denn ich muss ja noch die result-Tabelle mit dem SendDate aktualisieren. Wenn ich den ganzen Ausdruck als Subquery handeln könnte, dann wäre es kein Problem für mich. Aber sowas wie

    select sub.* from (

    [dein kompletter Ausdruck]

    ) as sub

    geht leider nicht


    Samstag, 16. März 2013 18:07
  • Eben kam ich selbst auf die Lösung: Das entscheidende Select ist dieses hier:

    select
            rq.DNo, rq.SendDateFrom as SendDate, rs.ResultDate

    Die Ausdücke darüber sind ja quasi Subqueries. An "with" muss ich mich erst noch gewöhnen ;-)

    Samstag, 16. März 2013 18:29