Post reply

help on query

  • October 22, 2009 at 4:06 pm

    #136631

    I have table data like this

    Id(PrimaryKey)Time ValueTime Order

    106:00AM1

    207:00AM2

    308:00AM3

    409:00AM4

    510:00AM5

    611:00AM6

    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

  • October 22, 2009 at 11:25 pm

    #1069793

    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

    ..

  • October 22, 2009 at 11:26 pm

    #1069795

    Ashok Nalam (10/22/2009)

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

    It depends... where is the "configuration by customer specific" coming from?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply