June 27, 2016 at 10:18 pm
access to SQL Fiddle is blocked but I'm including table, seed data, output expected with query I have been trying. Need some advise or point me to correct direction.
I need to write this using joins so that I can use it EF 6. I was able to get the expected results using temp table but currently that is not an option.
Can anyone please help?
Table:-
CREATE TABLE [Test].[ServiceCallData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TestCaseNumber] [int] NULL,
[UniqueKey] [nvarchar](max) NULL,
[AccountNumber] [nvarchar](15) NULL,
[ServiceName] [nvarchar](max) NULL,
[ServiceCallOrderId] [int] NULL,
[SourceSystem] [int] NULL,
CONSTRAINT [PK_ServiceCallParsedData1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Seed data:-
SET IDENTITY_INSERT [TestPortal].[ServiceCallData] ON
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (1, 1, N'1', N'10101010101', N'ACB', 1, 3)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (2, 1, N'2', N'10101010101', N'NAB', 2, 3)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (3, 1, N'3', N'10101010101', N'UTY', 3, 3)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (4, 1, N'4', N'10101010101', N'OOP', 4, 3)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (5, 1, N'5', N'10101010101', N'ZMAN', 5, 3)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (11, 1, N'1', N'10101010101', N'ACB', 1, 1)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (12, 1, N'2', N'10101010101', N'NAB', 2, 1)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (13, 1, N'3', N'10101010101', N'UTY', 3, 1)
GO
INSERT [TestPortal].[ServiceCallData] ([Id], [TestCaseNumber], [UniqueKey], [AccountNumber], [ServiceName], [ServiceCallOrderId], [SourceSystem]) VALUES (15, 1, N'5', N'10101010101', N'ZMAN', 5, 1)
GO
SET IDENTITY_INSERT [TestPortal].[ServiceCallData] OFF
GO
OutPut:-
ServiceCallOrderIdServiceNameSourceSystem ServiceCallOrderIdServiceNameSourceSystem
1 ACB 3 1 ACB 1
2 NAB 3 2 NAB 1
3 UTY 3 3 UTY 1
4 OOP 3 null null 1
5 ZMAN 3 5 ZMAN 1
Query with the where clause:-
select [scd].[ServiceCallOrderId], [scd].[ServiceName], scd.SourceSystem
from [TestPortal].[ServiceCallParsedData1] scd
where scd.TestCaseNumber= 1 and scd.AccountNumber = '10101010101' and scd.SourceSystem = 3
order by scd.[ServiceCallOrderId]
June 28, 2016 at 8:03 am
You're going to have to do a left join from the table to itself. The LHS resultset will have 'SourceSystem = 3' and it will join to records with 'SourceSystem = 1' (as well as other join fields)
June 28, 2016 at 9:58 am
This query will work for your test data set. If your real data is different than your sample, then the left join and where clause will have to be tweaked. Also, your expected output for UniqueKey 4 is not possible with the sample data.
select s1.UniqueKey, s1.ServiceName, s1.SourceSystem, s2.ServiceCallOrderId, s2.SourceSystem
from ServiceCallData s1
left join ServiceCallData s2
on s2.UniqueKey = s1.UniqueKey
and s2.SourceSystem <> s1.SourceSystem
where s1.SourceSystem > 1
order by s1.UniqueKey
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 28, 2016 at 1:57 pm
Thanks. This is what I needed.
June 29, 2016 at 11:20 am
Glad it worked for you.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply