Post reply

Select Count Help

  • July 2, 2010 at 4:21 pm

    #220666

    Hello again,

    I have a client, location and service set of tables. They are easily joined to create a query to return all three. Now what I would like to do is count all the 'trips' spent doing a particular service for a location & client from a 4th table (@ServiceDates).

    'Trips': same Date and same userName entered on multiple rows = 1 trip

    different Date or different userName = count of rows

    So a SELECT count(*)

    FROM (SELECT DISTINCT Date , userName

    FROM @ServiceDates sd

    WHERE service/location/client = what is in the first 2 columns) trips scenario is what I am looking for however I want to return it as the next column in my select statement. How do I do this?

    Once I have trips I also want to do something similar by summing the number of hours spent for that service/location/client regardless of trips.

    Below are some sample tables. The @Results is what I would like to return.

    DECLARE @Location TABLE (LocationID int, ClientID int)

    INSERT INTO @Location

    SELECT 1, 234 UNION ALL

    SELECT 2, 234 UNION ALL

    SELECT 3, 234 UNION ALL

    SELECT 4, 200

    DECLARE @Serivce TABLE (ServiceID int, LocationID int)

    INSERT INTO @Serivce

    SELECT 10, 1 UNION ALL

    SELECT 11, 1 UNION ALL

    SELECT 12, 2 UNION ALL

    SELECT 13, 3

    DECLARE @ServiceDates TABLE (ServiceID int,

    numHours int, Date datetime, UserName nchar(2))

    INSERT INTO @ServiceDates

    SELECT 10,2,'6/3/2010', 'ZC' UNION ALL

    SELECT 10,15,'6/3/2010', 'ZC' UNION ALL

    SELECT 11,3,'6/4/2010', 'BG' UNION ALL

    SELECT 12,8,'6/4/2010', 'BG' UNION ALL

    SELECT 12,2,'6/4/2010', 'ZC' UNION ALL

    SELECT 10,7,'6/5/2010', 'BG' UNION ALL

    SELECT 13,4,'6/2/2010', 'BG' UNION ALL

    SELECT 11,1,'6/5/2010', 'CC' UNION ALL

    SELECT 11,5,'6/5/2010', 'BG'

    DECLARE @Results TABLE (ClientID int, LocationID int,

    ServiceID int, numTrips int, totalnumHours int)

    INSERT INTO @Results

    SELECT 234,1,10,2,24 UNION ALL

    SELECT 234,1,11,3,9 UNION ALL

    SELECT 234,2,12,2,10 UNION ALL

    SELECT 234,3,13,1,4

    Select * from @results

    Thanks again

    Zane

  • July 3, 2010 at 2:50 am

    #1188490

    try this -

    SELECT L.ClientID, S.LocationID, S.ServiceID, COUNT(D.ServiceID) numTrips, SUM(numHours) totalnumHours FROM @Location L INNER JOIN @Service S ON L.LocationID = S.LocationID

    INNER JOIN @ServiceDates D ON S.ServiceID = D.ServiceID

    GROUP BY L.ClientID, S.LocationID, S.ServiceID

    but i m surprised that how serviceid 10 having numtrips is 2.

    please explain or its typing error as it should be 3

    Vaibhav K Tiwari
    To walk fast walk alone
    To walk far walk together

  • July 3, 2010 at 9:18 am

    #1188523

    Thanks for the reply, I am out of the office until Tuesday but I will let you know how that works. The count of ServiceID 10 should be 2 because it is the same user on the same day... that is where it gets tricky.

  • July 3, 2010 at 10:54 am

    #1188534

    I would precalculate the data per user and day using a CTE or subquery.

    ; WITH cte AS

    (

    SELECT ServiceID ,

    COUNT(DISTINCT DATE) AS cnt,

    SUM(numHours) AS subtotal

    FROM @ServiceDates

    GROUP BY ServiceID, UserName

    )

    SELECT L.ClientID, S.LocationID, S.ServiceID, SUM(cte.cnt) numTrips, SUM(subtotal) totalnumHours

    FROM @Location L

    INNER JOIN @Serivce S ON L.LocationID = S.LocationID

    INNER JOIN cte ON S.ServiceID = cte.ServiceID

    GROUP BY L.ClientID, S.LocationID, S.ServiceID


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 6, 2010 at 9:52 am

    #1189167

    Thanks Lutz!

    It works just as intended. One question, do you know where I can find some tutorials on how to use cte's? I don't exactly know how your code works and I just want to learn.

    Thanks

    Zane

  • July 6, 2010 at 10:00 am

    #1189172

    This link is an excellent place to start.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 6, 2010 at 10:10 am

    #1189180

    Thanks Wayne, if I would have known cte = common table expression I would have googled it myself.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply