Count for first record

[Dodecahedron]

I have a query as below

Select NameID,Surname,Date,TypeofCalll, etc
FROM table
WHERE

This gives me the result below

ID Surname Date TypeofCall
1 Smith 01/01/2007 Incoming
1 Smith 02/01/2007 Note
1 Smith 02/01/2007 Incoming
2 Brown 01/01/2007 Note
2 Brown 01/01/2007 Incoming

I now need to do a count of the first time a user is registers an incoming call on the system, the result would be

ID Surname Date TypeofCall NewUser
1 Smith 01/01/2007 Incoming 1
1 Smith 02/01/2007 Note 0
1 Smith 02/01/2007 Incoming 0
2 Brown 01/01/2007 Note 0
2 Brown 01/01/2007 Incoming 1

How could I do this

Thanks

 

[gmmastros]

The first thing that comes to mind is...

1. Subquery to get the 'new user' information.
2. Left Join to original table
3. Use a little conditional logic to create derived column.

So... let's build this in pieces.

1. Query to get new user info.

Select Id, 
       SurName, 
       Min(Date) As MinDate, 
       'InComing' As TypeOfCall
From   [!]Table[/!]
Where  TypeOfCall = 'Incoming'
Group By Id, SurName

Running this code should get you all of the new user registrations. It doesn't contain the rest of the info you want, but we're getting to that.

2. The next step is to make the previous query become a subquery to a larger query. We'll need to left join to the original table to make sure we don't miss any rows. Like this...

Select  T.Id, 
        T.SurName, 
        T.Date, 
        T.TypeOfCall, 
        [green]Case When A.Id Is NULL 
             Then 0 
             Else 1 
             End As NewUser,[/green]
        [red]IsNull(Sign(A.Id), 0) As NewUser[/red]
From    Table As T
        Left Join (
[blue]          Select Id, 
                 SurName, 
                 Min(Date) As MinDate, 
                 'InComing' As TypeOfCall
          From   Table
          Where  TypeOfCall = 'Incoming'
          Group By Id, SurName[/blue]
          ) As A
          On T.Id = A.Id
          And T.SurName = A.SurName
          And T.TypeOfCall = A.TypeOfCall
          And T.Date = A.MinDate

Notice the part in blue. It's the same query, but it's now part of a larger query that you can use to get the new user registration information.

Now, notice the part in green. That's a case/when statment. Because of the left join, non-matching rows will have NULL for the right table (in this case, the subquery). So, when there is no match, we get null, and the case/when is testing for nulls (and returning 0 when the value is null, otherwise it returns a 1).

Now, notice the part in red. It effectively does the same thing as the case/when, but it uses IsNull and Sign. Sign will return 1 for positive numbers, 0 for 0, and -1 for negative numbers. Assuming that your ID's will always be positive integers greater than 0, you can use the sign function to convert those values to 1. Again, since we are left joining on the table, the value for non-matching rows will be NULL, so sign will return NULL. So, we put the sign function within an IsNull function, so that when the value is NULL, 0 will be returned.

Does this help? I hope so, but if you have any questions (to help you understand the query), feel free to ask.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I think you want to join to a subquery. I don't know that this exact syntax will work, but I think it will.

Select a.NameID, a.Surname, a.Date, a.TypeofCall, 
case when b.date is null then 0
else 1 end as NewUser
FROM mytable a
left join
(select ID, min(date) from mytable group by ID) b
on a.ID = b.id
WHERE something = something

Basically, the idea is to display a 1 when the date is the minimum date (grouped by ID), and otherwise display a zero.

If this does not work, I am sure someone else will have a better idea.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Dah! At least I was right about someone having a better idea!

'morning George

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Alex,

You were on the right track. I bet that if you had the table, you would have come up with the right solution.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Yeah, I usually set up a test table but I was lazy this morning...

Just killing time while I waited for the plumber to fix our toilet. (SQL 2005 takes too long to fire up on my laptop, and I already had visual studio open so this was a no-go). Time to go in to work now though :-(

One question about your subquery, why do you select 'InComing' as TypeOfCall? Wouldn't your where clause cause the query to just return Incoming calls anyway (therefore you could just select TypeOfCall), or am I missing something?

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

The '1' only applies to Incoming calls. So... I wanted that as part of my subquery. I suppose that I could have returned the column instead of 'hard coding' it, but then I would have to add another condition to the group by. Faster! Slower! I dunno. It was the first thing that came to mind. I know that without joining on the 'incoming' calls, the results wouldn't be accurate.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Good stuff George, I bet it would be faster. I didn't think of any extra overhead associated with the group by. For this particular case it does seem like a good technique (especially if large recordsets are involved)

Ignorance of certain subjects is a great part of wisdom