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

Question

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.

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)

Your results seem to depend on an ordering of the rows, but there is no column that specifies the ordering. Also, if `tid` is the primary key, why are values repeated? — Gordon Linoff, Sep 21 '20 at 20:48
@GordonLinoff, I am sorry, the table is actually very big in size. I copy pasted in Excel to explain the context. Sorry.. I overlloked while pasting... apologies. I have edited now. — Shaaaan, Sep 21 '20 at 20:52

score 0 · Answer 1 · answered Sep 21 '20 at 20:52

0

I am going to guess that tid really is a primary key and represents the ordering you care about.

You can assign the value you want by looking at rows where some columns are the same -- I am guess those are drivername, cardgdate, dircid, load, and unitcount. Then, you can treat this as a gaps and islands problem. Check to see when these values change and do a cumulative sum:

select t.*,
       sum(case when prev_tid_grp = prev_tid then 0  -- no change in the key columns
                else 1                               -- count the change!
           end) over (order by tid) as workserial
from (select t.*,
             lag(tid) over (order by tid) as prev_tid,
             lag(tid) over (partition by rivername, cardgdate, dircid, load, unitcount order by tid) as prev_tid_grp
      from t
     ) t;

answered Sep 21 '20 at 20:52

Gordon Linoff

1,242,037
58
646
786

Thank you so much for the advise. Indeed i was working with your code... and then i got the result.. I appreciate your instant support .... Thank You so much – Shaaaan Sep 21 '20 at 21:09
@Shaaaan . . . I'm surprised you didn't accept this answer then. – Gordon Linoff Sep 21 '20 at 21:17
Well, its been years i queried in stackoverflow.... I was over the clouds when i got the solution instantly and was busy continuing my work.... :) – Shaaaan Sep 21 '20 at 21:35

score 0 · Answer 2 · edited Sep 22 '20 at 06:49

0

As I understand your question, you want an index count that increments every time the unit count changes.

If so, you can use lag(), then a window count(). It is unclear which column can be used to order the rows, so I assumed id.

select t.*,
    sum(case when unitcount = lag_unitcount then 0 else 1 end)
        over(partition by drivename order by id) worserial      
from (
    select t.*, 
        lag(unitcount) over(partition by drivename order by id) lag_unitcount
    from mytable t
) t

edited Sep 22 '20 at 06:49

Dale K

25,246
15
42
71

answered Sep 21 '20 at 20:54

GMB

216,147
25
84
135

Thank you so much.. Wow... I didn't know that stackoverflow can do magic... Man.... exactly that was what i needed. I owe you a lotzzzzz. – Shaaaan Sep 21 '20 at 21:06

score 0 · Accepted Answer · edited Sep 22 '20 at 06:49

0

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;

edited Sep 22 '20 at 06:49

Dale K

25,246
15
42
71

answered Sep 21 '20 at 21:13

Python noob

46
6

well i am working with MSSQL2012. I appreciate your suggestion. I tried your code; and got the 4 rows with value of Cumaltive_trips as 1,2,3 and 4... Actually my requirement was to merge '2 Units' values ' rows to be considered as 1. My query table has 6 rows. The query must reduce that to 4 rows (considering 2-units' trips as 1 trip). Though I seriaously appreciate your effort. and Thank you so much – Shaaaan Sep 21 '20 at 22:02
@Shaaaan I know you already got your answer, if you just changed sum to unit_flag- you would get your 4 trips. To remove 1units trips - you can add where statement `where units<>'1units'` otherwise you can keep everything - it is not gonna change cumulative trip counts. `(select b.*, sum (unit_flg) over (partition by DriverName order by id rows unbounded preceding ) as cumulative_trips from(select id,TripCount,DriverName,unit_flg from (select *,case when units='2units' then 1 else 0 end as unit_flg from test_table ) a group by 1,2,3,4) b group by 1,2,3,4;` – Python noob Sep 24 '20 at 08:50

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

3 Answers3

Linked