SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Counting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

beemd
Starting Member

14 Posts

Posted - 12/02/2013 :  04:33:57  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 12/02/2013 :  04:40:17  Show Profile  Reply with Quote
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
Go to Top of Page

beemd
Starting Member

14 Posts

Posted - 12/02/2013 :  04:45:23  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/02/2013 :  04:53:10  Show Profile  Reply with Quote
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
Go to Top of Page

beemd
Starting Member

14 Posts

Posted - 12/02/2013 :  05:00:02  Show Profile  Reply with Quote
Thanks for your help.

But, that gives the final total for every date.

Ben
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/02/2013 :  07:29:36  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/02/2013 :  07:31:33  Show Profile  Reply with Quote
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
Go to Top of Page

beemd
Starting Member

14 Posts

Posted - 12/02/2013 :  08:01:15  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/02/2013 :  08:08:40  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

beemd
Starting Member

14 Posts

Posted - 12/02/2013 :  08:12:52  Show Profile  Reply with Quote
Now it's perfect. Thanks! :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000