February 25, 2016 at 5:11 am
I want to build a SP for following input.
--Table Script
Create table #DimLLTest
(
CounterpartyGroupid int,
EffectiveFromDate date,
EffectiveToDate date)
--DML
INSERT INTO #DimLLTest
SELECT 369,'2015-12-09','2015-12-29' UNION ALL
SELECT 369,'2016-01-08','2016-01-31' UNION ALL
SELECT 369,'2016-02-15','2016-02-25'
Now I want to write SP from above table where I'll pass any date and for above input
If we pass date as '2015-12-10' the new derived column should have value as 'IN'
if we pass date between '2015-12-29' to '2016-01-07' the new derived column should have value as 'OUT'
if we pass date between '2016-01-08' to '2016-01-30' the new derived column should have value as 'IN'
if we pass date between '2016-01-31' to '2016-02-14' the new derived column should have value as 'OUT'
if we pass date between '2016-02-15' to '2016-02-24' the new derived column should have value as 'IN'
If we pass date as '2016-02-25' the new derived column should have value as 'OUT'
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
February 25, 2016 at 5:59 am
What logic determines IN verses OUT?
Hard coding based on the dates given is not wise. Ideally, code should be written to logically decide what fits either circumstance depending on other criteria.
EDIT: If you want to base your code off what you posted, just use a CASE statement to check the date passed in and then push out the IN or OUT values.
February 25, 2016 at 6:08 am
Brandie Tarvin (2/25/2016)
What logic determines IN verses OUT?Hard coding based on the dates given is not wise. Ideally, code should be written to logically decide what fits either circumstance depending on other criteria.
EDIT: If you want to base your code off what you posted, just use a CASE statement to check the date passed in and then push out the IN or OUT values.
I have just provided sample data.Actually there are many groupkey which can have single or multiple entries.So if date falls between any date range for that group key it should be IN otherwise it should be OUT.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
February 25, 2016 at 6:39 am
rhythmk (2/25/2016)
Brandie Tarvin (2/25/2016)
What logic determines IN verses OUT?Hard coding based on the dates given is not wise. Ideally, code should be written to logically decide what fits either circumstance depending on other criteria.
EDIT: If you want to base your code off what you posted, just use a CASE statement to check the date passed in and then push out the IN or OUT values.
I have just provided sample data.Actually there are many groupkey which can have single or multiple entries.So if date falls between any date range for that group key it should be IN otherwise it should be OUT.
Ah, then in that case, the CASE statement is your best bet. You just gave the logic you need for the statement, and you can use the BETWEEN keyword or a >= and <= in your conditional.
So, do you know how to write a CASE statement?
If not, look it up in Books Online and play with it. Once you do that, it's a simple matter of wrapping it with your stored procedure and sticking your variable in there.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply