How to implement this logic in SQL server ?

Question

1.00/5 (1 vote)

See more:

please tell me how to implement this logic in SQL server i am a newbie i am confused how to implement this in SQL server using case or if/else
@from and @to are the date entered
leas start- lease start date
lease end-leas end date
daydiff-no of days
for 1.2 as 05/09/2016 it will be 1st date of 2016 for this eg:(or the any year)

1.) Rating period
@from @to
01/01/2016 31/12/2016
lease
1.1) start end
01/02/2016 31/12/2016
then daydiff(lease start)-(lease end))

1.2) 01/01/2015 05/09/2016
then daydiff( 1st day of the year of(leas end)-(05/09/2016))

1.3) 01/01/2015 05/09/2018
then daydiff(rating @from)-(rating @to)

1.4)01/03/2016 01/09/2016
then daydiff(lease.start-lease end)

2 .Rating period
@from @to
01/03/2016 31/12/2016

lease

2.1) start end
01/02/2016 31/12/2016
then daydiff( @from- @to))

2.2) 01/01/2015 05/09/2016
then daydiff(@from-leas.end)

2.3) 01/01/2015 05/09/2018
then daydiff(@from-leas.end)

2.4)01/04/2016 01/09/2016
daydiff(lease.start-leas.end)

What I have tried:

i am a newbie in Sql server pls help me how to implement this logic in SQL server

Posted 20-Apr-17 23:06pm

Akhil Jain

Updated 24-Apr-17 20:01pm

v3

Add a Solution

Comments

CHill60 21-Apr-17 5:15am

Not clear what you mean. Explain it better, tell us what the tables look like and give some sample data. And newbie or not, we expect you to make some effort

Akhil Jain 21-Apr-17 5:21am

rating period @from and @and will be input by user and start and end are date type columns of leas table

CHill60 21-Apr-17 5:25am

Not clear enough. Define the table(s). Explain the "logic" better for example 2.1 for start date = 01/02/2016, end date 31/12/2016 then WHAT = daydiff(rating period @from-rating period @to))?
WHAT is "daydiff"
WHERE is your sample data? Where are your expected results
Use the Improve question link

Akhil Jain 21-Apr-17 5:30am

daydiff is no of days b/w from_date and @to_date

Akhil Jain 21-Apr-17 5:31am

i want to get the no. of days for the following conditions !!

CHill60 21-Apr-17 5:34am

Then tabulate those conditions properly. Use the proper terms - see 1) for entered dates between 01/01/2016 and 31/12/2016 lease - what is "lease"?
Show the table structure and give some sample data and expected results.
Spell things properly and consistently - what is daydiff(1st day(leas end0-(05/09/2006)) supposed to mean? "1st day"???
You are being obscure - we can't help you unless you state your problem clearly. This is probably the most valuable lesson you could learn as a "newbie" - state your requirements clearly

Akhil Jain 21-Apr-17 5:45am

done pls see.need help

Akhil Jain 21-Apr-17 5:25am

pls let me know what more do u want

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-04-21T00:38:00

I'm going to do my best to explain to you what you need, but you really, really really need to listen to advice and act upon it. Answer the questions that are actually asked, not what you want to write.

CHill60 asked:
Show the table structure and give some sample data and expected results.

You presumably have a table that you want to extract this information from. I have guessed what it might look like as you haven't actually told us. I was hoping for something like this...

SQL

create table test
(
	id int identity(1,1),
	[lease start] date,
	[lease end] date
)

Some sample data:

SQL

INSERT INTO test VALUES
('2016-FEB-01', '2016-DEC-31'),
('2016-JAN-01', '2016-SEP-05'),
('2015-JAN-01', '2018-SEP-05'),
('2016-APR-01', '2016-SEP-01'),
('2015-JAN-01', '2016-SEP-05')

Expected results:

SQL

id      result
1	365
2	248
3	978
4	153
5	248

Akhil Jain said:
for 1.2 as 05/09/2016 it will be 1st date of 2016 for this eg:(or the any year)

To get the first day of a year you can use this query snippet:

SQL

dateadd(yy, datediff(yy, 0, [lease end]), 0)

Akhil Jain said:
daydiff is no of days b/w from_date and @to_date

The function you are looking for is actually DATEDIFF[^] - to get the results in days you can use day, dd or d as the first parameter. For example:

SQL

DATEDIFF(day, @from, [lease end])
DATEDIFF(dd, @from, [lease end])
DATEDIFF(d, @from, [lease end])

CHill60 said:
Then tabulate those conditions properly ... Spell things properly and consistently ...

If you draw up a TABLE of the dates you are checking - e.g. in a spreadsheet - then you can spot any problems, overlaps or gaps. It also makes the requirements much, much easier to read. I was expecting something like:

1) Rating period @from = 01/01/2016 @to 31/12/2016
 
 Reference [lease start]  [lease end]	Calculation (results in days)
 1.1        01/02/2016	  31/12/2016    [lease start] - [lease end]
 1.2        01/01/2015    05/09/2016    1stDayOfYear([lease end]) - (05/09/2016)
 1.3        01/01/2015    05/09/2018    @from- @to
 1.4        01/03/2016    01/09/2016    [lease start] -[lease end]

 2) Rating period @from = 01/03/2016 @to = 31/12/2016

 Reference [lease start]  [lease end]	Calculation (results in days)
 2.1       01/02/2016     31/12/2016    @from - @to
 2.2	   01/01/2015     05/09/2016	@from -[lease end]
 2.3       01/01/2015     05/09/2018    @from -[lease end] 
 2.4       01/04/2016     01/09/2016    [lease start] - [lease end]

