sql - 更新动态列，SQL

Question

Select 
    Case 
       When VacationsAvailed = 'Yes' 
          Then Sum(Convert(int, dbo.RemainingDays(a.DutyFrom, a.DutyTo,  a.VacationsAvailed))) over (order by a.Sno)
    End [RemainingDays] 
from 
    OLVA a

此查询导致：

RemainingDays
---------------
     2
     6
    NULL
     17
    NULL
    NULL
    NULL
     31

我要做的是：当RemainingDays超过 30 时，最后一个值 RemainingDays = RemainingDays - 30，以及 RemainingDays = 0 的所有先前行；

标签： sqlsql-servertsql

Answer 1

Select   FooSno,EmpNo,DutyFrom,DutyTo, DutyPeriod,LE_EL
   , LE_Bal,LeaveFrom,LeaveTo,LeavesTaken
   , VacationsAvailed,Nature,Remarks, LE_Bal - LeavesTaken [Balance]
   , RemainingDays 
from 
(
Select *,
Case 
when FooSno = 1 then LE_EL
when FooSno > 1 then
((SUM(Convert(int,LE_EL)) OVER (Order By FooSno)) - ((SUM(LeavesTaken) Over (Order By FooSno) - LeavesTaken)))
end [LE_Bal]
from
(
select a.EmpNo,a.Sno,a.DutyFrom,a.DutyTo,dbo.GetDateDifference(a.DutyFrom,a.DutyTo) [DutyPeriod],
dbo.GetEarnedLeaves(a.DutyFrom,a.DutyTo,VacationsAvailed) [LE_EL],
ROW_NUMBER() Over (Order By a.EmpNo) [FooSno],
a.LeaveFrom,a.LeaveTo,
Case when DateDiff(dd,a.LeaveFrom,a.LeaveTo) <> 0 then (DateDiff(dd,a.LeaveFrom,a.LeaveTo)+1) 
else DateDiff(dd,a.LeaveFrom,a.LeaveTo) end [LeavesTaken],
a.VacationsAvailed,a.Nature,a.Remarks,
ISNULL(Case 
when a.VacationsAvailed = 'Yes' then
SUM(Convert(int,dbo.RemainingDays(a.DutyFrom,a.DutyTo,a.VacationsAvailed))) over (order by a.Sno)
end,0)
[RemainingDays]
from OLVA a
)y
)z
where EmpNo = (1)
order by Sno

此查询得出此结果：-

+--------+-------+------------+------------+-------------+-------+--------+------------+------------+-------------+------------------+--------+---------+---------+---------------+
| FooSno | EmpNo |  DutyFrom  |   DutyTo   | DutyPeriod  | LE_EL | LE_Bal | LeaveFrom  |  LeaveTo   | LeavesTaken | VacationsAvailed | Nature | Remarks | Balance | RemainingDays |
+--------+-------+------------+------------+-------------+-------+--------+------------+------------+-------------+------------------+--------+---------+---------+---------------+
|      1 |     1 | 1997-03-31 | 2004-07-01 | 7 - 3 - 2   |    87 |     87 | 1900-01-01 | 1900-01-01 |           0 | Yes              |        |         |      87 |             2 |
|      2 |     1 | 2006-01-28 | 2006-12-31 | 0 - 11 - 4  |    11 |     98 | 1900-01-01 | 1900-01-01 |           0 | Yes              |        |         |      98 |             6 |
|      3 |     1 | 2007-01-01 | 2012-03-23 | 5 - 2 - 23  |   250 |    348 | 2012-03-24 | 2012-04-01 |           9 | No               |        |         |     339 |             0 |
|      4 |     1 | 2012-04-02 | 2012-04-12 | 0 - 0 - 11  |     0 |    339 | 1900-01-01 | 1900-01-01 |           0 | Yes              |        |         |     339 |            17 |
|      5 |     1 | 2012-04-13 | 2015-01-18 | 2 - 9 - 6   |   132 |    471 | 2015-01-19 | 2015-01-30 |          12 | No               |        |         |     459 |             0 |
|      6 |     1 | 2015-01-31 | 2016-01-18 | 0 - 11 - 19 |    46 |    505 | 2016-01-19 | 2016-01-29 |          11 | No               |        |         |     494 |             0 |
|      7 |     1 | 2016-01-30 | 2017-04-04 | 1 - 2 - 6   |    56 |    550 | 1900-01-01 | 1900-01-01 |           0 | No               |        |         |     550 |             0 |
|      8 |     1 | 2018-01-24 | 2018-07-07 | 0 - 5 - 14  |     5 |    555 | 1900-01-01 | 1900-01-01 |           0 | Yes              |        |         |     555 |            31 |
|      9 |     1 | 2018-07-08 | 2018-07-15 | 0 - 0 - 8   |     0 |    555 | 1900-01-01 | 1900-01-01 |           0 | Yes              |        |         |     555 |            39 |
+--------+-------+------------+------------+-------------+-------+--------+------------+------------+-------------+------------------+--------+---------+---------+---------------+

剩余天数：-

USE [Leave_Account]
GO
/****** Object:  UserDefinedFunction [dbo].[RemainingDays]    Script Date: 14-Dec-18 10:12:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[RemainingDays]
(
   @FromDate DATETIME, @ToDate DATETIME, @SummerVac nvarchar(50)
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
          @FromDate) > @ToDate THEN 1 ELSE 0 END) 

    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 

    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  (DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)+1)

    RETURN 
           Case when @SummerVac = 'Yes'
           then
            @DAYS 
           End
END