query by Date in table

Here are the columns of my table (tblHours)

HourID
Agency
AgencyID
ActivityID
ActivityDate
Hours


I would like to output the number of days in a selected month that every unique activityID has had more than 0 hours in SQL

Output would look like this

ActivityID       Activity Date          Hours
3                         1/1/18                   1
3                         1/2/18                   3
3                          1/3/18                  1
54                        1/1/18                  1
54                        1/2/18                 4
34                        1/1/18                 4
34                         1/2/18                2
23                           1/1/18             2
23                          1/2/18               2

Could you provide the SQL string that would accomplish this?
al4629740Asked:
Who is Participating?
 
Bill PrewCommented:
You didn't provide any feedback to my original suggestion, but based on your data sample, the following seems to work.

SELECT h.* 
FROM   (SELECT ActivityID, 
               ActivityDate, 
               Sum(Hours) AS 'Hours' 
        FROM   tblHours 
        GROUP  BY ActivityID, 
                  ActivityDate) AS h 
WHERE  h.Hours > 0 
ORDER  BY h.ActivityID, 
          h.ActivityDate; 

Open in new window

Results from test data:
sshot-382.png
»bp
0
 
Bill PrewCommented:
I would like to output the number of days in a selected month that every unique activityID has had more than 0 hours

Can you expand on this a bit?  You state you want to output the number of days, but your example output shows number of hours??

Are you saying you just want exclude rows with 0 hours, if so then a simple SUM() would do it.

SELECT ActivityID, ActivityDate, SUM(Hours) AS 'Hours'
FROM table1
GROUP BY ActivityID, ActivityDate
ORDER BY ActivityID, ActivityDate

Open in new window



»bp
0
 
al4629740Author Commented:
this starts me off right.  Thanks
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.

 
Scott PletcherSenior DBACommented:
You can explicitly check for total hours <> 0 by adding a HAVING clause:

...
GROUP BY ...
HAVING SUM(HOURS) <> 0
ORDER BY ...
0
 
Mark WillsTopic AdvisorCommented:
So, you still want to see the individual rows as well ?

with CTE_Activity as
( SELECT ActivityID, FORMAT(ActivityDate,'yyyyMM') as ActivityMonth, SUM(Hours) AS ActivityHours
  FROM Your_Table
  GROUP BY ActivityID, FORMAT(ActivityDate,'yyyyMM')
) Select T.*
  from Your_Table T
  Inner Join CTE_Activity A on T.ActivityID = A.activityID 
  where A.ActivityHours > 0

Open in new window

Probably a few typo's because just typed it in - no testing as yet, but will test in a couple of hours.....
0
 
Mark WillsTopic AdvisorCommented:
Back now, and have been reading your question (a few times).

Bit confused about the requirement. Are you saying :

1) For a unique Activity ID, hours in the month must be > 0
2) You want to see individual rows that make up that ActivityID
3) If an individual row has zero hours, report it anyway because the total hours for the Activity ID is > zero
4) just show activityID rows where hours > 0
5) you want a function where you can specify month, or , provide the output by month.
6) what does " number of days" mean - a count of separate days where activity hours > 0
7) does activity ID pan across multiple HourID, Agency, AgencyId
8) In your output example, the column 'Activity Date' is really the month ? ie d/m/y format ?
9) What version of SQL Server are you running ?

so... Depending on clarifications above, and backing up my original promise to test later...

My original post probably wont work, because of point 7 above - the activity ID in the join will bring back duplicated rows.

And depending on clarifications above (primarily point 8) this may also work
select ActivityID, Format(ActivityDate,'01\/MM\/yyyy') as ActivityMonth, SUM(Hours) as Month_Hours, count(distinct cast(activityDate as date)) as Number_Of_Days
from #tblHours
where [hours] > 0                                          -- if individual hours > 0 then SUM(Hours) must be, or, comment out this 'where' and remove he comment (ie --) from the Having below
Group by ActivityID, Format(ActivityDate,'01\/MM\/yyyy')
-- Having SUM(Hours) > 0
Order by 1,2

Open in new window


Any way, if you can please clarify your requirements by answering as many of the points above, we can then provide a working solution...
0
 
al4629740Author Commented:
Here we go

1) For a unique Activity ID, hours in the month must be > 0
Yes

2) You want to see individual rows that make up that ActivityID
Yes

3) If an individual row has zero hours, report it anyway because the total hours for the Activity ID is > zero
No

4) just show activityID rows where hours > 0
Yes

5) you want a function where you can specify month, or , provide the output by month.
Yes

