Combine three queries into one for a single report 1

[360degreehosting]

Hello,

We're using ASP to show reports to our affiliates. Right now, we have a report page that has 3 tables on it (there is one html table per query). We would like to have a single query that groups rows by RegTime and shows the value for each of these three queries on the same row.

For example:

Date Unique Invalid Net
07/17/07 130 12 150
...


I hope I've made my question clear enough. Below are the actual queries that we're currently using.

I appreciate any help offered.

Warmest Regards,
Steve

SELECT 'Unique' = Count(*), 'UniqueDate' = RegTime FROM Master WHERE Affiliate = 'sb1'AND UniquePhone = 'y' AND onnetmatch = 'y' GROUP BY RegTime Order by RegTime DESC


SELECT 'Invalid' = Count(*), 'InvalidDate' = RegTime FROM Master WHERE Affiliate = 'sb1' AND UniquePhone = 'y' and onnetmatch = 'y' AND status = '8' GROUP BY RegTime Order by RegTime DESC


SELECT 'Net' = Count(*), 'NetDate' = RegTime FROM Master WHERE Affiliate = 'sb1' AND UniquePhone = 'y' and onnetmatch = 'y' AND status != '8' GROUP BY RegTime Order by RegTime DESC

 

[r937]

SELECT RegTime                  as Date 
     , COUNT(*)                 as Unique
     , COUNT(CASE WHEN status = '8' 
                  THEN 1 
                  ELSE NULL 
                END)            as Invalid
     , COUNT(CASE WHEN status <> '8' 
                  THEN 1 
                  ELSE NULL 
                END)            as Net
  FROM Master 
 WHERE Affiliate = 'sb1'
   AND UniquePhone = 'y' 
   AND onnetmatch = 'y' 
GROUP 
    BY RegTime 
ORDER 
    BY RegTime DESC

r937.com | rudy.ca

 

[bang99]

I think that is going to work great!!!!!!!

I got an error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Unique'.

I think that was my mistake in giving the word 'Unique' to you in the first place. I changed it to 'Unique1' and it's working great. It must be a reserved word.

Thank you very much for your help.

Regards.
Steve