I have inherited a stored procedure that runs a GROUP BY query, places it into a temp table, then combines columns from the temp table into another GROUP BY query. If possible I'd like to do away with the temp table and run it using one query.
The first query is: -
SELECT M.[CO No], DateName(month, S.[Calendar Mnth Key]) AS Month_Name, LEFT(S.[Calendar Mnth Key], 4) AS Year, S.[Calendar Mnth Key]) AS [Date], [Company Name], SUM(S.Amt) AS [2010 Sales], SUM(S.[Txn Cnt]) AS [2010 Sales Trans], [2010 Avg Sales Ticket] =
-
CASE SUM(S.[Txn Cnt])
-
WHEN 0 THEN 0
-
ELSE SUM(S.[Amt)/SUM(S.[Txn Cnt]) END,
-
FROM Merch AS M
-
INNER JOIN Sales AS S
-
ON M.[Cust Key] = S.[Cust Key]
-
WHERE M.[CO No] = 1234
-
AND (S.[Calendar Mnth Key] >= 201001
-
AND S.[Calendar Mnth Key] <= 201012)
-
AND S.[Mop Key] IN (16,24)
-
GROUP BY M.[CO No], DateName(month, S.[Calendar Mnth Key]), LEFT(S.[Calendar Mnth Key], 4), S.[Calendar Mnth Key]), [Company Name]
-
ORDER BY S.[Calendar Mnth Key]
-
The second query, without the fields from the first query, would be the exact same as the first, only dealing with Returns: -
SELECT M.[CO No], DateName(month, R.[Calendar Mnth Key]) AS Month_Name, LEFT(R.[Calendar Mnth Key], 4) AS Year, R.[Calendar Mnth Key]) AS [Date], [Company Name], SUM(R.Amt) AS [2010 Returns], SUM(R.[Txn Cnt]) AS [2010 Return Trans], [2010 Avg Return Ticket] =
-
CASE SUM(R.[Txn Cnt])
-
WHEN 0 THEN 0
-
ELSE SUM(R.[Amt)/SUM(R.[Txn Cnt]) END,
-
FROM Merch AS M
-
INNER JOIN Returns AS R
-
ON M.[Cust Key] = R.[Cust Key]
-
WHERE M.[CO No] = 1234
-
AND (R.[Calendar Mnth Key] >= 201001
-
AND R.[Calendar Mnth Key] <= 201012)
-
AND R.[Mop Key] IN (16,24)
-
GROUP BY M.[CO No], DateName(month, R.[Calendar Mnth Key]), LEFT(R.[Calendar Mnth Key], 4), R.[Calendar Mnth Key]), [Company Name]
-
ORDER BY R.[Calendar Mnth Key]
-
I need to combine them into one query to return the results below: -
CO No Month_Name Year Date Company Name 2010 Sales 2010 Trans 2010 Avg Ticket 2010 Returns 2010 Trans 2010 Avg Ticket
-
1234 January 2010 1/1/2010 Bob's Bobbers $4,485,980.95 9322 $ 481.23 $(14,540.52) 56 $ (259.65)
-
1234 February 2010 2/1/2010 Bob's Bobbers $3,743,675.54 8120 $ 461.04 $(21,844.87) 74 $ (295.20)
-
1234 March 2010 3/1/2010 Bob's Bobbers $4,068,638.11 9028 $ 450.67 $(26,547.92) 80 $ (331.85)
-
1234 April 2010 4/1/2010 Bob's Bobbers $4,474,000.98 9709 $ 460.81 $(35,441.54) 97 $ (365.38)
-
1234 May 2010 5/1/2010 Bob's Bobbers $5,041,425.10 10817 $ 466.06 $(35,551.93) 110 $ (323.20)
-
1234 June 2010 6/1/2010 Bob's Bobbers $5,832,407.31 12703 $ 459.14 $(36,107.16) 128 $ (282.09)
-
Thanks for any help you can give!
1 1383
Try something like... -
SELECT 'sales' as source, alltheothercolumns
-
from YourFirstQuery
-
UNION ALL
-
SELECT 'returns' as source, alltheothercolumns
-
from YourSecondQuery
-
The derived "source" column will identify which record came from which query.
Good Luck!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: frizzle |
last post by:
Hi,
I know this might sound strange but i think(/hope)
it's quite simple:
I'm running 2 queries in a mysql DB, first one returns 20 results.
Now how can i echo results from the second query...
|
by: Bruce MacDonald |
last post by:
I've got a question/request for the SQL gurus.
I'm building a model of bandwidth demand in MS Access and want to get
aggregated results for demand at each PCP in each time period.
The two...
|
by: Mark Harrison |
last post by:
How can I combine these two queries?
# select viewerid,count(*) from viewer_movies group by viewerid order by viewerid;
viewerid | count
----------+--------
22964835 | 3055
22964836 | ...
|
by: isdeveloper |
last post by:
Hi All,
I have a problem with a table that I want to get nice data out of in a
single query. The guys here reckon it can't be done in a single query
but I wanted to prove them wrong !!...
|
by: abbaskhan |
last post by:
I have four columns in a table called rpot1(on overdraft bank a/c)
Actually it is a reporting table
1)sum(os)---outstandings(sm_os)
2)sum(lmt_gap)---limit gap (limit allowed for overdraft-actual...
|
by: billelev |
last post by:
Does anyone know if it is possible to combine two queries that have the same fields? I basically want to combine a number of fields with the totals for those fields into one query (see example...
|
by: Hillary H |
last post by:
I would like to take these two queries and combine them into one if possible. This is the first query:
SELECT DISTINCT . INTO
FROM INNER JOIN NOLDBA_OBLIGATION ON .=NOLDBA_OBLIGATION.ID_CASE...
|
by: tcveltma |
last post by:
Hi again,
Ok, so I have about 15 crosstab queries. Each crosstab query is an employee's name. Within the query are the weeks as the column heading, the work order numbers as the row heading, and...
|
by: |
last post by:
I have query1 where I set a date range. Query2 is a crosstab query and is based on query1. I don't want the queries to be saved in the mdb but I do want them to be run in VBA using a querystring. If...
|
by: autoalert |
last post by:
HI
I am running mysql 5.0.51a and have a table with the following structure
ID, Date, startTime, stopTime, Name
I want to find the last stopTime from yesterday for each name and the first...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |