SQL Server Performance

count once in multiple records

Discussion in 'General DBA Questions' started by Sidd, Jul 30, 2007.

  1. Sidd New Member

    sql gurus
    The situation is
    I have table where fields are:
    recdorddate smalldatetime ,
    recordtime varhchar(100) ,
    name varchar(100) ,
    Callnumber int ,
    repesentativeid int

    now this table has 13 million records every day.
    there are multiple records for each callnumber on the same day.
    only one of the records would be counted as "Distinguised Record"
    If name is called twice during day with different representative id we would count that as two records.
    Can you plz suggest me the best way to count the number of records only once every day
    where one record should match with recdorddate ,recordtime ,name ,Callnumber,repesentativeid
  2. satya Moderator

    Is that representativeid unique?
  3. Sidd New Member

    no it is not unique it repeats if the records for the same call number repeats.
    i have to count records for callnumber and each call is handles by one representative ..
    but if records repeat with same reresentativeid it is for the same callnumber.
    so please guide me soon


  4. Adriaan New Member

    Your basic syntax for getting the number of calls "per name, day and representative" is a pretty basic aggregate query:
    SELECT name, RecordDate, Representative, COUNT(*)
    FROM MyTable
    GROUP BY name, RecordDate, Representative
    What we don't know is whether RecordDate also includes the time of day. If it doesn't, or it is 00:00:00.000 on all rows, then your query is already done.
    If there is a time of day on RecordDate, then make two changes to the representation of RecordDate in the query:

    SELECT name, CONVERT(VARCHAR(10), RecordDate, 120), Representative, COUNT(*)
    FROM MyTable
    GROUP BY name, CONVERT(VARCHAR(10), RecordDate, 120), Representative
  5. Sidd New Member

    thanks a lot and i highly appreciate.
    actually can you tell me now if i want to count total with once occurance of each records please. where i cam match with
    name, CONVERT(VARCHAR(10), RecordDate, 120), Representative
  6. Adriaan New Member

    Sorry, can't make heads or tails of what you're asking ...
    "want to count total with once occurance of each records ... where i cam match with name, CONVERT(VARCHAR(10), RecordDate, 120), Representative"
    If you want to have the total listed at the end of the list, add WITH ROLLUP after the GROUP BY - check Books OnLine for complete details.
    If you want to see those where the COUNT(*) is 1, then add a HAVING clause after the GROUP BY. This is similar to a WHERE clause, but it allows you to filter on aggregate values.
    GROUP BY ...
    HAVING COUNT(*) = 1
  7. i2lovefishing New Member

    My 1st post. Not a sql guru, apparently.
    If Adrianan's suggestion is close to what you are looking for, I would expend on it to show -
    Select count(*) from (SELECT distinct name, CONVERT(VARCHAR(10), Recdorddate, 120), RepresentativeID FROM MyTable WHERE Recdorddate = '7/31/2007') as MyVirtualTable
    If following your thought of "... there are multiple records for each callnumber on the same day. only one of the records would be counted as 'Distinguised Record' ..." How about-
    SELECT count(distinct CallNumber) FROM MyTable WHERE Recdorddate = '7/31/2007'
  8. Adriaan New Member

    @ i2lovefishing - welcome aboard! Always good to see new people starting to respond to other people's questions. Like me, you don't need to be a SQL guru to reply to posts. And one does not become a guru by reason of the number of posts alone - plenty of opportunities for everyone!
  9. Sidd New Member

    thanks a lot to all .
    actually i am sorry i think i am able to explain my situation properly
    situation is:
    i have a table :
    where i have following fields:
    1.CustomerNumber varchar(16)
    2.RepresentativeID varchar(5)
    3.recordDate smalldatetime
    4.recordtime varchar(10)
    note: nothing is unique
    now i am trying to count number of calls.
    in that one customer can call more than one time in a day.
    moreover if same call is long it might shows two or three records with same representativeid.

    so how i can count number of calls for example for '07/31/07'
    so that if the customer has called again should be counted as second record.
    and if the records exist with same customerNumber and representativeid it should be counted as one.
    please help me with this i have less time.
    thanks a lot to everyone once again.
  10. i2lovefishing New Member

    Adriaan, Thank you for the kind words.
    Sidd,
    if a table ZCustomerCallRecord only has 4 fields, with data as -
    CustomerNumber RepresentativeID RecordDate Recordtime
    1 101 2007-07-31 00:00:00 8:00
    2 102 2007-07-31 00:00:00 9:00
    3 105 2007-07-31 00:00:00 10:00
    1 101 2007-07-31 00:00:00 8:05
    1 101 2007-07-31 00:00:00 8:10
    1 105 2007-07-31 00:00:00 17:00
    2 103 2007-07-31 00:00:00 9:05

    The following script will give you the call count (5) for the date of 7/31/2007.
    select count(*) from (select distinct CustomerNumber, RepresentativeID, CONVERT(CHAR(10), RecordDate, 120) as rd from ZCustomerCallRecord) as MyVirtualTable
  11. Adriaan New Member

    "now i am trying to count number of calls.
    in that one customer can call more than one time in a day."
    > So far, so good.
    "moreover if same call is long it might shows two or three records with same representativeid."
    > If it is a single call, then there should be just one row, not two or three. This does not make sense, and it's probably the cause of your problem.
    "so how i can count number of calls for example for '07/31/07' so that if the customer has called again should be counted as second record."
    > With the multiple rows per call, this is exactly the results that you get for COUNT(*), with the GROUP BY CustomerNumber, RepresentativeID, RecordDate clause
    "and if the records exist with same customerNumber and representativeid it should be counted as one."
    > We're already doing that - the only thing is that now you're not interested in the number of rows. Just drop the COUNT(*) expression.
  12. Sidd New Member

    yes, adriaan that is the problme i am facing.
    actually:
    1. yes as I mentioned before there can be more records with same customernumber and same representativeid with liitle diiference of time.
    I am trying to count number of calls made by the customers to represtative. as i mentioned one customer can call more than once.
    yes, might be count on records with combination of (same customer number and repid) can be counted as one.
    but du you think
    select count(*) from (select distinct CustomerNumber, RepresentativeID, CONVERT(CHAR(10), RecordDate, 120) as rd from ZCustomerCallRecord) as MyVirtualTable
    will give the count of records with situation:
    situation is:
    i have a table :
    where i have following fields:
    1.CustomerNumber varchar(16)
    2.RepresentativeID varchar(5)
    3.recordDate smalldatetime
    4.recordtime varchar(10)
    note: nothing is unique
    now i am trying to count number of calls.
    in that one customer can call more than one time in a day.
    moreover if same call is long it might shows two or three records with same representativeid.

    so how i can count number of calls for example for '07/31/07'
    so that if the customer has called again should be counted as second record.
    and if the records exist with same customerNumber and representativeid it should be counted as one.
    please help me with this i have less time.
    thanks a lot to everyone once again


  13. Adriaan New Member

    Can you give us some examples of your actual data, with the expected results that you want to get from your query.
  14. Sidd New Member

    here is the example :
    suppose i have data set as


    CustomerNUmber RepresentativeNo Rectime
    1 99 221505
    1 99 221855
    2 100 102545
    3 99 074530
    3 102 090608
    3 102 091410
    4 54 104645
    5 89 070906
    5 89 071509
    5 107 060409
    in rectime format is hhmmss (h -hour , m- minutes , s-second)
    Now in the above situation total count should be =7
    now the question i am trying to aks is that how i get 7
    what query will give me count = 7 so that same call should not be counted again.
  15. Adriaan New Member

    To get the number of distinct combinations of (CustomerNumber, RepresentativeNo) for a given RecordDate, step 1 is to set up a query to get the distinct combinations:
    SELECT CustomerNumber, RepresentativeNo
    FROM MyTable
    GROUP BY CustomerNumber, RepresentativeNo
    HAVING RecordDate = @MyDate
    The next step is to get the row count for this query, by using it as a derived table:
    SELECT COUNT(*) FROM
    (SELECT CustomerNumber, RepresentativeNo
    FROM MyTable
    GROUP BY CustomerNumber, RepresentativeNo
    HAVING RecordDate = @MyDate) AS X
  16. Sidd New Member

    Thanks a lot adriann but it is not working or what i described you.
    i haved pasted the example down again. please let me know if possible .
    CustomerNUmber RepresentativeNo Rectime
    1 99 221505
    1 99 221855
    2 100 102545
    3 99 074530
    3 102 090608
    3 102 091410
    4 54 104645
    5 89 070906
    5 89 071509
    5 107 060409
    in rectime format is hhmmss (h -hour , m- minutes , s-second)
    Now in the above situation total count should be =7
    now the question i am trying to aks is that how i get 7
    what query will give me count = 7 so that same call should not be counted again.
  17. Adriaan New Member

    I added a variable @MyDate into which you should put the date that you want.
    DECLARE @MyDate DATETIME
    SET @MyDate = '2007-07-31'
    SELECT COUNT(*) FROM
    (SELECT CustomerNumber, RepresentativeNo
    FROM MyTable
    GROUP BY CustomerNumber, RepresentativeNo
    HAVING RecordDate = @MyDate) AS X
  18. i2lovefishing New Member

    Or, try -
    DECLARE @MyDate DATETIME
    SET @MyDate = '2007-07-31'
    SELECT COUNT(*) FROM
    (SELECT CustomerNumber, RepresentativeNo
    FROM MyTable
    WHERE RecordDate = @MyDate
    GROUP BY CustomerNumber, RepresentativeNo
    ) AS X
    [quote user="Adriaan"]
    I added a variable @MyDate into which you should put the date that you want.
    DECLARE @MyDate DATETIME
    SET @MyDate = '2007-07-31'
    SELECT COUNT(*) FROM
    (SELECT CustomerNumber, RepresentativeNo
    FROM MyTable
    GROUP BY CustomerNumber, RepresentativeNo
    HAVING RecordDate = @MyDate) AS X
    [/quote]
  19. Sidd New Member

    Dear friends
    the query you sent is not working for the situation i am trying to describe in front of you.
    I haved pasted the example down again. please let me know if possible .
    CustomerNUmber RepresentativeNo Rectime
    1 99 221505
    1 99 221855
    2 100 102545
    3 99 074530
    3 102 090608
    3 102 091410
    4 54 104645
    5 89 070906
    5 89 071509
    5 107 060409
    in rectime format is hhmmss (h -hour , m- minutes , s-second)
    Now in the above situation The total count of records should be =7
    now the question i am trying to aks is that how i get 7
    what query will give me count = 7 so that same call should not be counted again.

Share This Page