Ich versuche, eine PIVOT TSQL-Anweisung zu erstellen, die die Produkte nach Datum und Bundesland / Provinz zusammenfasst und die AVG-Transitzeit angibt. Folgendes habe ich bisher:
select *
from (select createdate [Date Processed],
stateprovince as [Province],
count(*) as [Total],
avg(datediff(day,createdate,t.eventdate)) as [AVG Delivery],
product
from recipient C left outer join
(select delivid, product, eventdesc, eventdate, eventcode
from deliverystatus
where delivid in (select max(deliv_id)
from deliverystatus
where eventcode = 'DELIVERED'
group by product)) as t ON c.product = t.product
where account = 3519 and consol <>'' and trknum <> '' and C.createdate between '2/4/2016' and '2/4/2016'
group by C.createdate, c.stateprovince, c.product
) as Q
pivot (
count(product)
for [Province] in (NY, IL, GA)
) as PVT
Mein Ergebnis ist:
Date Processed Total AVG Transit NY IL GA
2016-02-04 00:00:00.000 1 8 0 0 1
2016-02-04 00:00:00.000 1 11 2 4 1
2016-02-04 00:00:00.000 1 12 0 0 0
2016-02-04 00:00:00.000 1 15 0 0 0
Ich brauche das Ergebnis, um zu sein:
Date Processed Total AVG Transit NY IL GA
2016-02-04 00:00:00.000 8 11.5 2 4 2
Das ultimative Ziel ist es, den AVG Transit nach Bundesstaat / Provinz wie folgt aufzulisten:
Date Processed Total Total AVG NY AVG IL AVG GA AVG
2016-02-04 00:00:00.000 8 11.5 2 8 4 11 2 15
Danke im Voraus.
Sie müssen GROUP BYnach dem Pivot eine Klausel hinzufügen und entweder den Wert AVG SUModer MAXfür jede Ausgabespalte verwenden:
select [Date Processed], SUM(NY+IL+GA) AS [Total], AVG([AVG Delivery]) AS [AVG Delivery], SUM(NY) AS NY, SUM(IL) AS IL, SUM(GA) AS GA
from (select createdate [Date Processed],
stateprovince as [Province],
count(*) as [Total],
avg(datediff(day,createdate,t.eventdate)) as [AVG Delivery],
product
from recipient C left outer join
(select delivid, product, eventdesc, eventdate, eventcode
from deliverystatus
where delivid in (select max(deliv_id)
from deliverystatus
where eventcode = 'DELIVERED'
group by product)) as t ON c.product = t.product
where account = 3519 and consol <>'' and trknum <> '' and C.createdate between '2/4/2016' and '2/4/2016'
group by C.createdate, c.stateprovince, c.product
) as Q
pivot (
count(product)
for [Province] in (NY, IL, GA)
) as PVT
group by [Date Processed]
Dieser Artikel stammt aus dem Internet. Bitte geben Sie beim Nachdruck die Quelle an.
Bei Verstößen wenden Sie sich bitte [email protected] Löschen.
Lass mich ein paar Worte sagen