How can I get the result I need using a stored prcedure?

I need to be able to produce numbers based on an ageing cycle.

I have as an example, a table that contains data for a stock item, as per the attached spreadsheet

The date is the date of the stock movement, the status is either I or O (In or Out), the Qty In and Qty Out are self explanatory. I have a current physical stock of 108. I need to be able to determine how old this physical stock is and break it down into specific periodic groups as follows:

0-13 weeks
14-26 weeks
27-52 weeks
53-78 weeks
79-104 weeks
104+ weeks

So based on todays date and the periodic groups, report the number of stock items from the physical stock.

I hope that makes sense!

Thanks

Steve

Book2.xlsx
ABM SupportIT SupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
This will give you exactly what you need :)

SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,1 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 0 
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 13
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,2 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 14 
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 26
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,3 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 27
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 52
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,4 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 53
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 78
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,5 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 79
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 104
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,6 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) > 104

Open in new window



Query Results based of your data
plusone3055Commented:
Sorry forgot the Order Clause :)

SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,1 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 0 
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 13
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,2 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 14 
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 26
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,3 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 27
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 52
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,4 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 53
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 78
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,5 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) >= 79
AND   (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) <= 104
UNION 
SELECT SUM(QtyIn) as InStock
      ,SUM(QtyOut)as OutStock
	  ,6 as PeriodicGroup
FROM  EE_Question
WHERE (SELECT DATEDIFF(week,Date,(SELECT replace(convert(varchar, getdate(), 111), '/', '-'))) AS DiffDate) > 104 
ORDER BY PeriodicGroup

Open in new window

Results from your table
PortletPaulEE Topic AdvisorCommented:
That spreadsheet does not look like the source table(s) for stock movement, it appears to be the result of a query. It may be more efficient to deal with the source data than this summary.

But if this is all you have, please also supply the "expected result" of this sample
Date	Status	Qty_In	Qty_Out
2008-10-31	I	63	0
2009-01-14	O	0	63
2009-01-14	I	63	0
2009-06-22	O	0	63
2009-06-22	I	63	0
2009-06-22	I	100	0
2009-06-22	O	0	2
2010-07-28	O	0	16
2011-03-29	O	0	10
2012-06-07	I	25	0
2013-03-21	O	0	7
2013-07-01	O	0	50
2014-06-04	O	5	0

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
really rough and untested, and without the benefit of knowing what you really want to see:
DECLARE @dt AS DATE -- STRIP OFF TIME
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT 
      stocknumber
    , SUM(CASE WHEN [Date] >= wk.dt1                     then ISNULL(qty_in,0) + (ISNULL(qty_out,0)*-1) else 0 end) as ageto13
    , SUM(CASE WHEN [Date] >= wk.dt2 and [Date] < wk.dt1 then ISNULL(qty_in,0) + (ISNULL(qty_out,0)*-1) else 0 end) as age13to26
    , SUM(CASE WHEN [Date] >= wk.dt3 and [Date] < wk.dt2 then ISNULL(qty_in,0) + (ISNULL(qty_out,0)*-1) else 0 end) as age26to52
    , SUM(CASE WHEN [Date] >= wk.dt4 and [Date] < wk.dt3 then ISNULL(qty_in,0) + (ISNULL(qty_out,0)*-1) else 0 end) as age52to78
    , SUM(CASE WHEN [Date] >= wk.dt5 and [Date] < wk.dt4 then ISNULL(qty_in,0) + (ISNULL(qty_out,0)*-1) else 0 end) as age78to104
    , SUM(CASE WHEN [Date]                      < wk.dt5 then ISNULL(qty_in,0) + (ISNULL(qty_out,0)*-1) else 0 end) as age104up
FROM STOCK_TABLE AS S
CROSS JOIN (
            SELECT
                      DATEADD(WEEK,-13,@DT)  AS DT1
                    , DATEADD(WEEK,-26,@DT)  AS DT2
                    , DATEADD(WEEK,-52,@DT)  AS DT3
                    , DATEADD(WEEK,-78,@DT)  AS DT4
                    , DATEADD(WEEK,-104,@DT) AS DT5
            ) AS WK
GROUP BY
      stocknumber
;

Open in new window

It's relatively easy to calculate the needed date boundaries. These can then be "added" into the record by using a cross join (this does not affect the number of data rows as there is only 1 row in the cross join).

So, once we have the date boundaries available, use a set of case expressions and sum those calculations.
ABM SupportIT SupportAuthor Commented:
Thanks for the responses both!

Plusone3055, this is not the results I'm expecting to see.

I need to know the age of the 108 I have in stock, therefore based on the data supplied (Paul Maxwell) I would expect to see all 108 in the 104+ group as the 25 in on 07/06/2015 is 166 weeks old and the remaining 83 that came in as part of the 100 received on 22/06/2009 is 320 weeks old.

I hope I'm explaining this!

Thanks

Steve
PortletPaulEE Topic AdvisorCommented:
quite literally I would like to see your "expected result", this includes the layout

and it should be something that we can test our query against (i.e. all calcs done)

By the way, if you provide the table name and the real field names then any code we propose won't need much editing.
ABM SupportIT SupportAuthor Commented:
Paul, I hope the below is what you need:

The table name is STK_Movements and the stock number is SM_STOCK_CODE. The quantity in and out is SM_Quantity and the direction is determined by SM_STATUS being either 'I' or 'O'

The expected layout is as your SQL, so

SM_STOCK_CODE, ageto13, age13to26, age26to52, age52to78, age78to104, age104up

The data output I would expect to see, based on the data supplied would be:

JI24, 0, 0, 0, 0, 0, 108

Thanks

Steve
PortletPaulEE Topic AdvisorCommented:
Thanks. I don't have an ability to test right now, but hopefully it will get you working:
DECLARE @dt AS DATE -- STRIP OFF TIME
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT 
      S.SM_STOCK_CODE
    , SUM(CASE WHEN [Date] >= wk.dt1                     then ca.qty_in + ca.qty_out else 0 end) as ageto13
    , SUM(CASE WHEN [Date] >= wk.dt2 and [Date] < wk.dt1 then ca.qty_in + ca.qty_out else 0 end) as age13to26
    , SUM(CASE WHEN [Date] >= wk.dt3 and [Date] < wk.dt2 then ca.qty_in + ca.qty_out else 0 end) as age26to52
    , SUM(CASE WHEN [Date] >= wk.dt4 and [Date] < wk.dt3 then ca.qty_in + ca.qty_out else 0 end) as age52to78
    , SUM(CASE WHEN [Date] >= wk.dt5 and [Date] < wk.dt4 then ca.qty_in + ca.qty_out else 0 end) as age78to104
    , SUM(CASE WHEN [Date]                      < wk.dt5 then ca.qty_in + ca.qty_out else 0 end) as age104up
FROM STK_Movements AS S
CROSS APPLY (
            select
                    case when SM_STATUS = 'I' then SM_Quantity else 0 end as qty_in
                  , case when SM_STATUS = 'O' then SM_Quantity * -1 else 0 end as qty_out
             ) ca
CROSS JOIN (
            SELECT
                      DATEADD(WEEK,-13,@DT)  AS DT1
                    , DATEADD(WEEK,-26,@DT)  AS DT2
                    , DATEADD(WEEK,-52,@DT)  AS DT3
                    , DATEADD(WEEK,-78,@DT)  AS DT4
                    , DATEADD(WEEK,-104,@DT) AS DT5
            ) AS WK
GROUP BY
      S.SM_STOCK_CODE
;

Open in new window

Note if you don't want the data per stock number, remove that column from the select clause and remove the group by clause altogether. I have introduced a cross apply with the alias ca. This is useful as it allows us to define qty_in and qty_out as aliases, and then reuse those aliases as if they were real columns. The cross join is simply used to make the range dates available on every row.

If you do run this, and there are problems:
a. I will not know what you have run unless you include the query here (exactly! as you ran it)
b. provide the full error detail
PortletPaulEE Topic AdvisorCommented:
mmm, may be able to move the cross join into the cross apply, oh well.
ABM SupportIT SupportAuthor Commented:
Hi Paul

I ran it with a couple of minor changes! It initially fell over on the @dt declaration, cannot find data type DATE. It runs cleanly but the results are all zero. Below is the script I ran:

DECLARE @dt AS DATETIME -- STRIP OFF TIME
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT 
      S.SM_STOCK_CODE
    , SUM(CASE WHEN [SM_Date] >= wk.dt1                     then ca.qty_in + ca.qty_out else 0 end) as ageto13
    , SUM(CASE WHEN [SM_Date] >= wk.dt2 and [SM_Date] < wk.dt1 then ca.qty_in + ca.qty_out else 0 end) as age13to26
    , SUM(CASE WHEN [SM_Date] >= wk.dt3 and [SM_Date] < wk.dt2 then ca.qty_in + ca.qty_out else 0 end) as age26to52
    , SUM(CASE WHEN [SM_Date] >= wk.dt4 and [SM_Date] < wk.dt3 then ca.qty_in + ca.qty_out else 0 end) as age52to78
    , SUM(CASE WHEN [SM_Date] >= wk.dt5 and [SM_Date] < wk.dt4 then ca.qty_in + ca.qty_out else 0 end) as age78to104
    , SUM(CASE WHEN [SM_Date]						  < wk.dt5 then ca.qty_in + ca.qty_out else 0 end) as age104up
FROM STK_Movements AS S
CROSS APPLY (
            select
                    case when SM_STATUS = 'I' then SM_Quantity else 0 end as qty_in
                  , case when SM_STATUS = 'O' then SM_Quantity * -1 else 0 end as qty_out
             ) ca
CROSS JOIN (
            SELECT
                      DATEADD(WEEK,-13,@DT)  AS DT1
                    , DATEADD(WEEK,-26,@DT)  AS DT2
                    , DATEADD(WEEK,-52,@DT)  AS DT3
                    , DATEADD(WEEK,-78,@DT)  AS DT4
                    , DATEADD(WEEK,-104,@DT) AS DT5
            ) AS WK
Where S.SM_STOCK_CODE = 'JI24'
GROUP BY
      S.SM_STOCK_CODE
;

Open in new window

PortletPaulEE Topic AdvisorCommented:
try this, oh&  I used SM_Date instead of just Date for a column
DECLARE @dt AS DATETIME
SET @dt = DATEADD(day, DATEDIFF(day,0, GETDATE() ), 0) -- set to today, but strip off time
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT 
      S.SM_STOCK_CODE
    , SUM(CASE WHEN [SM_Date] >= wk.dt1                     then ca.qty_in + ca.qty_out else 0 end) as ageto13
    , SUM(CASE WHEN [SM_Date] >= wk.dt2 and [SM_Date] < wk.dt1 then ca.qty_in + ca.qty_out else 0 end) as age13to26
    , SUM(CASE WHEN [SM_Date] >= wk.dt3 and [SM_Date] < wk.dt2 then ca.qty_in + ca.qty_out else 0 end) as age26to52
    , SUM(CASE WHEN [SM_Date] >= wk.dt4 and [SM_Date] < wk.dt3 then ca.qty_in + ca.qty_out else 0 end) as age52to78
    , SUM(CASE WHEN [SM_Date] >= wk.dt5 and [SM_Date] < wk.dt4 then ca.qty_in + ca.qty_out else 0 end) as age78to104
    , SUM(CASE WHEN [SM_Date]						  < wk.dt5 then ca.qty_in + ca.qty_out else 0 end) as age104up
FROM STK_Movements AS S
CROSS APPLY (
            select
                    case when SM_STATUS = 'I' then SM_Quantity else 0 end as qty_in
                  , case when SM_STATUS = 'O' then SM_Quantity * -1 else 0 end as qty_out
             ) ca
CROSS JOIN (
            SELECT
                      DATEADD(WEEK,-13,@DT)  AS DT1
                    , DATEADD(WEEK,-26,@DT)  AS DT2
                    , DATEADD(WEEK,-52,@DT)  AS DT3
                    , DATEADD(WEEK,-78,@DT)  AS DT4
                    , DATEADD(WEEK,-104,@DT) AS DT5
            ) AS WK
Where S.SM_STOCK_CODE = 'JI24'
GROUP BY
      S.SM_STOCK_CODE
;

Open in new window

PortletPaulEE Topic AdvisorCommented:
Was able to trial these. I'm giving 2 variants because I'm not sure if you want periodic movement, or cumulative movement. I removed the cross join. Note both use [SM_Date] for the date column which I hope isn't just called [Date].

