October 14, 2013 at 4:46 am
please i have a one problem this query
DECLARE @a1 table(
p1 int ,
x nvarchar(20),
date1 smalldatetime
)
Insert Into @a1
(p1,x,date1)
Values
(1,'yes','2013-10-01')
Insert Into @a1
(p1,x,date1)
Values
(2,'yes','2013-10-02')
Insert Into @a1
(p1,x,date1)
Values
(3,'yes','2013-10-03')
----------
select p1=case when p1 >= 1 and p1 <= 2 then 'my phone ' else 'thanks' end ,
count(x) num_p1 from @a1 where date1>='2013-10-01' group by p1
----
p1 num_p1
my phone 1
my phone 1
thanks 1
DECLARE @a2 table(
p2 int ,z nvarchar(20),
date2 smalldatetime
)
Insert Into @a2
(p2,z,date2)
Values
(1,'end','2013-10-01')
Insert Into @a2
(p2,z,date2)
Values
(2,'end','2013-10-02')
Insert Into @a2
(p2,z,date2)
Values
(3,'end','2013-10-03')
select p2=case when p2 >= 1 and p2 <= 2 then 'my phone ' else 'thanks' end ,
count(z) num_p2 from @a2 where date2>='2013-10-01' group by p2
-----
p2 num_p2
my phone 1
my phone 1
thanks 1
------
DECLARE @a3 table(
p3 int ,x nvarchar(20),
date3 smalldatetime
)
Insert Into @a3
(p3,x,date3)
Values
(1,'no','2013-10-01')
Insert Into @a3
(p3,x,date3)
Values
(2,'no','2013-10-02')
Insert Into @a3
(p3,x,date3)
Values
(3,'no','2013-10-03')
Insert Into @a3
(p3,x,date3)
Values
(4,'no','2013-10-05')
Insert Into @a3
(p3,x,date3)
Values
(5,'no','2013-10-06')
select p3=case when p3 >= 1 and p3 <= 2 then 'my phone '
when p3 >= 3 and p3 <= 4 then 'my '
when p3 = 5 then 'your '
else 'thanks' end ,
count(x) num_p3 from @a3 where date3>='2013-10-01' group by p3
------
p3 num_p3
-----------------
my phone 1
my phone 1
my 1
my 1
your 1
how i get resulting table
phone num_p1 num_p2 num_p3
my phone 1 0 1
my phone 1 0 1
my 0 0 1
my 0 0 1
thanks 0 1 0
October 14, 2013 at 11:09 am
Your sample data and your expected output don't seem to match please clarify
a) Why not my phone 1 1 1 and my phone 1 1 1?
b) Why isn't "your" included at all?
c) Why not thanks 1 1 0?
d) How are the numeric values fpr p1, p2, and p3 related to each other in the first place? (Except for the manual replacement using the CASE expression)?
Sometimes (over-)simplifying a scenario will not really help to clarify what you're looking for...
October 15, 2013 at 1:24 pm
This sounds like a text book question...and my guess is it wasn't posted the way it was asked in the book.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply