Counting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Counting

Author

Topic

beemd
Starting Member

14 Posts

Posted - 2013-12-02 : 04:33:57

Hi,

I've got a simple table with two fields

dt(datetime)
tphone(varchar)

Each time a record is inserted the dt column is set to the current date/time.

I'm trying to write a query to display a list of dates in one column and the record count up to and including that date in the next column.

Any help appreciated.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 04:40:17

depending on your condition you need one of the counts below


SELECT DATEADD(dd,DATEDIFF(dd,0,dt),0) AS DateVal,
COUNT(*) AS CntTotal,
COUNT(tphone) AS CntNonNullPhone
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,dt),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beemd
Starting Member

14 Posts

Posted - 2013-12-02 : 04:45:23

Hi,

Thanks for that, this seems to count the total for each day, I need the cumulative total, i.e. the count should increase over time.

Thanks

Ben

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 04:53:10

quote:
Originally posted by beemd

Hi,

Thanks for that, this seems to count the total for each day, I need the cumulative total, i.e. the count should increase over time.

Thanks

Ben

Sorry that was not quite clearly from your original problem statement
Anyways here you go


SELECT  DateVal,
CummCnt
FROM (SELECT DISTINCT DATEADD(dd,DATEDIFF(dd,0,dt),0) AS dateVal FROM Table)t
CROSS APPLY (SELECT COUNT(1) AS CummCnt
             FROM Table
             WHERE DateVal < t.DateVal + 1
             )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beemd
Starting Member

14 Posts

Posted - 2013-12-02 : 05:00:02

Thanks for your help.

But, that gives the final total for every date.

Ben

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 07:29:36

quote:
Originally posted by beemd

Thanks for your help.

But, that gives the final total for every date.

Ben

then whats it that you're expecting?
Atleast show us some sample data and output you desire out of it.
thats much easier for getting quick and accurate solution to your issue.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 07:31:33

one more attempt


SELECT  dt,CummCnt
FROM Table t
CROSS APPLY (SELECT COUNT(1) AS CummCnt
             FROM Table
             WHERE dt <= t.dt
             )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beemd
Starting Member

14 Posts

Posted - 2013-12-02 : 08:01:15

A sample would be

dt tphone
01/01/1900 dfd
01/01/1900 dfd
01/01/1900 dfd
02/01/1900 dfd
02/01/1900 dfd
03/01/1900 dfd
04/01/1900 dfd
04/01/1900 dfd
04/01/1900 dfd
04/01/1900 dfd

And the result from that should be

dt count
01/01/1900 3
02/01/1900 5
03/01/1900 6
04/01/1900 10

Hope that makes sense :)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 08:08:40

[code]
declare @t table
(
dt datetime,
tphone varchar(10)
)
insert @t
values('01/01/1900', 'dfd'),
('01/01/1900', 'dfd'),
('01/01/1900', 'dfd'),
('02/01/1900', 'dfd'),
('02/01/1900', 'dfd'),
('03/01/1900', ' dfd'),
('04/01/1900', ' dfd'),
('04/01/1900', ' dfd'),
('04/01/1900', ' dfd'),
('04/01/1900', ' dfd')

SELECT DateVal,
CummCnt
FROM (SELECT DISTINCT DATEADD(dd,DATEDIFF(dd,0,dt),0) AS dateVal FROM @t)t
CROSS APPLY (SELECT COUNT(1) AS CummCnt
FROM @t
WHERE dt <= t.DateVal
)t1

output
--------------------------------
DateVal CummCnt
---------------------------------
1900-01-01 00:00:00.000 3
1900-02-01 00:00:00.000 5
1900-03-01 00:00:00.000 6
1900-04-01 00:00:00.000 10

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

beemd
Starting Member

14 Posts

Posted - 2013-12-02 : 08:12:52

Now it's perfect. Thanks! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources