?
Solved

SQL 2005

Posted on 2011-05-12
17
Medium Priority
?
410 Views
Last Modified: 2012-05-11
Hello,
I've been using the attached script for some time and is working properly.
Except, when a client has more than one Invoice per period.
The script supposed to group the information in a certain way, please see atatched xls.
But, when a client has more than one Invoice, it duplicates the information and doesnt group them properly.

Any help is appreciated.
Thanks sample.xls
SQL-SCRIPT-i-USE.txt
0
Comment
Question by:W.E.B
  • 7
  • 7
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35745997
this article shall help to see where the problem comes from, and how to solve it:
http://www.experts-exchange.com/A_3203.html
0
 

Author Comment

by:W.E.B
ID: 35753315
Hello,
I read the article, tried to apply to what I have,
but no luck,.

Help is appreciated,
TX
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35762208
did you try to use "select distinct c.seq, d.*for result set?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:W.E.B
ID: 35763579
Hello,
the first thing I tried was the---   Select Distinct c.seq, d.*
Error,
Msg 145, Level 15, State 1, Line 11
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

thanks
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35764961
ok
1st
try to comment order by and check the dups rersults:
if it is what do you need
work on order by
as it says:
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"

--order by C.Accountnumber,Line,TransactionDate
0
 

Author Comment

by:W.E.B
ID: 35765504
Hello,
I used Disticnt,
commented the order by,
I don't get the duplicates,
I tried to play around with the order by, nothing seems to group them the way it is supposed to be (see attached),
Thanks
Resullts.xlsx
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35765949
As I understood - tha data is fine - it is just order by is problem:
--------------

check
SELECT DISTINCT and ORDER BY
http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

also if you have INNER JOIN

use
order by D.Accountnumber...

instead of
order by C.Accountnumber
---
--- Select Distinct TransactionDate from AROpenTransactions where TransactionDate >= '04-01-2011'
/*
2011-04-01 00:00:00.000
2011-04-08 00:00:00.000
2011-04-15 00:00:00.000
*/

---------------------------

