Reputation: 23
I have the following query which returns the total value of purchase orders against suppliers for a particular company (we have multiple companies in our group).
I want to add a date range using poheader.orderdate
but as I didn't write this query and it is slightly more complex than I am used to I am not sure where to add this clause.
Any advice would be greatly appreciated.
SELECT vendor.Vendorid AS 'ID',
vendor.name AS 'Name',
Total
FROM vendor LEFT JOIN
(
select sum (podetail.orderqty * podetail.unitcost) AS Total,
podetail.VendorNum,
podetail.Company
from podetail
where exists (
select 1
from poHeader
where poHeader.OrderNum = podetail.OrderNum
and poHeader.ApprovalStatus = 'A'
)
and exists (
select 1
from vendor
WHERE vendor.company = 'MyCompany'
and vendor.VendorNum = podetail.VendorNum
and vendor.Company = podetail.Company
)
group by podetail.VendorNum,
podetail.Company
) OD ON vendor.Vendornum = OD.Vendornum
and vendor.Company = OD.Company
WHERE vendor.company = 'MyCompany'
ORDER BY vendor.name
Upvotes: 0
Views: 1254
Reputation: 1177
It looks like the first exists clause should be modified as below:
(select 1 from poHeader where poHeader.OrderNum = podetail.OrderNum and poHeader.ApprovalStatus = 'A' and poheader.orderdate BETWEEN 'start_date' AND 'end_date's)
Upvotes: 0
Reputation: 30530
I'm making an assumption as to what you mean by 'add a date range'. I'm assuming you want to filter out the results that fall in a particular range:
SELECT vendor.Vendorid AS 'ID', vendor.name AS 'Name', Total
FROM vendor
LEFT JOIN (select sum (podetail.orderqty * podetail.unitcost) AS Total, podetail.VendorNum, podetail.Company
from podetail
where exists
(select 1
from poHeader
where poHeader.OrderNum = podetail.OrderNum and poHeader.ApprovalStatus = 'A')
/*this line*/ and convert(varchar(8),poHeader.orderdate,112) between '20100101' and '20101231'
and exists
(select 1 from vendor
WHERE vendor.company = 'MyCompany'
and vendor.VendorNum = podetail.VendorNum
and vendor.Company = podetail.Company)
group by podetail.VendorNum, podetail.Company) OD
ON vendor.Vendornum = OD.Vendornum
and vendor.Company = OD.Company
WHERE vendor.company = 'MyCompany'
ORDER BY vendor.name
I've reformatted for ease of reading.
I've also converted to a varchar(8), but the parameters that specify a range could be a datetime:
and poHeader.orderdate between getdate()-10 and getdate()
Upvotes: 1
Reputation: 166626
OK, once it is formatted, it dosnt look all that scary, now does it?
Now you can see where to place the where clause?
Something like
SELECT vendor.Vendorid AS 'ID',
vendor.name AS 'Name',
Total
FROM vendor LEFT JOIN
(
select sum (podetail.orderqty * podetail.unitcost) AS Total,
podetail.VendorNum,
podetail.Company
from podetail
where exists (
select 1
from poHeader
where poHeader.OrderNum = podetail.OrderNum
and poHeader.ApprovalStatus = 'A'
/*-------PLACE THE Date Range Check HERE------*/
AND poheader.orderdate BETWEEN @StartDate AND @EndDate
)
and exists (
select 1
from vendor
WHERE vendor.company = 'MyCompany'
and vendor.VendorNum = podetail.VendorNum
and vendor.Company = podetail.Company
)
group by podetail.VendorNum,
podetail.Company
) OD ON vendor.Vendornum = OD.Vendornum
and vendor.Company = OD.Company
WHERE vendor.company = 'MyCompany'
ORDER BY vendor.name
Upvotes: 1