Code needed for searching between date ranges

[Former Member]

Ive got a report that i need some code for. The report is showing some costing information. There is a part number in the database that im trying to show the costing for. However over time the operations department has changed the cost of this part number. When you query the database, the cost shown for this part is relative to the date the job was created.

e.g.,

part 1029 cost has changed throughout its history.

on 9/2/09 its original cost was .0024

on 6/9/10 its cost was changed to .0032

on 6/30/10 its cost was changed to .0026

all the dates of these changes are in one table and in one column

In the ERP system, when you ask for costing on shop orders for this part #, it shows you the costing relative to the date the shop order was created. I need code that would enable me to say the shop order was created on 3/8/10 and the cost associated for this part number is relative to this date.

The shop order date is created on 3/8/10 which falls between the 1st and 2nd record on the cost table shown above. in this case i would need the cost from the 1st entry.

[Former Member · Accepted solution]

Matthew,

This is only how I would do it... Not necessarily the ONLY way to do it...

It base all of my reports on SQL Commands anyway and I like to have my record selection done before I even open CR. So my solution would be to handle in the SQL itself, using a couple of nested sub-queries. Something like this...

SELECT
o.OrderID,
o.ItemID,
i.ItemName,
o.OrderDate,
(SELECT h.Price 
	FROM PriceHishory AS h
	WHERE h.ItemID = o.ItemID
	AND h.HistoryID IN (
		SELECT MAX(HistoryID)
		FROM PriceHistory
		WHERE PriceDate <= o.OrderDate
		GROUP BY ItemID)) AS Price

FROM Orders AS o
INNER JOIN ItemInfo AS i ON o.ItemID = i.ItemID

Essentially what this is doing, working from the inside out...

The innermost sub-query in looking for the maximum "HistoryID" for a given "ItemID" who's "PriceDate" is <= the "OrderDate".

This assumes that "HistoryID" is an auto-incrimenting, primary key that increases it's value as each new record is added...

The sub-query surrounding that is used to find the corresponding "price" that matches the the returned "HistoryID" and is used used as the "Price" field in the outer query.

HTH,

Jason

[Former Member · Accepted solution]

Matthew, Jason's solution will be much more elegant than mine, and, since the report will be based on a command, it will also process quicker.

[Former Member · Accepted solution]

This is a little over my head, so let me ask a few questions that might be silly. Im a bit of a rookie with SQL/Crystal. im a decent VB programmer though and have a good understanding how SQL works, just dont know all the functions all that well. The date the shop order was created is a single date field. when i tell it what shop order it is, i get one date. when i ask the other table for the different cost values, i get multiple records. These multiple records contain different dates, but all the dates are in the same column. i dont know if this routine below includes that, and i dont know how to say find the cost where the shop order date is greater that Column1DateA and less then Column1DateB, if shop order date does not fall in that date range continue to the next record and check if shop order date is greater then Column1DateB and less then Column1DateC

SELECT

o.OrderID, why is there an "o." before the OrderId

o.ItemID, Same for the rest of these, why is there a letter then a period (fieldname)

i.ItemName,

o.OrderDate,

(SELECT h.Price h.price, not sure what variable this is. it looks to me like 'h' is defined in the FROM statement

FROM PriceHishory AS h

WHERE h.ItemID = o.ItemID

AND h.HistoryID IN (

SELECT MAX(HistoryID)

FROM PriceHistory

WHERE PriceDate <= o.OrderDate

GROUP BY ItemID)) AS Price

FROM Orders AS o

INNER JOIN ItemInfo AS i ON o.ItemID = i.ItemID

Hope this reply makes sense.. its a little confusing

[Former Member · Accepted solution]

Well you'll be happy to know that SQL is MUCH easier to learn than VB and is fairly intuitive once you get your head wrapped around the basics.

The 1st thing... You issue in a nutshell is that each item on an order form can have multiple prices associated with it... each price having a unique date indicating when it became the current price for that item.... So the real task is to "pick the right one" based on the dates in the date column and the order date.

So to begin... You don't want anything with a date greater than the order date... That's easy enough...

SELECT
h.ProductID,
h.Price
FROM PriceHistory AS h
WHERE h.PriceDate <= o.OrderDate

