question

Murali avatar image
Murali asked

Query Issue

As per my requirment, through store proc i am passing few parameters like @Time time,@App_Duration time...suppose if i give @time as "09:15" and @App_Duration as "60" (minuts) .. In my table there are two columns called "break1_from" and "break1_to" ... suppose "break1_from" value is "10:00" and "break1_to" value is "11:00" ... now i want to findout whatever the @time and @app_duration (@time + @app_duration) if that time lies between "break1_from" and "break1_to" ..i need to get a message as 'there is a break'...i am perfectly getting that ...but the problem is ...suppose if i give @app_duration" value as "150" then it is adding @time+@app_duration (i.e 9:15 + 150 ) ...apporx 11:30 ...but now also i need to get as "there is a break"... Any help is highly appreciatable...
t-sql
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have added a query like this CONVERT(VARCHAR,DATEADD(MI,@APP_DURATION-1,CONVERT(DATETIME,@TIME)),108) BETWEEN BREAK1_FROM AND BREAK1_TO
0 Likes 0 ·
Sorry, have you solved the problem on your own then?
0 Likes 0 ·
with the above query ...it is not cmg
0 Likes 0 ·

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
You're comparing the end date looking for an end time within the break times. You need to compare the start date & end dates to see if the start date is before the break time, and the end date is after the start of that break time. However, this approach may not work if there are multiple breaks in the span...
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ThomasRushton +1 This upvote moved you over 2k mark, congratulations! Now you should be able to edit the questions.
0 Likes 0 ·
Way to go Thomas!
0 Likes 0 ·
@Mark - \\o/ @Oleg - that's assuming that editing questions works in a different way to editing tags - that still isn't working for me, and hasn't been for several weeks...
0 Likes 0 ·
@ThomasRushton Editing questions should work just fine and it is a very useful feature. What occasionally happens is that the person asking the question does not wrap the code into \ tag, which leads to the question looking good in the editor but completely unformatted once posted. Additionally, when long lines of T-SQL are used (like a long list of comma-separated columns with no spaces after the comma), the posted answer actually messes up the layout of the page because those long lines do not wrap. Editing fixes these issues very quickly (just highlight the code and wrap it into the code block), and makes the question easier to understand.
0 Likes 0 ·
@Oleg - I know what you're getting at - I've seen it done many times, and am looking forward to trying it out.
0 Likes 0 ·
And the retag option works in the full edit screen. \\o/
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.