Solved

help on combining 2 queries - sql

Posted on 2014-04-21
5
206 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
  • 3
  • 2
5 Comments
 
LVL 33

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 33

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

825 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