| Newbie | | Join Date: Oct 2008 Location: south africa
Posts: 1
| |
Hi I am new to the IT enviro...although i've used QSL query for a while now but on simple or single queries.
Here is the problem...
I've got two queries looking at a set of tables to extract the Costed and Invoiced data from the same database. I've tried to use a JOIN but it keeps failing. I've got multiple fields that needs to be linked.
Any suggestions. I've attached the query to make it easier -
Declare @IDateFrom DateTime,
-
@IDateTo DateTime,
-
@CDateFrom DateTime,
-
@CDateTo DateTime
-
-
-
-
-
Begin
-
---------------------------------------------------------------------------------------------------
-
---------------------------------------------------------------------------------------------------
-
-
Select
-
c.Description as CCompany,
-
lo.Description as CBranch,
-
u.Description as CService,
-
((DateName(Month,w.Shipdate))+'-'+(DateName(Year,w.Shipdate)))CShpMonth,
-
Sum(w.NumberOfParcels)CostedNoPcls,
-
Round(Sum(w.ActualMass),2)CostedActMass,
-
Round(Sum(w.ChargeMass),2)CostedChgMass,
-
Round(Sum(l.Amount),2)CostedAmount
-
From
-
tblCompany c(Nolock) Inner Join
-
tblCostingWaybill w(Nolock) On c.CompanyID = w.CompanyID Inner Join
-
tblCostingLines l(Nolock) On w.CostingWaybillID = l.CostingWaybillID Inner Join
-
tblWaybill wb(Nolock) On w.WaybillID = wb.WaybillID Inner Join
-
tblLookUp u(Nolock) On wb.WaybillClass = u.LookUpID Inner Join
-
tbllocation lo(Nolock) On wb.OriginID = lo.LocationID
-
Where
-
w.WaybillType = 'W'
-
and w.Shipdate between @CDateFrom and @CDateTo
-
Group By
-
c.Description,
-
lo.Description,
-
u.Description,
-
((DateName(Month,w.Shipdate))+'-'+(DateName(Year,w.Shipdate)))
-
Order by
-
c.Description,
-
lo.Description,
-
u.Description
-
-
--------------------------------------------------------------------------------------------------------
-
--------------------------------------------------------------------------------------------------------
-
-
-
Select
-
ic.Description as ICompany,
-
lc.Description as IBranch,
-
up.Description as IService,
-
((DateName(Month,wb.Shipdate))+'-'+(DateName(Year,wb.Shipdate)))IShpMonth,
-
Sum(iw.NumberOfParcels)InvoicedNoPcls,
-
Round(Sum(iw.ActualMass),2)InvoicedActMass,
-
Round(Sum(iw.ChargeMass),2)InvoicedChgMass,
-
Round(Sum(il.Amount),2)InvoicedAmount
-
From
-
tblCompany ic(Nolock) Inner Join
-
tblInvoiceHeader ih(Nolock) On ic.CompanyID = ih.CompanyID Inner Join
-
tblInvoiceWaybill iw(Nolock) On ih.InvoiceHeaderID = iw.InvoiceHeaderID Inner Join
-
tblInvoiceLines il(Nolock) On iw.InvoiceWaybillID = il.InvoiceWaybillID Inner Join
-
tblWaybill wb(Nolock) On iw.WaybillID = wb.WaybillID Inner Join
-
tblLookUp up(Nolock) On wb.WaybillClass = up.LookUpID Inner Join
-
tbllocation lc(Nolock) On wb.OriginID = lc.LocationID
-
Where
-
wb.WaybillType = 'W'
-
and ih.InvoiceDate between @IDateFrom and @IDateTo
-
Group By
-
ic.Description,
-
lc.Description,
-
up.Description,
-
((DateName(Month,wb.Shipdate))+'-'+(DateName(Year,wb.Shipdate)))
-
Order by
-
ic.Description,
-
lc.Description,
-
up.Description
-
-
End
|
