Union query - 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

Union query

Author

Topic

mkgmahi
Starting Member

24 Posts

Posted - 2011-08-11 : 02:33:21

how to use one parameter with union query...please help me out

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-08-11 : 02:37:23

Please give an example of what you want.

No, you're never too old to Yak'n'Roll if you're too young to die.

mkgmahi
Starting Member

24 Posts

Posted - 2011-08-11 : 02:47:11

select x,y,z from xx where date = '07/07/2011' union select x,y,z from yy where date = '07/07/2011'. Now instead of passing parameter twice, i have to use one parameter even if it is a union parameter. Is that possible please help me out

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-08-11 : 02:53:23

It's the same parameter used twice! that doesn't mean you have to send it two times...

Declare @myDate datetime = '07/07/2011'
select x,y,z from xx where date = @myDate
union
select x,y,z from yy where date = @myDate

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

mkgmahi
Starting Member

24 Posts

Posted - 2011-08-11 : 03:11:48

Thank you so much for the solution. I tried it was working....

mkgmahi
Starting Member

24 Posts

Posted - 2011-08-11 : 03:31:43

Is there a different way of writing the same above query with one parameter? As my lead is asking for a different possible way.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-08-11 : 06:48:09

Here's one way:


SELECT x,y,z
FROM(
	select x,y,z,date from xx
	union 
	select x,y,z,date from yy
) a
WHERE date= '07/07/2011'

It'll probably run slower though.

mkgmahi
Starting Member

24 Posts

Posted - 2011-08-11 : 07:48:44

Thank you... its working

mkgmahi
Starting Member

24 Posts

Posted - 2011-08-21 : 02:38:36

I have to write another query where i am facing problem again in passing parameter.

select x,y,z from xx where recdate between '01/07/2011' and '31/07/2011' union select x,y,z from yy where orddate between '01/07/2011' and '31/07/2011'. So now i have two different date fields but i need to pass to this query as one parameter again and my boss says its possible to pass as one parameter even though there are different date fields. Could any one help me out in this.... i am not getting the solution

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-21 : 02:59:14

quote:
Originally posted by mkgmahi

I have to write another query where i am facing problem again in passing parameter.

select x,y,z from xx where recdate between '01/07/2011' and '31/07/2011' union select x,y,z from yy where orddate between '01/07/2011' and '31/07/2011'. So now i have two different date fields but i need to pass to this query as one parameter again and my boss says its possible to pass as one parameter even though there are different date fields. Could any one help me out in this.... i am not getting the solution

This is basically the same as your earlier question.

Just replace the 2 dates with variables or your input



declare @date_st datetime, @date_en datetime
select @date_st = '2011-07-01',
       @date_en = '2011-07-31'

select x,y,z from xx where recdate between @date_st and @date_en 
union 
select x,y,z from yy where orddate between @date_st and @date_en

Basically this is what Lumbago has posted earlier

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-21 : 03:50:33

if you want to pass values from same parameter as your boss says. you need this


declare @date datetime
select @date = '2011-07-01'

select x,y,z from xx where recdate >=@date and recdate< DATEADD(mm,1,@date)
union 
select x,y,z from yy where orddate >=@date and orddate < DATEADD(mm,1,@date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-08-22 : 04:34:16

Or you can even use the same query I posted before...



SELECT x,y,z
FROM(
	select x,y,z,date=recdate  from xx
	union 
	select x,y,z,date=orddate  from yy
) a
WHERE date between '01/07/2011' and '31/07/2011'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 04:37:08

quote:
Originally posted by michael.appleton

Or you can even use the same query I posted before...

SELECT x,y,z
FROM(
select x,y,z,date=recdate from xx
union
select x,y,z,date=orddate from yy
) a
WHERE date between '01/07/2011' and '31/07/2011'

I think OP is asking a way to pass the date range values through a single parameter not passing them directly.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-08-22 : 04:53:59

Sure, it's just the first question they asked they also wanted a second type of solution where it seemed by passing a parameter they actually meant using it in the query in one place, hence my first answer where the date is only referred to once. I'm not really sure why the needed this kind of method, maybe they didn't mean that! But for some reason Lumbago's first solution didn't seem good enough for the OP.

So changing my last query to actually use parameters:




SELECT x,y,z
FROM(
	select x,y,z,date=recdate  from xx
	union 
	select x,y,z,date=orddate  from yy
) a
WHERE
 date>=@Date 
 and
 date<dateadd(month,1,@Date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources