asked on

how to use 2 different date columns to select two different types of records.

I need help writing an AND condition on my query that selects orders.

There are two types of orders: 'blanket' and 'standard'
For standards orders I need to look at the 'RequestedDeliverydate' field
For blanket orders I need to look at the 'ContractStart', 'ContractEnd' date

Both orders are found in the same table

The 2 parameters that are passed to the Stored Procedure are: 'StartDate', 'EndDate'

	
        SELECT 
		ItemNumber [ItemNumber]
	,	MAX(ItemDescription) [ItemDescription]
	,	MAX(ItemCategoryCode) [ItemCategoryCode]
	,	MAX(ItemFamilyGroupCode) [ItemFamilyGroupCode]
	,	MAX(ItemFamilyTypeCode) [ItemFamilyTypeCode]
	,	MAX(ItemSweetenerGroupCode) [ItemSweetenerGroupCode]
	,	MAX(ItemProductGroupCode) [ItemProductGroupCode]
	,	MAX(ItemDefaultProductionFacility) [ItemDefaultProductionFacility]
	,	MAX(SalesOrderLineTypeCode) [SalesOrderLineTypeCode]
	,	SUM(SalesOrderLineOpenQuantity) [SalesOrderLineOpenQuantity]
	,	MAX(ContractStartDate) [ContractStartDate]
	,	MAX(ContractEndDate) [ContractEndDate]
	,	MAX(RequestedDeliveryDate) [RequestedDeliveryDate]
	FROM 
		SalesOrderLine vsol
	WHERE 
		(
			SalesOrderTypeCode = 'BLANKET' AND  
			IsActiveBlanket = 1
		)
		OR	
		(
			SalesOrderTypeCode = 'STANDARD' AND 
			SalesOrderLineOpenQuantity <> 0
		)
	GROUP BY 
		ItemNumber

Open in new window

Thank you very much for your help.

metropia

ASKER

I tried to do as below, but is does not work. for Blanket orders.

If I enter a date combination of:

Start Date: 3/15/2014
End Date: 3/31/2014

That code misses the Blanket orders that have a ContracStartDate of 01/01/2014 and ContractEndDate of 06/01/2014 and so on.

	WHERE 
		(
			vsol.SalesOrderTypeCode = 'BLANKET' AND  
			vsol.IsActiveBlanket = 1 AND
			vsol.ContractStartDate >= @ReportStartDate AND vsol.ContractEndDate <= @ReportEndDate
		)
		OR	
		(
			vsol.SalesOrderTypeCode = 'STANDARD' AND 
			vsol.SalesOrderLineOpenQuantity <> 0 AND
			vsol.RequestedDeliveryDate BETWEEN @ReportStartDate AND @ReportEndDate
		)

Open in new window

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

metropia

ASKER

The dates are to be used on the where clause to limit the number of records returned
I know that for standard orders i can do a

requestdeliverydate between @startdate and @enddate

But for blanket orders is more tricky because I do not know how to use StartEnd columns, and StartDate, EndDate parameters together and also, if I enter a date combination of:

Start Date: 3/15/2014
End Date: 3/31/2014

That code misses the Blanket orders that have a ContracStartDate of 01/01/2014 and ContractEndDate of 06/01/2014 and so on.

David Kroll

Do you mean that the 3/15/2014-3/31/2014 parameter dates fall between the contract start date of 1/1/2014 and end date of 6/1/2014 ?

David Kroll

WHERE
            (
                  vsol.SalesOrderTypeCode = 'BLANKET' AND
                  vsol.IsActiveBlanket = 1 AND
(@ReportStartDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate) AND
(@ReportEndDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate)

            )

metropia

ASKER

>>Do you mean that the 3/15/2014-3/31/2014 parameter dates fall between the contract start date of 1/1/2014 and end date of 6/1/2014 ?<<
yes

ASKER CERTIFIED SOLUTION

David Kroll

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

metropia

ASKER

and to include the Standard orders, does this look correct to you?

	WHERE 
		(
			vsol.SalesOrderTypeCode = 'BLANKET' AND  
            vsol.IsActiveBlanket = 1 AND
            (@ReportStartDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate) AND      
			(@ReportEndDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate)             

		)
 	OR	
		(
			vsol.SalesOrderTypeCode = 'STANDARD' AND 
			vsol.SalesOrderLineOpenQuantity <> 0 AND
			vsol.RequestedDeliveryDate BETWEEN @ReportStartDate AND @ReportEndDate
		)

Open in new window

Thank you.

David Kroll

Yes, that looks fine if you just want the requested delivery date to be between the report start and report end dates.

metropia

ASKER

thank you very much for your help