June 8, 2014 at 7:57 pm
Hello,
I am new to SQL and scripts in general so please bear with me. There are 3 tables involved: CLIENTS, WORKTIME and BILLING. Each table contains 5 fields that should be updated: Primary (PrimID), Insurance (InsID), Group (GrpID), Program (ProgID) and Billing Code (CltBillID). Tables CLIENTS contains the most up-to-date values for these fields. Only CLIENTS records with a GroupID of a specified value are to be updated. Table WORKTIME and Table BILLING both need to be updated with the values in Table CLIENTS. Table WORKTIME, however should only be updated within a specified date parameter and Table BILLING records should only be updated if they match the records in table WORKTIME. All tables are connected by the ClientID.
This is the script I have so far. Remember I have no real experience with this 🙂
SELECT *
FROM WORKTIME INNER JOIN BILLING
WHERE WORKTIME.ClientID IN (SELECT * FROM CLIENTS WHERE CLIENTS.ClientID = WORKTIME.ClientID) IN
(SELECT * FROM CLIENTS WHERE CLIENTS.GroupID = 999) AND
(WORKTIME.DateCreated => ‘MM/DD/2014’ <= ‘MM/DD/2014’) AND
(WORKTIME.ClientID = BILLING.ClientID),
UPDATE WORKTIME
SETWORKTIME.GroupID=CLIENTS.GroupID
WORKTIME.InsID = CLIENTS.InsID
WORKTIME_ProgID = CLIENTS.ProgID
WORKTIME_PrimID = CLIENTS_PrimID
WORKTIME.CltBillID = CLIENTS.CltBillID
WHERE SCHEDULES.CltID = CLIENTS.ClientID ;
UPDATE BILLING
SET BILLING.GroupID = CLIENTS.GroupID
BILLING.InsID = CLIENTS.InsID
BILLING_ProgID = CLIENTS_ProgID
BILLING_PrimID = CLIENTS_PrimID
BILLING.CltBILLID = CLIENTS.CltBillID
WHERE BILLING.ClientID = WORKTIME.CLientID ;
June 8, 2014 at 10:29 pm
June 9, 2014 at 8:32 am
wcharlton1 (6/8/2014)
Hello,I am new to SQL and scripts in general so please bear with me. There are 3 tables involved: CLIENTS, WORKTIME and BILLING. Each table contains 5 fields that should be updated: Primary (PrimID), Insurance (InsID), Group (GrpID), Program (ProgID) and Billing Code (CltBillID). Tables CLIENTS contains the most up-to-date values for these fields. Only CLIENTS records with a GroupID of a specified value are to be updated. Table WORKTIME and Table BILLING both need to be updated with the values in Table CLIENTS. Table WORKTIME, however should only be updated within a specified date parameter and Table BILLING records should only be updated if they match the records in table WORKTIME. All tables are connected by the ClientID.
This is the script I have so far. Remember I have no real experience with this 🙂
SELECT *
FROM WORKTIME INNER JOIN BILLING
WHERE WORKTIME.ClientID IN (SELECT * FROM CLIENTS WHERE CLIENTS.ClientID = WORKTIME.ClientID) IN
(SELECT * FROM CLIENTS WHERE CLIENTS.GroupID = 999) AND
(WORKTIME.DateCreated => ‘MM/DD/2014’ <= ‘MM/DD/2014’) AND
(WORKTIME.ClientID = BILLING.ClientID),
UPDATE WORKTIME
SETWORKTIME.GroupID=CLIENTS.GroupID
WORKTIME.InsID = CLIENTS.InsID
WORKTIME_ProgID = CLIENTS.ProgID
WORKTIME_PrimID = CLIENTS_PrimID
WORKTIME.CltBillID = CLIENTS.CltBillID
WHERE SCHEDULES.CltID = CLIENTS.ClientID ;
UPDATE BILLING
SET BILLING.GroupID = CLIENTS.GroupID
BILLING.InsID = CLIENTS.InsID
BILLING_ProgID = CLIENTS_ProgID
BILLING_PrimID = CLIENTS_PrimID
BILLING.CltBILLID = CLIENTS.CltBillID
WHERE BILLING.ClientID = WORKTIME.CLientID ;
Hi,
you have the right idea, but your syntax is a bit off. You should keep in mind that the multiple SQL statements are independent of each other, unless you are dealing with updated values, or temp tables.
I recommend first trying to simply select the value you are dealing with, to make sure they are the ones you will want to change with an update statement. based on what you said and wrote, I have interpreted it as the following, which may be easier to read / understand and work with:
SELECT *
FROM
Clients c
INNER JOIN Worktime W ON c.ClientID = w.ClientID
LEFT JOIN billing B ON b.ClientID = w.ClientID
WHERE
c.GroupID = 999
AND
(WORKTIME.DateCreated > 'MM/DD/2014' AND WORKTIME.DateCreated <= 'MM/DD/2014')
I used a left join, so that it will only bring in records from billing that match. I assume all your tables join by clientID, but it may not be the case.
once you have what you want, you can essentially copy and paste everything from the "FROM" line down, and paste it to an update statement. Or build above the select, and comment the select out.
If you comment something out, you can still run what is commented if it is highlighted and executed without the comment dashes.
you will likely need to run two updates, that are almost the same.
the worktime statement does not need to deal with the Billing table, so the join I commented out.
For the second update statement on Billing, you want to only deal with the records that joining, so in that situation I wrote an inner join.
ex:
UPDATE W
SETW.GroupID=C.GroupID
W.InsID = C.InsID
W.ProgID = C.ProgID
W.PrimID = C.PrimID
W.CltBillID = C.CltBillID
--SELECT *
FROM
Clients c
INNER JOIN Worktime W ON c.ClientID = w.ClientID
--LEFT JOIN billing B ON b.ClientID = w.ClientID
WHERE
c.GroupID = 999
AND
(W.DateCreated > 'MM/DD/2014' AND W.DateCreated <= 'MM/DD/2014')
UPDATE B
SETB.GroupID=C.GroupID
B.InsID = C.InsID
B.ProgID = C.ProgID
B.PrimID = C.PrimID
B.CltBillID = C.CltBillID
--SELECT *
FROM
Clients c
INNER JOIN Worktime W ON c.ClientID = w.ClientID
INNER JOIN billing B ON b.ClientID = w.ClientID
WHERE
c.GroupID = 999
AND
(W.DateCreated > 'MM/DD/2014' AND W.DateCreated <= 'MM/DD/2014')
hope that helps clarify things, and sets you on your way.
June 9, 2014 at 8:50 am
Mike,
Thanks so much for your help. Your explanations are very helpful and I can actually understand the code and the nuances you introduced. Also makes sense to test the dataset as well. Very helpful and enlightening. I'll let you know how things work out.
Thanks.
Winston.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply