SQL Server 2008-从单日期列创建和从日期

Question

要开始我正在处理的表有不一致，因为有空白的租户值，我无法控制（供应商将需要时间来解决它）。日期列也是varchar，就像它在实际表中的样子一样。再次，我无法控制的东西，并为了报告的目的选择最大日期，日期列需要从字符更改为日期。数据库正在SQL Server 2008上运行，因此无法使用LEAD功能

我有一个@rent表变量，如下所示，它包含租户租金收费值。 I.e每次租户租金更新\更改它将应用于@rent表中。在某些情况下，租金可能会在一天内多次更新，或者如果团队发现不正确的租金被应用（注意创建的date_sql），则会在将来更改租金。 Date_changed_SQL表示何时实际应用租金。 DATE_CHANGED_SQL列将用于创建From至To列。

在大多数情况下会出现重复DATE_CHANGED_SQL而是基于CREATED_DATE_SQL和最新TOT_DEB_Val需要时间来选择

的样本数据：

Declare @rent TABLE 
    ([R23_SQL_ID] int, [PROP_CODE] int, [TENANT_NUMB] varchar(5), [TOT_DEB_VAL] int, [DATE_CHANGED_SQL] varchar(10), [TIME_CHANGED_SQL] varchar(5), [CREATED_DATE_SQL] varchar(10), [CREATED_TIME_SQL] varchar(5)) 

INSERT INTO @rent 
    ([R23_SQL_ID], [PROP_CODE], [TENANT_NUMB], [TOT_DEB_VAL], [DATE_CHANGED_SQL], [TIME_CHANGED_SQL], [CREATED_DATE_SQL], [CREATED_TIME_SQL]) 
VALUES 
    (12080, 2524, '41673', 77.49, '2011-07-28', '04:42', '2013-03-19', '04:42'), 
    (12081, 2524, '41673', 79.42, '2012-02-02', '04:42', '2013-03-19', '04:42'), 
    (12082, 2524, '41673', 79.95, '2012-08-16', '04:42', '2013-03-19', '04:42'), 
    (21819, 2524, '', 67.91, '2015-01-15', '09:39', '2015-01-15', '09:39'), 
    (21820, 2524, '51500', 67.91, '2015-01-16', '', '2015-01-15', '09:45'), 
    (31729, 2524, '51500', 67.91, '2016-08-08', '', '2016-08-08', ''), 
    (31152, 2524, '51500', 193.48, '2016-09-05', '10:53', '2016-09-05', '10:53'), 
    (34215, 2524, '', 89.86, '2017-03-14', '18:53', '2017-03-14', '18:53'), 
    (34216, 2524, '53407', 89.86, '2017-03-15', '', '2017-03-14', '18:53'), 
    (34260, 2524, '53407', 89.86, '2017-03-15', '12:32', '2017-03-20', '12:32'), 
    (29461, 1589, '33659', 151.78, '2000-08-21', '19:00', '2016-08-16', '19:00'), 
    (8356, 1589, '33659', 136.84, '2011-05-26', '04:42', '2013-03-19', '04:42'), 
    (8357, 1589, '33659', 144.36, '2011-11-24', '04:42', '2013-03-19', '04:42'), 
    (8358, 1589, '33659', 147.91, '2012-05-31', '04:42', '2013-03-19', '04:42'), 
    (8359, 1589, '33659', 151.78, '2012-11-29', '04:42', '2013-03-19', '04:42'), 
    (14502, 1589, '33659', 200.00, '2013-08-12', '08:30', '2013-08-12', '08:30'), 
    (15476, 1589, '', 157.68, '2013-11-22', '09:49', '2013-11-22', '09:49'), 
    (17846, 1589, '50602', 157.68, '2013-11-29', '14:00', '2014-05-21', '14:00'), 
    (18548, 1589, '50980', 157.68, '2014-06-18', '', '2014-06-18', '13:21'), 
    (18547, 1589, '50980', 160.69, '2014-06-18', '13:21', '2014-06-18', '13:21'), 
    (20351, 1589, '51343', 160.69, '2014-11-07', '', '2014-11-05', '14:20'), 
    (24096, 1589, '51343', 163.74, '2015-07-27', '07:34', '2015-07-27', '07:34'), 
    (26286, 1589, '51343', 165.01, '2016-01-25', '08:59', '2016-01-25', '08:59'), 
    (28168, 1589, '51343', 166.31, '2016-06-13', '11:02', '2016-06-13', '11:02'), 
    (32751, 1589, '51343', 166.80, '2016-12-12', '08:52', '2016-12-12', '08:52'), 
    (34058, 1589, '53386', 110.91, '2017-03-07', '', '2017-03-07', '08:22'), 
    (34057, 1589, '53386', 110.91, '2017-03-07', '08:17', '2017-03-07', '08:17'), 
    (34189, 1589, '53386', 110.91, '2017-03-07', '13:19', '2017-03-13', '13:19') 

要求：我想创建以显示租期（From和To），每个物业和租户的租金价值如下

Desired Result Screenshot

Answer 1

以下内容似乎与您想要的非常接近。您可能需要稍微调整一下。它不能完全按照您的数据给出结果...但它非常接近，应该给你一些工作。

/*cte assigns a row number to each entry, so we can join to the following row for that property*/ 
with cte as (
select 
    R23_SQL_ID, 
    PROP_CODE, 
    TENANT_NUMB, 
    DATE_CHANGED_SQL, 
    TOT_DEB_VAL, 
    CREATED_DATE_SQL, 
    ROW_NUMBER() OVER (PARTITION BY PROP_CODE ORDER BY DATE_CHANGED_SQL, TIME_CHANGED_SQL) as Seqn 
from @rent 
    --I wasn't sure about this where clause, you may want to get rid of it, but the results were closer to what you asked for with it in. 
    where TENANT_NUMB <> '' 
    ) 

select 
    rentStart.R23_SQL_ID, 
    rentStart.PROP_CODE, 
    rentStart.TENANT_NUMB, 
    rentStart.DATE_CHANGED_SQL as From_Date, 
    --where you've got to the last row, I've put in "today" as the end date 
    convert(char(10),isnull(dateadd(dd,-1,rentEnd.DATE_CHANGED_SQL), getdate()),121) as To_Date, 
    rentStart.TOT_DEB_VAL, 
    rentStart.CREATED_DATE_SQL 
from 
    cte as rentStart 
    LEFT JOIN cte as rentEnd ON 
     rentStart.Prop_Code = rentEnd .Prop_Code AND 
     rentStart.Seqn + 1 = rentEnd .Seqn 
where 
    --Filtering to just the property you were showing 
    rentStart.Prop_Code = 2524 
    --This is the attempt to look only for the final change in the day. It may be better handled in the CTE rather than here. 
AND (rentStart.DATE_CHANGED_SQL < rentEnd.Date_CHANGED_SQL OR rentEnd.DATE_CHANGED_SQL is null)