Solved

help on combining 2 queries - sql

Posted on 2014-04-21
5
210 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 34

Accepted Solution

by:
ste5an earned 500 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 34

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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