Need help with (in my mind) a complex query with subqueries

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 1615 Views - Last Post: 09 May 2012 - 01:05 PM

#1 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 07:23 AM

I work for a book manufacturing company

Minimal background of problem: Trying to calculate Number Of Carts containing specific paper types of specific sheet sizes for jobs that have and have not yet been scheduled.

basically, there will be 4 different queries that I need combined into 1 master query - I've got the 4 queries ironed out, I just need help combining them.

Qry1 gives results for text paper required for scheduled jobs.
Columns returned: PaperType, SheetSize, TotalNumberCarts, ScheduledCartsRequired

Qry2 gives results for text paper required for unscheduled jobs.
Columns returned: PaperType, SheetSize, TotalNumberCarts, UncheduledCartsRequired

Qry3 gives results for cover paper required for scheduled jobs.
Columns returned: PaperType, SheetSize, TotalNumberCarts, ScheduledCartsRequired

Qry4 gives results for cover paper required for unscheduled jobs.
Columns returned: PaperType, SheetSize, TotalNumberCarts, UncheduledCartsRequired

Basically, I'm having an issue trying to combine all 4 queries into 1 master query. There are all sorts of joins in each column to get the desired information for each, but I need a hand getting them combined.
Desired Columns Returned from all 4:
PaperType, SheetSize, TotalNumberCarts, ScheduledCartsRequired, UncheduledCartsRequired

If someone could give me a push in the right direction, that would be helpful.

Thanks,
Tim

Is This A Good Question/Topic? 0
  • +

Replies To: Need help with (in my mind) a complex query with subqueries

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13758
  • View blog
  • Posts: 54,939
  • Joined: 12-June 08

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 07:38 AM

Why would you want to aggregate all of those columns? Those numbers appear to be for pretty disparate functions, and I were to be looking at them I would appreciate having them broken down to that level.
Was This Post Helpful? 0
  • +
  • -

#3 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 07:55 AM

Mainly because the boss wants 1 report with all of the information on it. The people using the report know which paper types are for text and which are for cover - the only reason it's broken down into 4 queries is because of the way the data is in the database (not my design...). They want a quick way to see how many carts of each particular type and size we have, as well as what's needed in the near future so they can schedule what needs to be sheeted (cut down to size X x Y) next.

EDIT:
Additionally, I'm going to be doing some other math in there, too... the final result will be:
PaperType, SheetSize, CartInventory, ShedCartsRequired, UncommittedCarts (inventory - scheduled carts required), UnschedCartsRequired, AvailableCartInventory (uncommitted - unscheduled)

This post has been edited by tbare: 09 May 2012 - 08:05 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13758
  • View blog
  • Posts: 54,939
  • Joined: 12-June 08

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 08:10 AM

Hmm.. okay - just a thought - since the columns are all the same why not do a union so you get four lines of distinct data AND then do a sum at the bottom? A classic data aggregate report!
Was This Post Helpful? 0
  • +
  • -

#5 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 08:58 AM

View Postmodi123_1, on 09 May 2012 - 09:10 AM, said:

Hmm.. okay - just a thought - since the columns are all the same why not do a union so you get four lines of distinct data AND then do a sum at the bottom? A classic data aggregate report!


Mainly because the report needs to be as described - My boss is an accountant, and very particular about how he wants information. :)

I'm not trying to be a tool - I do appreciate the options given (always nice to see other ways to tackle a given problem), but i really just need to be pointed in the direction of getting the results of all 4 queries in 1 query and I'm having a hard time wrapping my head around the syntax to that -- I really want to avoid a stored procedure if I can... :-/
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13758
  • View blog
  • Posts: 54,939
  • Joined: 12-June 08

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 09:49 AM

Understandable.. So yeah, what you need to do is union your four statements, and then use that as a the source for one more select statement where you union up the like columns.

Here's an example.. fyi - the alias bit is important.

-- example tables.. same column types.
create table #temp(lVal int, lPageVal int)
create table #foo(lVal int, lPageVal int)

-- just filling them.. 
insert into #temp (lval, lPageVal)
	values (1, 50)
insert into #temp (lval, lPageVal)
	values (2, 51)

insert into #foo (lval, lPageVal)
	values (5, -100)
insert into #foo (lval, lPageVal)
	values (3, 1000)

-- verifying data's there
select lval, lPageVal
from #temp
-- verifying data's there
select lval,lPageVal
from #foo

-- sum the values over a union of all the data.
select sum(a.lval), sum(a.lPageVal)
from (
	select lval, lPageVal
	from #temp
	
	union
	
	select lval,lPageVal
	from #foo
) a  -- the alias is critical


-- remove the tables.
drop table #temp
drop table #foo




Quote

lval lPageVal
---- ------
1 50
2 51

---- ------
5 -100
3 1000

---- ------
11 1001

Was This Post Helpful? 0
  • +
  • -

#7 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 10:50 AM

We're on the right track, I believe...

