distinct not needed? Hour SUM

In the following query I want to have a count of  distinct hours column for each Agency.  However, each agency shows up numerous times because of how I grouped the variables.  HELP.  I need a query that shows the distinct Agency with total sum of distinct hours


Here is what I have as the query.  I have attached an example of what my current output is and what I am trying to achieve.

DECLARE @fromdt AS datetime
                    DECLARE @todt AS datetime
                    SET @fromdt = '7/1/2014'
                    SET @todt = '7/31/2014'
                   SELECT  p.Agency, h.Activityid, h.HourTimeFrom,h.HourTimeTo,h.ActivityDate,  h.hours
                    FROM tblOrgProfile as p 
                    LEFT JOIN tblOrgRegistrations as r
                        ON p.AgencyID = r.AgencyID 

                    LEFT JOIN tblOrgHours as h 
                        ON h.RegID = r.regid 
                        AND h.ActivityDate >= @fromdt 
                        AND h.ActivityDate <= @todt 
                        AND h.Hours > 0 
          
    
                        Where p.Agency <> 'Administrator' 
                    GROUP BY p.Agency,h.Activityid,h.ActivityDate, h.HourTimeFrom,h.HourTimeTo,h.Hours
                    ORDER BY p.Agency

Open in new window

EXAMPLE.xlsx
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
For this result:
Agency                                       hours
Better Life for Youth                        6
Bishop Shepard Little Memorial Center, Inc.  1

Open in new window


You could use this query:
DECLARE @fromdt AS datetime
DECLARE @todt AS datetime
SET @fromdt = '7/1/2014'
SET @todt = '7/31/2014'
SELECT
    p.Agency
  , SUM(h.hours) AS sum_hours
FROM tblOrgProfile AS p
LEFT JOIN tblOrgRegistrations AS r
    ON p.AgencyID = r.AgencyID
LEFT JOIN tblOrgHours AS h
    ON h.RegID = r.regid
    AND h.ActivityDate >= @fromdt
    AND h.ActivityDate <= @todt
    AND h.Hours > 0
WHERE p.Agency <> 'Administrator'
GROUP BY
    p.Agency
ORDER BY
    p.Agency

Open in new window

0
al4629740Author Commented:
Paul,

I tried that, but the resulting hour total is wrong.

I need to GROUP BY p.Agency,h.Activityid,h.ActivityDate, h.HourTimeFrom,h.HourTimeTo,h.Hours in order to get the right list.  Then from this query list, I need the hours added up by Agency.
0
PortletPaulfreelancerCommented:
For the expected result provided in the spreadsheet (2 columns) that query works: It is conventional for the expected result to display all the requirements.

To display all the details AND produce a SUM() you need to use the OVER() clause:

       , SUM(h.hours) OVER(PARTITION BY p.Agency, h.Activityid) AS sum_hours

and do  NOT use GROUP BY

Please note I have guessed that the PARTITION BY is p.Agency, h.Activityid it may additional fields which you can add. Think of the 'partition by' as similar to GROUP BY

DECLARE @fromdt AS datetime
DECLARE @todt AS datetime
SET @fromdt = '7/1/2014'
SET @todt = '7/31/2014'
SELECT
    p.Agency
  , h.Activityid
  , h.HourTimeFrom
  , h.HourTimeTo
  , h.ActivityDate
  , h.hours
  , SUM(h.hours) OVER(PARTITION BY p.Agency, h.Activityid) AS sum_hours 
FROM tblOrgProfile AS p
LEFT JOIN tblOrgRegistrations AS r
    ON p.AgencyID = r.AgencyID

LEFT JOIN tblOrgHours AS h
    ON h.RegID = r.regid
    AND h.ActivityDate >= @fromdt
    AND h.ActivityDate <= @todt
    AND h.Hours > 0


WHERE p.Agency <> 'Administrator'

ORDER BY
    p.Agency

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

al4629740Author Commented:
Here is what I used:

DECLARE @fromdt AS datetime
DECLARE @todt AS datetime
SET @fromdt = '7/1/2014'
SET @todt = '7/31/2014'
SELECT
    p.Agency
  , h.Activityid
  , h.HourTimeFrom
  , h.HourTimeTo
  , h.ActivityDate
  , h.hours
  , SUM(h.hours) OVER(PARTITION BY p.Agency, h.Activityid, h.HourTimeFrom,h.HourTimeTo,h.ActivityDate) AS sum_hours 
FROM tblOrgProfile AS p
 JOIN tblOrgRegistrations AS r
    ON p.AgencyID = r.AgencyID

 JOIN tblOrgHours AS h
    ON h.RegID = r.regid
    AND h.ActivityDate >= @fromdt
    AND h.ActivityDate <= @todt
    AND h.Hours > 0


WHERE p.Agency <> 'Administrator'

ORDER BY
    p.Agency

   

Open in new window


Sample portion of the Output

Beacon Hill Community Improvement Assoc	289	12:00:00.0000000	17:00:00.0000000	2014-07-16	5.00	25.00
Beacon Hill Community Improvement Assoc	289	12:00:00.0000000	17:00:00.0000000	2014-07-16	5.00	25.00
Beacon Hill Community Improvement Assoc	289	12:00:00.0000000	17:00:00.0000000	2014-07-16	5.00	25.00
Beacon Hill Community Improvement Assoc	289	12:00:00.0000000	17:00:00.0000000	2014-07-16	5.00	25.00
Beacon Hill Community Improvement Assoc	289	12:00:00.0000000	17:00:00.0000000	2014-07-16	5.00	25.00

Open in new window


The problem is that the records are duplicated and not showing one per agency and activityid.  Hope there is a way to do it
0
PortletPaulfreelancerCommented:
If you want help to write a query to avoid repetition of data you should supply sample data PER TABLE

so, some rows for each of these tables
tblOrgProfile
tblOrgRegistrations
tblOrgHours

Then the expected result should show the complete & final result you want (derived from the sample you supplied).
0
al4629740Author Commented:
I have attached the three tables and the desired output in one excel sheet.

Let me clarify what I am exactly looking for on the output.  

HourID	Agency	AgencyID	Program	ActivityID	RegID	ActivityDate	Hours	HourTimeFrom	HourTimeTo	SubGroup	ParticipantHour	VolunteerHour	Fiscal	EntryTime
1298	Mid-Austin	48	CYD	88	80 7/8/2014	2	10:00:00 AM	12:00:00 PM		1	0	2015	43:29.0
1299	Mid-Austin	48	CYD	88	80 7/8/2014	2	10:00:00 AM	12:00:00 PM		1	0	2015	43:29.0
1300	Mid-Austin	48	CYD	88	80 7/8/2014	2	10:00:00 AM	12:00:00 PM		1	0	2015	43:29.0
1301	Mid-Austin	48	CYD	88	80 7/8/2014	2	10:00:00 AM	12:00:00 PM		1	0	2015	43:29.0

Open in new window


All these records have the same AgencyName and the same ActivityID and were from 10am to 12pm and they occurred on 7/8/2014.   Since they are all the same on those 5 factors, then the hour is counted as 1.

Normally since they occurred by different students, it would be counted as 4.  However, in this case the activity is unique to the hour count.  So the hour that needs to be calculated is one hour for this entire unique activity.

I am looking for the output to show the agency, activity and total hours based on the unique factors mentioned above.  

Ask me follow up questions if needed.
tables-and-desired-output.xlsx
0
PortletPaulfreelancerCommented:
Thanks the expanded information does help.

I suggest this:

select
      p.Agency, h.AgencyID, h.ActivityID, h.ActivityDate, h.HourTimeFrom, h.HourTimeTo, h.Hours
from (
      select
          row_number() over(partition by AgencyID, ActivityID, ActivityDate, HourTimeFrom, HourTimeTo
                            order by (select 1)) as rn
          , *
      from tblOrgHours
      ) AS h
inner join tblOrgProfile as p on h.AgencyID = p.AgencyID
where h.rn = 1
order by p.Agency, h.ActivityID, h.ActivityDate, h.HourTimeFrom, h.HourTimeTo
;

Open in new window


and the result, for the data I have used (which is different to the spreadsheet) is:
|     Agency | AgencyID | ActivityID |             ActivityDate | HourTimeFrom | HourTimeTo | Hours |
|------------|----------|------------|--------------------------|--------------|------------|-------|
| Mid-Austin |       48 |         88 | August, 07 2014 00:00:00 |      00:00.0 |    00:00.0 |     2 |
| Mid-Austin |       48 |         92 | August, 07 2014 00:00:00 |      00:00.0 |    00:00.0 |     1 |
| Mid-Austin |       48 |         98 | August, 07 2014 00:00:00 |      00:00.0 |    00:00.0 |     1 |

Open in new window


Details, including data
    CREATE TABLE tblOrgHours
        ([HourID] int, [Agency] varchar(10), [AgencyID] int, [Program] varchar(3)
         , [ActivityID] int, [RegID] int, [ActivityDate] datetime
         , [Hours] int, [HourTimeFrom] varchar(7), [HourTimeTo] varchar(7)
         , [SubGroup] varchar(4), [ParticipantHour] int, [VolunteerHour] int
         , [Fiscal] int, [EntryTime] varchar(7))
    ;
        
    INSERT INTO tblOrgHours
        ([HourID], [Agency], [AgencyID], [Program], [ActivityID], [RegID], [ActivityDate], [Hours], [HourTimeFrom], [HourTimeTo], [SubGroup], [ParticipantHour], [VolunteerHour], [Fiscal], [EntryTime])
    VALUES
        (1298, 'Mid-Austin', 48, 'CYD', 88, 80, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1299, 'Mid-Austin', 48, 'CYD', 88, 81, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1300, 'Mid-Austin', 48, 'CYD', 88, 82, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1301, 'Mid-Austin', 48, 'CYD', 88, 83, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1302, 'Mid-Austin', 48, 'CYD', 88, 84, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1303, 'Mid-Austin', 48, 'CYD', 88, 218, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1304, 'Mid-Austin', 48, 'CYD', 88, 86, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1305, 'Mid-Austin', 48, 'CYD', 88, 89, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1306, 'Mid-Austin', 48, 'CYD', 88, 249, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1307, 'Mid-Austin', 48, 'CYD', 88, 248, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1308, 'Mid-Austin', 48, 'CYD', 88, 71, '2014-08-07 00:00:00', 2, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '43:29.0'),
        (1309, 'Mid-Austin', 48, 'CYD', 98, 80, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1310, 'Mid-Austin', 48, 'CYD', 98, 81, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1311, 'Mid-Austin', 48, 'CYD', 98, 82, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1312, 'Mid-Austin', 48, 'CYD', 98, 83, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1313, 'Mid-Austin', 48, 'CYD', 98, 84, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1314, 'Mid-Austin', 48, 'CYD', 98, 218, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1315, 'Mid-Austin', 48, 'CYD', 98, 86, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1316, 'Mid-Austin', 48, 'CYD', 98, 89, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1317, 'Mid-Austin', 48, 'CYD', 98, 249, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1318, 'Mid-Austin', 48, 'CYD', 98, 248, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1319, 'Mid-Austin', 48, 'CYD', 98, 71, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '48:03.0'),
        (1320, 'Mid-Austin', 48, 'CYD', 92, 80, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '49:36.0'),
        (1321, 'Mid-Austin', 48, 'CYD', 92, 218, '2014-08-07 00:00:00', 1, '00:00.0', '00:00.0', NULL, 1, 0, 2015, '49:36.0')
    ;
    
    
    CREATE TABLE tblOrgProfile	
        ([AgencyID] int, [Agency] varchar(120))
    ;
        
    INSERT INTO tblOrgProfile	
        ([AgencyID], [Agency])
    VALUES
        (1, 'A Knock at Midnight'),
        (2, 'Adler School, The'),
        (3, 'Alliance for Community Peace'),
        (4, 'Alternatives, Inc.'),
        (5, 'Amer-I-Can'),
        (6, 'Ark of Saint Sabina & ERC'),
        (7, 'Beacon Hill Community Improvement Assoc'),
        (48, 'Mid-Austin')
    ;
    
**Query 1**:

    select
          p.Agency, h.AgencyID, h.ActivityID, h.ActivityDate, h.HourTimeFrom, h.HourTimeTo, h.Hours
    from (
          select
              row_number() over(partition by AgencyID, ActivityID, ActivityDate, HourTimeFrom, HourTimeTo
                                order by (select 1)) as rn
              , *
          from tblOrgHours
          ) AS h
    inner join tblOrgProfile as p on h.AgencyID = p.AgencyID
    where h.rn = 1
    order by p.Agency, h.ActivityID, h.ActivityDate, h.HourTimeFrom, h.HourTimeTo
    

**[Results][2]**:
    |     Agency | AgencyID | ActivityID |             ActivityDate | HourTimeFrom | HourTimeTo | Hours |
    |------------|----------|------------|--------------------------|--------------|------------|-------|
    | Mid-Austin |       48 |         88 | August, 07 2014 00:00:00 |      00:00.0 |    00:00.0 |     2 |
    | Mid-Austin |       48 |         92 | August, 07 2014 00:00:00 |      00:00.0 |    00:00.0 |     1 |
    | Mid-Austin |       48 |         98 | August, 07 2014 00:00:00 |      00:00.0 |    00:00.0 |     1 |

http://sqlfiddle.com/#!6/9c96a/1

Open in new window

0
al4629740Author Commented:
We are getting closer...much closer.

If the result is

    |     Agency | AgencyID | ActivityID |             ActivityDate | HourTimeFrom | HourTimeTo | Hours |
    |------------|----------|------------|--------------------------|--------------|------------|-------|
    | Mid-Austin |       48 |         88 | August, 07 2014 00:00:00 |      00:00.0 |    00:00.0 |     2 |
    | Mid-Austin |       48 |         88| August, 08 2014 00:00:00 |      00:00.0 |    00:00.0 |     1 |

Open in new window


Can it consolidate the Activity by adding the hours as below if the ActivityDate ,HourTimeFrom, HourTimeTo  are the same?


    |     Agency | AgencyID | ActivityID | HourTimeFrom | HourTimeTo | Hours |
    |------------|----------|------------|--------------------------|--------------|------------|-------|
    | Mid-Austin |       48 |         88  |      00:00.0 |    00:00.0 |     3 |
 

Open in new window

0
PortletPaulfreelancerCommented:
Yes, group by and a SUM() can be introduced, like so:
select
      p.Agency, h.AgencyID, h.ActivityID, h.ActivityDate, h.HourTimeFrom, h.HourTimeTo, SUM(h.Hours) as Hours
from (
      select
          row_number() over(partition by AgencyID, ActivityID, ActivityDate, HourTimeFrom, HourTimeTo
                            order by (select 1)) as rn
          , *
      from tblOrgHours
      ) AS h
inner join tblOrgProfile as p on h.AgencyID = p.AgencyID
where h.rn = 1
group by p.Agency, h.ActivityID, h.ActivityDate, h.HourTimeFrom, h.HourTimeTo
order by p.Agency, h.ActivityID, h.ActivityDate, h.HourTimeFrom, h.HourTimeTo
;

Open in new window

0
PortletPaulfreelancerCommented:
hold on.... remove h.ActivityDate from that query in lines 2, 12 & 13

to get this output do NOT group by or output the h.ActivityDate column
   |     Agency | AgencyID | ActivityID | HourTimeFrom | HourTimeTo | Hours |
    |------------|----------|------------|--------------------------|--------------|------------|-------|
    | Mid-Austin |       48 |         88  |      00:00.0 |    00:00.0 |     3 |

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I made a few mods and it worked perfectly.

select
      p.Agency,a.ActivityName, h.ActivityID, SUM(h.Hours) as Hours
from (
      select
          row_number() over(partition by AgencyID, ActivityID, ActivityDate, HourTimeFrom, HourTimeTo
                            order by (select 1)) as rn
          , *
      from tblOrgHours
      ) AS h
 join tblOrgProfile as p on h.AgencyID = p.AgencyID
 join tblOrgActivities as a on a.ActivityID = h.ActivityID
where h.rn = 1 and p.AgencyID <> 74
group by p.Agency, h.ActivityID, a.ActivityName
order by p.Agency, h.ActivityID, a.ActivityName

Open in new window

0
al4629740Author Commented:
Excellent!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.