Please find the logic for the below table in sql 2005

Question

Please find the logic for the below table in sql 2005

vemula.narayanan

Mr or Mrs. 500

Points: 579
More actions
February 21, 2012 at 2:58 am

#253328

Hi ,
Please find the logic for the below table in sql 2005.(without using the while loops)
InputOutPut
Row Person Row Person Count
110111011
11021 1021
110311031
210121012
21022 1022
21042 1031
310121041
310331013
310531022
31033
31041
31051

Viewing 15 posts - 1 through 15 (of 16 total)

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

Divine Flame SSCoach Points: 15943 More actions · Answer 1

vemula.narayanan (2/21/2012)
Hi ,
Please find the logic for the below table in sql 2005.(without using the while loops)
InputOutPut
Row Person Row Person Count
110111011
11021 1021
110311031
210121012
21022 1022
21042 1031
310121041
310331013
310531022
31033
31041
31051

Please provide the exact problem that you are facing. Do you want to write some query which produces such results? If yes, then you should provide the table(s) structure also that is involved in this query. It will be nice if you can explain what you want from the query.

Sujeet Singh

vemula.narayanan Mr or Mrs. 500 Points: 579 More actions · Answer 2

Hi

InputOutPut

Row Person Row Person Count

110111011

11021 1021

110311031

210121012

21022 1022

21042 1031

310121041

310331013

310531022

31033

31041

31051

vemula.narayanan Mr or Mrs. 500 Points: 579 More actions · Answer 3

Hi Divine Flame,

Sorrry for Inconvenience.Please Construct the query to obtain the requiered output table without using while loops.

Input TableRequiered OutPut Table

Row Person Row Person Count

110111011

11021 1021

110311031

210121012

21022 1022

21042 1031

310121041

310331013

310531022

31033

31041

31051

Thanks & Regards,

Narayanan

Divine Flame SSCoach Points: 15943 More actions · Answer 4

Questions asked with such limited information/clarity have very less chances to be answered/answered correctly.

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please explain the logic that produces those results. Which columns are part of which table (it's not clear), why is there no 105 in the output? Why are some numbers repeated in the output and some not?

Also easily usable table definitions and sample data would be useful. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vemula.narayanan Mr or Mrs. 500 Points: 579 More actions · Answer 6

Hi,

Please Construct the query to get the Requiered output Table using the InputTable(without using the while loops)

Input Table

RowPerson

1101

1 102

1103

2101

2 102

2 104

3101

3103

3105

Requiered OutPut Table

Row Person Count

11011

1 1021

11031

21012

2 1022

2 1031

21041

31013

31022

31032

31041

31051

Thanks & Regards

Narayanan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please explain the logic that produces those results. What exactly defines what the count is? Or is it just a random number?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vemula.narayanan Mr or Mrs. 500 Points: 579 More actions · Answer 8

Hi,

Please Construct the query to get the Requiered output Table using the InputTable(without using the while loops)

Input Table

Row Person

1 101

1 102

1 103

2 101

2 102

2 104

3 101

3 103

3 105

Requiered OutPut Table

Row Person Count

1 101 1

1 102 1

1 103 1

2 101 2

2 102 2

2 103 1

2 104 1

3 101 3

3 102 2

3 103 2

3 104 1

3 105 1

Explanation:

Row is consider as WEEK

Person-101,102 etc are the Person Id's

In First Week 101,102,103 are the Person ID's ,they listen the partcular programme then their count is 1 for all peoples

In Second Week we consider both first and Second week

101-listen the channel both first week and second week then their count is 2

102,-listen the channel both first week and second week then their count is 2

104 -they Listen the Programme in Second Week Only then their count is 1

103 -they Listen the Programme in first Week Only then their count is 1

similarly for week 3 also

Thanks & Regards

Narayanan

vemula.narayanan Mr or Mrs. 500 Points: 579 More actions · Answer 9

Hi,

Please Construct the query to get the Requiered output Table using the InputTable(without using the while loops)

Input Table

Row Person

1 101

1 102

1 103

2 101

2 102

2 104

3 101

3 103

3 105

Requiered OutPut Table

Row Person Count

1 101 1

1 102 1

1 103 1

2 101 2

2 102 2

2 103 1

2 104 1

3 101 3

3 102 2

3 103 2

3 104 1

3 105 1

Explanation:

Row is consider as WEEK

Person-101,102 etc are the Person Id's

In First Week 101,102,103 are the Person ID's ,they listen the partcular programme then their count is 1 for all peoples

In Second Week we consider both first and Second week

101-listen the channel both first week and second week then their count is 2

102,-listen the channel both first week and second week then their count is 2

104 -they Listen the Programme in Second Week Only then their count is 1

103 -they Listen the Programme in first Week Only then their count is 1

similarly for week 3 also

Thanks & Regards

Narayanan

Divine Flame SSCoach Points: 15943 More actions · Answer 10

I am not sure if it is exactly the solution, but I think you need something like this:

CREATE TABLE #MyTestTable

(

Row INT,

Person INT

)

GO

INSERT INTO #MyTestTable VALUES (1, 101)

INSERT INTO #MyTestTable VALUES (1 ,102)

INSERT INTO #MyTestTable VALUES (1 ,103 )

INSERT INTO #MyTestTable VALUES (2 ,101 )

INSERT INTO #MyTestTable VALUES (2 ,102 )

INSERT INTO #MyTestTable VALUES (2 ,104 )

INSERT INTO #MyTestTable VALUES (3 ,101 )

INSERT INTO #MyTestTable VALUES (3 ,103 )

INSERT INTO #MyTestTable VALUES (3 ,105 )

INSERT INTO #MyTestTable VALUES (3 ,103 )

INSERT INTO #MyTestTable VALUES (3 ,104 )

GO

SELECT MTT1.Row,MTT2.Person,DENSE_RANK()OVER (PARTITION BY MTT2.Person ORDER BY MTT1.Row)

AS PersonCountDR

FROM #MyTestTable MTT1 OUTER APPLY #MyTestTable MTT2

WHERE MTT1.Person = MTT2.Person AND MTT1.Row = MTT2.Row

GROUP BY MTT1.Row,MTT2.Person

ORDER BY MTT1.Row,MTT2.Person

I will suggest to try this on some test database with reasonable amount of data present. Use it in production only if you are satisfied with the results.

Sujeet Singh

vemula.narayanan Mr or Mrs. 500 Points: 579 More actions · Answer 11

Hi Dwine Flame,

Its really helpfull..Thanks a lot

with Regards

Narayanan

adrian.facio SSCrazy Points: 2406 More actions · Answer 12

Hello,

I came up with these query using Divine's table ( i just took off a row 3 person 103 pair because i think they were inserted twice).

select [Rows] .Row,

Persons.Person,

RunningCount = COUNT(*)

FROM (SELECT DISTINCT Row FROM #MyTestTable) Rows

CROSS JOIN (SELECT DISTINCT Person FROM #MyTestTable) Persons

INNER JOIN #MyTestTable t ON t.Row <= [Rows].Row AND t.Person = Persons.Person

GROUP BY Persons.Person,Rows.Row

I ran these with the input you provide and it generates the output you expect.

vemula.narayanan Mr or Mrs. 500 Points: 579 More actions · Answer 13

hi adrian.facio,

This Query shows the good performance and got the exact output..

Thanks!!!

Divine Flame SSCoach Points: 15943 More actions · Answer 14

adrian.facio (2/23/2012)
Hello,
I came up with these query using Divine's table ( i just took off a row 3 person 103 pair because i think they were inserted twice).
select [Rows] .Row,
Persons.Person,
RunningCount = COUNT(*)
FROM (SELECT DISTINCT Row FROM #MyTestTable) Rows
CROSS JOIN (SELECT DISTINCT Person FROM #MyTestTable) Persons
INNER JOIN #MyTestTable t ON t.Row <= [Rows].Row AND t.Person = Persons.Person
GROUP BY Persons.Person,Rows.Row
I ran these with the input you provide and it generates the output you expect.

Nice work Adrian 🙂

Sujeet Singh