[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

help on combining 2 queries - sql

Posted on 2014-04-21
5
Medium Priority
?
214 Views
Last Modified: 2014-04-21
Can someone help me please?

i have 2 seperate queries both work fine but im now wanting to combine them.

1st query...

union's 2 tables for pass and fail..

SELECT SUM([Pass Qty])  AS 'Pass Qty' ,SUM([Fail Qty])  AS 'Fail Qty'
              FROM (
                       select
                             sum(case when TaTransCdKey = 'OutAccept' then TaQty else 0 end) as 'Pass Qty'
                           , sum(case when TaTransCdKey = 'OutReject' then TaQty else 0 end) as 'Fail Qty'
                       FROM TblTransaction WITH (NOLOCK) where tajobkey = @workorder
                           and TaTransCdKey in ('OutAccept','OutReject')
                           and TaStationIdKey = @workcenter
                       union all
                       select
                             sum(case when TaTransCdKey = 'OutAccept' then TaQty else 0 end) as 'Pass Qty'
                           , sum(case when TaTransCdKey = 'OutReject' then TaQty else 0 end) as 'Fail Qty'
                       FROM TtmpTransaction WITH (NOLOCK) where tajobkey = @workorder
                           and TaTransCdKey in ('OutAccept','OutReject')
                           and TaStationIdKey = @workcenter) as PQ


2nd query...

just gets sum of qty from a station joining 2 tables based on its station name

SELECT staQty.BktStationIDKey, SUM(staQty.BktQty) AS SumOfBktQty
        FROM tblStationQty staQty WITH (NOLOCK)
            INNER JOIN tblStation sta WITH (NOLOCK)
                  ON sta.StaIdKey = staQty.BktStationIdKey
       WHERE staQty.BktJobKey = @jobKey
      GROUP BY staQty.BktStationIDKey, sta.StaSeq
      ORDER BY sta.StaSeq


the relation between 1st query(TaStationIdKey ) and 2nd query(StaIdKey )

all i'm wanting is to be able to bring back the same pass and fail qty from 1st query and the SUM(staQty.BktQty) AS SumOfBktQty from 2nd in one row e.g.

Pass  Fail  SumOfBktQty
10      0      10



Thanks
0
Comment
Question by:razza_b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40012301
E.g.

DECLARE @JobKey INT;
DECLARE @workcenter INT;
DECLARE @workorder INT;

WITH Unioned AS 
	(
		SELECT	TaTransCdKey, 
			TaQty,
			tajobkey,
			TaStationIdKey
		FROM	TblTransaction
		UNION ALL
		SELECT	TaTransCdKey, 
			TaQty,
			tajobkey,
			TaStationIdKey
		FROM	TtmpTransaction
	),
	Query1 AS 
	(
		SELECT	SUM( CASE WHEN TaTransCdKey = 'OutAccept' THEN TaQty ELSE 0 END ) AS 'Pass Qty',
			SUM( CASE WHEN TaTransCdKey = 'OutReject' THEN TaQty ELSE 0 END ) AS 'Fail Qty'
		FROM	Unioned
		WHERE	tajobkey = @workorder
			AND TaTransCdKey IN ( 'OutAccept', 'OutReject' )
			AND TaStationIdKey = @workcenter
	),
	Query2 AS 
	(
		SELECT	Q.BktStationIDKey,
			SUM(Q.BktQty) AS SumOfBktQty
		FROM	tblStationQty Q
			INNER JOIN tblStation S ON S.StaIdKey = Q.BktStationIdKey
		WHERE	staQty.BktJobKey = @jobKey
			AND Q.BktStationIDKey = @workcenter
		GROUP BY Q.BktStationIDKey, S.StaSeq
	)
	SELECT	*
	FROM Query1 Q1
		CROSS JOIN Query2 Q2;

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 40012336
Thanks...

wheni run my 1st query just to check my qty's

SELECT SUM([Pass Qty])  AS 'Pass Qty' ,SUM([Fail Qty])  AS 'Fail Qty'
              FROM (
                       select
                             sum(case when TaTransCdKey = 'OutAccept' then TaQty else 0 end) as 'Pass Qty'
                           , sum(case when TaTransCdKey = 'OutReject' then TaQty else 0 end) as 'Fail Qty'
                       FROM TblTransaction WITH (NOLOCK) where tajobkey = 'DMFDUMMY06'
                           and TaTransCdKey in ('OutAccept','OutReject')
                           and TaStationIdKey = 'FSMT1'
                       union all
                       select
                             sum(case when TaTransCdKey = 'OutAccept' then TaQty else 0 end) as 'Pass Qty'
                           , sum(case when TaTransCdKey = 'OutReject' then TaQty else 0 end) as 'Fail Qty'
                       FROM TtmpTransaction WITH (NOLOCK) where tajobkey = 'DMFDUMMY06'
                           and TaTransCdKey in ('OutAccept','OutReject')
                           and TaStationIdKey = 'FSMT1') as PQ

i get ...

Pass Qty  Fail Qty
10             0

if i run your combined query i get empty row.

Im aiming to get the pass and fail qty from of what has already occured in 1st query and the stationqty from 2nd (if null or empty make it zero), as it wont always have stationqty as the qty gets moved to next station.
0
 
LVL 1

Author Comment

by:razza_b
ID: 40012343
if i run just this...


    WITH Unioned AS
      (
            SELECT      TaTransCdKey,
                      TaQty,
                        tajobkey,
                        TaStationIdKey
            FROM      TblTransaction
            UNION ALL
            SELECT      TaTransCdKey,
                        TaQty,
                        tajobkey,
                        TaStationIdKey
            FROM      TtmpTransaction
      ),
      Query1 AS
      (
            SELECT      SUM( CASE WHEN TaTransCdKey = 'OutAccept' THEN TaQty ELSE 0 END ) AS 'Pass Qty',
                      SUM( CASE WHEN TaTransCdKey = 'OutReject' THEN TaQty ELSE 0 END ) AS 'Fail Qty'
            FROM      Unioned
            WHERE      tajobkey = 'DMFDUMMY06'
                  AND TaTransCdKey IN ( 'OutAccept', 'OutReject' )
                  AND TaStationIdKey = 'FSMT1'
      ),
      Query2 AS
      (
            SELECT      isnull(SUM(Q.BktQty), '0') AS SumOfBktQty
            FROM      tblStationQty Q
                  INNER JOIN tblStation S ON S.StaIdKey = Q.BktStationIdKey
            WHERE      Q.BktJobKey = 'DMFDUMMY06'
                  AND Q.BktStationIDKey = 'FSMT1'
            GROUP BY Q.BktStationIDKey, S.StaSeq
      )
      SELECT      *
      FROM Query1 Q1


this is without the cross join at the bottom i get Pass Qty = 10 and Fail Qty = 0

it is close...
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40012346
Have you assigned the correct data types and values?

DECLARE @workcenter VARCHAR(255);
DECLARE @workorder VARCHAR(255);

SET @workcenter = 'FSMT1';
SET @workorder =  = 'DMFDUMMY06';

WITH Unioned AS 
	(
		SELECT	TaTransCdKey, 
			TaQty,
			tajobkey,
			TaStationIdKey
		FROM	TblTransaction
		UNION ALL
		SELECT	TaTransCdKey, 
			TaQty,
			tajobkey,
			TaStationIdKey
		FROM	TtmpTransaction
	)
	SELECT	SUM( CASE WHEN TaTransCdKey = 'OutAccept' THEN TaQty ELSE 0 END ) AS 'Pass Qty',
		SUM( CASE WHEN TaTransCdKey = 'OutReject' THEN TaQty ELSE 0 END ) AS 'Fail Qty'
	FROM	Unioned
	WHERE	tajobkey = @workorder
		AND TaTransCdKey IN ( 'OutAccept', 'OutReject' )
		AND TaStationIdKey = @workcenter;

Open in new window

0
 
LVL 1

Author Comment

by:razza_b
ID: 40012349
i got it this works, removed group by and got correct data....thank you sir!
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question