OPENQUERY

Question

OPENQUERY

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

No, still not clear. You say you want to concatenate ReportingGroup, but your sample results only show one value per row in that column. And I'm afraid I can't work out what determines what goes in the Before and After columns.

John

Charmer SSChampion Points: 14903 More actions · Answer 2

Are you able to see the Before and After columns shown red in the screenshot? That is how the expected output should display.

User can add ReportingGroup to an account . In this screenshot, you can see there are 6 reporting groups have been added over a period of time. The last 2 rows were the first reporting group added to this AccountID on 16/11/2015.

When the first reporting group was added, I showed in the screen shot Before column as empty and After column as '180407 (EID)'. When they added next one on the same date, I am showing Before column as '180407 (EID)' and After column as '180407 (EID),180407-2 (EID)'.

For Example , If they added a 3rd reporting group as '185102 (EID)' on different date, so Before column will display '180407 (EID),180407-2 (EID)' and After column will become '180407 (EID),180407-2 (EID),185102 (EID)' . So I am trying to track the changes of ReportingGroup column over a period of time of an AccountID.

Am I making sense, John?

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Right, I think I understand now. Please will you post some sample data (INSERT statements again) that will yield the results you're looking for.

Thanks

John

Charmer SSChampion Points: 14903 More actions · Answer 4

Hi John, here is the sample data scripts

DECLARE @ReportingGroupChanges TABLE

(

AccountId nvarchar(50) NOT NULL

,ReportingGroupID nvarchar(50) NOT NULL

,Operation varchar(10)

,AccountNumber nvarchar(40) NOT NULL

,ReportingGroup nvarchar (205) NOT NULL

,CreatedOn datetime NOT NULL

)

INSERT INTO @ReportingGroupChanges

SELECT '0D11B029-C516-45C5-A33B-2163DC449B4B','84b54fa8-3d8d-e511-94d5-005056b10138','Update','CZ00000242','180522 (EID)','2015-11-18 15:17:40.000'

UNION

SELECT '0D11B029-C516-45C5-A33B-2163DC449B4B','2cad83b6-3d8d-e511-94d5-005056b10138','Update','CZ00000242','180533 (EID)','2015-11-17 15:17:40.000'

UNION

SELECT '0D11B029-C516-45C5-A33B-2163DC449B4B','c9dda383-981c-e411-8f78-005056b10138','Delete','CZ00000242','180998 (EID)','2015-11-17 18:17:13.000'

UNION

SELECT '0D11B029-C516-45C5-A33B-2163DC449B4B','5f5de78c-9a1c-e411-8f78-005056b10138','Delete','CZ00000242','180501 (EID)','2015-11-17 15:17:13.000'

UNION

SELECT '0D11B029-C516-45C5-A33B-2163DC449B4B','5f5gd78c-9a1c-e411-8f78-005056b10138','Update','CZ00000242','180407-2 (EID)','2015-11-16 09:16:53.000'

UNION

SELECT '0D11B029-C516-45C5-A33B-2163DC449B4B','c9978383-981c-e411-8f78-005056b10138','Update','CZ00000242','180407 (EID)','2015-11-16 08:16:53.000'

select * from @ReportingGroupChanges

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The values in the ReportingGroup column in your sample data don't match up with those in the same column in your result set. Am I missing something, or have you posted the wrong data?

John

Charmer SSChampion Points: 14903 More actions · Answer 6

Data can be anything. It can be either same or different. so I gave different data.

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

OK, if the question is how do you get the result set you posted based on the sample data you posted, then the answer is I don't know. Maybe someone smarter than me can help.

John

Charmer SSChampion Points: 14903 More actions · Answer 8

John,

I was just explaining the scenario..I don't want the exact result set posted in the screen shot. Since as you told, you understand the scenario, I just gave some sample data. but any way I am sorry if I am not made you clear.

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

If you post sample data that agrees with the result set, then you get a fully tested solution. I'm not comfortable coming up with something and then not being able to check that it's correct.

John