January 3, 2012 at 7:56 am
I have a table similar to :
tbl_Report
MyValue int
DateAdded datetime
It has sequential dates and values but for some days there may be no date and value.
On some dates there may be multiple values.
For each day I want the minimum value and if no value exists take the last minimum value available.
e.g.
value dd/mm
12 01/01
32 03/01
14 03/01
15 05/01
04/01 has no value, and two exist for 03/01 the mimimum being 14. so the query would show :
value dd/mm
12 01/01
14 03/01
14 04/01
15 05/01
What is the best way to do this?
January 3, 2012 at 8:02 am
Your post raises more questions to those of us wanting to help that we could answer. It is unclear if your table has records for all the dates but the value column is null or if the base table does not contain those records. The approach is very different in these two scenarios.
If you can post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data we can come up with a solution.
_______________________________________________________________
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/
January 3, 2012 at 8:13 am
There isn't a real table yet, but there will be no nulls just missing dates in the range as in the example I gave.
January 3, 2012 at 8:14 am
Also I just noticed my example output is erroneous....
It should be
value dd/mm
12 01/01
12 02/01
14 03/01
14 04/01
15 05/01
January 3, 2012 at 8:22 am
daninmanchester (1/3/2012)
There isn't a real table yet, but there will be no nulls just missing dates in the range as in the example I gave.
Then I will assume you want a direction for solving and not any actual sql. 😛
Since it sounds like you will have dates missing from your base table that you want to include you will have to use a calendar table. There are several good articles on this site.
Here is on of them that should help you with an understanding of how to go about this type of problem.
http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]
_______________________________________________________________
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/
January 3, 2012 at 8:39 am
Great, a calendar table is a good idea.
So lets assume I've plugged the date gaps using a calendar table.
If I do something like this :
SELECT tbl_Calendar.Date, Min(tbl_Report.MyValue) as MyVal
FROM tbl_Calendar LEFT JOIN tbl_Report
ON tbl_Calendar.Date = tbl_Report.Date
GROUP BY tbl_Calendar.Date
tbl_Report.MyValue is going to return nulls because there are no records for some dates.
How do I plug the gap?
The example SQL is overly simplified but you get the idea.
January 3, 2012 at 9:30 am
daninmanchester (1/3/2012)
Great, a calendar table is a good idea.So lets assume I've plugged the date gaps using a calendar table.
If I do something like this :
SELECT tbl_Calendar.Date, Min(tbl_Report.MyValue) as MyVal
FROM tbl_Calendar LEFT JOIN tbl_Report
ON tbl_Calendar.Date = tbl_Report.Date
GROUP BY tbl_Calendar.Date
tbl_Report.MyValue is going to return nulls because there are no records for some dates.
How do I plug the gap?
The example SQL is overly simplified but you get the idea.
Pretty hard to write sql with nothing to look at but a case with a subquery should do it.
SELECT tbl_Calendar.Date, case when Min(tbl_Report.MyValue) is null then (select top 1 Min(r2.tbl_report) from tbl_Report r2 where r2.Date < tbl_Report.Date order by r2.Date desc) else Min(tbl_Report.MyValue) end as MyVal
FROM tbl_Calendar LEFT JOIN tbl_Report
ON tbl_Calendar.Date = tbl_Report.Date
GROUP BY tbl_Calendar.Date
That may not be exact because without tables I can't test it but it should be close.
_______________________________________________________________
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/
January 3, 2012 at 9:46 am
Thanks, that looks like it will do the job and is along the lines of what I had thought, but it seems expensive.
If you imagine 16000 header records and then for each day there will a history value stored in the table we have been discussing.
At the end of a year it would be 5840000 records (16000 * 365).
Assuming the 16000 doesn't increase (which it will) it will be at least 11680000 records by year two.
The storage overhead is likely to be small as they are numbers but the record numbers large.
Would and index covering the MinValue sub query column be sensible?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply