Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pstrobis
Starting Member
3 Posts |
Posted - 2013-10-10 : 12:17:04
|
Hello, I build this query using a Union clause, it gets me the data I need, but not in a result format I would like. can it be modified to result like thisDistinct Date, Total Trips, late tripsCompared toTotal_TripCount Tripdate190 2013-06-18 00:00:00.0002264 2013-06-18 00:00:00.000Here is the queryDeclare @BeginDate DATETIME, @EndDate DATETIME, @RouteStart Int, @RouteStop Int Set @BeginDate = '06/18/13'Set @EndDate = '06/18/13'Set @RouteStart = '0000'Set @RouteStop = '99999'SELECT COUNT(id) AS Total_TripCount, TripdateFROM TRIPARCHIVE WHERE Tripdate between @beginDate and @EndDate and (Status like 'S') AND Clientid <> '-9999' and RouteId between '0001' and '99999' group by tripdate UNIONSELECT COUNT(id) AS Total_LateCount, Tripdate FROM TRIPARCHIVEWHERE Tripdate between @beginDate and @EndDate and Status like 'S' AND Clientid <> '-9999' and RouteId between @RouteStart and @routestop and (CONVERT(INTEGER,((Substring(CASE Performpickup WHEN 0 THEN PickTime WHEN 1 THEN PickTime WHEN 2 THEN APtime1 WHEN 5 THEN PickTime ELSE PickTime END,1,2))*60 + substring(CASE Performpickup WHEN 0 THEN PickTime WHEN 1 THEN PickTime WHEN 2 THEN APtime1 WHEN 5 THEN PickTime ELSE PickTime END,4,2)) )- CONVERT(INTEGER,(Substring(promisetime,1,2))*60 + substring(promisetime,4,2))) > 30 group by Tripdate |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-10 : 12:31:07
|
The least intrusive is probably to make your existing query into a subquery or cte and then PIVOT like shown below. Alternatively, you can do this as one query by including all the conditions as case expression within the count.DECLARE @BeginDate DATETIME , @EndDate DATETIME , @RouteStart INT , @RouteStop INTSET @BeginDate = '06/18/13'SET @EndDate = '06/18/13'SET @RouteStart = '0000'SET @RouteStop = '99999';WITH cte AS( SELECT COUNT(id) AS Total_TripCount , Tripdate FROM TRIPARCHIVE WHERE Tripdate BETWEEN @beginDate AND @EndDate AND ( Status LIKE 'S' ) AND Clientid <> '-9999' AND RouteId BETWEEN '0001' AND '99999' GROUP BY tripdate UNION SELECT COUNT(id) AS Total_LateCount , Tripdate FROM TRIPARCHIVE WHERE Tripdate BETWEEN @beginDate AND @EndDate AND Status LIKE 'S' AND Clientid <> '-9999' AND RouteId BETWEEN @RouteStart AND @routestop AND ( CONVERT(INTEGER, ( ( SUBSTRING(CASE Performpickup WHEN 0 THEN PickTime WHEN 1 THEN PickTime WHEN 2 THEN APtime1 WHEN 5 THEN PickTime ELSE PickTime END, 1, 2) ) * 60 + SUBSTRING(CASE Performpickup WHEN 0 THEN PickTime WHEN 1 THEN PickTime WHEN 2 THEN APtime1 WHEN 5 THEN PickTime ELSE PickTime END, 4, 2) )) - CONVERT(INTEGER, ( SUBSTRING(promisetime, 1, 2) ) * 60 + SUBSTRING(promisetime, 4, 2)) ) > 30 GROUP BY Tripdate) SELECT TripDate, MAX(Total_TripCount) Total_TripCount, MAX(Total_LateCount) Total_LateCount FROM cte GROUP BY TripDate |
|
|
pstrobis
Starting Member
3 Posts |
Posted - 2013-10-10 : 14:09:52
|
That didnt work, got an invalid column name error for Total_TripCount. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-10 : 14:29:29
|
quote: Originally posted by pstrobis That didnt work, got an invalid column name error for Total_TripCount.
Can you give this a try?DECLARE @BeginDate DATETIME , @EndDate DATETIME , @RouteStart INT , @RouteStop INTSET @BeginDate = '06/18/13'SET @EndDate = '06/18/13'SET @RouteStart = '0000'SET @RouteStop = '99999';WITH cte AS( SELECT COUNT(id) AS Total_TripCount , Tripdate, 'TripCount' AS [Type] FROM TRIPARCHIVE WHERE Tripdate BETWEEN @beginDate AND @EndDate AND ( Status LIKE 'S' ) AND Clientid <> '-9999' AND RouteId BETWEEN '0001' AND '99999' GROUP BY tripdate UNION SELECT COUNT(id) AS Total_TripCount , Tripdate,'LateCount' AS [Type] FROM TRIPARCHIVE WHERE Tripdate BETWEEN @beginDate AND @EndDate AND Status LIKE 'S' AND Clientid <> '-9999' AND RouteId BETWEEN @RouteStart AND @routestop AND ( CONVERT(INTEGER, ( ( SUBSTRING(CASE Performpickup WHEN 0 THEN PickTime WHEN 1 THEN PickTime WHEN 2 THEN APtime1 WHEN 5 THEN PickTime ELSE PickTime END, 1, 2) ) * 60 + SUBSTRING(CASE Performpickup WHEN 0 THEN PickTime WHEN 1 THEN PickTime WHEN 2 THEN APtime1 WHEN 5 THEN PickTime ELSE PickTime END, 4, 2) )) - CONVERT(INTEGER, ( SUBSTRING(promisetime, 1, 2) ) * 60 + SUBSTRING(promisetime, 4, 2)) ) > 30 GROUP BY Tripdate) SELECT TripDate, MAX(CASE WHEN [Type] ='TripCount' THEN Total_TripCount END) Total_TripCount, MAX(CASE WHEN [Type] ='LateCount' THEN Total_TripCount END ) Total_LateCount FROM cte GROUP BY TripDate |
|
|
pstrobis
Starting Member
3 Posts |
Posted - 2013-10-10 : 15:56:54
|
That did it, thanks!!! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-10 : 16:32:32
|
Great! Glad to be of help. |
|
|
|
|
|
|
|