With myCTE as
(
SELECT 
	BatchNumber AS [Batch#],
	(SELECT Description FROM dbo.CurrencyTypes WHERE CurrencyTypeID = AR.TransactionCurrencyTypeID) AS [Currency],
	AR.AccountNumber AS [AccountNumber],
	CAST(C1.Name AS CHAR(35)) AS [Client],
	---C1.Name AS [Client],
	C1.Street AS [Street],
	C1.Unit AS [Unit],
	C1.City AS [City],
	C1.Province AS [Province],
	C1.PostalCode AS [PostalCode],
	C1.Phone AS [Phone],
	C1.Fax AS [Fax],
	(SELECT Top 1 ClientContacts.Email FROM ClientContacts WHERE ClientContacts.AccountNumber = C1.AccountNumber AND ClientContacts.ContactTypeID = 2) AS [Email],
	C1.AccountCode AS [AccountCode],
	TransactionNumber AS [TransactionNumber],
	CONVERT(varchar(10), TransactionDate, 101) AS [TransactionDate],
	PostDate AS [PostDate],
	SalesAmount AS [SalesAmount],
	TaxAmount AS [TaxAmount],
	dbo.sfReportTransactionTaxesList(AR.AccountNumber,1,AR.TransactionNumber) AS [TaxesDetail],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'GST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [GST on Sales (NEW)],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'GST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [GSTAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST_ON' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_ON on Sales],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST_ON' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_ONAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST_NS' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_NS on Sales],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST_NS' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_NSAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST_BC' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_BC on Sales],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST_BC' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_BCAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST on Sales (NEW)],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HSTAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'TVQ' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [QST on Sales (NEW)],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'TVQ' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [TVQAmount],
ARAmount AS [ARAmount],
ISNULL((SELECT COUNT(*) FROM dbo.FinalizedOrdersInvoices 
WHERE
		InvoiceNumber = AR.TransactionNumber),0)
		AS [No Orders]
FROM dbo.AROpenTransactions AR JOIN dbo.Clients C1 ON AR.AccountNumber = C1.AccountNumber
WHERE
	TransactionType = 1 AND
	ISNULL(AR.Cancelled,0) = 0 AND
	C1.AccountNumber Not in (1,2,3,4,5) AND
	TransactionDate >= '2011-04-15' ---Between '09-16-2010' and '09-30-2010' ----- 
	
)

---EZ
SELECT 
Select c.seq, d.*
from
(
Select AccountNumber, AccountCode, 1 as line, 'TRNS' code , 'Invoice' Type,  TransactionDate , 'Accounts Receivable' title ,Client,City,ARAmount,TransactionNumber
From myCTE 
union all 
select AccountNumber,AccountCode, 2, 'SPL' code, 'Invoice', TransactionDate, 'Courier Services' ,'',City,(Select convert (varchar (16),cast(SalesAmount * (-1) as money),1)) as [Salesamount],NULL
From myCTE 
union all 
select AccountNumber,AccountCode, 3, 'SPL', 'Invoice', TransactionDate, 'GST on Sales (NEW)','',City,convert(varchar(16),ISNULL(GSTAmount,0)*(-1),1) as [GST],NULL
From myCTE
union all
select AccountNumber,AccountCode, 4, 'SPL', 'Invoice', TransactionDate, 'QST on Sales (NEW)','',City,convert(varchar(16),ISNULL(TVQAmount,0)*(-1),1) as [TVQ],NULL
from myCTE 
union all
select AccountNumber,AccountCode, 5, 'SPL', 'Invoice', TransactionDate, 'HST on Sales (NEW)','',City,convert(varchar(16),ISNULL(HSTAmount,0)*(-1),1) as [HST],NULL
from myCTE 
union all
select AccountNumber,AccountCode, 6, 'SPL', 'Invoice', TransactionDate, 'HST_ON on Sales','',City,convert(varchar(16),ISNULL(HST_ONAmount,0)*(-1),1) as [HST_ON],NULL
from myCTE 
union all
select AccountNumber,AccountCode, 7, 'SPL', 'Invoice', TransactionDate, 'HST_NS on Sales','',City,convert(varchar(16),ISNULL(HST_NSAmount,0)*(-1),1) as [HST_NS],NULL
from myCTE 
union all
select AccountNumber,AccountCode, 8, 'SPL', 'Invoice', TransactionDate, 'HST_BC on Sales','',City,convert(varchar(16),ISNULL(HST_BCAmount,0)*(-1),1) as [HST_BC],NULL
from myCTE 
union all
select AccountNumber,'', 9, 'ENDTRNS', '',NULL,'','','',NULL,NULL
From myCTE 
) as d
Inner join
(Select c2.AccountNumber,  (Select count(*) as seq from myCTE where myCTE.AccountNumber <= c2.AccountNumber) as Seq from myCTE as c2) AS C
on c.accountnumber = d.accountnumber
order by Accountnumber,Line,TransactionDate

Open in new window

0
 

Author Comment

by:W.E.B
ID: 35769116
Hello,
Thanks for the help,
Correct, no duplicates.
but, no matter how I order by, I'm not able to get the proper results.

i'm not sure if any grouping is needed before order by, or may be a case if client has more than one Invoice,...

Thanks,
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35773375
try this :



---EZ
Select *
FROM
(
SELECT  DISTINCT
c.seq, d.*
from
(
Select AccountNumber, AccountCode, 1 as line, 'TRNS' code , 'Invoice' Type,  TransactionDate , 'Accounts Receivable' title ,Client,City,ARAmount,TransactionNumber
From myCTE
union all
select AccountNumber,AccountCode, 2, 'SPL' code, 'Invoice', TransactionDate, 'Courier Services' ,'',City,(Select convert (varchar (16),cast(SalesAmount * (-1) as money),1)) as [Salesamount],NULL
From myCTE
union all
select AccountNumber,AccountCode, 3, 'SPL', 'Invoice', TransactionDate, 'GST on Sales (NEW)','',City,convert(varchar(16),ISNULL(GSTAmount,0)*(-1),1) as [GST],NULL
From myCTE
union all
select AccountNumber,AccountCode, 4, 'SPL', 'Invoice', TransactionDate, 'QST on Sales (NEW)','',City,convert(varchar(16),ISNULL(TVQAmount,0)*(-1),1) as [TVQ],NULL
from myCTE
union all
select AccountNumber,AccountCode, 5, 'SPL', 'Invoice', TransactionDate, 'HST on Sales (NEW)','',City,convert(varchar(16),ISNULL(HSTAmount,0)*(-1),1) as [HST],NULL
from myCTE
union all
select AccountNumber,AccountCode, 6, 'SPL', 'Invoice', TransactionDate, 'HST_ON on Sales','',City,convert(varchar(16),ISNULL(HST_ONAmount,0)*(-1),1) as [HST_ON],NULL
from myCTE
union all
select AccountNumber,AccountCode, 7, 'SPL', 'Invoice', TransactionDate, 'HST_NS on Sales','',City,convert(varchar(16),ISNULL(HST_NSAmount,0)*(-1),1) as [HST_NS],NULL
from myCTE
union all
select AccountNumber,AccountCode, 8, 'SPL', 'Invoice', TransactionDate, 'HST_BC on Sales','',City,convert(varchar(16),ISNULL(HST_BCAmount,0)*(-1),1) as [HST_BC],NULL
from myCTE
union all
select AccountNumber,'', 9, 'ENDTRNS', '',NULL,'','','',NULL,NULL
From myCTE
) as d
Inner join
(Select c2.AccountNumber,  (Select count(*) as seq from myCTE where myCTE.AccountNumber <= c2.AccountNumber) as Seq from myCTE as c2) AS C
on c.accountnumber = d.accountnumber) EZ
order by Accountnumber,Line,TransactionDate

 

0
 

Author Comment

by:W.E.B
ID: 35774208
Hello,
same Results,
no duplicates, but the order by is still off,
thanks
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 375 total points
ID: 35775944
hmm..
is your CTE select has dups?

if yes - try select distinct there ..

SELECT distinct
      BatchNumber AS [Batch#],
      (SELECT Description FROM dbo.CurrencyTypes WHERE CurrencyTypeID = AR.TransactionCurrencyTypeID) AS [Currency],
      AR.AccountNumber AS [AccountNumber],
      CAST(C1.Name AS CHAR(35)) AS [Client],
      ---C1.Name AS [Client],
      C1.Street AS [Street],
      C1.Unit AS [Unit],
      C1.City AS [City],
      C1.Province AS [Province],
      C1.PostalCode AS [PostalCode],
      C1.Phone AS [Phone],
      C1.Fax AS [Fax],
      (SELECT Top 1 ClientContacts.Email FROM ClientContacts WHERE ClientContacts.AccountNumber = C1.AccountNumber AND ClientContacts.ContactTypeID = 2) AS [Email],
      C1.AccountCode AS [AccountCode],
      TransactionNumber AS [TransactionNumber],
      CONVERT(varchar(10), TransactionDate, 101) AS [TransactionDate],
      PostDate AS [PostDate],
      SalesAmount AS [SalesAmount],
      TaxAmount AS [TaxAmount],
      dbo.sfReportTransactionTaxesList(AR.AccountNumber,1,AR.TransactionNumber) AS [TaxesDetail],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'GST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [GST on Sales (NEW)],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'GST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [GSTAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST_ON' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_ON on Sales],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST_ON' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_ONAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST_NS' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_NS on Sales],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST_NS' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_NSAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST_BC' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_BC on Sales],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST_BC' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST_BCAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'HST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HST on Sales (NEW)],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'HST' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [HSTAmount],
(Select Distinct TaxName from TransactionTaxes where TaxName Like 'TVQ' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [QST on Sales (NEW)],
(Select Sum (TaxAmount) from TransactionTaxes where TaxName Like 'TVQ' AND TransactionTaxes.TransactionNumber = AR.TransactionNumber) AS [TVQAmount],
ARAmount AS [ARAmount],
ISNULL((SELECT COUNT(*) FROM dbo.FinalizedOrdersInvoices
WHERE
            InvoiceNumber = AR.TransactionNumber),0)
            AS [No Orders]
FROM dbo.AROpenTransactions AR JOIN dbo.Clients C1 ON AR.AccountNumber = C1.AccountNumber
WHERE
      TransactionType = 1 AND
      ISNULL(AR.Cancelled,0) = 0 AND
      C1.AccountNumber Not in (1,2,3,4,5) AND
      TransactionDate >= '2011-04-15' ---Between '09-16-2010' and '09-30-2010' -----
      
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35775953
if not - try group by your final result set : the one that is producing right order by has dups


Select c.seq, d.*
from
(
Select AccountNumber, AccountCode, 1 as line, 'TRNS' code , 'Invoice' Type,  TransactionDate , 'Accounts Receivable' title ,Client,City,ARAmount,TransactionNumber
From myCTE
union all
select AccountNumber,AccountCode, 2, 'SPL' code, 'Invoice', TransactionDate, 'Courier Services' ,'',City,(Select convert (varchar (16),cast(SalesAmount * (-1) as money),1)) as [Salesamount],NULL
From myCTE
union all
select AccountNumber,AccountCode, 3, 'SPL', 'Invoice', TransactionDate, 'GST on Sales (NEW)','',City,convert(varchar(16),ISNULL(GSTAmount,0)*(-1),1) as [GST],NULL
From myCTE
union all
select AccountNumber,AccountCode, 4, 'SPL', 'Invoice', TransactionDate, 'QST on Sales (NEW)','',City,convert(varchar(16),ISNULL(TVQAmount,0)*(-1),1) as [TVQ],NULL
from myCTE
union all
select AccountNumber,AccountCode, 5, 'SPL', 'Invoice', TransactionDate, 'HST on Sales (NEW)','',City,convert(varchar(16),ISNULL(HSTAmount,0)*(-1),1) as [HST],NULL
from myCTE
union all
select AccountNumber,AccountCode, 6, 'SPL', 'Invoice', TransactionDate, 'HST_ON on Sales','',City,convert(varchar(16),ISNULL(HST_ONAmount,0)*(-1),1) as [HST_ON],NULL
from myCTE
union all
select AccountNumber,AccountCode, 7, 'SPL', 'Invoice', TransactionDate, 'HST_NS on Sales','',City,convert(varchar(16),ISNULL(HST_NSAmount,0)*(-1),1) as [HST_NS],NULL
from myCTE
union all
select AccountNumber,AccountCode, 8, 'SPL', 'Invoice', TransactionDate, 'HST_BC on Sales','',City,convert(varchar(16),ISNULL(HST_BCAmount,0)*(-1),1) as [HST_BC],NULL
from myCTE
union all
select AccountNumber,'', 9, 'ENDTRNS', '',NULL,'','','',NULL,NULL
From myCTE
) as d
Inner join
(Select c2.AccountNumber,  (Select count(*) as seq from myCTE where myCTE.AccountNumber <= c2.AccountNumber) as Seq from myCTE as c2) AS C
on c.accountnumber = d.accountnumber
order by Accountnumber,Line,TransactionDate
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35775970
also try UNION instead of union all

and see if your dups source is 2nd inner join part

see results with and witour group by

Select c2.AccountNumber,  (Select count(*) as seq from myCTE where myCTE.AccountNumber <= c2.AccountNumber) as Seq from myCTE as c2
group by c2.AccountNumber,Seq


--------------


SELECT
Select c.seq, d.*
from
(
Select AccountNumber, AccountCode, 1 as line, 'TRNS' code , 'Invoice' Type,  TransactionDate , 'Accounts Receivable' title ,Client,City,ARAmount,TransactionNumber
From myCTE
union
select AccountNumber,AccountCode, 2, 'SPL' code, 'Invoice', TransactionDate, 'Courier Services' ,'',City,(Select convert (varchar (16),cast(SalesAmount * (-1) as money),1)) as [Salesamount],NULL
From myCTE
union  
select AccountNumber,AccountCode, 3, 'SPL', 'Invoice', TransactionDate, 'GST on Sales (NEW)','',City,convert(varchar(16),ISNULL(GSTAmount,0)*(-1),1) as [GST],NULL
From myCTE
union a
select AccountNumber,AccountCode, 4, 'SPL', 'Invoice', TransactionDate, 'QST on Sales (NEW)','',City,convert(varchar(16),ISNULL(TVQAmount,0)*(-1),1) as [TVQ],NULL
from myCTE
union
select AccountNumber,AccountCode, 5, 'SPL', 'Invoice', TransactionDate, 'HST on Sales (NEW)','',City,convert(varchar(16),ISNULL(HSTAmount,0)*(-1),1) as [HST],NULL
from myCTE
union
select AccountNumber,AccountCode, 6, 'SPL', 'Invoice', TransactionDate, 'HST_ON on Sales','',City,convert(varchar(16),ISNULL(HST_ONAmount,0)*(-1),1) as [HST_ON],NULL
from myCTE
union
select AccountNumber,AccountCode, 7, 'SPL', 'Invoice', TransactionDate, 'HST_NS on Sales','',City,convert(varchar(16),ISNULL(HST_NSAmount,0)*(-1),1) as [HST_NS],NULL
from myCTE
union
select AccountNumber,AccountCode, 8, 'SPL', 'Invoice', TransactionDate, 'HST_BC on Sales','',City,convert(varchar(16),ISNULL(HST_BCAmount,0)*(-1),1) as [HST_BC],NULL
from myCTE
union
select AccountNumber,'', 9, 'ENDTRNS', '',NULL,'','','',NULL,NULL
From myCTE
) as d
Inner join
(Select c2.AccountNumber,  (Select count(*) as seq from myCTE where myCTE.AccountNumber <= c2.AccountNumber) as Seq from myCTE as c2
group by c2.AccountNumber,Seq
) AS C
on c.accountnumber = d.accountnumber
order by Accountnumber,Line,TransactionDate
0
 

Author Comment

by:W.E.B
ID: 35777132
Hello,
Sorry, but still , same results, no duplicates, but order by is off,

I tried to go around this,
what if we added a column,TransactionNumber for each line, and then
order by TransactionNumber ,d.Accountnumber,Line,TransactionDate

I think this will work,because each TransactionNumber is unique, and then I will delete the column after I export.

I tried to do it my self, but, not sure why I keep getting error,
Msg 8156, Level 16, State 1, Line 1
The column 'TransactionNumber' was specified multiple times for 'd'.

So basically, I added TransactionNumber.

Select Distinct c.seq, d.*
from
(
Select AccountNumber,TransactionNumber, AccountCode, 1 as line, 'TRNS' code , 'Invoice' Type,  TransactionDate , 'Accounts Receivable' title ,Client,City,ARAmount,TransactionNumber
From myCTE
union all
select AccountNumber,TransactionNumber,AccountCode, 2, 'SPL' code, 'Invoice', TransactionDate, 'Courier Services' ,'',City,(Select convert (varchar (16),cast(SalesAmount * (-1) as money),1)) as [Salesamount],NULL
From myCTE
union all
select AccountNumber,TransactionNumber,AccountCode, 3, 'SPL', 'Invoice', TransactionDate, 'GST on Sales (NEW)','',City,convert(varchar(16),ISNULL(GSTAmount,0)*(-1),1) as [GST],NULL
From myCTE
union all
select AccountNumber,TransactionNumber,AccountCode, 4, 'SPL', 'Invoice', TransactionDate, 'QST on Sales (NEW)','',City,convert(varchar(16),ISNULL(TVQAmount,0)*(-1),1) as [TVQ],NULL
from myCTE
union all
select AccountNumber,TransactionNumber,AccountCode, 5, 'SPL', 'Invoice', TransactionDate, 'HST on Sales (NEW)','',City,convert(varchar(16),ISNULL(HSTAmount,0)*(-1),1) as [HST],NULL
from myCTE
union all
select AccountNumber,TransactionNumber,AccountCode, 6, 'SPL', 'Invoice', TransactionDate, 'HST_ON on Sales','',City,convert(varchar(16),ISNULL(HST_ONAmount,0)*(-1),1) as [HST_ON],NULL
from myCTE
union all
select AccountNumber,TransactionNumber,AccountCode, 7, 'SPL', 'Invoice', TransactionDate, 'HST_NS on Sales','',City,convert(varchar(16),ISNULL(HST_NSAmount,0)*(-1),1) as [HST_NS],NULL
from myCTE
union all
select AccountNumber,AccountCode, 8, 'SPL', 'Invoice', TransactionDate, 'HST_BC on Sales','',City,convert(varchar(16),ISNULL(HST_BCAmount,0)*(-1),1) as [HST_BC],NULL
from myCTE
union all
select AccountNumber,TransactionNumber,'', 9, 'ENDTRNS', '',NULL,'','','',NULL,NULL
From myCTE
) as d
Inner join
(Select c2.AccountNumber,  (Select count(*) as seq from myCTE where myCTE.AccountNumber <= c2.AccountNumber) as Seq from myCTE as c2) AS C
on c.accountnumber = d.accountnumber
Order By TransactionNumber,D.accountnumber,Line,TransactionDate

Thanks,
0
 

Author Closing Comment

by:W.E.B
ID: 35785065
Hello,
I ended up adding a nothe column that has the unique TransactioNumber, the order by TransactioNumber,seq,Line,.....

Thank you.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question