Day count - 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 2005 Forums

Transact-SQL (2005)

Day count

Author

Topic

Ali T
Starting Member

22 Posts

Posted - 2009-01-17 : 12:58:56

Dear all;
I have a table including the call record data of a telecom company. the columns of this table as as follow: ID, Date, and duration
needless to say that each record (row) of this table represents a call (i.e if a subscriber makes 10 call we would have 10 records in the table)
Now what I'm going to do is to calculate the number of days in which each Subscriber (ID) has had a call (no matter outgiong or incoming) and I'm looking for a query that can extract the number of days in which the customer has made a phone call for every single cutomer.
I'd be grateful if you help mewith this problem.

Regards
Ali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 13:09:18

this will give you number of days with a call for each subscriber


SELECT ID,
COUNT(DISTINCT Date) AS NoOfDays
FROM
(
SELECT 
ID,
DATEADD(dd,DATEDIFF(dd,0,Date),0) AS Date
FROM Table
)t
GROUP BY ID

this will give you number of days where all customers have made atleast one call


SELECT COUNT(Date)
FROM
(
SELECT DATEADD(dd,DATEDIFF(dd,0,Date),0) AS Date,
COUNT(DISTINCT ID) AS CustomerNo
FROM Table
GROUP BY DATEADD(dd,DATEDIFF(dd,0,Date),0)
)t
CROSS JOIN
(SELECT COUNT(DISTINCT ID) AS CustCount FROM Table)tot
WHERE tot.CustCount=t.CustomerNo

Ali T
Starting Member

22 Posts

Posted - 2009-01-17 : 13:47:53

It worked And I'm Really thankful
Ali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 13:56:27

welcome

Ali T
Starting Member

22 Posts

Posted - 2009-01-18 : 14:50:00

What if I want to add the result of counting as a column named noofdays to my table?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-18 : 15:25:02

Can't you do like this?


Insert into Newtable(columns)
Select .....(from visakh's query)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-18 : 22:54:24

and obviously you need to add the new column to table before using above insert using

ALTER TABLE ADD noofdays int...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources