SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Point of time		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zrw
Starting Member

2 Posts
Posted - 06/26/2012 :  09:45:34  Show Profile  Reply with Quote
Hi, I am having one of those days, I just cant think...


I have the following fields LoggedDate,ResolvedDate,ID

I would like to list all the records that were open at a point in time...

eg ResolvedDate of 1753 or resolved after or before the selected date
and logged before the selected date

r1 10/02/2010 11:53:53 1/01/1753 00:00:00
r2 11/02/2010 11:53:53 11/02/2010 11:00:00
r3 12/02/2010 11:53:53 14/02/2010 13:00:00
r4 13/02/2010 11:53:53 15/02/2010 09:00:00
r5 14/02/2010 11:53:53 1/01/1753 00:00:00

Therefore if i select the 12/02/2010 it will count r1 and r3 only

Hope this is enough info?

jimf
Flowing Fount of Yak Knowledge

USA
2865 Posts
Posted - 06/26/2012 :  11:50:24  Show Profile  Reply with Quote
WHERE LoggedDate <= '20101202'
and (ResolvedDate >= '20101202' or ResolvedDate = '17530101')

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3821 Posts
Posted - 06/26/2012 :  11:58:43  Show Profile  Reply with Quote
quote:
resolved after or before the selected date
Does that mean you only want Resolved dates not equal to the selected date (12/02/2010) or can that predicate really be ignored?
Go to Top of Page

zrw
Starting Member

2 Posts
Posted - 06/27/2012 :  08:47:50  Show Profile  Reply with Quote
jimf i tried that and I get the correct logged date, but the resolve date only returns 1753 not and job that was added prior to the date but closed after the selected date.

Lamprey if the call was logged prior to the selected date, though closed after the selected date then i want to see it...
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2865 Posts
Posted - 06/27/2012 :  09:14:28  Show Profile  Reply with Quote
Slight change to mine
WHERE LoggedDate < '20101302'
and (ResolvedDate >= '20101202' or ResolvedDate = '17530101')

I didn't take the time element into consideration! This gives what you originally asked for.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000