如何在SQL Server中实现这个逻辑？ [英] How to implement this logic in SQL server ?
问题描述
请告诉我如何在SQL服务器中实现这个逻辑我是一个新手我很困惑如何在SQL服务器中使用case实现这个或if / else
@from和@to是输入的日期
leas开始 - 租赁开始日期
租赁结束日期结束日期
daydiff-没有天数
$ 2016年9月5日的b $ b为1.2，这将是2016年的第1日，例如:(或任何一年）
1.）评级期
@from @to
01/01/2016 31/12/2016
租约
1.1）开始结束
01/02/2016 31/12/2016
then daydiff（租约开始） - （租约结束））
1.2）01/01/2015 05/09/2016
然后是daydiff（（leas end）年份的第一天 - （05/09/2016））
1.3）01/01/2015 05/09/2018
then daydiff（rating @from ） - （评分@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
租赁
2.1 ）开始结束
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
然后daydiff（@ from-leas.end）
2.4）01 / 04/2016 2016/09/01
daydiff（lease.start-leas.end）
我试过的：
i我是Sql server的新手请帮我讲解如何在SQL server中实现这个逻辑
我会尽力向你解释你需要什么，但你真的，真的需要倾听建议并采取行动。回答实际问的问题，而不是你想写的问题。
CHill60问：显示表结构并提供一些示例数据和预期结果。
您可能有一个表要从中提取此信息。我已经猜到了它可能看起来像你没有真正告诉过我们的样子。我希望这样的事情...
创建 表 test
（
id int identity （ 1 ， 1 ），
[租约开始] 日期，
[lease end ] date
）一些示例数据：
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'）预期结果：
id结果
1 365
2 < span class =code-digit> 248
3 978
4 153
5 248
Akhil Jain说：为1.2 20年5月9日16这将是2016年的第一个日期，例如:(或任何一年）
要获得一年的第一天，您可以使用此查询片段：
dateadd（yy，datediff） （yy， 0 ，[lease end ]）， 0 ）
Akhil Jain说：daydiff没有天b / w from_date和@to_date
您正在寻找的功能实际上是 DATEDIFF [ ^ ] - 以天为单位获得结果，您可以使用
天，dd或d作为第一个参数。例如：DATEDIFF（day， @ from ，[lease end ] ）
DATEDIFF（dd， @ from ，[lease end ]）
DATEDIFF （d， @ from ，[lease end ]）
CHill60说：然后正确地列出这些条件...正确且一致地拼写事物......
如果你绘制一个日期表，你是检查 - 例如在电子表格中 - 然后您可以发现任何问题，重叠或差距。它还使要求更加容易阅读。我期待的是：
1）评级期@from = 01/01/2016 @to 31/12/2016
参考[lease start] [lease end]计算（以天为单位）
1.1 01/02/2016 2016年12月31日[租赁开始] - [租赁结束]
1.2 01/01/2015 05/09/2016 1stDayOfYear（[租赁结束] ]） - （05/09/2016）
1.3 01/01/2015 05/09/2018 @ from- @to
1.4 01/03/2016 01/09/2016 [lease start] - [租赁结束]
2）评级期@from = 01/03/2016 @to = 31/12/2016
参考[租约开始] [租赁结束]计算（结果天数）
2.1 01/02/2016 2016/12/33 @from - @to
2.2 01/01/2015 05/09/2016 @from - [lease end]
2.3 01/01/2015 05/09/2018 @from - [租约结束]
2.4 01/04/2016 01/09/2016 [租约开始] - [租约结束]
请注意我是如何保持一致的 - 所有列名都拼写相同而不是leas.start，（租约结束），l easy.end等
用@符号清楚地表示用户输入。
通过使用此表我可以清楚地看到一些重叠问题（例如2.2和2.3）
要实现评级期间的更改，您可能需要使用 IF ... ELSE [ ^ ]。例如：IF @ from = ' 2016-JAN-01' AND @ to = ' 2016-DEC-31'
< span class =code-comment> - 一个查询
ELSE
- 其他查询要实施其他检查，您需要需要使用 CASE [ ^ ]。一个部分示例：
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 ）和 [lease end ] = CAST（' 2016-SEP -05' AS DATE ）那么 DATEDIFF（dd， @ from ，[lease end ]）
WHEN [lease start] = CAST（' 2015-JAN-01' AS DATE ） AND [lease end ] = CAST（' 2018-SEP- 05' AS DATE ）那么 DATEDIFF（dd， @ from ，[lease end ]）
WHEN [租约开始] = CAST（' 2016-APR-01' AS DATE ） AND [lease end ] = CAST（' 2016-SEP-01' AS DATE ）那么 DATEDIFF（ dd，[lease start]，[lease end ]）
WHEN [lease start] = CAST（' 2015-JAN-01' AS DATE ） AND [lease end ] = CAST（' 2016-SEP-05' AS DATE ）那么 DATEDIFF（dd，dateadd（yy，datediff（yy， 0 ，[lease 结束]）， 0 ），CAST（' 2016-SEP-05' AS DATE ））
END as result
FROM test
其余由您决定。如果您还有其他问题，请继续回来，但要记得明确说明您的问题，并包括您尝试过的代码
@ chill60我为此制定了通用解决方案，它将适用于上述2个案例
INSERT INTO测试价值
（'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。[租赁结束]）
WHEN（@FROM> = [租约开始]）AND（@ to> = [租约结束]）
那么DATEDIFF（DAY，@ from，test。[租结束]）
WHEN（@FROM> = [租约开始]）AND（@ to> = [租约结束]）AND（（CAST（DATEADD）（yy，DATEDIFF（yy，0，dbo） .test。[租约结束]），0）AS DATE）> @FROM））
THEN DATEDIFF（DAY，@ from，test。[lease end]）
WHEN（@ FROM> [租约开始]）AND（@到> [租约结束]）
那么DATEDIFF（DAY，CAST（DATEADD（yy，DATEDIFF（yy，0，dbo.test。[lease end]），0 ）AS DATE），[租约结束]）
WHEN（@from> [租赁开始]）AND（[租赁结束]> @to）
那么DATEDIFF（DAY，@ FROM，@ to）
WHEN（[租赁开始]> ; @FROM）AND（[租约结束]< @to）
那么DATEDIFF（DAY，[租约开始]，[租约结束]）ELSE 5 END）
FROM dbo.test
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
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...
create table test ( id int identity(1,1), [lease start] date, [lease end] date )Some sample data:
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:
id result 1 365 2 248 3 978 4 153 5 248Akhil 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:
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,ddordas the first parameter. For example: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:IF @from = '2016-JAN-01' AND @to = '2016-DEC-31' -- a query ELSE -- another queryTo implement the other checks you will need to use CASE[^]. A partial example:
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
@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
这篇关于如何在SQL Server中实现这个逻辑？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
