Help with View

I have a view that returns, amount other things, category and Hours. See attached.  I would like to create another view the uses this view as input, but that adds the hours together for certain categories.  

For example, see the 2 in red - "Annual: Harassment Prevention" and "Annual: ACLSA".  Now there can also be records that correspond like - "Req: Harassment Prevention" and "Req: ACLSA".

I need to add together the hours of the Annual and Req records and end up with one record with combined hours and a category of "Harassment Prevention" and "ACLSA" for this example.

In general, all the records where the category begins with "Annual:" might have a corresponding record where the category begins with "Req:" and I need to combine them.
hours.png
LVL 1
HLRosenbergerAsked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Any chance you can create a 'parent category' table that has groups of categories per your requirements above, then create another view that groups on that?
0
HLRosenbergerAuthor Commented:
I can create another table.  But how does that help?   What would be in that table?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If the view in the .png has Category, then it likely has access to a CategoryID, which would be the 'child' record in a 'parent category' table.  

You could re-create the view in the .png, then join on the 'parent category' table, and SUM() the hours.  You'd have to figure out what you want to do with the date, if the sum's are grouped on that date, or just allow for a date range, or whatever.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

HLRosenbergerAuthor Commented:
actually, there is not a parent table with a category ID.  It's based on a convoluted third-party product that allows for user defined data fields.  The categories are just text, with no primary key.
0
PortletPaulfreelancerCommented:
I believe the point Jim is making is that IF the mapping of Category to the 'higher level' existed in a table, then this query will become quite simple, e.g.
CREATE TABLE YourView
	([Date] datetime, [Hours] int, [Category] varchar(29))
;
	
INSERT INTO YourView
	([Date], [Hours], [Category])
VALUES
	('2012-03-26 00:00:00', 1.0, 'Annual: Harassment Prevention'),
	('2012-03-08 00:00:00', 2.0, 'Annual: ACLSA')
;


CREATE TABLE CategoryMapping
	([Category] varchar(29), [Grouping] varchar(26))
;
	
INSERT INTO CategoryMapping
	([Category], [Grouping])
VALUES
	('Annual: Harassment Prevention', 'Req: Harassment Prevention'),
	('Annual: ACLSA', 'Req: Harassment Prevention')
;

Open in new window

Here I've mimicked just a tad of your data, but the "mapping" is the important bit, so to produce the wanted result could be achieved by:
SELECT
        m.grouping
      , sum(yv.hours) AS sum_hours
FROM YourView AS YV
INNER JOIN CategoryMapping AS M ON YV.category = M.category
GROUP BY
        m.grouping
	

Open in new window

see this working: http://sqlfiddle.com/#!3/92227f/3
The efficiency of this would be affected by scale and how information is or isn't indexed - but hopefully you can see the point about that mapping table.
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
HLRosenbergerAuthor Commented:
thanks
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 2008

From novice to tech pro — start learning today.