Dear group,
I need to create a stored procedure to return some data. The trick is the
data needs to be AGGREGATED with a SUM of commissions against each line for
each broker...
I have a table (TRADE) with the following data:
trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
1, 1, 1, 150, 13/06/2006
2, 2, 1, 100, 13/06/2006
3, 1, 1, 75, 14/06/2006
4, 1, 2, 165, 14/06/2006
5, 3, 1, 33.50, 14/06/2006
I want to display (for a DataTable to be used in a Crystal Report) a grid
where the headers will be:
Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id =
2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
(where tradeType_id = 2), Sum Monthly Total.
So that the query, when run on (14/06/2006), will look like:
1, 75, 165, 240, 225, 165, 190
2, null, null, null, 100, null, 100
3, 33.50, null, 33.50, 33.50, null, 33.50
The concept here is that I have a table which contains trades that a broker
has made. Each trade has a commission_amount column and a datestamp. I need
to be able to produce a report which has daily totals for different trade
types, but where the data is AGGREGATED by broker_id. All the SQL I've been
coming up with has been a total mess.
Can anyone assist with the above problem?
Many thanks!
MikeLiddle,
I think the trickiest thing here is the grouping. Is the monthly
total the running total since the first of the month, or just the sum
or trade type 2 records for a broker on a given day?
You can pretty easily group by date, broker id, and trade type.
SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
sumcom
FROM TRADE
GROUP BY broker_id, tradetype_id, datestamp
but the most straightforward way to get it into the format you want is
to do two subqueries and join them back together. But, what that looks
like will depend on whether you're looking for a running total or not.
Ion
Liddle Feesh wrote:
> Dear group,
> I need to create a stored procedure to return some data. The trick is the
> data needs to be AGGREGATED with a SUM of commissions against each line fo
r
> each broker...
> I have a table (TRADE) with the following data:
> trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
> 1, 1, 1, 150, 13/06/2006
> 2, 2, 1, 100, 13/06/2006
> 3, 1, 1, 75, 14/06/2006
> 4, 1, 2, 165, 14/06/2006
> 5, 3, 1, 33.50, 14/06/2006
> I want to display (for a DataTable to be used in a Crystal Report) a grid
> where the headers will be:
> Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id
=
> 2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
> (where tradeType_id = 2), Sum Monthly Total.
> So that the query, when run on (14/06/2006), will look like:
> 1, 75, 165, 240, 225, 165, 190
> 2, null, null, null, 100, null, 100
> 3, 33.50, null, 33.50, 33.50, null, 33.50
> The concept here is that I have a table which contains trades that a broke
r
> has made. Each trade has a commission_amount column and a datestamp. I nee
d
> to be able to produce a report which has daily totals for different trade
> types, but where the data is AGGREGATED by broker_id. All the SQL I've bee
n
> coming up with has been a total mess.
> Can anyone assist with the above problem?
> Many thanks!
> Mike|||<ionFreeman@.gmail.com> wrote in message
news:1150320207.374658.305960@.f6g2000cwb.googlegroups.com...
> Liddle,
> I think the trickiest thing here is the grouping. Is the monthly
> total the running total since the first of the month, or just the sum
> or trade type 2 records for a broker on a given day?
There are only four aggregated calculated fields, those are the daily totals
for trade_Type 1 and 2 and the monthly totals for trade_Type 1 and 2. The
sum is just an addition of those two fields and can be calculated easily by
hand.
> You can pretty easily group by date, broker id, and trade type.
> SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
> sumcom
> FROM TRADE
> GROUP BY broker_id, tradetype_id, datestamp
> but the most straightforward way to get it into the format you want is
> to do two subqueries and join them back together. But, what that looks
> like will depend on whether you're looking for a running total or not.
Cheers, Jon - that's close, but not quite right. I'm looking for a distinct
group, so that if there are only 2 broker_id's, there will be only two rows
and the SUM() data will be aggregated on those rows.
Thanks!sql
Showing posts with label sum. Show all posts
Showing posts with label sum. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Help With Slow SQL Query
This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the p
rimary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I sol
ved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered inde
x scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple
I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know abo
ut
| your data or needs, but in my general experience I've found that indexes o
n
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used f
or
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical que
ries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Wort
h
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip
>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
50,000 records.
clustered, non-unique index.
index scan against the
>primary key of the table, with a WHERE clause for the
far more complex. I
>solved it by altering the joins and rearranging predicates
use the clustered
>index scan at 100% cost and the execution time on that
query
preceding query is so
>simple I don't know what to do.
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
>
> I don't know about
> found that indexes on
> me this one row
> hand, are used for
> identified by this FK).
> FKBatchID would allow
> increasing your
> that it includes
> query shouldn't
> it needs... Worth
> 50,000 records.
> clustered, non-unique index.
> index scan against the
> far more complex. I
> use the clustered
> query
> preceding query is so|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||Thanks for the clarification, Ray; most of my experience is with large
datawarehouse type applications so light on the insert (during production
hours) and very heavy querying is the direction I'm coming from.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:es0OpkwAEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Try putting a nonclustered index on processcount and fkbatchID if this is
> something you do often, if fkbatchid is your clustered index you only have
> to put a nonclustered index on processcount as the clustered key is
included
> in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
> propertied column) if you are heavy inserts this is a good idea as it
> creates hot spots on the disk as your inserts will fall to bottom of the
> leaf level (reducing page splits and affording you not to have to mess
with
> fill factor) If you are query intensive and light inserts, test out Adam's
> solution.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Chris Gallucci" <chris@.gallucci.com> wrote in message
> news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
index.
the
> primary key of the table, with a WHERE clause for the
> solved it by altering the joins and rearranging predicates
> index scan at 100% cost and the execution time on that query
> simple I don't know what to do.
>
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the p
rimary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I sol
ved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered inde
x scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple
I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know abo
ut
| your data or needs, but in my general experience I've found that indexes o
n
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used f
or
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical que
ries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Wort
h
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip
>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
50,000 records.
clustered, non-unique index.
index scan against the
>primary key of the table, with a WHERE clause for the
far more complex. I
>solved it by altering the joins and rearranging predicates
use the clustered
>index scan at 100% cost and the execution time on that
query
preceding query is so
>simple I don't know what to do.
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
>
> I don't know about
> found that indexes on
> me this one row
> hand, are used for
> identified by this FK).
> FKBatchID would allow
> increasing your
> that it includes
> query shouldn't
> it needs... Worth
> 50,000 records.
> clustered, non-unique index.
> index scan against the
> far more complex. I
> use the clustered
> query
> preceding query is so|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||Thanks for the clarification, Ray; most of my experience is with large
datawarehouse type applications so light on the insert (during production
hours) and very heavy querying is the direction I'm coming from.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:es0OpkwAEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Try putting a nonclustered index on processcount and fkbatchID if this is
> something you do often, if fkbatchid is your clustered index you only have
> to put a nonclustered index on processcount as the clustered key is
included
> in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
> propertied column) if you are heavy inserts this is a good idea as it
> creates hot spots on the disk as your inserts will fall to bottom of the
> leaf level (reducing page splits and affording you not to have to mess
with
> fill factor) If you are query intensive and light inserts, test out Adam's
> solution.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Chris Gallucci" <chris@.gallucci.com> wrote in message
> news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
index.
the
> primary key of the table, with a WHERE clause for the
> solved it by altering the joins and rearranging predicates
> index scan at 100% cost and the execution time on that query
> simple I don't know what to do.
>
Help With Slow SQL Query
This query takes 1 minute to execute...
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the primary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I solved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered index scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know about
| your data or needs, but in my general experience I've found that indexes on
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used for
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical queries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Worth
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip
>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
>> This query takes 1 minute to execute...
>> SELECT SUM([ProcessCount])
>> FROM [ProcessTable]
>> WHERE [FKBatchID] = 1
>> The table contains about 5,000,000 records.
>> The total record count that matches the WHERE clause is
50,000 records.
>> The table has a clustered index on the primary key.
>> The foreign key in the WHERE clause has a non-
clustered, non-unique index.
>> The execution plan shows a 100% cost on a clustered
index scan against the
>primary key of the table, with a WHERE clause for the
>> [FKBatchID] column.
>> I had a similar problem with another query which was
far more complex. I
>solved it by altering the joins and rearranging predicates
>> in the WHERE clause. This cause the plan to no longer
use the clustered
>index scan at 100% cost and the execution time on that
query
>> went from 2 minutes to 5 seconds. However, the
preceding query is so
>simple I don't know what to do.
>> Please help.
>> ChrisG
>>
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
> >--Original Message--
> >Two questions/suggestions:
> >
> >First of all, why are you clustering on the primary key?
> I don't know about
> >your data or needs, but in my general experience I've
> found that indexes on
> >PKs are used more often for random data retrieval (give
> me this one row
> >identified by this one PK). Indexes on FKs, on the other
> hand, are used for
> >more range-related activity (give me these rows
> identified by this FK).
> >Indeed, for this query, having a clustered index on
> FKBatchID would allow
> >the data to be read contiguously from the disc, greatly
> increasing your
> >performance. So you might consider switching that.
> >
> >Second, you could try covering the non-clustered index so
> that it includes
> >ProcessCount (something like, CREATE INDEX MyIndex ON
> >ProcessTable(FKBatchID, ProcessCount)) ... This way, the
> query shouldn't
> >have to go into the leaves of the cluster to get the data
> it needs... Worth
> >a try, at any rate...
> >
> >
> >"Chris Gallucci" <chris@.gallucci.com> wrote in message
> >news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> >> This query takes 1 minute to execute...
> >> SELECT SUM([ProcessCount])
> >> FROM [ProcessTable]
> >> WHERE [FKBatchID] = 1
> >>
> >> The table contains about 5,000,000 records.
> >> The total record count that matches the WHERE clause is
> 50,000 records.
> >> The table has a clustered index on the primary key.
> >> The foreign key in the WHERE clause has a non-
> clustered, non-unique index.
> >> The execution plan shows a 100% cost on a clustered
> index scan against the
> >primary key of the table, with a WHERE clause for the
> >> [FKBatchID] column.
> >>
> >> I had a similar problem with another query which was
> far more complex. I
> >solved it by altering the joins and rearranging predicates
> >> in the WHERE clause. This cause the plan to no longer
> use the clustered
> >index scan at 100% cost and the execution time on that
> query
> >> went from 2 minutes to 5 seconds. However, the
> preceding query is so
> >simple I don't know what to do.
> >>
> >> Please help.
> >>
> >> ChrisG
> >>
> >>
> >
> >
> >.
> >|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>
SELECT SUM([ProcessCount])
FROM [ProcessTable]
WHERE [FKBatchID] = 1
The table contains about 5,000,000 records.
The total record count that matches the WHERE clause is 50,000 records.
The table has a clustered index on the primary key.
The foreign key in the WHERE clause has a non-clustered, non-unique index.
The execution plan shows a 100% cost on a clustered index scan against the primary key of the table, with a WHERE clause for the
[FKBatchID] column.
I had a similar problem with another query which was far more complex. I solved it by altering the joins and rearranging predicates
in the WHERE clause. This cause the plan to no longer use the clustered index scan at 100% cost and the execution time on that query
went from 2 minutes to 5 seconds. However, the preceding query is so simple I don't know what to do.
Please help.
ChrisGTwo questions/suggestions:
First of all, why are you clustering on the primary key? I don't know about
your data or needs, but in my general experience I've found that indexes on
PKs are used more often for random data retrieval (give me this one row
identified by this one PK). Indexes on FKs, on the other hand, are used for
more range-related activity (give me these rows identified by this FK).
Indeed, for this query, having a clustered index on FKBatchID would allow
the data to be read contiguously from the disc, greatly increasing your
performance. So you might consider switching that.
Second, you could try covering the non-clustered index so that it includes
ProcessCount (something like, CREATE INDEX MyIndex ON
ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
have to go into the leaves of the cluster to get the data it needs... Worth
a try, at any rate...
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>|||WOW! You're the man! (see *** inline )
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message news:eYLXOPsAEHA.4080@.TK2MSFTNGP09.phx.gbl...
| Two questions/suggestions:
|
| First of all, why are you clustering on the primary key? I don't know about
| your data or needs, but in my general experience I've found that indexes on
| PKs are used more often for random data retrieval (give me this one row
| identified by this one PK). Indexes on FKs, on the other hand, are used for
| more range-related activity (give me these rows identified by this FK).
| Indeed, for this query, having a clustered index on FKBatchID would allow
| the data to be read contiguously from the disc, greatly increasing your
| performance. So you might consider switching that.
*** I'll look into this but I'm almost sure we have a couple of critical queries that require this.
| Second, you could try covering the non-clustered index so that it includes
| ProcessCount (something like, CREATE INDEX MyIndex ON
| ProcessTable(FKBatchID, ProcessCount)) ... This way, the query shouldn't
| have to go into the leaves of the cluster to get the data it needs... Worth
| a try, at any rate...
|
***Bang! That smokes. The query is instantaneous.
Thanks so much.
ChrisG|||Hi Adam,
I am a aspirant of RDBMS design and learning things. I
would to request you to give some practical/technical
information on the Below suggestion by you as It is
interesting.
"Indexes on PKs are used more often for random data
retrieval (Eg: give me this one row identified by this one
PK). Indexes on FKs, on the other hand, are used for more
range-related activity (Eg: give me these rows identified
by this FK)".
Thanks in Advance
Chip
>--Original Message--
>Two questions/suggestions:
>First of all, why are you clustering on the primary key?
I don't know about
>your data or needs, but in my general experience I've
found that indexes on
>PKs are used more often for random data retrieval (give
me this one row
>identified by this one PK). Indexes on FKs, on the other
hand, are used for
>more range-related activity (give me these rows
identified by this FK).
>Indeed, for this query, having a clustered index on
FKBatchID would allow
>the data to be read contiguously from the disc, greatly
increasing your
>performance. So you might consider switching that.
>Second, you could try covering the non-clustered index so
that it includes
>ProcessCount (something like, CREATE INDEX MyIndex ON
>ProcessTable(FKBatchID, ProcessCount)) ... This way, the
query shouldn't
>have to go into the leaves of the cluster to get the data
it needs... Worth
>a try, at any rate...
>
>"Chris Gallucci" <chris@.gallucci.com> wrote in message
>news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
>> This query takes 1 minute to execute...
>> SELECT SUM([ProcessCount])
>> FROM [ProcessTable]
>> WHERE [FKBatchID] = 1
>> The table contains about 5,000,000 records.
>> The total record count that matches the WHERE clause is
50,000 records.
>> The table has a clustered index on the primary key.
>> The foreign key in the WHERE clause has a non-
clustered, non-unique index.
>> The execution plan shows a 100% cost on a clustered
index scan against the
>primary key of the table, with a WHERE clause for the
>> [FKBatchID] column.
>> I had a similar problem with another query which was
far more complex. I
>solved it by altering the joins and rearranging predicates
>> in the WHERE clause. This cause the plan to no longer
use the clustered
>index scan at 100% cost and the execution time on that
query
>> went from 2 minutes to 5 seconds. However, the
preceding query is so
>simple I don't know what to do.
>> Please help.
>> ChrisG
>>
>
>.
>|||Okay, let's pretend that we're modelling data for an HR management company
that does HR for lots of companies... They might have some tables like:
CREATE TABLE Companies(CompanyID INT NOT NULL, CompanyName VARCHAR(20) NOT
NULL)
GO
ALTER TABLE Companies ADD CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
GO
CREATE TABLE Employees(EmployeeID INT NOT NULL, CompanyID INT NOT NULL,
EmployeeName VARCHAR(20) NOT NULL)
GO
ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)
GO
ALTER TABLE Employees ADD CONSTRAINT FK_Companies FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
GO
Now we might want to think about what kinds of questions they would ask:
What employees are in company XYZ? How many employees are in company XYZ?
What company is employee XYZ in?
For the first two questions, we might request the data via CompanyName. The
server would search the table for the row containing that name, at which
point the primary key would be obtained and used to filter the Employees
table via the foreign key, FK_Companies. The search on the company table,
via the CompanyName column, would not use a clustered index on the PK.
Neither would the filtration on the Employees table use any index on
EmployeeID. A clustered index on the FK, CompanyID, would be quite helpful,
as the server could then retrieve data contiguously (as I said in my
original post).
For the third question, we might request the data via an EmployeeID; but
this question will not require a clustered index because the EmployeeID is
only pointing to a single row. So we need to find that single row as
quickly as possible and use it to answer the question. No ordering,
grouping, or contiguous data access will be necessary on the Employees
table.
There may be other cases where this doesn't hold true (which is why we have
jobs; if there were cookbook answers to all questions we wouldn't be
needed), but I think this methodology tends to work for the majority of
cases.
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:798b01c402d3$37d88bf0$a301280a@.phx.gbl...
> Hi Adam,
> I am a aspirant of RDBMS design and learning things. I
> would to request you to give some practical/technical
> information on the Below suggestion by you as It is
> interesting.
> "Indexes on PKs are used more often for random data
> retrieval (Eg: give me this one row identified by this one
> PK). Indexes on FKs, on the other hand, are used for more
> range-related activity (Eg: give me these rows identified
> by this FK)".
> Thanks in Advance
> Chip
> >--Original Message--
> >Two questions/suggestions:
> >
> >First of all, why are you clustering on the primary key?
> I don't know about
> >your data or needs, but in my general experience I've
> found that indexes on
> >PKs are used more often for random data retrieval (give
> me this one row
> >identified by this one PK). Indexes on FKs, on the other
> hand, are used for
> >more range-related activity (give me these rows
> identified by this FK).
> >Indeed, for this query, having a clustered index on
> FKBatchID would allow
> >the data to be read contiguously from the disc, greatly
> increasing your
> >performance. So you might consider switching that.
> >
> >Second, you could try covering the non-clustered index so
> that it includes
> >ProcessCount (something like, CREATE INDEX MyIndex ON
> >ProcessTable(FKBatchID, ProcessCount)) ... This way, the
> query shouldn't
> >have to go into the leaves of the cluster to get the data
> it needs... Worth
> >a try, at any rate...
> >
> >
> >"Chris Gallucci" <chris@.gallucci.com> wrote in message
> >news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> >> This query takes 1 minute to execute...
> >> SELECT SUM([ProcessCount])
> >> FROM [ProcessTable]
> >> WHERE [FKBatchID] = 1
> >>
> >> The table contains about 5,000,000 records.
> >> The total record count that matches the WHERE clause is
> 50,000 records.
> >> The table has a clustered index on the primary key.
> >> The foreign key in the WHERE clause has a non-
> clustered, non-unique index.
> >> The execution plan shows a 100% cost on a clustered
> index scan against the
> >primary key of the table, with a WHERE clause for the
> >> [FKBatchID] column.
> >>
> >> I had a similar problem with another query which was
> far more complex. I
> >solved it by altering the joins and rearranging predicates
> >> in the WHERE clause. This cause the plan to no longer
> use the clustered
> >index scan at 100% cost and the execution time on that
> query
> >> went from 2 minutes to 5 seconds. However, the
> preceding query is so
> >simple I don't know what to do.
> >>
> >> Please help.
> >>
> >> ChrisG
> >>
> >>
> >
> >
> >.
> >|||Try putting a nonclustered index on processcount and fkbatchID if this is
something you do often, if fkbatchid is your clustered index you only have
to put a nonclustered index on processcount as the clustered key is included
in all NCI's. Regarding clustered indexes on a PK (if it is an IDENT
propertied column) if you are heavy inserts this is a good idea as it
creates hot spots on the disk as your inserts will fall to bottom of the
leaf level (reducing page splits and affording you not to have to mess with
fill factor) If you are query intensive and light inserts, test out Adam's
solution.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Gallucci" <chris@.gallucci.com> wrote in message
news:emlZ6$rAEHA.2768@.tk2msftngp13.phx.gbl...
> This query takes 1 minute to execute...
> SELECT SUM([ProcessCount])
> FROM [ProcessTable]
> WHERE [FKBatchID] = 1
> The table contains about 5,000,000 records.
> The total record count that matches the WHERE clause is 50,000 records.
> The table has a clustered index on the primary key.
> The foreign key in the WHERE clause has a non-clustered, non-unique index.
> The execution plan shows a 100% cost on a clustered index scan against the
primary key of the table, with a WHERE clause for the
> [FKBatchID] column.
> I had a similar problem with another query which was far more complex. I
solved it by altering the joins and rearranging predicates
> in the WHERE clause. This cause the plan to no longer use the clustered
index scan at 100% cost and the execution time on that query
> went from 2 minutes to 5 seconds. However, the preceding query is so
simple I don't know what to do.
> Please help.
> ChrisG
>
Friday, March 23, 2012
Help With Select Statement
My Current Query:
select rpg.rpg_sortorder,rpg.rpg_groupname,
act.act_cardprocid, sum(act.act_trxamtn) as Amount from
actlog act
right outer join
report_groups rpg on rpg.rpg_groupcode = act.act_CardProcID
WHERE
(rpg.rpg_report = 'SS') AND
(rpg.rpg_groupname <> 'not on report')
group by rpg.rpg_groupname, rpg.rpg_sortorder, act.act_cardprocid
order by rpg_sortorder
Report_groups looks like this
rpg_sortorder rpg_groupname rpg_groupcode
2 debit cards db
3 discover ds
4 visa vs
8 food stamps ef
10 gift cards gc
14 fleet cards wx
15 fleet cards mf
16 fleet cards vy
17 ach ac
Actlog looks like this:
act_cardprocid Amount
db 25.00
db 25.00
vs 100.00
vs 200.00
resultset I wish to achieve
rpg_sortorder rpg_groupname act_cardprocid Amount
2 debit cards db 50.00
3 Discover null null
4 Visa vs 300.00
8 food stamps null null
10 gift cards null null
14 Fleet Cards null null
17 ach null null
Note that in the join, I only need one record to represent group name and sortorder.
If there happens to be three records in report_groups for the same groupname, I only want
the top record. Hence, I do NOT want the following to showup in my results:
15 Fleet cards null null
16 Fleet cards null null
How can I filter out these unwanted records?
This seems to produce your desired output.
Code Snippet
SET NOCOUNT ON
DECLARE @.Report_Groups table
( Rpg_SortOrder int,
Rpg_GroupName varchar(20),
Rpg_GroupCode char(2)
)
INSERT INTO @.Report_Groups VALUES ( 2, 'debit cards', 'db' )
INSERT INTO @.Report_Groups VALUES ( 3, 'discover', 'ds' )
INSERT INTO @.Report_Groups VALUES ( 4, 'visa', 'vs' )
INSERT INTO @.Report_Groups VALUES ( 8, 'food stamps', 'ef' )
INSERT INTO @.Report_Groups VALUES ( 10, 'gift cards', 'gc' )
INSERT INTO @.Report_Groups VALUES ( 14, 'fleet cards', 'wx' )
INSERT INTO @.Report_Groups VALUES ( 15, 'fleet cards', 'mf' )
INSERT INTO @.Report_Groups VALUES ( 16, 'fleet cards', 'vy' )
INSERT INTO @.Report_Groups VALUES ( 18, 'ach', 'ac' )
DECLARE @.Actlog table
( Act_CardProcID char(2),
Act_TrxAmtn decimal(10,2)
)
INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 100.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 200.00 )
SELECT
SortOrder = min( r.Rpg_SortOrder ),
GroupName = r.Rpg_GroupName,
CardProdID = min( a.Act_CardProcID ),
Amount = sum( a.Act_TrxAmtn )
FROM @.Report_Groups r
LEFT JOIN @.ActLog a
ON r.Rpg_GroupCode = a.Act_CardProcID
GROUP BY r.Rpg_GroupName
ORDER BY SortOrder
-- -- -
2 debit cards db 50.00
3 discover NULL NULL
4 visa vs 300.00
8 food stamps NULL NULL
10 gift cards NULL NULL
14 fleet cards NULL NULL
18 ach NULL NULL
Help with select query
Hi All,
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA0111.000022.0000
DVD0036.000053.5600
ZZZ0234.000044.5000
BMM0023.000029.8500
BMM0013.000032.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01blah blah 57750.0000115500.0000
BMM001blah blah blah 8739.000095692.0500
DVD003blah blah blabber DVD4230.000037759.8000
BMM002Yadda Yadda2772.000027581.4000
DVD001Dooddly doo DVD1605.000013658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.
Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA0111.000022.0000
DVD0036.000053.5600
ZZZ0234.000044.5000
BMM0023.000029.8500
BMM0013.000032.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01blah blah 57750.0000115500.0000
BMM001blah blah blah 8739.000095692.0500
DVD003blah blah blabber DVD4230.000037759.8000
BMM002Yadda Yadda2772.000027581.4000
DVD001Dooddly doo DVD1605.000013658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.
Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
Help with select query
Hi All,
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
Help with select query
Hi All,
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
I'm new-ish to SQL so bear with me...
If I run this query
SELECT Distinct Top 5
c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
FROM acocmp1.currsale c
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
group by c.stkcode
order by Quantity desc
I get the correct data:
AAA01 11.0000 22.0000
DVD003 6.0000 53.5600
ZZZ023 4.0000 44.5000
BMM002 3.0000 29.8500
BMM001 3.0000 32.8500
but if I run this query:
SELECT Distinct Top 5
c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
'Amount'
FROM acocmp1.currsale c, opacif_Detail od
WHERE (c.STKCODE Not In ('i98','SUBS'))
and (c.trandate = '2007-07-24 00:00:00')
and c.stkcode = od.stkcode
group by c.stkcode, od.Description
order by Quantity desc
I get:
AAA01 blah blah 57750.0000 115500.0000
BMM001 blah blah blah 8739.0000 95692.0500
DVD003 blah blah blabber DVD 4230.0000 37759.8000
BMM002 Yadda Yadda 2772.0000 27581.4000
DVD001 Dooddly doo DVD 1605.0000 13658.5500
c and od don't share a key that I can reference them with to keep the
linking one to one which I guess is what is causing the massive jump.
I just need the description column so that staff who can't read the
stock codes can read the table.
I'm pretty sure that I'm either missing something or that what I want
isn't possible and I'll have to find another way around.Always worth writing something down to figure out the answer...
almost as soon as I'd typed "c and od don't share a key"
I decided to look for another table that held the description and the
code and solved my own problem
P
On 25 Jul, 17:22, Panda <paul.dam...@.gmail.com> wrote:
> Hi All,
> I'm new-ish to SQL so bear with me...
> If I run this query
> SELECT Distinct Top 5
> c.stkcode, sum(c.qty) as 'Quantity', sum(c.amount) as 'Amount'
> FROM acocmp1.currsale c
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> group by c.stkcode
> order by Quantity desc
> I get the correct data:
> AAA01 11.0000 22.0000
> DVD003 6.0000 53.5600
> ZZZ023 4.0000 44.5000
> BMM002 3.0000 29.8500
> BMM001 3.0000 32.8500
> but if I run this query:
> SELECT Distinct Top 5
> c.stkcode, od.description, sum(c.qty) as 'Quantity', sum(c.amount) as
> 'Amount'
> FROM acocmp1.currsale c, opacif_Detail od
> WHERE (c.STKCODE Not In ('i98','SUBS'))
> and (c.trandate = '2007-07-24 00:00:00')
> and c.stkcode = od.stkcode
> group by c.stkcode, od.Description
> order by Quantity desc
> I get:
> AAA01 blah blah 57750.0000 115500.0000
> BMM001 blah blah blah 8739.0000 95692.0500
> DVD003 blah blah blabber DVD 4230.0000 37759.8000
> BMM002 Yadda Yadda 2772.0000 27581.4000
> DVD001 Dooddly doo DVD 1605.0000 13658.5500
> c and od don't share a key that I can reference them with to keep the
> linking one to one which I guess is what is causing the massive jump.
> I just need the description column so that staff who can't read the
> stock codes can read the table.
> I'm pretty sure that I'm either missing something or that what I want
> isn't possible and I'll have to find another way around.
Monday, March 19, 2012
Help with query! Ranking of Sum() column
If anyone can help with this, I'd be most appreciative.
Basically, I'm trying to sum a column based on a unique ID and then
find out the RANK of that record in the table.
Here's the table:
Points
- PointID
- UserID
- Points
There can be multiple records with the same UserID.
Here's the query I'm using now:
SELECT Sum(Points), UserID FROM Points Group By UserID Order By
Sum(Points) desc
This basically returns ALL the records with the Points summed. I think
loop through in my code to find what row number a specific ID is. This
is NOT efficient and is slowing down my site considerably.
This is a query someone recommended I used:
SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
sum(points) FROM Points WHERE UserId = 65))
SQL Server doesn't like that query, though, because of the aggregate
function. I was searching for the rank of UserID 65.
If anyone could help me with this I'd appreciate it. I ONLY need the
rank of one record, so hoepfully I don't need to use a temp table for
this.
Thanks,
Andyhttp://www.aspfaq.com/show.asp?id=2427
<andymilk@.gmail.com> wrote in message
news:1146492469.413328.220320@.j33g2000cwa.googlegroups.com...
> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>|||Try,
-- sql server 2000
create view v1
as
select UserID, sum(Points) as sum_Points
from t1
group by UserID
go
select * from v1
go
select
count(*) as rank,
a.UserID,
a.sum_Points
from
v1 as a inner join v1 as b
on (a.sum_Points < b.sum_Points)
or (a.sum_Points = b.sum_Points and a.UserID >= b.UserID)
group by
a.UserID,
a.sum_Points
order by
rank
go
AMB
"andymilk@.gmail.com" wrote:
> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>
Basically, I'm trying to sum a column based on a unique ID and then
find out the RANK of that record in the table.
Here's the table:
Points
- PointID
- UserID
- Points
There can be multiple records with the same UserID.
Here's the query I'm using now:
SELECT Sum(Points), UserID FROM Points Group By UserID Order By
Sum(Points) desc
This basically returns ALL the records with the Points summed. I think
loop through in my code to find what row number a specific ID is. This
is NOT efficient and is slowing down my site considerably.
This is a query someone recommended I used:
SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
sum(points) FROM Points WHERE UserId = 65))
SQL Server doesn't like that query, though, because of the aggregate
function. I was searching for the rank of UserID 65.
If anyone could help me with this I'd appreciate it. I ONLY need the
rank of one record, so hoepfully I don't need to use a temp table for
this.
Thanks,
Andyhttp://www.aspfaq.com/show.asp?id=2427
<andymilk@.gmail.com> wrote in message
news:1146492469.413328.220320@.j33g2000cwa.googlegroups.com...
> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>|||Try,
-- sql server 2000
create view v1
as
select UserID, sum(Points) as sum_Points
from t1
group by UserID
go
select * from v1
go
select
count(*) as rank,
a.UserID,
a.sum_Points
from
v1 as a inner join v1 as b
on (a.sum_Points < b.sum_Points)
or (a.sum_Points = b.sum_Points and a.UserID >= b.UserID)
group by
a.UserID,
a.sum_Points
order by
rank
go
AMB
"andymilk@.gmail.com" wrote:
> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>
Help with query! Ranking of Sum() column
Are you sure this works with an aggregate function?
Also, can I return ONE row and get the correct rank?Can you provide some useful DDL, sample data, and desired output? (
http://www.aspfaq.com/5006 )
I'm having a hard time visualizing " I think loop through in my code to find
what row number a specific ID is."
A
<andymilk@.gmail.com> wrote in message
news:1146493870.042951.123210@.i39g2000cwa.googlegroups.com...
> Are you sure this works with an aggregate function?
> Also, can I return ONE row and get the correct rank?
>
Also, can I return ONE row and get the correct rank?Can you provide some useful DDL, sample data, and desired output? (
http://www.aspfaq.com/5006 )
I'm having a hard time visualizing " I think loop through in my code to find
what row number a specific ID is."
A
<andymilk@.gmail.com> wrote in message
news:1146493870.042951.123210@.i39g2000cwa.googlegroups.com...
> Are you sure this works with an aggregate function?
> Also, can I return ONE row and get the correct rank?
>
Help with query to fill in missing records
The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
Help with query to fill in missing records
The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000
On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000
On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
Help with query to fill in missing records
The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
Friday, March 9, 2012
Help with query
Hello,
I have two queries
SELECT TOP (100) PERCENT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value,
dbo.Events.Top_up * dbo.Event_data.Transaction_value AS [Top-ups]
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 2)) AS Topups
GROUP BY Player_name
ORDER BY TOPUPS DESC
and
SELECT TOP (100) PERCENT Player_name, SUM(Expr1) AS Expr1
FROM (SELECT TOP (100) PERCENT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys,
dbo.Event_data.Transaction_value * dbo.Events.Rebuys AS Expr1
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 3)
ORDER BY Expr1 DESC) AS REBUYS
GROUP BY Player_name
ORDER BY Expr1 DESC
Can I combine these into one query to get the Player_name result, rebuys and top ups?
Sure, pop a UNION between the queries.
SELECT TOP (100) PERCENT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value,
dbo.Events.Top_up * dbo.Event_data.Transaction_value AS [Top-ups]
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 2)) AS Topups
GROUP BY Player_name
ORDER BY TOPUPS DESC
UNION
SELECT TOP (100) PERCENT Player_name, SUM(Expr1) AS Expr1
FROM (SELECT TOP (100) PERCENT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys,
dbo.Event_data.Transaction_value * dbo.Events.Rebuys AS Expr1
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 3)
ORDER BY Expr1 DESC) AS REBUYS
GROUP BY Player_name
ORDER BY Expr1 DESC
Thanks for replying, but I am getting an "incorrect syntax near the keyword Union"
am I missing a comma or something?
|||You should not use ORDER BY for subqueries.
Also if you are selecting all the rows why bother with top 100 PERCENT.
SELECT Player_name,SUM([Top-ups])AS TOPUPSFROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value, dbo.Events.Top_up * dbo.Event_data.Transaction_valueAS [Top-ups]FROM dbo.Event_dataINNERJOIN dbo.EventsON dbo.Event_data.Event_id = dbo.Events.Event_idINNERJOIN dbo.PlayersON dbo.Event_data.Player_id = dbo.Players.Player_idWHERE (dbo.Event_data.Transaction_type = 2))AS TopupsGROUP BY Player_name--ORDER BY TOPUPS DESCUNION SELECT Player_name,SUM(Expr1)AS TOPUPSFROM (SELECT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys, dbo.Event_data.Transaction_value * dbo.Events.RebuysAS Expr1FROM dbo.Event_dataINNERJOIN dbo.EventsON dbo.Event_data.Event_id = dbo.Events.Event_idINNERJOIN dbo.PlayersON dbo.Event_data.Player_id = dbo.Players.Player_idWHERE (dbo.Event_data.Transaction_type = 3) )AS REBUYSGROUP BY Player_name--ORDER BY TOPUPS DESC|||
OK Its working but I am only getting two colums Player_name and TOPUPS, when I really want Three coloums Player_name TOPUPS and REBUYS.
I am getting the player name twice in the player column with the rebuy total under the TOPUP column.
Any Ideas?
SELECT
Player_name,SUM([Top-ups])AS TOPUPSFROM
(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,Events
.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOINEvents
ON Event_data.Event_id= Events.idINNERJOINPlayers
ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS TopupsGROUP
BY Player_nameUNION
SELECT
Player_name,SUM([Re-buys])AS REBUYSFROM
(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOINEvents
ON Event_data.Event_id= Events.idINNERJOINPlayers
ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYSGROUP
BY Player_namewhat I am getting
What I want
Thanks again.
|||
You could then insert the result of each of the queries into a table varible and do a SELECT from it.
declare @.ttable( player_namevarchar(100), topupsint, buyingint)INSERT INTO @.t (player_name, topups, buying )SELECT Player_name,SUM([Top-ups])AS TOPUPS ,NULLFROM (SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,Events.Top_up * Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN EventsON Event_data.Event_id = Events.idINNERJOIN PlayersON Event_data.Player_id = Players.Player_idWHERE Event_data.Transaction_type = 2--AS TopupsGROUP BY Player_name )AS TopupsINSERT INTO @.t (player_name, topups, buying )SELECT Player_name,NULL,SUM([Re-buys])AS REBUYSFROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN EventsON Event_data.Event_id = Events.idINNERJOIN PlayersON Event_data.Player_id = Players.Player_idWHERE Event_data.Transaction_type = 3--AS REBUYSGROUP BY Player_name )AS REBUYSSELECT player_name,min(topups),min(buying)FROM @.tGROUP BY player_nameORDER BY player_name
|||
Once again thank you for all your help. I am going to mark the previous one as the answer, but I still have one outstanding issue: the return results give me the column name of player_name but the other two say no column name even thought the totals are correct. Heres what I have:
declare
@.ttable( player_namevarchar(100), topups1int, buying1int)INSERT
INTO @.t(player_name, topups1)SELECT Player_name,SUM([Top-ups])AS TOPUPSFROM
(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,Events
.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOINEvents
ON Event_data.Event_id= Events.idINNERJOINPlayers
ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS TopupsGROUP
BY player_nameINSERT
INTO @.t(player_name, buying1)SELECT
Player_name,SUM([Re-buys])AS REBUYSFROM
(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOINEvents
ON Event_data.Event_id= Events.idINNERJOINPlayers
ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYSGROUP
BY Player_nameSELECT
player_name,min(topups1),min(buying1)FROM
@.tGROUP
BY player_nameORDER
BY player_name|||Give column names for your expressions in your last SELECT statement.
SELECTplayer_name,min(topups1) AS topups1,min(buying1) AS buying1
FROM@.t
GROUPBY player_name
ORDERBY player_name
|||Excellent!!! Thank you very much
help with query
Hi, need some help...
The problem I face is that the last column in the select list which is
[Amount2]
returns the sum of all users sum so each row in the returned result has the
same number.
The [Amount1] column returns the correct sum per each user due to the
grouping.
How shall I do this?
SELECT u.Col1, u.Col2, u.Col3, SUM(t.amount) AS Amount1 /* this SUM is OK
*/,
(SELECT SUM(t.amount) /* this SUM is not OK */
FROM dbo.Table1 T1
INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
WHERE T3.UserId IN (1, 2, 3)
AND T3.TypeId = 2
) AS Amount2
FROM dbo.Table1 T1
INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
WHERE T3.UserId IN (1, 2, 3)
AND T3.TypeId = 1
GROUP BY T4.Col1, T4.Col2, T4.Col3
Thanks,
YanivYaniv
I did some testing on Northwind database
select Customerid, count(employeeid),
(select count(employeeid) from orders
where Customerid in ('vinet','hanar')) as d
from orders
where Customerid in ('vinet','hanar')
group by Customerid
--Customerid d
-- -- --
HANAR 14 19
VINET 5 19
select Customerid, count(employeeid),
(select count(o.employeeid) from orders o
where orders.Customerid=o.Customerid) as d
from orders
where Customerid in ('vinet','hanar')
group by Customerid
--Customerid d
-- -- --
HANAR 14 14
VINET 5 5
You did not have a group by customerid in my case ,thus you've got the
wrong output
See if my example helps you otherwise please post your ddl + expected result
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:ettjcwM9FHA.2264@.tk2msftngp13.phx.gbl...
> Hi, need some help...
> The problem I face is that the last column in the select list which is
> [Amount2]
> returns the sum of all users sum so each row in the returned result has
> the same number.
> The [Amount1] column returns the correct sum per each user due to the
> grouping.
> How shall I do this?
> SELECT u.Col1, u.Col2, u.Col3, SUM(t.amount) AS Amount1 /* this SUM is OK
> */,
> (SELECT SUM(t.amount) /* this SUM is not OK */
> FROM dbo.Table1 T1
> INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
> INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
> INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
> WHERE T3.UserId IN (1, 2, 3)
> AND T3.TypeId = 2
> ) AS Amount2
> FROM dbo.Table1 T1
> INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
> INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
> INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
> WHERE T3.UserId IN (1, 2, 3)
> AND T3.TypeId = 1
> GROUP BY T4.Col1, T4.Col2, T4.Col3
>
> Thanks,
> Yaniv
>|||Great, this is what I need.
Many many thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23$x6W5M9FHA.132@.TK2MSFTNGP15.phx.gbl...
> Yaniv
> I did some testing on Northwind database
> select Customerid, count(employeeid),
> (select count(employeeid) from orders
> where Customerid in ('vinet','hanar')) as d
> from orders
> where Customerid in ('vinet','hanar')
> group by Customerid
> --Customerid d
> -- -- --
> HANAR 14 19
> VINET 5 19
>
> select Customerid, count(employeeid),
> (select count(o.employeeid) from orders o
> where orders.Customerid=o.Customerid) as d
> from orders
> where Customerid in ('vinet','hanar')
> group by Customerid
> --Customerid d
> -- -- --
> HANAR 14 14
> VINET 5 5
>
> You did not have a group by customerid in my case ,thus you've got the
> wrong output
> See if my example helps you otherwise please post your ddl + expected
> result
>
>
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:ettjcwM9FHA.2264@.tk2msftngp13.phx.gbl...
>
The problem I face is that the last column in the select list which is
[Amount2]
returns the sum of all users sum so each row in the returned result has the
same number.
The [Amount1] column returns the correct sum per each user due to the
grouping.
How shall I do this?
SELECT u.Col1, u.Col2, u.Col3, SUM(t.amount) AS Amount1 /* this SUM is OK
*/,
(SELECT SUM(t.amount) /* this SUM is not OK */
FROM dbo.Table1 T1
INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
WHERE T3.UserId IN (1, 2, 3)
AND T3.TypeId = 2
) AS Amount2
FROM dbo.Table1 T1
INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
WHERE T3.UserId IN (1, 2, 3)
AND T3.TypeId = 1
GROUP BY T4.Col1, T4.Col2, T4.Col3
Thanks,
YanivYaniv
I did some testing on Northwind database
select Customerid, count(employeeid),
(select count(employeeid) from orders
where Customerid in ('vinet','hanar')) as d
from orders
where Customerid in ('vinet','hanar')
group by Customerid
--Customerid d
-- -- --
HANAR 14 19
VINET 5 19
select Customerid, count(employeeid),
(select count(o.employeeid) from orders o
where orders.Customerid=o.Customerid) as d
from orders
where Customerid in ('vinet','hanar')
group by Customerid
--Customerid d
-- -- --
HANAR 14 14
VINET 5 5
You did not have a group by customerid in my case ,thus you've got the
wrong output
See if my example helps you otherwise please post your ddl + expected result
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:ettjcwM9FHA.2264@.tk2msftngp13.phx.gbl...
> Hi, need some help...
> The problem I face is that the last column in the select list which is
> [Amount2]
> returns the sum of all users sum so each row in the returned result has
> the same number.
> The [Amount1] column returns the correct sum per each user due to the
> grouping.
> How shall I do this?
> SELECT u.Col1, u.Col2, u.Col3, SUM(t.amount) AS Amount1 /* this SUM is OK
> */,
> (SELECT SUM(t.amount) /* this SUM is not OK */
> FROM dbo.Table1 T1
> INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
> INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
> INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
> WHERE T3.UserId IN (1, 2, 3)
> AND T3.TypeId = 2
> ) AS Amount2
> FROM dbo.Table1 T1
> INNER JOIN dbo.Table2 T2 ON T1.TransId = T2.TransId
> INNER JOIN dbo.Table3 T3 ON T3.LoanId = T2.LoanId
> INNER JOIN dbo.Table4 T4 ON T4.UserId = T3.UserId
> WHERE T3.UserId IN (1, 2, 3)
> AND T3.TypeId = 1
> GROUP BY T4.Col1, T4.Col2, T4.Col3
>
> Thanks,
> Yaniv
>|||Great, this is what I need.
Many many thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23$x6W5M9FHA.132@.TK2MSFTNGP15.phx.gbl...
> Yaniv
> I did some testing on Northwind database
> select Customerid, count(employeeid),
> (select count(employeeid) from orders
> where Customerid in ('vinet','hanar')) as d
> from orders
> where Customerid in ('vinet','hanar')
> group by Customerid
> --Customerid d
> -- -- --
> HANAR 14 19
> VINET 5 19
>
> select Customerid, count(employeeid),
> (select count(o.employeeid) from orders o
> where orders.Customerid=o.Customerid) as d
> from orders
> where Customerid in ('vinet','hanar')
> group by Customerid
> --Customerid d
> -- -- --
> HANAR 14 14
> VINET 5 5
>
> You did not have a group by customerid in my case ,thus you've got the
> wrong output
> See if my example helps you otherwise please post your ddl + expected
> result
>
>
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:ettjcwM9FHA.2264@.tk2msftngp13.phx.gbl...
>
Monday, February 27, 2012
help with my select
I hope this is the right place.
I have a proc here, it selects the data needed, but when i put in a sum aggrate the service if sumed up, but it sums all of the records. what i am trying to do is sum up the first service fees that appear.
would you folks be kind and point me in the right direction as how i can acomplish this?
here is my proc code.
DECLARE @.EOBFileName NVARCHAR(50)
SET @.EOBFileName = 'B835255227__CHSEP__2107032414052256619'
SELECT DISTINCT ISNULL(slp.pkServiceLinePayment, 0) AS PaymentKey
, ISNULL(slp.fkClaim, 0) AS ClaimKey
, ISNULL(slp.fkServiceLine, 0) AS ServiceLineKey
, ISNULL(slp.fkInsurance, 0) AS InsuranceKEy
, ISNULL(slp.ServiceDate, 0) AS ServiceDate
, ISNULL(slp.ServiceCode, 0) AS ServiceCode
, ISNULL(slp.ServiceFee, '') AS ServiceFee
, ISNULL(slp.InsurancePayment, '') AS InsurancePayment
, ISNULL(c.fkRenderingServiceProvider, '') AS ProviderKey
, ISNULL(ent.NM103, '') AS ProviderName
, ISNULL(slp.CurrentStatus, '') AS Status
, ISNULL(pat.NM103, '') + ', ' + ISNULL(pat.NM104, '') AS PatientName
, ISNULL(ins.PlanID, '') AS Policy
, ISNULL(ins.GroupPlanID, '') AS GroupID
, ISNULL(slp.PayerClaimTrace, '') AS ClaimTrace
, ISNULL(slpa.GroupCode, '') AS GroupCode
, ISNULL(grp.CodeDescription, '') AS GroupDescription
, ISNULL(slpa.ReasonCode1, '') AS ReasonCode1
, ISNULL(rcode1.CodeDescription, '') AS ReasonDesc1
, ISNULL(slpa.MonetaryAmount1, '') AS Amount1
, ISNULL(slpa.Quantity1, 1) AS Qt1
, ISNULL(slpa.ReasonCode2, '') AS ReasonCode2
, ISNULL(rcode2.CodeDescription, '') AS ReasonDesc2
, ISNULL(slpa.MonetaryAmount2, '') AS Amount2
, ISNULL(slpa.Quantity2, '') AS Qt2
, ISNULL(slpa.ReasonCode3, '') AS ReasonCode3
, ISNULL(rcode3.CodeDescription, '') AS ReasonDesc3
, ISNULL(slpa.MonetaryAmount3, '') AS Amount3
, ISNULL(slpa.Quantity3, '') AS Qt3
, ISNULL(slpa.ReasonCode4, '') AS ReasonCode4
, ISNULL(rcode4.CodeDescription, '') AS ReasonDesc4
, ISNULL(slpa.MonetaryAmount4, '') AS Amount4
, ISNULL(slpa.Quantity4, '') AS Qt4
, ISNULL(slpa.ReasonCode5, '') AS ReasonCode5
, ISNULL(rcode5.CodeDescription, '') AS ReasonDesc5
, ISNULL(slpa.MonetaryAmount5, '') AS Amount5
, ISNULL(slpa.Quantity5, '') AS Qt5
, ISNULL(slpa.ReasonCode6, '') AS ReasonCode6
, ISNULL(rcode6.CodeDescription, '') AS ReasonDesc6
, ISNULL(slpa.MonetaryAmount6, '') AS Amount6
, ISNULL(slpa.Quantity6, '') AS Qt6
, ISNULL(slpr.Qualifier + ' ' + slpr.RemarkCode + ' - ' + rkcode.CodeDescription, '') AS Remark
FROM tbl_ServiceLine_Payments slp
INNER JOIN tbl_Claim_Info c
ON slp.fkClaim = c.pkClaim
INNER JOIN tbl_Entities ent
ON c.fkRenderingServiceProvider = ent.pkEntity
INNER JOIN tbl_Entities pat
ON c.fkPatient = pat.pkEntity
INNER JOIN tbl_Patient_Insurance_Plans ins
ON slp.fkInsurance = ins.pkInsurance
LEFT OUTER JOIN tbl_ServiceLine_Payments_AdjustmentCodes slpa
ON slp.pkServiceLinePayment = slpa.fkServiceLinePayment
LEFT OUTER JOIN tbl_Claim_Adjustment_Group_Codes grp
ON slpa.GroupCode = grp.ClaimAdjustmentGroupCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode1
ON slpa.ReasonCode1 = rcode1.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode2
ON slpa.ReasonCode2 = rcode2.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode3
ON slpa.ReasonCode3 = rcode3.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode4
ON slpa.ReasonCode4 = rcode4.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode5
ON slpa.ReasonCode5 = rcode5.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode6
ON slpa.ReasonCode6 = rcode6.ClaimAdjustmentReasonCode
LEFT OUTER JOIN dbo.tbl_ServiceLine_Payments_RemarkCodes slpr
ON slp.pkServiceLinePayment = slpr.fkServiceLinePayment
LEFT OUTER JOIN dbo.tbl_Claim_Advice_Remark_Codes rkcode
ON slpr.RemarkCode = rkcode.ClaimAdviceRemarkCode
WHERE (slp.EOBFileName LIKE @.EOBFileName)
ORDER BY ClaimKey, PaymentKey
here is the table that is created.
PaymentKey ClaimKey ServiceLineKey InsuranceKEy ServiceDate ServiceCode ServiceFee InsurancePayment ProviderKey ProviderName Status PatientName Policy GroupID ClaimTrace GroupCode GroupDescription ReasonCode1 ReasonDesc1 Amount1 Qt1 ReasonCode2 ReasonDesc2 Amount2 Qt2 ReasonCode3 ReasonDesc3 Amount3 Qt3 ReasonCode4 ReasonDesc4 Amount4 Qt4 ReasonCode5 ReasonDesc5 Amount5 Qt5 ReasonCode6 ReasonDesc6 Amount6 Qt6 Remark
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
1107 52 5589 416 02/12/2007 97124 22.00 17.6 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 4.40 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1108 52 5588 416 02/12/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1110 52 5586 416 02/10/2007 72100 38.00 30.4 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.60 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1111 52 5585 416 02/10/2007 72040 35.00 28 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1112 52 5584 416 02/10/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1113 52 5583 416 02/10/2007 98943 25.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 185 The rendering provider is not eligible to perform the service billed. 25.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1114 52 5582 416 02/10/2007 99211 22.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations 97 Payment is included in the allowance for another service/procedure. 22.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE M144 - Pre-/post-operative care payment is included in the allowance for the surgery/procedure.
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 9.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
(11 row(s) affected)
Hi,
could you post it in a more readable format, please?
Y
help with my select
I hope this is the right place.
I have a proc here, it selects the data needed, but when i put in a sum aggrate the service if sumed up, but it sums all of the records. what i am trying to do is sum up the first service fees that appear.
would you folks be kind and point me in the right direction as how i can acomplish this?
here is my proc code.
DECLARE @.EOBFileName NVARCHAR(50)
SET @.EOBFileName = 'B835255227__CHSEP__2107032414052256619'
SELECT DISTINCT ISNULL(slp.pkServiceLinePayment, 0) AS PaymentKey
, ISNULL(slp.fkClaim, 0) AS ClaimKey
, ISNULL(slp.fkServiceLine, 0) AS ServiceLineKey
, ISNULL(slp.fkInsurance, 0) AS InsuranceKEy
, ISNULL(slp.ServiceDate, 0) AS ServiceDate
, ISNULL(slp.ServiceCode, 0) AS ServiceCode
, ISNULL(slp.ServiceFee, '') AS ServiceFee
, ISNULL(slp.InsurancePayment, '') AS InsurancePayment
, ISNULL(c.fkRenderingServiceProvider, '') AS ProviderKey
, ISNULL(ent.NM103, '') AS ProviderName
, ISNULL(slp.CurrentStatus, '') AS Status
, ISNULL(pat.NM103, '') + ', ' + ISNULL(pat.NM104, '') AS PatientName
, ISNULL(ins.PlanID, '') AS Policy
, ISNULL(ins.GroupPlanID, '') AS GroupID
, ISNULL(slp.PayerClaimTrace, '') AS ClaimTrace
, ISNULL(slpa.GroupCode, '') AS GroupCode
, ISNULL(grp.CodeDescription, '') AS GroupDescription
, ISNULL(slpa.ReasonCode1, '') AS ReasonCode1
, ISNULL(rcode1.CodeDescription, '') AS ReasonDesc1
, ISNULL(slpa.MonetaryAmount1, '') AS Amount1
, ISNULL(slpa.Quantity1, 1) AS Qt1
, ISNULL(slpa.ReasonCode2, '') AS ReasonCode2
, ISNULL(rcode2.CodeDescription, '') AS ReasonDesc2
, ISNULL(slpa.MonetaryAmount2, '') AS Amount2
, ISNULL(slpa.Quantity2, '') AS Qt2
, ISNULL(slpa.ReasonCode3, '') AS ReasonCode3
, ISNULL(rcode3.CodeDescription, '') AS ReasonDesc3
, ISNULL(slpa.MonetaryAmount3, '') AS Amount3
, ISNULL(slpa.Quantity3, '') AS Qt3
, ISNULL(slpa.ReasonCode4, '') AS ReasonCode4
, ISNULL(rcode4.CodeDescription, '') AS ReasonDesc4
, ISNULL(slpa.MonetaryAmount4, '') AS Amount4
, ISNULL(slpa.Quantity4, '') AS Qt4
, ISNULL(slpa.ReasonCode5, '') AS ReasonCode5
, ISNULL(rcode5.CodeDescription, '') AS ReasonDesc5
, ISNULL(slpa.MonetaryAmount5, '') AS Amount5
, ISNULL(slpa.Quantity5, '') AS Qt5
, ISNULL(slpa.ReasonCode6, '') AS ReasonCode6
, ISNULL(rcode6.CodeDescription, '') AS ReasonDesc6
, ISNULL(slpa.MonetaryAmount6, '') AS Amount6
, ISNULL(slpa.Quantity6, '') AS Qt6
, ISNULL(slpr.Qualifier + ' ' + slpr.RemarkCode + ' - ' + rkcode.CodeDescription, '') AS Remark
FROM tbl_ServiceLine_Payments slp
INNER JOIN tbl_Claim_Info c
ON slp.fkClaim = c.pkClaim
INNER JOIN tbl_Entities ent
ON c.fkRenderingServiceProvider = ent.pkEntity
INNER JOIN tbl_Entities pat
ON c.fkPatient = pat.pkEntity
INNER JOIN tbl_Patient_Insurance_Plans ins
ON slp.fkInsurance = ins.pkInsurance
LEFT OUTER JOIN tbl_ServiceLine_Payments_AdjustmentCodes slpa
ON slp.pkServiceLinePayment = slpa.fkServiceLinePayment
LEFT OUTER JOIN tbl_Claim_Adjustment_Group_Codes grp
ON slpa.GroupCode = grp.ClaimAdjustmentGroupCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode1
ON slpa.ReasonCode1 = rcode1.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode2
ON slpa.ReasonCode2 = rcode2.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode3
ON slpa.ReasonCode3 = rcode3.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode4
ON slpa.ReasonCode4 = rcode4.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode5
ON slpa.ReasonCode5 = rcode5.ClaimAdjustmentReasonCode
LEFT OUTER JOIN tbl_Claim_Adjustment_Reason_Codes rcode6
ON slpa.ReasonCode6 = rcode6.ClaimAdjustmentReasonCode
LEFT OUTER JOIN dbo.tbl_ServiceLine_Payments_RemarkCodes slpr
ON slp.pkServiceLinePayment = slpr.fkServiceLinePayment
LEFT OUTER JOIN dbo.tbl_Claim_Advice_Remark_Codes rkcode
ON slpr.RemarkCode = rkcode.ClaimAdviceRemarkCode
WHERE (slp.EOBFileName LIKE @.EOBFileName)
ORDER BY ClaimKey, PaymentKey
here is the table that is created.
PaymentKey ClaimKey ServiceLineKey InsuranceKEy ServiceDate ServiceCode ServiceFee InsurancePayment ProviderKey ProviderName Status PatientName Policy GroupID ClaimTrace GroupCode GroupDescription ReasonCode1 ReasonDesc1 Amount1 Qt1 ReasonCode2 ReasonDesc2 Amount2 Qt2 ReasonCode3 ReasonDesc3 Amount3 Qt3 ReasonCode4 ReasonDesc4 Amount4 Qt4 ReasonCode5 ReasonDesc5 Amount5 Qt5 ReasonCode6 ReasonDesc6 Amount6 Qt6 Remark
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
1107 52 5589 416 02/12/2007 97124 22.00 17.6 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 4.40 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1108 52 5588 416 02/12/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1109 52 5587 416 02/12/2007 98941 36.00 13.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1110 52 5586 416 02/10/2007 72100 38.00 30.4 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.60 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1111 52 5585 416 02/10/2007 72040 35.00 28 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 7.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1112 52 5584 416 02/10/2007 97014 15.00 11.84 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 2.96 0 42 Charges exceed our fee schedule or maximum allowable amount. 0.20 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1113 52 5583 416 02/10/2007 98943 25.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 185 The rendering provider is not eligible to perform the service billed. 25.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE N14 - Payment based on a contractual amount or agreement, fee schedule, or maximum allowable amount.
1114 52 5582 416 02/10/2007 99211 22.00 0 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations 97 Payment is included in the allowance for another service/procedure. 22.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0 HE M144 - Pre-/post-operative care payment is included in the allowance for the surgery/procedure.
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 CO Contractual Obligations A2 Contractual adjustment. 9.20 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
1115 52 5581 416 02/10/2007 98942 46.00 21.8 9053 CARTER Processed as Primary FISCHER, MARY ANN R58246471 7047969996000 PR Patient Responsibility 2 Coinsurance Amount. 15.00 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 0
(11 row(s) affected)
Hi,
could you post it in a more readable format, please?
Y
Subscribe to:
Posts (Atom)