如何从多个选择中删除union all并使其成为一个单独的查询

Question

我正试图在一个查询中编写这篇文章。我该怎么做？

基本上我每个月都需要这个，所以我不能写30次。任何可能的实现方式请分享。在下面的查询中，相同的代码被写了3次，但日期参数不同。

Select  
case when Cast(ILE.[Posting Date] as date) <  Cast(Getdate() as date)  then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Opening],
case when Cast(ILE.[Posting Date] as date) <=  Cast(Getdate() as date)  then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Closing]
from  [Item Ledger Entry] ILE
where Cast(ILE.[Posting Date] as date) <= Cast(Getdate() as date)
Union all
Select  
case when Cast(ILE.[Posting Date] as date) <  Cast(Getdate()-1 as date)  then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Opening],
case when Cast(ILE.[Posting Date] as date) <=  Cast(Getdate()-1 as date)  then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Closing]
from [Item Ledger Entry] ILE
where Cast(ILE.[Posting Date] as date) <= Cast(Getdate()-1 as date)
union all
Select  
case when Cast(ILE.[Posting Date] as date) <  Cast(Getdate()-2 as date)  then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Opening],
case when Cast(ILE.[Posting Date] as date) <=  Cast(Getdate()-2 as date)  then cast(ILE.Quantity as Numeric(19,6)) else 0 end AS [Closing]
from [Item Ledger Entry] ILE
where Cast(ILE.[Posting Date] as date) <= Cast(Getdate()-2 as date)

---

数据如下图所示。通过使用上述查询https://i.stack.imgur.com/Hc6Co.png

Answer 1

为什么不使用CONVERT(VARCHAR(7)，ILE。[发布日期]，120)如果您在当月对此进行分组，如果您需要在当天将varchar(7)更改为varchar(10)。删除并集，只需编写一个基本的聚合查询。

注意： 您也可以在分组中的选择部分中逐部分使用此字段，您想要细分的字段可以为您提供正确的细分。

时间和范围逻辑的情况也必须改变。

Answer 2

试试这个：

DECLARE @DaysPast TABLE(
Diff SMALLINT
)
DECLARE @MaxDaysPast SMALLINT=30;
WITH DaysPast(Diff) AS   
(  
SELECT 0
UNION ALL  
SELECT Diff+1
FROM DaysPast
WHERE Diff<=@MaxDaysPast
)
INSERT INTO @DaysPast(Diff)
SELECT *  
FROM DaysPast  
WHERE Diff<=@MaxDaysPast;
Select  
case
when Cast(ILE.[Posting Date] as date) <  Cast(DATEADD(DAYS,-1*dp.Diff,Getdate() as date)  then cast(ILE.Quantity as Numeric(19,6))
else 0
end AS [Opening],
case
when Cast(ILE.[Posting Date] as date) <=  Cast(DATEADD(DAYS,-1*dp.Diff,Getdate() as date)  then cast(ILE.Quantity as Numeric(19,6))
else 0
end AS [Closing]
from  [Item Ledger Entry] ILE
inner join @DaysPast dp ON Cast(ILE.[Posting Date] as date) <= Cast(DATEADD(DAYS,-1*dp.Diff,Getdate() as date)