Q:

Display only one account with its response and date

I'm new to SQL and have limited hours in its use, so be kind. I've programmed before and write code in NPR but I have basic knowledge in SQL, and SQL can be a different kind of beast. I like to learn by doing and have been stumbling over this for a couple days now and its time I call in the big guns! I have read many posts in StackOverflow and really liked how your community works together to find solutions for people who cant (or wont) Hats off to all those who respond and assist others, well done. Anyway, I just cant think through the requirements i have to get the data I need. I have looked through numerous posts and read a bunch and some deal with sub-queries, others DISTINCT, and yuet others joins but I haven't seen one that can resolve the issue Im having. Everything works fine with the exception of the following:

I cant get the stupid column headers on the sub-queries to print.
I cant figure out the rule to show only 1 account with its response and date.
count the numerator of #2
The big one is #2

The rules:
 1. I can only have one account for the date range no matter how many show up. 
 2. I have to record the response of the visit, if there is a 1 response add it to a numerator and take that specific date. Varying dates with a response of "1" dont matter, just capture only one of them. So for example if there were 5 accounts with the same UnitNumber and 3 had a response of "1" and 2 had a response of "0" only grab one of the "1"'s with its date and increase the numerator; otherwise if they were all 0's display only one of the accounts but dont increase the numerator.
 3. I have to capture the date of the response.
So for example: The account below M000003206 should only show up once and the response value should be 1 with a date of 01/03/13 and the denominator should be 13.
*M000003206 1   1/03/2011 should show up in the data, and populate numerator
*M000003206 0   1/04/2011 should not show up in the data, and not be in the denominator or the numerator.
NOTES:
 - The "response" is a field that tracks a bunch of queries 6000 or so. The C.AD.DOCS section can have 5 answers which I dont care about I just want to see if they responded to any of them, if so throw in a 1; if no answer response is a 0.
 - I havent written the numerator part as I have no idea how to make the rule to limit the account to only show 1 visit 
 - Also I am getting a No column name listed and I've looked but I cant seem to find how to correct this in a sub-query
The numerator (once written) should be 7, for all the responses that are 1.
BTW: I have changes Unit Numbers and admit dates to protect confidential data.
Thank you for any replies!
OUTPUT-------------------------------------------------------------
Denominator 14
UnitNumber     no column name    no column name
M000001058 1     1/04/2011
M000004955 0     1/03/2011
M000006362 1     1/03/2011
M000006211 1     1/03/2011
M000004212 0     1/03/2011
M000009850 1     1/03/2011
M000003047 0     1/04/2011
*M000003206   1     1/03/2011*
*M000003206   0     1/04/2011*
M000002526 0     1/04/2011
M000000538 1     1/04/2011
M000003813 0     1/03/2011
M000004473 1     1/04/2011
M000004794 1       1/03/2011
        Should be   Should be 
        Response    AdmitDate

CODE-------------------------------------------------------------------
    use livedb
    DECLARE @StartDate DateTime,
    @EndDate DateTime

    SET @StartDate = '1/03/2011 00:00:00.000'
    SET @EndDate =   '1/05/2011 00:00:00.000'
    SELECT DISTINCT COUNT(UnitNumber) AS Denominator
    FROM AbstractData 
    WHERE (AbstractData.AdmitDateTime BETWEEN @StartDate and @EndDate) 
            and (AbstractData.BirthDateTime < '1946-09-29 00:00:00.000') 
            and (AbstractData.PtStatus IN ('IN','INO')) 
    SELECT DISTINCT AbstractData.UnitNumber AS UnitNumber,
    (SELECT MAX(CASE WHEN AbsQueriesMult.GroupResponse = 'C.AD.DOCS' THEN 1 ELSE 0 END) AS Response 
    FROM AbsQueriesMult WHERE AbstractData.AbstractID =  AbsQueriesMult.AbstractID),
    (SELECT CONVERT(VARCHAR(12),AbstractData.AdmitDateTime,101) AS AdmitDate)
    FROM AbstractData 
    WHERE (AbstractData.AdmitDateTime BETWEEN @StartDate and @EndDate) 
            and (AbstractData.BirthDateTime < '1946-09-29 00:00:00.000') 
            and (AbstractData.PtStatus IN ('IN','INO')) 
    ORDER BY UnitNumber

