Nested and aggregate queries - SQL Server Forums

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

Nested and aggregate queries

Author

Topic

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-14 : 12:49:58

I have a the following fields in a table TblToDo

[ToDoID] [int] IDENTITY(1,1)
[ToDoReferralID] [int]
[ToDoDate] [datetime]
[ToDoCompleteBy] [datetime]
[ToDoCompleted] [datetime]

and want to produce a query to show the with following

All Grouped by [ToDoReferralID]
Count [ToDoID] AS [ToDoToTal]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AS [ToDoIncomplete]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] <= GETDATE() AS [IncompleteUTarget]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] > GETDATE()AS [IncompleteOTarget]

I can manage a single aggregate but have no idea where to being trying to incorporate all into one query.

Any help would be much appreciated

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-14 : 13:05:19

Can you please provide sample data and expected output in a consumable format? The links below will help you prepare your data so we can help you better.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 13:17:45

this?


SELECT [ToDoReferralID],
Count ([ToDoID]) AS [ToDoToTal]
Count (CASE WHEN [ToDoCompleted] IS NULL THEN [ToDoID] END) AS [ToDoIncomplete]
Count (CASE WHEN [ToDoCompleted] IS NULL AND [ToDoCompleteBy] <= GETDATE() THEN [ToDoID] END) AS [IncompleteUTarget]
Count (CASE WHEN [ToDoCompleted] IS NULL AND [ToDoCompleteBy] > GETDATE() THEN [ToDoID] END) AS [IncompleteOTarget]
FROM TblToDo
GROUP BY [ToDoReferralID]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-14 : 13:29:27

Thanks, not sure how to post consumable data (the first link requires membership which I don't really want to join)

Anyway, I seem to have missed some of the question in my original post (my copy\past fault) so here's what I need again with expected results

All Grouped by [ToDoReferralID]
Count [ToDoID] AS [ToDoToTal]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AS [ToDoIncomplete]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] <= GETDATE() AS [IncompleteUTarget]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] > GETDATE()AS [IncompleteOTarget]

Count [ToDoID]WHERE [ToDoCompleted] IS NOT NULL AS [ToDoCompleted]
Count [ToDoID]WHERE [ToDoCompleted] <= [ToDoCompleteBy] AS [CompletedUTarget]
Count [ToDoID]WHERE [ToDoCompleted] >[ToDoCompleteBy] AS [CompletedOTarget]

EXPECTED RESULTS (but I suspect the formatting wont hold in the post)
ToDoToTal ToDoIncomplete IncompleteUTarget IncompleteOTarget ToDoCompleted CompletedUTarget CompletedOTarget
10 5 3 2 5 5 0
5 2 0 2 3 2 1
20 3 2 1 17 7 10

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-14 : 13:37:29

Many thanks visakh16
That seems to have done the trick (with the addition of a few commas) - I'll compare data to make sure the results are correct, but on first glance it looks fine. Great - I've been messing about with that for ages

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 13:41:41

quote:
Originally posted by MrSmallTime

Many thanks visakh16
That seems to have done the trick (with the addition of a few commas) - I'll compare data to make sure the results are correct, but on first glance it looks fine. Great - I've been messing about with that for ages

No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-14 : 14:05:19

Just checked the data after incorporating the additional fields and it looks absolutely perfect - I owe you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources