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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help

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 this
Distinct Date, Total Trips, late trips

Compared to
Total_TripCount Tripdate
190 2013-06-18 00:00:00.000
2264 2013-06-18 00:00:00.000

Here is the query
Declare @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, 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

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 INT

SET @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
Go to Top of Page

pstrobis
Starting Member

3 Posts

Posted - 2013-10-10 : 14:09:52
That didnt work, got an invalid column name error for Total_TripCount.

Go to Top of Page

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 INT

SET @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
Go to Top of Page

pstrobis
Starting Member

3 Posts

Posted - 2013-10-10 : 15:56:54
That did it, thanks!!!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-10 : 16:32:32
Great! Glad to be of help.
Go to Top of Page
   

- Advertisement -