Combine selects - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Combine selects

Author

Topic

Rwj6001
Starting Member

12 Posts

Posted - 2013-01-14 : 18:57:21

I have two stored procedures that I need to somehow combine and I have no idea how to do it.

The procedures are as follows:

SP1:


SELECT A.PLU, A.StoreID, SUM(A.Movement) AS SALES
FROM
(SELECT PLU, StoreID, Movement from dbo.itemhistory WHERE (SaleDate BETWEEN CAST(@startdate AS smalldatetime) AND CAST(@enddate AS smalldatetime))) AS A
WHERE (A.PLU = @PLU)
GROUP BY A.StoreID, A.PLU

SP1 returns all the sales between a specified date range, and sums them by storeID, as follows:

Item# - StoreID - Sales
0011 - store770 - 500
0011 - store771 - 400
0011 - store771 - 300

SP2:


select SUM(a.Store770) AS Store770, SUM(a.store771) AS Store771, SUM(a.store773) AS Store773, SUM(a.store775) AS Store775, SUM(a.store776) AS Store776, SUM(a.store777) AS Store777
FROM
(SELECT * FROM dbo.TheView1 WHERE (PLU = @PLU) AND (OrderDate BETWEEN CAST(@startdate AS smalldatetime) AND CAST(@enddate AS smalldatetime))) as a

SP2 returns all preorders between a specified date range, and sums them by store number as follows:

store770preorder - store771preorder - store772preorder
150 - 250 - 350

I would like to combine the two statements to produce the following output:

Item# - storeID - sales - preorder
0011 - store770 - 500 - 150
0011 - store771 - 400 - 250
0011 - store772 - 300 - 350

Is this possible? It seems that it is possible using joins in my select statements, but I don't have a lot of experience using them.

Tables are below:

Table1
PLU - Primary key
StoreID - Primary Key
SaleDate - Primary Key
Movement

Table2
OrderID - Primary Key
PLU - Primary Key
Orderdate - Primary Key
Store770
store771
store773
store775

If combining them is not possible, then possibly I can create a temp table, or a view with one statement and then update it with the second one?

Thanks in advance for any help!

Rwj6001

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-15 : 00:26:04

you could do like this


SELECT m.*,n.Amt
FROM
(
SELECT A.PLU, A.StoreID, SUM(A.Movement) AS SALES
FROM
(SELECT PLU, StoreID, Movement from dbo.itemhistory WHERE (SaleDate BETWEEN CAST(@startdate AS smalldatetime) AND CAST(@enddate AS smalldatetime))) AS A
WHERE (A.PLU = @PLU)
GROUP BY A.StoreID, A.PLU
)m
LEFT JOIN
(
select Store,Amt
from
(
select SUM(a.Store770) AS Store770, SUM(a.store771) AS Store771, SUM(a.store773) AS Store773, SUM(a.store775) AS Store775, SUM(a.store776) AS Store776, SUM(a.store777) AS Store777
FROM
(SELECT * FROM dbo.TheView1 WHERE (PLU = @PLU) AND (OrderDate BETWEEN CAST(@startdate AS smalldatetime) AND CAST(@enddate AS smalldatetime))) as a
)p 
UNPIVOT(Amt FOR Store IN ([Store770],[Store771],...))u
)n
ON n.Store = m.StoreID

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

Rwj6001
Starting Member

12 Posts

Posted - 2013-01-15 : 09:09:34

Thank you for the reply. However, when I attempt a test of the combined query I get the following error:

Msg 102, Level 15, State 1, Procedure TheView_Movement_PLU__plus_preorder, Line 35
Incorrect syntax near ')'.

Which seems to indicate a problem on the following line:


UNPIVOT(Amt FOR Store IN ([Store770],[Store771],...))u

Thanks!

Rwj6001

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 04:26:04

quote:
Originally posted by Rwj6001
UNPIVOT(Amt FOR Store IN ([Store770],[Store771],...))u

That should be....

UNPIVOT(Amt FOR Store IN ([Store770],[Store771],[Store773], [Store775], [Store776], [Store777]))u

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 23:55:33

quote:
Originally posted by Rwj6001

Thank you for the reply. However, when I attempt a test of the combined query I get the following error:

Msg 102, Level 15, State 1, Procedure TheView_Movement_PLU__plus_preorder, Line 35
Incorrect syntax near ')'.

Which seems to indicate a problem on the following line:

UNPIVOT(Amt FOR Store IN ([Store770],[Store771],...))u

Thanks!

Rwj6001

you need to explicitly put all values you want to pivot on!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources