| Author |
 Topic  |
|
|
Rwj6001
Starting Member
USA
12 Posts |
 Posted - 01/14/2013 : 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
India
47189 Posts |
Posted - 01/15/2013 : 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
USA
12 Posts |
Posted - 01/15/2013 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 01/16/2013 : 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
India
47189 Posts |
Posted - 01/16/2013 : 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/
|
|
|
| |
Topic |
|
|
|
Combine selects
