How to get a specific 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)

How to get a specific count

Author

Topic

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-03 : 20:04:48

Hi,
I have a table with the following fields:
AutoID (Identity)
EventNumber (Int)
EventDate (SmallDateTime)
BranchID (Int)
UserID (Nchar)

In each event I can have one or more userID inserted.
A specific EventID can be repeated everyday.

I need to count the number of distinct events per every branch in the table between Jan 1st 2010 and Dec 31st 2009.

There are over 3000 entries in the table, but the number of distinct events should be lower (my estimation around 2500)

Thanks,
Gil

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-03 : 20:28:28

What makes a distinct Event? EventNumber? I assume it can't be null.

select BranchID, count(distinct EventNumber)
from tbl
where EventDate between ...
group by BranchID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-03 : 20:48:32

Each branch is counting it's own events and reporting back at a daily basis.
A distinct event is an event that happened in a specific branch at a specific date and has the same eventnumber.
The same eventNumber can be repeated daily, I can have eventnumber 1 in branch no 1 everyday of the year....
I can have multiple rows for the same event (At the same branch and date) but the user will be different.

What I need to get is the count for all events per branch without the duplicate rows of the users.

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-03 : 20:55:39

Here is an example:
Row 1:
AutoID=1
BranchID=1
EventNumber=13
EventDate=1.1.2010
UserID=98

Row 2:
AutoID=2
BranchID=1
EventNumber=13
EventDate=1.1.2010
UserID=99

Row 3:
AutoID=3
BranchID=2
EventNumber=13
EventDate=1.1.2010
UserID=90

Row 4:
AutoID=4
BranchID=1
EventNumber=11
EventDate=1.1.2010
UserID=97

The report should show:
BranchID=1, number of events=2 (from Row1 and row4)
BranchID=2, Number of events=1 (from Row3)

Row2 is disregarded in the example because it's in the same branch at the same date with the same eventNumber the only diff is the UserID.

Hope this helps....
Gil

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-03 : 20:58:57

The code I gave should work.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-03 : 21:01:36

unfortunately it won't help me because the same eventnumber can be repeated daily.
And I need to count all of the distinct events even if they do have the same eventnumber in different dates...

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-04 : 00:08:37

quote:
Originally posted by Gil_Bar

unfortunately it won't help me because the same eventnumber can be repeated daily.
And I need to count all of the distinct events even if they do have the same eventnumber in different dates...

Did you tried the code given by nigelrivett ?

For me it seems like it works for your requirement:

Create table tbltest
(
AutoID int identity,
EventNumber int,
EventDate SmallDatetime,
BranchId int,
UserId Nchar(50))

Insert into tblTest (BranchID,EventNumber,EventDate,UserID)
Select 1,13,'20100101','98' union
Select 1,13,'20100101','99' union
Select 2,13,'20100101','90' union
Select 1,11,'20100101','97'

select BranchID, count(distinct EventNumber) As EventNumber
from tbltest
where EventDate between '2010-01-01' and '2010-12-31'
group by BranchID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 01:11:35

quote:
Originally posted by Gil_Bar

unfortunately it won't help me because the same eventnumber can be repeated daily.
And I need to count all of the distinct events even if they do have the same eventnumber in different dates...

Try it - it should work.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-04 : 01:51:03

Hi Guys,
I tried...
If I put in just these records as I submitted earlier I get the following results:
BranchId Num
1____________2
2____________1

But if I add this (Different date, same eventnumber, same user:
Insert into tblTest (BranchID,EventNumber,EventDate,UserID)
Select 1,13,'20100102','98' union
Select 1,13,'20100102','99' union
Select 2,13,'20100102','90' union
Select 1,11,'20100102','97'

I get the same results instead of the one that I should get which are:

BranchId Num
1____________4
2____________2

Hope you have any other ideas 'cause I'm stuck...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 03:26:57

Are you sure you're typing in the right thing?
I doubt it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-04 : 03:37:49

copy & paste as you guys wrote it...

If I put in just one date with the same eventnumbers it works fine, but if the same eventnumbers (in the same branches) are on different dates it's not working...

This is what I did:

Create table tbltest
(
AutoID int identity,
EventNumber int,
EventDate SmallDatetime,
BranchId int,
UserId Nchar(50))

Insert into tbltest (BranchId,EventNumber,EventDate,UserId)
Select 1,13,'20100101','98' union
Select 1,13,'20100101','99' union
Select 2,13,'20100101','90' union
Select 1,11,'20100101','97' union
Select 1,13,'20100102','98' union
Select 1,13,'20100102','99' union
Select 2,13,'20100102','90' union
Select 1,11,'20100102','97'

select BranchId, count(distinct EventNumber) As EventNumber
from tbltest
where EventDate between '2010-01-01' and '2010-12-31'
group by BranchId

This is what you get:
Branch (1) has 2 events (instead of 4)
Bracnh (2) has 1 events (instead of 2)

Thanks again,
Gil

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 03:51:01

Looks to me like branch 1 has 2 events, 13 and 11. Branch 2 has 1 event 11.
You said you wanted to only count events ignoring the day they occured on.

It's easy to count events per day but you need to decide what you want.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-04 : 03:56:23

I guess I didn't explain my self properly

I need to count the actual events which means the total number of events in each branch even if the same eventnumber is in multiple dates.
What I need to ignore is if more than one user was involved at the same event in the same branch at the same date.

Hope I'm more clear this time.
Gil

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 04:16:04

Do you mean you want the number of distinct daily events? Still isn't very clear.
maybe
select BranchID, events = sum(events)
from
(
select BranchID, EventDate, events = count(distinct EventNumber)
where EventDate between ...
from tbl
group by BranchID, EventDate
) a
group by BranchID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gil_Bar
Starting Member

11 Posts

Posted - 2011-01-04 : 04:31:08

THANK YOU! That's exactly what I needed....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources