Solved

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

Posted on 2014-03-06
10
186 Views
Last Modified: 2014-03-13
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.
0
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 

Author Comment

by:metropia
ID: 39910752
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 39910829
>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
 

Author Comment

by:metropia
ID: 39910847
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 11

Expert Comment

by:David Kroll
ID: 39910855
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
 
LVL 11

Expert Comment

by:David Kroll
ID: 39910861
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
 

Author Comment

by:metropia
ID: 39910878
>>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
 
LVL 11

Accepted Solution

by:
David Kroll earned 400 total points
ID: 39910921
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
 

Author Comment

by:metropia
ID: 39910940
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
 
LVL 11

Expert Comment

by:David Kroll
ID: 39910950
Yes, that looks fine if you just want the requested delivery date to be between the report start and report end dates.
0
 

Author Closing Comment

by:metropia
ID: 39926580
thank you very much for your help
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question