Groups and joins - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Groups and joins

Author

Topic

hugh_mungo
Starting Member

10 Posts

Posted - 2009-03-24 : 12:22:46

Hi I have 2 tables as below

[Table] newactriskt
idnewactrisk, reference, dept, assess, reassess, SITE_CODE

[Table] actiont
idaction, action, dueon, parentid, tablename, status, SITE_CODE

The tables are related via idnewactrisk --> parentid

I need group funtions for the main record to count the records by dept as per:

All = Count of all records by dept

New = case when (assess > DATEADD(YY,-1,GetDate())) then 1 else 0 end,

Updated = case when (assess > DATEADD(yy,-2,GetDate()) AND (reassess > DATEADD(mm, 3, GetDate()))) then 1 else 0 end,

DueReview = case when (assess > DATEADD(yy,-2,GetDate()) AND (reassess < DATEADD(mm,3,GetDate()))) then 1 else 0 end,

Overdue = case when (assess < DATEADD(yy,-2,GetDate()) AND (reassess < GetDate())) then 1 else 0 end

Additionally I require related grouped fields from the actiont table listed per the logic below.

Complete = case when [status] = 'Completed' then 1 else 0 end,

InProgress = case when [status]<> 'Completed' AND dueon > GetDate() then 1 else 0 end,

Overdue= case when [status] <> 'Completed' AND dueon < GetDate() then 1 else 0 end

The problem I have is getting the accociated actions for each dept to group properly.

I have sql sripts to create the tables and insert test data if anyone thinks they can help. I also have the scripts to calculate the derived status fields for each table.

Can anyone help.

foxprorawks
Starting Member

17 Posts

Posted - 2009-03-25 : 04:44:52

Had a go at this, but can't see a way of doing it.

Get the two separate parts is fine, but doing both together is quite problematical.

Hopefully someone else will come along to help out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 14:19:05

seems like this


SELECT n.dept,
All = COUNT(1),
New = SUM(case when assess > DATEADD(YY,-1,GetDate()) then 1 else 0 end),
Updated = SUM(case when assess > DATEADD(yy,-2,GetDate()) AND reassess > DATEADD(mm, 3, GetDate()) then 1 else 0 end),
DueReview = SUM(case when assess > DATEADD(yy,-2,GetDate()) AND reassess < DATEADD(mm,3,GetDate()) then 1 else 0 end),
Overdue = SUM(case when assess < DATEADD(yy,-2,GetDate()) AND reassess < GetDate()) then 1 else 0 end),
Complete =SUM( case when [status] = 'Completed' then 1 else 0 end),
InProgress = SUM(case when [status]<> 'Completed' AND dueon > GetDate() then 1 else 0 end),
Overdue= SUM(case when [status] <> 'Completed' AND dueon < GetDate() then 1 else 0 end)
FROM actiont a
JOIN newactriskt n
ON n.idnewactrisk = a.parentid
GROUP BY n.dept

hugh_mungo
Starting Member

10 Posts

Posted - 2009-03-26 : 05:33:51

Thanks visakh16

Your post was 95% there and answered the main problem we were having, below is a slightly edited version which now works perfectly.

SELECT n.dept,
COUNT(distinct n.idnewactrisk) as 'All',
New = SUM(distinct case when assess > DATEADD(YY,-1,GetDate()) then 1 else 0 end),
Updated = SUM(distinct case when assess > DATEADD(yy,-2,GetDate()) AND reassess > DATEADD(mm, 3, GetDate()) then 1 else 0 end),
DueReview = SUM(distinct case when assess > DATEADD(yy,-2,GetDate()) AND reassess < DATEADD(mm,3,GetDate()) then 1 else 0 end),
ReviewOverdue = SUM(distinct case when assess < DATEADD(yy,-2,GetDate()) AND reassess < GetDate() then 1 else 0 end ),
ActionComplete =SUM( case when [status] = 'Completed' and tablename = 'newactrisk' then 1 else 0 end),
ActionInProgress = SUM(case when [status]<> 'Completed' AND dueon > GetDate() and tablename = 'newactrisk' then 1 else 0 end),
ActionOverdue= SUM(case when [status] <> 'Completed' AND dueon < GetDate() and tablename = 'newactrisk' then 1 else 0 end)
FROM action a
RIGHT OUTER JOIN newactrisk n
ON n.idnewactrisk = a.parentid

GROUP BY n.dept

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:27:18

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources