Hi,
I am trying to do a query whereby i set a variable to be a value and then use that value in the following query. The issue is that it only runs one statement so I need to assignment and use in the same block. Any ideas.
Set @todate := '2009-12-21';
Set @fromdate := '2009-12-18';
select fromdate as 'Arrival', pa.`text` as 'Key No.', p.propname as 'Property Name', p.areacode as 'Area',
p.cleanercode as 'Cleaner ref.', concat(c.title, ' ', c.inits, ' ', c.surname) as 'Cleaner Name',
concat(cu.title, ' ', cu.inits, ' ', cu.surname) as 'Customer Name', b.bookref as 'Bookref', 'Out' as 'Status', b.notes from bookings b
inner join prop_attributes pa on pa.propref = b.propref
inner join property p on p.propref = b.propref
inner join cleaner c on c.cleanercode = p.cleanercode
inner join customer cu on cu.cusref = b.cusref
where (b.fromdate between @fromdate and @todate) and pa.attrcode = 'attr50' and b.bstatus not in ('AC', 'AT', 'A')
UNION
select todate as 'Arrival', pa.`text` as 'Key No.', p.propname as 'Property Name', p.areacode as 'Area',
p.cleanercode as 'Cleaner ref.', concat(c.title, ' ', c.inits, ' ', c.surname) as 'Cleaner Name',
concat(cu.title, ' ', cu.inits, ' ', cu.surname) as 'Customer Name', b.bookref as 'Bookref', 'In' as 'Status', b.notes from bookings b
inner join prop_attributes pa on pa.propref = b.propref
inner join property p on p.propref = b.propref
inner join cleaner c on c.cleanercode = p.cleanercode
inner join customer cu on cu.cusref = b.cusref
where (b.todate between @fromdate and @todate) and pa.attrcode = 'attr50' and b.bstatus not in ('AC', 'AT', 'A')
order by arrival;
Thats the sql that I have. Any questions or ideas would be much appreciated.
Thanks
I am trying to do a query whereby i set a variable to be a value and then use that value in the following query. The issue is that it only runs one statement so I need to assignment and use in the same block. Any ideas.
Set @todate := '2009-12-21';
Set @fromdate := '2009-12-18';
select fromdate as 'Arrival', pa.`text` as 'Key No.', p.propname as 'Property Name', p.areacode as 'Area',
p.cleanercode as 'Cleaner ref.', concat(c.title, ' ', c.inits, ' ', c.surname) as 'Cleaner Name',
concat(cu.title, ' ', cu.inits, ' ', cu.surname) as 'Customer Name', b.bookref as 'Bookref', 'Out' as 'Status', b.notes from bookings b
inner join prop_attributes pa on pa.propref = b.propref
inner join property p on p.propref = b.propref
inner join cleaner c on c.cleanercode = p.cleanercode
inner join customer cu on cu.cusref = b.cusref
where (b.fromdate between @fromdate and @todate) and pa.attrcode = 'attr50' and b.bstatus not in ('AC', 'AT', 'A')
UNION
select todate as 'Arrival', pa.`text` as 'Key No.', p.propname as 'Property Name', p.areacode as 'Area',
p.cleanercode as 'Cleaner ref.', concat(c.title, ' ', c.inits, ' ', c.surname) as 'Cleaner Name',
concat(cu.title, ' ', cu.inits, ' ', cu.surname) as 'Customer Name', b.bookref as 'Bookref', 'In' as 'Status', b.notes from bookings b
inner join prop_attributes pa on pa.propref = b.propref
inner join property p on p.propref = b.propref
inner join cleaner c on c.cleanercode = p.cleanercode
inner join customer cu on cu.cusref = b.cusref
where (b.todate between @fromdate and @todate) and pa.attrcode = 'attr50' and b.bstatus not in ('AC', 'AT', 'A')
order by arrival;
Thats the sql that I have. Any questions or ideas would be much appreciated.
Thanks