cumulative:
DECLARE @dt AS DATETIME
SET @dt = DATEADD(day, DATEDIFF(day,0, GETDATE() ), 0) -- set to today, but strip off time
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT 
      S.SM_STOCK_CODE
    , SUM(ca.qty_in + ca.qty_out) agecurrent
    , SUM(CASE WHEN [SM_Date] < ca.dt1 then ca.qty_in + ca.qty_out else 0 end) as age13
    , SUM(CASE WHEN [SM_Date] < ca.dt2 then ca.qty_in + ca.qty_out else 0 end) as age26
    , SUM(CASE WHEN [SM_Date] < ca.dt3 then ca.qty_in + ca.qty_out else 0 end) as age52
    , SUM(CASE WHEN [SM_Date] < ca.dt4 then ca.qty_in + ca.qty_out else 0 end) as age78
    , SUM(CASE WHEN [SM_Date] < ca.dt5 then ca.qty_in + ca.qty_out else 0 end) as age104
FROM STK_Movements AS S
CROSS APPLY (
            select
                      case when SM_STATUS = 'I' then SM_Quantity else 0 end as qty_in
                    , case when SM_STATUS = 'O' then SM_Quantity * -1 else 0 end as qty_out
                    , DATEADD(WEEK,-13,@DT)  AS DT1
                    , DATEADD(WEEK,-26,@DT)  AS DT2
                    , DATEADD(WEEK,-52,@DT)  AS DT3
                    , DATEADD(WEEK,-78,@DT)  AS DT4
                    , DATEADD(WEEK,-104,@DT) AS DT5
            ) AS ca
Where S.SM_STOCK_CODE = 'JI24'
GROUP BY
      S.SM_STOCK_CODE
;

Open in new window

periodic
DECLARE @dt AS DATETIME
SET @dt = DATEADD(day, DATEDIFF(day,0, GETDATE() ), 0) -- set to today, but strip off time
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT 
      S.SM_STOCK_CODE
    , SUM(CASE WHEN [SM_Date] >= ca.dt1                        then ca.qty_in + ca.qty_out else 0 end) as ageto13
    , SUM(CASE WHEN [SM_Date] >= ca.dt2 and [SM_Date] < ca.dt1 then ca.qty_in + ca.qty_out else 0 end) as age13to26
    , SUM(CASE WHEN [SM_Date] >= ca.dt3 and [SM_Date] < ca.dt2 then ca.qty_in + ca.qty_out else 0 end) as age26to52
    , SUM(CASE WHEN [SM_Date] >= ca.dt4 and [SM_Date] < ca.dt3 then ca.qty_in + ca.qty_out else 0 end) as age52to78
    , SUM(CASE WHEN [SM_Date] >= ca.dt5 and [SM_Date] < ca.dt4 then ca.qty_in + ca.qty_out else 0 end) as age78to104
    , SUM(CASE WHEN [SM_Date]						  < ca.dt5 then ca.qty_in + ca.qty_out else 0 end) as age104up
FROM STK_Movements AS S
CROSS APPLY (
            select
                      case when SM_STATUS = 'I' then SM_Quantity else 0 end as qty_in
                    , case when SM_STATUS = 'O' then SM_Quantity * -1 else 0 end as qty_out
                    , DATEADD(WEEK,-13,@DT)  AS DT1
                    , DATEADD(WEEK,-26,@DT)  AS DT2
                    , DATEADD(WEEK,-52,@DT)  AS DT3
                    , DATEADD(WEEK,-78,@DT)  AS DT4
                    , DATEADD(WEEK,-104,@DT) AS DT5
            ) AS ca
Where S.SM_STOCK_CODE = 'JI24'
GROUP BY
      S.SM_STOCK_CODE
;

Open in new window

ABM SupportIT SupportAuthor Commented:
Good Morning Paul

I'm using the periodic version here and it's almost there, however I think that possibly through my definition of requirement, the numbers are reporting in the wrong column!

The result from running the query is as follows:

SM_STOCK_CODE, ageto13, age13to26, age26to52, age52to78, age78to104, age104up
JI24, 0, 0, 0, 5, 0, 103

whereas I believe that from the supplied data, it should be
JI24, 0, 0, 0, 0, 0, 108

I think the issue here is the Qty_Out should not need to be used in this case!

I'll try to explain again, see if this makes more sense.

JI24 currently has 108 items of physical stock. From the STK_Movements file I need to determine the age of the 108 items so I think we only need to look at the QTY_In numbers. There were 25 booked in on the 07/06/2012 which makes those 25 166 weeks old so would fall into the 104+ group. There were 100 booked in on the 22/06/2019 which makes them 321 weeks old and would then go in the 104+ group, however that makes the total booked in at that stage greater than the physical stock, so based on the 25 already booked in, only 83 of that 100 remain in stock, resulting in 108 in the 104+ group.

I think it needs to read the data backwards maintaining a running total to ensure that it doesn't exceed the current physical stock.

The physical stock is recorded on another table, STK_Stock, keyed by STK_STKCODE for the join, in the STK_Physical column.

Apologies for omitting that key bit of information and thanks for your continued efforts

Steve
PortletPaulEE Topic AdvisorCommented:
A good set of sample data using the real table snd  column names will assist.

Then you need to msnually work throuh the sample to produce a set of expected results

Then you know what the calculations are as well
ABM SupportIT SupportAuthor Commented:
Hi Paul

I have attached the sample data and expected results for the STK_StockCode 'JI24', please let me know if that is suitable or you need more info.

I think I need an SQL course as I am VERY limited and this is baffling me....I could code this easily as an executable but unfortunately it's not suitable in this instance.

Steve
Ageing-data.xlsx
PortletPaulEE Topic AdvisorCommented:
It is a mistake, if you will pardon my frankness, to hide details.
Like the outward movements. Keep sample data in columns to get it into tables simply.
Not sure I can get a result of 108 without the outward records by the way.

It isn't great for testing to rely on a sample that produces almost all zeros.

Plus, the expected results. Do you really want them presented that way?
ABM SupportIT SupportAuthor Commented:
Hi Paul

I prefer frankness however I'm not sure I understand about 'hiding details' as after thinking about this further, I do do not believe the outward movements have any bearing on the result.

I understand what you are saying about most of the columns resulting in zeroes and as such I have prepared a further set of data for you which has more recent movements.

The expected results will be fine like that as I intend to pass them into a Crystal Report (I know how to do that)

Now to explain how the result set works. Item A01 has 54 items currently in stock. Working backwards from todays date, through the movements, the quantity of 1 booked in on 01/11/2014 is 41 weeks old so falls in the age26to52 column, giving a running total of 1 booked in. The quantity of 40 booked in on 18/06/2014 is 60 weeks old so falls in the age52to78 column, giving a running total of 41. The quantity of 30 booked in on 24/03/2014 is 73 weeks old and will also fall into the age52to78 column, however, this will give a running total 71 which is more than the current physical stock so only the 13 (the difference between the previous running total and the physical stock) should be added to the age52to78 column. This calculation will result in my expected results.

I hope that helps.

Steve
a01.xlsx
Scott PletcherSenior DBACommented:
Hmm, running totals are calculated from oldest period to most current period, not in reverse order.

I think this summary is rather easy overall, but what specifically are the ranges?  :
What do you mean by "week"?  Seven calendar days or a week starting on a given day?  
I also don't see how you can get a valid total if you double-count, which means that 0-13 weeks and 13-26 weeks is not really quite correct.  Is it 0-13 weeks, 14-26 -- OR -- is it 0-13 weeks, 13weeks+1day-26 weeks?
PortletPaulEE Topic AdvisorCommented:
run on 2015-08-15, the boundary dates are calculates as:
|        dt0 |        DT1 |        DT2 |        DT3 |        DT4 |        DT5 |
|------------|------------|------------|------------|------------|------------|
| 2015-08-09 | 2015-05-10 | 2015-02-08 | 2014-08-10 | 2014-02-09 | 2013-08-11 |

DECLARE @dt AS DATETIME
SET @dt = DATEADD(day, DATEDIFF(day,0, GETDATE() ), 0) -- set to today, but strip off time
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT
      convert(varchar(10), @dt ,121) as dt0
    , convert(varchar(10), DATEADD(WEEK,-13,@DT) ,121)  AS DT1
    , convert(varchar(10), DATEADD(WEEK,-26,@DT) ,121)  AS DT2
    , convert(varchar(10), DATEADD(WEEK,-52,@DT) ,121)  AS DT3
    , convert(varchar(10), DATEADD(WEEK,-78,@DT) ,121)  AS DT4
    , convert(varchar(10), DATEADD(WEEK,-104,@DT) ,121) AS DT5
;

Open in new window

The logic is take GETDATE(), set time to 00:00:00, then work out the beginning of the current week (Sunday, this logic may need to alter to suit your needs). Then once that reference dat is known, deduct the relevant weeks.

If sum the data "less than" each date
e.g.
the current position is the sum of data less than today,
the position as of beginning this year is the sum of all data less than that point, etc.

