Date variance as a column - 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

Date variance as a column

Author

Topic

ttruheyo
Starting Member

9 Posts

Posted - 2011-09-26 : 14:53:52

I have a column in one table that is a date field. This date field is the key to all the data I need to pull for instance I need to the count of column a when the datefield is Getdate()+5, 10, 30, 45 etc.. Right now I have the same statement with the where clause changed each time. Using the above scenario I assume I can do something. However, everything I have tried has failed. Ideas.

I am looking for something like:

select ColumnA, count(FinishDate >= getdate()+30) as 'Due 30+', count(FinishDate <= getdate()-1)as 'Past Due' from MyTableB group by ColumnA

Now I am well aware that this select statement is not valid but it is the best way I could think of to show what data I am loking for.

NotTheDBA - But playing one.
Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 14:57:24

[code]
select ColumnA,
count(case when datediff(dd,getdate(),FinishDate)>30 then 1 else null end) as 'Due 30+',
count(case when datediff(dd,getdate(),FinishDate)<0 then 1 else null end) as 'Past Due',
count(case when datediff(dd,getdate(),FinishDate)=5 then 1 else null end) as 'Past 5 days',
....
from MyTableB
group by ColumnA
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ttruheyo
Starting Member

9 Posts

Posted - 2011-09-26 : 16:33:02

This is awesome and I thank you. I see where I made my first mistake. However, I have one more question for you.

What is the syntax I need to have the second couynt not include the items from the first.

select ColumnA,
count(case when datediff(dd,getdate(),FinishDate)<0 then 1 else null end) as 'Past Due',
count(case when datediff(dd,getdate(),FinishDate)<=5 and datediff(dd,getdate(),FinishDate)>=0 then 1 else null end) as 'Due 0-5 days',
.....
From MyTableB
Group by ColumnA

NotTheDBA - But playing one.
Tom

ttruheyo
Starting Member

9 Posts

Posted - 2011-09-26 : 18:16:30

I think I figured it out. I was trying to get particular task, resource and date assignment acrodd multiple porjects on our project server. Here is what I came up with.

SELECT MEP.ProjectName AS [Project Name], MET.TaskName AS [Task Name], MET.[Task Site] AS Site, MER.ResourceName AS [Resource Assigned],
SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE()) < 0 THEN 1 ELSE '' END) AS 'PASTDUE', SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate,
GETDATE()) BETWEEN 0 AND 5 THEN 1 ELSE '' END) AS 'DUE5', SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE()) BETWEEN 6 AND
10 THEN 1 ELSE '' END) AS 'DUE10', SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE()) BETWEEN 11 AND 20 THEN 1 ELSE '' END) AS 'DUE20',
SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE()) BETWEEN 21 AND 30 THEN 1 ELSE '' END) AS 'DUE30', SUM(CASE WHEN DATEDIFF(DAY,
MET.TaskFinishDate, GETDATE()) BETWEEN 31 AND 45 THEN 1 ELSE '' END) AS 'DUE45', SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE())
BETWEEN 46 AND 60 THEN 1 ELSE '' END) AS 'DUE60', SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE()) BETWEEN 61 AND
90 THEN 1 ELSE '' END) AS 'DUE90', SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE()) BETWEEN 91 AND 120 THEN 1 ELSE '' END) AS 'DUE120',
SUM(CASE WHEN DATEDIFF(DAY, MET.TaskFinishDate, GETDATE()) > 120 THEN 1 ELSE '' END) AS '120+'
FROM dbo.MSP_EpmProject_UserView AS MEP INNER JOIN
dbo.MSP_EpmTask_UserView AS MET ON MEP.ProjectUID = MET.ProjectUID AND MEP.ProjectName <> MET.TaskName LEFT OUTER JOIN
dbo.MSP_EpmAssignment_UserView AS MEA ON MET.TaskUID = MEA.TaskUID AND MET.ProjectUID = MEA.ProjectUID LEFT OUTER JOIN
dbo.MSP_EpmResource_UserView AS MER ON MEA.ResourceUID = MER.ResourceUID
WHERE (MET.TaskPercentCompleted < 100) AND (MET.TaskIsSummary <> 1) AND (MEP.ProjectOwnerName IS NOT NULL)
GROUP BY MEP.ProjectName, MET.TaskName, MET.[Task Site], MET.TaskFinishDate, MER.ResourceName

So Thanks to everyone for all your help.

NotTheDBA - But playing one.
Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 00:46:41

quote:
Originally posted by ttruheyo

So Thanks to everyone for all your help.

NotTheDBA - But playing one.
Tom

you regarded me as Everyone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources