view: vwCompanyLedger
select t1.col1
      ,t2.col2
      ,t3.col2
      ,t4.col2
      ,t5.col2
      ,t6.col2
      ,t.*
from tbltransactions as t
left join tbl1 as t1 on t1.id = t.id
left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id
left join tbl4 as t4 on t4.id = t.id
left join tbl5 as t5 on t5.id = t.id
left join tbl6 as t6 on t6.id = t.id

CREATE FUNCTION dbo.fnCompanyLedger(
 @From as datetime
,@To as datetime
,@CompanyID as numeric(18,0)
) RETURNS @List TABLE 
(
	Id int identity(1,1),
	col1 varchar(200),
	col2 varchar(200),
	col3 varchar(200),
	col4 varchar(200),
	col5 varchar(200),
	col6 varchar(200),
	trans7 varchar(200),
	trans8 varchar(200),
	trans9 varchar(200),
	trans10 varchar(200),
	trans11 varchar(200),
	trans12 varchar(200),
	trans13 varchar(200),
	trans14 varchar(200),
	transID as numeric(18),
	CompanyID as numeric(18)
)

BEGIN

insert into @List (col1, col2, col3, col4, col5, col16, 
		   trans7, trans8, trans9, trans10, 
		   trans11, trans12, trans13, trans14, transID, CompanyID)
select t1.col1
      ,t2.col2
      ,t3.col2
      ,t4.col2
      ,t5.col2
      ,t6.col2
      ,t.trans7, t.trans8, t.trans9, t.trans10, 
      ,t.trans11, t.trans12, t.trans13, t.trans14, transID, t.CompanyID 
from tbltransactions as t
left join tbl1 as t1 on t1.id = t.id
left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id
left join tbl4 as t4 on t4.id = t.id
left join tbl5 as t5 on t5.id = t.id
left join tbl6 as t6 on t6.id = t.id
WHERE dtFrom >= @From and dtTo <= @To
  and CompanyID = @CompanyID
RETURN

END

SELECT l.*, p.paymentDate, o.orderDate
FROM vwCompanyLedger as l
left join tblpayments as p on p.id =  l.col1
left join tblorders as o on p.id = l.col2
WHERE l.dtFrom >= @From and l.dtTo <= @To
  and l.CompanyID = @CompanyID

SELECT l.*, p.paymentDate, o.orderDate
FROM dbo.fnCompanyLedger(@From, @To, @CompanyID) as l
left join tblpayments as p on p.id =  l.col1
left join tblorders as o on p.id = l.col2

select		t1.col1,
		t2.col2,
		t3.col2,
		t1.col2,
		t1.col2,
		t1.col2
from		tbltransactions as t
left join	tbl1 as t1 on t1.id = t.id
left join	tbl2 as t2 on t2.id = t1.id
left join	tbl3 as t3 on t3.id = t1.id

quote:

---

Originally posted by Peso

Are these also typos?

left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by jonasalbert20

quote:

---

Originally posted by Peso

Are these also typos?

left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id

---

E 12°55'05.25"
N 56°04'39.16"

---

Nope Peso. thanks for the response. Any suggestion from here???




Want Philippines to become 1st World COuntry? Go for World War 3...

---

quote:

---

Originally posted by jonasalbert20

Which is better and much faster? example 1 or 2?
Thanks in advance.

---

quote:

---

I'm wondering what would be the performance when I deploy it on the server?

---