March 12, 2009 at 10:58 am
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
March 12, 2009 at 11:12 am
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
March 12, 2009 at 11:31 am
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
March 12, 2009 at 11:36 am
Thanks, I will try this when I get time and let you know.
March 12, 2009 at 12:14 pm
Thanks a bunch. That worked like a gem.
March 12, 2009 at 12:19 pm
Happy it helps, thanks for your feedback!
Cheers,
J-F
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply