Question: Sample sets and how do I manage them

[dkintheuk]

Hi everyone,

I have a table with a set of accounts in that contains one row for each
account for each time it has been audited. (this may not include every
account we deal with)

Each month I want to select, from a separate and full list of accounts
that i import to the database, a random selection of new accounts to be
audited this month. Hence, a row for each one needs to be added to the
original database and the most recent entry updated (if it has been
audited before).

I don't want to select an account for re-audit within 3 months (for
example) and the accounts have a category. I want the sample selection
to be variable for each category i.e. 12 account of type A, 25 accounts
of type B etc.

I'm currently doing this with query upon query upon query (about 16 at
the moment) and can't help thinking that there must be a more logical
way of doing this in code or maybe by using more effective queries...

I'm looking for advice on the approach i should take not really looking
for code samples but will take advice on that if it is provided.

Any good ideas out there?

Thanks,

Rob.

[Arno R]

"dkintheuk" <rm*******@fire net.uk.com> schreef in bericht news:11******** *************@o 13g2000cwo.goog legroups.com...

Hi everyone,

I have a table with a set of accounts in that contains one row for each
account for each time it has been audited. (this may not include every
account we deal with)

Each month I want to select, from a separate and full list of accounts
that i import to the database, a random selection of new accounts to be
audited this month. Hence, a row for each one needs to be added to the
original database and the most recent entry updated (if it has been
audited before).

I don't want to select an account for re-audit within 3 months (for
example) and the accounts have a category. I want the sample selection
to be variable for each category i.e. 12 account of type A, 25 accounts
of type B etc.

I'm currently doing this with query upon query upon query (about 16 at
the moment) and can't help thinking that there must be a more logical
way of doing this in code or maybe by using more effective queries...

I'm looking for advice on the approach i should take not really looking
for code samples but will take advice on that if it is provided.

Any good ideas out there?

Thanks,

Rob.

Hi Rob,
If you need 16 query's for this, there is something wrong in your approch.
I think a UNION-query is what you need here.

I suppose you have a table called TblAccounts (AccountID-AccountType)
and a Table Called TblAudits (AccountID-AuditDate) (or a query with the last AuditDate for each Account)

3 months is ~ 100 days so something like:

SELECT TOP 12 TblAccounts.Acc ountID, TblAudits.Audit Date
FROM TblAccounts LEFT JOIN TblAudits ON TblAccounts.Acc ountID = TblAudits.Accou ntID
WHERE (((TblAudits.Au ditDate) Is Null Or (TblAudits.Audi tDate)>Date()-100) AND ((TblAccounts.A ccountType)="A" ))
UNION SELECT TOP 25 TblAccounts.Acc ountID, TblAudits.Audit Date
FROM TblAccounts LEFT JOIN TblAudits ON TblAccounts.Acc ountID = TblAudits.Accou ntID
WHERE (((TblAudits.Au ditDate) Is Null Or (TblAudits.Audi tDate)>Date()-100) AND ((TblAccounts.A ccountType)="B" ))
.......
UNION SELECT Top xx ........ where ...... AccountType) = "Z"))

will give you what you need.
You will have to adapt this SQL as needed of course !

Arno R

[dkintheuk]

Arno R wrote:

"dkintheuk" <rm*******@fire net.uk.com> schreef in bericht news:11******** *************@o 13g2000cwo.goog legroups.com...

Hi everyone,

I have a table with a set of accounts in that contains one row for each account for each time it has been audited. (this may not include every account we deal with)

Each month I want to select, from a separate and full list of accounts that i import to the database, a random selection of new accounts to be audited this month. Hence, a row for each one needs to be added to the original database and the most recent entry updated (if it has been
audited before).

I don't want to select an account for re-audit within 3 months (for
example) and the accounts have a category. I want the sample selection to be variable for each category i.e. 12 account of type A, 25 accounts of type B etc.

I'm currently doing this with query upon query upon query (about 16 at the moment) and can't help thinking that there must be a more logical way of doing this in code or maybe by using more effective queries...
I'm looking for advice on the approach i should take not really looking for code samples but will take advice on that if it is provided.

Any good ideas out there?

Thanks,

Rob.

Hi Rob,
If you need 16 query's for this, there is something wrong in your

approch. I think a UNION-query is what you need here.

I suppose you have a table called TblAccounts (AccountID-AccountType) and a Table Called TblAudits (AccountID-AuditDate) (or a query with the last AuditDate for each Account)
3 months is ~ 100 days so something like:

SELECT TOP 12 TblAccounts.Acc ountID, TblAudits.Audit Date
FROM TblAccounts LEFT JOIN TblAudits ON TblAccounts.Acc ountID = TblAudits.Accou ntID WHERE (((TblAudits.Au ditDate) Is Null Or (TblAudits.Audi tDate)>Date()-100) AND ((TblAccounts.A ccountType)="A" )) UNION SELECT TOP 25 TblAccounts.Acc ountID, TblAudits.Audit Date
FROM TblAccounts LEFT JOIN TblAudits ON TblAccounts.Acc ountID = TblAudits.Accou ntID WHERE (((TblAudits.Au ditDate) Is Null Or (TblAudits.Audi tDate)>Date()-100) AND ((TblAccounts.A ccountType)="B" )) ......
UNION SELECT Top xx ........ where ...... AccountType) = "Z"))

will give you what you need.
You will have to adapt this SQL as needed of course !

Arno R

Arno, Thanks for that,

That's kind of what i thought about but i inherited some of this and
built other parts. Each query is doing a little bit of incremental
change to the data to allow me to work with it.

Of course you're right about the UNION approach. I had though about
that but, guess what, i'd never heard of the TOP function in SQL (never
had need before). That solves a great deal of problems and makes the
query easier to handle.

I will need a cuople of preparatory queries though but now that is far
less onerous. Thanks for the advice can't think why i didn't get closer
befoer... never mind!

Cheers,

Rob.

[dkintheuk]

Just a small update...

I now have to handle this in 3 stages:

1. Prepare the new records so that they have unique random numbers
attached.
2. Prepare the existing table so that i report only one row per account
(even if it has been audited more than once before)
3. Join these together as per your query but make sure I ORDER BY the
unique number so that I force a better random set and append this data
to the original table!

Easy now I come to think about it...

As the categories can vary, i've written a funky loop to handle that
and create the long UNION query before running it.

Good job mate, helped me loads!

Rob.