With that logic, from your sample data and the dates shown above, I get this result:
| SM_STOCK_CODE | agecurrent | age13 | age26 | age52 | age78 | age104 |
|---------------|------------|-------|-------|-------|-------|--------|
|           A01 |         54 |    54 |    68 |    71 |    14 |      7 |

DECLARE @dt AS DATETIME
SET @dt = DATEADD(day, DATEDIFF(day,0, GETDATE() ), 0) -- set to today, but strip off time
SET @dt = DATEADD(DD, 1 - DATEPART(DW, @DT), @DT) -- SET TO BEGINNING OF WEEK

SELECT 
      S.SM_STOCK_CODE
    , SUM(ca.qty_in + ca.qty_out) agecurrent
    , SUM(CASE WHEN [SM_Date] < ca.dt1 then ca.qty_in + ca.qty_out else 0 end) as age13
    , SUM(CASE WHEN [SM_Date] < ca.dt2 then ca.qty_in + ca.qty_out else 0 end) as age26
    , SUM(CASE WHEN [SM_Date] < ca.dt3 then ca.qty_in + ca.qty_out else 0 end) as age52
    , SUM(CASE WHEN [SM_Date] < ca.dt4 then ca.qty_in + ca.qty_out else 0 end) as age78
    , SUM(CASE WHEN [SM_Date] < ca.dt5 then ca.qty_in + ca.qty_out else 0 end) as age104
FROM STK_Movements AS S
CROSS APPLY (
            select
                      case when SM_STATUS = 'I' then SM_Quantity else 0 end as qty_in
                    , case when SM_STATUS = 'O' then SM_Quantity * -1 else 0 end as qty_out
                    , DATEADD(WEEK,-13,@DT)  AS DT1
                    , DATEADD(WEEK,-26,@DT)  AS DT2
                    , DATEADD(WEEK,-52,@DT)  AS DT3
                    , DATEADD(WEEK,-78,@DT)  AS DT4
                    , DATEADD(WEEK,-104,@DT) AS DT5
            ) AS ca
Where S.SM_STOCK_CODE = 'A01'
GROUP BY
      S.SM_STOCK_CODE
;

Open in new window


data:
CREATE TABLE STK_Movements 
    ([SM_STOCK_CODE] varchar(3), [SM_Date] datetime, [SM_Status] varchar(1), [SM_Quantity] int)
;
    
INSERT INTO STK_Movements 
    ([SM_STOCK_CODE], [SM_Date], [SM_Status], [SM_Quantity])
VALUES
    ('A01', '2012-04-03 00:00:00', 'I', 20),
    ('A01', '2012-06-14 00:00:00', 'O', 1),
    ('A01', '2012-06-22 00:00:00', 'O', 2),
    ('A01', '2012-06-28 00:00:00', 'O', 6),
    ('A01', '2012-09-24 00:00:00', 'I', 30),
    ('A01', '2012-09-24 00:00:00', 'O', 16),
    ('A01', '2012-10-05 00:00:00', 'O', 5),
    ('A01', '2012-11-14 00:00:00', 'O', 3),
    ('A01', '2012-12-11 00:00:00', 'O', 4),
    ('A01', '2013-01-10 00:00:00', 'O', 2),
    ('A01', '2013-01-22 00:00:00', 'O', 1),
    ('A01', '2013-02-28 00:00:00', 'O', 2),
    ('A01', '2013-03-07 00:00:00', 'O', 8),
    ('A01', '2013-03-07 00:00:00', 'I', 30),
    ('A01', '2013-03-07 00:00:00', 'O', 6),
    ('A01', '2013-04-02 00:00:00', 'O', 1),
    ('A01', '2013-04-05 00:00:00', 'O', 1),
    ('A01', '2013-04-19 00:00:00', 'O', 1),
    ('A01', '2013-05-09 00:00:00', 'O', 3),
    ('A01', '2013-05-21 00:00:00', 'O', 4),
    ('A01', '2013-06-10 00:00:00', 'O', 1),
    ('A01', '2013-06-26 00:00:00', 'O', 2),
    ('A01', '2013-07-18 00:00:00', 'O', 2),
    ('A01', '2013-07-19 00:00:00', 'O', 2),
    ('A01', '2013-08-16 00:00:00', 'O', 1),
    ('A01', '2013-08-21 00:00:00', 'O', 2),
    ('A01', '2013-09-05 00:00:00', 'O', 1),
    ('A01', '2013-09-10 00:00:00', 'I', 50),
    ('A01', '2013-09-10 00:00:00', 'O', 5),
    ('A01', '2013-10-01 00:00:00', 'O', 2),
    ('A01', '2013-10-16 00:00:00', 'O', 10),
    ('A01', '2013-11-04 00:00:00', 'O', 3),
    ('A01', '2013-11-06 00:00:00', 'O', 1),
    ('A01', '2013-11-19 00:00:00', 'O', 10),
    ('A01', '2013-12-09 00:00:00', 'O', 5),
    ('A01', '2014-01-07 00:00:00', 'O', 5),
    ('A01', '2014-01-15 00:00:00', 'I', 2),
    ('A01', '2014-02-20 00:00:00', 'O', 1),
    ('A01', '2014-02-28 00:00:00', 'O', 4),
    ('A01', '2014-03-24 00:00:00', 'I', 30),
    ('A01', '2014-04-29 00:00:00', 'O', 1),
    ('A01', '2014-05-01 00:00:00', 'O', 4),
    ('A01', '2014-05-13 00:00:00', 'O', 3),
    ('A01', '2014-06-18 00:00:00', 'I', 40),
    ('A01', '2014-11-01 00:00:00', 'I', 1),
    ('A01', '2014-11-12 00:00:00', 'O', 1),
    ('A01', '2014-12-09 00:00:00', 'O', 1),
    ('A01', '2014-12-19 00:00:00', 'O', 2),
    ('A01', '2015-02-19 00:00:00', 'O', 5),
    ('A01', '2015-03-04 00:00:00', 'O', 2),
    ('A01', '2015-03-04 00:00:00', 'O', 1),
    ('A01', '2015-03-05 00:00:00', 'O', 6)
