与一年重叠的周数SQL [英] Weeks that overlap a year SQL

问题描述

我目前正在撰写一份庞大的报告，并且需要对我辛苦工作了几个星期的重要部分提供帮助

I'm currently working on a huge report and need some help on a vital part which I've been on for ironically weeks

我有一份报告可以计算基于几件事
的数量，但是我想做的是，当 PREQuantity列为空并且一年中的某周= 1时，然后从前一年
的第52周开始获得数量说明我知道还没有完成，但是本质上我需要在上一年的第52周强制执行

I have a report that calculates quantity based on a few things but what im trying to do is when the "PREQuantity" column is null and week of year = 1 then get quanitity from week of year 52 for the previous year the case statement is unfinished i know, but essentially i need the report to force in week 52 of the previous year for the prequantity

SELECT DR.DivNo
    ,p.[ProductCode]
    ,p.ProductClass
    ,p.EmpNo
    ,[Description]
    ,[CGNo]
    ,[SCGNo]
    ,dr.Retail
    ,bd.[Buying Director]
INTO #Product
FROM [PRODUCT] p
LEFT JOIN [DIVRETAIL] DR ON p.ProductCode = DR.ProductCode
LEFT JOIN vwAllBuyingDirectors bd ON p.EmpNo = bd.bd_BuyingDirector
WHERE dr.ValidTo IS NULL
    AND dr.DivNo NOT LIKE '8__'
GROUP BY DR.DivNo
    ,p.[ProductCode]
    ,p.ProductClass
    ,p.EmpNo
    ,bd.[Buying Director]
    ,[Description]
    ,[CGNo]
    ,[SCGNo]
    ,dr.Retail

    --select * from #Product

SELECT  [Datetime]
    ,[FirstDayOfWeek]
    ,[FirstDayOfMonth]
    ,c.YearWeek
    ,s.NoOfStores
    ,s.DivNo
INTO #stores
FROM [Calendar] c
LEFT JOIN Stores s ON c.FirstDayOfMonth = s.Validfrom
WHERE c.[Datetime] = firstdayofweek

--- GIVE STORE COUNT TO EACH DATETIME
SELECT s.DivNo
    ,c.[Datetime]
    ,c.WeekofYear
    ,c.FirstdayofWeek
    ,c.YearWeek
    ,s.NoOfStores
INTO #weeklystores
FROM .Calendar c
LEFT JOIN #stores s ON c.FirstDayOfWeek = s.FirstDayOfWeek
 -- Join numbers from #stores according to first day of week.  Each week onmly has the first day of the week's store count as a reference.

------------------------------------------PREVIOUS WEEK---------------------     --------------------
 SELECT  [Datetime]
    ,[FirstDayOflastWeek]
    ,[FirstDayOflastMonth]
    ,c.YearWeek
    ,s.NoOfStores
    ,s.DivNo
INTO #storesv2
FROM [Calendar] c
LEFT JOIN Stores s ON c.FirstDayOfMonth = s.Validfrom
WHERE c.[Datetime] = FirstDayOfWeek

--- GIVE STORE COUNT TO EACH DATETIME
SELECT s.DivNo
    ,c.[Datetime]
    ,c.WeekofYear
    ,c.FirstdayoflastWeek
    ,c.YearWeek
    ,s.NoOfStores
INTO #preweeklystores
FROM .Calendar c
LEFT JOIN #storesv2 s ON c.FirstDayOfWeek = s.FirstDayOfLastWeek    

/*
ASSIGN STORE COUNTS AND WEEK NUMBERS TO POS DATA
*/
    CREATE TABLE #salescore (
    [DivNo] NVARCHAR(max)
    ,[ProductCode] INT
    ,[Description] NVARCHAR(max)
    ,[CGNo] NVARCHAR(max)
    ,[SCGNo] NVARCHAR(max)
    ,Retail DECIMAL(38, 2)
    ,[WeekOfYear] TINYINT
    ,FirstDayOfWeek DATETIME
    ,[Quantity] INT
    ,[Sales] DECIMAL(38, 2)
    ,[NoOfStores] INT
    ,[USW] DECIMAL(38, 0)
    )

