Query Help - History Table with no range data.

Question

Query Help - History Table with no range data.

simplemind

Valued Member

Points: 64
More actions
March 13, 2020 at 1:55 pm

#3734062

I am trying to join some history table data to a sales interface.
I need to return value @time.
does anyone have a good solution to this?
I apologize if this is simple for some.
My issue is I need to compare the sale date and return values where saledate is the max, but less than update date where name key in left table =namekey in right table. I cant figure out how to do this by row and not at the table level.
Again, apologies if this is something simple.
Any other approach would be welcome as well.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76998 More actions · Answer 1

A picture of your data is worthless. You need to supply actual data by supplying a script using the {;} Insert/edit code sample to do the following.

Create a temp tables (or declare a table variables) for your sample data. This allows for easier cleanup.
Insert data into said tables.
Create a temp table for the expected results
Insert data into said table.

That being said, the easiest approach is to use a CROSS APPLY with a TOP(1) ordered by [Update Date].

Drew

PS: It is bad practice to have column names with spaces in it.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Neil Burton SSC-Insane Points: 23014 More actions · Answer 2

Without wanting to pile-on, there's no 31st of September either and there's a Sale Date in your Desired Results picture that's not in Table 1.

You'll generally find most people around here are pretty willing to help. Few will judge a newcomer on how simple a query may be because it's not simple to everyone. What we do like is the sample data like Drew says and a willingness to learn.

That being said, you can have this as freebie because it's Friday. The mistakes are still there but you should get the idea about how to post your samples.

CREATE TABLE #Current
(
NameCHAR(2)
,NameKeyINT
,SaleDateDATE
,SaleQuantityTINYINT
,CurrentCategoryTINYINT
);
CREATE TABLE #History
(
NameCHAR(2)
,NameKeyINT
,UpdateDateDATE
,ColumnNameCHAR(170)
,ValueTINYINT
);

INSERT INTO #Current
VALUES('g1',1001,'2008-01-01',1,1)
,('g1',1001,'2008-06-01',2,1)
,('g1',1001,'2008-09-01',4,1)
,('g2',1002,'2008-01-01',2,2)
,('g2',1002,'2008-06-01',3,2)
,('g2',1002,'2008-09-01',5,2)
,('g2',1002,'2008-09-31',4,2);

INSERT INTO #History
VALUES('g1',1001,'2008-03-01','CurrentCategory',3)
,('g1',1001,'2008-07-01','CurrentCategory',4)
,('g1',1001,'2008-10-01','CurrentCategory',5)
,('g2',1002,'2007-12-01','CurrentCategory',1)
,('g2',1002,'2008-03-01','CurrentCategory',2)
,('g2',1002,'2008-06-01','CurrentCategory',3);

This reply was modified 4 years, 10 months ago by Neil Burton.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

simplemind Valued Member Points: 64 More actions · Answer 3

Thank you, sorry for being an annoying noob moron, will try to do better in the future with well-formed requests. This is very helpful, I have been stuck for a day.

Neil Burton SSC-Insane Points: 23014 More actions · Answer 4

simplemind wrote:

Thank you, sorry for being an annoying noob moron, will try to do better in the future with well-formed requests. This is very helpful, I have been stuck for a day.

You're welcome and don't be so hard on yourself. It's not the noob morons that are annoying. The morons that have been around for years and are still asking the same questions, now they'll get on your nerves.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

drew.allen SSC Guru Points: 76998 More actions · Answer 5

Based on Neil's sample data, here is a first attempt at a query.

SELECT c.Name, c.NameKey, c.SaleDate, c.SaleQuantity, c.CurrentCategory, h.Value
FROM #Current c
OUTER APPLY
(
SELECT TOP(1) *
FROM #History h
WHERE h.NameKey = c.NameKey
AND h.UpdateDate <= c.SaleDate
ORDER BY h.UpdateDate DESC
) h;

I used OUTER APPLY instead of CROSS APPLY to handle cases where there wasn't enough history.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 6

I'm a bit confused by the first row of your posted desired result... Is the 4/1/2008 date for the SaleDate just a typo that should have been 1/1/2008 or am I missing something in your requirements?

--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)

simplemind Valued Member Points: 64 More actions · Answer 7

simplemind

Valued Member

Points: 64

March 16, 2020 at 5:04 pm

#3734796

It was a typo. Thank you!