Note how I have been consistent - all of the column names are spelled the same way instead of leas.start, (lease end), lease.end etc.
The user inputs are clearly indicated with the @ symbol.
By using this table I can clearly see some overlap issues (2.2 and 2.3 for example)

To implement the change in Rating Periods you will probably need to use IF...ELSE[^]. For example:

SQL

IF @from = '2016-JAN-01' AND  @to = '2016-DEC-31'
	-- a query
ELSE
	-- another query

To implement the other checks you will need to use CASE[^]. A partial example:

SQL

SELECT id,
	CASE WHEN [lease start] = CAST('2016-FEB-01' AS DATE) AND [lease end] = CAST('2016-DEC-31' AS DATE) THEN DATEDIFF(dd, @from, @to)
		 WHEN [lease start] = CAST('2016-JAN-01' AS DATE) AND [lease end] = CAST('2016-SEP-05' AS DATE) THEN DATEDIFF(dd, @from, [lease end])
		 WHEN [lease start] = CAST('2015-JAN-01' AS DATE) AND [lease end] = CAST('2018-SEP-05' AS DATE) THEN DATEDIFF(dd, @from, [lease end])
		 WHEN [lease start] = CAST('2016-APR-01' AS DATE) AND [lease end] = CAST('2016-SEP-01' AS DATE) THEN DATEDIFF(dd, [lease start], [lease end])
		 WHEN [lease start] = CAST('2015-JAN-01' AS DATE) AND [lease end] = CAST('2016-SEP-05' AS DATE) THEN DATEDIFF(dd, dateadd(yy, datediff(yy, 0, [lease end]), 0), CAST('2016-SEP-05' AS DATE))
	END as result
FROM test

The rest is up to you. If you have further questions then do come back but remember to clearly state your problem and include the code that you have tried

Akhil Jain · Accepted Answer · 2017-04-24T20:01:00

@chill60 i have made a Generic solution for this it will work for above 2 cases

INSERT INTO test VALUES
('2016-FEB-01', '2016-DEC-31'),
('2016-JAN-01', '2016-SEP-05'),
('2015-JAN-01', '2018-SEP-05'),
('2016-APR-01', '2016-SEP-01'),
('2015-JAN-01', '2016-SEP-05')
('1997-08-16 00:00:00.000','2010-02-28 00:00:00.000')
('2007-10-01 00:00:00.000','2017-06-30 00:00:00.000')


DECLARE @FROM DATE='2016-Mar-01'
,@to DATE='2016-DEC-31'


SELECT id, 
(CASE 
WHEN @FROM <dbo.test.[lease start]
THEN DATEDIFF(DAY,dbo.test.[lease start],dbo.test.[lease end])

WHEN (@FROM >=[lease start]) AND (@to>=[lease end])
THEN DATEDIFF(DAY,@from,test.[lease end])

WHEN (@FROM >=[lease start]) AND (@to>=[lease end]) AND ((CAST(DATEADD(yy, DATEDIFF(yy, 0,dbo.test.[lease end]), 0) AS DATE) >@FROM))
THEN DATEDIFF(DAY,@from,test.[lease end])

WHEN (@FROM >[lease start]) AND (@to>[lease end])
THEN DATEDIFF(DAY,CAST(DATEADD(yy, DATEDIFF(yy, 0,dbo.test.[lease end]), 0) AS DATE),[lease end])

WHEN (@from > [lease start]) AND ([lease end]>@to)
THEN DATEDIFF(DAY,@FROM,@to)


WHEN ([lease start]>@FROM) AND ([lease end]<@to)
THEN DATEDIFF(DAY,[lease start],[lease end]) ELSE 5 END)
  FROM dbo.test

How to implement this logic in SQL server ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0