Sql in one statement without UNION

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 2000 Forums

Transact-SQL (2000)

Sql in one statement without UNION

Author

Topic

Kaleem021
Starting Member

26 Posts

Posted - 2005-05-04 : 04:43:24

I have three tables CashMemo, SaleReturn, Product with following structure
CashMemo
ProductId , PackSize , Date_Time , Quantity
1 S 01-May-2005 2
1 XL 01-May-2005 1
2 10.5 01-May-2005 3
...
...

SaleReturn
ProductId , PackSize , Date_Time , Quantity
1 S 02-May-2005 1
2 10.5 01-May-2005 1
3 M 03-May-2005 2
...
...

Product
ProductId , PackSize , Cost , GPC
1 S 1500 Apparel
1 M 1500 Apparel
1 L 1500 Apparel
1 XL 1500 Apparel
1 XXL 1500 Apparel
2 10.5 3000 Footware
3 M 1000 Apparel
...
..
I want to display report showing cost within a date range like between 01May to 03May
ProductId , Packsize , Date_Time , Quantity , Cost , GPC
1 S 01-May-2005 2 3000 Apparel
1 S 02-May-2005 -1 -1500 Apparel
1 XL 01-May-2005 1 1500 Apparel
2 10.5 01-May-2005 3 9000 Footware
2 10.5 01-May-2005 -1 3000 Footware
3 M 03-May-2005 -2 -2000 Apparel
I can do it like
Select .. from Cashmemo C inner join Product P on P.ProductId=C.ProductId where date_time between @FromDt and @ToDate
Union All
Select .. from SaleReturn SR inner join Product P on P.ProductId=SR.ProductId where date_time between @FromDt and @ToDate
is there any other way without using UNION.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-04 : 06:06:52

if you want to display them like that then no.

Go with the flow & have fun! Else fight the flow

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-04 : 17:55:54

What do you have against the UNION? I don't know of any particular reason to avoid it.

---------------------------
EmeraldCityDomains.com

Kaleem021
Starting Member

26 Posts

Posted - 2005-05-05 : 01:34:09

I am using Crystal Report 8.5. Records are grouped by ProductId and PackSize in report.
ProductId , Packsize , Quantity , Cost , GPC
1 S 1 1500 Apparel
1 XL 1 1500 Apparel
2 10.5 2 6000 Footware
3 M -2 -2000 Apparel
My client says this report runs slow for long date range. I think if records were selectedd in one statement(w/o UNION) it would be faster.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources