tblexpense tblpayments tblstock
ipaymentid ipaymentid id id
expname ibookingid productid
vpatientname dealername
vtest stockname
vpayment packsize
vbillnum quantity
dbilldate price
invoicenumber
invoicedate
totamount
HOW CAN I WRITE STORED PROCEDURE FROM ABOVE MENTIONED THREE TABLES.THE TABLES WHICH CONTAIN NULL ROWS.BECAUSE ONE OF THE TABLE CONTAIN A ROW WITH VALUES IN ID AND OTHER TWO TABLES DOES NOT HAVE VALUES .BUT I NEED TO MAKE CRYSTAL REPORT INCLUDING ALL THESE OR PLEASE CORRECT BELOW MENTIONED QUERY
alter PROCEDURE [Prcexpincbalview]
(
@month varchar(50),
@year varchar(50)
)
as
--SELECT tblexpense.expname,tblexpense.expamount,tblpayments.vpayment from tblexpense Full join tblpayments on tblexpense.dbilldate=tblpayments.dbilldate
--WHERE tblexpense.dbilldate is null or tblpayments.dbilldate is null
SELECT
T.ipaymentid,
T.dbilldate,
E.expname,
E.expamount,
I.vpayment
FROM
(
select ipaymentid, dbilldate from tblpayments
UNION
select ipaymentid, dbilldate from tblexpense
) T
LEFT JOIN tblpayments I ON I.ipaymentid = T.ipaymentid and I.dbilldate=I.dbilldate
LEFT JOIN tblexpense E ON E.ipaymentid = T.ipaymentid and E.dbilldate=E.dbilldate
Where T.dbilldate = T.dbilldate