Well, I think it's complex anyway -- you might not :)
TableDef:
CREATE TABLE CustTransaction s (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);
TransactionKey is the primary key, CustomerID and AmountSpent are both
indexed (non unique).
What I would like to do is, for all of the records in descending order
of "AmountSpen t" where "CustSelect ed = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000).
What I'm doing at the moment is a "SELECT * FROM CustTransaction s WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.
The closest I can get is:-
UPDATE CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
However, this mereley ensures only the top 50,000 customers by amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent ) =50000)".
Is it even possible to achieve what I'm trying to do?
Thanks in advance for any assistance offered!
--
SlowerThanYou 13 5647
Hi,
Consider the following sample data:
INSERT INTO CustTransaction s VALUES (1, 1000, 0)
INSERT INTO CustTransaction s VALUES (2, 1000, 1)
INSERT INTO CustTransaction s VALUES (2, 2500, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (3, 30000, 1)
INSERT INTO CustTransaction s VALUES (3, 17000, 1)
What is the expected result (the output of SELECT * FROM
CustTransaction s) ?
Also consider this sample data:
INSERT INTO CustTransaction s VALUES (1, 10000, 0)
INSERT INTO CustTransaction s VALUES (2, 20000, 1)
INSERT INTO CustTransaction s VALUES (2, 25000, 0)
INSERT INTO CustTransaction s VALUES (2, 2500, 0)
What is the expected result in this case ?
Razvan
Slower Than You wrote:
Well, I think it's complex anyway -- you might not :)
TableDef:
CREATE TABLE CustTransaction s (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);
TransactionKey is the primary key, CustomerID and AmountSpent are both
indexed (non unique).
What I would like to do is, for all of the records in descending order
of "AmountSpen t" where "CustSelect ed = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000).
What I'm doing at the moment is a "SELECT * FROM CustTransaction s WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.
The closest I can get is:-
UPDATE CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
However, this mereley ensures only the top 50,000 customers by amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent ) =50000)".
Is it even possible to achieve what I'm trying to do?
Thanks in advance for any assistance offered!
--
SlowerThanYou
You do not have a table at all; it is an attempt to mimic a deck of
punch cards. You confuse columns and fields, rows and records and use
the wrong data types.
do these transactions create a customer or a sale? Why is there DDL in
narratives? Why did youn use an IDENTITY columns? Why FLOAT for money?
CREATE TABLE SalesTransactio ns
(sales_nbr INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
sales_amt DECIMAL(12,2) NOT NULL);
>What I would like to do is, for all of the records [sic] in descending order of "AmountSpen t" where "CustSelect ed = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000). <<
Did you know that SQL has no Boolean data type? That using BIT is
proprietary and an awful coding practice? We updated punch cards like
you are doing because we had no choice about it.
>What I'm doing at the moment is a "SELECT * FROM CustTransaction s WHERE CustSelected = TRUE ORDER BY AmountSpent;", programatically looping through all the records [sic] until AmountSpent 50000, then continuine to loop through the remainder of the records [sic] setting CustSelected = FALSE.
You did not say what to do about ties; if I have five sales of
$50,000.00 which one would I mark?
give us a RELATIONAL spec and we can probably help you
Razvan Socol wrote:
Consider the following sample data:
INSERT INTO CustTransaction s VALUES (1, 1000, 0)
INSERT INTO CustTransaction s VALUES (2, 1000, 1)
INSERT INTO CustTransaction s VALUES (2, 2500, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (3, 30000, 1)
INSERT INTO CustTransaction s VALUES (3, 17000, 1)
What is the expected result (the output of SELECT * FROM
CustTransaction s) ?
Hi Razvan,
Thanks for responding. The expected result for the above sample data
would be:-
1, 1000, 0
2, 1000, 0
2, 2500, 0
1, 1000, 1
1, 1000, 1
3, 30000, 1
3, 17000, 1
To clarify this:-
1) The first row is completely ignored because its CustSelected field
is FALSE (as would be any other records where CustSelected = 0)
2) The rows WHERE CustSelected = 1 are sorted in descending order of
AmountSpent (where two or more records have equal values for
AmountSpent, the ordered of them is arbitrary - I don't care).
3) Any rows that would cause the sum of AmountSpent WHERE CustSelected
= 1 to exceed our selection criteria ($50,000) have their
CustSelected value set to 0.
>
Also consider this sample data:
INSERT INTO CustTransaction s VALUES (1, 10000, 0)
INSERT INTO CustTransaction s VALUES (2, 20000, 1)
INSERT INTO CustTransaction s VALUES (2, 25000, 0)
INSERT INTO CustTransaction s VALUES (2, 2500, 0)
What is the expected result in this case ?
Assuming our "target" figure is 50000 again:-
1, 10000, 0
2, 20000, 1
2, 25000, 0
2, 2500, 0
The three records where CustSelected = 0 are ignored. As a possible
point of additional interest, if the target figure was less than 20000
then row two would have had its CustSelected column set to 0 (because
this would have caused the "target" figure to be exceeded.
I hope I've done a better job of explaining my requirement this time
around!
--
SlowerThanYou
--CELKO-- wrote:
You do not have a table at all; it is an attempt to mimic a deck of
punch cards. You confuse columns and fields, rows and records and use
the wrong data types.
do these transactions create a customer or a sale? Why is there DDL
in narratives? Why did youn use an IDENTITY columns? Why FLOAT for
money?
Forget about the datatypes; they are largely irrelevant to the problem
I am trying to solve. I have abstracted the problem to attempt to make
it as easy to explain as possible. The real table I am trying to update
is, in fact, not called CustTransaction s and has nothing to do with
"customers" and it does, in fact, have a non-monetary floating point
value that is the focus of my update. You are reading more than I
intended into the column names I've used in my example.
Did you know that SQL has no Boolean data type? That using BIT is
proprietary and an awful coding practice? We updated punch cards like
you are doing because we had no choice about it.
No, I didn't know that SQL has no boolean data type, and that BIT is
proprietary, so thanks for that information. You can pretend it is an
integer type if you prefer. Again, do not read anything into the table
and field names I have used in my abstract example - just assume that
there is a True/False type flag that I need to record for each row
according to the critera I outlined.
What I'm doing at the moment is a "SELECT * FROM CustTransaction s
WHERE CustSelected = TRUE ORDER BY AmountSpent;", programatically
looping through all the records [sic] until AmountSpent 50000, then
continuine to loop through the remainder of the records [sic] setting
CustSelected = FALSE.
You did not say what to do about ties; if I have five sales of
$50,000.00 which one would I mark?
give us a RELATIONAL spec and we can probably help you
Please have a look at my reply to Razvan, which I hope describes the
problem I am trying to solve more accurately than my previous post
(which was not as coherent as it might have been, for which I
apologise).
--
SlowerThanYou
"Slower Than You" <no.way@josewro te in
news:11******** *******@iris.uk .clara.net:
Well, I think it's complex anyway -- you might not :)
TableDef:
CREATE TABLE CustTransaction s (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);
TransactionKey is the primary key, CustomerID and AmountSpent are both
indexed (non unique).
What I would like to do is, for all of the records in descending order
of "AmountSpen t" where "CustSelect ed = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000).
What I'm doing at the moment is a "SELECT * FROM CustTransaction s WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.
The closest I can get is:-
UPDATE CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
However, this mereley ensures only the top 50,000 customers by amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent ) =50000)".
Is it even possible to achieve what I'm trying to do?
See example 4 (cumulative sum) of http://www.databasejournal.com/featu...0894_3373861_2
HTH
--
For e-mail address, remove the XXs
Slower Than You wrote:
Razvan Socol wrote:
Consider the following sample data:
INSERT INTO CustTransaction s VALUES (1, 1000, 0)
INSERT INTO CustTransaction s VALUES (2, 1000, 1)
INSERT INTO CustTransaction s VALUES (2, 2500, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (3, 30000, 1)
INSERT INTO CustTransaction s VALUES (3, 17000, 1)
What is the expected result (the output of SELECT * FROM
CustTransaction s) ?
Hi Razvan,
Thanks for responding. The expected result for the above sample data
would be:-
1, 1000, 0
2, 1000, 0
2, 2500, 0
1, 1000, 1
1, 1000, 1
3, 30000, 1
3, 17000, 1
The above result has a sum of 49000. From your narrative, I would
expect a result which has a sum of 49500, for example this:
1, 1000, 0
2, 1000, 0
2, 2500, 1
1, 1000, 0
1, 1000, 0
3, 30000, 1
3, 17000, 1
Which one is the correct result ?
Razvan
Razvan Socol wrote:
>
Slower Than You wrote:
Razvan Socol wrote:
Consider the following sample data:
>
INSERT INTO CustTransaction s VALUES (1, 1000, 0)
INSERT INTO CustTransaction s VALUES (2, 1000, 1)
INSERT INTO CustTransaction s VALUES (2, 2500, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (1, 1000, 1)
INSERT INTO CustTransaction s VALUES (3, 30000, 1)
INSERT INTO CustTransaction s VALUES (3, 17000, 1)
>
What is the expected result (the output of SELECT * FROM
CustTransaction s) ?
Hi Razvan,
Thanks for responding. The expected result for the above sample data
would be:-
1, 1000, 0
2, 1000, 0
2, 2500, 0
1, 1000, 1
1, 1000, 1
3, 30000, 1
3, 17000, 1
The above result has a sum of 49000. From your narrative, I would
expect a result which has a sum of 49500, for example this:
1, 1000, 0
2, 1000, 0
2, 2500, 1
1, 1000, 0
1, 1000, 0
3, 30000, 1
3, 17000, 1
Which one is the correct result ?
You are absolutely right - I was a little to hasty in putting my
response together. The sum of 49500 is correct.
--
SlowerThanYou
Chris Cheney wrote:
"Slower Than You" <no.way@josewro te in
news:11******** *******@iris.uk .clara.net:
Well, I think it's complex anyway -- you might not :)
TableDef:
CREATE TABLE CustTransaction s (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);
TransactionKey is the primary key, CustomerID and AmountSpent are
both indexed (non unique).
What I would like to do is, for all of the records in descending
order of "AmountSpen t" where "CustSelect ed = TRUE", set
CustSelected to FALSE such that the sum of all the AmountSpent
records with CustSelected = TRUE is no greater than a specified
amount (say $50,000).
What I'm doing at the moment is a "SELECT * FROM CustTransaction s
WHERE CustSelected = TRUE ORDER BY AmountSpent;", programatically
looping through all the records until AmountSpent 50000, then
continuine to loop through the remainder of the records setting
CustSelected = FALSE. This does exactly what I want but is slow
and inefficient. I am sure it could be done in a single SQL
statement with subqueries, but I lack the knowledge and experience
to figure out how.
The closest I can get is:-
UPDATE CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
However, this mereley ensures only the top 50,000 customers by
amount spent remain "selected", not the top "X" customers whose
total spend is $50,000. I really need to replace the "SELECT TOP
50000" with some form of "SELECT TOP (X rows until sum(AmountSpent )
=50000)".
Is it even possible to achieve what I'm trying to do?
See example 4 (cumulative sum) of http://www.databasejournal.com/featu...p/10894_337386
1_2
HTH
Ahah! That helped enormously - thanks, much appreciated.
--
SlowerThanYou
>No, I didn't know that SQL has no boolean data type, and that BIT is proprietary, so thanks for that information. You can pretend it is an integer type if you prefer. Again, do not read anything into the table and field [sic] names .. <<
O)kay. You have SERIOUS conceptual problems with SQL and RDBMS. The
reason that SQL has no BOOLEAN data types is one of those "mathematic al
foundations" things that has to do with NULLs, 3-valued logic and
logic. In 25 words or less, we discover a state of being via
predicates rather than by looking for a flag.
In procedural, step-by-step file system models you set flags in step
(n) to pass control information to step (n+1) of the process. In the RM
model, multiple users can change the basic facts of a schema and thus
the criteria of the subset, so we do not store computed columns. You
compute subset membership at run time.
Fields have mean because of the program that reads them; columns have a
domain, a value and constraints in the schema -- totally separate from
any program that uses them -- which give them meaning.
It does not matter if you use a Standard data type; you are still not
programming with relational data model. Think in terms of predicates,
sets and declarations, not flags, sequences and procedures. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: John Baker |
last post by:
Hi:
As those who have looked at this newsgroup recently will realize, I am a neophyte with
Access, although I have experienced with Approach (the Lotus product). There are things I
could easily do in Approach that appear to require some subtle and complex manipulation in
Access. Herein lies my present problem.
I have a number of tables, including one called "tblPO", which contains purchase orders.
Some of the details of these change...
|
by: EJC |
last post by:
Hi Folks,
I've been trying to build a query from a form of dialogue boxes I
have created that holds search criteria for my main database. I have
been able to get the query to retrieve the data selected in the
relevant dialogue boxes and perform the query on these boxes. However
my problem lies in the fact that if no choice is selected in the
dialogue box I want the query to default (or get passed) a wildcard
that will mean it will...
|
by: Jack Schitt |
last post by:
I thought I was starting to get a handle on Access, until I tried
doing something useful...now I'm stuck. I have a DB with two tables -
to keep it simple I'll say that one is an Employee File (Employees),
and the other is an Address File (Addresses) linked by SSN. I've set
Addresses as a Lookup Table - If the user starts typing in the SSN it
should pull up the Employees records.
I'm getting stuck in the Data Entry form. When I type in...
|
by: Siv |
last post by:
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
|
by: rola |
last post by:
Hi Group!
I am having a problem of using SUM under UPDATE statement. I understand
that SQL does not allow me to use SUM in UPDATE, but unfortunately, I
can not find a way to get around it. Can someone please give me some
idea? Thanks a million!
--Here is the criteria:
Under the same PORTFOLIO_ID, if the ACCOUNT_OPENDATE is in the same
month as the PORTFOLIO_OPENDATE, then sum the account_openamt for the
| |
by: Siv |
last post by:
Hi,
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason for this being to stop
the user thinking the application has frozen when in fact it is just waiting
for a long SP to complete. Another reason for doing it like this is that I
also have had a problem in the past where the SP takes longer than the...
|
by: arion |
last post by:
Hello,
I am working on a very complex database and now I am trying to create an update query. The query is showing too few records. I am trying to update data in the table "inventory" with data from "workorder" table. The query is just copying the last workorder. I would like to update all new workorders to the inventory (can be until 100 records).
I would really appreciate your help! Thank you!
Imke
|
by: pbd22 |
last post by:
hi.
I am having probelms with an update statement. every time
i run it, "every" row updates, not just the one(s) intended.
so, here is what i have. i have tried this with both AND and OR
and neither seem to work.
i dont know why this is elluding me, but i'd appreciate help with the
solution.
|
by: Slower Than You |
last post by:
I am trying to write an SQL UPDATE statement for an MSAccess table and
am having some problems getting my head around it. Can anyone help?
TableName: CustTransactions
TransactionKey AutoNumber (Primary Key)
CustomerID Long Integer (Non-unique index)
AmountSpent Double
CustSelected Boolean
What I would like to do is, for all of the records in descending order
|
by: koehlerc14 |
last post by:
So here is the deal,
I am attempting to make what is the most complex form i have made yet. It really is not much, but as an amatuer it is a little overwhelming. Here's a few critical background point.s
I have 2 Master Databses - I will be linking in only 1 table from each - tblMaster
Each record contains demographic data and about 40 "variable" fields which consist of a name for each variable, and its data
Variable Field 20-29 -...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |