如何在MSSQL中找到去年的WTD(周至今)数据？

Question

所以我找到了TYWTD(今年到目前为止)，我得到了今年的查询，但是我需要知道如何为去年的4-5-4日历日期编写一个查询。

如果我今天撤回报告，那么日期范围应该是下面的。

今年世界贸易署的日期范围应为6/11/17 - 6/13/17，去年的世界贸易署日期范围应为6/12/17 - 6/14/17。

有人能给我去年的WTD建议吗？

谢谢!

                        SELECT MAX(te.StoreID) AS StoreID, 
                               SUM(te.Price * te.Quantity) AS Sales, 
                               SUM(te.Cost * te.Quantity) AS Cost,  
                               COUNT(DISTINCT t.TransactionNumber) AS Trxn,
                               SUM(te.Quantity) AS Quantity 
                        FROM   TransactionEntry te 
                               INNER JOIN [Transaction] t 
                                       ON te.TransactionNumber = t.TransactionNumber 
                                          AND te.StoreID = t.StoreID 
                               LEFT JOIN item i 
                                      ON te.itemID = i.ID 
                               LEFT JOIN Department d 
                                      ON i.DepartmentID = d.ID 
                        WHERE  d.ID <> 8 
                               AND CONVERT(date, t.Time) >= dateadd(day, 1-datepart(dw, getdate()), convert(date, getdate()))
                               AND CONVERT(date, t.Time) <= dateadd(day, 8-datepart(dw, getdate()), convert(date, getdate()))
                        GROUP  BY te.StoreID) AS TYWTD

Answer 1

从GetDate()中减去一年应该可以做到这一点。

dateadd(day, 1-datepart(dw, DateAdd(year,-1,getdate())), convert(date, DateAdd(year,-1,getdate())))

这就产生了2016-06-12。

这是更新的脚本。又更新了.

SELECT MAX(te.StoreID) AS StoreID, 
                               SUM(te.Price * te.Quantity) AS Sales, 
                               SUM(te.Cost * te.Quantity) AS Cost,  
                               COUNT(DISTINCT t.TransactionNumber) AS Trxn,
                               SUM(te.Quantity) AS Quantity 
                        FROM   TransactionEntry te 
                               INNER JOIN [Transaction] t 
                                       ON te.TransactionNumber = t.TransactionNumber 
                                          AND te.StoreID = t.StoreID 
                               LEFT JOIN item i 
                                      ON te.itemID = i.ID 
                               LEFT JOIN Department d 
                                      ON i.DepartmentID = d.ID 
                        WHERE  d.ID <> 8 
                               AND CONVERT(date, t.Time) >= dateadd(day, 1-datepart(dw, DateAdd(year,-1,getdate())), convert(date, DateAdd(year,-1,getdate())))
                               AND dateadd(day, 8-datepart(dw, DateAdd(year,-1,getdate())), convert(date, DateAdd(year,-1,getdate())))
                        GROUP  BY te.StoreID) AS TYWTD