Retrieving from two temp tables

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

General SQL Server Forums

New to SQL Server Programming

Retrieving from two temp tables

Author

Topic

CThomp
Starting Member

5 Posts

Posted - 2014-03-31 : 11:38:30

I have two temporary tables, both contain field 'BranchID'.

First table @TEMPNET returns 10 different BranchIDs, but the second table @TEMPCREDIT returns only 7 of those IDs, so when I run select from both tables 'WHERE N.BranchID = C.BranchID2' the result is blank. If I limit both tables to the IDs that are the same in both sets, it works.

How can I get ALL the results from table1 AND the results from table2 which match table1?

Thanks for any help!
-----------------------------

DECLARE @TEMPNET
TABLE
( BranchID int
, NetSales decimal(18,4)
, Cost decimal(18,4)
, SaleMonthNbr int
, SaleMonth varchar(15))

INSERT INTO @TEMPNET
( BranchID
, NetSales
, Cost
, SaleMonthNbr
, SaleMonth)

SELECT
, sum((IL.TOTALAMOUNT+IL.TOTALTAX)-(IL.TOTALTAX + ((IL.TOTALAMOUNT+IL.TOTALTAX)*(IH.SETTLEMENTDISCOUNT/100)) + CASE WHEN IL.PRODUCTID IN (10,18,11743,12473,13192)

THEN (IL.TOTALAMOUNT) ELSE 0 END))
, sum(IL.TOTALCOST)
, month(IH.INVOICEDATE)
, datename(month, IH.INVOICEDATE)

FROM INVOICELINE IL
INNER JOIN INVOICEHEADER IH ON IH.INVOICEID = IL.INVOICEID
INNER JOIN BRANCH B ON B.BRANCHID = IH.BRANCHID
WHERE IL.PRODUCTID NOT IN(22,23,33495)
AND YEAR(IH.INVOICEDATE)=YEAR(GETDATE())
GROUP BY B.BranchID, month(IH.INVOICEDATE), datename(month, IH.INVOICEDATE)

DECLARE @TEMPCREDIT
TABLE
( BranchID2
, CreditNotes decimal(18,4)
, CreditMonthNbr int
, CreditMonth varchar(15))

INSERT INTO @TEMPCREDIT
( BranchID2
, CreditNotes
, CreditMonthNbr
, CreditMonth)

SELECT
B.BranchID2
, sum((((CL.TOTALAMOUNT-CL.RESTOCKCHARGE)+CL.TOTALTAX))-(CL.TOTALTAX+(((CL.TOTALAMOUNT-CL.RESTOCKCHARGE)+CL.TOTALTAX)*(CH.SETTLEMENTDISCOUNT/100))+CASE WHEN CL.PRODUCTID IN (10,18,11743,12473,13192) THEN (CL.TOTALAMOUNT) ELSE 0 END))
, month(CH.DATETIMERELEASED)
, datename(month, CH.DATETIMERELEASED)

FROM CREDITNOTEHEADER CH
INNER JOIN CREDITNOTELINE CL ON CL.CREDITNOTEID = CH.CREDITNOTEID
INNER JOIN BRANCH B2 ON B2.BRANCHID = CH.BRANCHID
WHERE CL.PRODUCTID NOT IN(22,23,33495)
AND YEAR(CH.DATETIMERELEASED)=YEAR(GETDATE())
GROUP BY B2.BranchID, month(CH.DATETIMERELEASED), datename(month, CH.DATETIMERELEASED)

SELECT
N.BranchID
, N.SaleMonthNbr
, N.SaleMonth
, N.NetSales
, C.CreditNotes
, (N.NetSales - C.CreditNotes) as Net_Less_Credits
, N.Cost as TotalCosts
, N.NetSales-(C.CreditNotes+N.Cost) AS Margin_Amt
, CAST ((N.NetSales-(C.CreditNotes+N.Cost)) / (N.NetSales-C.CreditNotes) AS decimal(4,4)) as Margin_Pct

FROM @TEMPNET N, @TEMPCREDIT C
WHERE N.BranchID = C.BranchID2 AND N.SaleMonthNbr = C.CreditMonthNbr
ORDER BY N.BranchID, N.SaleMonthNbr

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-31 : 11:47:17

LEFT OUTER JOIN:

SELECT *
FROM 
	@TEMPNET AS N
LEFT OUTER JOIN
	@TEMPCREDIT AS C
	ON N.BranchID = C.BranchID2 
	AND N.SaleMonthNbr = C.CreditMonthNbr

CThomp
Starting Member

5 Posts

Posted - 2014-03-31 : 12:02:22

Thank you for the quick reply, and it works!!

I did have to take out the CAST line otherwise the result was empty ... now how do I set '0' for the missing values to run calculations?

, (N.NetSales - C.CreditNotes) as Net_Less_Credits
, N.NetSales-(C.CreditNotes+N.Cost) AS Margin_Amt
, CAST ((N.NetSales-(C.CreditNotes+N.Cost)) / (N.NetSales-C.CreditNotes) AS decimal(4,4)) as Margin_Pct

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-31 : 12:25:32

I would use the NULLIF function on the denominator and then use the ISNULL or COALESCE function on the results if you need to prevent NULL values:

CAST ((N.NetSales-(C.CreditNotes+N.Cost)) / NULLIF((N.NetSales-C.CreditNotes), 0) AS decimal(4,4)) as Margin_Pct

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources