Finding current and prior date

Question

Post reply

Finding current and prior date

Shaira

SSCrazy

Points: 2432
More actions
November 19, 2010 at 9:24 am

#229440

I receive a list of parts daily with price updates. In the file I get a part number, effective date and the new price. This data is appended to a table of all the existing parts.
I want to write a query for a report that captures all the parts, their most recent price and effective date, however I also want the price and effective date that was prior to the latest data.
The first part was easy –
SELECT DISTINCT Part, Price/UnitofMeasure IndvPric, MAX(EffectiveDate) EffectiveDate
FROM dbo.PartTable
GROUP BY Part, Price, UnitOfMeasure
But since my updates are random as prices increase, I don’t know how to capture the second MAX effective date since they’re all over the board. I have tried a subquery stating where my max date in query 1 <> to max date in query 2, but some of my parts have been updated many times so I get back as many rows as updates.
Hope this makes sense ~ thanks for any suggestions you may have!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Try something like this - without data to try it with there may be errors

;with cte as (

SELECT

Part,

Price/UnitofMeasure IndvPric,

EffectiveDate,

ROW_NUMBER() OVER(PARTITION BY Part ORDER BY EffectiveDate DESC) as Position

FROM dbo.PartTable

)

select Part,

MAX(CASE WHEN Position = 1 THEN Price/UnitofMeasure ELSE 0 END) As LatestIndvPric,

MAX(CASE WHEN Position = 1 THEN EffectiveDate ELSE 0 END) As LatestEffectiveDate,

MAX(CASE WHEN Position = 2 THEN Price/UnitofMeasure ELSE 0 END) As PreviousIndvPric,

MAX(CASE WHEN Position = 2 THEN EffectiveDate ELSE 0 END) As LatestEffectiveDate

from cte

where Position <= 2

group by Part

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Shaira SSCrazy Points: 2432 More actions · Answer 2

Absolutely sweeeet!! 🙂 Thank you!!

I needed to make just a couple changes –

1. I had to convert my Effective Date to a datetime as I was getting an error - "operand type clash int is incompatible with date" (it is a DATE data type in the table)

2. I had to remove the calculation in the top portion of the script

3. Finally, I needed to add a group by, I’m thinking because we have duplicates in the table

Thank you so much for your expertise and quick response!!

Now I’m off to research PARTITION – I have never used it before, but I’m gonna learn!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

shairalt (11/19/2010)
Absolutely sweeeet!! 🙂 Thank you!!
I needed to make just a couple changes –
1. I had to convert my Effective Date to a datetime as I was getting an error - "operand type clash int is incompatible with date" (it is a DATE data type in the table)
2. I had to remove the calculation in the top portion of the script
3. Finally, I needed to add a group by, I’m thinking because we have duplicates in the table
Thank you so much for your expertise and quick response!!
Now I’m off to research PARTITION – I have never used it before, but I’m gonna learn!

You are welcome. It's good to know that you are going to research the subject more - it does seem with some people that all they want is an answer whereas I would prefer to help them reach it by learning themselves - just as I am doing...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

CELKO (11/19/2010)
I am also confused by your price, individual price and unit of measure columns. To me a unit of measure is meters, liters, grams, etc,

In this case, I believe unit of measure is due to dozen/gross/# in a box sales. You price things on a per sellable/buyable unit (box, gross, etc), but for costing purposes you do it on a per unit basis. It's marketing's usability vs. data consistency.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

CELKO (11/19/2010)
SELECT P1.part_nbr, P1.part_price AS current_part_price,
P1.price_effective_date AS current_price_effective_date,
CAST (NULL AS DATE)) AS price_current_date,
P2.part_price AS prior_part_price,
P2.price_effective_date AS prior_price_effective_date,
P2.price_terminal_date AS prior_price_terminal_date
FROM PartsPriceList AS P1, PartsPriceList AS P2
WHERE P1.price_terminal_date IS NULL
AND P1.price_effective_date = DATEADD (DD, 1, P2.price_terminal_date)
Things are much easier with good DDL.

BWAA-HAAA!!!! I NEVER want to hear you bitch about non-ANSI code ever again! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)