The problem is that there could still be several date/price rows that still exist (ie: the price had already changed several times before the order)..... Soooo you now want the must recent date/price for the ordered product... and that where the sub-query comes in

SELECT 
p.ProductID,
p.Price
FROM PriceHistory AS p
WHERE PriceHistoryID IN (
	SELECT MAX(p2.PriceHistoryID)
	FROM PriceHistoryID AS p2
	WHERE p2.PriceDate <= o.OrderDate
	AND p2.ProductID = o.ProductID

The use of the "PriceHistoryID" if available, help to make the coding a little easier... Like I said before, it assumes an auto-incrementing primary key who's value increases each time a value is added... Therefore, the higher the "PriceHistoryID" the more recent the date. So in this instance... If we can find the greatest "PriceHistoryID" with a date <= the "OrderDate" we can get the price selected down to a single value.

If a "PriceHistoryID" type of field is not available, it still doable but it's not very pretty.

As far as

(SELECT h.Price h.price, not sure what variable this is.
 it looks to me like 'h' is defined in the FROM statement

You have VB on the brain... "h" isn't a variable at all. It's an alias. Alias's are just a simple way to keep you from having to type long table names over and over again.

SELECT
VeryLongTableName.Field1,
VeryLongTableName.Field2,
VeryLongTableName.Field3
FROM VeryLongTableName

is the same as

SELECT
v.Field1,
v.Field2,
v.Field3
FROM VeryLongTableName AS z

If you still need help and you're willing to supply the following info, I should be able to get you a little closer...

1) Database type and version (aka MS SQL Server 2005)

2) The actual names of the tables and the fields needed from those tables

3) Does the "Price History" table have a unique primary key as described above... if so what is it's name?

HTH,

Jason

[Former Member · Accepted solution]

Not to worry Matthew, commands, are just a little more advanced data connection than you may be used to.

If you were to run your report, and then look at the SQL, you could create a new data connection, select

add command, and paste the SQL statement into the window, ( Now sometimes you will need to add some

things, but anyway, once you add the command, you can change your existing data source over to the command.

Commands in general are quicker than regular ODBC connections, because most of the processing is done on the

DB server. "Which in most cases is quicker than using your desktop"

The o.OrderID or I.Itemname, are just place holders, for whatever your DB fields are called.

so, instead of h.price, you might put Invoice_Total, or whatever your DB field calls that item.

When you put the date in, your parameter is returning just that date "Which is why you only get one record", in my example, it returns the param date + any dates younger than that. I then take that result to the Sub-report, as part of the date selection range,

along with the max date from the list. This gives us a range to work from.

In effect, giving us a blend of just searching by date, or amount.

The select statement that was provided will do all this work for you. But, if you are not familiar with commands,

it may be a little hard. You could run a sample report, then look at the SQL behind it, this should give

you the correct field names. If you can publish a sample of your SQL, either one of us can re-write the command

for you. (Assuming it is obvious which field names are which)

HTH

[Former Member · Accepted solution]

Ok, you guys are great.. let me give this a shot and see what happens. ill post something back once i get something in place.

Would it be a better idea to do this as a separate report and then link it in as a subreport?

[Former Member · Accepted solution]

There shouldn't be any reason to get sub-reports involved... at least not based on anything discussed so far in this thread.

[Former Member · Accepted solution]

Jason

Thanks for your help. Im working on it and here's what i got back for you. The second sql statement seems like the one that im looking for i cant get it to work for me. Probably has something to do with my lack of sql knowledge. When writing this, do i write it as a SQL expression in crystal XI?

The Oracle Db is 10g and for your second question ive filled in the sql statement you wrote with the verylongtablenames. This should show all the tables and fields im looking for.

I understand what you said about alias's, however some of these fields are from different tables. so how do i assign multiple alias's to multiple tables. I get how to assign alias's to single tables, is it even possible to do it to multiple tables at a time? You should see what i mean in the sql statement below.

SELECT

p."SHOP_ORD\"."PART_NO",

p."PART_COST_BUCKET_HISTORY\"."BUCKET_ACCUM_COST"

FROM PART_COST_BUCKET_HISTORY,SHOP_ORD AS p

WHERE "PART_COST_BUCKET_HISTORY\"."HISTORY_SEQ_NO" IN (

SELECT MAX(p2."PART_COST_BUCKET_HISTORY\"."HISTORY_SEQ_NO")

FROM "PART_COST_BUCKET_HISTORY\"."HISTORY_SEQ_NO" AS p2

WHERE p2."PART_COST_BUCKET_HISTORY\"."CALCULATION_DATE" <= "SHOP_ORD\"."REVISED_START_DATE"

AND p2."PART_COST_BUCKET_HISTORY\"."PART_NO" = "SHOP_ORD\"."PART_NO"

[Former Member · Accepted solution]

i might be totally off with that statement, im not sure

[Former Member · Accepted solution]

Jason,

I tried a basic SQL query to see if i could get alias's to work. This should have returned me the two fields (part_no and bucket_accum_cost), instead it just error'd

SELECT

h.part_no,

h.bucket_accum_cost

FROM PriceHistory AS h

I tried this in PL/SQL developer and it tells me

ORA-00933: SQL command not properly ended

I looked it up, turns out to alias in Oracle the alias name must immediately follow the table name without AS keyword.

SELECT

h.part_no,

h.bucket_accum_cost

FROM PriceHistory h

This worked for aliasing

Matt

[Former Member · Accepted solution]

#1 No... This will not be a SQL Expression. It will be a Command. To learn more about Commands in general, including hot to add one, search for Defining an SQL Command in CR's Online Help.

#2 Aliases can be used in several different ways, them table alias and field alias are the most common... Here's an example to add both:

SELECT
a.Field1,
b.Field1 AS OtherField,  -- Field alias to distinguish between 2 fields, from 2 different tables that have the same name
c.Fielld3
FROM FirstTable AS a  -- Table alias used to abbreviate a long table name
INNER JOIN SeconfTable AS b ON a.Field1 = b.Field1
LEFT OUTER JOIN ThirdTable AS c ON b.Field3 = c.Field3

Aliases can also be helpful hen you need to add the same table more than once to the same query

SELECT 
c.ContractNumber,
p.FullName,
x.FieldValue AS Color,
y.FieldValue AS Make,
z.FieldValue AS Model
FROM tblContracts AS c
INNER JOIN tblPurchaser AS p ON c.PurchaserID = p.PurchaserID
LEFT OUTER JOIN tblAttributes AS x ON c.VehicleID = x.VehicleID 
     AND x.AttributeTypeID = 10015 -- indicating a Color attribute
LEFT OUTER JOIN tblAttributes AS y ON c.VehicleID = y.VehicleID 
     AND y.AttributeTypeID = 10025 -- indicating a Make attribute
LEFT OUTER JOIN tblAttributes AS z ON c.VehicleID = z.VehicleID 
     AND z.AttributeTypeID = 10037 -- indicating a Model attribute
WHERE (place selection criteria here)

#3 Oh Oracle developers and their love of ALL CAPS and their '_'s... yuk.

This should get you a little closer...

SELECT 
s."PART_NO",
s."REVISED_START_DATE",
(SELECT p."BUCKET_ACCUM_COST"
	FROM "PART_COST_BUCKET_HISTORY\" AS p
	WHERE p."HISTORY_SEQ_NO" IN (
		SELECT p2."HISTORY_SEQ_NO"
		FROM "PART_COST_BUCKET_HISTORY\" AS p2
		WHERE p2."CALCULATION_DATE" <= s."REVISED_START_DATE"
		AND p2."PART_NO" = s."PART_NO")) AS "BUCKET_ACCUM_COST"
FROM "SHOP_ORD" AS s

HTH,

Jason

[Former Member · Accepted solution]

Ooops sorry... I'm more of a SQL Server kinda guy and I not currently working with any Oracle DB's at all so I'm not too familiar with the syntax. I like to use the AS because it makes it easier to find the aliases but they are not necessary in SQL Server either.

So... without the the "AS"s

SELECT 
s."PART_NO",
s."REVISED_START_DATE",
(SELECT p."BUCKET_ACCUM_COST"
	FROM "PART_COST_BUCKET_HISTORY\" p
	WHERE p."HISTORY_SEQ_NO" IN (
		SELECT p2."HISTORY_SEQ_NO"
		FROM "PART_COST_BUCKET_HISTORY\" p2
		WHERE p2."CALCULATION_DATE" <= s."REVISED_START_DATE"
		AND p2."PART_NO" = s."PART_NO")) "BUCKET_ACCUM_COST"
FROM "SHOP_ORD" s;

I also added a ";" at the end of the statement. It's usually considered a good practice to end a SQL statements with a ; but not always required ... so I tend to leave them out.

HTH,

Jason

[Former Member · Accepted solution]

The more i work with Oracle, the less im liking it. I dont like the all caps and the "_"s either. also dont like how the 'AS' command doesnt work. When im referencing SQL syntax and the standards have things like AS that dont work, its maddening.

Ive created this SQL as a command, thats easy and i know how to do that. Reading that last post you had without the AS, looks like it should work. It makes total sense to me reading it. However Crystal tells me

Failed to retrieve data from the database. Details: ORA-00911: invalid character [Database Vendor Code: 911]

Hit OK then,

Failed to retrieve data from the database.

Details: Fail to execute SQL statement. OCI Call: OCIStmExecute [Database Vendor Code:911]

if i take the semi-colon at the end out, it gives me a similar but different error:

Failed to retrieve data from the database.

Details: ORA-00942: table or view does not exist

[Database Vendor Code: 942]

Which the tables called in the from clauses PART_COST_BUCKET_HISTORY and SHOP_ORD is the correct tables or view names in Oracle.

[Former Member · Accepted solution]

Like I said I'm not an Oracle guy...

Try executing the SQL in the PL/SQL developer. Any errors should be more descriptive and it should be easier to debug.

[Former Member · Accepted solution]

So i re-typed the code into PL/SQL and Oracle insisted on caps for everything. Finally got it so it runs without syntax errors and now its returning this:

ORA-10427: single-row subquery returns more then one row.

looking this up now

[Former Member · Accepted solution]

The error is meaning one of 2 things... The sub-query has the potential to return multiple rows or that the sub-query is trying to return multiple rows.

I don't think PL-SQL has a TOP function equivalent to SQL Server so you you should be able to correct the error by adding a bit more...

So something along these lines...

SELECT 
s."PART_NO",
s."REVISED_START_DATE",
(SELECT "BUCKET_ACCUM_COST"
	FROM (
		SELECT p."BUCKET_ACCUM_COST"
		FROM "PART_COST_BUCKET_HISTORY\" p
		WHERE p."HISTORY_SEQ_NO" IN (
			SELECT p2."HISTORY_SEQ_NO"
			FROM "PART_COST_BUCKET_HISTORY\" p2
			WHERE p2."CALCULATION_DATE" <= s."REVISED_START_DATE"
			AND p2."PART_NO" = s."PART_NO"))
	WHERE rownum = 1
	ORDER BY rownum) "BUCKET_ACCUM_COST"
FROM "SHOP_ORD" s;

See if that helps any...

Jason

[Former Member · Accepted solution]

Now i feel like a total rookie.. when i run it , it tells me im missing a right parenthesis. ive gone over it like 10 times and it looks like they're all there in place. PL/SQL even highlights the open/close parenthesis so its easy to find. it says im missing a right parenthesis before the "ORDER BY" command at the end

SELECT

s."PART_NO",

s."REVISED_START_DATE",

(SELECT "BUCKET_ACCUM_COST"

FROM (

SELECT p."BUCKET_ACCUM_COST"

FROM "PART_COST_BUCKET_HISTORY\" p

WHERE p."HISTORY_SEQ_NO" IN (

SELECT p2."HISTORY_SEQ_NO"

FROM "PART_COST_BUCKET_HISTORY\" p2

WHERE p2."CALCULATION_DATE" <= s."REVISED_START_DATE"

AND p2."PART_NO" = s."PART_NO"))

WHERE rownum = 1

missing here----

---

> ORDER BY rownum) "BUCKET_ACCUM_COST"

FROM "SHOP_ORD" s;

[Former Member · Accepted solution]

All of the parenthesis are there... At least in terms of the open/close ones. Check the "rownumber" syntax, I may not have it 100% correct.

You may even be able to the "ORDER BY rownum" altogether. the few samples I looked at it had it, so I went with it.