SQL Server Programming - restructure on query

Author

restructure on query

Peter Newman

2005-10-28, 7:03 pm

Im having trouble witht he following query

SELECT a.BHYear_OrigSortCode, RIGHT('00000000' + a.BHYear_OrigAccount,8)
as 'value' ,
Case c.BTYear_TransCode
WHEN '99' then '17'
WHEN '01' Then '99'
WHEN '04' THEN '99'
WHEN '17' THEN '99'
WHEN '18' THEN '99'
WHEN '19' THEN '99'
Else 'AUD' END as 'TRANTYPE',
RIGHT( '00000000000' + CAST(SUM(CAST(c.BTYear_TransValue as decimal(11,0)))
AS VARCHAR(11)), 11),
LEFT( b.tblOA_AccountName+ Space(18) , 18),
convert(char(10), a.BHYear_MoveDate, 103),
a.BHYear_OrigAccRef
FROM dbo.BacsHdrYearly a
LEFT JOIN dbo.OriginatingAccounts b
ON a.BHYear_Licence = b.tblOA_Licence
LEFT JOIN dbo.BacsTrnYear c
ON a.BHYear_LedgerKey = c.BTYear_LedgerKey
WHERE (a.BHYear_OrigAccRef = b.tblOA_AccountID AND a.BHYear_FileStatus =
'Unsubmitted'
AND a.BHYear_SubmissionType = 'Live'AND DATEDIFF(d, a.BHYear_RecievedDate,
GETDATE()) = 0
AND a.BHYear_Licence = '217505' AND a.BHYear_SerialNumber = '227')
GROUP BY c.BTYear_TransCode, a.BHYear_OrigSortCode, a.BHYear_OrigAccount,
b.tblOA_AccountName , a.BHYear_MoveDate, a.BHYear_OrigAccRe

in this instance it returns the following

BHYear_OrigSortCode value TRANTYPE BHYear_OrigAccRef
------------------- -------- -------- ----------- -------- -----------------
985040 20006361 99 00000012050 Company 01/11/2005 003
985040 20006361 99 00000020950 Conpany 01/11/2005 003
but i need it to show

BHYear_OrigSortCode value TRANTYPE BHYear_OrigAccRef
------------------- -------- -------- ----------- -------- -----------------
985040 20006361 99 00000032900 Company 01/11/2005 003

Anith Sen

2005-10-28, 7:03 pm

Please go through www.aspfaq.com/5006 and post relevant information for
others to better understand your requirements.

--
Anith

Hugo Kornelis

2005-10-28, 7:03 pm

On Fri, 28 Oct 2005 08:02:05 -0700, Peter Newman wrote:

>Im having trouble witht he following query
>
>SELECT a.BHYear_OrigSortCode, RIGHT('00000000' + a.BHYear_OrigAccount,8)
>as 'value' ,
>Case c.BTYear_TransCode
>WHEN '99' then '17'
>WHEN '01' Then '99'
>WHEN '04' THEN '99'
>WHEN '17' THEN '99'
>WHEN '18' THEN '99'
>WHEN '19' THEN '99'
>Else 'AUD' END as 'TRANTYPE',
>RIGHT( '00000000000' + CAST(SUM(CAST(c.BTYear_TransValue as decimal(11,0)))
>AS VARCHAR(11)), 11),
>LEFT( b.tblOA_AccountName+ Space(18) , 18),
>convert(char(10), a.BHYear_MoveDate, 103),
>a.BHYear_OrigAccRef
>FROM dbo.BacsHdrYearly a
>LEFT JOIN dbo.OriginatingAccounts b
>ON a.BHYear_Licence = b.tblOA_Licence
>LEFT JOIN dbo.BacsTrnYear c
>ON a.BHYear_LedgerKey = c.BTYear_LedgerKey
>WHERE (a.BHYear_OrigAccRef = b.tblOA_AccountID AND a.BHYear_FileStatus =
>'Unsubmitted'
>AND a.BHYear_SubmissionType = 'Live'AND DATEDIFF(d, a.BHYear_RecievedDate,
>GETDATE()) = 0
>AND a.BHYear_Licence = '217505' AND a.BHYear_SerialNumber = '227')
>GROUP BY c.BTYear_TransCode, a.BHYear_OrigSortCode, a.BHYear_OrigAccount,
>b.tblOA_AccountName , a.BHYear_MoveDate, a.BHYear_OrigAccRe
>
> in this instance it returns the following
>
>BHYear_OrigSortCode value TRANTYPE BHYear_OrigAccRef
>------------------- -------- -------- ----------- -------- -----------------
>985040 20006361 99 00000012050 Company 01/11/2005 003
>985040 20006361 99 00000020950 Conpany 01/11/2005 003
> but i need it to show
>
>BHYear_OrigSortCode value TRANTYPE BHYear_OrigAccRef
>------------------- -------- -------- ----------- -------- -----------------
>985040 20006361 99 00000032900 Company 01/11/2005 003

Hi Peter,

Change your GROUP BY clause to

GROUP BY CASE c.BTYear_TransCode
WHEN '99' then '17'
WHEN '01' Then '99'
WHEN '04' THEN '99'
WHEN '17' THEN '99'
WHEN '18' THEN '99'
WHEN '19' THEN '99'
ELSE 'AUD'
END,
a.BHYear_OrigSortCode, a.BHYear_OrigAccount,
b.tblOA_AccountName, a.BHYear_MoveDate, a.BHYear_OrigAccRef

Or, since there are no aggregates in the query, completely remove the
GROUP BY clause and add a DISTINCT to the SELECT clause (if that's
needed at all!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links