Weeks that overlap a year SQL

Question

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

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

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

it didnt work

After Changes

is the above output for the query you have mentioned? you are using aggregate functions along with non aggregated columns without specifying a group by. — ughai, Aug 21 '15 at 08:36

Abhay Chauhan · Answer 1 · 2015-08-21T10:15:09.833

0

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

Sometable can be many tables joined together from where you can calculate sum(quantity)

(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?

edited Aug 21 '15 at 10:15

answered Aug 21 '15 at 08:48

Abhay Chauhan

404
3
11

i will paste my whole stored proc – James Chaggar Aug 21 '15 at 09:08
You can try using subquery in case statement. It should solve your problem. – Abhay Chauhan Aug 21 '15 at 09:10
could you show me what you mean ive pasted my whole query @AbhayChauhan – James Chaggar Aug 21 '15 at 09:14
this didnt work case when Sum(PreQuantity)= null and pos.WeekOfYear = 1 then (Select Sum(Quantity) from #presales where WeekOfYear =52 and @year = @Year-1) else SUM(Quantity) end as quantity – James Chaggar Aug 21 '15 at 09:36
@JamesChaggar `@year =@year-1` is not making sense. Just try to put values in it. It won't be true ever. E.g. 2015=2015-1 won't be true. You should use a column if it is available in your tables. – Abhay Chauhan Aug 21 '15 at 10:10
this did not work ,case when Sum(PreQuantity)= null and pos.WeekOfYear = 1 then (Select Sum(PreQuantity) from #presales where WeekOfYear =52 and YearWeek = YearWeek-1) else SUM(PreQuantity) end as quantity – James Chaggar Aug 21 '15 at 10:22
If I am not wrong this case is for prequantity. Try this: `case when Sum(PreQuantity)= null and pos.WeekOfYear = 1 then (Select Sum(Quantity) from #presales where WeekOfYear =52 and YearWeek = YearWeek-1) else SUM(PreQuantity) end as Prequantity`. Assuming you need to show `sum(quantity)` of last year's week 52 when prequantity is null for 1st week of current year. – Abhay Chauhan Aug 21 '15 at 10:39
this had the same output of null – James Chaggar Aug 21 '15 at 10:43
Sorry, I am tied up with my work. So not able to re-create your scenario and give you a working query. Can you please create required tables and insert sample data and create this scenario on SQLFiddle. I should be able to help you there. – Abhay Chauhan Aug 21 '15 at 10:46

Weeks that overlap a year SQL

1 Answers1

Linked