INSERT INTO #salescore
SELECT pos.DivNo
    ,pos.ProductCode
    ,[Description]
    ,[CGNo]
    ,[SCGNo]
    ,p.Retail
    ,w.WeekOfYear
    ,w.FirstDayOfWeek
    ,Sum(Quantity) [Quantity]
    ,sum(pos.Retail) [Retail2]
    ,w.NoOfStores
    ,(Sum(Quantity) / w.NoOfStores) USW
FROM .pos pos
LEFT JOIN #weeklystores w ON pos.PosDate = w.[DATETIME]
LEFT JOIN #Product p ON p.ProductCode = pos.ProductCode
    AND p.DivNo = pos.DivNo
    AND pos.DivNo = w.DivNo
WHERE pos.DivNo NOT LIKE '8__' /* Get rid of IRL Regions */
and w.YearWeek >2013
    and w.YearWeek IN (
        SELECT Item
        FROM DataWarehouse.dbo.ufnSplit(@YEAR, ',')
        )
    AND p.EmpNo IN (
        SELECT Item
        FROM ufnSplit(@BD, ',')
        )
    AND p.CGNo IN (
        SELECT Item
        FROM .ufnSplit(@CGNo, ',')
        )
    AND p.SCGNo IN (
        SELECT Item
        FROM ufnSplit(@SCGNo, ',')
        )
    AND p.ProductClass IN (
        SELECT Item
        FROM ufnSplit(@ProductClass, ',')
        )
    AND p.ProductCode IN (
        SELECT Item
        FROM ufnSplit(@ProductCode, ',')
        )
    AND pos.DivNo IN (
        SELECT Item
        FROM ufnSplit(@Region, ',')
        )
    AND w.WeekOfYear IN (
        SELECT Item
        FROM ufnSplit(@WOY, ',')
        )
    AND w.WeekOfYear IS NOT NULL /* Get rid of pos before goldthorp opening day */
GROUP BY pos.DivNo
    ,pos.ProductCode
    ,w.WeekOfYear
    ,w.NoOfStores
    ,w.FirstDayOfWeek
    ,[Description]
    ,[CGNo]
    ,[SCGNo]
    ,p.Retail


    ----------------------------------------PreviousWeekSales---------------------------------------------

        CREATE TABLE #presales (
    [DivNo] NVARCHAR(max)
    ,[ProductCode] INT
    ,[Description] NVARCHAR(max)
    ,[CGNo] NVARCHAR(max)
    ,[SCGNo] NVARCHAR(max)
    ,Retail DECIMAL(38, 2)
    ,PrevWOY TINYINT
    ,FirstDayOfWeek DATETIME
    ,[PreQuantity] INT
    ,[Sales] DECIMAL(38, 2)
    ,[NoOfStores] INT
    ,[USW] DECIMAL(38, 0)
    )

INSERT INTO #presales
SELECT pos.DivNo
    ,pos.ProductCode
    ,[Description]
    ,[CGNo]
    ,[SCGNo]
    ,p.Retail
    ,w.WeekOfYear as PrevWOY
    ,w.FirstDayOfLastWeek
    ,SUM(Quantity) Quantity
    ,sum(pos.Retail) [Retail3]
    ,w.NoOfStores
    ,(Sum(Quantity) / w.NoOfStores) USW
FROM .pos pos
LEFT JOIN #preweeklystores w ON pos.PosDate = w.[DATETIME]
LEFT JOIN #Product p ON p.ProductCode = pos.ProductCode
    AND p.DivNo = pos.DivNo
    AND pos.DivNo = w.DivNo
WHERE pos.DivNo NOT LIKE '8__' /* Get rid of IRL Regions */
and w.YearWeek >2013
    and w.YearWeek IN (
        SELECT Item
        FROM .ufnSplit(@YEAR, ',')
        )
    AND p.EmpNo IN (
        SELECT Item
        FROM ufnSplit(@BD, ',')
        )
    AND p.CGNo IN (
        SELECT Item
        FROM ufnSplit(@CGNo, ',')
        )
    AND p.SCGNo IN (
        SELECT Item
        FROM ufnSplit(@SCGNo, ',')
        )
    AND p.ProductClass IN (
        SELECT Item
        FROM ufnSplit(@ProductClass, ',')
        )
    AND p.ProductCode IN (
        SELECT Item
        FROM ufnSplit(@ProductCode, ',')
        )
    AND pos.DivNo IN (
        SELECT Item
        FROM ufnSplit(@Region, ',')
        )
    --AND w.WeekOfYear IN (
    --  SELECT Item
    --  FROM ufnSplit(@WOY, ',')
    --  )
    AND w.WeekOfYear IS NOT NULL /* Get rid of pos before goldthorp opening day */
GROUP BY pos.DivNo
    ,pos.ProductCode
    ,w.WeekOfYear
    ,w.NoOfStores
    ,w.FirstDayOfLastWeek
    ,[Description]
    ,[CGNo]
    ,[SCGNo]
    ,p.Retail



    CREATE TABLE #sales (
    [DivNo] NVARCHAR(max)
    ,[ProductCode] INT
    ,[Description] NVARCHAR(max)
    ,[CGNo] NVARCHAR(max)
    ,[SCGNo] NVARCHAR(max)
    ,Retail DECIMAL(38, 2)
    ,[WeekOfYear] TINYINT
    ,FirstDayOfWeek DATETIME
    ,[PreQuantity] int
    ,[Quantity] INT
    ,[Sales] DECIMAL(38, 2)
    ,[NoOfStores] INT
    ,[USW] DECIMAL(38, 0)
    ) 
    INSERT INTO #sales
    SELECT pos.DivNo
    ,pos.ProductCode
    ,p.Description
    ,p.CGNo
    ,p.SCGNo
    ,p.Retail
    ,pos.WeekOfYear
    ,pos.FirstDayOfWeek
    ,SUM(ps.PreQuantity)
    ,Sum(pos.Quantity) [Quantity]
    ,sum(pos.Sales) Sales
    ,pos.NoOfStores
    ,(Sum(pos.Quantity) / pos.NoOfStores) USW
FROM #salescore pos
LEFT JOIN #presales PS on (pos.WeekOfYear -1) = ps.PrevWOY and pos.ProductCode = ps.ProductCode and pos.DivNo= ps.DivNo

LEFT JOIN #Product p ON p.ProductCode = pos.ProductCode
    AND p.DivNo = pos.DivNo
WHERE pos.DivNo NOT LIKE '8__' /* Get rid of IRL Regions */
Group By
    pos.DivNo
    ,pos.ProductCode
    ,p.Description
    ,p.CGNo
    ,p.SCGNo
    ,p.Retail
    ,pos.WeekOfYear
    ,pos.FirstDayOfWeek
    ,pos.NoOfStores






SELECT *
FROM #Sales

ORDER BY 3 ASC

DROP TABLE #weeklystores
DROP TABLE #stores
DROP TABLE #storesv2
DROP TABLE #sales
DROP TABLE #Product
END

我做了这个根据@Abhay的建议进行更改

i made this change as suggested by @Abhay

 ,case when Sum(PreQuantity)= null and pos.WeekOfYear = 1 then (Select        Sum(PreQuantity) from #presales where WeekOfYear =52 and @year = @Year-1) else       SUM(PreQuantity) end as quantity

$ b结束$ b

它没有起作用

it didnt work

更改后

推荐答案

根据我的理解，我正在尝试完成您的个案声明：

I am trying to complete your case statement based on what I understand:

case when Sum(PreQuantity)= null and WeekOfYear = 1 then 
(Select Sum(Quantity)= null from Sometable a where a.WeekOfYear =52 and a.year = Year-1) 
else null end

注意：
假设您有一个列或可以导出年份的列

Note: Assuming you have a column or can derive a column for year

可能有很多表被连接

（年份）-1将从您最初的外部查询中得出，即

(Year)-1 will come from your original outer query i.e

FROM pos pos
LEFT JOIN #preweeklystores w ON pos.PosDate = w.[DATETIME]
LEFT JOIN #Product p ON p.ProductCode = pos.ProductCode
    AND p.DivNo = pos.DivNo
    AND pos.DivNo = w.DivNo
WHERE pos.DivNo NOT LIKE '8__' /* Get rid of IRL Regions */
and w.YearWeek >2013

如果您可以将别名用作表名作为每列的前缀，我将能够给出确切的查询。另外，您是否有日期/年份的列？

If you can use alias for tablename as prefix for each column, I would be able to give a exact query. Also, do you have a column for date/year?

这篇关于与一年重叠的周数SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！