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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy