Select Transaction in same table with Sub Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select Transaction in same table with Sub Query

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-03-02 : 22:47:24

TABLE1:

ID UniqueId DATE TIME Q2 Q3 Info1 Info2 Info3
1 100 03/01/2011 17:40:37 1 11 NULL 9092892450 NULL
2 100 03/01/2011 17:40:42 19 0 5000 NULL NULL
3 100 03/01/2011 17:40:51 20 0 5000 NULL NULL
4 100 03/01/2011 17:40:59 15 0 5000 NULL NULL
5 100 03/01/2011 17:41:07 16 0 5000 NULL NULL
6 100 03/01/2011 17:41:16 17 0 5000 NULL NULL
7 100 03/01/2011 17:41:23 18 10 46 101 NULL
8 100 03/01/2011 17:41:29 18 7 46 6 1
9 101 03/02/2011 17:42:28 1 11 NULL 9940640699 NULL
10 101 03/02/2011 17:42:40 20 0 5000 NULL NULL
11 101 03/02/2011 17:42:45 1 1 1 1 1
12 102 03/02/2011 17:42:58 1 11 NULL 9940540588 NULL
13 102 03/02/2011 17:43:05 20 0 5000 NULL NULL
14 102 03/02/2011 17:43:15 15 0 5000 NULL NULL
15 102 03/02/2011 17:43:22 16 0 5000 NULL NULL
17 102 03/02/2011 17:43:23 18 10 46 101 NULL
18 102 03/02/2011 17:43:39 18 8 46 16 1

Above is my sample table records. There you can find UniqueId column. It Reference each call transaction. For each Unique Id there may be Multiple columns. I need the output as single row for each Unique Id..

Will explain how the Output should. I need 6 columns as my output.Column follows...
UniqueID: Distinct Unique Id
Date: Unique Date for each Unique Id
Time: Time from Unique Id with Q3=11
MDN: Info2 from UniqueId with Q3=11
Status: Q3 from UniqueId (If Q3=1 Then 'NO ANSWER'.
If Q3 IN (7,8) Then 'ANSWER'. Here
Q3 IN (7,8) must be come from Q3=10)
Duration: Info2 from uniqueId (If Q3=1 Info2 Value
If Q3 IN (7,8) Info2 Value AND
Q3 IN (7,8) must be come from Q3=10)

Hence the Output Must be:

UNIQUEID DATE TIME MDN STATUS DURATION
100 03/01/2011 17:40:37 9092892450 ANSWER 6
101 03/02/2011 17:42:28 9940640699 NO ANSWER 1
102 03/02/2011 17:42:58 9940540588 ANSWER 16

Please help in this to built a Query ASAP. Its all comes from Sub Query within the table. It must be Distinct no repetition. Its sample records I will get 1000 of records per day.

Regards,
Kalaiselvan R
Love Yourself First....

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-11 : 07:05:18

Select distinct UNIQUEID, Date, Case when Q3=11 then TIME else null end as Time, Case when Q3=11 then Info2 else null end as MDN, Case when Q3=1 Then 'NO ANSWER' else Q3 end as STATUS,
Case Q3
when 1 then Info2
when 7 then Info2
--I lost your logic here
else '......' end as DURATION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources