Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
Hi everyone,
I provide my stored procedure. My problem is that I want to add one more column. Look at this.
alter PROCEDURE [dbo].[GetFacebookGeneralStatistics]
	
	 @FDate int
	,@TDate int
	,@Page int
	
AS
BEGIN
	
	declare @tbl1 table(PageId int,PageName varchar(50),[Month] varchar(20),[Total Likes] int,[New Likes] int,Unlikes int,[People Talking About Us] int,
	[Total Average Impressions per post] float,[Total Average Reach per post] float,[Total Average Organic Reach] float,[Total Average Viral Reach] float,
	[Total Average Paid Reach] float,[Active Database] float);
	
	declare @Month int;
	
	while @FDate<=@TDate
	begin
	
	set @Month=@FDate;
	
	insert into @tbl1
	
	select t1.PageId,t1.PageName,t1.[Month],t.[Total Likes],t1.[New Likes],t1.Unlikes,t2.[People Talking About Us],t3.[Total Average Impressions per post],
	t3.[Total Average Reach per post],t3.[Total Average Organic Reach],t3.[Total Average Viral Reach],t3.[Total Average Paid Reach],t1.[Active Database]
	 from 
	(select a.PageId ,DATENAME(MONTH,b.[Date]) as [Month], b.LifetimeTotalLikes as [Total Likes]
	from tbl_PageMaster as a, tbl_PageDetails as b	
	where a.PageId=@Page and Month(b.[Date])=@Month and a.PageId=b.PageId and b.LifetimeTotalLikes<>0 and
	b.[Date]=(select MAX(c.[Date]) from tbl_PageDetails as c where c.PageId=a.PageId and Month(c.[Date])=Month(b.[Date]) and c.LifetimeTotalLikes<>0 )) as t
		
	full outer join
	(select a.PageId,a.PageName,DATENAME(MONTH,b.[Date]) as [Month],
	round(Convert(float,(Sum(b.DailyPeopleTalkingAboutThis)))/Sum(b.DailyNewLikes),2) as [Active Database],
	Sum(b.DailyNewLikes) as [New Likes],
	Sum(b.DailyUnlikes ) as [Unlikes]
	from tbl_PageMaster as a, tbl_PageDetails as b
	where a.PageId=b.PageId and a.PageId=@Page and month(b.[Date])=@Month
	group by a.PageId,a.PageName,DATENAME(MONTH,b.[Date])) as t1 on t.PageId=t1.PageId and t.[Month]=t1.[Month] 
	full outer join 
	(select a.PageId,DATENAME(MONTH,a.[Posted]) as [Month], 
	Sum(isnull(d.likes,0)) + Sum(isnull(d.comments,0)) + Sum(isnull(d.shares,0)) as [People Talking About Us]
	from tbl_PostMaster as a, tbl_Post_TalkingAboutThis as d
	where a.PostMainId=d.PostId and a.PageId=@Page and Month(a.Posted)=@Month 
	group by a.PageId,DATENAME(MONTH,a.[Posted])) as t2 on t1.PageId=t2.PageId and t1.[Month]=t2.[Month]
	full outer join 
	(select a.PageId,DATENAME(MONTH,a.[Posted]) as [Month],
	round(Convert(float,(Sum(isnull(c.TotalImpressions,0))))/(COUNT(a.PostId)),2) as [Total Average Impressions per post],
	round(Convert(float,(Sum(isnull(c.TotalReach,0))))/(COUNT(a.PostId)),2) as [Total Average Reach per post],
	round(Convert(float,(Sum(isnull(c.OrganicReach,0))))/(COUNT(a.PostId)),2) as [Total Average Organic Reach],
	round(Convert(float,(Sum(isnull(c.ViralReach,0))))/(COUNT(a.PostId)),2) as [Total Average Viral Reach],
	round(Convert(float,(Sum(isnull(c.PaidReach,0))))/(COUNT(a.PostId)),2) as [Total Average Paid Reach]
	from tbl_PostMaster as a, tbl_Post_Reach as c
	where a.PostMainId=c.PostId and a.PageId=@Page and Month(a.Posted)=@Month 
	group by a.PageId,DATENAME(MONTH,a.[Posted])) as t3 on t1.PageId=t3.PageId and t1.[Month]=t3.[Month]
	
		
		set @FDate=@FDate+1;
	
	end
	
	select * from @tbl1;
	
END
my ouput is like
 
Facebook October November Percentage Diff
Total Likes 254 220 xyz
New Likes 51 14 xyz
.... .... ...
 
Here percentage Diff gives errer I want to display the percentage difference last column but it is very difficult.
So plz help me out.
Posted 27-Dec-12 0:38am
Comments
AshishChaudha at 27-Dec-12 5:43am
   
which line??
faisal23 at 27-Dec-12 5:51am
   
there is no error in current procedure but i want to add new column Percentage diff as shows above which displays the per.diff. between months.
ssd_coolguy at 27-Dec-12 6:56am
   
you just simply add perc.diff column in you @tbl1 table and insert query..
faisal23 at 27-Dec-12 7:11am
   
ya thats right i have done this but main problem is that suppose first item is Total likes in facebook column so there are two months so i got total likes of two months but in perc. diff column i want the per. diff. of these months. But look at this query there is outer join and i am not getting from where it is added.
Also one main problem it is find total likes from from month but only start month so i find difficulty in calc. perc. Please help me out.
ssd_coolguy at 27-Dec-12 7:20am
   
yet i am not clear with your question..
see you have total likes calculated for each month no? means oct and nov?
means your total likes for oct 254 and for nov 220..r8?
faisal23 at 27-Dec-12 7:59am
   
ok sir i will explain whole thing.
I am using asp.net with c#. In repeater i was bind the above procedure so i got correct answer like total likes, new likes for selected month may be 1 or many now my problem is that I want to add new column after months column in which perc. diff. is calculated. But its quit difficult look at this procedure.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

For the love of dinky the duck whoever wrote that sql snippet needs to be slapped. Seriously...
 
Anywho i took the however long of my life i wont get back to clean up this sql (by clean i mean pain stakingly make it more readble through indentation).
 
Im not entirely sure what metric specifically you are looking for (basically what columns) but i've highleted what was changed.
 
DECLARE @tbl1 TABLE (PageId int
						,PageName varchar(50)
						,[Month] varchar(20)
						,[Total Likes] int
						,[New Likes] int
						,Unlikes int
						,[People Talking About Us] int
						,[Total Average Impressions per post] float
						,[Total Average Reach per post] float
						,[Total Average Organic Reach] float
						,[Total Average Viral Reach] float
						,[Total Average Paid Reach] float
						,[Active Database] float
						,[Percent Different] float);
--------------------------
--Second Portion Of Fix
--------------------------
(SELECT 
			a.PageId,
			a.PageName,
			DATENAME(MONTH,b.[Date]) as [Month],
			ROUND(Convert(float,(Sum(b.DailyPeopleTalkingAboutThis)))/Sum(b.DailyNewLikes),2) as [Active Database],
			Sum(b.DailyNewLikes) as [New Likes],
			Sum(b.DailyUnlikes ) as [Unlikes],
			Sum(b.DailyNewLikes)/ Sum(b.DailyUnlikes) AS [Percent Different]
		FROM 
			tbl_PageMaster as a, 
			tbl_PageDetails as b
		WHERE 
			a.PageId=b.PageId 
		AND 
			a.PageId=@Page 
		AND 
			month(b.[Date])=@Month
		GROUP BY a.PageId,a.PageName, DATENAME(MONTH,b.[Date])) as t1 ON t.PageId=t1.PageId and t.[Month]=t1.[Month] 
 
Here is the snippet reformatted (As good as its gonna get because i just want to hurt whoever wrote that). Obviously its untested as i do not have access to your database.
 
alter PROCEDURE [dbo].[GetFacebookGeneralStatistics]
	
	 @FDate int
	,@TDate int
	,@Page int
	
AS
BEGIN
	DECLARE @tbl1 TABLE (PageId int
						,PageName varchar(50)
						,[Month] varchar(20)
						,[Total Likes] int
						,[New Likes] int
						,Unlikes int
						,[People Talking About Us] int
						,[Total Average Impressions per post] float
						,[Total Average Reach per post] float
						,[Total Average Organic Reach] float
						,[Total Average Viral Reach] float
						,[Total Average Paid Reach] float
						,[Active Database] float
						,[Percent Different] float);
	
	DECLARE @Month int;
	
	WHILE @FDate<=@TDate
	BEGIN
		
		SET @Month=@FDate;
		
		INSERT INTO @tbl1
		
		SELECT 
			t1.PageId,
			t1.PageName,
			t1.[Month],
			t.[Total Likes],
			t1.[New Likes],
			t1.Unlikes,
			t2.[People Talking About Us],
			t3.[Total Average Impressions per post],
			t3.[Total Average Reach per post],
			t3.[Total Average Organic Reach],
			t3.[Total Average Viral Reach],
			t3.[Total Average Paid Reach],
			t1.[Active Database]
			t1.[Percent Different]
		FROM (SELECT 
				a.PageId,
				DATENAME(MONTH,b.[Date]) as [Month], 
				b.LifetimeTotalLikes as [Total Likes]
			  FROM 
				tbl_PageMaster as a, tbl_PageDetails as b	
			  WHERE 
				a.PageId=@Page 
			  AND 
				Month(b.[Date])=@Month 
			  AND 
				a.PageId=b.PageId 
			  AND 
				b.LifetimeTotalLikes <> 0 
			  AND
				b.[Date]=(SELECT 
							MAX(c.[Date]) 
						  FROM 
							tbl_PageDetails as c 
						  WHERE 
								c.PageId=a.PageId 
							AND 
								Month(c.[Date]) = Month(b.[Date]) 
							AND 
								c.LifetimeTotalLikes<>0 )
			 ) as t
		FULL OUTER JOIN
		(SELECT 
			a.PageId,
			a.PageName,
			DATENAME(MONTH,b.[Date]) as [Month],
			ROUND(Convert(float,(Sum(b.DailyPeopleTalkingAboutThis)))/Sum(b.DailyNewLikes),2) as [Active Database],
			Sum(b.DailyNewLikes) as [New Likes],
			Sum(b.DailyUnlikes ) as [Unlikes],
			Sum(b.DailyNewLikes)/ Sum(b.DailyUnlikes) AS [Percent Different]
		FROM 
			tbl_PageMaster as a, 
			tbl_PageDetails as b
		WHERE 
			a.PageId=b.PageId 
		AND 
			a.PageId=@Page 
		AND 
			month(b.[Date])=@Month
		GROUP BY a.PageId,a.PageName, DATENAME(MONTH,b.[Date])) as t1 ON t.PageId=t1.PageId and t.[Month]=t1.[Month] 
		--------------------------------------------------------
		FULL OUTER JOIN 
		(SELECT 
			a.PageId,
			DATENAME(MONTH,a.[Posted]) as [Month], 
			Sum(isnull(d.likes,0)) + Sum(isnull(d.comments,0)) + Sum(isnull(d.shares,0)) as [People Talking About Us]
		FROM 
			tbl_PostMaster as a, 
			tbl_Post_TalkingAboutThis as d
		WHERE 
			a.PostMainId = d.PostId 
		AND 
			a.PageId=@Page 
		AND 
			Month(a.Posted)=@Month 
		GROUP BY a.PageId,DATENAME(MONTH,a.[Posted])) as t2 on t1.PageId=t2.PageId and t1.[Month]=t2.[Month]
		--------------------------------------------------------
		FULL OUTER JOIN 
		(SELECT 
			a.PageId,
			DATENAME(MONTH,a.[Posted]) as [Month],
			round(Convert(float,(Sum(isnull(c.TotalImpressions,0))))/(COUNT(a.PostId)),2) as [Total Average Impressions per post],
			round(Convert(float,(Sum(isnull(c.TotalReach,0))))/(COUNT(a.PostId)),2) as [Total Average Reach per post],
			round(Convert(float,(Sum(isnull(c.OrganicReach,0))))/(COUNT(a.PostId)),2) as [Total Average Organic Reach],
			round(Convert(float,(Sum(isnull(c.ViralReach,0))))/(COUNT(a.PostId)),2) as [Total Average Viral Reach],
			round(Convert(float,(Sum(isnull(c.PaidReach,0))))/(COUNT(a.PostId)),2) as [Total Average Paid Reach]
		FROM 
			tbl_PostMaster as a, 
			tbl_Post_Reach as c
		WHERE 
			a.PostMainId = c.PostId 
		AND 
			a.PageId=@Page 
		AND 
			Month(a.Posted)=@Month 
		GROUP BY a.PageId,DATENAME(MONTH,a.[Posted])) as t3 on t1.PageId=t3.PageId and t1.[Month]=t3.[Month]
		
		set @FDate=@FDate+1;
		
	end
	
	select * from @tbl1;
	
END
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 13 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100