Getting the correct records

rstelma · May 6th, 2009, 09:05 PM

Hi,

Thanks for your time.

Products that have been discontinued have a TransID of 238 in the ProductHistory table.

Products that are back ordered have a TransID of 237.

At all times I need to eliminate the products from the query that have the latest entry in the ProductHistory table with a TransID of 238. That line is included in the query.

But my problem is with the products that have been back ordered. I do not want any back ordered products returned when the latest entry in the history table is more than six months old.

The query that I have now should only return the 237 row if the query date is within six months of the ChangeDate.

CREATE TABLE [Inv] (
[InvID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Product] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

INSERT INTO Inv
SELECT 'Chains'
UNION ALL
SELECT 'Cuffs'
UNION ALL
SELECT 'Leggings'
UNION ALL
SELECT 'Ropes'
UNION ALL
SELECT 'Thumb Screws'

CREATE TABLE [ProductHistory] (
[InvID] [int] NULL ,
[ProductHistID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ChangeDate] [datetime] NOT NULL ,
[TransID] [int] NOT NULL ,
CONSTRAINT [PK_ProductHistory] PRIMARY KEY CLUSTERED
(
[ProductHistID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO ProductHistory
SELECT 1, '10/4/2005', 22
UNION ALL
SELECT 1, '4/23/2008', 238
UNION ALL
SELECT 2, '8/8/2008', 22
UNION ALL
SELECT 3, '9/22/2004', 22
UNION ALL
SELECT 3, '4/23/2005', 237
UNION ALL
SELECT 4, '6/27/2007', 22
UNION ALL
SELECT 5, '10/4/2008', 22

SELECT DISTINCT Product, (SELECT TOP 1 ChangeDate FROM ProductHistory WHERE Inv.InvID = ProductHistory.InvID ORDER BY ChangeDate DESC)AS ChangeDate,
(SELECT TOP 1 TransID FROM ProductHistory WHERE Inv.InvID = ProductHistory.InvID ORDER BY ChangeDate DESC) AS 'TransID'
FROM Inv INNER JOIN ProductHistory ON Inv.InvID = ProductHistory.InvID
WHERE (SELECT TOP 1 TransID FROM ProductHistory WHERE Inv.InvID = ProductHistory.InvID ORDER BY ChangeDate DESC) <> 238
--AND (SELECT TOP 1 TransID FROM ProductHistory WHERE Inv.InvID = ProductHistory.InvID ORDER BY ChangeDate DESC) <> 237

Thanks for your help.

Richard

Old Pedant · May 7th, 2009, 04:14 PM

Question: Would you ever see a product record with a backorder date a year ago and then a more recent ordinary record (say TransID = 22)??

Or maybe, to put it another way, which takes precedence: The most recent record for a given product or the most recent backorder (237) or discontinued (238) record?

Example: Which of these records prevails:

INSERT INTO ProductHistory
SELECT 3, '3/22/2009', 22
UNION ALL
SELECT 3, '4/23/2005', 237

rstelma · May 7th, 2009, 05:01 PM

Yes, that could happen. An item can come off of back order. So the latest date takes precedence and the previous237 would be completely disregarded.

That's why I'm doing the SELECT TOP 1 TransID

I could totally eliminate the 237 TransID's except that I need to show them if the query date is within the six months after the product went back ordered. That is where I'm stuck.

There is also a need for historical reporting so I won't be able to use getDate(). I'll have to supply a date.

I tried this in the WHERE statement:

AND (SELECT TOP 1 TransID FROM ProductHistory WHERE Inv.InvID = ProductHistory.InvID ORDER BY ChangeDate DESC) = 237

But this only returns the 237 record if I query within the six months after the ChangeDate and none of the other records.

Thanks so much for looking at this. Very much appreciated.

Richard

rstelma · May 7th, 2009, 05:03 PM

Oooops!

The WHERE statement that I tried is:

AND (SELECT TOP 1 TransID FROM ProductHistory WHERE Inv.InvID = ProductHistory.InvID ORDER BY ChangeDate DESC) = 237 AND (SELECT TOP 1 ChangeDate FROM ProductHistory WHERE Inv.InvID = ProductHistory.InvID ORDER BY ChangeDate DESC) >= DATEADD(mm,-6, '9/1/2007')

Old Pedant · May 7th, 2009, 07:46 PM

Last two questions (I think):

(1) Can a product ever come back off of discontinued status???

So if the data had been

INSERT INTO ProductHistory
SELECT 3, '3/22/2009', 22
UNION ALL
SELECT 3, '4/23/2005', 238

would that 238 *also* be ignored?

I'm going to assume so while I think on this. If not, let me know.

(2) So if we are supposed to ignore a 237 record that is more than 6 months old, does that mean we go back to a prior record for that same ID and use *it*??

INSERT INTO ProductHistory
SELECT 3, '3/22/2007', 22
UNION ALL
SELECT 3, '4/23/2008', 237

Here, the 237 record is more than 6 months old, so do we pull the 22 record for the same product, instead? Or does this truly "kill off" product 3 from the report? [I would assume the latter.]

EHHH...reading your original post again, I think you've already answered this.

Once more: it's STRICTLY the LATEST RECORD for each product that prevails. Period.

Right?

Old Pedant · May 7th, 2009, 08:11 PM

I *think* this works:

Code:

SELECT Inv.Product, PH.TransID, PH.ChangeDate
FROM Inv, 
     ProductHistory AS PH,
     ( SELECT InvID, MAX(ChangeDate) AS LastChange
       FROM ProductHistory
       GROUP BY InvID ) AS Latest
WHERE Inv.InvID = PH.InvID
  AND PH.InvID = Latest.InvID
  AND PH.ChangeDate = Latest.LastChange
  AND (    PH.TransID NOT IN ( 237, 238 )
        OR ( PH.TransID = 237 AND Latest.LastChange > DATEADD(m,-6,getDate()) )
      )
ORDER BY Inv.Product

The inner SELECT is used to get one record per InvID; in particular, it will have the InvID and the very latest ChangeDate for that InvID.

So then we join that back to the entire ProductHistory table, matching on both InvID and ChangeDate. That results in us getting a single record *per InvID* from the ProductHistory table. (At the same time, we join to the Inv table to get the product name, of course.)

Then we filter the records:
-- All records that have a TransID of *OTHER* than 237 or 238 are automatically "in."
-- Any records with a TransID of 237 *and* where the LastChange is later than 6 months ago are also "in".

Doesn't that do it?

Notice the careful use of parentheses in that last part of the WHERE.
The two sub-conditions *must* be "OR"ed together before being "AND"ed with the other conditions.

rstelma · May 7th, 2009, 08:30 PM

Hi,
(1)
No. Once a product is discontinued it is gone so we would not ever have a new entry with a TransID of 22. Once it has a 238 it is gone forever. However, the query should return that record if the query date is before the product went discontinued.

The 238 would be completely ignored for for every query date after 4/23/2005. There are controls in the application that prevent any further entries to a product once it has hit 238.

A date prior to 4/23/2008 would pull that product.

(2)
No, you wouldn't go back to a prior recrod. It's always the latest record based on the date fed to the query.

Unless another entry was added for the product and it's six months after the TransID change then it would kill off that product.

It's strictly based on the LATEST RECORD for each product that prevails.

This is a real brain twister isn't it.

I was thinking to create a stored procedure with a temp table. Put in all of the records that are not 237 or 238. And then write a second query with just the applicable 237 records. And just SELECT * FROM #temptable.

I have tried to UNION the two queries and I got an error stating that I could not use ORDER BY when using UNION and that killed it since I have to order the records to make sure I get the latest.

I just thought there might be some way to do this with a query.

Thanks man. I really appreciate your help with this.

Richard

rstelma · May 7th, 2009, 09:04 PM

It's pretty close. It works fine for getDate().

It doesn't work for 1/1/2007 since it's not pulling Chains which has a TransID of 22 with a date of 10/4/2005. And 1/1/2007 is before the 238 entry on 4/23/2008.

I like what you're doing and wish I understood it better.

How do the parens around the last part of the WHERE change that statement so much? I took them out and the query returned 28 rows.

PH.TransID NOT IN ( 237, 238 ) says don't get any of these

OR

if any of these are there get them
( PH.TransID = 237 AND Latest.LastChange > DATEADD(m,-6,'10/6/2005') )

I just don't see why the first part doesn't just eliminate them all

Anyway, really appreciate your input.

Thanks,
Richard

Old Pedant · May 8th, 2009, 01:39 AM

What is the value of

Code:

      3 + 4 * 3 * 2

???

If you said 42, go back to elementary school.

Multiplication has a higher PRECENDENCE than addition. So that expression should be evaluated JUST AS IF it was written

Code:

    3 + ( 4 * 3 * 2 )

and the answer is 27.

SAME THING with the AND and OR operators in logical expressions!

AND has a higher precedence.

SO *WITHOUT* any parens, that WHERE clause *automatically* becomes

Code:

WHERE (     Inv.InvID = PH.InvID
        AND PH.InvID = Latest.InvID
        AND PH.ChangeDate = Latest.LastChange
        AND PH.TransID NOT IN ( 237, 238 )
      )
      OR 
      (     PH.TransID = 237
        AND Latest.LastChange > DATEADD(m,-6,getDate())
      }

Meaning that ANY record with a transID of 237 within last 6 months will be accepted! Without regard to LatestChange or match on InvID!!

Now go look at how I coded it and you can see what a huge difference the parens make.

Old Pedant · May 8th, 2009, 01:47 AM

So no place in your first post did you have any DATE explicitly given, so I had no idea this code had to work for dates other than today.

But that's easy enough to fix.

We just have to put limits on how "new" the records can be in order to get results that are "as of" a date in the past.

Code:

SELECT Inv.Product, PH.TransID, PH.ChangeDate
FROM Inv, 
     ProductHistory AS PH,
     ( SELECT InvID, MAX(ChangeDate) AS LastChange
       FROM ProductHistory
       WHERE ChangeDate < @ASOFDATE
       GROUP BY InvID ) AS Latest
WHERE Inv.InvID = PH.InvID
  AND PH.InvID = Latest.InvID
  AND PH.ChangeDate = Latest.LastChange
  AND (    PH.TransID NOT IN ( 237, 238 )
        OR ( PH.TransID = 237 AND Latest.LastChange > DATEADD(m,-6,@ASOFDATE) )
      )
ORDER BY Inv.Product

[/code]
Untested, but feels right:

Now the MAX(ChangeDate) will be the last date for each InvID that is *before* the @ASOFDATE (which you will pass in to this query).

And the "in the last 6 months" now means "in the 6 months before the @ASOFDATE.

Looks to me like that works.

Turn this into a Stored Proc and then test it:

Code:

CREATE PROCEDURE GetActiveProducts
    @ASOFDATE DATETIME = GetDate()
AS 
SELECT Inv.Product, PH.TransID, PH.ChangeDate
FROM Inv, 
     ProductHistory AS PH,
     ( SELECT InvID, MAX(ChangeDate) AS LastChange
       FROM ProductHistory
       WHERE ChangeDate < @ASOFDATE
       GROUP BY InvID ) AS Latest
WHERE Inv.InvID = PH.InvID
  AND PH.InvID = Latest.InvID
  AND PH.ChangeDate = Latest.LastChange
  AND (    PH.TransID NOT IN ( 237, 238 )
        OR ( PH.TransID = 237 AND Latest.LastChange > DATEADD(m,-6,@ASOFDATE) )
      )
ORDER BY Inv.Product

[/code]
Now, if you omit the @ASOFDATE parameter to the SP, it will use GetDate(). But pass in a date, such as

Code:

EXEC GetActiveProducts( '1/1/2007' )

and see if it works that way, as well.