I have a table of questions. The questions can be high priority, low priority, open/answered. What is the best way to create a summary. Should I use a view or several stored procedures?
CREATE TABLE [dbo].[question] ( [Id] INT NOT NULL, [Priority] INT NOT NULL, [State] INT NOT NULL)
I think creating a view with 4 columns is the best: OpenQuestionHighPriority, OpenQuestionLowPriority, ClosedQuestionHighPriority,ClosedQuestionLowPriority.
Like to know how to do this or other suggestions.
You could query that with a
select sum(case when priority = 1 and state = 1 then 1 end) as OpenQuestionHighPriority , sum(case when priority = 1 and state = 2 then 1 end) as ClosedQuestionHighPriority .... from dbo.question
You could store this query in a view, a procedure, or execute it as ad-hoc SQL. It will work in either form.