AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of al4629740
al4629740
Flag for United States of America asked on

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
Microsoft SQL Server
Avatar of undefined
Last Comment
al4629740
8/22/2022 - Mon
PortletPaul
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

al4629740
ASKER
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.
PortletPaul
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
al4629740
ASKER
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
PortletPaul
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).
al4629740
ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul
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

al4629740
ASKER
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

PortletPaul
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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
PortletPaul
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
al4629740
ASKER
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

al4629740
ASKER
Excellent!  Thank you!
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent