Joining two tables - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joining two tables

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-25 : 23:13:04

Hi Forumer's

Im running my query but the result i get is not correct.
im only getting 1 record w/c is the first row but it should be 3.
the first row records shows positive Qty but the 2 row that i did not get are with negative qty. positive qty should be always on the top.

Other requirements:
1. need to get records from table1 the positive Qty.
2. need to get the records from table2 the negative Qty which are the breakdown of records from Table1

Can you help me guys to modify my script. thanks.


Script:
;With CTE 
AS
(
Select 
	JournalID
	,Transdate
	,Itemid
	,Qty
	,CostAmount
	,JOURNALTYPE
	,BOMLINE
From #Table1
Where TRANSDATE >= '2012/06/01' and TRANSDATE <'2012/06/27'
and JOURNALTYPE=3
and BOMLINE=0
and JOURNALID='PJ01032608'
)
Select 
	t1.JOURNALID
	,t1.TRANSDATE
	,t1.ITEMID
	,t1.QTY
	,t1.COSTAMOUNT
	,t1.JOURNALTYPE
	,t1.BOMLINE
	,t2.QTY as BOMQTY
	,t2.COSTAMOUNTPOSTED 
From CTE t1	
Left Outer Join #Table2 t2
On t2.TRANSREFID=t1.JOURNALID
and t2.ITEMID=t1.ITEMID
and t2.STATUSISSUE=1
and t2.TRANSTYPE=9

Data:
Create Table #Table1
(JOurnalid nvarchar(35),
Transdate datetime,
Journaltype int,
Itemid nvarchar(35),
Qty numeric(28,12),
Costamount numeric(28,12)
BOMLINE int)

INSERT INTO #Table1(Journalid, transdate, journaltype, itemid, qty, costamount,bomline)
SELECT 'PJ01032608','2012-06-22',3,'HTC1393',-68.00,0,1 UNION ALL
SELECT 'PJ01032608','2012-06-22',3,'HTC1394',-1.00,0,1 UNION ALL
SELECT 'PJ01032608','2012-06-22',3,'HTC1175',65.00,1913.42,0 UNION ALL


Create Table #Table2
(Itemid nvarchar(35),
Statusissue int,
Qty numeric(28,12),
CostAmountPosted numeric(28,12),
transtype int,
transferid nvarchar(35))


INSERT INTO #Table2(itemid, statusissue,qty,costamountposted,transtype,transferid)
SELECT 'HTC1393',1,-68.00,-1874.77,9,'PJ01032608' union all
SELECT 'HTC1394',1,-1.00,-38.65,9,'PJ01032608' union all
SELECT 'HTC1175',0,65.00,1913.42,10,'PJ01032608' union all

Derived Result:
Transdate ----Journalid---Itemid----QTY---Costamount
-----------------------------------------------------------------------
2012-06-22---PJ01032608--HTC1175-- 65.00--  1913.42
2012-06-22---PJ01032608--HTC1393-- -68.00-- -1874.77
2012-06-22---PJ01032608--HTC1394-- -1.00-- -38.65

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-25 : 23:32:21

Hi Guys,

After removing the Itemid in the left outer joi i got the correct records. Btw, How could i format my script w/c the result i would like my sanmple. see below.

result get from scripts:

JOURNALID	TRANSDATE	ITEMID	QTY	COSTAMOUNT	JOURNALTYPE	BOMLINE	BOMQTY	COSTAMOUNTPOSTED
PJ01032608	2012-06-22 00:00:00.000	HTC1175	65.000000000000	1913.420000000000	3	0	-68.000000000000	-1874.770000000000
PJ01032608	2012-06-22 00:00:00.000	HTC1175	65.000000000000	1913.420000000000	3	0	-1.000000000000	-38.650000000000


;With CTE 
AS
(
Select 
	JournalID
	,Transdate
	,Itemid
	,Qty
	,CostAmount
	,JOURNALTYPE
	,BOMLINE
From #Table1
Where TRANSDATE >= '2012/06/01' and TRANSDATE <'2012/06/27'
and JOURNALTYPE=3
and BOMLINE=0
and JOURNALID='PJ01032608'
)
Select 
	t1.JOURNALID
	,t1.TRANSDATE
	,t1.ITEMID
	,t1.QTY
	,t1.COSTAMOUNT
	,t1.JOURNALTYPE
	,t1.BOMLINE
	,t2.QTY as BOMQTY
	,t2.COSTAMOUNTPOSTED 
From CTE t1	
Left Outer Join #Table2 t2
On t2.TRANSREFID=t1.JOURNALID
----and t2.ITEMID=t1.ITEMID
and t2.STATUSISSUE=1
and t2.TRANSTYPE=9




SAMPLE
Transdate ----Journalid---Itemid----QTY---Costamount
-----------------------------------------------------------------------
2012-06-22---PJ01032608--HTC1175-- 65.00--  1913.42
2012-06-22---PJ01032608--HTC1393-- -68.00-- -1874.77
2012-06-22---PJ01032608--HTC1394-- -1.00-- -38.65

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-26 : 01:40:51

I got it.

Here is the script.

;With CTE 
AS
(
Select 
	v.JOURNALID
	,v.TRANSDATE
	,v.ITEMID
	,v.QTY
	,v.COSTAMOUNT
	,v.JOURNALTYPE
	,v.BOMLINE
From #Sample1 v
Where TRANSDATE >= '2012/06/01' and TRANSDATE <'2012/06/27'
and JOURNALTYPE=3
and BOMLINE=0
and LEFT(v.itemid,3)='HTC'
and v.JOURNALID='PJ01032608'
)
Select 
	t1.JOURNALID
	,t1.TRANSDATE
	,t1.ITEMID as MotherITEMID
	,t1.QTY 
	,t1.COSTAMOUNT
	,t1.JOURNALTYPE
	,t1.BOMLINE
	,inv.ITEMID as ChildItemid
	,inv.QTY as BOMQTY
	,inv.COSTAMOUNTPOSTED 
	,i.WMSLOCATIONID
From CTE t1	
Left Outer Join (Select Itemid, qty , COSTAMOUNTPOSTED, STATUSISSUE, TRANSTYPE, TRANSREFID, INVENTDIMID
       from #Sample2) inv
On inv.TRANSREFID=t1.JOURNALID
Left Outer Join Dynamics.dbo.INVENTDIM i
On i.INVENTDIMID=inv.INVENTDIMID
and inv.STATUSISSUE=1
and inv.TRANSTYPE=9
Order by t1.JOURNALID, BOMQTY desc

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-26 : 02:57:11

Hi Guys,

Running this Query, it takes more than 20 minutes to process the records of 7229 and also use high CPU utilization
Is there any wrong from my Query? any other method to speed the
processing. I was thinking the second CTE has problem in processing. Thanks.

;With CTE 
AS
(
Select 
	v.JOURNALID
	,v.TRANSDATE
	,v.ITEMID
	,v.QTY
	,v.COSTAMOUNT
	,v.JOURNALTYPE
	,v.BOMLINE
From INVENTJOURNALTRANS v
Where TRANSDATE >= '2012/06/01' and TRANSDATE <'2012/06/27'
and JOURNALTYPE=3
and BOMLINE=0
and LEFT(v.itemid,3)='HTC'
--and v.JOURNALID='PJ01032608'
),
CTE2 AS
(
Select 
	t1.JOURNALID
	,t1.TRANSDATE
	,t1.ITEMID as MotherITEMID
	,t1.QTY 
	,t1.COSTAMOUNT
	,t1.JOURNALTYPE
	,t1.BOMLINE
	,inv.ITEMID as ChildItemid
	,inv.QTY as BOMQTY
	,inv.COSTAMOUNTPOSTED 
	,i.WMSLOCATIONID
	,inv.STATUSISSUE
	,inv.TRANSTYPE
From CTE t1	
Left Outer Join (Select Itemid, qty , COSTAMOUNTPOSTED, STATUSISSUE, TRANSTYPE, TRANSREFID, INVENTDIMID
       from INVENTTRANS ) inv
On inv.TRANSREFID=t1.JOURNALID
Left Outer Join INVENTDIM i
On i.INVENTDIMID=inv.INVENTDIMID
and inv.STATUSISSUE=1
and inv.TRANSTYPE=9
)
Select 
	JournalID
	,Transdate
	,Case When STATUSISSUE=0 Then MotherITEMID End as MotherItemID
	,Case When STATUSISSUE=0 Then Qty End as Quantity
	,Case When STATUSISSUE=0 Then COSTAMOUNT End as CostAmount
	,Case When STATUSISSUE=1 Then ChildItemid End as ChildItemid
	,Case When STATUSISSUE=1 Then BOMQTY End as BOMQty
	,Case When STATUSISSUE=1 Then COSTAMOUNTPOSTED End as CostAmountPosted
	,WMSLOCATIONID
From CTE2
Order by JOURNALID, BOMQty asc

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-26 : 23:13:26

Make some adjustment. instead of using Left Outer Join, I use Cross Apply and it takes only a minute.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources