SQL for HST tax calculator -solved but needs help...

[ivo]

Hello.
I have gotten my HST tax cal calculator to work but it is not pretty. there must be a more straight forward and elegant way to
do this. So when you enter in your transactions you 'tag' the number column with a rev for revenue and a hst for purchases you paid HST
tax on and can claim. So when you have to remit the HST to the gorvernment just run the report and its all there.

Here is what I have:

select 'Hst to pay 2012','Hst to pay','Revenue+Hst','Hst collected','Qualifying expenses',"Hst paid on expenses"
union all
select 'Hst to pay Q1',(sum(amount))/113*13,
( select total (amount) total from alldata a where (transactionnumber = 'rev')and date between '2012-01-01' and '2012-03-31'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'rev')and date between '2012-01-01' and '2012-03-31'),
( select total (amount) total from alldata a where (transactionnumber = 'hst')and date between '2012-01-01' and '2012-03-31'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'hst')and date between '2012-01-01' and '2012-03-31')
--
from alldata
--
where transactionnumber in ('rev', 'hst')
and date between '2012-01-01' and '2012-03-31'
union all
select 'Hst to pay Q2',(sum(amount))/113*13,
( select total (amount) total from alldata a where (transactionnumber = 'rev')and date between '2012-04-01' and '2012-06-30'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'rev')and date between '2012-04-01' and '2012-06-30'),
( select total (amount) total from alldata a where (transactionnumber = 'hst')and date between '2012-04-01' and '2012-06-30'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'hst')and date between '2012-04-01' and '2012-06-30')
--
from alldata --alldata is the table name
--
where transactionnumber in ('rev', 'hst')
--
and date between '2012-04-01' and '2012-06-30'
union all
select 'Hst to pay Q3',(sum(amount))/113*13,
( select total (amount) total from alldata a where (transactionnumber = 'rev')and date between '2012-07-01' and '2012-09-30'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'rev')and date between '2012-07-01' and '2012-09-30'),
( select total (amount) total from alldata a where (transactionnumber = 'hst')and date between '2012-07-01' and '2012-09-30'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'hst')and date between '2012-07-01' and '2012-09-30')
--
from alldata --alldata is the table name
--
where transactionnumber in ('rev', 'hst')
--
and date between '2012-07-01' and '2012-09-30'
union all
select 'Hst to pay Q4',(sum(amount))/113*13,
( select total (amount) total from alldata a where (transactionnumber = 'rev')and date between '2012-10-01' and '2012-12-31'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'rev')and date between '2012-10-01' and '2012-12-31'),
( select total (amount) total from alldata a where (transactionnumber = 'hst')and date between '2012-10-01' and '2012-12-31'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'hst')and date between '2012-10-01' and '2012-12-31')
--
from alldata --alldata is the table name
--
where transactionnumber in ('rev', 'hst')
--
and date between '2012-10-01' and '2012-12-31'
--
--and now for the total
union all
select 'Total',(sum(amount))/113*13,
( select total (amount) total from alldata a where (transactionnumber = 'rev')and date between '2012-01-01' and '2012-12-31'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'rev')and date between '2012-01-01' and '2012-12-31'),
( select total (amount) total from alldata a where (transactionnumber = 'hst')and date between '2012-01-01' and '2012-12-31'),
( select total (amount)/113*13 total from alldata a where (transactionnumber = 'hst')and date between '2012-01-01' and '2012-12-31')
--
from alldata
--
where transactionnumber in ('rev', 'hst')
and date between '2012-01-01' and '2012-12-31'

Is there a way to set variables to represent the time intervals so that they do not have to by repeated over and over again.
Like Q1 = '2012-01-01' and '2012-03-31' so then I could just use : and date between Q1.
How do I stop the column headings from repeating on the second row ?
Could someone who knows what they are doing clean this up and post it back with some explanations?
I think I could learn a lot. There is no way this has to be so convoluted, This is what I call the brute force method. There must be a way
to do it with the CASE comand but I don't know how to use it .