Ok, I'm trying to make an indexed view that is against a simple table that stores the results of what people think is good/bad for a post. This is the results of a thumbs up / thumbs down, voting on posts.
So here's my pseduo fake table :-
HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PostId INT NOT NULL,
IsHelpful BIT NOT NULL,
UserId INT NOT NULL
So a user can only have one vote per post. It's either a 1 (helpful) or 0 (unhelpful) <-- not sure of a better way to handle that, if there is a better way.
Ok. What i'm trying to do is get a view that looks like the following.
HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PostId INT NOT NULL,
IsHelpfulCount COUNT_BIG (WHERE IsHelpful = 1)
IsNotHelpfulCount COUNT_BIG (WHERE IsHelpful = 0)
And finally, i'll need to make it schemabindable so i can add an index on the PK and then an index on the PostId.
I have no idea about the sql to make the view. Any suggestions?
Cheers :)