February 21, 2012 at 2:58 am
Hi ,
Please find the logic for the below table in sql 2005.(without using the while loops)
Input OutPut
Row Person Row Person Count
1 101 1 101 1
1 102 1 102 1
1 103 1 103 1
2 101 2 101 2
2 102 2 102 2
2 104 2 103 1
3 101 2 104 1
3 103 3 101 3
3 105 3 102 2
3 103 3
3 104 1
3 105 1
February 21, 2012 at 3:29 am
vemula.narayanan (2/21/2012)
Hi ,Please find the logic for the below table in sql 2005.(without using the while loops)
Input OutPut
Row Person Row Person Count
1 101 1 101 1
1 102 1 102 1
1 103 1 103 1
2 101 2 101 2
2 102 2 102 2
2 104 2 103 1
3 101 2 104 1
3 103 3 101 3
3 105 3 102 2
3 103 3
3 104 1
3 105 1
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.
February 21, 2012 at 3:38 am
Hi
Input OutPut
Row Person Row Person Count
1 101 1 101 1
1 102 1 102 1
1 103 1 103 1
2 101 2 101 2
2 102 2 102 2
2 104 2 103 1
3 101 2 104 1
3 103 3 101 3
3 105 3 102 2
3 103 3
3 104 1
3 105 1
February 21, 2012 at 3:43 am
Hi Divine Flame,
Sorrry for Inconvenience.Please Construct the query to obtain the requiered output table without using while loops.
Input Table Requiered OutPut Table
Row Person Row Person Count
1 101 1 101 1
1 102 1 102 1
1 103 1 103 1
2 101 2 101 2
2 102 2 102 2
2 104 2 103 1
3 101 2 104 1
3 103 3 101 3
3 105 3 102 2
3 103 3
3 104 1
3 105 1
Thanks & Regards,
Narayanan
February 21, 2012 at 3:45 am
Questions asked with such limited information/clarity have very less chances to be answered/answered correctly.
February 21, 2012 at 3:46 am
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://qa.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
February 21, 2012 at 3:54 am
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
Thanks & Regards
Narayanan
February 21, 2012 at 4:02 am
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
February 21, 2012 at 4:15 am
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
February 21, 2012 at 4:23 am
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
February 21, 2012 at 6:19 am
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.
February 22, 2012 at 1:23 am
Hi Dwine Flame,
Its really helpfull..Thanks a lot
with Regards
Narayanan
February 23, 2012 at 6:58 am
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.
February 23, 2012 at 10:40 pm
hi adrian.facio,
This Query shows the good performance and got the exact output..
Thanks!!!
February 24, 2012 at 1:30 am
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 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply