P
poppy
Hi Experts
I'm so seriously sick of this query now - I hope you can help m
otherwise I will no longer have any hair left. I realize that this i
not a sql forum, but I thought to take a chance and see if there migh
be someone who can help me here.
I have the following two queries:
select a.group_descr, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat
as value from py_group a
left outer join cb b on
a.py_mat_no = b.py_mat_no
and month(b.inv_date) = 01
and year(b.inv_date) = 2004
and upper(hstatus) <> 'D'
group by a.group_descr
order by a.group_descr
-----------------------------------------------------------------------
And
-----------------------------------------------------------------------
select group_descr, sum(a.qty) as qty, sum(a.value_incl_vat - a.vat) a
value from cb a
left outer join chain_group b on
a.mat_no = b.product_code
where month(a.inv_date) = 01
and year(a.inv_date) = 2004
and upper(hstatus) <> 'D'
and a.mat_group in ('CBS04', 'CBS09', 'CBS12', 'CBS14', 'CBS22'
'CBS42', 'CBS95',' CBS96')
group by group_descr
order by group_descr
My problem is that I need to join these two queries into one query s
they can extract data into excel. I tried using union like this:
select a.group_descr, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat
as value from py_group a
left outer join cb b on
a.py_mat_no = b.py_mat_no
and month(b.inv_date) = 01
and year(b.inv_date) = 2004
and upper(hstatus) <> 'D'
group by a.group_descr
union select group_descr, sum(a.qty) as qty, sum(a.value_incl_vat
a.vat) as value from cb a
left outer join chain_group b on
a.mat_no = b.product_code
where month(a.inv_date) = 01
and year(a.inv_date) = 2004
and upper(hstatus) <> 'D'
and a.mat_group in ('CBS04', 'CBS09', 'CBS12', 'CBS14', 'CBS22'
'CBS42', 'CBS95',' CBS96')
group by group_descr
order by group_descr
--------------------------------------------------------------------------
But this is not what I'm looking for. I would like the program to rea
the first query then populate excel with the resulting data, then rea
the next query and dump the resulting data INTO THE NEXT EMPTY colum
in the same sheet AND MAKE SURE that the group_descr match, fo
example:
Code
-------------------
For example
Instead of this:
Both query results
A B C
January
Group_descr Qty Value
Matchs 45 56.98 (From Table 1)
Matchs 3 23.10 (From Table 2)
Firesticks 20 234.4 (From Table 1)
Firesticks 12 45.3 (From Table 2)
-------------------------------------------------------------------------
This is what I want to happen
First query result Second query result
A B C D E
January
Group_descr Qty Value Qty Value
Matchs 45 56.98 3 23.10
Firesticks 20 234.4 12 45.3
I'm so seriously sick of this query now - I hope you can help m
otherwise I will no longer have any hair left. I realize that this i
not a sql forum, but I thought to take a chance and see if there migh
be someone who can help me here.
I have the following two queries:
select a.group_descr, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat
as value from py_group a
left outer join cb b on
a.py_mat_no = b.py_mat_no
and month(b.inv_date) = 01
and year(b.inv_date) = 2004
and upper(hstatus) <> 'D'
group by a.group_descr
order by a.group_descr
-----------------------------------------------------------------------
And
-----------------------------------------------------------------------
select group_descr, sum(a.qty) as qty, sum(a.value_incl_vat - a.vat) a
value from cb a
left outer join chain_group b on
a.mat_no = b.product_code
where month(a.inv_date) = 01
and year(a.inv_date) = 2004
and upper(hstatus) <> 'D'
and a.mat_group in ('CBS04', 'CBS09', 'CBS12', 'CBS14', 'CBS22'
'CBS42', 'CBS95',' CBS96')
group by group_descr
order by group_descr
My problem is that I need to join these two queries into one query s
they can extract data into excel. I tried using union like this:
select a.group_descr, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat
as value from py_group a
left outer join cb b on
a.py_mat_no = b.py_mat_no
and month(b.inv_date) = 01
and year(b.inv_date) = 2004
and upper(hstatus) <> 'D'
group by a.group_descr
union select group_descr, sum(a.qty) as qty, sum(a.value_incl_vat
a.vat) as value from cb a
left outer join chain_group b on
a.mat_no = b.product_code
where month(a.inv_date) = 01
and year(a.inv_date) = 2004
and upper(hstatus) <> 'D'
and a.mat_group in ('CBS04', 'CBS09', 'CBS12', 'CBS14', 'CBS22'
'CBS42', 'CBS95',' CBS96')
group by group_descr
order by group_descr
--------------------------------------------------------------------------
But this is not what I'm looking for. I would like the program to rea
the first query then populate excel with the resulting data, then rea
the next query and dump the resulting data INTO THE NEXT EMPTY colum
in the same sheet AND MAKE SURE that the group_descr match, fo
example:
Code
-------------------
For example
Instead of this:
Both query results
A B C
January
Group_descr Qty Value
Matchs 45 56.98 (From Table 1)
Matchs 3 23.10 (From Table 2)
Firesticks 20 234.4 (From Table 1)
Firesticks 12 45.3 (From Table 2)
-------------------------------------------------------------------------
This is what I want to happen
First query result Second query result
A B C D E
January
Group_descr Qty Value Qty Value
Matchs 45 56.98 3 23.10
Firesticks 20 234.4 12 45.3