Search code examples
sqlsql-serverdatetimewindow-functionsgaps-and-islands

Query to assign serial number for rows without grouping together and without changing the order of rows

This table consists of the truck trips that carries containers to another location. Each time the truck carries a container, the records are stored. If it carries 1 unit, then I've store "1 Unit" under Unitcount Field and also assigned TripCount as 1. If the truck carries 2 units, then the Unitcount Field value is saved as "2 Units" and TripCount as 0.5

Below is the table contents - here Tid is the primary key:

I need the below output:

That is, if the driver has carried 2 units, then "Work Serial" value should store the no. of trips he worked and must keep constant value for the trips in which he has carried 2 units. The purpose of this output is that, I will use this "Work Serial" column to calculate the trip cost for his trips. Like, First trip he gets $20, Second trip $20, Third trip $25 and the for fourth trip he gets $30. My problem is that, however i tried, my query calculates as 6 trips in fact he has performed only 4 trips. Hope i was able to explain my requirement. I understand, this is very simple query; but unfortunately I am unable to solve.

Please note, the order of the rows shouldn't be changed.

Sorry for not explaining clearly. It was my mistake as I specified only one driver name and single date. In fact I am calculating the driver trip cost for the specific week and for all the drivers. Though I have copied the possible values from the table and have pasted here.

enter image description here

Use the below scripts for creating the table and inserting the data (for workaround)

CREATE TABLE [dbo].[Test_Table](
[Tid] [bigint] NOT NULL,
[DriverName] [nvarchar](50) NULL,
[Cardgdate] [date] NULL,
[Dircid] [int] NULL,
[Load] [nvarchar](50) NULL,
[UnitCount] [nchar](10) NULL,
[Result] [int] NULL,
CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED 
(
    [Tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253293, N'Naveed Khan', '20200823 00:00:00.000', 34, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253320, N'Aas Muhammad', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253358, N'Danish Imtiaz', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253407, N'Naveed Khan', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253434, N'Aas Muhammad', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253449, N'Danish Imtiaz', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253492, N'Naveed Khan', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253516, N'Danish Imtiaz', '20200824 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253520, N'Naveed Khan', '20200824 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253525, N'Aas Muhammad', '20200824 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253576, N'Danish Imtiaz', '20200824 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253592, N'Aas Muhammad', '20200824 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253599, N'Naveed Khan', '20200824 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254647, N'Danish Imtiaz', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254659, N'Naveed Khan', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254664, N'Aas Muhammad', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254711, N'Danish Imtiaz', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254743, N'Chandra Shekar', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254744, N'Aas Muhammad', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254745, N'Naveed Khan', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254783, N'Danish Imtiaz', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254785, N'Chandra Shekar', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254802, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254803, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254815, N'Chandra Shekar', '20200826 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254833, N'Aas Muhammad', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254900, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254904, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254905, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254927, N'Chandra Shekar', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254964, N'Aas Muhammad', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254986, N'Danish Imtiaz', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254987, N'Naveed Khan', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254992, N'Chandra Shekar', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
Solution

  • I am bit confused as you count trips even with 1 units, but if I understood the main objective right this should help.

    drop table if exists test_table;
create table test_table as
select 254802 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units union
select 254803 as id, 0.5 as TripCount, 'Abdul' as DriverName,'2units' as units  union
select 254987 as id, 1 as TripCount, 'Abdul' as DriverName,'1units' as units  union
select 254910 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units  union
select 254911 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units  union
select 254912 as id, 1 as TripCount,'Abdul' as DriverName,'1units' as units  ;

select id,TripCount,DriverName,unit_flg,
sum (TripCount) over (partition by DriverName,unit_flg order by  id rows unbounded preceding )   cumulative_trips
from (select *,case when units='2units' then 1 else 0 end as unit_flg
from test_table ) a where a.unit_flg>0 group by 1,2,3,4;