Maybe this is easy but im stuck

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

Maybe this is easy but im stuck

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2008-09-05 : 06:11:01

Hi All
I seem to have hit a blank wall with what probably is an easy issue on paper,i have written the following code which does what i wanted firstly. It is just giving me the details of a table where one date is less than the other.
However
I now want it to give me details only when the A.close_dttime is 3 days less than the B.BD_start_dttime.

Any guidance would be truly helpful.
Cheers

select convert(varchar,A.close_dttime,101), convert(varchar,B.BD_start_dttime,101),A.close_dttime,B.bus_day_ID
from it_trn.dbo.order_header A
Inner Join it_trn.dbo.Business_Day_Dates B on A.Bus_Day_ID = B.Bus_Day_ID
Where convert(varchar,A.close_dttime,101) <> convert(varchar,B.BD_start_dttime,101)
And A.open_term_id = 901
Order by A.close_dttime,convert(varchar,B.BD_start_dttime,101)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-05 : 06:20:23

[code]select A.close_dttime, B.BD_start_dttime, A.close_dttime, B.bus_day_ID
from it_trn.dbo.order_header A
Inner Join it_trn.dbo.Business_Day_Dates B on A.Bus_Day_ID = B.Bus_Day_ID
Where datediff(day, A.close_dttime, B.BD_start_dttime) < 3
And A.open_term_id = 901
Order by A.close_dttime, B.BD_start_dttime[/code]

KH
[spoiler]Time is always against us[/spoiler]

stumbling
Posting Yak Master

104 Posts

Posted - 2008-09-05 : 06:29:01

Thanks Khtan but i tried it but no good unfortunately. In most cases the dates are now identical the result set came back as follows.
04/07/2007 04/07/2007 2007-04-07 12:02:33.000 999
04/07/2007 04/07/2007 2007-04-07 12:10:25.000 999
04/07/2007 04/07/2007 2007-04-07 12:16:48.000 999
04/07/2007 04/07/2007 2007-04-07 12:18:26.000 999
04/07/2007 04/07/2007 2007-04-07 12:20:30.000 999

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-05 : 06:32:06

[code]select A.close_dttime, B.BD_start_dttime, A.close_dttime, B.bus_day_ID
from it_trn.dbo.order_header A
Inner Join it_trn.dbo.Business_Day_Dates B on A.Bus_Day_ID = B.Bus_Day_ID
Where datediff(day, A.close_dttime, B.BD_start_dttime) < 3
And a.close_dttime <> B.BD_Start_dttime
And A.open_term_id = 901
Order by A.close_dttime, B.BD_start_dttime[/code]

KH
[spoiler]Time is always against us[/spoiler]

stumbling
Posting Yak Master

104 Posts

Posted - 2008-09-05 : 21:03:12

No good unfotunatelly
The result set countt is the same with or without this new line and the results appear to be the same?
2007-04-07 12:02:33.000 2007-04-07 05:01:07.000 2007-04-07 12:02:33.000 999
2007-04-07 12:10:25.000 2007-04-07 05:01:07.000 2007-04-07 12:10:25.000 999
2007-04-07 12:16:48.000 2007-04-07 05:01:07.000 2007-04-07 12:16:48.000 999
2007-04-07 12:18:26.000 2007-04-07 05:01:07.000 2007-04-07 12:18:26.000 999
2007-04-07 12:20:30.000 2007-04-07 05:01:07.000 2007-04-07 12:20:30.000 999
2007-04-07 12:22:12.000 2007-04-07 05:01:07.000 2007-04-07 12:22:12.000 999
2007-04-07 12:23:00.000 2007-04-07 05:01:07.000 2007-04-07 12:23:00.000 999
2007-04-07 12:23:36.000 2007-04-07 05:01:07.000 2007-04-07 12:23:36.000 999
2007-04-07 12:29:24.000 2007-04-07 05:01:07.000 2007-04-07 12:29:24.000 999
2007-04-07 12:29:59.000 2007-04-07 05:01:07.000 2007-04-07 12:29:59.000 999
2007-04-07 12:30:47.000 2007-04-07 05:01:07.000 2007-04-07 12:30:47.000 999
2007-04-07 12:32:01.000 2007-04-07 05:01:07.000 2007-04-07 12:32:01.000 999

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-05 : 22:26:32

Please post your table DDL, sample data and required result

KH
[spoiler]Time is always against us[/spoiler]

stumbling
Posting Yak Master

104 Posts

Posted - 2008-09-06 : 19:00:15

Hi again

This is what i would like returned
08/17/2008 09/01/2008 2008-08-17 12:42:53.000 1506
08/17/2008 09/01/2008 2008-08-17 12:44:35.000 1506
08/17/2008 09/01/2008 2008-08-17 12:47:17.000 1506
08/17/2008 09/01/2008 2008-08-17 12:52:09.000 1506
08/17/2008 09/01/2008 2008-08-17 12:57:09.000 1506
08/17/2008 09/01/2008 2008-08-17 12:59:05.000 1506
08/17/2008 09/01/2008 2008-08-17 13:00:10.000 1506
08/17/2008 09/01/2008 2008-08-17 13:00:23.000 1506
But this is what i get
05/26/2007 05/27/2007 2007-05-26 16:08:06.000 1049
05/26/2007 05/27/2007 2007-05-26 17:12:17.000 1049
05/26/2007 05/27/2007 2007-05-26 17:16:20.000 1049
05/26/2007 05/27/2007 2007-05-26 17:18:56.000 1049
05/26/2007 05/27/2007 2007-05-26 17:19:14.000 1049
05/26/2007 05/27/2007 2007-05-26 17:40:29.000 1049
05/26/2007 05/27/2007 2007-05-26 17:41:21.000 1049
05/26/2007 05/27/2007 2007-05-26 17:41:33.000 1049
05/26/2007 05/27/2007 2007-05-26 17:44:17.000 1049
Original data looks like this
2007-05-26 16:08:06.000 2007-05-27 05:01:24.000 2007-05-26 16:08:06.000 1049
2007-05-26 17:12:17.000 2007-05-27 05:01:24.000 2007-05-26 17:12:17.000 1049
2007-05-26 17:16:20.000 2007-05-27 05:01:24.000 2007-05-26 17:16:20.000 1049
2007-05-26 17:18:56.000 2007-05-27 05:01:24.000 2007-05-26 17:18:56.000 1049
2007-05-26 17:19:14.000 2007-05-27 05:01:24.000 2007-05-26 17:19:14.000 1049
2007-05-26 17:40:29.000 2007-05-27 05:01:24.000 2007-05-26 17:40:29.000 1049
2007-05-26 17:41:21.000 2007-05-27 05:01:24.000 2007-05-26 17:41:21.000 1049
2007-05-26 17:41:33.000 2007-05-27 05:01:24.000 2007-05-26 17:41:33.000 1049
2007-05-26 17:44:17.000 2007-05-27 05:01:24.000 2007-05-26 17:44:17.000 1049
2007-05-26 17:44:51.000 2007-05-27 05:01:24.000 2007-05-26 17:44:51.000 1049
2007-05-26 17:45:57.000 2007-05-27 05:01:24.000 2007-05-26 17:45:57.000 1049
2007-05-26 17:46:24.000 2007-05-27 05:01:24.000 2007-05-26 17:46:24.000 1049

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources