Unique row based on week

Transact-SQL
1

I have a scenario where we get transactional data. there will be orders coming from customers .for different services for different providers. In the example below
for the same customer if the order changes it has to update with new order but the other orders for that customer should not change. ie it has to update the columns in way if something is modified there should be in single row for each week.

customer is identified uniquely based on mail id and phone number.
CREATE TABLE [dbo].[testsold](
[Weeknumber] [int] NULL,
[customerphone] char NULL,
[Emailid] varchar NULL,
[customername] varchar NULL,
[vidsalesrep] varchar NULL,
[vidsatco] char NULL,
[video] [numeric](1, 0) NULL,
[viddatesold] [datetime] NOT NULL,
[vidamtsold] [numeric](10, 2) NULL,
[vidcallorigination] varchar NULL,
[intsalesrep] varchar NULL,
[intsatco] char NULL,
[internet] [numeric](1, 0) NULL,
[intdatesold] [datetime] NOT NULL,
[intamtsold] [numeric](10, 2) NULL,
[intcallorigination] varchar NULL,
[basalesrep] varchar NULL,
[basatco] char NULL,
[ba] [numeric](1, 0) NULL,
[badatesold] [datetime] NOT NULL,
[baamtsold] [numeric](10, 2) NULL,
[bacallorigination] varchar NULL,
[ithgsalesrep] varchar NULL,
[ithgsatco] char NULL,
[ithg] [numeric](1, 0) NULL,
[ithgdatesold] [datetime] NOT NULL,
[ithgamtsold] [numeric](10, 2) NULL,
[ithgcallorigination] varchar NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[testsold] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], [video], [viddatesold], [vidamtsold], [vidcallorigination], [intsalesrep], [intsatco], [internet], [intdatesold], [intamtsold], [intcallorigination], [basalesrep], [basatco], [ba], [badatesold], [baamtsold], [bacallorigination], [ithgsalesrep], [ithgsatco], [ithg], [ithgdatesold], [ithgamtsold], [ithgcallorigination]) VALUES (19, N'988 334-9999', N'MMM@gmail.com', N'Mon Joe', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N'TRB ', N'IT GROUP ', CAST(1 AS Numeric(1, 0)), CAST(0x0000A5FC00D7229C AS DateTime), CAST(3.50 AS Numeric(10, 2)), N'6866 ')
INSERT [dbo].[testsold] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], [video], [viddatesold], [vidamtsold], [vidcallorigination], [intsalesrep], [intsatco], [internet], [intdatesold], [intamtsold], [intcallorigination], [basalesrep], [basatco], [ba], [badatesold], [baamtsold], [bacallorigination], [ithgsalesrep], [ithgsatco], [ithg], [ithgdatesold], [ithgamtsold], [ithgcallorigination]) VALUES (19, N'988 334-9999', N'MMM@gmail.com', N'Mon Joe', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N'TRB ', N'BA ', CAST(1 AS Numeric(1, 0)), CAST(0x0000A5FC00D753D4 AS DateTime), NULL, N'6866', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'')
INSERT [dbo].[testsold] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], [video], [viddatesold], [vidamtsold], [vidcallorigination], [intsalesrep], [intsatco], [internet], [intdatesold], [intamtsold], [intcallorigination], [basalesrep], [basatco], [ba], [badatesold], [baamtsold], [bacallorigination], [ithgsalesrep], [ithgsatco], [ithg], [ithgdatesold], [ithgamtsold], [ithgcallorigination]) VALUES (19, N'988 334-9999', N'MMM@gmail.com', N'Mon Joe', N'TRB ', N'Dishtv ', CAST(1 AS Numeric(1, 0)), CAST(0x0000A5FC00D6C98C AS DateTime), CAST(25.00 AS Numeric(10, 2)), N'6866', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'')
INSERT [dbo].[testsold] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], [video], [viddatesold], [vidamtsold], [vidcallorigination], [intsalesrep], [intsatco], [internet], [intdatesold], [intamtsold], [intcallorigination], [basalesrep], [basatco], [ba], [badatesold], [baamtsold], [bacallorigination], [ithgsalesrep], [ithgsatco], [ithg], [ithgdatesold], [ithgamtsold], [ithgcallorigination]) VALUES (19, N'888 333-3333', N'kalu@gmail.com ', N'uhn anda', N'WAZ ', N'Dishtv ', CAST(1 AS Numeric(1, 0)), CAST(0x0000A5F900AC79E8 AS DateTime), CAST(25.00 AS Numeric(10, 2)), N' 999-312-4609', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'')
INSERT [dbo].[testsold] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], [video], [viddatesold], [vidamtsold], [vidcallorigination], [intsalesrep], [intsatco], [internet], [intdatesold], [intamtsold], [intcallorigination], [basalesrep], [basatco], [ba], [badatesold], [baamtsold], [bacallorigination], [ithgsalesrep], [ithgsatco], [ithg], [ithgdatesold], [ithgamtsold], [ithgcallorigination]) VALUES (19, N'888 333-3333', N'kalu@gmail.com ', N'uhn anda', N'ROC', N'TWC ', CAST(1 AS Numeric(1, 0)), CAST(0x0000A5FA00B54640 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N'', N' ', CAST(1 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'')
INSERT [dbo].[testsold] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], [video], [viddatesold], [vidamtsold], [vidcallorigination], [intsalesrep], [intsatco], [internet], [intdatesold], [intamtsold], [intcallorigination], [basalesrep], [basatco], [ba], [badatesold], [baamtsold], [bacallorigination], [ithgsalesrep], [ithgsatco], [ithg], [ithgdatesold], [ithgamtsold], [ithgcallorigination]) VALUES (19, N'888 333-3333', N'kalu@gmail.com ', N'uhn anda', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N'WAZ ', N'CenturyLink ', CAST(1 AS Numeric(1, 0)), CAST(0x0000A5F900AE6168 AS DateTime), CAST(80.00 AS Numeric(10, 2)), N' 999-312-4609', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'', N' ', N' ', CAST(0 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'')

select * from testsold

I have data in testsold

expecting result as

Pasted image2300×73 8.8 KB

Could some one help me in solving this .Thanks in advance

2

You MAX() with GROUP BY:

SELECT Weeknumber, Emailid,
    MAX(customerphone) AS customerphone,
    MAX(customername) AS customername,
    MAX(vidsalesrep) AS vidsalesrep,
    --...
    MAX(ithgdatesold) AS ithgdatesold,
    MAX(ithgamtsold) AS ithgamtsold,
    MAX(ithgcallorigination) AS ithgcallorigination
FROM dbo.testsold
GROUP BY Weeknumber, Emailid
3

I did that way but it gives wrong result as for date we can choose max but for varchar columns it doesnot work.

4

You can use window functions. Try this

with mycte
as
(
select DENSE_RANK() over(partition by t.customername order by vidamtsold) as ct,
Max(t.vidamtsold) over(partition by t.customername order by (select null)) as maxSold,
Max(t.viddatesold) over(partition by t.customername order by (select null)) as maxDate,
 t.* from #testsold  as t
 )

 select 
 c.weeknumber,c.customerPhone,c.emailid,
 c.customername, c.vidsalesrep, c.vidsatco,
 c.video, 'viddatesold'=(case when c.viddatesold < c.maxDate then c.maxDate else c.viddatesold end) ,
 'vidamtsold'= (case when c.vidamtsold=0 then c.maxSold else c.vidamtsold end)
   from mycte as c
 where c.ct=2

sqlR.png927×156 6.01 KB