J'ai 2 tableaux.
Le premier tableau est un répertoire de mes véhicules, et le second est un tableau où j'ai la date et le compteur kilométrique de ce véhicule (il peut y avoir plus d'un trajet par jour par véhicule). Je veux avoir une sortie où je peux avoir une analyse pour savoir si un véhicule a des lectures d'odomètre. Certains d'entre eux pourraient être signalés comme 0, ce que je veux voir ou peut-être même ne pas avoir de données du tout.
TBLVehicles :
Vehicle Group 2018-01-12 2018-02-12 2018-03-12 2018-04-12 2018-05-12 2018-06-12 ---------------------------------------------------------------------------------- 100 A 1 0 0 0 0 0 101 A 0 0 0 0 2 0 102 B 0 0 0 0 0 0 103 B 0 0 0 0 1 1 104 C 0 0 0 0 0 0 105 C 0 0 0 0 0 2
TBLTrips :
Vehicle StartDate Odometer ----------------------------------------- 100 2018-01-12 100 101 2018-05-12 1000 101 2018-05-12 1010 103 2018-05-12 500 103 2018-06-12 505 105 2018-06-12 0 105 2018-06-12 0
Je voudrais obtenir une sortie comme celle ci-dessous où je peux voir quel véhicule a un odomètre valide par jour dans une plage de dates spécifiée. Ce serait même bien d'avoir une colonne de total à la fin pour voir lesquels n'ont absolument pas d'odomètre dans cette plage de dates.
SORTIE
Vehicle Group ----------------- 100 A 101 A 102 B 103 B 104 C 105 C
3 Réponses :
J'ai préparé un script concernant ce que vous voulez. J'ai utilisé CTE et PIVOT pour trouver ce que vous voulez. Aussi, j'ai créé une table temporaire "Dates". Si vous prévoyez d'exécuter ce rapport plusieurs fois, je vous conseille de créer une table statique pour augmenter les performances.
J'espère que cette méthode peut fonctionner pour vous.
IF OBJECT_ID('TEMPDB..#TBLVehicles') IS NOT NULL
DROP TABLE #TBLVehicles;
CREATE TABLE #TBLVehicles
(
VehicleId INT
);
INSERT INTO #TBLVehicles
VALUES (100),(101),(102),(103),(104),(105);
IF OBJECT_ID('TEMPDB..#TBLTrips') IS NOT NULL
DROP TABLE #TBLTrips;
CREATE TABLE #TBLTrips
(
VehicleId INT
,StartDate DATE
,Odometer INT
);
INSERT INTO #TBLTrips
VALUES
(100,'2018-01-12',100 )
,(101,'2018-05-12',1000 )
,(101,'2018-05-12',1010 )
,(103,'2018-05-12',500 )
,(103,'2018-06-12',505 )
,(105,'2018-06-12',0 )
,(105,'2018-06-12',0 );
DECLARE @SQLString NVARCHAR(MAX)
DECLARE @DateArray VARCHAR(MAX)
DECLARE @StartDate DATE='2018-01-12'
DECLARE @EndDate DATE='2018-06-12'
IF OBJECT_ID('TEMPDB..#Dates') IS NOT NULL
DROP TABLE #Dates;
CREATE TABLE #Dates
(
Date DATE
);
WHILE @StartDate<=@EndDate
BEGIN
INSERT INTO #Dates
SELECT @StartDate
SET @StartDate = DATEADD(DAY,1,@StartDate)
END
SELECT @DateArray=ISNULL(@DateArray+',','')+'['+CAST(Date AS VARCHAR)+']' FROM #Dates ;
SET @SQLString=
'
WITH CTE AS
(
SELECT V.VehicleId
,T1.Date
,CASE WHEN T2.Odometer IS NULL THEN 0 ELSE 1 END OdometerKey
FROM #TBLVehicles V
LEFT JOIN FROM #Dates T1 ON 1=1
LEFT JOIN #TBLTrips T2 ON V.VehicleId = T2.VehicleId AND T1.Date=T2.StartDate
)
SELECT * FROM CTE
PIVOT
(SUM(OdometerKey) FOR Date IN ('+@DateArray+')
) PVT
';
EXEC SP_EXECUTESQL @SQLString
--PRINT(@SQLString)
Pivotez avec une jointure à gauche de Véhicules à Odomètre pour que tous les véhicules soient représentés dans la sortie
Create Table Vehicle
(
Id Int
)
Insert Into Vehicle Values
(100),
(101),
(102),
(103),
(104),
(105)
Create Table Odometer
(
Vehicle_Id Int,
StartDT Date,
Odometer Int
)
Insert Into Odometer Values
(100,'2018-01-12',100),
(101,'2018-05-12',1000),
(101,'2018-05-12',1010),
(103,'2018-05-12',500),
(103,'2018-06-12',505),
(105,'2018-06-12',0),
(105,'2018-06-12',0);
Select
id ,
[2018-01-12],
[2018-02-12],
[2018-03-12],
[2018-04-12],
[2018-05-12],
[2018-06-12],
[2018-07-12],
[2018-08-12],
[2018-09-12],
[2018-10-12],
[2018-11-12],
[2018-12-12],
(
isnull([2018-01-12],0)+
isnull([2018-02-12],0)+
isnull([2018-03-12],0)+
isnull([2018-04-12],0)+
isnull([2018-05-12],0)+
isnull([2018-06-12],0)+
isnull([2018-07-12],0)+
isnull([2018-08-12],0)+
isnull([2018-09-12],0)+
isnull([2018-10-12],0)+
isnull([2018-11-12],0)+
isnull([2018-12-12],0)
) As Total
From
(
Select * From Vehicle v Left Join
Odometer o On v.Id = o.Vehicle_Id
) p
Pivot
(
count(Odometer)
For StartDT In ([2018-01-12],[2018-02-12],[2018-03-12],[2018-04-12],[2018-05-12],[2018-06-12],[2018-07-12],[2018-08-12],[2018-09-12],[2018-10-12],[2018-11-12],[2018-12-12])
) pv
order by pv.id
Celui-ci nécessiterait DYNAMIC SQL.
Les colonnes sont MONTHS, mais c'est une petite question de passer aux jours.
Exemple
Declare @D1 date,@D2 date
Select @D1=Min(convert(Date,TripStart)),@D2=Max(convert(Date,TripStart)) from EMS_trip_Data2
Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1)
Into #Dates
From master..spt_values n1
-- Generate Columns
Declare @Cols varchar(max) = stuff( (Select ','+QuoteName(D) From #Dates Order by 1 For XML Path('')),1,1,'')
-- Generate Dynamic SQL
Declare @SQL varchar(max) = '
Select *
From (
Select Src1.*,VS.[Group]
From (
Select Vehicle,TripStart=convert(Date,TripStart),StartOdometer=sign(StartOdometer) From EMS_trip_Data2
Union
Select A.Vehicle
,B.D
,0
From VehicleSummary2 A
Cross Join #Dates B
) src1
Join VehicleSummary2 VS on src1.Vehicle = VS.Vehicle
) Src
Pivot (sum([StartOdometer]) For [TripStart] in (' + @Cols + ') ) p
Cross Apply ( Select Total = '+replace(@Cols,',','+')+' ) T
'
--Print @SQL
Exec(@SQL)
Retours
MODIFIER - MISE À JOUR demandée
Declare @D1 date,@D2 date
Select @D1=Min(convert(Date,TripStart)),@D2=Max(convert(Date,TripStart)) from EMS_trip_Data2
Select Top (DateDiff(DAY,@D1,@D2)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@D1)
Into #Dates
From master..spt_values n1
-- Generate Columns
Declare @Cols varchar(max) = stuff( (Select ','+QuoteName(D) From #Dates Order by 1 For XML Path('')),1,1,'')
-- Generate Dynamic SQL
Declare @SQL varchar(max) = '
Select *
From (
Select Vehicle,TripStart=convert(Date,TripStart),StartOdometer=sign(StartOdometer) From EMS_trip_Data2
Union
Select A.Vehicle
,B.D
,0
From VehicleSummary2 A
Cross Join #Dates B
) Src
Pivot (sum([StartOdometer]) For [TripStart] in (' + @Cols + ') ) p
Cross Apply ( Select Total = '+replace(@Cols,',','+')+' ) T
'
--Print @SQL
Exec(@SQL)
EDIT-2 avec une nouvelle colonne
-- Generate Date Full Date Range
Declare @D1 date,@D2 date
Select @D1=Min(StartDate),@D2=Max(StartDate) from TBLTrips
Select Top (DateDiff(Month,@D1,@D2)+1) D=DateAdd(Month,-1+Row_Number() Over (Order By (Select Null)),@D1)
Into #Dates
From master..spt_values n1
-- Generate Columns
Declare @Cols varchar(max) = stuff( (Select ','+QuoteName(D) From #Dates Order by 1 For XML Path('')),1,1,'')
-- Generate Dynamic SQL
Declare @SQL varchar(max) = '
Select *
From (
Select Vehicle,StartDate,Odometer=sign(Odometer) From TBLTrips
Union
Select A.Vehicle
,B.D
,0
From TBLVehicles A
Cross Join #Dates B
) Src
Pivot (sum([Odometer]) For [StartDate] in (' + @Cols + ') ) p
Cross Apply ( Select Total = '+replace(@Cols,',','+')+' ) T
'
--Print @SQL
Exec(@SQL)
Merci @John, ce type de sortie est exactement ce que je recherche, pour une raison quelconque, mes tables sortent toutes avec des 0. Im essayant de faire l'exemple avec ma base de données dans SQLfiddle mais cela ne me permettra pas de construire mon schéma. Cela pourrait-il être dû au fait que mes colonnes Vehicle sont varchar?
@ pato.llaguno Cela ne devrait pas avoir d'importance. Les colonnes sont-elles des mois ou des jours? Si Mois, est-ce que tous les odomètres se lisent le 12?
j'ai essayé les deux façons, pastebin.com/ZZAAkzU2 qui est une petite partie de la base de données, les dates sont de du 1er décembre 2018 au 14 janvier 2019
@ pato.llaguno Donnez-moi un moment
@ pato.llaguno OK, je vois le problème. Vous avez datetime pas date. Encore une fois, voulez-vous les colonnes par JOUR ou MOIS?
@ pato.llaguno Voir EDIT - Mise à jour demandée
@ pato.llaguno Juste un petit mot. le véhicule 10000 n'est PAS répertorié dans VehicleSummary2, c'est pourquoi vous verrez des NULL sur la première ligne
Merci beaucoup, il a fait ce que je demandais, je vais essayer d'apprendre à utiliser le pivot de cet exemple.
@ pato.llaguno Prenez un peu de temps pour disséquer la requête. Si vous êtes coincé, je peux ajouter un petit récit.
ce serait génial!
il semble que je n'ai pas pu comprendre par moi-même comment cette requête fonctionne complètement car j'essaie d'agréger une autre colonne avant toutes les dates et après le numéro de véhicule, mais j'obtiens une erreur. la colonne est à l'intérieur du tableau de récapitulatif des véhicules et j'essaye quelque chose comme ça. Sélectionnez A.Vehicle, A.Group, B.D mais j'obtiens une erreur, pourriez-vous m'aider avec le récit pour essayer de comprendre cela? Merci!
@ pato.llaguno Avoir un petit problème avec le visuel. Vous pouvez peut-être modifier votre question et ajouter les résultats souhaités modifiés.
@ pato.llaguno Voir EDIT-2 Nous venons d'ajouter un JOIN au résumé du véhicule pour attacher le groupe.
@ pato.llaguno Vous pouvez souhaiter le changement Join VehicleSummary2 en GAUCHE Join VehicleSummary2 s'il vous manque un véhicule dans le tableau récapitulatif
Salut, je pense que vous devez utiliser le pivot avec une requête dynamique.
Sont les colonnes JOURS ou Mois. Si jours nous parlons de 152 colonnes.