joining three queries

I am using sql server 2000 so cannot use CTE's


Declare @reportdate as datetime
set @reportdate='08/26/2013'
1st query
SELECT      SUM(NENTEREDACD) AS calls,DINTERVALSTART
FROM         dbo.QUEUESTATS
WHERE     (DINTERVALSTART > @reportdate + ' 00:00:00.000') AND (DINTERVALSTART < DATEADD(d, 1, @reportdate) + ' 00:00:00.000')
                   
GROUP BY DINTERVALSTART
ORDER BY DINTERVALSTART

2nd query
select count(cname) as AvaiAgents,dintervalstart as startdatetime from
(
SELECT  dintervalstart,cname
  FROM [dbo].[AGENTSTATS]
 group by dintervalstart,cname
having (DINTERVALSTART > @reportdate + ' 00:00:00.000') AND (DINTERVALSTART < DATEADD(d, 1, @reportdate) + ' 00:00:00.000')
  ) T
group by T.dintervalstart

3nd query
select count(cname) as ActiveAgents,dintervalstart as startdatetime from
(
SELECT  dintervalstart,cname
  FROM [dbo].[AGENTSTATS1]
 group by dintervalstart,cname
having (DINTERVALSTART > @reportdate + ' 00:00:00.000') AND (DINTERVALSTART < DATEADD(d, 1, @reportdate) + ' 00:00:00.000')
  ) T
group by T.dintervalstart

I want to only pull only the records from first query so it should be something like
so I need to get three columns from three different queries. The common thing is timestamp

I inetval     Calls  Avaiagents     Active agents
08/26/2012    20     22              23
Declare @reportdate as datetime
set @reportdate='08/26/2013'
1st query
SELECT      SUM(NENTEREDACD) AS calls,DINTERVALSTART
FROM         dbo.QUEUESTATS
WHERE     (DINTERVALSTART > @reportdate + ' 00:00:00.000') AND (DINTERVALSTART < DATEADD(d, 1, @reportdate) + ' 00:00:00.000')
                    
GROUP BY DINTERVALSTART
ORDER BY DINTERVALSTART

2nd query
select count(cname) as AvaiAgents,dintervalstart as startdatetime from 
(
SELECT  dintervalstart,cname
  FROM [dbo].[AGENTSTATS] 
 group by dintervalstart,cname 
having (DINTERVALSTART > @reportdate + ' 00:00:00.000') AND (DINTERVALSTART < DATEADD(d, 1, @reportdate) + ' 00:00:00.000')
  ) T
group by T.dintervalstart

3nd query
select count(cname) as ActiveAgents,dintervalstart as startdatetime from 
(
SELECT  dintervalstart,cname
  FROM [dbo].[AGENTSTATS1] 
 group by dintervalstart,cname 
having (DINTERVALSTART > @reportdate + ' 00:00:00.000') AND (DINTERVALSTART < DATEADD(d, 1, @reportdate) + ' 00:00:00.000')
  ) T
group by T.dintervalstart

I want to only pull the the data for records from first query so it should be something like


I inetval     Calls  Avaiagents     Active agents
08/26/2012    20     22              23

Open in new window

Angela4evaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MangusDatabase AdministratorCommented:
With no joining column I'd create a temp table with the format of your desired output.  Then I'd insert a row from your first query.  Then I'd do the second query and use the returned value to update the row in your temp table.  Do the same thing with the third query.

It's a bit kludgy but it will work.
0
PortletPaulfreelancerCommented:
You seem to be selecting 3 values fora single day (i.e. a single row)
try this:
DECLARE @reportdate AS DATETIME

SET @reportdate = '20130826' -- '08/26/2013'

SELECT
       @reportdate  AS startdatetime
     , (
        /* 1st query */
        SELECT
               SUM(NENTEREDACD) AS calls
        FROM dbo.QUEUESTATS
        WHERE DINTERVALSTART >= @reportdate
             AND DINTERVALSTART < DATEADD(day, 1, @reportdate)
       ) AS calls
     , (
        /* 2nd query */
        SELECT
               count(distinct cname) as Avai_Agents
        FROM [dbo].[AGENTSTATS]
        WHERE DINTERVALSTART >= @reportdate
          AND DINTERVALSTART < DATEADD(day, 1, @reportdate)
        ) AS Avai_Agents
     , (
        /* 3rd query */
        SELECT
               count(distinct cname) AS Active_Agents
        FROM [dbo].[AGENTSTATS1]
        WHERE DINTERVALSTART >= @reportdate
             AND DINTERVALSTART < DATEADD(day, 1, @reportdate)
        ) AS Active_Agents
; 

Open in new window

Observations:
1. YYYYMMDD or YYYY-MM-DD is a much better/safer/reliable/international date sequence
     (only a small % of people in the world use mm/dd/yyyy by the way)
    -- it's best practice to use YYYYMMDD really

2. I'm assuming DINTERVALSTART is a datetime field
2a. then you do not have to do this " + ' 00:00:00.000' "

2. you should select the date range using a combination of >= with <

for more on 1,2,3 topics please see:

"Bad habits to kick : mis-handling date / range queries"
"The ultimate guide to the datetime datatypes"
"Beware of Between"
"What do BETWEEN and the devil have in common?"

4. You can use "distinct" inside the count() to avoid the nested group by in queries 2 & 3

5. not much point in ordering a single row :)
0
PortletPaulfreelancerCommented:
I did this along the way, so might as well provide it.
IF, you are after a larger range of dates you could try this:
DECLARE @date_from AS DATETIME
DECLARE @date_to   AS DATETIME

SET @date_from = '20130801' -- '08/01/2013'
SET @date_to   = '20130831'
SELECT
       DINTERVALSTART
     , SUM(calls)         AS calls
     , SUM(Avai_Agents)   AS Avai_Agents
     , SUM(Active_Agents) AS Active_Agents
FROM (
        --1st query
        SELECT
               DINTERVALSTART
             , SUM(NENTEREDACD) AS calls
             , NULL             AS Avai_Agents
             , NULL             AS Active_Agents
        FROM dbo.QUEUESTATS
        WHERE DINTERVALSTART >= @date_from
             AND DINTERVALSTART < DATEADD(d, 1, @date_to)
        GROUP BY DINTERVALSTART

        UNION ALL
        --2nd query
        SELECT
               DINTERVALSTART
             , NULL                  AS calls
             , count(distinct cname) as Avai_Agents
             , NULL                  AS Active_Agents
        FROM [dbo].[AGENTSTATS]
        WHERE DINTERVALSTART >= @date_from
          AND DINTERVALSTART < DATEADD(d, 1, @date_to)
        GROUP BY dintervalstart

        UNION ALL
        --3rd query
        SELECT
               DINTERVALSTART
             , NULL                  AS calls
             , NULL                  AS Avai_Agents
             , count(distinct cname) AS Active_Agents
        FROM [dbo].[AGENTSTATS1]
        WHERE DINTERVALSTART >= @date_from
             AND DINTERVALSTART < DATEADD(d, 1, @date_to)
        GROUP BY dintervalstart
    ) AS U
GROUP BY
       DINTERVALSTART
ORDER BY
       DINTERVALSTART
;

Open in new window

note you could just set @date_to "the next day" e.g.  '20130901' and then get rid of the dateadd(day,1,@date_to) - but I leave that choice to you.

{+edit a misspelling}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angela4evaAuthor Commented:
exactly what I needed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.