help on sql query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

help on sql query

Author

Topic

asknalam
Starting Member

1 Post

Posted - 2009-10-22 : 18:11:50

I have table data like this
Id(PrimaryKey) Time Value Time Order
1 06:00AM 1
2 07:00AM 2
3 08:00AM 3
4 09:00AM 4
5 10:00AM 5
6 11:00AM 6

If I get query like select * from table order by Time Order then I will above data
My requirement is if I have 2 customers
• Customer1 configure start time as 08:00AM then I have to bind drop down first value as 08:00AM , 09:00AM, 10:00AM, 11:00AM, 06:00AM, 07:00AM
• Customer1 configure start time as 10:00AM then I have to bind drop down first value as 10:00AM , 11:00AM, 06:00AM, 07:00AM, 08:00AM, 09:00AM

So it has basically start from ( based on configuration by customer specific) to end time then start time onwards.

Can you please how can we write SQL query above and how can I achive above requirement?

Thank,
Ashok

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-22 : 23:59:35

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=134754
check this

gaauspawcscwcj

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-23 : 00:45:53

create Table #TableName(Id int Primary Key,TimeValue varchar(20),TimeOrder Int)
Insert #TableName values (1,'06:00AM',1)
Insert #TableName values (2,'07:00AM',2)
Insert #TableName values (3,'08:00AM',3)
Insert #TableName values (4,'09:00AM',4)
Insert #TableName values (5,'10:00AM',5)
Insert #TableName values (6,'11:00AM',6)

Declare @ConfigTime Varchar(20)
Set @ConfigTime='08:00AM'

Select
TimeValue
from
#TableName T,
(Select TimeOrder from #TableName Where TimeValue=@ConfigTime)ConfigTime,
(Select Max(TimeOrder)TimeOrder From #TableName)MaxOrder
Order by
CASE WHEN (T.TimeOrder-ConfigTime.TimeOrder)<0 THEN MaxOrder.TimeOrder+(T.TimeOrder-ConfigTime.TimeOrder) ELSE (T.TimeOrder-ConfigTime.TimeOrder) END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-23 : 02:20:10

[code]
declare @TimeOrder int

select @TimeOrder = TimeOrder
from data
where TimeValue = '08:00 AM'

select *
from data
order by case when TimeOrder >= @TimeOrder then 1 else 2 end, TimeOrder
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources