Combining Queries Into A Single Script

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

General SQL Server Forums

New to SQL Server Programming

Combining Queries Into A Single Script

Author

Topic

WilderBog
Starting Member

4 Posts

Posted - 2011-12-13 : 08:26:47

Hi There,

I have been searching for ages on a method to combine information which is all stored in a singular table using numerous queries and outputting this into a single table.

The table used is labelled 'Request' and I under this table exists alot of information pertaining to a Helpdesk software which I would like to pull some statistics from.

The end result I would like to get is have the list of specialists (column - SpecialistID) and beside this the statistic data for lets say 'OpenCalls', 'CallsClosedToday', 'CallsClosedThisWeek', 'CallsClosedLastWeek', 'CallsOver10DaysOld', 'NoResponseIn2Days'.

I have seperate queries for each of the above but need to knit this all together, i wont bombard with every query, but some example of what I am using...

/*-- Query 1 - All Open Calls --*/
SELECT SpecialistID,COUNT(*) AS NumberOfOpenCalls FROM Request
WHERE StatusID IN ('1','13','9','6','12','11','5','4')
AND GroupID LIKE '1'
GROUP BY SpecialistID

/*-- Query 2 - Calls Closed Today --*/
SELECT SpecialistID,COUNT(1) AS ClosedToday FROM Request
WHERE ResolutionActual
>= (DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())))
AND GroupID LIKE '1'
GROUP BY SpecialistID

/*-- Query 3 - Calls Closed Last Week --*/
DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
SET @TodayDayOfWeek = datepart(dw, GetDate())
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())
SELECT SpecialistID,COUNT(*) AS TotalLastWk FROM Request
WHERE ResolutionActual BETWEEN
CONVERT(VARCHAR, @StartOfPrevWeek,7)
AND
CONVERT(VARCHAR, @EndOfPrevWeek+1,7)
AND StatusID LIKE '2'
AND GroupID LIKE '1'
GROUP BY SpecialistID

So lets take these 3 queries as a basis and each outputs a COUNT number Against the name of each specialist - can I marry these queries up to become one glorious output?

Many Thanks for any help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-13 : 08:35:24

You can, by moving the where clause into a CASE expression within the count function - for example like this (I am showing only the first 2)


SELECT
   SpecialistID,
   COUNT
   (
      CASE
         WHEN
            StatusID IN ('1', '13', '9', '6', '12', '11', '5', '4')
            AND GroupID LIKE '1'
         THEN 1
      END
   ) AS NumberOfOpenCalls,
   COUNT
   (
      CASE
         WHEN
            ResolutionActual
            >= (DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())))
            AND GroupID LIKE '1'
         THEN 1 
      END
   ) AS ClosedToday
FROM
   Request
GROUP BY
   SpecialistID

WilderBog
Starting Member

4 Posts

Posted - 2011-12-13 : 14:55:02

Hi,

Many Thanks for the great solution, the only problem I can see after using this method is that a load of results come up that I dont want.

the SpecialistID (names within) include ppl who are not on the 'tech' team which I have tried to exclude by only generating members who have a GroupID LIKE '1' which is the tech guys - I am seeing all users from SpecialistID regardless of my attempts to filter them out.

Is there a query command that will allow me to strip out the unwanted results - i don't mind manually specifying the ones to remove as its only 5 SpecialistID's

Best Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 00:11:29

if you're looking for patterns starting with 1 it should be

AND GroupID LIKE '1%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WilderBog
Starting Member

4 Posts

Posted - 2011-12-16 : 08:51:37

Thanks for the information, that did not appear to do the trick though.

I'll explain in better detail,

When I run the code provided by our friend 'sunitabeck' I get the following (example)

Lets say that from the above, Jill Bloggs, Jeremy Bloggs and Jackie Bloggs are not wanted on the output because they are not in the tech team, how do i 'drop' them from the results? in theory my queries should filter this out when i declare

AND GroupID LIKE '1'

As GroupID '1' is the tech team

Many Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 09:36:34

add that condition on WHERE

WHERE GroupID LIKE '1'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WilderBog
Starting Member

4 Posts

Posted - 2011-12-16 : 15:02:46

All looks to be good :)

Thanks to you both for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 01:44:02

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources