Hi, I am trying to get unique records as a result of the following query but it is not working-
SELECT
DISTINCT A.ACTINDX,
A.ACTNUMBR_1,
A.ACTNUMBR_2,
A.ACTNUMBR_3,
A.ACTNUMBR_4,
A.ACCTTYPE,
A.ACTDESCR,
A.PSTNGTYP,
A.ACCATNUM,
A. TPCLBLNC,
B.TRXDATE
FROM GL00100 A
JOIN GL20000 B ON
A.ACTINDX = B.ACTINDX
WHERE
B.TRXDATE BETWEEN '04-01-2014' AND GETDATE()
OR
A.CREATDDT >'04-01-2014'
I would like just one Acccount Index for every row, any help
*This post is locked for comments
How about this:
SELECT Max(DISTINCT gl20000.trxdate) AS LatestTrxDate
, gl00105.actnumst
, gl00100.creatddt
, gl00100.actnumbr_1
, gl00100.actnumbr_2
, gl00100.actnumbr_3
, gl00100.actnumbr_4
, gl00100.accttype
, gl00100.pstngtyp
, gl00100.actdescr
, gl00100.accatnum
, gl00100.tpclblnc
FROM gl00100
INNER JOIN gl00105
ON gl00100.actindx = gl00105.actindx
LEFT OUTER JOIN gl20000 AS gl20000
ON gl00105.actindx = gl20000.actindx
GROUP BY gl00105.actnumst
, gl00100.creatddt
, gl00100.actnumbr_1
, gl00100.actnumbr_2
, gl00100.actnumbr_3
, gl00100.actnumbr_4
, gl00100.accttype
, gl00100.pstngtyp
, gl00100.actdescr
, gl00100.accatnum
, gl00100.tpclblnc
HAVING ( Max(DISTINCT gl20000.trxdate) BETWEEN
CONVERT(DATETIME, '2014-04-01 00:00:00',
102) AND Getdate() )
OR ( gl00100.creatddt > CONVERT(DATETIME, '2014-04-01 00:00:00', 102) )
ORDER BY gl00105.actnumst
I think it will give you every account created after 4-1-2014 or used in a transaction since 4-1-2014 regardless of when it was created.
Kind regards,
Leslie
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
