July 28, 2014 at 1:40 pm
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.
July 28, 2014 at 2:15 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2014 at 2:27 pm
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?
July 28, 2014 at 3:17 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2014 at 3:19 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2014 at 3:24 pm
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.
July 28, 2014 at 3:36 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 28, 2014 at 3:46 pm
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
ONf.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.
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
July 28, 2014 at 3:56 pm
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.
July 28, 2014 at 4:03 pm
Auto, you need to (Partition by item order by changedate) for that to work.
Edit: see below, sorry.
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
July 28, 2014 at 4:14 pm
Even with the...
AND CTE1.item = CTE2.item
...in the join?
It seemed to work okay. Did I screw something up?
July 28, 2014 at 4:29 pm
autoexcrement (7/28/2014)
Even with the...
AND CTE1.item = CTE2.item...in the join?
It seemed to work okay. Did I screw something up?
Hrm, good point, my apologies. I read it too quickly. Habit on my part to make sure I don't screw up the partitioning versus the ordering, but it works both ways.
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
July 28, 2014 at 5:20 pm
And another clarifying question:
Can a value change twice in a day such that there will be 2 values of an item for a given day? If so, how will you know which one to return?
July 29, 2014 at 12:58 am
Thanks to everyone for your responses. Craig has the answer 'cos I'm used to using calendar tables. But I do like Autos cte as well - it's self-contained.
I appreciate your efforts and I will endeavour in future posts to make my questions un-ambiguous and as clear as possible.
You've made my day easier so I wish you all a good day too.
July 29, 2014 at 7:04 am
I see that several others jumped in and found you a working solution. Glad you were able to get it working and thanks to the others for jumping in. 😀
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply