Solved

help on combining 2 queries - sql

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 53
How to add an Index to a date/time field? 15 39
Please explain Equi-join 3 42
Any benefit to adding a Clustered index here? 4 35
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

737 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