473,549 Members | 2,815 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question: Sample sets and how do I manage them

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.

Nov 13 '05 #1
3 1547

"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
Nov 13 '05 #2

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.

Nov 13 '05 #3
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.

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

43
2583
by: nospam | last post by:
I got three (3) files (1) Untitled.aspx (2) Untitled.aspx.1.cs (3) Untitled.aspx.2.cs These three files must be used together to make file #1, Untitled.aspx, page work via J.I.T. when the User first hits Internet Explorer 6.0 on your browser.
44
4142
by: lester | last post by:
a pre-beginner's question: what is the pros and cons of .net, compared to ++ I am wondering what can I get if I continue to learn C# after I have learned C --> C++ --> C# ?? I think there must be many know the answer here. thanks
11
2973
by: nrk | last post by:
Isn't: char s = "--4"; char *endptr; strtol(s, &endptr, 0); supposed to return 0 and set endptr to s? I have run into an implementation (not gcc, gcc does what I expect) that is returning LONG_MIN!! I am afraid to see what it did to endptr :-)
1
2005
by: Jim | last post by:
I have created a windows form that contains several tab pages which contain a panels. On a tab page I am trying to dynamically create a series of buttons in that pages panel. I am failing because I can not find the proper way to point to the specific tab page and its panel when creating the buttons. I also need to dynamically change the...
11
2533
by: Peter M. | last post by:
Hi all, I'm currently designing an n-tier application and have some doubts about my design. I have created a Data Access layer which connects to the database (SQL Server) and performs Select, update, delete and inserts. I use dataset objects to pass data to and from the DAL. In my GUI (windows forms), I use databinding to bind controls...
3
1455
by: Henry | last post by:
I know it is possible to store dynamic propterties for applications in XML files. The app.config and the web.config files can be used to store AppSettings... I am just wondering how far one can go with this technique. In my case, what I am interested in doing is to store information about reports and report parameters that I might want to...
73
4224
by: JoeC | last post by:
I am writing a game and I am having a challenge with my combat function. All I want to do is find out how to group pieces that are in the same space. There are two sides and all the units that are in the same space fight. I want to add up the attack factors and defending factors in the same space then figure out the odds so I can roll...
3
1672
by: Ara Kooser | last post by:
Hello all, I hope I am posting this correctly. I am running Python 2.4.2 on Slackware 11.0 using IDLE. I am in the process of learning python so I am writing a text adventure game using functions and global variables only. I know there is a better way to do this but that is for later. When I run the python program it works fine until I...
4
2706
by: Ara Kooser | last post by:
I am working on a text adventure game for python to get back into python programming. My version 0.1 used only functions so I could get familiar with how those work. I want to move beyond that. I am not sure what would be a good Python way of handling this. I was wondering if classes would help? What things should be included in the main...
0
7551
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7477
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7750
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
6084
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5396
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5118
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3503
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1084
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
790
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.