CODEWITHSUNDEEP
sql
Rogue Wolf
Rogue Wolf

Reputation: 23

Add Date Range to SQL Query

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

Answers (3)

Hps
Hps

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

Mutation Person
Mutation Person

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

Adriaan Stander
Adriaan Stander

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

Related Questions