sql - 使列超出日期范围

Question

我有 2 张桌子。

第一个表是我的车辆目录，第二个表是我有该车辆的日期和里程表读数的表（每辆车每天可能有超过 1 次行程）。我想有一个输出，我可以对车辆是否有里程表读数进行一些分析。其中一些可能被报告为 0，这是我希望能够看到的，甚至可能根本没有数据。

TBLV车辆：

Vehicle       Group
-----------------
100             A
101             A
102             B
103             B
104             C
105             C

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

我想得到一个像下面这样的输出，我可以看到哪辆车在指定的日期范围内每天都有有效的里程表。在最后有一个总列来查看哪些在该日期范围内绝对没有里程表甚至会很好。

输出

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

标签： sqlsql-serversql-server-2012

Answer 1

这需要DYNAMIC SQL。

列是MONTHS，但切换到天是小事。

例子

-- 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)

退货

编辑 - 请求更新

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

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)

退货