Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-SQL to get value on any given day Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 1:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 5, 2014 3:47 AM
Points: 56, Visits: 161
I have a table that records when a value changes. For example item = x, date = 2014-06-15, value = 0; item = y, date = 2014-06-16, value = 1; item = x, date = 2014-07-10, value = 1; item = y, date = 2014-07-12, value = 2 .... etc etc.

How can I find out what the value is for each item on any given date in any month?

Thanks in advance for supporting my weary brain cells.
Post #1597044
Posted Monday, July 28, 2014 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
OldCursor (7/28/2014)
I have a table that records when a value changes. For example item = x, date = 2014-06-15, value = 0; item = y, date = 2014-06-16, value = 1; item = x, date = 2014-07-10, value = 1; item = y, date = 2014-07-12, value = 2 .... etc etc.

How can I find out what the value is for each item on any given date in any month?

Thanks in advance for supporting my weary brain cells.


I suspect you have a trigger that does and EAV style of auditing. This is a serious PITA to deal with and what you are about to face is exactly why I try to steer people away from this approach.

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1597054
Posted Monday, July 28, 2014 2:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 5, 2014 3:47 AM
Points: 56, Visits: 161
Your reply is most unhelpful. I suspect I may stop using SSC in future if this is the sort of reply I get.

To make it clear try this:
Create table foo (item char(1), changedate datetime, value int)

insert foo (item, changedate, value)
values (x, '2014-06-15', 0)
insert foo (item, changedate, value)
values (y, '2014-06-16', 1)
insert foo (item, changedate, value)
values (x, '2014-07-10', 1)
insert foo (item, changedate, value)
values (y, '2014-07-15', 2)

expected return values
2014-06-15, x, 0
2014-06-16, x, 0
2014-06-16, y, 1
2014-06-17, y, 1
etc etc

Anything else required?
Post #1597058
Posted Monday, July 28, 2014 3:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
OldCursor (7/28/2014)
Your reply is most unhelpful. I suspect I may stop using SSC in future if this is the sort of reply I get.

To make it clear try this:
Create table foo (item char(1), changedate datetime, value int)

insert foo (item, changedate, value)
values (x, '2014-06-15', 0)
insert foo (item, changedate, value)
values (y, '2014-06-16', 1)
insert foo (item, changedate, value)
values (x, '2014-07-10', 1)
insert foo (item, changedate, value)
values (y, '2014-07-15', 2)

expected return values
2014-06-15, x, 0
2014-06-16, x, 0
2014-06-16, y, 1
2014-06-17, y, 1
etc etc

Anything else required?


How was my reply unhelpful? I was asking for clarification so I can help YOU with your problem. Can we now focus on the issue at hand?

Can you help me understand the desired results? The first row is easy enough. I don't understand the logic here for the remainder of the rows. Why is the second row 6/16 and the item is x? What is the business rule for the values column?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1597082
Posted Monday, July 28, 2014 3:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
Oh wait...I think I am beginning to see what you are trying to do here. You want to see what the value of x and y is for any given date. Do you have a range of dates or that sort of thing you want to use?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1597083
Posted Monday, July 28, 2014 3:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 5, 2014 3:47 AM
Points: 56, Visits: 161
Forgive me for being grumpy and I do appreciate your offer of help - really. But I thought my original post was clear. The table holds records for when a value changes. It is deemed to be the same value in between changes - and yes I want to identify the value of all items on all days. For the sake of argument lets say starting from 2014-06-01 to 2014-08-01 (using the records I've given you).

Any ideas?

btw it's not an audit table or any such thing.
Post #1597087
Posted Monday, July 28, 2014 3:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
OldCursor (7/28/2014)
Forgive me for being grumpy and I do appreciate your offer of help - really. But I thought my original post was clear. The table holds records for when a value changes. It is deemed to be the same value in between changes - and yes I want to identify the value of all items on all days. For the sake of argument lets say starting from 2014-06-01 to 2014-08-01 (using the records I've given you).

Any ideas?

btw it's not an audit table or any such thing.


I will forgive you for being grumpy if you will forgive me assuming. You know what they say about that.

The challenge is that is was crystal clear to you. Some others might find it clear too but I am a very visual person so a table makes it perfectly clear. This also helps because it is the first thing anybody needs to do in order to help write sql.

Yes, this is definitely a dooable query. It will require the use of a tally or numbers table. I am about to head out for the day. I will follow up on this first thing in the morning and help you with a solution if nobody else has come along before then.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1597091
Posted Monday, July 28, 2014 3:46 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
It's alright to be grumpy, just remember we're all volunteers too, and we're trying to make sure we don't waste your time either. A few clarifications cleans up a few dozen assumptions that can lead to blind alleys.

So, here's the basic plan when you're going into a logging table to find the most recent entries for a date. Find the max date in the table below your date for the key, then rejoin on the key. There are multiple approaches to this method, below is one of them:

IF OBJECT_ID( 'tempdb..#foo') IS NOT NULL
DROP TABLE #foo

Create table #foo (item char(1), changedate datetime, value int)

insert #foo (item, changedate, value)
values ('x', '2014-06-15', 0)
insert #foo (item, changedate, value)
values ('y', '2014-06-16', 1)
insert #foo (item, changedate, value)
values ('x', '2014-07-10', 1)
insert #foo (item, changedate, value)
values ('y', '2014-07-15', 2)

DECLARE @DateToGet DATETIME
SET @DateToGet = '20140709'

SELECt
*
FROM
#foo AS f
JOIN
(SELECT
item, MAX(changeDate) AS MaxCD
FROM
#foo
WHERE
ChangeDate <= @DateToGet
GROUP BY
item
) AS drv
ON f.item = drv.item
and f.ChangeDate = drv.MaxCD

If you need to do this for a range, use another table to contain your range, feed its date column in as a replacement for the @DateToGet variable.



- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1597099
Posted Monday, July 28, 2014 3:56 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 3:19 PM
Points: 157, Visits: 608
If I'm understanding correctly, here's what I'd do in this situation:

IF object_id('tempdb..#foo') IS NOT NULL
DROP TABLE #foo;

CREATE TABLE #foo (item CHAR(1), changedate DATETIME, value INT);

INSERT #foo (item, changedate, value)
VALUES ('x', '2014-06-15', 0);
INSERT #foo (item, changedate, value)
VALUES ('y', '2014-06-16', 1);
INSERT #foo (item, changedate, value)
VALUES ('x', '2014-07-10', 1);
INSERT #foo (item, changedate, value)
VALUES ('y', '2014-07-15', 2);
INSERT #foo (item, changedate, value)
VALUES ('x', '2014-08-10', 3);

WITH CTE AS
(
SELECT RN = ROW_NUMBER() OVER(ORDER BY item, changedate),
*
FROM #foo
)
SELECT
-- CTE1.RN,
CTE1.item,
CTE1.value,
CTE1.changedate AS startdate,
ISNULL(DATEADD(day, -1, CTE2.changedate), '9999-12-31') AS enddate
FROM CTE AS CTE1
LEFT OUTER JOIN CTE AS CTE2 ON
CTE1.RN + 1 = CTE2.RN
AND CTE1.item = CTE2.item
;

This way you have a start and end date range for each value of each product that you can query.



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1597104
Posted Monday, July 28, 2014 4:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
Auto, you need to (Partition by item order by changedate) for that to work.

Edit: see below, sorry.



- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1597107
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse