Thursday, December 15, 2011

Show the lastweek, Today, Mostvotes, Leastvotes,Noofvotes using a sp in Sql Server


USE [db1]
GO

/****** Object:  StoredProcedure [dbo].[THRSP_ShowListOfIdea]    Script Date: 12/16/2011 10:34:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[THRSP_ShowListOfIdea]
(
@Showme varchar(20)
)
AS
IF(@Showme='Last Week')
BEGIN
declare @fromdate datetime;
declare @enddate datetime;
set @fromdate=(select convert(datetime, (convert (varchar(10), getdate(),101))))
set @enddate=(select convert(datetime, (convert (varchar(10), getdate()-7,101))))
select Title,Description from Idea where CreatedOn between  @enddate and @fromdate
END

ELSE IF(@Showme='Today')
BEGIN
Declare @today datetime;
set @today =(select convert(datetime, (convert (varchar(10), getdate(),101))))
SELECT i.Title, i.Description from idea i INNER JOIN votes v on i.ideaid=v.ideaid where
(year (v.VotedOn)=year(@today)and  month (v.VotedOn)=month(@today)and  day (v.VotedOn)=day(@today))
END

ELSE IF(@Showme='Most votes')
BEGIN
select * from (
SELECT i.Title, i.Description
,(select COUNT(ideaid) from Votes where ideaid=v.ideaid)as ideacount
from idea i INNER JOIN votes v on i.ideaid=v.ideaid ) as tempTable
order by ideacount desc
END

ELSE IF(@Showme='Least Votes')
BEGIN
select * from (
SELECT i.Title, i.Description
,(select COUNT(ideaid) from Votes where ideaid=v.ideaid)as ideacount
from idea i INNER JOIN votes v on i.ideaid=v.ideaid ) as tempTable
order by ideacount ASC
END
ELSE IF(@Showme='Number Of Votes')
BEGIN
SELECT i.Title, i.Description from idea i INNER JOIN votes v on i.ideaid=v.ideaid
END

GO


No comments:

Post a Comment