SQLTeam.com | Weblogs | Forums

Query filter Starting Date and Ending Date in Table Unit Price


#1

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

DECLARE @DateFrom AS DATETIME,
@DateTo AS DATETIME

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
dinhson_vn


#2

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