Understanding Case, Count, and Group By

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 2005 Forums

Transact-SQL (2005)

Understanding Case, Count, and Group By

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 09:35:10

I have the following I will be using for a subquery test:

SET @STARTDATE = Dateadd(d,-1,Convert(DATETIME,Convert(VARCHAR(11),Getdate(),109),109))
SET @ENDDATE = @STARTDATE + 1
Select 
	Resource_ID, 
	count(Date_Provided) CDateProvided, 
	--date_provided,
	case 
		--when count(date_provided) > 0 then 'Yes'
		when count(date_provided between @startdate and @enddate) > 0 then 'Yes'
		--when date_provided between @startdate and @enddate then 'Yes'
		else 'No'
	end scheduled
from sys_sked_conflict 
--where date_provided between @startdate and @enddate
group by resource_id
order by resource_id

I left the commented parts in to give more clarity on what I am trying to do. When I use it as is, I get a syntax error. When I use the red line instead of the green, I get all 'Yes's in the 3rd column because it is only grabbing the ones within the criteria. I want to put a 'No' in the column if the count of Date_Provided in within the date range is 0. If there is even 1 in the daterange it should be 'Yes'. If there is none, then it should be 'No'. Please help me get this.

Duane

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-11 : 09:49:58

May be this??

SELECT resource_id, 
       CASE 
         WHEN cdateprovided > 0 
         THEN 'Yes' 
         ELSE 'No' 
       END AS [scheduled] 
FROM   (SELECT   resource_id, 
                 count(date_provided) AS [CDateProvided], 
        FROM     sys_sked_conflict 
        WHERE    date_provided BETWEEN @startdate AND @enddate 
        GROUP BY resource_id 
        ORDER BY resource_id) t

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-11 : 10:43:49

[code]SET @StartDate= DATEADD(d, -1,DATEDIFF(d, 0, GETDATE()))
SET @EndDate = @StartDate + 1

SELECT
Resource_ID
,COUNT(date_provided) AS CDateProvided
,COUNT(CASE WHEN date_provided BETWEEN @StartDate AND @EndDate THEN 1 END)
AS CDateProvidedWithinRange
,CASE COUNT(CASE WHEN date_provided BETWEEN @StartDate AND @EndDate THEN 1 END)
WHEN 0 THEN 'No'
ELSE 'Yes'
END AS Scheduled
FROM sys_sked_conflict
GROUP BY resource_id
ORDER BY resource_id[/code]

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 16:20:03

Thank you both for trying to help me. Ifor's query worked great without a hitch, first time, no modifications. Just for educational purposes, I will point out that vijayisonly's query had a couple minor (punctuation) errors in it and it ran, too, but gave me all 'Yes' results, 490 results out of a possible 640. I got them all with Ifor's query, both 'Yes' and 'Nos'.

Duane

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-11 : 16:26:59

Oops..Oh yeah...since i included this line

WHERE    date_provided BETWEEN @startdate AND @enddate

to get the derived table it would have got only the satisfied records and all of them would have 'Yes'...

Never thought of that when I gave the query...Should have tried with sample data...

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 16:50:59

Actually, I tried vijayisonly's query without the the WHERE statement and it ran (I had to move the ORDER BY statement to the end). It gave me all 641 results, but they were all 'Yes'. In reality, about 1/3 of them are 'No'. Maybe it can be refined as an alternate way to code this. Thanks again.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-11 : 17:37:08

Could somebody be so kind as to show me how to put this in a subquery where I am trying to get the Scheduled field where the Resource_ID in the sub-query is equal to the Resource_ID in the main query?
SET @StartDate= DATEADD(d, -1,DATEDIFF(d, 0, GETDATE()))
SET @EndDate = @StartDate + 1

SELECT
Resource_ID
,COUNT(date_provided) AS CDateProvided
,COUNT(CASE WHEN date_provided BETWEEN @StartDate AND @EndDate THEN 1 END)
AS CDateProvidedWithinRange
,CASE COUNT(CASE WHEN date_provided BETWEEN @StartDate AND @EndDate THEN 1 END)
WHEN 0 THEN 'No'
ELSE 'Yes'
END AS Scheduled
FROM sys_sked_conflict
GROUP BY resource_id
ORDER BY resource_id

This is Ifor's query. I know that the variables exist at the top of the outer sql. I know that I don't need the columns cdateprovided and cdateprovidedwithinrange. Beyond that, I am not sure how to do put it in place. Thanks again.

Duane

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-12 : 04:47:44

Use a derived table (inline view in Oracle speak) or a CTE:

SELECT Y.*, D.Scheduled
FROM YourTables Y
    JOIN
    (
        SELECT
            Resource_ID
            ,CASE COUNT(CASE WHEN date_provided BETWEEN @StartDate AND @EndDate THEN 1 END)
                WHEN 0 THEN 'No'
                ELSE 'Yes'
            END AS Scheduled
        FROM sys_sked_conflict 
        GROUP BY resource_id
    ) D
        ON Y.Resource_ID = D.Resource_ID

;WITH ResourceScheduled (Resource_ID, Scheduled)
AS
(
    SELECT
        Resource_ID
        ,CASE COUNT(CASE WHEN date_provided BETWEEN @StartDate AND @EndDate THEN 1 END)
            WHEN 0 THEN 'No'
            ELSE 'Yes'
        END
    FROM sys_sked_conflict
)
SELECT Y.*, R.Scheduled
FROM YourTables Y
    JOIN ResourceScheduled R
        ON Y.Resource_ID = R.Resource_ID

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-12 : 14:53:13

Thank you. Are these 2 different approaches divided by the semicolon?

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources