help getting return of function and loop into stored proc

Question

Post reply

help getting return of function and loop into stored proc

plivingstone

Old Hand

Points: 397
More actions
June 7, 2013 at 7:35 am

#276038

Hi,
I have a tricky issue I am struggly with on a mental level.
In our db we have a table showing the UK Holidays for the next few years, and a stored function returns a recordset to my front end.
I have a flag in my recordset called 'deletable' which allows the frontend to decide if a context menu can be shown in the data grid, thus allowing that record to be deleted.
Currently the test (in my stored proc) just checks if the date column has a date from three days ago or more.
case when DATEDIFF(d,a.[date],GETDATE()) > 3 then 1 else 0 end as [deletable]
how can I modify that to find the previous working date by checking weekends and the Holidays table 'Holiday' column (which is a Datetime) and see if the [date] column in my recordset row is 3 working days before, taking into account Holidays from the Holidays table and weekends?
Is there an elegent way to do that?
thanks
Philip

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Hi and welcome to the forum.

It's possible, but in order to assist we need a bit more details about your chelenge.

Please provide DDL of tables invloved and some test data (in form of insert script).

If you follow the link at the bottom of my signature you will find great tips about what kind of details expected.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

plivingstone Old Hand Points: 397 More actions · Answer 2

thanks I got some answers on StackOverflow, one even using master..sp_spt

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 3

plivingstone (6/7/2013)
thanks I got some answers on StackOverflow, one even using master..sp_spt

Cool. What's the link for that?

--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)

plivingstone Old Hand Points: 397 More actions · Answer 4

StackOverFlow: calculate 3 working days before in stored proc using Holidays lookup table

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 5

plivingstone (6/10/2013)
StackOverFlow: calculate 3 working days before in stored proc using Holidays lookup table

It's a funny thing that people think that recalculating the same thing over and over is the right way to go. Even the Calendar table method they show has a performance problem because the day you're looking for has to be calculated using ROW_NUMBER() each time you need to find the day. That's probably ok for very low usage but I never plan on very low usage.

The key would be to consecutively number the workdays and then it's a simple and very fast lookup. I'll try to get back to this tonight after work to demonstrate.

The question that I have about your problem is... Is the start date included in the 3 days before or not? For example, if the start date were a Friday, what would be 3 work days before? Tuesday or Wednesday?

--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)

plivingstone Old Hand Points: 397 More actions · Answer 6

hi,

it would be Tuesday

An example from last month:

May 27th was Bank Hoiliday Monday, so if the [Date] columns shows any date after 22nd May then it cannot be deleted.

So that's the test.

Thanks very much for taking a look.

Surely the answer on SO above isn't so bad, create the test date variable at the beginning of the stored proc, then use that as a test in my 'can it be deleted' derived column?

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 7

plivingstone (6/10/2013)
hi,
it would be Tuesday
An example from last month:
May 27th was Bank Hoiliday Monday, so if the [Date] columns shows any date after 22nd May then it cannot be deleted.
So that's the test.
Thanks very much for taking a look.
Surely the answer on SO above isn't so bad, create the test date variable at the beginning of the stored proc, then use that as a test in my 'can it be deleted' derived column?

I don't know for sure because I haven't done any testing with the SO solutions. They just look a bit challenged. It could be that the While Loop solution will be the fastest but we'll have to check. I that a few milliseconds and a handful of reads might not make any difference for your particular application but it could for someone else. If you have the fastest, then you never have to worry about where it can be used. I know... it's just me. 😛

--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)

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 8

Apologies... It's 1:30AM here. I'm almost done but too pooped to pop. I'll have to finish this "tomorrow" night.

--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)