;

Open in new window

also see: http://sqlfiddle.com/#!3/49879/5
ABM SupportIT SupportAuthor Commented:
Hi Paul

Apologies for the long response time, I've been away from the office!

Right, I've reviewed your results and they are not as I expect and I can now only put this down to my explanation of the requirement not being clear enough.

I'll try again.

Within your stored procedure you make no reference to the physical stock for 'A01', this is held on the STK_Stock table in the STK_Physical column, keyed by the STKCode column.

We need to discount all of the records from the STK_Movements where the SM_Status = 'O', these do not matter. Also, the remaining records need to be sorted by SM_Date and in a descending Order. Looping through each record in turn, the number of weeks between the current date and the SM_Date needs to be determined to ascertain which group the SM_Quantity will need to be placed.

A running total of stock added to the groups from the STK_Movements needs to be maintained with the sum of the aged columns only ever holding the same as the physical stock quantity. The loop can be terminated when this point is reached.

I hope this makes more sense

Cheers

Steve
PortletPaulEE Topic AdvisorCommented:
>>"I've reviewed your results and they are not as I expect  and I can now only put this down to my explanation of the requirement not being clear enough."

If you know what to expect then please DISPLAY by it EXAMPLE. Don't rely on words alone.

There is a method called "short self contained correct example" it requires 2 basic elements:
see http://sscce.org/

"sample data"
"expected result"

Describing what you want through words will simply lead to more disappointment. While you may still needs some words, providing an "expected result" will truly help communicate your need.

Notes:
"sample data" must be "per table" (I recommend using real table and column names)
the "expected result" must be based solely on the sample data provided
ABM SupportIT SupportAuthor Commented:
Hi Paul

I have supplied a full set of data, including column names and a full set of expected results as an attachement on 14/08/2015, however I have re-attached this to this post and expanded it to include all the detail I have.

a01.xlsx

I hope this helps further

Thanks

Steve
PortletPaulEE Topic AdvisorCommented:
I have tried to give you a query (2 variants, cumulative and periodic) that you can modify to suit your purpose.

>>"Within your stored procedure you make no reference to the physical stock for 'A01'"
there is no stored procedure, just a query
it does not use that table or field because there is no apparent reason to use either.

>>"We need to discount all of the records from the STK_Movements where the SM_Status = 'O', these do not matter. "
OK, l'll take your word for it, so just exclude the rows with a status of 'O'

good luck.
ABM SupportIT SupportAuthor Commented:
Thanks for all the help received, however I have not been able to arrive at the required result so I intending to pursue and alternative approach,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
Sorry. I've been unwell.
ABM SupportIT SupportAuthor Commented:
I have not been able to arrive at the required result so I intending to pursue and alternative approach,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.