Hi
Good Day.
Below is my query for grouping the values
SELECT
** CONVERT(varchar,Duty."dat_TrafficDate",23) AS Date_Trans, Journey."str_RouteID" AS RouteNo, **
** CONVERT(int,Duty."str_DutyID") AS DutyNo, CONVERT(int,Duty."str_BusID") AS BusNo,**
** Duty."int4_OperatorID" AS DriverNo, count(Journey."int2_JourneyID") AS TripNo, **
** CONVERT(float,sum(Journey."int4_JourneyRevenue"))/1000 AS Revenue, sum(Journey."int4_JourneyTickets") AS Tickets, sum(Journey."int4_JourneyPasses") AS PassTpas**
FROM
** { oj "Merit"."dbo"."Duty" Duty INNER JOIN "Merit"."dbo"."Journey" Journey ON**
** Duty."id_Duty" = Journey."id_Duty"} **
WHERE
** Duty."int4_OperatorID" <> 12345 AND **
** CONVERT(varchar,Duty."dat_TrafficDate",23) BETWEEN '2019-07-06' AND '2019-07-06' AND **
** Duty."str_BusID" <> '000000' and Duty."int4_OperatorID"=20194**GROUP BY
** Duty."dat_TrafficDate", Journey."str_RouteID", Duty."str_DutyID", Duty."str_BusID",**
** Duty."int4_OperatorID" **
ORDER BY
** Duty."dat_TrafficDate", Journey."str_RouteID",Duty."str_DutyID"**
and results of the above query is
If u can see one driver is doing is his duty in 2 routes using one bus.
We assign a driver in the morning one bus and route, Sometimes he does one or two trips in other route in the same.
While displaying the output i want to show one row instead of two rows. Revenue and tickets all should be displayed against the route which is assigned to him before he starts his route.
In the about results, we assigned him route 66 , i want the final output as follow
Date_Trans RouteNo DutyNo BusNo DriverNo TripNo Revenue Tickets PassTpas
2019-07-06 66 63 5270 20194 9 55.75 223 81
Someone please help me out to write the query to get the above result
Thank You