Grouping Distinct Column

Transact-SQL
1

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

image

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

3

harish's query is close but the row number column guestimates what the assigned route is. Also it only works for one driver.

IF OBJECT_ID('tempdb..#data') IS NOT NULL 
    DROP TABLE #data
 go 

 IF OBJECT_ID('tempdb..#driverassignments') IS NOT NULL 
    DROP TABLE #driverassignments

create table #data 
(
date_trans  date , 
route_no    varchar(100) , 
dutyNo  int , 
busno int , 
driverno int, 
tripno int,
Revenue  decimal(10,2) , 
tickets int ,
PassTpas int 
)
go 

create table #driverassignments
(DutyNo int, 
 Date_Trans date,
 RouteAssignment varchar(100))

insert into #driverassignments values
(63, '7/6/2019', 66),
(1, '7/6/2019', 67)

insert into #data select '2019-07-06','66',63,5270,20194,8,53,212,71
insert into #data select '2019-07-06','66X',63,5270,20194,1,2.75,11,10
insert into #data select '2019-07-06','67X',1,5270,20194,8,153,412,71
insert into #data select '2019-07-06','67',1,5270,20194,1,12.75,111,10
go 

select * from #data

SELECT a.date_trans, 
       b.RouteAssignment, 
       a.dutyno, 
       a.busno, 
       a.driverno, 
       Sum(a.tripno), 
       Sum(a.revenue),
       Sum(a.tickets), 
       Sum(a.passtpas) 
 FROM  #data a 
		join #driverassignments b
			on a.dutyNo = b.DutyNo
			and a.date_trans = b.Date_Trans
GROUP  BY a.date_trans, 
          b.RouteAssignment, 
          a.dutyno, 
          a.busno, 
          a.driverno
4

Thanks Harish and Mik for your reply

I am new to the sql.
We have only read access to the tables so cant do any inserts

need a simple query based on the tripno.
If he is doing more trips then we have to consider that route as Main route and display all details against that route .

Thanks in advance

6

with cte
as (select row_number() over(order by (select null)) as rn * from
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" and b.rn=1}
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"

Is this what u said or did i made i any mistake.

8

Kernel error: [FreeTDS][MSSQL Server] , Server CGC-MERIT, Line 2
Incorrect syntax near '*'.
[FreeTDS][MSSQL Server] , Server CGC-MERIT, Line 19
Incorrect syntax near ')'.

Got above error

10

Share me your emailid i will share team viewer details