Query filter Starting Date and Ending Date in Table Unit Price

Dear Everybody

When I filter [Posting Date] form Date and To Date then Ok with SQL :smile:

"a.[Posting Date] BETWEEN @FormDate AND @ToDate" But when filter in table Sales Price structured as follows :

Customer No| Item No | Starting Date| Ending Date|
CU0001 | 10000 | 03/28/15 | 08/01/15 |
CU0001 | 10000 | 08/02/15 | NULL |

I use query


SET @DateFrom='03/12/2015'
SET @DateTo = '09/30/2015'

SELECT a.[Posting Date],a.[Document No_] 'Document No',c.No_ 'Customer No',c.Name 'Customer Name',a.[Post Code],
c.City 'Province', a.[Location Code],a.[Item No_] 'Item No',a.[Description], -a.[Invoiced Quantity] 'Quantity',
d.[Unit Price],-a.[Invoiced Quantity]*d.[Unit Price] 'Amount', b.[Gross Weight],(-a.[Invoiced Quantity]*b.[Gross Weight])/1000 'Quantity Ton',
b.[Product Group Code]
FROM [LOA$Value Entry] a, [LOA$Item] b, [LOA$Customer] c, [LOA$Sales Price] d

WHERE a.[Item No_]=b.No_
AND c.No_=a.[Source No_]
AND d.[Sales Code]=c.No_
AND b.No_=d.[Item No_]
AND a.[Location Code] IN ('LOA-01')

AND ((@DateFrom IS NULL OR d.[Starting Date] >= @DateFrom)
AND (@DateTo IS NULL OR d.[Starting Date] < DATEADD(d, 1, @DateTo)))

AND a.[Post Code] IN ('0009')

AND a. [Posting Date] BETWEEN '03/12/2015' AND '09/30/2015'
AND (a.[Document Type]=2 OR a.[Document Type]=4)
AND a.[Gen_ Prod_ Posting Group]='RETAIL'

But cannot obtain accurate data for Starting Date and Ending Date.

Please help fix error on. Thanks very much

Best Regards

Can you post some sample data along with expected result? You did not include data for [posting date] column