June 8, 2018 at 1:34 pm
I am trying sum some columns and have a column that is different in each row but I only want one row back with the latest value based on the TDate.
Declare @TEST TABLE
(
FullName varchar(20),
NewType varchar(20),
Amount1 int,
Amount2 int,
TDate DateTime
)
INSERT @test-2 values('Tom', 'Emp', 10, 20, '10/15/2017')
INSERT @test-2 values('Tom', 'Family', 200, 39, '09/15/2017')
INSERT @test-2 values('Tom', 'Emp/SP', 180, 90, '02/15/2016')
SELECT FullName, NewType, SUM(Amount1), SUM(Amount2)
FROM @test-2
GROUP BY FullName, NewType
This gets me:
FullName NewType (No column name) (No column name)
Tom Emp 10 20
Tom Emp/SP 180 90
Tom Family 200 39
What I want is only one row with "Emp" as the NewType based on the latest TDate.
Thanks.
June 8, 2018 at 4:25 pm
This perhaps?
SELECT FullName, NewType, SUM(Amount1), SUM(Amount2)
FROM
(
SELECT TOP (1) FullName, NewType, Amount1, Amount2
FROM @TEST
ORDER BY TDate DESC
) x
GROUP BY FullName, NewType
-- Itzik Ben-Gan 2001
June 8, 2018 at 6:18 pm
That doesn't sum anything because the join only passes back one row.
I get this a the answer:
FullName NewType (No column name) (No column name)
Tom Emp 10 20
The amounts should sum all three rows.
I am trying to get sum all the rows but get the current values for the other fields.
June 9, 2018 at 12:37 am
Straight forward if you use the Window functions
😎
USE TEEST;
GO
SET NOCOUNT ON
Declare @TEST TABLE
(
FullName varchar(20),
NewType varchar(20),
Amount1 int,
Amount2 int,
TDate DateTime
)
INSERT @TEST values('Tom', 'Emp', 10, 20, '10/15/2017')
INSERT @TEST values('Tom', 'Family', 200, 39, '09/15/2017')
INSERT @TEST values('Tom', 'Emp/SP', 180, 90, '02/15/2016')
;WITH BASE_DATA AS
(
SELECT
T.FullName
,ROW_NUMBER() OVER
(
PARTITION BY T.FullName
ORDER BY T.TDate ASC
) AS RID
,LAST_VALUE(T.NewType) OVER
(
PARTITION BY T.FullName
ORDER BY T.TDate ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
) AS NewType
,SUM(T.Amount1) OVER
(
PARTITION BY T.FullName
) AS Amount1
,SUM(T.Amount2) OVER
(
PARTITION BY T.FullName
) AS Amount2
,MAX(T.TDate) OVER
(
PARTITION BY T.FullName
) AS TDate
FROM @TEST T
)
SELECT
BD.FullName
,BD.NewType
,BD.TDate
,BD.Amount1
,BD.Amount2
FROM BASE_DATA BD
WHERE BD.RID = 1;
Output
FullName NewType TDate Amount1 Amount2
--------- -------- ----------------------- -------- --------
Tom Emp 2017-10-15 00:00:00.000 390 149
June 11, 2018 at 1:45 am
I tried that but got the following error:
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'ROWS'.
Am I missing something?
I am using Sql Server 2008 R2
Thanks.
June 11, 2018 at 2:18 am
tshad - Monday, June 11, 2018 1:45 AMI tried that but got the following error:
Msg 102, Level 15, State 1, Line 25Incorrect syntax near 'ROWS'.
Am I missing something?I am using Sql Server 2008 R2
Thanks.
That is because the support for the Window functions is very limited before SQL Server 2012.
😎
Since you posted on the 2012 forum, I assumed you were on 2012 or later.
Here is a 2008 compatible version
USE TEEST;
GO
SET NOCOUNT ON
Declare @TEST TABLE
(
FullName varchar(20),
NewType varchar(20),
Amount1 int,
Amount2 int,
TDate DateTime
)
INSERT @TEST values('Tom', 'Emp', 10, 20, '10/15/2017')
INSERT @TEST values('Tom', 'Family', 200, 39, '09/15/2017')
INSERT @TEST values('Tom', 'Emp/SP', 180, 90, '02/15/2016')
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY T.FullName
ORDER BY T.TDate DESC
) AS RID
,T.FullName
,T.NewType
,T.TDate
FROM @TEST T
)
,AGGRECATED_DATA AS
(
SELECT
TA.FullName
,SUM(TA.Amount1) AS AMT01
,SUM(TA.Amount2) AS AMT02
FROM @TEST TA
GROUP BY TA.FullName
)
SELECT
BD.FullName
,BD.NewType
,BD.TDate
,AD.AMT01
,AD.AMT02
FROM BASE_DATA BD
INNER JOIN AGGRECATED_DATA AD
ON BD.FullName = AD.FullName
WHERE BD.RID = 1;
June 12, 2018 at 12:10 pm
I forgot I was on 2008. I use both servers.
This gave me what I needed.
Thanks.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply