Date Difference 1

[Gatorajc]

I need a query that comes up with the number of days the record has been in the system. Im using MS SQL Server 7.0.

Entydate Dept Unit User
10/31/02 7:49:54 AM 120 724 joe
10/1/02 8:05:58 PM 120 697 Larry
10/30/02 10:05:02 AM 86 15 Mo
10/31/02 9:19:24 AM 120 724 Curly

I want this result (Using today as the difference in the date.

Objects Dept Unit Days Old
2 120 724 1
1 120 697 31
1 86 15 2

I can get it in the code but have not been able to put a SQL statment together to do it. AJ
I would lose my head if it wasn't attached.

 

[dickiebird]

select dept,unit,user,@days = datediff(day,entydate,getdate()) from yourtable

;-) Dickie Bird
Honi soit qui mal y pense

 

[r937]

select count(*) as Objects
     , Dept
     , Unit
     , datediff(d,Entydate,getdate()) as DaysOld            
  from yourtable
group
    by Dept
     , Unit

 

[r937]

aw, crap (obligatory "Raymond" reference)

i forgot something

it should be

group
    by Dept
     , Unit
     , datediff(d,Entydate,getdate())

 

[Gatorajc]

Thanks to all, AJ
I would lose my head if it wasn't attached.

 

[Gatorajc]

Is there a way to then narrow it then by DaysOld so say I only wanted Records that were less than 11 days. I tried this

select count(*) as Objects
, Dept
, Unit
, datediff(d,Entydate,getdate()) as DaysOld
from tablename

Where Dept = 120 and DaysOld < 11

group
by Dept
, Unit
, datediff(d,Entydate,getdate())

I knew it wouldnt work but I thought I would try it and sure enough.
AJ
I would lose my head if it wasn't attached.

 

[r937]

i don't have sql/server here to test it, but i'm guessing you have to repeat the expression for DaysOld in the WHERE clause like it is in the GROUP BY clause

where Dept = 120
and datediff(d,Entydate,getdate()) < 11

rudy

 

[Gatorajc]

Thanks Rudy,

Should have known that one. AJ
I would lose my head if it wasn't attached.

 

[Gatorajc]

Believe it or not I am trying to learn this but Im stuck again. I am making progress though. How can I get it to display only 1 name right now it looks like this.

Unit Cnt DaysOld
UnitA 4 2
UnitA 1 6
UnitA 1 7
UnitB 2 1

I want it to display like this.

Unit Cnt DaysOld
UnitA 4 2
1 6
1 7
UnitB 2 1

Select Distinct (B.Unit), count(*) as cnt, datediff(d,Dept_Recvd_date,getdate()) as DaysOld,

From mytable A Left OUTER JOIN Units B ON (B.Unit = A.Unit)

Where A.Dept = 120 and datediff(d,Dept_Recvd_date,getdate()) < 11

Group by B.Unit, datediff(d,Dept_Recvd_date,getdate()), A.Dept

order by B.Unit ASC

I know I must be on the right track. I hope. AJ
I would lose my head if it wasn't attached.

 

[r937]

distinct is not necessary when you use GROUP BY, as all the groups are distinct by definition

to suppress the unit on subsequent lines, use a reporting program, not sql