select with date parameters - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

select with date parameters

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-14 : 05:09:19

hi
i have 3 table

DECLARE @a1 table(
p1 int ,
date1 smalldatetime
)
Insert Into @a1
(p1,date1)
Values
(1,'2013-10-01')
Insert Into @a1
(p1,date1)
Values
(2,'2013-10-02')
Insert Into @a1
(p1,date1)
Values
(3,'2013-10-03')
select count(p1 ) as num_p1 from @a1 where date1<='2013-10-01'
-----
num_p1
1
------
DECLARE @a2 table(
p2 int ,
date2 smalldatetime
)
Insert Into @a2
(p2,date2)
Values
(1,'2013-10-01')
Insert Into @a2
(p2,date2)
Values
(2,'2013-10-02')
Insert Into @a2
(p2,date2)
Values
(3,'2013-10-03')
select count(p2 ) as num_p2 from @a2 where date2>='2013-10-01'

num_p2
3
----------
DECLARE @a3 table(
p3 int ,
date3 smalldatetime
)
Insert Into @a3
(p3,date3)
Values
(1,'2013-10-01')
Insert Into @a3
(p3,date3)
Values
(2,'2013-10-02')
Insert Into @a3
(p3,date3)
Values
(3,'2013-10-03')
Insert Into @a3
(p3,date3)
Values
(4,'2013-10-05')
Insert Into @a3
(p3,date3)
Values
(5,'2013-10-06')
select count(p3 ) as num_p3 from @a3 where date3>='2013-10-01'
--
num_p3
5

how i get resulting table

num_p1 num_p2 num_p3
1 3 5

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 05:25:43

[code]
SELECT (select count(p1 ) as num_p1 from @a1 where date1<='2013-10-01') num_p1,
(select count(p2 ) as num_p2 from @a2 where date2>='2013-10-01') num_p2,
(select count(p3 ) as num_p3 from @a3 where date3>='2013-10-01') num_p3
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-14 : 05:28:15

thank you very much visakh

you is my friend

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 05:29:28

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-14 : 06:39:26

please visakh i have a one problem this query

i ?hanged 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

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-14 : 07:00:41

?????

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 07:02:21

what happened to other values like your from last query? can you specify your rules clearly?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-14 : 07:17:01

ok no problem

i have 3 table

@a1
@a2
@a3

and have 3 query
for
@a1

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

result
p1 num_p1

my phone 1
my phone 1
thanks 1
for @a2

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

result

p2 num_p2
my phone 1
my phone 1
thanks 1

and for @a3

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

result

p3 num_p3
my phone 1
my phone 1
my 1
my 1
your 1

how i make Group Result all queries

for example

phone num_p1 num_p2 num_p3

my phone 1 0 1

my 0 0 1

thanks 0 1 0

your 0 0 1

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 07:22:19

Still output is not correct.
For example how did you get value as 0 for num_p2 for my phone whreas its 1 for the other two? In separate outputs i see same value for them in all the three cases.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-14 : 07:30:49

yes you are right

I have written is not correct

for example

phone num_p1 num_p2 num_p3
my phone 1 1 1
thanks 1 1 0

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-14 : 08:08:31

???

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 08:53:58

[code]
select p1,
case when SUM(num_p1) > 0 THEN 1 ELSE 0 END AS num_p1,
case when SUM(num_p2) > 0 THEN 1 ELSE 0 END AS num_p2,
case when SUM(num_p3) > 0 THEN 1 ELSE 0 END AS num_p3
from
(
select p1=case when p1 >= 1 and p1 <= 2 then 'my phone ' else 'thanks' end ,
count(x) num_p1,0 as num_p2,0 as num_p3 from @a1 where date1>='2013-10-01' group by p1
union all
select case when p2 >= 1 and p2 <= 2 then 'my phone ' else 'thanks' end ,0 as num_p1,
count(z) num_p2,0 as num_p3 from @a2 where date2>='2013-10-01' group by p2
union all
select 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 ,0 as num_p1,0 as num_p2,
count(x) num_p3 from @a3 where date3>='2013-10-01' group by p3
)t
group by p1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-10-17 : 05:48:13

thank you very much visakh
you is my friend

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 07:41:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources