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.
metropiaAsked:
Who is Participating?
 
David KrollConnect With a Mentor Commented:
This should work for you then...
WHERE
            (
                  vsol.SalesOrderTypeCode = 'BLANKET' AND  
                  vsol.IsActiveBlanket = 1 AND
                         (@ReportStartDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate) AND      
 (@ReportEndDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate)             

            )
 

Open in new window

0
 
metropiaAuthor Commented:
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

0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>For standards orders I need to look at the 'RequestedDeliverydate' field
>For blanket orders I need to look at the 'ContractStart', 'ContractEnd' date
SELECT blah, blah, blah, 
  CASE SalesOrderTypeCode
     WHEN 'BLANKET' THEN RequestedDeliverydate
     WHEN 'STANDARD' THEN ContractStart END as make_up_a_name_here
FROM YourTable

Open in new window

btw spell out what you mean by 'ContractStart', 'ContractEnd'

Also I have an article out there on SQL Server CASE Solutions, if it gives you any ideas that can help.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
metropiaAuthor Commented:
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.
0
 
David KrollCommented:
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 ?
0
 
David KrollCommented:
WHERE
            (
                  vsol.SalesOrderTypeCode = 'BLANKET' AND  
                  vsol.IsActiveBlanket = 1 AND
                         (@ReportStartDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate) AND      
 (@ReportEndDate BETWEEN vsol.ContractStartDate AND vsol.ContractEndDate)             

            )
0
 
metropiaAuthor Commented:
>>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
0
 
metropiaAuthor Commented:
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.
0
 
David KrollCommented:
Yes, that looks fine if you just want the requested delivery date to be between the report start and report end dates.
0
 
metropiaAuthor Commented:
thank you very much for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.