find data point with value >0 & begin clculations 1

[s0224317]

I have a table with the following 3 fields: Item#, Date (year-Month), Units sold

For each item there are 24 months of data. I need to find the first data in the data set where the value is greater than 0 & calculate the standard deviation for the range consisting of that month plus the subsequent 11 months.

Example:

Item Date Value
001 2006-11 0
001 2006-12 0
001 2007-01 3
001 2007-02 0
001 2007-03 6
001 2007-04 2


so for this data set I need a code that will find 2007-01 which has value greater than 0 & begin calculating standard deviation for the subsequent 11 months (1+11= 12 total months)

Each item will potnetially have a different month as starting point.

I hope this is clear. Please let me know if any furhter clarifications are needed

 

[VRoscioli]

How about something like:

SELECT Item, StD(Val) 
    FROM tblTable INNER JOIN 
        (SELECT Item, min(Date) AS Start_Date 
            FROM tblTable 
            WHERE Value > 0 
            GROUP BY Item) A
        ON tblTable.Item = A.Item
    WHERE tblTable.Date Between A.Start_Date and DateAdd("m",11,A.Start_Date)
    GROUP BY Item

-V

 

[VRoscioli]

Oops... Should be StDev(), not StD()


-V

 

[s0224317]

Thanks for your help so far. I tailored your code into the following:

Option Compare Database
SELECT Item Number, StDev(Val)
FROM tbl_UsageandFcst INNER JOIN
(SELECT Item Number, min(Date) AS Start_Date
FROM tbl_UsageandFcst
WHERE Value > 0
GROUPBY Item Number)tbl_UsageandFcst
ON tbl_UsageandFcst.Item Number = tbl_UsageandFcst.Item Number
WHERE tbl_UsageandFcst.Date Between tbl_UsageandFcst.Year & Date and DateAdd("m",11, tbl_UsageandFcst.Year & Date)
GROUP BY Item Number


Issue is I get an end "Expected End Statement" error before the end parenthesis (highlighting "Number".

 

[PHV]

Are you aware thay you wrote SQL code in a VBA module ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[s0224317]

No. I wasn't aware of that. Thanks.