Was the item 'active' in a certain week - Array/Procedure/View???

[suzey]

I am a complete novice in SQL (2000) and wonder if someone could offer some guidance.

I would like to create a view or procedure, or something else, not sure what is best that would find out the status for each item at a particular point in the year ie: during week 24, how many items were complete, active, rejected, cancelled.

All of the data exists in one table. Each item has a start date and when completed a finish date. When they are in a rejected status, I have a flag in the same table that changes.

The status of the items can change during each week, so an item may have been rejected in week 23, then made active again in week 24 and cancelled in week 25.

So for each item, I'd like to do something like this :

When was the item active? :
item start date end date wk1 wk2 wk3 wk4 wk5
ABC 03/01/05 31/03/05 yes yes yes yes yes
DEF 10/01/05 14/01/05 no yes no no no
GHI 17/01/05 31/03/05 no no yes yes yes
TOTAL 1 2 2 2 2

Was the item rejected? :
item start date end date wk1 wk2 wk3 wk4 wk5
ABC 03/01/05 31/03/05 no no yes no no
DEF 10/01/05 14/01/05 no no no no no
GHI 17/01/05 31/03/05 no no yes no no
TOTAL 0 0 2 0 0

Was the item completed? :
item start date end date wk1 wk2 wk3 wk4 wk5
ABC 03/01/05 31/03/05 no no no no no
DEF 10/01/05 14/01/05 no yes no no no
GHI 17/01/05 31/03/05 no no no no yes
TOTAL 0 1 0 0 1

And then summarise the data
wk1 wk2 wk3 wk4 wk5
Active 1 2 2 2 2
Rejected 0 0 2 0 0
Completed 0 1 0 0 1

I can easily get the number completed, and rejected in a week, as I'm only looking at one date, but I was not sure how to find out when an item was active based on the start and finish dates.

Any help would be really appreciated.

 

[sdempsey]

You can use datadiff function that works out the number of day, months and years between a start and finish date.

Datediff('m', [database column with start date], [database column with end date])

Not sure if this will help.

 

[suzey]

Thanks sdempsey,

I don't think the datediff function will help as I need to know for multiple weeks if the item was active in that week, even though it could be completed in the current week.


Sue