June 14, 2016 at 11:48 am
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](12) NULL,
[Emailid] [varchar](100) NULL,
[customername] [varchar](50) NULL,
[vidsalesrep] [varchar](50) NULL,
[vidsatco] [char](20) NULL,
[numeric](1, 0) NULL,
[viddatesold] [datetime] NOT NULL,
[vidamtsold] [numeric](10, 2) NULL,
[vidcallorigination] [varchar](30) NULL,
[intsalesrep] [varchar](50) NULL,
[intsatco] [char](20) NULL,
[internet] [numeric](1, 0) NULL,
[intdatesold] [datetime] NOT NULL,
[intamtsold] [numeric](10, 2) NULL,
[intcallorigination] [varchar](30) NULL,
[basalesrep] [varchar](50) NULL,
[basatco] [char](20) NULL,
[ba] [numeric](1, 0) NULL,
[badatesold] [datetime] NOT NULL,
[baamtsold] [numeric](10, 2) NULL,
[bacallorigination] [varchar](30) NULL,
[ithgsalesrep] [varchar](50) NULL,
[ithgsatco] [char](20) NULL,
[ithg] [numeric](1, 0) NULL,
[ithgdatesold] [datetime] NOT NULL,
[ithgamtsold] [numeric](10, 2) NULL,
[ithgcallorigination] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[testsold] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], , [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], , [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], , [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], , [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], , [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], , [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 single line as shown in attachment
June 14, 2016 at 12:00 pm
Unfortunately, your picture cuts off much of the data. It is hard to tell how the data should be combined from multiple rows to a single row. Please provide the expected results as a separate table and data.
June 14, 2016 at 12:29 pm
based on email id and phone the customer is unique. If the customer changes the order it should only update the product columns which are modified for example internet,video ithg,ba are the services. Same customer can order all or only few. based on that the destination table should update the values from the source. PLease let me know if it is not clear. Attached is the expected result.this destination table updates daily once.
June 14, 2016 at 1:24 pm
Savya (6/14/2016)
based on email id and phone the customer is unique. If the customer changes the order it should only update the product columns which are modified for example internet,video ithg,ba are the services. Same customer can order all or only few. based on that the destination table should update the values from the source. PLease let me know if it is not clear. Attached is the expected result.this destination table updates daily once.
Reposting the picture doesn't help, it still cuts off some of the columns. Post the expected results to a separate table just like you posted the sample data and table.
How hard can that be?
June 14, 2016 at 1:47 pm
Sorry I taught the next image was visible.
please see the data below. consider this as destination. Thanks..
CREATE TABLE [dbo].[testsolddest](
[Weeknumber] [int] NULL,
[customerphone] [char](12) NULL,
[Emailid] [varchar](100) NULL,
[customername] [varchar](50) NULL,
[vidsalesrep] [varchar](50) NULL,
[vidsatco] [char](20) NULL,
[numeric](1, 0) NULL,
[viddatesold] [datetime] NOT NULL,
[vidamtsold] [numeric](10, 2) NULL,
[vidcallorigination] [varchar](30) NULL,
[intsalesrep] [varchar](50) NULL,
[intsatco] [char](20) NULL,
[internet] [numeric](1, 0) NULL,
[intdatesold] [datetime] NOT NULL,
[intamtsold] [numeric](10, 2) NULL,
[intcallorigination] [varchar](30) NULL,
[basalesrep] [varchar](50) NULL,
[basatco] [char](20) NULL,
[ba] [numeric](1, 0) NULL,
[badatesold] [datetime] NOT NULL,
[baamtsold] [numeric](10, 2) NULL,
[bacallorigination] [varchar](30) NULL,
[ithgsalesrep] [varchar](50) NULL,
[ithgsatco] [char](20) NULL,
[ithg] [numeric](1, 0) NULL,
[ithgdatesold] [datetime] NOT NULL,
[ithgamtsold] [numeric](10, 2) NULL,
[ithgcallorigination] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[testsolddest] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], , [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'TRB', N'BA', CAST(1 AS Numeric(1, 0)), CAST(0x0000A5FC00D753D4 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'6866', 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].[testsolddest] ([Weeknumber], [customerphone], [Emailid], [customername], [vidsalesrep], [vidsatco], , [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(25.00 AS Numeric(10, 2)), N' 999-312-4609', 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(1 AS Numeric(1, 0)), CAST(0x0000000000000000 AS DateTime), CAST(0.00 AS Numeric(10, 2)), N'')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply