Calculate New 'Unique' Records Each Day

Hi All,
If I have the following table and want to get the below results out,
what would be the best way of approaching this:
StartDate | TelNumber
01/11/05 | 1111
01/11/05 | 2222
01/11/05 | 3333
02/11/05 | 1111
02/11/05 | 4444
03/11/05 | 1111
03/11/05 | 5555
03/11/05 | 1111
03/11/05 | 6666
Each day I want to know the number of Unique TelNumbers across the
whole table each day.
So the answer I am looking for based on the above table, from the query
is:
01/11/05 -> 3 Unique TelNumbers
02/11/05 -> 1 Unique TelNumbers
03/11/05 -> 2 Unique TelNumbers
I've been messing around with Distinct but I can't seem to get it to
look at previous days.
Can anyone help?
Thanking you all in advance.
HPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
SELECT foobar_date, COUNT(DISTINCT tel_nbr)
FROM Foobar
GROUP BY foobar_date;
You might also want to learn about ISO-8601 dates.|||harrys@.gmail.com wrote:

> Each day I want to know the number of Unique TelNumbers across the
> whole table each day.
> So the answer I am looking for based on the above table, from the
> query is:
> 01/11/05 -> 3 Unique TelNumbers
> 02/11/05 -> 1 Unique TelNumbers
> 03/11/05 -> 2 Unique TelNumbers
> I've been messing around with Distinct but I can't seem to get it to
> look at previous days.
> Can anyone help?
DDL would indeed be nice. This is untested but I think it'll work:
select T1.StartDate, count(select distinct TelNumber from Table T2
where T2.StartDate = T1.StartDate and TelNumber not in (select distinct
TelNumber from Table T3 where T3.StartDate < T2.StartDate))
from Table T1
group by T1.StartDate
HTH,
Stijn Verrept.|||Thanks for the replies. As requested, just to be more specific.
I receive a file every day that is set up as below. I have been
requested to find out how many new callers there are each day that have
never called before:
I use BULK INSERT to put the table into SQL.
-- Insert Data Into Table --
BULK INSERT [MasterData]
FROM 'c:\a2.csv'
WITH (FIELDTERMINATOR = ',')
Table:
ID | StartDate | TelNumber |
1 | 01/11/05 09:32:02 | 012321111
2 | 01/11/05 09:35:18 | 012322222
3 | 02/11/05 15:50:32 | 012321111
4 | 02/11/05 19:10:46 | 012323333
5 | 03/11/05 11:10:11 | 012324444
6 | 03/11/05 17:46:25 | 012322222
7 | 03/11/05 06:34:42 | 012328888
So i have to work out how many new callers there are each day that have
never called in before. So the results from the above would be:
01/11/05 - 2 New Callers
02/11/05 - 1 New Callers
03/11/05 - 3 New Callers
- - - - - - - - - - - -
Celko- Your query gives me distinct callers each day, but it doesnt
take into account if they have already called on the previous day.
Stijn - I tried working/amending your query but just couldnt get it to
work. (Apologies if it's me being stupid!) Could you expand on how you
think that may solve my dilema?
This query will need to be run everyday!
Thanks for your assistance.
H|||Hi There,
See if this can help you.
Select T1.StartDate,(
Select Count(Distinct T2.TElNumber) From tmpData1 T2 Where
T2.StartDate =T1.StartDate
And T2.TelNumber Not In ( Select T3.TElNumber From tmpData1 T3 Where
T3.StartDate<T2.StartDate)
)
>From tmpData1 T1 Group By T1.StartDate
Here tmpData1 is the table Name
With Warm regards
Jatinder Singh|||Hi,
No joy!
I tried the following:
SELECT DATEPART(dd,T1.StartDate),(
SELECT Count(Distinct T2.CLI) From MasterData T2 Where
T2.StartDate =T1.StartDate
And T2.CLI Not In ( Select T3.CLI From MasterData T3 Where
T3.StartDate<T2.StartDate)
)
FROM MasterData T1 GROUP BY DATEPART(dd,T1.StartDate)
But I just get a list of zeros!
Exscuse my ignorance, but where can I found out more about this T1 T2
business? Had a look on google, but obviously I dont know what to
search on at the moment!
Thanks
H|||Just to add to this:
If I run the following:
SELECT convert(varchar(10), StartDate, 101) AS DayCol, COUNT(DISTINCT
CLI) AS CountCLI
FROM MasterData
GROUP BY convert(varchar(10), StartDate, 101)
ORDER BY convert(varchar(10), StartDate, 101)
It gives me the nuimber of unique telephone numbers EACH DAY.
Is there no way to creat a sub-query to calculate the number of unique
telephone numbers each day since the beggining?|||harrys@.gmail.com wrote:

> It gives me the nuimber of unique telephone numbers EACH DAY.
> Is there no way to creat a sub-query to calculate the number of unique
> telephone numbers each day since the beggining?
Well that is what my query should do LOL. The T1, T2, ... are just
aliases. If you can provide some DLL (create table, insert into table,
...) of the table and sample data you use, we can easily try out the
code and correct it if wrong. See this post for an example of good
DDL:
http://groups.google.com/group/micr...programming/br
owse_thread/thread/5d4fc158de977f3f
Kind regards,
Stijn Verrept.|||Stign,
Thanks for the reference link. I see what you mean now. And ackowledged
for future reference!
So on that basis, below are the details:
- - - - - TABLE
CREATE TABLE [dbo].[MasterData](
[CDRRef] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[CLI] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[AccessNumber] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[TransNumber] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[DurSec] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
- - - - - INSERT DATA
INSERT INTO [YourCallCDR].[dbo].[MasterData]
([CDRRef],[CLI],[AccessNumber],[TransNum
ber],[StartDate],[EndDate],[DurSec])
VALUES
(AFE5468ASD,0122233344,0777000000,077700
0000,10/01/05
09:00:02,10/01/05 09:05:05,5)
INSERT INTO [YourCallCDR].[dbo].[MasterData]
([CDRRef],[CLI],[AccessNumber],[TransNum
ber],[StartDate],[EndDate],[DurSec])
VALUES
(ADF8912GGE,0122233355,0777000000,077700
0000,10/01/05
10:18:05,10/01/05 10:18:32,27)
So on and so forth. . . .
Stign - I did try your query; Amended as follows:
SELECT T1.StartDate,
COUNT(SELECT DISTINCT CLI FROM MasterData T2
where T2.StartDate = T1.StartDate and CLI not in (select distinct
CLI from MasterData T3 where T3.StartDate < T2.StartDate))
from MasterData T1
group by T1.StartDate
But even after playing around with it, I receive the error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Hope the additional info helps someone help me solve this one!!
Thanks
H|||harrys@.gmail.com wrote:

> Hope the additional info helps someone help me solve this one!!
Indeed much better, only the data isn't correct. You can use this
great freeware program to generate some data that can be inserted:
http://www.rac4sql.net/objectscriptr_main.asp
Try this query:
SELECT T1.StartDate,
(SELECT COUNT(DISTINCT CLI) FROM #MasterData T2
where T2.StartDate = T1.StartDate and CLI not in (select distinct
CLI from #MasterData T3 where T3.StartDate < T2.StartDate)) as UniqueCLI
from #MasterData T1
group by T1.StartDate
It doesn't give any error.
Kind regards,
Stijn Verrept.