March 6, 2008 at 9:50 am
SELECT depositid, depositdate, batchid
INTO #deposits
SELECT batchid, type, amount, adj
INTO #batch
SELECT batchid, description, merchantno, settlementdate, amount
INTO #batch_detail
SELECT b.batchid, d.depositid, b.amount, b.adj, "amount_detail"=ISNULL(bd.amount,b.amount)
FROM #batch b, #deposits d, #batch_detail bd
WHERE b.batchid = d.batchid AND
b.batchid *= bd.batchid
ORDER BY d.depositdate
The #batch_detail table has repeating rows (more than one entry makes up a batch),
so I get result set that includes:
1500559441500271497233.50 -581.5235.00
1500559441500271497233.50 -581.5253.50
1500559441500271497233.50 -581.5276.50
1500559441500271497233.50 -581.5277.00
1500559441500271497233.50 -581.5296.00
1500559441500271497233.50 -581.52103.00
1500559441500271497233.50 -581.52439.00
1500559441500271497233.50 -581.52654.50
1500559441500271497233.50 -581.521150.74
1500559441500271497233.50 -581.521703.51
1500559441500271497233.50 -581.523426.27
How would I write this to get one adj value per batchid?
Thanks
March 6, 2008 at 10:25 am
Modify this:
SELECT batchid, description, merchantno, settlementdate, amount
INTO #batch_detail
SELECT DISTINCT batchid, description, merchantno, settlementdate, amount INTO #batch_detail
HTH,
Arun
March 6, 2008 at 10:38 am
amount is distinct (values can match, but I need all payments)
March 8, 2008 at 6:56 pm
frank.perkins (3/6/2008)
amount is distinct (values can match, but I need all payments)
The problem is that we have no idea what this means. You have no fields or tables called "payment", so this is just a mystery to us.
What you need to do is tell us, if you query should return only one row instead of the long list in your example, then specifically what should be in that row, especially in that last column (amount)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2008 at 4:33 am
Frank
SELECT batchid, description, merchantno, settlementdate, amount
INTO #batch_detail
In this table you have multiple rows with the same batchid, which is your join column to the other two tables. How do you decide which row to take? For example, min or max amount? Alternatively, you could sum the amount column for each batchid.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 22, 2008 at 8:25 am
rbarryyoung (3/8/2008)
The problem is that we have no idea what this means. You have no fields or tables called "payment", so this is just a mystery to us.What you need to do is tell us, if you query should return only one row instead of the long list in your example, then specifically what should be in that row, especially in that last column (amount)?
Sorry, the post doesn't really doesn't make a lot of sense.
The amount field is the payment for a service.
The system is a medical application. For a given visit, there may be multiple services and each service has to be billed separately. Frequently a service amount has to be adjusted down (the insurance company only pays X even if we bill Y).
The query is attempting to select the total originally billed amount (sum the amount field) and the total adjustment. The problem is each service within a batch (the batch is the total of all services for a visit) is getting an adjustment value which is really a repeat of the sum of the adjustment for each service in the batch.
Instead of returning something like this
amount adjustment
10 2
10
10 5
10 1
---------------------
40 8
It is returning
10 8
10 8
10 8
10 8
---------------------
40 32
But the users would really prefer to see
10 8
10
10
10
--------------------
40 8
Thanks
March 22, 2008 at 9:35 am
OK, I believe that I understand now.
Frank.M.Perkins (3/22/2008)
The system is a medical application. For a given visit, there may be multiple services and each service has to be billed separately. Frequently a service amount has to be adjusted down (the insurance company only pays X even if we bill Y).The query is attempting to select the total originally billed amount (sum the amount field) and the total adjustment. The problem is each service within a batch (the batch is the total of all services for a visit) is getting an adjustment value which is really a repeat of the sum of the adjustment for each service in the batch.
As it happens, I worked in this field for several years and I am familiar with what you describe.
The problem is that what you show:
[font="Courier New"]
10 8
10
10
10
--------------------
40 8
[/font]
It is actually quite hard to do in this SQL and would usually be done by the front-end or by some presentation software like a report generator. However, what you show above is also not how this is usually displayed, but rather:
[font="Courier New"]
10
10
10
10
8
--------------------
40 8
[/font]
In order to prevent the assumption that the total adj is related to any single payment detail. As it turns out, this is also relatively easier to compose in SQL:
SELECT batchid--the combined rows
, depositid
, amount
, adj
, amount_detail
FROM (
SELECT b.batchid--the payments rows
, d.depositid
, d.depositdate
, b.amount
, Cast(NULL as money) as [adj]
, Coalesce(bd.amount,b.amount) as [amount_detail]
FROM #batch b
Inner Join #deposits d ON (b.batchid = d.batchid)
Left Outer Join #batch_detail bd ON (b.batchid = bd.batchid)
UNION ALL
SELECT b.batchid--the adj row(s)
, d.depositid
, d.depositdate
, b.amount
, b.adj
, Cast(NULL as money) as [amount_detail]
FROM #batch b
Inner Join #deposits d ON (b.batchid = d.batchid)
Left Outer Join #batch_detail bd ON (b.batchid = bd.batchid)
) AS batch_rows
ORDER BY depositdate, batchid, adj
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply