none
Filter on date, but results not in query

    Question

  • I have a question,

    I have a query that shows a summery of sold items. This one is running fine. But now I want to filter the query on a deliverdate. (ie give a range from lets say 1-1-2012 to 12-31-2012. I added a paramater and all went fine exect the results are different now. The items aren't grouped anymore becouse it also groups on deliverydate and I don't want that.

    The query is:

    SELECT     VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDTABLE.NAME, VENDPACKINGSLIPTRANS.ITEMID, VENDPACKINGSLIPTRANS.NAME AS ItemDescr,
                          VENDPACKINGSLIPTRANS.PURCHUNIT, SUM(VENDPACKINGSLIPTRANS.QTY) AS Aantal, SUM(VENDPACKINGSLIPTRANS.VALUEMST) AS Bedrag
    FROM         VENDPACKINGSLIPJOUR INNER JOIN
                          VENDPACKINGSLIPTRANS ON VENDPACKINGSLIPJOUR.PACKINGSLIPID = VENDPACKINGSLIPTRANS.PACKINGSLIPID INNER JOIN
                          VENDTABLE ON VENDPACKINGSLIPJOUR.ORDERACCOUNT = VENDTABLE.ACCOUNTNUM AND
                          VENDPACKINGSLIPTRANS.DATAAREAID = VENDTABLE.DATAAREAID AND VENDPACKINGSLIPJOUR.DATAAREAID = VENDTABLE.DATAAREAID
    GROUP BY VENDPACKINGSLIPTRANS.ITEMID, VENDTABLE.NAME, VENDPACKINGSLIPTRANS.NAME, VENDTABLE.DATAAREAID,
                          VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDPACKINGSLIPTRANS.PURCHUNIT, VENDPACKINGSLIPTRANS.DELIVERYDATE
    HAVING      (VENDTABLE.DATAAREAID = N'zm') AND (VENDPACKINGSLIPTRANS.DELIVERYDATE >= @startdate) AND
                          (VENDPACKINGSLIPTRANS.DELIVERYDATE <= DATEADD(d, 0, @enddate))
    ORDER BY VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDPACKINGSLIPTRANS.ITEMID

    the bold code is added to filter the results based on deliverydate.

    The results show that itemid 0040 isn't grouped on but split (due to a different deliveryday).

    How can I fix this?

    acc        name          itemid itemdesc                       purchunit  Qty                  valuemst

    020038 customer a 0258 Ophoogzand  / ton Ton 14,500000000000 8124,990000000000
    020044 customer x 0141 Cement CEM I 52,5 N Ton 28,360000000000 21900,120000000000
    020044 customer x 0384 Restanten divers Ton 1,000000000000 2191,000000000000
    020057 customer b 0442 Grauwacke 80-120 Ton 33,900000000000 810,210000000000
    020103 customer e 0173 Filtergrind 3-5 mm 25kg Ton 2,500000000000 632,500000000000
    020104 customer z 0006 Betonzand 0-4 (0658) Ton 1073,000000000000 130343,720000000000
    020104 customer z 0022 Kies 8-16 Ton 505,000000000000 66381,150000000000
    020104 customer z 0023 Kies 4-32 Ton 575,000000000000 70676,750000000000
    020104 customer z 0040 Scheepsvracht Ton 1073,000000000000 29950,750000000000
    020104 customer z 0040 Scheepsvracht Ton 1080,000000000000 29770,000000000000
    Tuesday, September 25, 2012 2:25 PM

Answers

  • Edwin,

    before the GROUP BY clause, I would add this:

    WHERE VENDPACKINGSLIPTRANS.DELIVERYDATE >= @startdate
    AND VENDPACKINGSLIPTRANS.DELIVERYDATE <= @enddate

    The code in bold that you added can be removed then.


    -

    Tuesday, September 25, 2012 2:29 PM

All replies

  • Edwin,

    before the GROUP BY clause, I would add this:

    WHERE VENDPACKINGSLIPTRANS.DELIVERYDATE >= @startdate
    AND VENDPACKINGSLIPTRANS.DELIVERYDATE <= @enddate

    The code in bold that you added can be removed then.


    -

    Tuesday, September 25, 2012 2:29 PM
  • Perfect! Thank you

    Edwin

    Tuesday, September 25, 2012 5:16 PM