The issue I'm coming up with now is this: I can UNION the Text and Cover Scheduled OR Unscheduled -- But I'm not sure how to get both -- thoughts?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13758
  • View blog
  • Posts: 54,939
  • Joined: 12-June 08

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 10:58 AM

Quote

The issue I'm coming up with now is this: I can UNION the Text and Cover Scheduled OR Unschedu

What? What text? I thought this was all about the numbers.
Was This Post Helpful? 0
  • +
  • -

#9 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 11:00 AM

It is.. Sorry -

I can UNION the 2 queries to get ScheduledCartsRequired (text paper and cover paper) OR UnscheduledCartsRequired (text paper and cover paper), but I can't get both...
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13758
  • View blog
  • Posts: 54,939
  • Joined: 12-June 08

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 11:06 AM

What error is it giving you? What is your query looking like now?
Was This Post Helpful? 0
  • +
  • -

#11 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 11:16 AM

My query looks something like:
SELECT A.ID, A.SheetSize, A.PaperType, A.NumberCarts, --Count(QuotePaper.Quote_ID) AS TotalScheduledJobs, 
 SUM(A.ScheduledCartsRequired) AS ScheduledCartsRequired, 
 SUM(A.UnscheduledCartsRequired) AS UnscheduledCartsRequired
 
 FROM 
(SELECT PaperCartsInventory.*,
		ROUND((SUM(QuoteJobInfo.TxtShtsPrJob) / (.5 * 30 * QuotePaper.PPI)),2) AS ScheduledCartsRequired 
	FROM PaperCartsInventory --...Joins, Where, Group by etc..

	UNION
	
	SELECT PaperCartsInventory.*,
		ROUND((SUM((QuoteJobInfo.CvrShtsPrJob) * 2) / (4 * 30 * QuotePaper.PPI)),2) AS ScheduledCartsRequired 
	FROM PaperCartsInventory --...Joins, Where, Group by etc..

	UNION

	SELECT PaperCartsInventory.*,
		ROUND((SUM(QuoteJobInfo.TxtShtsPrJob) / (.5 * 30 * QuotePaper.PPI)),2) AS UnscheduledCartsRequired 
	FROM PaperCartsInventory --...Joins, Where, Group by etc..

	UNION
	
	SELECT PaperCartsInventory.*,
		ROUND((SUM((QuoteJobInfo.CvrShtsPrJob) * 2) / (4 * 30 * QuotePaper.PPI)),2) AS UnscheduledCartsRequired 
	FROM PaperCartsInventory --...Joins, Where, Group by etc..
) A

Group By A.ID, A.SheetSize, A.PaperType, A.NumberCarts
ORDER BY A.PaperType ASC, A.SheetSize ASC



And get the error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'UnscheduledCartsRequired'.


Edit:
Thanks for helping out, by the way. I've been pounding my head against the table for a while now on this... I'm a programmer that gets thrown SQL issues from time to time -- I learn as I go..

This post has been edited by tbare: 09 May 2012 - 11:24 AM

Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13758
  • View blog
  • Posts: 54,939
  • Joined: 12-June 08

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 11:26 AM

As a personal habit - I always enumerate my columns when dealing with unions.

SELECT PaperCartsInventory.*,


Remember - unions like things to be the same across the board. If you have a query that doesn't have a "ScheduledCartsRequired" column then add one with a zero in it.. it keeps the column names in sync across the board and doesn't add anything to your sum!

SELECT PaperCartsInventory.*
,0 as ScheduledCartsRequired
, ROUND((SUM(QuoteJobInfo.TxtShtsPrJob) / (.5 * 30 * QuotePaper.PPI)),2) AS UnscheduledCartsRequired 



The same goes for ones without a "UnscheduledCartsRequired " column.

SELECT PaperCartsInventory.*
,ROUND((SUM((QuoteJobInfo.CvrShtsPrJob) * 2) / (4 * 30 * QuotePaper.PPI)),2) AS ScheduledCartsRequired 
,0 as UnscheduledCartsRequired 

Was This Post Helpful? 1
  • +
  • -

#13 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 11:45 AM

Awesome... Thanks for your help - my desk thanks you, as well... i'm sure it was getting upset at all the hammering I was doing to it... ;)

Oh, and for the record, I did enumerate all of my columns - out of laziness, i didn't do it here :)
Was This Post Helpful? 0
  • +
  • -

#14 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13758
  • View blog
  • Posts: 54,939
  • Joined: 12-June 08

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 11:56 AM

No problem.. out of curiosity how's your general level with SQL? Is this a new adventure of them chucking you into the tank of sea bass?
Was This Post Helpful? 0
  • +
  • -

#15 tbare  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 47
  • Joined: 29-July 11

Re: Need help with (in my mind) a complex query with subqueries

Posted 09 May 2012 - 12:04 PM

Haha.. no -- I know my way around. I've done a lot with JOINS, and gotten some pretty complex queries done for them in the past, but I just couldn't wrap my head around this one -- the reason being i've never done anything with UNION.

Another notch added to my belt of ever-growing SQL familiarity. :)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2