Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help. I just need to bring duplicates accouts

Author  Topic 

hizakemi
Starting Member

33 Posts
Posted - 2006-03-15 : 12:20:09
Hi, Guys. I have union queries (see the attachment). I would like to bring only duplicate ACCT (and keep all the fields from union query)- after the union query is done. Any suggestion?

Thk


DECLARE @STARTDATE datetime
DECLARE @ENDDATE datetime


SET @STARTDATE = '3/7/2006'
SET @ENDDATE = '3/8/2006'

SELECT
T.[BANK NUMBER],
T.[ACCT],
LEFT(T.[SERIAL NUMBER], 8) [SERIAL NUMBER],
T.[ACCT],
CONVERT(NUMERIC(16,2),T.[ITEM AMOUNT]) [ITEM AMOUNT],
T.[DEPARTMENT NAME],
T.[POSTING DATE],
T.[PLACEMENT DATE],
T.[RELEASE DATE],
T.[ACCT],
T.[BANK NUMBER],
T.[ITEM AMOUNT],
T.[HOLD AMOUNT],
T.[SERIAL NUMBER],
T.[DEPARTMENTID],
T.[HOLD TYPE],
T.[EARLY RELEASE REASON],
T.[DRAWEE ACCOUNT]

FROM

(

SELECT
D.DepartmentName [DEPARTMENT NAME],
ACCT.PostingDate [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.SerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.AccountNumber [DRAWEE ACCOUNT]
FROM
dbo.tbXrefDepartments D
JOIN
dbo.tbHolds H
JOIN
dbo.tbItemASI19Rpt I
ON
(H.ItemID = I.ItemId)
JOIN
dbo.tbAcctASI19Rpt ACCT
ON
(I.AccountId = ACCT.AccountId)
ON
(D.DepartmentId = H.DepartmentID)
WHERE
(H.PlacementDate BETWEEN @startdate and @enddate) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID='2') AND
(H.HoldTypeID IN ('3', '5')) AND
(H.EarlyReleaseReasonID Is Null) AND
(H.PlacedViaHMS =1)
GROUP BY
D.DepartmentName,
ACCT.PostingDate ,
H.PlacementDate ,
H.ReleaseDate ,
ACCT.BankNumber,
I.ItemAmount,
H.HoldAmount ,
I.SerialNumber,
H.DepartmentID ,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.AccountNumber,
ACCT.AccountNumber


UNION ALL

SELECT
D.DepartmentName [DEPARTMENT NAME],
ACCT.DepositDate [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.SerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.MakerAccountNumber [DRAWEE ACCOUNT]
FROM
dbo.tbXrefDepartments D
JOIN

dbo.tbHolds H
JOIN
dbo.tbItemEARNSRpt I
ON
(H.ItemID = I.ItemID)
JOIN
dbo.tbAcctEARNSRpt ACCT
ON
(I.AccountID = ACCT.AccountID)
ON
(D.DepartmentId = H.DepartmentID)
WHERE

(H.PlacementDate BETWEEN @STARTDATE and @ENDDATE) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID='8') AND (
(H.HoldTypeID)='3' Or (H.HoldTypeID)='5') AND
(H.EarlyReleaseReasonID Is Null) AND
(H.PlacedViaHMS = 1)
GROUP BY
D.DepartmentName ,
ACCT.DepositDate,
H.PlacementDate ,
H.ReleaseDate,
ACCT.BankNumber,
I.ItemAmount,
H.HoldAmount,
I.SerialNumber,
H.DepartmentID,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.MakerAccountNumber,
ACCT.AccountNumber

UNION ALL

SELECT
D.DepartmentName [DEPARTMENT NAME],
ACCT.PostingDate [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.SerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.ItemAccountNumber [DRAWEE ACCOUNT]
FROM
dbo.tbXrefDepartments D
JOIN
dbo.tbHolds H
JOIN
dbo.tbItemMMSRpt I
ON
(H.ItemID = I.ItemId)
JOIN
dbo.tbAcctMMSRpt ACCT
ON
(I.AccountId = ACCT.AccountId)
ON
(D.DepartmentId = H.DepartmentID)
WHERE
(H.PlacementDate BETWEEN @STARTDATE and @ENDDATE) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID='12') AND
((H.HoldTypeID ='3') Or (H.HoldTypeID='5')) AND
(H.EarlyReleaseReasonID Is Null) AND
(H.PlacedViaHMS = 1)
GROUP BY
D.DepartmentName,
ACCT.PostingDate,
H.PlacementDate,
H.ReleaseDate,
ACCT.BankNumber,
I.ItemAmount,
H.HoldAmount,
I.SerialNumber ,
H.DepartmentID ,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.ItemAccountNumber,
ACCT.AccountNumber

UNION ALL

SELECT
D.DepartmentName [DEPARTMENT NAME],
ACCT.PostingDate [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.itemSerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.ItemAccountNumber [DRAWEE ACCOUNT]
FROM
dbo.tbXrefDepartments D
JOIN
dbo.tbHolds H
JOIN
dbo.tbItemONURpt I
ON
(H.ItemID = I.ItemId)
JOIN
dbo.tbAcctONURpt ACCT
ON
(I.AccountId = ACCT.AccountId)
ON
(D.DepartmentId = H.DepartmentID)
WHERE
(H.PlacementDate BETWEEN @STARTDATE and @ENDDATE) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID ='13') AND
((H.HoldTypeID='3') Or (H.HoldTypeID='5')) AND
(H.EarlyReleaseReasonID Is Null) AND
(H.PlacedViaHMS = 1)
GROUP BY
D.DepartmentName,
ACCT.PostingDate,
H.PlacementDate,
H.ReleaseDate,
ACCT.BankNumber,
I.ItemAmount,
H.HoldAmount,
I.ITEMSerialNumber ,
H.DepartmentID ,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.ItemAccountNumber,
ACCT.AccountNumber

UNION ALL

SELECT
D.DepartmentName [DEPARTMENT NAME],
ACCT.PostingDate [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.SerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.OriginalAccount [DRAWEE ACCOUNT]
FROM
dbo.tbXrefDepartments D
JOIN
dbo.tbHolds H
JOIN
dbo.tbItemPPSRpt I
ON
(H.ItemID = I.ItemId)
JOIN
dbo.tbAcctPPSRpt ACCT
ON
(I.AccountId = ACCT.AccountId)
ON
(D.DepartmentId = H.DepartmentID)
WHERE
(H.PlacementDate BETWEEN @STARTDATE and @ENDDATE) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID='3') AND
((H.HoldTypeID='3') Or (H.HoldTypeID='5')) AND
(H.EarlyReleaseReasonID Is Null)
GROUP BY
D.DepartmentName,
ACCT.PostingDate,
H.PlacementDate,
H.ReleaseDate,
ACCT.BankNumber,
I.ItemAmount,
H.HoldAmount,
I.SerialNumber ,
H.DepartmentID ,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.OriginalAccount,
ACCT.AccountNumber

UNION ALL

SELECT
D.DepartmentName [DEPARTMENT NAME],
I.DEPOSITDATE [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.SerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.OriginalPayorAccount [DRAWEE ACCOUNT]
FROM
dbo.tbXrefDepartments D
JOIN
dbo.tbHolds H
JOIN
dbo.tbItemPPSRIRpt I
ON
(H.ItemID = I.ItemId)
JOIN
dbo.tbAcctPPSRIRpt ACCT
ON
(I.AccountId = ACCT.AccountId)
ON
(D.DepartmentId = H.DepartmentID)
WHERE
(H.PlacementDate BETWEEN @STARTDATE and @ENDDATE) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID ='10') AND
((H.HoldTypeID='3') Or (H.HoldTypeID='5')) AND
(H.EarlyReleaseReasonID Is Null) AND
(H.PlacedViaHMS = 1)

GROUP BY
D.DepartmentName,
I.DEPOSITDate,
H.PlacementDate,
H.ReleaseDate,
ACCT.BankNumber,
I.ItemAmount,
H.HoldAmount,
I.SerialNumber ,
H.DepartmentID ,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.OriginalPayorAccount,
ACCT.AccountNumber

UNION ALL

SELECT
D.DepartmentName [DEPARTMENT NAME],
R.DepositDate [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.SerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.MakerAccountNumber [DRAWEE ACCOUNT]

FROM
dbo.tbAcctRegCC ACCT
JOIN
dbo.tbXrefDepartments D
JOIN
dbo.tbHolds H
JOIN
dbo.tbItemRegCC I
ON
(H.ItemID = I.ItemID)
ON
(D.DepartmentId = H.DepartmentID)
JOIN
dbo.tbDepositRegCC R
ON
(I.DepositID = R.DepositID)
ON
(ACCT.AccountID = R.AccountID)
WHERE
(H.PlacementDate BETWEEN @STARTDATE and @ENDDATE) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID='7') AND
((H.HoldTypeID='3') Or (H.HoldTypeID='5')) AND
(H.EarlyReleaseReasonID Is Null) AND
(H.PlacedViaHMS = 1)

GROUP BY
D.DepartmentName,
R.DepositDate,
H.PlacementDate,
H.ReleaseDate,
ACCT.BankNumber,
ACCT.AccountNumber,
I.ItemAmount,
H.HoldAmount,
I.SerialNumber,
H.DepartmentID,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.MakerAccountNumber,
ACCT.AccountNumber

UNION ALL

SELECT
D.DepartmentName [DEPARTMENT NAME],
ACCT.PostingDate [POSTING DATE],
H.PlacementDate [PLACEMENT DATE],
H.ReleaseDate [RELEASE DATE],
ACCT.BankNumber [BANK NUMBER],
ACCT.AccountNumber ACCT,
I.ItemAmount [ITEM AMOUNT],
H.HoldAmount [HOLD AMOUNT],
I.SerialNumber [SERIAL NUMBER],
H.DepartmentID [DEPARTMENTID],
H.HoldTypeID [HOLD TYPE],
H.EarlyReleaseReasonID [EARLY RELEASE REASON],
I.ItemAccountNumber [DRAWEE ACCOUT]
FROM
dbo.tbXrefDepartments D
JOIN
dbo.tbHolds H
JOIN
dbo.tbItemSAMSRpt I
ON
(H.ItemID = I.ItemId)
JOIN
dbo.tbAcctSAMSRpt ACCT
ON
(I.AccountId = ACCT.AccountId)
ON
(D.DepartmentId = H.DepartmentID)
WHERE
(H.PlacementDate BETWEEN @STARTDATE and @ENDDATE) AND
(H.HoldAmount Is Not Null) AND
(H.DepartmentID='11') AND
((H.HoldTypeID='3') Or (H.HoldTypeID ='5')) AND
(H.EarlyReleaseReasonID Is Null) AND
(H.PlacedViaHMS = 1)
GROUP BY
D.DepartmentName,
ACCT.PostingDate,
H.PlacementDate,
H.ReleaseDate,
ACCT.BankNumber,
I.ItemAmount,
H.HoldAmount,
I.SerialNumber,
H.DepartmentID,
H.HoldTypeID,
H.EarlyReleaseReasonID,
I.ItemAccountNumber,
ACCT.AccountNumber
) T

GROUP BY
-- CONVERT(INT, XD.DEPARTMENTID) DEPARTMENTID,
T.[BANK NUMBER],
LEFT(T.[SERIAL NUMBER], 8),
T.[DEPARTMENT NAME],
T.[POSTING DATE],
T.[PLACEMENT DATE],
T.[RELEASE DATE],
T.[BANK NUMBER],
T.[ACCT],
T.[ITEM AMOUNT],
T.[HOLD AMOUNT],
T.[SERIAL NUMBER],
T.[DEPARTMENTID],
T.[HOLD TYPE],
T.[EARLY RELEASE REASON],
T.[DRAWEE ACCOUNT]
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site