We have an off-shelf program package and it has two tables ARSIA and ARSIAO. The simplified tables are shown here.
ARSIA:
GROUPID IDCUST AMTDUE
RENT 340943 120
PARKING 340943 30
RENT 450001 175
RENT 841190 290
PARKING 841190 40
RENT 950100 500
ARSIAO:
GROUPID IDCUST OPTFIELD VALUE
RENT 340943 ACCOUNT 459-01209
RENT 340943 RUNDATE 20061201
PARKING 340943 ACCOUNT 459-01209
PARKING 340943 RUNDATE 20061201
RENT 450001 ACCOUNT
RENT 450001 RUNDATE 20061201
RENT 841190 ACCOUNT
RENT 841190 RUNDATE 20061201
PARKING 841190 ACCOUNT 37373981
PARKING 841190 RUNDATE 20061201
RENT 950100 ACCOUNT 3801-99-2
RENT 950100 RUNDATE 20061201
Assumptions:
1) For each IDCUST in ARSIA there are two lines in ARSIAO table for that GROUPID.
2) In ARSIAO table "VALUE" field for OPTFIELD='ACCOUNT' can be blank.
3) If there are two GROUPIDs for the same IDCUST in table ARSIAO, the field VALUE for OPTFIELD can only be the same or one can be blank.
I would like to select a unique IDCUST from ARSIA with OPTFIELD='ACCOUNT' is not blank in ARSIAO and Total of AMTDUE if OPTFIELD='ACCOUNT' is not blank for that GROUPID.
The expected result from above tables is:
IDCUST ACCOUNT RUNDATE TOTALAMT
340943 459-01209 20061201 150
841190 37373981 20061201 40
950100 3801-99-2 20061201 500
Note that for IDCUST=841190 the TOTALAMT is 40 not 330. This is because in table ARSIAO for GROUPID='RENT' and
OPTFIELD='ACCOUNT' the field 'VALUE' is blank.
Here is my query that doesn't work well.
SELECT DISTINCT IDCUST,
(SELECT DISTINCT [Value]
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'ACCOUNT' AND Len([Value]) > 0) AS ACCOUNT,
(SELECT MAX(DISTINCT [Value])
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'RUNDATE' AND Len([Value]) > 0) AS RUNDATE,
(SELECT SUM(AMTDUE)
FROM ARSIA
WHERE IDCUST = A1.IDCUST AND (EXISTS
(SELECT DISTINCT [Value]
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'ACCOUNT' AND Len([Value]) > 0))) AS TOTALAMT
FROM ARSIAO A1
WHERE EXISTS
(SELECT DISTINCT [Value]
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'ACCOUNT' AND Len([Value]) > 0)
Can someone please help?
Thanks.
ARSIA:
GROUPID IDCUST AMTDUE
RENT 340943 120
PARKING 340943 30
RENT 450001 175
RENT 841190 290
PARKING 841190 40
RENT 950100 500
ARSIAO:
GROUPID IDCUST OPTFIELD VALUE
RENT 340943 ACCOUNT 459-01209
RENT 340943 RUNDATE 20061201
PARKING 340943 ACCOUNT 459-01209
PARKING 340943 RUNDATE 20061201
RENT 450001 ACCOUNT
RENT 450001 RUNDATE 20061201
RENT 841190 ACCOUNT
RENT 841190 RUNDATE 20061201
PARKING 841190 ACCOUNT 37373981
PARKING 841190 RUNDATE 20061201
RENT 950100 ACCOUNT 3801-99-2
RENT 950100 RUNDATE 20061201
Assumptions:
1) For each IDCUST in ARSIA there are two lines in ARSIAO table for that GROUPID.
2) In ARSIAO table "VALUE" field for OPTFIELD='ACCOUNT' can be blank.
3) If there are two GROUPIDs for the same IDCUST in table ARSIAO, the field VALUE for OPTFIELD can only be the same or one can be blank.
I would like to select a unique IDCUST from ARSIA with OPTFIELD='ACCOUNT' is not blank in ARSIAO and Total of AMTDUE if OPTFIELD='ACCOUNT' is not blank for that GROUPID.
The expected result from above tables is:
IDCUST ACCOUNT RUNDATE TOTALAMT
340943 459-01209 20061201 150
841190 37373981 20061201 40
950100 3801-99-2 20061201 500
Note that for IDCUST=841190 the TOTALAMT is 40 not 330. This is because in table ARSIAO for GROUPID='RENT' and
OPTFIELD='ACCOUNT' the field 'VALUE' is blank.
Here is my query that doesn't work well.
SELECT DISTINCT IDCUST,
(SELECT DISTINCT [Value]
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'ACCOUNT' AND Len([Value]) > 0) AS ACCOUNT,
(SELECT MAX(DISTINCT [Value])
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'RUNDATE' AND Len([Value]) > 0) AS RUNDATE,
(SELECT SUM(AMTDUE)
FROM ARSIA
WHERE IDCUST = A1.IDCUST AND (EXISTS
(SELECT DISTINCT [Value]
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'ACCOUNT' AND Len([Value]) > 0))) AS TOTALAMT
FROM ARSIAO A1
WHERE EXISTS
(SELECT DISTINCT [Value]
FROM ARSIAO
WHERE IDCUST = A1.IDCUST AND OPTFIELD = 'ACCOUNT' AND Len([Value]) > 0)
Can someone please help?
Thanks.