SP 给定日期的上一年销售数据

Question

如何在两列中显示当前年份数据和以前的数据 SQL Server 2000

下面的过程显示我的给定日期数据 我想在其他列中设置给定日期的上一年数据

SELECT TOP 100 PERCENT
  dbo.SI_Item.ig2_Code, dbo.SI_ItemGroup2.ig2_Desc,    
  SUM(SI_InvoiceDetail.invcd_Rate * dbo.SI_InvoiceDetail.invcd_Qty -
      dbo.SI_InvoiceDetail.invcd_DiscountAmt) AS Total
FROM
  dbo.SI_InvoiceDetail
INNER JOIN
  dbo.SI_Item ON dbo.SI_InvoiceDetail.itm_ItemCode = dbo.SI_Item.itm_ItemCode
INNER JOIN
  dbo.SI_InvoiceMaster ON dbo.SI_InvoiceDetail.invcm_CoCode = dbo.SI_InvoiceMaster.invcm_CoCode AND 
  dbo.SI_InvoiceDetail.invcm_BrCode = dbo.SI_InvoiceMaster.invcm_BrCode AND 
  dbo.SI_InvoiceDetail.invcm_SiteCode = dbo.SI_InvoiceMaster.invcm_SiteCode AND 
  dbo.SI_InvoiceDetail.invcm_Year = dbo.SI_InvoiceMaster.invcm_Year AND 
  dbo.SI_InvoiceDetail.invcm_Period = dbo.SI_InvoiceMaster.invcm_Period AND 
  dbo.SI_InvoiceDetail.docs_DocCode = dbo.SI_InvoiceMaster.docs_DocCode AND 
  dbo.SI_InvoiceDetail.doctyp_Code = dbo.SI_InvoiceMaster.doctyp_Code AND 
  dbo.SI_InvoiceDetail.invcm_DocNo = dbo.SI_InvoiceMaster.invcm_DocNo
INNER JOIN
  dbo.SI_ItemGroup2 ON dbo.SI_Item.ig2_Code = dbo.SI_ItemGroup2.ig2_Code
WHERE  (dbo.SI_InvoiceDetail.docs_DocCode = 'inv') AND 
       (dbo.SI_InvoiceDetail.itm_ItemCode BETWEEN '0101010000001' AND '0301020004001') AND
       (dbo.SI_InvoiceMaster.invcm_Date BETWEEN @Invcm_date_from AND @Invcm_date_to)
GROUP BY dbo.SI_Item.ig2_Code ,SI_ItemGroup2.ig2_Desc

Answer 1

通过在查询中更改如下解决了 70%

SELECT TOP 100 PERCENT dbo.SI_Item.ig2_Code, dbo.SI_ItemGroup2.ig2_Desc,year(dbo.SI_InvoiceMaster.invcm_Date) AS SalesYear,

&

按年份分组（dbo.SI_InvoiceMaster.invcm_Date），dbo.SI_Item.ig2_Code，SI_ItemGroup2.ig2_Desc

按年份排序（dbo.SI_InvoiceMaster.invcm_Date），dbo.SI_Item.ig2_Code

但它的作品，但不是我想要的，因为它在行中显示年份然后将显示用户输入 2 年以上的日期范围。

如果有人有正确的解决方案请分享