March 16, 2016 at 3:58 pm
Hi guys, can anyone give me some pointers regarding sorting by two time fields such as timein(date,not null) and timeout (date, not Null) what i need is the listing of all fields in one day in order of time so that the in and out times are mixed
Or in other words I may have
ID Datein Timein Dateout Time Out
1 1/3/16 11:30 6/3/16 12:00
2 6/3/16 11:00 9/3/16 14:00
3 2/3/16 16:00 6/3/16 14:00
4 6/3/16 9:00 12/3/16 9:00
so I want to see the list for the 6/3/16 as
4 [highlight="#7fff00"]6/3/16 9:00[/highlight] 12/3/16 9:00
2 [highlight="#7fff00"]6/3/16 11:00[/highlight] 9/3/16 14:00
1 1/3/16 11:30 [highlight="#7fff00"]6/3/16 12:00[/highlight]
3 2/3/16 16:00 [highlight="#7fff00"]6/3/16 14:00[/highlight]
Any ideas please
March 16, 2016 at 4:23 pm
andrewbowles949 (3/16/2016)
Hi guys, can anyone give me some pointers regarding sorting by two time fields such as timein(date,not null) and timeout (date, not Null) what i need is the listing of all fields in one day in order of time so that the in and out times are mixedOr in other words I may have
ID Datein Timein Dateout Time Out
1 1/3/16 11:30 6/3/16 12:00
2 6/3/16 11:00 9/3/16 14:00
3 2/3/16 16:00 6/3/16 14:00
4 6/3/16 9:00 12/3/16 9:00
so I want to see the list for the 6/3/16 as
4 [highlight="#7fff00"]6/3/16 9:00[/highlight] 12/3/16 9:00
2 [highlight="#7fff00"]6/3/16 11:00[/highlight] 9/3/16 14:00
1 1/3/16 11:30 [highlight="#7fff00"]6/3/16 12:00[/highlight]
3 2/3/16 16:00 [highlight="#7fff00"]6/3/16 14:00[/highlight]
Any ideas please
What datatypes are these columns? You kind of eluded to them being date but the timein and timeout columns contain time. Why do you need to split date and time into 2 columns anyway?
I can come up with some ideas that will work for your sample data using a case expression but I am not sure of the actual tables and if the data will actually work like I think it might.
Please take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 16, 2016 at 4:27 pm
Sorry my mistake its a time(7) field there is no real reason that its split other than perhaps my inexperience! I will check out the links ..... Andrew
March 16, 2016 at 5:51 pm
andrewbowles949 (3/16/2016)
Sorry my mistake its a time(7) field there is no real reason that its split other than perhaps my inexperience! I will check out the links ..... Andrew
What about something like this.
order by case when startdate > enddate then starttime else endtime end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply