select id,mainid,FBaseWages,FPostSalary,FStartDate,FEndDate
from uf_base_salary_info_dt1
where id=486
-----
转换成:开始日期到结束日期 每天一条数据
SELECT tmp.id,tmp.mainid,FBaseWages,FPostSalary,
DATEADD(DAY, Nbr - 1, tmp.FStartDate) Detail_Date
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
cross join
(
select id,mainid,FBaseWages,FPostSalary,FStartDate,FEndDate
from uf_base_salary_info_dt1
where len(FEndDate)>0 --and id=486
union all
select id,mainid,FBaseWages,FPostSalary,FStartDate, GETDATE() FEndDate
from uf_base_salary_info_dt1
where LEN(FEndDate)=0
)tmp
WHERE Nbr - 1 <= DATEDIFF(DAY, tmp.FStartDate, tmp.FEndDate)
取每月最后一天数据(少3.17日数据)
select id,mainid,FBaseWages,FPostSalary,Detail_Date
from v_uf_base_salary_info_dt1_today
where Detail_Date=DATEADD(mm, DATEDIFF(mm, -1, Detail_Date), -1)
and ID=486
----取:结束日期<>月未最后一天的数据 + 上
select id,mainid,FBaseWages,FPostSalary,FEndDate
from uf_base_salary_info_dt1
where FEndDate<>DATEADD(mm, DATEDIFF(mm, -1, FEndDate), -1)
and ID=486
-----------------
合起来实现效果