Wednesday, 12 June 2013

stored procedure for counting Records


Create Procedure [dbo].[Buddy_DashBoard]
as
/*
exec Buddy_DashBoard
*/
begin


declare @Buddy_Total_Android int,@Buddy_Month_Android int,@Buddy_Week_Android int,@Buddy_Day_Android int

---To Get Android Users Data for dashBoard
select @Buddy_Total_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where BUD.User_From='Android'
select @Buddy_Month_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where MONTH(BUD.CreatedDate)=MONTH(dateadd(hh,5,dateadd(mi,30,getutcdate()))) and YEAR(BUD.CreatedDate)=YEAR(dateadd(hh,5,dateadd(mi,30,getutcdate()))) and BUD.User_From='Android'
select @Buddy_Week_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where CONVERT(date,BUD.CreatedDate,103) between CONVERT(date,dateadd(d,-7,dateadd(hh,5,dateadd(mi,30,getutcdate()))),103) and CONVERT(date,dateadd(hh,5,dateadd(mi,30,getutcdate()))) and BUD.User_From='Android'
select @Buddy_Day_Android=COUNT(*) from dbo.Buddy_tracker_User_Details BUD
where CONVERT(date,BUD.CreatedDate,103)=CONVERT(date,dateadd(hh,5,dateadd(mi,30,getutcdate())),103) and BUD.User_From='Android'


select @Buddy_Day_Android as Buddy_Day_Android,@Buddy_Week_Android as Buddy_Week_Android,@Buddy_Month_Android as Buddy_Month_Android,@Buddy_Total_Android as Buddy_Total_Android

end

No comments:

Post a Comment