quote:

---

Originally posted by mxfrail

Below is the stored procedure -

On the last union query I have the order by [date], [transaction] but when it orders it is sorting by XX/XX and not XX/XX/XX. So basically randomally I will see this -

1/10/2008
1/11/2008
1/12/2008
1/13/2007
etc.

Can someone help me here?



CREATE PROCEDURE ASP_All

@ClientID int

AS

SELECT convert(varchar,tblInvoiceHdr.InvoiceDate,101) AS [Date],
tblInvoiceHdr.ClientID, cast([TotalDue] as dec(12,2)) AS Amount,
[Transaction] = case when tblInvoiceHdr.Invoicetype = 'ADJ' then 'REF#' + convert(varchar(10), tblInvoiceHdr.InvoiceNO) + ' - ' + 'Account Adjustment-' else
'REF#' + convert(varchar(10), tblInvoiceHdr.InvoiceNO) + ' - ' + 'Advertising Services Due: for Mailing: ' + [Description] end,
'INV' as Type, invoiceno
FROM tblInvoiceHdr
WHERE tblInvoiceHdr.Status<>'VOID' and clientid=@clientid

union all SELECT convert(varchar,tblPayments.DatePaid ,101) AS [Date],
tblPayments.ClientNo AS ClientID,
Abs([AmtPaid]) AS Amount,
[transaction] = case when tblpayments.PaymentType='NSF' then
'NSF Check - ck#: ' + isnull(tblpayments.checkno, 'Not Available') else 'Payment Refund'
end,
'NSF' as Type, '' as invoiceno
FROM tblPayments
WHERE Abs(tblpayments.AmtPaid)<> 0
AND tblPayments.PaymentType='NSF' Or
tblPayments.PaymentType='Refund' and clientno=@clientid

union all SELECT convert(varchar,tblPayments.DatePaid ,101) AS [Date], tblPayments.ClientNo AS ClientID,
Sum(-CAST([AmtPaid] AS dec(12,2))) AS Amount,
[Transaction]= case when tblpayments.paymenttype = 'Credit' then
'Account Credit-' + tblpayments.DisplayDescription else
'Payment Received-' + case when tblpayments.paymenttype = 'Check' then
'Check # ' + tblpayments.checkno else 'CC Payment'
end
end, 'PMT' as Type, '' as invoiceno
FROM tblPayments
WHERE (((tblPayments.PaymentType)<>'NSF' And (tblPayments.PaymentType)<>'Refund')) and clientno=@clientid
GROUP BY tblPayments.DatePaid, tblPayments.ClientNo, case when tblpayments.paymenttype = 'Credit' then
'Account Credit-' + tblpayments.DisplayDescription else
'Payment Received-' + case when tblpayments.paymenttype = 'Check' then
'Check # ' + tblpayments.checkno else 'CC Payment'
end
end, tblPayments.CheckNo
HAVING (((Sum(-CAST([AmtPaid] AS dec(12,2))))<>0))

union all SELECT convert(varchar,tblClients.DateEntered ,101) AS [Date], tblClients.ClientID,
cast([BeginningBalance] as dec(12,2)) AS Amount, 'Beginning Balance --' AS [Transaction], 'BB' as Type,
'' as invoiceno
FROM tblClients
WHERE clientid=@clientid
order by [Date] tblInvoiceHdr.InvoiceDate, [transaction]
GO

---

quote:

---

Originally posted by mxfrail

I tried that before and it does not work.

I get the following error -

Error 104: Order By items must appear in the select list if the statement contains a union operator.

I also tried sorting by each unique date field I converted. I tried order by within each select and that does not seem to be work as I get errors unless I just put one at the end.

---

CREATE PROCEDURE ASP_All 

@ClientID int

AS
SELECT [Date],Amount,[Transaction],Type,invoiceno FROM
(
SELECT tblInvoiceHdr.InvoiceDate AS [Original],convert(varchar,tblInvoiceHdr.InvoiceDate,101) AS [Date], 
tblInvoiceHdr.ClientID, cast([TotalDue] as dec(12,2)) AS Amount, 
[Transaction] = case when tblInvoiceHdr.Invoicetype = 'ADJ' then 'REF#' + convert(varchar(10), tblInvoiceHdr.InvoiceNO) + ' - ' + 'Account Adjustment-' else
'REF#' + convert(varchar(10), tblInvoiceHdr.InvoiceNO) + ' - ' + 'Advertising Services Due: for Mailing: ' + [Description] end, 
'INV' as Type, invoiceno
FROM tblInvoiceHdr
WHERE tblInvoiceHdr.Status<>'VOID' and clientid=@clientid

union all 
SELECT tblPayments.DatePaid,convert(varchar,tblPayments.DatePaid ,101) AS [Date],
tblPayments.ClientNo AS ClientID,
Abs([AmtPaid]) AS Amount,
[transaction] = case when tblpayments.PaymentType='NSF' then
'NSF Check - ck#: ' + isnull(tblpayments.checkno, 'Not Available') else 'Payment Refund'
end,
'NSF' as Type, '' as invoiceno
FROM tblPayments
WHERE Abs(tblpayments.AmtPaid)<> 0 
AND tblPayments.PaymentType='NSF' Or 
tblPayments.PaymentType='Refund' and clientno=@clientid

union all 
SELECT tblPayments.DatePaid,
convert(varchar,tblPayments.DatePaid ,101) AS [Date], tblPayments.ClientNo AS ClientID, 
Sum(-CAST([AmtPaid] AS dec(12,2))) AS Amount, 
[Transaction]= case when tblpayments.paymenttype = 'Credit' then 
'Account Credit-' + tblpayments.DisplayDescription else
'Payment Received-' + case when tblpayments.paymenttype = 'Check' then
'Check # ' + tblpayments.checkno else 'CC Payment' 
end 
end, 'PMT' as Type, '' as invoiceno
FROM tblPayments
WHERE (((tblPayments.PaymentType)<>'NSF' And (tblPayments.PaymentType)<>'Refund')) and clientno=@clientid
GROUP BY tblPayments.DatePaid, tblPayments.ClientNo, case when tblpayments.paymenttype = 'Credit' then 
'Account Credit-' + tblpayments.DisplayDescription else
'Payment Received-' + case when tblpayments.paymenttype = 'Check' then
'Check # ' + tblpayments.checkno else 'CC Payment' 
end 
end, tblPayments.CheckNo
HAVING (((Sum(-CAST([AmtPaid] AS dec(12,2))))<>0))

union all 
SELECT tblClients.DateEntered,convert(varchar,tblClients.DateEntered ,101) AS [Date], tblClients.ClientID, 
cast([BeginningBalance] as dec(12,2)) AS Amount, 'Beginning Balance --' AS [Transaction], 'BB' as Type,
'' as invoiceno
FROM tblClients
WHERE clientid=@clientid)r
order by r.[Original], r.[transaction]
GO