A:

I will tackle the easy problem first. The syntax to label a subquery is: 
select (subquery) as label1, (subquery) as label2

For your example, it's:
SELECT DISTINCT AbstractData.UnitNumber AS UnitNumber,
(SELECT MAX(CASE WHEN AbsQueriesMult.GroupResponse = 'C.AD.DOCS' THEN 1 ELSE 0 END) 
FROM AbsQueriesMult WHERE AbstractData.AbstractID =  AbsQueriesMult.AbstractID) AS Response,
CONVERT(VARCHAR(12),AbstractData.AdmitDateTime,101) AS AdmitDate
FROM AbstractData 
WHERE (AbstractData.AdmitDateTime BETWEEN @StartDate and @EndDate) 
        and (AbstractData.BirthDateTime < '1946-09-29 00:00:00.000') 
        and (AbstractData.PtStatus IN ('IN','INO')) 
ORDER BY UnitNumber

I also removed the SELECT from AdmitDate because it's not necessary. Just cleaning out some stuff from your query.
Now for the confusing part. 
Let me summaries what I think your problem is. You have a table/view/whatever AbstractData. AbstractData has a number of fields, but the most interesting ones to your question are AdmitDateTime, UnitNumber, and your computed field Response. You want to list all UnitNumbers. For each UnitNumber, you want the Response to be 1 if any of the rows have a 1, otherwise 0. From all these UnitNumbers, you want the total count to be Denominator, and number of 1's to be Numerator.
So I think it would be good to do this in steps. In the first step, we will decorate all your rows with that computed field Response and AdmitDate:
SELECT AbstractData.UnitNumber AS UnitNumber,
(SELECT MAX(CASE WHEN AbsQueriesMult.GroupResponse = 'C.AD.DOCS' THEN 1 ELSE 0 END) 
FROM AbsQueriesMult WHERE AbstractData.AbstractID =  AbsQueriesMult.AbstractID) AS Response,
SELECT CONVERT(VARCHAR(12),AbstractData.AdmitDateTime,101) AS AdmitDate
FROM AbstractData 
WHERE (AbstractData.AdmitDateTime BETWEEN @StartDate and @EndDate) 
        and (AbstractData.BirthDateTime < '1946-09-29 00:00:00.000') 
        and (AbstractData.PtStatus IN ('IN','INO')) 

This query gets all of the duplicates. Now we will remove the duplicates. 
To do that, we'll partition the rows by UnitNumber. Within each UnitNumber, we will order Response DESC. We only want the first response from each group. 
Unfortunately, this isn't an easy operation. I'm hoping you're using SQL Server since your query looks like TSQL. I'm also hoping that you're using 2005+ for ROW_NUMBER. We will first label each row with its rank within the partition. Then we will select only the first response.
SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY UnitNumber ORDER BY Response DESC) as r
    FROM (
        SELECT AbstractData.UnitNumber AS UnitNumber,
        (SELECT MAX(CASE WHEN AbsQueriesMult.GroupResponse = 'C.AD.DOCS' THEN 1 ELSE 0 END) 
        FROM AbsQueriesMult WHERE AbstractData.AbstractID =  AbsQueriesMult.AbstractID) AS Response,
        SELECT CONVERT(VARCHAR(12),AbstractData.AdmitDateTime,101) AS AdmitDate
        FROM AbstractData 
        WHERE (AbstractData.AdmitDateTime BETWEEN @StartDate and @EndDate) 
                and (AbstractData.BirthDateTime < '1946-09-29 00:00:00.000') 
                and (AbstractData.PtStatus IN ('IN','INO'))
    ) Decorated
) Labeled
WHERE r = 1

Now that you have this result, there are a few ways you can use this to get the denominator and numerator. You can either store this in a temporary table or table variable, or you can copy and paste the query twice, once to list all the rows and once to do the aggregate (numerator and denominator). You can also use a CTE to alias the table, so that you decorate all your returned rows with the numerator and denominator. This is a lot of redundant information, but may mean faster query times. You'll have to try it out. Good luck.

