January 10, 2018 at 2:39 pm
I'm trying add specific qtys together in the same table where 3 fields match. For example
| RxBatch | RxNo | PatID | RoNo | DispenseDt | FacID | Qty | NDC |
| TEST1 | 7175542 | 753 | 44 | 00:00.0 | WYNHAM | 30 | 143126710 |
| TEST1 | 7201306 | 753 | 64 | 00:00.0 | WYNHAM | 60 | 27272727272 |
| TEST2 | 7201306 | 753 | 64 | 00:00.0 | WYNHAM | 15 | 27272727272 |
| TEST2 | 7211572 | 753 | 67 | 00:00.0 | WYNHAM | 18 | 50111043403 |
if this is my data and I want line 2 & 3 to add there qtys together based on the matching fields of facid, patid and ndc.
Here is my script:
UPDATE FWDB.RX.RXS
SET QTY=(select Qty where RxBatch='test1')+(select qty where RxBatch='test2')
WHERE FacID=FacID and PatID=PatID and NDC=NDC and RxBatch='test1' or RxBatch='test2'
Can anyone tell me what I'm doing wrong?
January 10, 2018 at 3:04 pm
spryngbyrd - Wednesday, January 10, 2018 2:39 PMI'm trying add specific qtys together in the same table where 3 fields match. For example
RxBatch RxNo PatID RoNo DispenseDt FacID Qty NDC TEST1 7175542 753 44 00:00.0 WYNHAM 30 143126710 TEST1 7201306 753 64 00:00.0 WYNHAM 60 27272727272 TEST2 7201306 753 64 00:00.0 WYNHAM 15 27272727272 TEST2 7211572 753 67 00:00.0 WYNHAM 18 50111043403 if this is my data and I want line 2 & 3 to add there qtys together based on the matching fields of facid, patid and ndc.
Here is my script:
UPDATE FWDB.RX.RXS
SET QTY=(select Qty where RxBatch='test1')+(select qty where RxBatch='test2')
WHERE FacID=FacID and PatID=PatID and NDC=NDC and RxBatch='test1' or RxBatch='test2'Can anyone tell me what I'm doing wrong?
just to be sure...what results are you expecting from this sample data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 10, 2018 at 3:14 pm
I do apologize, I'm new to SQL and posting about SQL. Usually when I'm trying to figure something out I make a table in SQLexpress with sample data to test the script I'm working on. That's all that the sample data is, I wasn't sure if it would be helpful or not to post it.
January 10, 2018 at 4:08 pm
Here's my best guess of what you want to do:
UPDATE RXS
SET QTY=RXS_Totals.Qty_Total
FROM FWDB.RX.RXS RXS
INNER JOIN (
SELECT FacID, PatID, NDC, SUM(Qty) AS Qty_Total
FROM FWDB.RX.RXS
WHERE RxBatch='test1' OR RxBatch='test2'
GROUP BY FacID, PatID, NDC
HAVING MIN(RxBatch) <> MAX(RxBatch)
) AS RXS_Totals ON RXS_Totals.FacID = RXS.FacID AND
RXS_Totals.PatID = RXS.PatID AND
RXS_Totals.NDC = RXS.NDC
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 10, 2018 at 4:11 pm
spryngbyrd - Wednesday, January 10, 2018 3:14 PMI do apologize, I'm new to SQL and posting about SQL. Usually when I'm trying to figure something out I make a table in SQLexpress with sample data to test the script I'm working on. That's all that the sample data is, I wasn't sure if it would be helpful or not to post it.
Welcome to SSC! It is hard to know at first what to post. Just like you make a table, sample data to test things so do folks up here who want to help out. It's too much for everyone to be making scripts and then we end up with something different from what you are really using and then the directions change, etc and it can be real time consuming to do that with all of the posts up here. So with queries especially, you'd want to post the ddl to create any tables, indexes, and such that is relevant and post sample data and the expected results. It makes things a lot faster and really helps people who are trying to help you out. You can find an article up here that shows you what to post - it's really so that people can give you the best help:
Forum Etiquette: How to post data/code on a forum to get the best help
Then when you are posting SQL scripts, towards the bottom of the window when you are typing your post there is a SQL Code button. Click on that and past your code in between the brackets. It keeps the format (kind of) of the scripts you post.
Sue
January 11, 2018 at 7:58 am
Thank you so much for the information and the help I really appreciate it
January 19, 2018 at 12:32 pm
This scripted worked beautifully, Thank you so much.
If I need this script to also sum day supply how would I go about adding that, also what would I change to have to qty sum taken from both rxbatch but only change the qty in one of the batches? Any help would be appreciated.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply