Union all with order by not working

Question

Post reply

Union all with order by not working

tschuler-738392

Ten Centuries

Points: 1172
More actions
March 12, 2009 at 10:58 am

#206877

I am exporting data with the following query and want to order by the end date but since I am using a Union all to join the Header and data it will not allow me to order by. If anyone has any idea on how I could get this to order and have a header that would be great. Below is the query.
select '0' + dbo.date_format(getdate(), 'MMDDYY')
union all
select
'1' +
CONVERT(varchar, dbo.date_format(mt.end_date, 'MMDDYY')) +
left(right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7) + ' ', 15) +
case
when (right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7))
= mm.mmatter
then left(mm.mbillaty+ ' ', 5)
else left(mt.employee_id + ' ', 5)
end
+
'608' +
space(4) +
left(convert(varchar, mt.meter_end - mt.meter_start - mt.meter_wasted)+' ', 7) +
left(isnull(convert(varchar,convert(Money,mt.unit_price)), '0.00')+' ', 7) +
left(CONVERT(VARCHAR,CONVERT(MONEY,mt.total_price))+' ', 12) +
+
case
When mt.office_id = '1' Then left('101'+' ', 4)
When mt.office_id = '2' Then left('102'+' ', 4)
When mt.office_id = '3' Then left('120'+' ', 4)
When mt.office_id = '4' Then left('150'+' ', 4)
When mt.office_id = '6' Then left('145'+' ', 4)
When mt.office_id = '7' Then left('180'+' ', 4)
When mt.office_id = '10' Then left('135'+' ', 4)
else left(mt.office_id+' ', 4)
end
+char(13)+char(10)+
'4'+
case
when mt.disbursement_code = '612' then 'Postage'
when mt.disbursement_code = 'EM01' then 'E-Mail'
when mt.disbursement_code = 'Stamps' then 'Stamps'
else mt.disbursement_code
end
from dbo.master_trans as mt
join MatterManager as mm
on (right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7))
= mm.mmatter
where mt.job_type_id = 3
and mt.date_export is null
and mt.export_id is null
AND mt.end_date < DBO.DATE_FORMAT(GETDATE(), 'YYYY-MM-DD 00:00:00')
order by mt.end_date asc

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Try this:

SELECT *

FROM (

select '0' + dbo.date_format(getdate(), 'MMDDYY') AS End_Date

union all

select

'1' +

CONVERT(varchar, dbo.date_format(mt.end_date, 'MMDDYY')) +

left(right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7) + ' ', 15) +

case

when (right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7))

= mm.mmatter

then left(mm.mbillaty+ ' ', 5)

else left(mt.employee_id + ' ', 5)

end

+

'608' +

space(4) +

left(convert(varchar, mt.meter_end - mt.meter_start - mt.meter_wasted)+' ', 7) +

left(isnull(convert(varchar,convert(Money,mt.unit_price)), '0.00')+' ', 7) +

left(CONVERT(VARCHAR,CONVERT(MONEY,mt.total_price))+' ', 12) +

+

case

When mt.office_id = '1' Then left('101'+' ', 4)

When mt.office_id = '2' Then left('102'+' ', 4)

When mt.office_id = '3' Then left('120'+' ', 4)

When mt.office_id = '4' Then left('150'+' ', 4)

When mt.office_id = '6' Then left('145'+' ', 4)

When mt.office_id = '7' Then left('180'+' ', 4)

When mt.office_id = '10' Then left('135'+' ', 4)

else left(mt.office_id+' ', 4)

end

+char(13)+char(10)+

'4'+

case

when mt.disbursement_code = '612' then 'Postage'

when mt.disbursement_code = 'EM01' then 'E-Mail'

when mt.disbursement_code = 'Stamps' then 'Stamps'

else mt.disbursement_code

end

from dbo.master_trans as mt

join MatterManager as mm

on (right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7))

= mm.mmatter

where mt.job_type_id = 3

and mt.date_export is null

and mt.export_id is null

AND mt.end_date < DBO.DATE_FORMAT(GETDATE(), 'YYYY-MM-DD 00:00:00')

) AS qry

order by end_date asc

Regards

Gianluca

-- Gianluca Sartori

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Gianluca Sartori (3/12/2009)
Try this:
SELECT *
FROM (
select '0' + dbo.date_format(getdate(), 'MMDDYY') AS End_Date
union all
select
'1' +
CONVERT(varchar, dbo.date_format(mt.end_date, 'MMDDYY')) +
left(right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7) + ' ', 15) +
case
when (right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7))
= mm.mmatter
then left(mm.mbillaty+ ' ', 5)
else left(mt.employee_id + ' ', 5)
end
+
'608' +
space(4) +
left(convert(varchar, mt.meter_end - mt.meter_start - mt.meter_wasted)+' ', 7) +
left(isnull(convert(varchar,convert(Money,mt.unit_price)), '0.00')+' ', 7) +
left(CONVERT(VARCHAR,CONVERT(MONEY,mt.total_price))+' ', 12) +
+
case
When mt.office_id = '1' Then left('101'+' ', 4)
When mt.office_id = '2' Then left('102'+' ', 4)
When mt.office_id = '3' Then left('120'+' ', 4)
When mt.office_id = '4' Then left('150'+' ', 4)
When mt.office_id = '6' Then left('145'+' ', 4)
When mt.office_id = '7' Then left('180'+' ', 4)
When mt.office_id = '10' Then left('135'+' ', 4)
else left(mt.office_id+' ', 4)
end
+char(13)+char(10)+
'4'+
case
when mt.disbursement_code = '612' then 'Postage'
when mt.disbursement_code = 'EM01' then 'E-Mail'
when mt.disbursement_code = 'Stamps' then 'Stamps'
else mt.disbursement_code
end
from dbo.master_trans as mt
join MatterManager as mm
on (right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7))
= mm.mmatter
where mt.job_type_id = 3
and mt.date_export is null
and mt.export_id is null
AND mt.end_date < DBO.DATE_FORMAT(GETDATE(), 'YYYY-MM-DD 00:00:00')
) AS qry
order by end_date asc
Regards
Gianluca

Using this will change the OP's output. Here's a sample of what the OP needs to do:

SELECT End_Date

FROM ( SELECT 0 AS [ORD],

dbo.date_format(GETDATE(), 'MMDDYY') AS End_Date

UNION ALL

SELECT 1 AS [ORD],

CONVERT(VARCHAR, dbo.date_format(mt.end_date, 'MMDDYY'))

+ LEFT(RIGHT('0000000' + mt.client_num, 7) + '.'

+ RIGHT('0000000' + mt.matter_num, 7)

+ ' ', 15)

+ CASE WHEN ( RIGHT('0000000' + mt.client_num, 7) + '.'

+ RIGHT('0000000' + mt.matter_num, 7) ) = mm.mmatter

THEN LEFT(mm.mbillaty + ' ', 5)

ELSE LEFT(mt.employee_id + ' ', 5)

END + '608' + SPACE(4)

+ LEFT(CONVERT(VARCHAR, mt.meter_end - mt.meter_start

- mt.meter_wasted) + ' ', 7)

+ LEFT(ISNULL(CONVERT(VARCHAR, CONVERT(MONEY, mt.unit_price)),

'0.00') + ' ', 7)

+ LEFT(CONVERT(VARCHAR, CONVERT(MONEY, mt.total_price))

+ ' ', 12)

+ +CASE WHEN mt.office_id = '1'

THEN LEFT('101' + ' ', 4)

WHEN mt.office_id = '2'

THEN LEFT('102' + ' ', 4)

WHEN mt.office_id = '3'

THEN LEFT('120' + ' ', 4)

WHEN mt.office_id = '4'

THEN LEFT('150' + ' ', 4)

WHEN mt.office_id = '6'

THEN LEFT('145' + ' ', 4)

WHEN mt.office_id = '7'

THEN LEFT('180' + ' ', 4)

WHEN mt.office_id = '10'

THEN LEFT('135' + ' ', 4)

ELSE LEFT(mt.office_id + ' ', 4)

END + CHAR(13) + CHAR(10) + '4'

+ CASE WHEN mt.disbursement_code = '612' THEN 'Postage'

WHEN mt.disbursement_code = 'EM01' THEN 'E-Mail'

WHEN mt.disbursement_code = 'Stamps' THEN 'Stamps'

ELSE mt.disbursement_code

END

FROM dbo.master_trans AS mt

JOIN MatterManager AS mm ON ( RIGHT('0000000'

+ mt.client_num, 7)

+ '.' + RIGHT('0000000' + mt.matter_num, 7) ) = mm.mmatter

WHERE mt.job_type_id = 3

AND mt.date_export IS NULL

AND mt.export_id IS NULL

AND mt.end_date < DBO.DATE_FORMAT(GETDATE(),

'YYYY-MM-DD 00:00:00')

) AS qry

ORDER BY ORD ASC, end_date ASC

That way, he does not have 1 or 0 appended to his output strings (probably for a file).

Hope this helps,

Cheers,

J-F

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 3

Thanks, I will try this when I get time and let you know.

tschuler-738392 Ten Centuries Points: 1172 More actions · Answer 4

tschuler-738392

Ten Centuries

Points: 1172

March 12, 2009 at 12:14 pm

#958585

Thanks a bunch. That worked like a gem.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 5

J-F Bergeron

SSCrazy Eights

Points: 8593

March 12, 2009 at 12:19 pm

#958593

Happy it helps, thanks for your feedback!

Cheers,

J-F