SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Combine selects
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rwj6001
Starting Member

USA
12 Posts

Posted - 01/14/2013 :  18:57:21  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 01/15/2013 :  00:26:04  Show Profile  Reply with Quote
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/

Go to Top of Page

Rwj6001
Starting Member

USA
12 Posts

Posted - 01/15/2013 :  09:09:34  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/16/2013 :  04:26:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 01/16/2013 :  23:55:33  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000