6) what does " number of days" mean - a count of separate days where activity hours > 0
No.  Just want each day for each activity in a separate row

7) does activity ID pan across multiple HourID, Agency, AgencyId
Don't understand the question

8) In your output example, the column 'Activity Date' is really the month ? ie d/m/y format ?
Its showing whats in that column ActivityDate

9) What version of SQL Server are you running ?
2012
0
 
Mark WillsTopic AdvisorCommented:
>> 7) does activity ID pan across multiple HourID, Agency, AgencyId

What I meant was the same ActivityID's can occur for combinations of AgencyID / HourID - we dont need to filter (or group by) AgencyID - do we ?

so when I linked back to tblhours in my original post, it would pick up multiple duplications.

Example : ActivtyID 1 exists for Agency A and Agency B and Agency C, all on the same day (at different times), all with 1 hour.

So ActivityID 1 for agency A would also show ActivityID 1 for agency B and agency C - which in turn - would also show the other agency's ActivityID 1 resulting in 9 rows, not 3 rows.  But we can accommodate by selecting distinct rows, then sum(hours) for activityID + Date

So, does this give you the rows you want
;WITH CTE_Detail AS
( Select HourID,Agency,AgencyID, ActivityID, ActivityDate, [Hours] 
         --,sum([Hours]) over (partition by ActivityID, format(ActivityDate,'MMyyyy')) as MonthlyHours         -- dont think this is needed
  from #tblHours 
  where [Hours] > 0
) Select ActivityID, Format(ActivityDate,'MM\/dd\/yyyy') as ActivityDate, SUM(Hours) as Hours
  from CTE_Detail
  --where MonthlyHours > 0                                        -- dont think this is needed
  Group by ActivityID, Format(ActivityDate,'MM\/dd\/yyyy')
  Order by 1,2

Open in new window

0
 
al4629740Author Commented:
Yes, I would want to separate AgencyID so that ActivityID 1 would result in 3 different records.  The example of resulting 9 rows would be correct
0
 
Mark WillsTopic AdvisorCommented:
OK then I think we now need some sample data and desired output....

What datatype is avtivitydate ?
0
 
al4629740Author Commented:
datetime

I gave the desired output in the original question...  

Some sample data:

HourID	Agency	AgencyID	Program	ActivityID	RegID	ActivityDate	Hours
180	Administrator	74	CSW	3	35	7/3/2014	1
181	Administrator	74	CSW	3	9	7/3/2014	1
183	Administrator	74	CSW	3	6	7/3/2014	1
187	Administrator	74	CSW	3	1	7/3/2014	1
190	Administrator	74	CYD	4	8	7/6/2014	1
193	Administrator	74	CSW	3	6	7/6/2014	4
196	Administrator	74	CYD	3	35	7/7/2014	1
199	Administrator	74	CYD	3	14	7/7/2014	1
200	Administrator	74	CYD	3	4	7/7/2014	1
202	Administrator	74	CYD	3	1	7/7/2014	1
203	Administrator	74	CYD	3	8	7/7/2014	1
204	Administrator	74	CYD	3	6	7/7/2014	1

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Noticed two new columns, Program and RegID are they significant ?

Did you try my last post ? That does give the desired output (format) - just need to check data and selections to be working (or not).
;WITH CTE_Detail AS
( Select HourID,Agency,AgencyID, ActivityID, ActivityDate, [Hours]                                             -- might cast(activitydate as date) as activitydate
  from tblHours 
  where [Hours] > 0
) Select ActivityID, Format(ActivityDate,'MM\/dd\/yyyy') as ActivityDate, SUM(Hours) as Hours
  from CTE_Detail
  Group by AgencyID, ActivityID, Format(ActivityDate,'MM\/dd\/yyyy')
  Order by ActivityID, ActivityDate

Open in new window

Please give it a try and give me feedback about the SQL and the new columns.
Otherwise, we will just keep "guessing" as to what we all need to do next. Need your input.
0
 
Mark WillsTopic AdvisorCommented:
Bill, The reason I deviated from that approach is suspicion of (and subsequently confirmed) activitydate is a datetime. Even though the test data seems to be formatted in a display context. Hence my use of FORMAT(). Was using cast(activitydate as date) for a while.

I already posed "different times",  and (I believe) had confirmation that 3 agencies, same activityID, same day, different times would yield 3 rows. Assumed that was agency rather then time, because time has never been shown.

Still unsure of wanting all the detail rows being returned with the addition of two new columns.


Good luck.
1
 
al4629740Author Commented:
Thanks Mark.  I appreciate that.
1
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.

All Courses

From novice to tech pro — start learning today.