I'm preparing a drawing issue register. I've been doing this for years but utilising formal issues in a separate table. I need VIRTUAL issues to be produced from the data. A virtual issue, the column (In
) in my pivot table, is a unique set of recipients (Rn
) for a date. As you can see some drawings (not shown) will have been issued on the same date (Dn
) but to a different unique set of recipients, or on a different date to the same set:
This should be simple but I can't get my head round it. Maybe this is the onset of middle age...
I want to take (simplified)
and create 3 derived tables with the magic virtual IssueNo:
I'm comfortable with cursors, table variables etc. Less so with the pivot command and ranking but happy to look at these methods. Just need a steer in the right direction!
Then I can generate the register.
I'm familiar with this process.
The best way of helping us to help you is to provide sample data and an expected result.
I guess that means some records from tblRevIssues and from those records what result you expect.
"the magic virtual IssueNo:"
so issue numbers don't have to be unique?
i.e. if one runs the report today you get an Issue-1
, and if you run it next month for different data you also get an Issue-1
Is ordering this IssueNo by fldIssuedDate expected?