SUM of Latest 4 dates
-
SET NOCOUNT ON
DECLARE @DataTable TABLE
(
RowID INT IDENTITY,
Version VARCHAR(32),
Vcount INT,
DataDate DATETIME
)
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AA', 22, '08/09/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AA', 12, '08/09/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AB', 22, '08/09/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AC', 22, '08/09/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AA', 42, '08/23/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AD', 192, '08/23/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AF', 2, '08/23/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AA', 212, '09/23/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AD', 122, '09/23/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AD', 242, '09/23/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AT', 42, '09/23/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AR', 21, '09/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AG', 52, '09/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AA', 2, '09/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AF', 72, '09/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AA', 231, '10/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AV', 532, '10/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AC', 32, '10/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AG', 372, '10/27/2004')
INSERT INTO @DataTable (Version, Vcount, DataDate) VALUES ('AV', 32, '10/27/2004')
SELECT * FROM @DataTable
/*
I want a sum for 4 latest dates
Version LatestDatecount PreviousDateCount PreviousDateCount PreviousDateCount
AA 231 2
AC 532 0
AF 0 72
AG 372 52
AR 0 21
AV 564 0
I can change the table's structure so if there is a solution to change the schema it is welcome.
Thank You
*/
Regards,
gova -
I made it this way. Please provide some easy solutions
DECLARE @CurWeek DATETIME
DECLARE @Week1 DATETIME
DECLARE @Week2 DATETIME
DECLARE @Week3 DATETIME
SELECT @CurWeek = MAX(DataDate) FROM @DataTable
SELECT @Week1 = MAX(DataDate) FROM @DataTable WHERE DataDate < @CurWeek
SELECT @Week2 = MAX(DataDate) FROM @DataTable WHERE DataDate < @Week1
SELECT @Week3 = MAX(DataDate) FROM @DataTable WHERE DataDate < @Week2
DECLARE @Results TABLE
(
Version VARCHAR(32),
Week3 INT NULL,
Week2 INT NULL,
Week1 INT NULL,
Currnt INT NULL
)
INSERT @Results
SELECT Version, 0, 0, 0, SUM(Vcount)
FROM @DataTable
WHERE DataDate = @CurWeek
GROUP BY VERSION
UPDATE @Results
SET Week1 = A.Week1
FROM
(
SELECT Version Vsn, 0 Week3, 0 Week2, SUM(Vcount) Week1, 0 Currnt
FROM @DataTable A
WHERE DataDate = @Week1
AND EXISTS (SELECT * FROM @Results R WHERE A.Version = R.Version)
GROUP BY VERSION) A
WHERE
Version = Vsn
INSERT @Results
SELECT Version, 0, 0, SUM(Vcount), 0
FROM @DataTable A
WHERE DataDate = @Week1
AND NOT EXISTS (SELECT * FROM @Results R WHERE A.Version = R.Version)
GROUP BY VERSION
UPDATE @Results
SET Week2 = A.Week2
FROM
(
SELECT Version Vsn, 0 Week3, SUM(Vcount) Week2, 0 Week1, 0 Currnt
FROM @DataTable A
WHERE DataDate = @Week2
AND EXISTS (SELECT * FROM @Results R WHERE A.Version = R.Version)
GROUP BY VERSION) A
WHERE
Version = Vsn
INSERT @Results
SELECT Version, 0, SUM(Vcount), 0, 0
FROM @DataTable A
WHERE DataDate = @Week2
AND NOT EXISTS (SELECT * FROM @Results R WHERE A.Version = R.Version)
GROUP BY VERSION
UPDATE @Results
SET Week3 = A.Week3
FROM
(
SELECT Version Vsn, SUM(Vcount) Week3, 0 Week2, 0 Week1, 0 Currnt
FROM @DataTable A
WHERE DataDate = @Week3
AND EXISTS (SELECT * FROM @Results R WHERE A.Version = R.Version)
GROUP BY VERSION) A
WHERE
Version = Vsn
INSERT @Results
SELECT Version, SUM(Vcount), 0, 0, 0
FROM @DataTable A
WHERE DataDate = @Week3
AND NOT EXISTS (SELECT * FROM @Results R WHERE A.Version = R.Version)
GROUP BY VERSION
SELECT * FROM @Results
Regards,
gova -
Working from your first post:
Select dt.Version, sum(dt.vcount)
From @DataTable dt
Where datadate IN
(Select top 4 datadate
From @datatable
Where version = dt.version
Order by datadate desc)
Group By dt.version
Order By dt.version
-
Sounds more like it
. -
Nope. Not at all. I want resultset like this.
Version Week3 Week2 Week1 Currnt
----------------------- -------- -------- --------- ------
AA 42 212 2 231
AC 0 0 0 32
AG 0 0 52 372
AV 0 0 0 564
AF 2 0 72 0
AR 0 0 21 0
AD 192 364 0 0
AT 0 42 0 0
Regards,
gova -
Select Distinct version,
(Select isnull(sum(vcount),0)
from @datatable
where datadate = @week3
and version = dt.version) week3,
(Select isnull(sum(vcount),0)
from @datatable
where datadate = @week2
and version = dt.version) week2,
(Select isnull(sum(vcount),0)
from @datatable
where datadate = @week1
and version = dt.version) week1,
(Select isnull(sum(vcount),0)
from @datatable
where datadate = @curweek
and version = dt.version) [current]
From @datatable dt
Order by version
version week3 week2 week1 current
-------------------------------- ----------- ----------- ----------- -----------
AA 42 212 2 231
AB 0 0 0 0
AC 0 0 0 32
AD 192 364 0 0
AF 2 0 72 0
AG 0 0 52 372
AR 0 0 21 0
AT 0 42 0 0
AV 0 0 0 564
-
What if the # of weeks is dynamic??
. -
Hey, that wasn't in the spec!
-
Cool. I will use it. Thank You.
Atleas
et that is not my case Remi. I am happy with Ron's query.We can continue for dynamic # of weeks for interest.
Regards,
gova -
Hehe, it's not your case YET. Don't forget to think a few years ahead when you design the database. You might get a few surprises if you don't.
-
See if this might meet your needs:
DECLARE @Dates TABLE (SeqNo int NOT NULL IDENTITY(1, 1),
DataDate datetime NOT NULL,
PRIMARY KEY CLUSTERED (SeqNo)
)
INSERT INTO @Dates (DataDate)
SELECT TOP 4 DataDate
FROM @DataTable
GROUP BY DataDate
ORDER BY DataDate DESC
SELECT Version,
SUM(CASE WHEN d.SeqNo = 4 THEN t.Vcount ELSE 0 END) AS [Date4],
SUM(CASE WHEN d.SeqNo = 3 THEN t.Vcount ELSE 0 END) AS [Date3],
SUM(CASE WHEN d.SeqNo = 2 THEN t.Vcount ELSE 0 END) AS [Date2],
SUM(CASE WHEN d.SeqNo = 1 THEN t.Vcount ELSE 0 END) AS [Date1]
FROM @DataTable t INNER JOIN @Dates d ON d.DataDate = t.DataDate
GROUP BY t.Version
ORDER BY t.Version
which gives the results:
Version Date4 Date3 Date2 Date1
-------------------------------- ----------- ----------- ----------- -----------
AA 42 212 2 231
AC 0 0 0 32
AD 192 364 0 0
AF 2 0 72 0
AG 0 0 52 372
AR 0 0 21 0
AT 0 42 0 0
AV 0 0 0 564
-
For a dynamic number of weeks AND columns that show the date you might try (note that I changed your table variable into a temporary table):
SET NOCOUNT ON
CREATE TABLE #DataTable
(
RowID INT IDENTITY,
Version VARCHAR(32),
Vcount INT,
DataDate DATETIME
)
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AA', 22, '08/09/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AA', 12, '08/09/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AB', 22, '08/09/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AC', 22, '08/09/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AA', 42, '08/23/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AD', 192, '08/23/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AF', 2, '08/23/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AA', 212, '09/23/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AD', 122, '09/23/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AD', 242, '09/23/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AT', 42, '09/23/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AR', 21, '09/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AG', 52, '09/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AA', 2, '09/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AF', 72, '09/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AA', 231, '10/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AV', 532, '10/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AC', 32, '10/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AG', 372, '10/27/2004')
INSERT INTO #DataTable (Version, Vcount, DataDate) VALUES ('AV', 32, '10/27/2004')
--SELECT * FROM #DataTable
/*
**************************************************************************************
*/
DECLARE @howmany int, @WhichOne int
DECLARE @Cmd varchar(2000), @ColHead char(10)
SET @howmany = 4
CREATE TABLE #Dates (SeqNo int NOT NULL IDENTITY(1, 1),
DataDate datetime NOT NULL,
PRIMARY KEY CLUSTERED (SeqNo)
)
SET @Cmd = '
INSERT INTO #Dates (DataDate)
SELECT TOP ' + CAST(@HowMany AS varchar) + ' DataDate
FROM #DataTable
GROUP BY DataDate
ORDER BY DataDate DESC
'
EXEC (@Cmd)
SET @Cmd = '
SELECT Version'
SET @WhichOne = @howmany
WHILE @WhichOne > 0
BEGIN
SELECT @ColHead = CONVERT(char(10), DataDate, 101)
FROM #Dates
WHERE SeqNo = @WhichOne
SET @Cmd = @Cmd + ',
SUM(CASE WHEN d.SeqNo = ' + CAST(@WhichOne AS varchar) + ' THEN t.Vcount ELSE 0 END) AS [' + @ColHead + ']'
SET @WhichOne = @WhichOne - 1
END
SET @Cmd = @Cmd + '
FROM #DataTable t INNER JOIN #Dates d ON d.DataDate = t.DataDate
GROUP BY t.Version
ORDER BY t.Version
'
EXEC (@Cmd)
DROP TABLE #Dates
DROP TABLE #DataTable
which gives the results:
Version 08/23/2004 09/23/2004 09/27/2004 10/27/2004
-------------------------------- ----------- ----------- ----------- -----------
AA 42 212 2 231
AC 0 0 0 32
AD 192 364 0 0
AF 2 0 72 0
AG 0 0 52 372
AR 0 0 21 0
AT 0 42 0 0
AV 0 0 0 564
This can easily be turned into a stored procedure where @howmany becomes a parameter instead of a manually set parameter in a script.
-
Thanks Paul. I am taking your first part.
I don't go for dynamic #of cols since the report generated has many other columns and some other complex logic. The specifications are fixed.
Regards,
gova
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply