April 28, 2012 at 12:44 pm
Hi, I need help in building a query that returns a cumulative count for a specified date range.
For example, if a table contains two entries for April first, one in April 15th, and one more on April 25th, there is cumulative count of 4 records.
my challenge is that I need to display what was the cumulative
count each date, on April first, April 15th, and April 25th.
Please see my sample output table below the DDL area.
I will appreciate help on this.
This is the DDL and data insert script.
CREATE TABLE [dbo].[TestTable](
[EmpId] [int] NULL,
[RecID] [varchar](4) NULL,
[AddDate] [datetime] NULL
)
INSERT INTO [TestTable]
(EmpId, RecID, AddDate)
SELECT '100','1','Apr 01 2012 12:00AM' UNION ALL
SELECT '100','10','Apr 01 2012 12:00AM' UNION ALL
SELECT '100','2','Apr 15 2012 12:00AM' UNION ALL
SELECT '100','3','Apr 25 2012 12:00AM' UNION ALL
SELECT '200','4','Apr 01 2012 12:00AM' UNION ALL
SELECT '200','5','Apr 15 2012 12:00AM' UNION ALL
SELECT '200','6','Apr 25 2012 12:00AM' UNION ALL
SELECT '300','7','Apr 01 2012 12:00AM' UNION ALL
SELECT '300','8','Apr 15 2012 12:00AM' UNION ALL
SELECT '300','9','Apr 25 2012 12:00AM'
Please note the entries foe employee 100.
select * from testtable
where empid = '100'
order by adddate desc
EmpId RecId AddDaate
100 3 2012-04-25 00:00:00.000
100 2 2012-04-15 00:00:00.000
100 1 2012-04-01 00:00:00.000
100 10 2012-04-01 00:00:00.000
Select
EmpID,
AddDate,
count(recid)as daylyCount,
count(recid)as Cumulative
from [TestTable]
--where adddate = '04-01-2012'
where addDate between '04-01-2012' and '04-26-2012'
and empid = '100'
group by addDate, empid
order by adddate desc
I need help to write a query to get the results as below.
EmpId AddDate Daily Count Cumulative
100 2012-04-25 00:00:00.000 1 4
100 2012-04-15 00:00:00.000 1 3
100 2012-04-01 00:00:00.000 2 2
Thanks,
April 28, 2012 at 1:13 pm
Amazing work posting all the needed information! Awesome.
IS this what u were looking for?
; with GroupedData AS
(
Select
RN = ROW_NUMBER() over (partition by empid order by AddDate)
,EmpID
,AddDate
,count(recid)as daylyCount
from [TestTable]
where addDate between '04-01-2012' and '04-26-2012'
and empid = '100'
group by addDate, empid
-- order by adddate desc
)
, rCTE as
(
select GD.RN, GD.EmpId , GD.AddDate , GD.daylyCount ,CummuCount = GD.daylyCount
from GroupedData GD
where RN = 1
union all
select base.RN , base.EmpId , base.AddDate , base.daylyCount , base.daylyCount + cte.CummuCount
from GroupedData base
inner join rCTE cte
on cte.RN + 1 = base.RN
)
select * from rCTE
April 28, 2012 at 1:30 pm
Yes, it is. Thank you...
April 29, 2012 at 11:37 am
CELKO (4/28/2012)
You do not know that rows are not records. Since the crap you did post has only no null-able columns by definition is not a table. You do not even know the ISO date formats! You do not know how to write a simple insertion statement. You do not know about keys. Why did you put strings into the INTEGER emp_id ??If I put this in a book, people would laugh at me. Since you were rude, here is my guess that rec_id is a sequential number of some kind. A competent programmer (of ANY language) would use the ISO 8601 date formatting, etc.
CREATE TABLE Foobar –- stupid vague name
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id),
rec_id CHAR(4) NOT NULL PRIMARY KEY,
CHECK(rec_id LIKE '[0-9][0-9][0-9][0-9]'),
something_date DATE NOT NULL);
INSERT INTO Foobar (emp_id, rec_seq, something_date)
(100, '0001', '2012-04-01'),
(100, '0002', '2012-04-15'),
(100, '0003', '2012-04-25'),
(200, '0004', '2012-04-01'),
(200, '0005', '2012-04-15'),
(200, '0006', '2012-04-25'),
(300, '0007', '2012-04-01'),
(300, '0008', '2012-04-15'),
(300, '0009', '2012-04-25'),
(100, '0010', '2012-04-01');
SELECT emp_id, something_date,
COUNT(rec_id) OVER (PARTITION BY emp_id)
AS daily_rec_cnt,
COUNT(rec_id)
OVER (PARTITION BY emp_id
ROWS UNBOUNDED PRECEDING BETWEEN CURRENT ROW)
AS cum_rec_cnt,
FROM Foobar
WHERE something_date BETWEEN '2012-04-01' AND '2012-04-26'
AND emp_id = 100;
Seriously? You're recommending a leading-zero incrementing sequence number stored in a VARCHAR column over an INT? You don't have to write that into a book for us to laugh, Joe. 😉
--Jeff Moden
April 29, 2012 at 9:50 pm
CELKO (4/28/2012)
You do not know that rows are not records. Since the crap you did post has only no null-able columns by definition is not a table. You do not even know the ISO date formats! You do not know how to write a simple insertion statement. You do not know about keys. Why did you put strings into the INTEGER emp_id ??If I put this in a book, people would laugh at me. Since you were rude, here is my guess that rec_id is a sequential number of some kind. A competent programmer (of ANY language) would use the ISO 8601 date formatting, etc.
CREATE TABLE Foobar –- stupid vague name
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id),
rec_id CHAR(4) NOT NULL PRIMARY KEY,
CHECK(rec_id LIKE '[0-9][0-9][0-9][0-9]'),
something_date DATE NOT NULL);
INSERT INTO Foobar (emp_id, rec_seq, something_date)
(100, '0001', '2012-04-01'),
(100, '0002', '2012-04-15'),
(100, '0003', '2012-04-25'),
(200, '0004', '2012-04-01'),
(200, '0005', '2012-04-15'),
(200, '0006', '2012-04-25'),
(300, '0007', '2012-04-01'),
(300, '0008', '2012-04-15'),
(300, '0009', '2012-04-25'),
(100, '0010', '2012-04-01');
SELECT emp_id, something_date,
COUNT(rec_id) OVER (PARTITION BY emp_id)
AS daily_rec_cnt,
COUNT(rec_id)
OVER (PARTITION BY emp_id
ROWS UNBOUNDED PRECEDING BETWEEN CURRENT ROW)
AS cum_rec_cnt,
FROM Foobar
WHERE something_date BETWEEN '2012-04-01' AND '2012-04-26'
AND emp_id = 100;
Typical Mr. Celko, arrogant and condescending. Not only that you provide an answer that WILL NOT WORK in SQL Server 2008.
Please, Mr. Celko, if you are going to take the time to respond, try being helpful and respectful. Also, try providing answers that will work on the version of SQL Server that the OP is using.
May 1, 2012 at 8:58 am
This seems like a good time to point something out I've thought about.
It is quite disappointing that SQL ServerCentral continues to leave Mr. Celko's comments on this board as he is the most condescending, elitist, and just plain mean person I've seen on technical boards. He is much more fit for political boards.
I've actually seen some defendants of Mr. Celko on here, and they need to know that there is no excuse for how he acts. "That's just how he is" and " He's a great guy in person" do not excuse him of his arrogant elitism.
Along those lines it is also disappointing that SQL ServerCentral leaves up the never ending post "Are the posted questions getting worse?".
Again, just drips with elitism.
Here's my real point. Those of you on this site answering questions are at best 5% of this sites visitors. MOST people like me come to the site to get answers to problems they are trying to solve, and we are quite grateful for your help. I would hope that you few would realize and remember that this site would not be necessary and certainly would not exist if not for the majority who are looking for help and many who may be beginners and know very little about SQL Server.
Maybe you can create a new site where only the elite who know everything already can go stroke each others ego and all us lowly no nothings would not be in your way.
Most of you are VERY patient and helpful, I do not intend to imply all of you reply anything like Mr. Celko or a handful of others I've seen.
May 1, 2012 at 9:58 am
jmadsen 98862 (5/1/2012)
This seems like a good time to point something out I've thought about.It is quite disappointing that SQL ServerCentral continues to leave Mr. Celko's comments on this board as he is the most condescending, elitist, and just plain mean person I've seen on technical boards. He is much more fit for political boards.
I've actually seen some defendants of Mr. Celko on here, and they need to know that there is no excuse for how he acts. "That's just how he is" and " He's a great guy in person" do not excuse him of his arrogant elitism.
Along those lines it is also disappointing that SQL ServerCentral leaves up the never ending post "Are the posted questions getting worse?".
Again, just drips with elitism.
Here's my real point. Those of you on this site answering questions are at best 5% of this sites visitors. MOST people like me come to the site to get answers to problems they are trying to solve, and we are quite grateful for your help. I would hope that you few would realize and remember that this site would not be necessary and certainly would not exist if not for the majority who are looking for help and many who may be beginners and know very little about SQL Server.
Maybe you can create a new site where only the elite who know everything already can go stroke each others ego and all us lowly no nothings would not be in your way.
Most of you are VERY patient and helpful, I do not intend to imply all of you reply anything like Mr. Celko or a handful of others I've seen.
while Celko is at times arrogant and pushes standards a lot harder than any one else that i have seen on these boards, there are small amounts of tsql nougatty goodness. his posts may need to be tempered at times but he does make valid points. ISO standards are a good thing and after reading his books i can see where he is coming from. Personally im not as concerned with ISO standards as i am with company standards.
as for Celko tearing into a poster for what is obviously test data (dbo.TestTable) that has been sanitized for public consumption, i agree that he was in the wrong in this case.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 1, 2012 at 10:08 am
jmadsen 98862 (5/1/2012)
This seems like a good time to point something out I've thought about.It is quite disappointing that SQL ServerCentral continues to leave Mr. Celko's comments on this board as he is the most condescending, elitist, and just plain mean person I've seen on technical boards. He is much more fit for political boards.
I've actually seen some defendants of Mr. Celko on here, and they need to know that there is no excuse for how he acts. "That's just how he is" and " He's a great guy in person" do not excuse him of his arrogant elitism.
Along those lines it is also disappointing that SQL ServerCentral leaves up the never ending post "Are the posted questions getting worse?".
Again, just drips with elitism.
Here's my real point. Those of you on this site answering questions are at best 5% of this sites visitors. MOST people like me come to the site to get answers to problems they are trying to solve, and we are quite grateful for your help. I would hope that you few would realize and remember that this site would not be necessary and certainly would not exist if not for the majority who are looking for help and many who may be beginners and know very little about SQL Server.
Maybe you can create a new site where only the elite who know everything already can go stroke each others ego and all us lowly no nothings would not be in your way.
Most of you are VERY patient and helpful, I do not intend to imply all of you reply anything like Mr. Celko or a handful of others I've seen.
I do not agree with much of what Mr. Celko says, nor do I appreciate his approach with regard to answering questions. I will, however, agree with SSC retaining his posts on these forums just as I will support his right to self-expression.
Also, you really should not criticize that which you do not understand. I have to ask, do you really believe that there almost 36,000 posts ranting about bad questions? Those of us who try help others do get frustrated, and we do need to blow off steam at times. That particular thread was started as a rant about bad questions, and yes there are quite a few. But there are also those who ask very well worded questions, or those whose questions are nebulous but are quick to provide feedback to allow us to answer theier questions, and I would say these far out number the bad questions.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply