Help requested to combine 2 queries into 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help requested to combine 2 queries into 1

Author

Topic

pillbug22
Starting Member

6 Posts

Posted - 2006-06-19 : 11:18:51

What's the easiest way to combine the following 2 queries into 1 query that returns 3 columns (Name, Total Minutes, MCDH Minutes) grouped by Name? I know I could do it the long way w/ temp tables or such, but I'm sure somone has a more elegant solution! :-)

SELECT Name, Sum(CallMinutes) AS 'Total Minutes'
FROM tbl_SprintCellPhoneMonthlyInvoice
GROUP BY Name
ORDER BY 'Total Minutes' DESC



SELECT Name, Sum(CallMinutes) AS 'MCDH Minutes'
FROM tbl_SprintCellPhoneMonthlyInvoice
WHERE (CallNumber IN
     (
     SELECT DISTINCT(PCSNumber)
     FROM tbl_SprintCellPhoneMonthlyInvoice
     ))
     OR (CallNumber LIKE '972566%')
GROUP BY Name
ORDER BY 'MCDH Minutes' DESC

Basically, I'm trying to convert the second query into a third column of the first query...

Thanks in advance-

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-19 : 11:23:45

Do you mean that you want the second query into a third column of the first query but you only want the where condition applying to the column from the second query?

If not, it'd just be -

SELECT Name, Sum(CallMinutes) AS 'Total Minutes', Sum(CallMinutes) AS 'MCDH Minutes'
FROM tbl_SprintCellPhoneMonthlyInvoice
WHERE (CallNumber IN
(
SELECT DISTINCT(PCSNumber)
FROM tbl_SprintCellPhoneMonthlyInvoice
))
OR (CallNumber LIKE '972566%')
GROUP BY Name
ORDER BY 'MCDH Minutes' DESC

Otherwise you might be able to do it

SELECT Name, Sum(CallMinutes) AS 'Total Minutes',
SUM (CASE WHEN (CallNumber IN
     (SELECT DISTINCT(PCSNumber) FROM tbl_SprintCellPhoneMonthlyInvoice)
     OR CallNumber LIKE '972566%') THEN CallMinutes ELSE 0 END) AS 'MCDH Minutes'
FROM tbl_SprintCellPhoneMonthlyInvoice
GROUP BY Name
ORDER BY 'Total Minutes' DESC

But it might be very slow.

-------
Moo. :)

pillbug22
Starting Member

6 Posts

Posted - 2006-06-19 : 11:29:59

Your second query is essentially what I'm aiming for

SELECT Name, Sum(CallMinutes) AS 'Total Minutes',
SUM (CASE WHEN (CallNumber IN
     (SELECT DISTINCT(PCSNumber) FROM tbl_SprintCellPhoneMonthlyInvoice)
     OR CallNumber LIKE '972566%') THEN CallMinutes ELSE 0 END) AS 'MCDH Minutes'
FROM tbl_SprintCellPhoneMonthlyInvoice
GROUP BY Name
ORDER BY 'Total Minutes' DESC

except it doesn't like having the subquery within an aggregate (the highlighted part). Any ideas to get around that?

pillbug22
Starting Member

6 Posts

Posted - 2006-06-19 : 13:26:40

Here's what I came up w/ using a temporary table (still only take 3 seconds...not too bad)

DROP TABLE #tbl_MCDHMinutes

SELECT Name, Sum(CallMinutes) AS 'MCDHMinutes'
INTO #tbl_MCDHMinutes
FROM tbl_SprintCellPhoneMonthlyInvoice
WHERE (CallNumber IN
	(
	SELECT DISTINCT(PCSNumber)
	FROM tbl_SprintCellPhoneMonthlyInvoice
	))
	OR (CallNumber LIKE '972566%')
GROUP BY Name

SELECT o.Name, Sum(o.CallMinutes) AS 'TotalMinutes', ISNULL(t.MCDHMinutes, 0)
FROM tbl_SprintCellPhoneMonthlyInvoice AS o
LEFT JOIN #tbl_MCDHMinutes AS t
ON t.Name = o.Name
GROUP BY o.Name, t.MCDHMinutes
ORDER BY 'TotalMinutes' DESC, t.MCDHMinutes DESC

DROP TABLE #tbl_MCDHMinutes

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-19 : 13:31:34

pillbug22 - It would be roughly 10 times easier to help (for me at least) if you could provide some sample data and the expected output for that sample data. Any chance?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-19 : 13:43:24

What about:

SELECT Name
      ,SUM(CallMinutes) AS 'Total Minutes'
      ,SUM(CASE WHEN MCDH = 1 THEN CallMinutes ELSE 0 END) AS 'MCDH Minutes'
FROM (
   SELECT Name,
          CallMinutes,  
          CASE WHEN EXISTS (SELECT * FROM tbl_SprintCellPhoneMonthlyInvoice WHERE PCSNumber = o.CallNumber)
                    OR CallNumber LIKE '972566%' THEN 1 ELSE 0 END MCDH
   FROM tbl_SprintCellPhoneMonthlyInvoice o) AS dt
GROUP BY Name
ORDER BY 'Total Minutes' DESC

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-19 : 14:13:13

We need to know about how the PCSNumber is populated and the assumptions behind it. I think, this is what Ryan is asking. For instance, if we there is an business assumption that the CallNumber is the same as PCSNumber on that record, (that means, the PCSNumber field is being used as an indicator), then, we may develop something more efficient query.

Otherwise, we may come up with different methods of how to write it but SQL will have the same execution plan. what's your main objective, just to have one query? or to improve performance? Let us know more so we can be of more help to you :).

May the Almighty God bless us all!

pillbug22
Starting Member

6 Posts

Posted - 2006-06-19 : 14:31:13

Business situation:
After we started rolling out cell phones (Sprint) to various users, it easily got out of hand. Now our finance dept. is wanting each person with an issued phone to go to a web page and check each number as "for work" or "private".

We get a .cvs download from Sprint (our monthly call log/invoice), which I have a DTS package to import into SQL.

Before we push out the web page, we are trying to come up with any basic summaries and pull out any info we can validate. This includes trying to do as much calculating as possible up front so we don't overload the user with a page full of calls. For example, the 972566 is the beginning of land lines at our facility, so we want to pull those out and not make the user validate them. The part with the


(CallNumber IN
	(
	SELECT DISTINCT(PCSNumber)
	FROM tbl_SprintCellPhoneMonthlyInvoice
	))

is checking to see if the number called is also one of our Sprint phones - if it is, it's a free call, so we really don't care about it (and assume it was business related).

Let me grab a few lines of the imported data and a few lines of the results I am looking for so you can see the beginning and end...

pillbug22
Starting Member

6 Posts

Posted - 2006-06-19 : 14:44:42

Relevant fields of the raw SQL table data:


PCSNumber    Name    CallNumber    CallMinutes
5551234567   User 1  5551231234    6
5551231234   User 2  9725669876    2
5551231000   User 3  5552229638    9
5551235555   User 4  9725664444    8
5551234567   User 1  5551234567    2
5551231234   User 2  5559991234    6
5551231000   User 3  5551231000    4
5551235555   User 4  9725667777    4
5551234567   User 1  5551231234    10
5551231234   User 2  9725664567    21

Where PCSNumber is the accountnumber (10 digit phone number, tied to the Name), CallNumber is the number on the other end of the call, and CallMinutes is the time spent on the call.

Results I get with my current temporary table-style query:


Name      TotalMinutes   MCDHMinutes
USER 1	  2919	         1816
USER 2	  2540	         1612
USER 3	  2360	         1280
USER 4	  2322	         1437
USER 5	  2084	         1223
USER 6	  2005	         1515
USER 7	  1779	         1059
USER 8	  1626	         1099
USER 9	  1617	         948
USER 10	  1580	         983

I'm not positive the numbers given in the first table will produce great result since I made them up (didn't want to sort through 32,000+ rows to find good records :-) ), but the second set is the top 10 results from my query.

I guess my biggest concern is query speed, since there's 32,000+ rows just in this month's list, and I'm assuming we'll be appending each month's data for historical purposes.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-19 : 14:51:04

Did you try mine?

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

pillbug22
Starting Member

6 Posts

Posted - 2006-06-19 : 15:08:35

Yes, it returns the same results as the temp-table query - it reduces the elapsed time from 3 to 2 seconds - we'll see how that translates out afer a few more months I guess.

Here's what I have once I put in some new custimizations:


SELECT Name, SUM(CallMinutes) AS 'Total Minutes', 
	SUM(CASE WHEN MCDH = 1 THEN CallMinutes ELSE 0 END) AS 'MCDH Minutes'
FROM (
   SELECT Name, RecordSubType, CallMinutes, CASE WHEN
		EXISTS (SELECT * FROM tbl_SprintCellPhoneMonthlyInvoice WHERE PCSNumber = o.CallNumber)
        	OR (CallNumber LIKE '972566%')
		OR
		(CallNumber IN
			(
			SELECT DISTINCT(ExcludedNumber)
			FROM tbl_SprintCellPhoneExclusionList
			)
		)
		AND (RecordSubType = 'V')
	THEN 1
	ELSE 0
	END MCDH
   FROM tbl_SprintCellPhoneMonthlyInvoice o
) AS dt
WHERE (dt.RecordSubType = 'V')
GROUP BY Name
ORDER BY 'Total Minutes' DESC, 'MCDH Minutes' DESC

which appears to be working just like we want.

Thanks for all the feedback and help!

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-19 : 15:21:10

Dont use:

(CallNumber IN (CallNumber IN (SELECT DISTINCT(ExcludedNumber) FROM tbl_SprintCellPhoneExclusionList))

but this instead (although i doubt there will be much to gain unless it is a huge list)

EXISTS (SELECT * FROM tbl_SprintCellPhoneExclusionList WHERE ExcludedNumber = o.CallNumber)

and there should be no reason for the outer WHERE (RecordSubType isnt in the derived coloumn list anyway, but that must be a typo)

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources