Dear All (any help will be greatly appreciated)
Database version: SQL 2000
I have created the following query which combines 2 tables containing multiple rows to the table CPCASE which holds unique records.
SELECT
C.SERNO, C.DOFF, C.REGNO, C.STATE, C.STATE_DATE, P.DESC1,
SUM(CASE WHEN BOX = 'H' THEN TOTAL ELSE 0 END) AS "TOTAL CHARGE",
SUM(CASE WHEN BOX = 'P' THEN TOTAL ELSE 0 END) AS "TOTAL PAID",
SUM(CASE WHEN BOX = 'C' THEN TOTAL ELSE 0 END) AS "TOTAL CREDIT",
SUM(CASE WHEN BOX = 'H' THEN TOTAL ELSE 0 END) - abs(SUM(CASE WHEN BOX = 'P' THEN TOTAL ELSE 0 END)) - abs(SUM(CASE WHEN BOX = 'C' THEN TOTAL ELSE 0 END)) as "OUTSTANDING TOTAL"
FROM CPCASE C
JOIN CPPROG P ON P.CODE = C.STATE AND C.TYPE = P.TYPE
JOIN CPPOTH T ON C.SERNO = T.SERNO
JOIN CPHIST H ON H.SERNO = C.SERNO
WHERE DOFF BETWEEN '01-04-2007' AND '01-07-2007'AND
C.MAKE <> 'TEST' AND
C.OFF1 NOT LIKE 'W%' AND
C.OFF1 <> 'TST' AND
(C.REGNO <> 'VOID' AND
C.REGNO <> 'TEST') AND
C.POCODE <> 'TEST' AND
C.COLOUR <> 'TEST' AND
C.STATUS = 'L' AND
C.CPCODE <> 'TST'
GROUP BY C.SERNO, C.DOFF, C.REGNO, C.STATUS, C.STATE, C.STATE_DATE, P.DESC1
HAVING SUM(CASE WHEN H.INITS NOT IN ('', 'AUTO','LJ','MAD','BM','AIE','VK', 'SQLUP') Then 1 Else 0 End) > '0'
The tables contain the following info
CPCASE - unique row of data
CPPROG - Holds data to explain the current status of the data in CPCASE. Can be referenced 'one to one'
CPPOTH - Stores the payment history for data held in CPCASE and is referenced in a 'one to many' relationship
CPHIST - Stores the status history for data held in CPCASE and is referenced in a 'one to many' relationship
What I am attemtping to do is return a single row of data which contains the total CHARGE, PAID, CREDIT and OUTSTANDING BALANCE for each individual case. I have limited the returned data to one row per set of CPCASE data by grouping.
The problem I am finding is that when I join CPHIST to the query I inflate the true financial values returned from CPPOTH as I create a many to many relationship.
The results I obtain are:
Case number date of issue Current state Last ctatus change Current status Total Charge Total Paid Total Credit Total Outstanding
BM13110327 04/01/2007 398 13/02/2007 LETTER RECEIVED - REPLY OUTSTANDING 4 - 8 WEEKS 240 0 0 240
BM13110429 05/01/2007 1175 17/01/2007 ON HOLD - DISABLED BADGE VERIFICATIONS 240 0 0 240
BM14147424 04/01/2007 1399 19/02/2007 LETTER RECEIVED - REPLY OUTSTANDING 2-4 WEEKS 360 -180 0 180
BM15143236 04/01/2007 398 14/02/2007 LETTER RECEIVED - REPLY OUTSTANDING 4 - 8 WEEKS 300 0 0 300
If I remove CPHIST from the query the results are as follows which show the correct financial values without multiplying them
Case number date of issue Current state Last status change Current status Total Charge Total Paid Total Credit Total Outstanding
BM12137430 04/01/2007 09/04/1900 04/01/2007 PCN ISSUED 60 0 0 60
BM13110316 04/01/2007 26/06/1905 02/02/2007 NOTICE TO OWNER POSTED 60 0 0 60
BM19157196 04/01/2007 26/06/1905 02/02/2007 NOTICE TO OWNER POSTED 60 0 0 60
The reason I need to include the CPHIST table is that I need to exclude data from CPCASE if the data has been updated by a member of staff. This is why I have used the following script
HAVING SUM(CASE WHEN H.INITS NOT IN ('', 'AUTO','LJ','MAD','BM','AIE','VK', 'SQLUP') Then 1 Else 0 End) > '0'
The initials above are all system administrators so are not to be excluded.
I hope that I have made things clear and not missed anything. If you can help and need anything further the please ask. I have tried to find my solution in the forums but have struggled.
Many Thanks
Lewis
United Kingdom
Database version: SQL 2000
I have created the following query which combines 2 tables containing multiple rows to the table CPCASE which holds unique records.
SELECT
C.SERNO, C.DOFF, C.REGNO, C.STATE, C.STATE_DATE, P.DESC1,
SUM(CASE WHEN BOX = 'H' THEN TOTAL ELSE 0 END) AS "TOTAL CHARGE",
SUM(CASE WHEN BOX = 'P' THEN TOTAL ELSE 0 END) AS "TOTAL PAID",
SUM(CASE WHEN BOX = 'C' THEN TOTAL ELSE 0 END) AS "TOTAL CREDIT",
SUM(CASE WHEN BOX = 'H' THEN TOTAL ELSE 0 END) - abs(SUM(CASE WHEN BOX = 'P' THEN TOTAL ELSE 0 END)) - abs(SUM(CASE WHEN BOX = 'C' THEN TOTAL ELSE 0 END)) as "OUTSTANDING TOTAL"
FROM CPCASE C
JOIN CPPROG P ON P.CODE = C.STATE AND C.TYPE = P.TYPE
JOIN CPPOTH T ON C.SERNO = T.SERNO
JOIN CPHIST H ON H.SERNO = C.SERNO
WHERE DOFF BETWEEN '01-04-2007' AND '01-07-2007'AND
C.MAKE <> 'TEST' AND
C.OFF1 NOT LIKE 'W%' AND
C.OFF1 <> 'TST' AND
(C.REGNO <> 'VOID' AND
C.REGNO <> 'TEST') AND
C.POCODE <> 'TEST' AND
C.COLOUR <> 'TEST' AND
C.STATUS = 'L' AND
C.CPCODE <> 'TST'
GROUP BY C.SERNO, C.DOFF, C.REGNO, C.STATUS, C.STATE, C.STATE_DATE, P.DESC1
HAVING SUM(CASE WHEN H.INITS NOT IN ('', 'AUTO','LJ','MAD','BM','AIE','VK', 'SQLUP') Then 1 Else 0 End) > '0'
The tables contain the following info
CPCASE - unique row of data
CPPROG - Holds data to explain the current status of the data in CPCASE. Can be referenced 'one to one'
CPPOTH - Stores the payment history for data held in CPCASE and is referenced in a 'one to many' relationship
CPHIST - Stores the status history for data held in CPCASE and is referenced in a 'one to many' relationship
What I am attemtping to do is return a single row of data which contains the total CHARGE, PAID, CREDIT and OUTSTANDING BALANCE for each individual case. I have limited the returned data to one row per set of CPCASE data by grouping.
The problem I am finding is that when I join CPHIST to the query I inflate the true financial values returned from CPPOTH as I create a many to many relationship.
The results I obtain are:
Case number date of issue Current state Last ctatus change Current status Total Charge Total Paid Total Credit Total Outstanding
BM13110327 04/01/2007 398 13/02/2007 LETTER RECEIVED - REPLY OUTSTANDING 4 - 8 WEEKS 240 0 0 240
BM13110429 05/01/2007 1175 17/01/2007 ON HOLD - DISABLED BADGE VERIFICATIONS 240 0 0 240
BM14147424 04/01/2007 1399 19/02/2007 LETTER RECEIVED - REPLY OUTSTANDING 2-4 WEEKS 360 -180 0 180
BM15143236 04/01/2007 398 14/02/2007 LETTER RECEIVED - REPLY OUTSTANDING 4 - 8 WEEKS 300 0 0 300
If I remove CPHIST from the query the results are as follows which show the correct financial values without multiplying them
Case number date of issue Current state Last status change Current status Total Charge Total Paid Total Credit Total Outstanding
BM12137430 04/01/2007 09/04/1900 04/01/2007 PCN ISSUED 60 0 0 60
BM13110316 04/01/2007 26/06/1905 02/02/2007 NOTICE TO OWNER POSTED 60 0 0 60
BM19157196 04/01/2007 26/06/1905 02/02/2007 NOTICE TO OWNER POSTED 60 0 0 60
The reason I need to include the CPHIST table is that I need to exclude data from CPCASE if the data has been updated by a member of staff. This is why I have used the following script
HAVING SUM(CASE WHEN H.INITS NOT IN ('', 'AUTO','LJ','MAD','BM','AIE','VK', 'SQLUP') Then 1 Else 0 End) > '0'
The initials above are all system administrators so are not to be excluded.
I hope that I have made things clear and not missed anything. If you can help and need anything further the please ask. I have tried to find my solution in the forums but have struggled.
Many Thanks
Lewis
United Kingdom
