including in count if "still open"

[sissajones]

Hi All,
I have a dataset of service orders (SO) - the pertinent fields are OpenDate, ClosedDate.

I am trying to do essentially a crosstab type of result that would count "carryover" records in each month that it was a carryover.

**Carryover service orders are defined as not closed in the same month that they were opened. I already have the records filtered to those that meet this criteria.

The result that I want would result in something similar to this:

Shop Jan Feb Mar Apr May
ABC 3 5 3 5 2
DEF 5 3 1 2 4

The count shown above would include an SO if it was not opened in that month - it could be closed in that month. The problem comes when I have to count one that crosses multiple months - as it would be historically considered a "carryover"

e.g. a record opened in Jan and closed in April would be considered a "carryover" in Feb, Mar & Apr.

This is my very first post and I hope I have explained myself OK - thank you for your time and help!

 

[SydneyGeek]

Hi Sissa, welcome to the Board!

Create an expression like IIf(IsNull([ClosedDate]),"Yes",IIf(([ClosedDate]>[OpenDate]) And (Month([ClosedDate])<>Month([OpenDate])),"Yes","No"))

Filter on "Yes" and complete the crosstab.

Denis

 

[sissajones]

Hi Denis,

Thank you for your help - I'm not sure that will work - let me show the SQL below:

TRANSFORM Count(qry_SV_CarryoverRecordsOnly.WORK_ORDER) AS CountOfWORK_ORDER
SELECT qry_SV_CarryoverRecordsOnly.WORK_GROUP, Count(qry_SV_CarryoverRecordsOnly.WORK_ORDER) AS [Total Of WORK_ORDER]
FROM qry_SV_CarryoverRecordsOnly
WHERE (((qry_SV_CarryoverRecordsOnly.RAISED_YEAR)=[enter year]))
GROUP BY qry_SV_CarryoverRecordsOnly.WORK_GROUP
PIVOT Format([wo_closed_date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I'm trying to have the record counted in each month that it is a carry over - so if it was opened in Jan and closed in May - it would be "counted" in Feb, Mar, Apr & May. I'm not sure how your IIF statement would ... I'm sure that its got to be something I put in the Count statement above to identify if the record should be included - I'm just not sure how to associate that with the appropriate column in the crosstab.

Again - thank you for your time!

 

[SydneyGeek]

The Iif statement isn't directly associated with any columns.
Insert it into the query, change the grouping to Where, and uncheck the tick so it doesn't show.

As for doing the month by month, you could cheat...
Create another table with one field called MonthEnd. Enter the last day of each month for the year.
Add this table to the query but don't join it. You will get a cartesian join, with 12 records for each original record. By placing the MonthEnd field in the grid you can check whether any SO is open, month by month. That should then give you the pivot result that you need; but you may need to build the detail query first, then base the pivot on that.

Denis

 

[sissajones]

Denis - thank you - funnily enough I came to the same conclusion about 10 minutes before I read your suggestion - so I was already doing the snoopy happy dance

Thanks for your help
Sissa