January 23, 2014 at 10:24 pm
anybody can give the sample procedure of 2 tables result stored in #temp,#temp1 and finall result ll be union all .
How to do that?
January 24, 2014 at 3:09 am
Getting UNION ALL to work is just a question of, sort of, writing the same query twice:
SELECT a.Column1,
b.Column2
FROM TableA as a
JOIN Table B as b
ON a.Id = b.Id
WHERE a.Id = 42
UNION ALL
SELECT c.Column3 AS Column1,
d.Column22 AS Column2
FROM TableC AS c
JOIN TableD as d
ON c.ID = d.Id
WHERE d.id = 24;
The only real trick is to ensure that the data types of the columns are the same and that you have exactly the same number of columns in the same order between the two queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 24, 2014 at 3:09 am
Note: both #temp and #temp1 should be same structure
or both select statements should return homogenious data.
create procedure testunionall
as
begin
select * from #temp
union all
select * from #temp1
end
January 24, 2014 at 3:24 am
Hi Friends,
as example:
create table Customers
(
companyname varchar(10),
invoice_locn varchar(20),
invoice_no varchar(10),
model_ref varchar(20),
item_type char(20),
item_no int,
invoice_qty int
)
insert Customers values ('VB','chennai ','tn/002/13-14','#','K','6000320','25')
insert Customers values ('VB','chennai','tn/002/13_14','6000320','X','500320','30')
insert Customers values ('VB','chennai','tn/002/13_14','6000320','X','500300','05')
insert Customers values ('VB','chennai','tn/002/13_14','#','I','500340','25')
like i ve many records......
my expecting o/p is:
--------------------
company invoice_no invoice_locn item Sales Free
VB tn/002/13-14 Chennai 500340 25 0
VB tn/002/13-14 Chennai 500320 25 5
VB tn/002/13-14 Chennai 500300 0 5
alter procedure tr
(
@x datetime,
@y datetime
)
as
begin
select
a.companY_No,
a.invoice_locn,
a.invoice_type,
xx.invoice_no,
xx. Kit_Ref_group,
xx.item_no,
xx.sales,
xx.ge
into
#temp
from
dms_t_invoice_hdr a
inner join
(
select
a.company_no,
a.invoice_no,
a.invoice_locn,
a.item_no as Kit_Ref_group,
b.item_no ,
max(CASE WHEN A.invoice_qty>B.invoice_qty THEN 0 ELSE A.invoice_qty END) as sales ,
b.invoice_qty,
a.invoice_type,
b.invoice_qty - (CASE WHEN A.invoice_qty>B.invoice_qty THEN 0 ELSE A.invoice_qty END) as ge
from
dms_t_invoice_item a
inner join
dms_t_invoice_item b
on
a.invoice_no= b.invoice_no
where
a.item_no=b.model_ref
and
a.company_no=b.company_no
and
a.invoice_type=b.invoice_type
and
a.invoice_locn=b.invoice_locn
--and
-- a.invoice_no='INVH/000193/12-13 '
group by
a.company_no,
a.invoice_no,
a.item_no ,
b.item_no ,
a.invoice_qty,
b.invoice_qty,
a.invoice_type,
a.invoice_locn
)xx
on
xx.company_no=a.company_no
where
a.invoice_no=xx.invoice_no
and
xx.invoice_type=a.invoice_type
and
xx.invoice_locn=a.invoice_locn
and
a.invoice_dt between @x and @y
--and a.invoice_no='BLR/001653/12-13'
and
xx.ge >='0'
group by
a.companY_No,
a.invoice_locn,
xx.invoice_no,
xx. Kit_Ref_group,
xx.item_no,
xx.sales,
xx.ge,
a.invoice_type
select
b.company_no,
b.invoice_locn,
a.invoice_type,
a.invoice_no,
a.item_no,
sales=sum(case when left(item_no,1)='5'and item_type ='I' then invoice_qty else 0 end),
ge= '.00000000'
into
#temp1
from
dms_t_invoice_hdr b
inner join
dms_t_invoice_item a
on
a.company_no=b.company_no
where
a.invoice_no=b.invoice_no
and
a.invoice_type=b.invoice_type
and
a.invoice_locn=b.invoice_locn
and
b.invoice_dt between @x and @y
--and a.invoice_no='BLR/001653/12-13'
group by
a.invoice_no,
b.companY_no,
b.invoice_locn,
a.item_no,
a.invoice_type
---------- my coding for combine------
select
Q.company_no,
Q.invoice_locn,
W.invoice_type,
W.invoice_no,
Q.item_no,
Q.sales as sales,
Q.ge
from
#ter Q
left outer join
#der W
on
Q.company_no=W.company_no
where
Q.invoice_locn=W.invoice_locn
and
W.invoice_no=Q.invoice_no
and
W.invoice_type=Q.invoice_type
and
w.item_no=q.item_no
end
Finally i wanna combine both #temp and #temp1 .
in #temp contains sales & free for ITEM_TYPE='x'(i.e group products which was referred on item)
#temp1 contains sales & free for ITEM_TYPE='I' which was directly billed now i wanna display both results on my o/p with out duplicate how to do
January 24, 2014 at 6:35 am
Does the stored procedure reference the Customers table?
Try to keep your code simple:
SELECT
h.company_no,
h.invoice_no,
h.invoice_locn,
h.invoice_type,
a.item_no as Kit_Ref_group,
b.item_no ,
max(CASE WHEN A.invoice_qty > B.invoice_qty THEN 0 ELSE A.invoice_qty END) as sales ,
b.invoice_qty - (CASE WHEN A.invoice_qty > B.invoice_qty THEN 0 ELSE A.invoice_qty END) as ge
INTO #temp
FROM dms_t_invoice_hdr h
INNER JOIN dms_t_invoice_item a
on a.company_no = h.company_no
and a.invoice_no = h.invoice_no
and a.invoice_type = h.invoice_type
and a.invoice_locn = h.invoice_locn
INNER JOIN dms_t_invoice_item b
on a.invoice_no = b.invoice_no
and a.company_no = b.company_no
and a.invoice_type = b.invoice_type
and a.invoice_locn = b.invoice_locn
and a.item_no = b.model_ref
--and
-- a.invoice_no='INVH/000193/12-13 '
WHERE h.invoice_dt between @x and @y
group by
h.invoice_no,
h.company_no,
h.invoice_type,
h.invoice_locn,
a.item_no,
b.item_no,
a.invoice_qty,
b.invoice_qty
HAVING ge >= 0
Edit: phat phinger
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply