?
Solved

help on combining 2 queries - sql

Posted on 2014-04-21
5
Medium Priority
?
211 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
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…

771 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