September 26, 2016 at 4:38 am
Hi all,
Please i want to make some decisions inside my sql statement using the case statement, but its not giving me my desired result.
My desired result is that during the case search if part of question3 is found in question4 then it should match and if there is no match but there is a question3 = 'Others specify...' then any other question4 should be selected.
Currently 'Others specify' is also selecting everything that the match selected which should not be so, it should only select whatever is left of the initial match between question3 and question4.
Thanks for your help
I attach my code and sample data
My Code:
with cte as (
select Count(*) as Base, Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector
from
(
select Period, data, Identifier
from TestTable1
) z
pivot
(
max(data)
for Identifier in (Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector)
) piv
group by Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender, BusinessCategory, Location, Sector
)
Select cte.Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector, b.Data as Question1
, c.data as Question2, d.data as Question3
, e.data as Question4
from cte inner join TestTable1 b on b.Period = cte.Period and b.Identifier = 'QuestionNo1'
inner join TestTable1 c on c.Period = cte.Period and c.Identifier = 'QuestionNo2'
inner join TestTable1 d on d.Period = cte.Period and d.Identifier = 'QuestionNo3'
left join TestTable1 e on e.Period = cte.Period and e.Identifier = 'QuestionNo4' and e.data = case when d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) then e.Data
when not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and d.Data like 'Others Spec%' then
(select distinct top(1) data from TestTable1 where not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and e.Period = cte.Period and e.Identifier = 'QuestionNo4') end
Sample Data:
CREATE TABLE [dbo].[TestTable1](
[PKID] [bigint] IDENTITY(1,1) NOT NULL,
[Identifier] [nvarchar](550) NULL,
[Data] [nvarchar](max) NULL,
[DateUpdate] [datetime] NULL,
[Period] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TestTable1] ON
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6516, N'Name', N'Eric Okhiria', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6517, N'CompanyName', N'Diamond Bank Plc', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6518, N'CompanyAddress', N'NBA House, Adeola Hopewell Street', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6519, N'Phone', N'07057024142', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6520, N'Email', N'eokhiria@diamondbank.com', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6521, N'Gender', N'Male', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6522, N'BusinessCategory', N'Corporate', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6523, N'Location', N'Lagos', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6524, N'Sector', N'Banking', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6525, N'QuestionNo1', N'4:Network and Telecoms Engineer', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6526, N'QuestionNo2', N'2', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6530, N'QuestionNo3', N'Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6531, N'QuestionNo3', N'Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6532, N'QuestionNo3', N'Others Specify...', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6533, N'QuestionNo4', N'eStream Networks:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6534, N'QuestionNo4', N'IPNX:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6535, N'QuestionNo4', N'Others specify:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6539, N'QuestionNo5', N'Metro Wireless:eStream:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6541, N'QuestionNo5', N'Metro Wireless:Others:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6542, N'QuestionNo6', N'Metro Wireless:eStream:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6543, N'QuestionNo6', N'Metro Wireless:IPNX:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6544, N'QuestionNo6', N'Metro Wireless:Others:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6545, N'QuestionNo4', N'Airtel [Enterprise]:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6546, N'QuestionNo4', N'Glo [Enterprise]:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6547, N'QuestionNo4', N'ICSL:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6548, N'QuestionNo4', N'IPNX:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6549, N'QuestionNo4', N'MainOne:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6550, N'QuestionNo4', N'MTN [Enterprise]:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6551, N'QuestionNo4', N'Estisalat:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6552, N'QuestionNo4', N'Swift Networks:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6554, N'QuestionNo4', N'21st Century:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6555, N'QuestionNo4', N'Internet Solutions:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6558, N'QuestionNo5', N'Metro Fibre:ICSL:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6560, N'QuestionNo5', N'Metro Fibre:MainOne:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6562, N'QuestionNo5', N'Metro Fibre:Estisalat:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6563, N'QuestionNo5', N'Metro Fibre:Swift:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6565, N'QuestionNo5', N'Metro Fibre:21st:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6566, N'QuestionNo5', N'Metro Fibre:Internet:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6571, N'QuestionNo4', N'Glo [Enterprise]:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6574, N'QuestionNo4', N'MainOne:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6576, N'QuestionNo4', N'Estisalat:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6577, N'QuestionNo4', N'Swift Networks:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6578, N'QuestionNo4', N'Vodacom:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6579, N'QuestionNo4', N'21st Century:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6580, N'QuestionNo4', N'Internet Solutions:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6586, N'QuestionNo4', N'Airtel [Enterprise]:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6588, N'QuestionNo4', N'ICSL:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6591, N'QuestionNo4', N'MTN [Enterprise]:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6597, N'QuestionNo5', N'Metro Wireless:Airtel:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6598, N'QuestionNo5', N'Metro Wireless:Glo:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6599, N'QuestionNo5', N'Metro Wireless:ICSL:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6600, N'QuestionNo5', N'Metro Wireless:IPNX:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6601, N'QuestionNo5', N'Metro Wireless:MainOne:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6602, N'QuestionNo5', N'Metro Wireless:MTN:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6603, N'QuestionNo5', N'Metro Wireless:Estisalat:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6604, N'QuestionNo5', N'Metro Wireless:Swift:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6605, N'QuestionNo5', N'Metro Wireless:Vodacom:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6606, N'QuestionNo5', N'Metro Wireless:21st:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6607, N'QuestionNo5', N'Metro Wireless:Internet:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6608, N'QuestionNo6', N'Metro Wireless:Airtel:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6609, N'QuestionNo6', N'Metro Wireless:Glo:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6610, N'QuestionNo6', N'Metro Wireless:ICSL:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6611, N'QuestionNo6', N'Metro Wireless:IPNX:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6612, N'QuestionNo6', N'Metro Wireless:MainOne:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6613, N'QuestionNo6', N'Metro Wireless:MTN:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6614, N'QuestionNo6', N'Metro Wireless:Estisalat:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6615, N'QuestionNo6', N'Metro Wireless:Swift:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6616, N'QuestionNo6', N'Metro Wireless:Vodacom:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6617, N'QuestionNo6', N'Metro Wireless:21st:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6618, N'QuestionNo6', N'Metro Wireless:Internet:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6626, N'QuestionNo4', N'VDT:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6627, N'QuestionNo4', N'Vodacom:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6630, N'QuestionNo5', N'Metro Fibre:Airtel:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6631, N'QuestionNo5', N'Metro Fibre:Glo:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6632, N'QuestionNo5', N'Metro Fibre:IPNX:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6634, N'QuestionNo5', N'Metro Fibre:MTN:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6637, N'QuestionNo5', N'Metro Fibre:VDT:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6638, N'QuestionNo5', N'Metro Fibre:Vodacom:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6641, N'QuestionNo6', N'Metro Fibre:Airtel:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6642, N'QuestionNo6', N'Metro Fibre:Glo:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6643, N'QuestionNo6', N'Metro Fibre:IPNX:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6644, N'QuestionNo6', N'Metro Fibre:MainOne:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6645, N'QuestionNo6', N'Metro Fibre:MTN:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6646, N'QuestionNo6', N'Metro Fibre:Estisalat:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6647, N'QuestionNo6', N'Metro Fibre:Swift:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6648, N'QuestionNo6', N'Metro Fibre:VDT:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6649, N'QuestionNo6', N'Metro Fibre:Vodacom:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6650, N'QuestionNo6', N'Metro Fibre:21st:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6651, N'QuestionNo6', N'Metro Fibre:Internet:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6652, N'QuestionNo4', N'eStream Networks:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6653, N'QuestionNo4', N'Priority Communication:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6654, N'QuestionNo4', N'VDT:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6655, N'QuestionNo4', N'Others specify:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6656, N'QuestionNo5', N'Point to Point radio:eStream:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6657, N'QuestionNo5', N'Point to Point radio:Priority:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6658, N'QuestionNo5', N'Point to Point radio:VDT:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6659, N'QuestionNo5', N'Point to Point radio:Others:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6660, N'QuestionNo6', N'Point to Point radio:eStream:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6661, N'QuestionNo6', N'Point to Point radio:Priority:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6662, N'QuestionNo6', N'Point to Point radio:VDT:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6663, N'QuestionNo6', N'Point to Point radio:Others:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6664, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6665, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6666, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6667, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6668, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6669, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6670, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6671, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6672, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6673, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6674, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6675, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6676, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6677, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6678, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6679, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6680, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6681, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6682, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6683, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6684, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6685, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6686, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6687, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6688, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6689, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6690, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6691, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6692, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6693, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6694, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6695, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6696, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6697, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6698, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6699, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6700, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6701, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6702, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6703, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6704, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6705, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6706, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6707, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6708, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6709, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6710, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6711, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6712, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6713, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6714, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6715, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6716, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6717, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6718, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6719, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6720, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6721, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6722, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6723, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6724, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6725, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6726, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6727, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6728, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6729, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6730, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6731, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6732, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6733, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6734, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6735, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6736, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6737, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6738, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6739, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6740, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6741, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6742, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6743, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6744, N'QuestionNo9', N'2', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6745, N'QuestionNo10', N'3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6746, N'QuestionNo11', N'None', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6747, N'QuestionNo12', N'Proactive monitoring of performance and not just up time as well as prompt and lasting resolution of issues.', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
SET IDENTITY_INSERT [dbo].[TestTable1] OFF
GO
September 26, 2016 at 6:36 am
Is this what you want?
with cte as (
select Count(*) as Base, Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector
from
(
select Period, data, Identifier
from TestTable1
) z
pivot
(
max(data)
for Identifier in (Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector)
) piv
group by Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender, BusinessCategory, Location, Sector
)
Select cte.Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector
, b.Data as Question1
, c.data as Question2
, d.data as Question3
, e.data as Question4
from cte inner join TestTable1 b on b.Period = cte.Period and b.Identifier = 'QuestionNo1'
inner join TestTable1 c on c.Period = cte.Period and c.Identifier = 'QuestionNo2'
inner join TestTable1 d on d.Period = cte.Period and d.Identifier = 'QuestionNo3'
left join TestTable1 e on e.Period = cte.Period and e.Identifier = 'QuestionNo4' and e.data = case when d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) then e.Data
when not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and d.Data like 'Others Spec%' then
(select distinct top(1) data from TestTable1 where not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and e.Period = cte.Period and Identifier = 'QuestionNo4') end
I have changed "e.Identifier" in the last line to "Identifier" - I think you want "QuestionNo4" in TestTable1 to get the desired result?
This gives a Question4 of "21st Century:Metro Fibre"
September 26, 2016 at 7:09 am
Hi laurie-789651,
Thanks for your reply. Actually what i want is the rows that does not have 'Metro Fibre' or 'Metro Wireless'.
What i actually want are the rows that do not contain these two but this other rows:
'Others specify:Point to Point radio'
'Priority Communication:Point to Point radio'
'eStream Networks:Point to Point radio'
If you look at the concept i used, the values in question3 are the right(values of question4)
that is what i use to match each row, but the 3 items i listed above do not belong to either 'Metro Fibre' or 'Metro Wireless', i want to assign them to others specify.
So they will form 3 separate rows with the other fields
I hope you get what i mean
Thanks
September 26, 2016 at 7:48 am
Hi laurie-789651,
Based on your suggestion, it got me thinking, eventually, i did some hard-coding to resolve it, i don't know if your answer can be more automatic, but this code gives me exactly what i want.
Thanks
Code Below:
with cte as (
select Count(*) as Base, Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector
from
(
select Period, data, Identifier
from TestTable1
) z
pivot
(
max(data)
for Identifier in (Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector)
) piv
group by Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender, BusinessCategory, Location, Sector
)
Select cte.Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector
, b.Data as Question1
, c.data as Question2
, d.data as Question3
, e.data as Question4
from cte inner join TestTable1 b on b.Period = cte.Period and b.Identifier = 'QuestionNo1'
inner join TestTable1 c on c.Period = cte.Period and c.Identifier = 'QuestionNo2'
inner join TestTable1 d on d.Period = cte.Period and d.Identifier = 'QuestionNo3'
left join TestTable1 e on e.Period = cte.Period and e.Identifier = 'QuestionNo4' and e.data = case when d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) then e.Data
when not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and d.Data like 'Others Spec%' AND (NOT (RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) IN ('Metro Wireless', 'Metro Fibre', 'Internet', 'Longhaul [DLC]'))) then e.data
--(select distinct top(1) data from TestTable1 where not(d.data = RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1)) and e.Period = cte.Period and Identifier = 'QuestionNo4' AND (NOT (RIGHT(e.Data, CHARINDEX(':', REVERSE(e.Data)) - 1) IN ('Metro Wireless', 'Metro Fibre', 'Internet', 'Longhaul [DLC]'))))
end
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply