June 15, 2012 at 5:13 pm
Hi All,
I have a revision process where newer records supercede older records but the older records still exists.
My assumption is that the last record per day is valid.
So I would like to sum records over a period only including the most recent record for each day.
My expected result is 15.
CREATE TABLE #Test
(
DateTimeStamp datetime,
Value int
)
INSERT INTO #Test SELECT '2012-06-15 16:00:00.000',4
INSERT INTO #Test SELECT '2012-06-15 2:05:00.000',5
INSERT INTO #Test SELECT '2012-06-14 2:10:00.000',3
INSERT INTO #Test SELECT '2012-06-14 2:00:00.000',4
INSERT INTO #Test SELECT '2012-06-13 2:00:00.000',6
INSERT INTO #Test SELECT '2012-06-12 2:00:00.000',1
INSERT INTO #Test SELECT '2012-06-12 4:30:00.000',2
SELECT * FROM #Test ORDER BY DateTimeStamp
DROP TABLE #Test
June 15, 2012 at 5:36 pm
Like so? (btw, thanks for the easy to use data)
SELECT
SUM( Value) AS SumValue
FROM
(SELECT
--DATEADD( dd, DATEDIFF( dd, 0, DateTimeStamp), 0) AS DayOfRecord,
MAX( DateTimeStamp) AS MaxDT
FROM
#Test
GROUP BY
DATEADD( dd, DATEDIFF( dd, 0, DateTimeStamp), 0)
) AS drv
JOIN
#Test AS t
ONt.DateTimeStamp = MaxDT
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
June 17, 2012 at 7:05 pm
I believe that this will also produce satisfactory results:
;WITH CTE AS (
SELECT DateTimeStamp, Value
,n=ROW_NUMBER() OVER (
PARTITION BY DATEADD(day, DATEDIFF(day, 0, DateTimeStamp), 0)
ORDER BY DateTimeStamp DESC)
FROM #Test)
SELECT SUM(Value) AS Value
FROM CTE
WHERE n=1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 17, 2012 at 7:25 pm
CELKO (6/16/2012)
There is no need to write 1970's Sybase dialect any; use ANSI Standard syntax:
Unless, of course, you're creating code that someone who's working on older systems will be able to use so they can help you. Many people out there are still running 2k5 and it does no harm to be overly explicit for the widest audience for your problem.
Untested:
Then why bother when the OP handed you an easy to use test script?
WE can also user the lastT() OVER() function
Very untested then, this function is not available in either 2k5 or 2k8. While I know you're all about ANSI coding and against dialect, this dialect doesn't contain that. There is a Last_value in 2012, but wrong forum.
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
June 18, 2012 at 12:19 am
You can do it like this as well:
--Creating Temporary Table
CREATE TABLE #Test
(
DateTimeStamp datetime,
Value int
)
--Inserting Sample Data
INSERT INTO #Test SELECT '2012-06-15 16:00:00.000',4
INSERT INTO #Test SELECT '2012-06-15 2:05:00.000',5
INSERT INTO #Test SELECT '2012-06-14 2:10:00.000',3
INSERT INTO #Test SELECT '2012-06-14 2:00:00.000',4
INSERT INTO #Test SELECT '2012-06-13 2:00:00.000',6
INSERT INTO #Test SELECT '2012-06-12 2:00:00.000',1
INSERT INTO #Test SELECT '2012-06-12 4:30:00.000',2
--Query For Your Requirement
Select SUM(Value) As TotalValue From
(Select *, ROW_NUMBER() Over (Partition By DatePart(DD, DateTimeStamp) Order By DatePart(HH, DateTimeStamp) Desc ) As rn From #Test) As a
Where rn = 1
June 18, 2012 at 12:26 am
vinu512 (6/18/2012)
You can do it like this as well:
--Creating Temporary Table
CREATE TABLE #Test
(
DateTimeStamp datetime,
Value int
)
--Inserting Sample Data
INSERT INTO #Test SELECT '2012-06-15 16:00:00.000',4
INSERT INTO #Test SELECT '2012-06-15 2:05:00.000',5
INSERT INTO #Test SELECT '2012-06-14 2:10:00.000',3
INSERT INTO #Test SELECT '2012-06-14 2:00:00.000',4
INSERT INTO #Test SELECT '2012-06-13 2:00:00.000',6
INSERT INTO #Test SELECT '2012-06-12 2:00:00.000',1
INSERT INTO #Test SELECT '2012-06-12 4:30:00.000',2
--Query For Your Requirement
Select SUM(Value) As TotalValue From
(Select *, ROW_NUMBER() Over (Partition By DatePart(DD, DateTimeStamp) Order By DatePart(HH, DateTimeStamp) Desc ) As rn From #Test) As a
Where rn = 1
Are you sure this is going to work across different months (e.g., if you include a record for 2012-07-12)?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 18, 2012 at 12:43 am
dwain.c (6/18/2012)
vinu512 (6/18/2012)
You can do it like this as well:
--Creating Temporary Table
CREATE TABLE #Test
(
DateTimeStamp datetime,
Value int
)
--Inserting Sample Data
INSERT INTO #Test SELECT '2012-06-15 16:00:00.000',4
INSERT INTO #Test SELECT '2012-06-15 2:05:00.000',5
INSERT INTO #Test SELECT '2012-06-14 2:10:00.000',3
INSERT INTO #Test SELECT '2012-06-14 2:00:00.000',4
INSERT INTO #Test SELECT '2012-06-13 2:00:00.000',6
INSERT INTO #Test SELECT '2012-06-12 2:00:00.000',1
INSERT INTO #Test SELECT '2012-06-12 4:30:00.000',2
--Query For Your Requirement
Select SUM(Value) As TotalValue From
(Select *, ROW_NUMBER() Over (Partition By DatePart(DD, DateTimeStamp) Order By DatePart(HH, DateTimeStamp) Desc ) As rn From #Test) As a
Where rn = 1
Are you sure this is going to work across different months (e.g., if you include a record for 2012-07-12)?
Good catch Dwain.
It will not work in that situation. I'd have to alter the query as follows then:
Select SUM(Value) As TotalValue From
(Select *, ROW_NUMBER() Over (Partition By DatePart(YY, DateTimeStamp), DatePart(MM, DateTimeStamp), DatePart(DD, DateTimeStamp) Order By DatePart(HH, DateTimeStamp) Desc ) As rn From #Test) As a
Where rn = 1
This would work....Right?
June 18, 2012 at 12:57 am
vinu512 (6/18/2012)
dwain.c (6/18/2012)
vinu512 (6/18/2012)
You can do it like this as well:
--Creating Temporary Table
CREATE TABLE #Test
(
DateTimeStamp datetime,
Value int
)
--Inserting Sample Data
INSERT INTO #Test SELECT '2012-06-15 16:00:00.000',4
INSERT INTO #Test SELECT '2012-06-15 2:05:00.000',5
INSERT INTO #Test SELECT '2012-06-14 2:10:00.000',3
INSERT INTO #Test SELECT '2012-06-14 2:00:00.000',4
INSERT INTO #Test SELECT '2012-06-13 2:00:00.000',6
INSERT INTO #Test SELECT '2012-06-12 2:00:00.000',1
INSERT INTO #Test SELECT '2012-06-12 4:30:00.000',2
--Query For Your Requirement
Select SUM(Value) As TotalValue From
(Select *, ROW_NUMBER() Over (Partition By DatePart(DD, DateTimeStamp) Order By DatePart(HH, DateTimeStamp) Desc ) As rn From #Test) As a
Where rn = 1
Are you sure this is going to work across different months (e.g., if you include a record for 2012-07-12)?
Good catch Dwain.
It will not work in that situation. I'd have to alter the query as follows then:
Select SUM(Value) As TotalValue From
(Select *, ROW_NUMBER() Over (Partition By DatePart(YY, DateTimeStamp), DatePart(MM, DateTimeStamp), DatePart(DD, DateTimeStamp) Order By DatePart(HH, DateTimeStamp) Desc ) As rn From #Test) As a
Where rn = 1
This would work....Right?
I think you should also change:
Order By DatePart(HH, DateTimeStamp) Desc
To this:
Order By DateTimeStamp Desc
Just to be sure.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 18, 2012 at 2:13 pm
Thanks all. This gives me several ways to approach the problem.
June 18, 2012 at 6:12 pm
Chrissy321 (6/15/2012)
Hi All,I have a revision process where newer records supercede older records but the older records still exists.
My assumption is that the last record per day is valid.
So I would like to sum records over a period only including the most recent record for each day.
My expected result is 15.
CREATE TABLE #Test
(
DateTimeStamp datetime,
Value int
)
INSERT INTO #Test SELECT '2012-06-15 16:00:00.000',4
INSERT INTO #Test SELECT '2012-06-15 2:05:00.000',5
INSERT INTO #Test SELECT '2012-06-14 2:10:00.000',3
INSERT INTO #Test SELECT '2012-06-14 2:00:00.000',4
INSERT INTO #Test SELECT '2012-06-13 2:00:00.000',6
INSERT INTO #Test SELECT '2012-06-12 2:00:00.000',1
INSERT INTO #Test SELECT '2012-06-12 4:30:00.000',2
SELECT * FROM #Test ORDER BY DateTimeStamp
DROP TABLE #Test
What do you want to do about missing days? Don't say there won't be any, either. Something always goes wrong... goes wrong... goes wrong...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2012 at 6:25 pm
Yes, something always does go wrong.
This particular business process is an intermediate step. If the primary business group doesn't generate the record the next business group in line can't go home. So we have had delays but never a missing day.
But good point, a record in the middle could get deleted, so a notification would be in order.
As far as the coding is concerned there is no real way to get a correct answer if a record is missing, so a notification would probably be my best bet.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply