Hi, here is a query which returns rolling sum for past5days.The resulting table has threee columns,namely,
MyDateFiled ,MyValue,Past5DaySum
SELECT tblData.MyDateField, tblData.MyValue,
(SELECT SUM(a.MyValue)
FROM tblData As a
WHERE DateDiff("d",a.MyDateField,tblData.MyDateField)
Between 0 and 4)
AS Past5DaySum
FROM tblData;
Someone help me in modifying the above query ,so that ,a Type(1,2,3) can be assigned to each Past5DaySum
based on the following criteria.Is,it possible to use IIF function for three variables ?,so as tp get a new table(Mydate,MyValue,PastDaySum,Type)
Type,LeanPeriod,GoodPeriod
1, A<5 , A<20
2, 5<=A<=10, 20<=A<=40
3, A>10, A>40
LeanPeriod=Jun to Nov
GoodPeriod=Dec to May
MyDateFiled ,MyValue,Past5DaySum
SELECT tblData.MyDateField, tblData.MyValue,
(SELECT SUM(a.MyValue)
FROM tblData As a
WHERE DateDiff("d",a.MyDateField,tblData.MyDateField)
Between 0 and 4)
AS Past5DaySum
FROM tblData;
Someone help me in modifying the above query ,so that ,a Type(1,2,3) can be assigned to each Past5DaySum
based on the following criteria.Is,it possible to use IIF function for three variables ?,so as tp get a new table(Mydate,MyValue,PastDaySum,Type)
Type,LeanPeriod,GoodPeriod
1, A<5 , A<20
2, 5<=A<=10, 20<=A<=40
3, A>10, A>40
LeanPeriod=Jun to Nov
GoodPeriod=Dec to May
