SELECT query from linked tables 1

[gord2]

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.

 

[alexjones]

This seemed to work well with your example data:

CREATE TABLE #ARSIA (
  groupid varchar(7),
  idcust int,
  amtDue int
)

CREATE TABLE #ARSIAO (
  groupid varchar(7),
  idcust int,
  optfield char(7),
  value varchar(50)
)


INSERT INTO #ARSIA (groupid, idcust, amtDue)
VALUES('RENT', 340943, 120)
INSERT INTO #ARSIA (groupid, idcust, amtDue)
VALUES('PARKING', 340943, 30)
INSERT INTO #ARSIA (groupid, idcust, amtDue)
VALUES('RENT', 450001, 175)
INSERT INTO #ARSIA (groupid, idcust, amtDue)
VALUES('RENT', 841190, 290)
INSERT INTO #ARSIA (groupid, idcust, amtDue)
VALUES('PARKING', 841190, 40)
INSERT INTO #ARSIA (groupid, idcust, amtDue)
VALUES('RENT', 950100, 500)

INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 340943, 'ACCOUNT', '459-01209')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 340943, 'RUNDATE', '20061201')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('PARKING', 340943, 'ACCOUNT', '459-01209')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('PARKING', 340943, 'RUNDATE', '20061201')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 450001, 'ACCOUNT', '')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 450001, 'RUNDATE', '20061201')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 841190, 'ACCOUNT', '')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 841190, 'RUNDATE', '20061201')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('PARKING', 841190, 'ACCOUNT', '37373981')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('PARKING', 841190, 'RUNDATE', '20061201')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 950100, 'ACCOUNT', '3801-99-2')
INSERT INTO #ARSIAO(groupid, idcust, optfield, value)
VALUES('RENT', 950100, 'RUNDATE', '20061201')

SELECT a.idcust, o.account, o.rundate, totalamt = sum(a.amtdue)
FROM #ARSIA AS a
JOIN 
(SELECT o.idcust, o.groupid,
        account = max(case when o.optfield = 'ACCOUNT' then o.value else null end),
        rundate = max(case when o.optfield = 'RUNDATE' then o.value else null end)
 FROM #ARSIAO as o
 GROUP BY o.idcust, o.groupid 
 HAVING max(case when o.optfield = 'ACCOUNT' then o.value else null end) <> ''
 AND max(case when o.optfield = 'RUNDATE' then o.value else null end) <> '') as o
ON o.groupid = a.groupid and o.idcust = a.idcust
GROUP BY a.idcust, o.account, o.rundate
ORDER BY a.idcust

The SELECT statement aliased as o pivots your optfield values into resultset column names and removes blank values from these pivoted columns. This result is then joined to #ARSIA to obtain the correct total amount.

Hope this helps

 

[gord2]

It works.
Thank you very much.