Coder's Space

  ALTER PROCEDURE [dbo].[SP_UPDATEUSERLEVEL] @Userid INT AS BEGIN Declare @CountOfUserReportedTweets INT Declare @LevelBadgeId INT SET @CountOfUserReportedTweets=(select count(*) from OnGoingReports where UserId=@Userid) --CHECKS WHETHER THE LEVEL EXIST OR NOT IF EXISTS(select Top 1 * from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) BEGIN --IF LEVEL EXISTS UPDATE OR INSERT WITH THAT LEVEL ID SET @LevelBadgeId=(select Top 1 Id from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLevel SET LevelBadgeId=@LevelBadgeId WHERE UserId=@Userid END ELSE BEGIN INSERT INTO UserLevel(UserId,LevelBadgeId) VALUES (@Userid,@LevelBadgeId) END END ELSE BEGIN --IF NO LEVEL EXISTS THEN INSERTING DEFAULT LEVEL IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLeve...

 ALTER PROCEDURE [dbo].[SP_GETUSERPROFILE] @UserId int AS BEGIN --checks whether user reported any tweet or not IF EXISTS(SELECT * FROM OnGoingReports WHERE UserId=@UserId) BEGIN --if user already reported tweets then gets the user details and rank select UserId,UserName,Name,UserReportedTweetCount,Rank      from  (select UserId,UserName,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from      (select ogr.UserId,uc.UserName,Name,Count(*) UserReportedTweetCount from UserCredentials uc       inner join OnGoingReports ogr       on       ogr.UserId=uc.UserId        GROUP BY ogr.UserId,uc.Name,uc.UserName) as TopList ) as UserRank      where UserId=@UserId END ELSE BEGIN --if user not reported any tweets then it get the default value as 0 and rank based on tweets reported select UserId,UserName,Nam...

 ALTER PROCEDURE [dbo].[SP_GETTWEETSBYTOPIC_REPORTEDSTATUS] @TopicId INT, @UserId INT AS BEGIN select distinct tw.TweetId,tw.Id,tw.CreatedOn, tw.TwitterHandle,tw.TwitterProfileLink,tw.Tweet,tw.TweetedOn,tw.Rating, tw.NoofComments,tw.NoofLikes,tw.NoofReTweets,tw.IsActive, (select case when ogr.userid IS NOT NULL and ogr.TweetId IS NOT NULL then CAST(1 AS BIT) else CAST(0 AS BIT) end as isreported ) IsReported from tweets tw inner join tweettopic tt on tt.twtid=tw.id left join ongoingreports ogr on ogr.TweetId=tw.TweetId and ogr.UserId=@UserId where tt.TopicId=@TopicId and tw.IsActive=1 and  (UPPER(tw.Rating)='NEGATIVE' OR UPPER(tw.Rating)='FALSE') END

  CREATE PROCEDURE [dbo].[SP_GETLEADERBOARD]  @UserId INT  AS  BEGIN   DECLARE @IsExist Int IF EXISTS(Select * from OnGoingReports where UserId=@UserId) BEGIN SET @IsExist=1 END ELSE BEGIN SET @IsExist=0 END --gets the user name,tweetsreported,rank of top 10 users from ongoingreports table select Top 10 UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from (select ogr.UserId,Name,Count(*) UserReportedTweetCount from UserCredentials uc inner join OnGoingReports ogr on ogr.UserId=uc.UserId  where uc.IsActive=1 GROUP BY ogr.UserId,uc.Name) TopList   UNION    --if user reported then the if block executes and calculates rank based on tweets reported select UserId,Name,UserReportedTweetCount,Rank from (select UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Ran...

 SELECT City, COUNT (OrgId) as NoofOrgs FROM Organisation_Address oa inner join Organisations org  on org.Id=oa.OrgId Where City!='' and Org.IsActive=1 and org.IsApproved=1 GROUP BY City 

SELECT ChildUserType.Id as CategoryId,ChildUserType.Category as CategoryName, ChildUserType.IsActive,ChildUserType.CategoryURL as CategoryURL, ChildUserType.CreatedOn,ChildUserType.ParentId, ParentUserType.Category as ChildParentname,ChildUserType.NoofOrgs FROM  [dbo].[Categories] AS ChildUserType LEFT JOIN  [dbo].[Categories] AS ParentUserType  ON  ChildUserType.ParentId = ParentUserType.Id 

--creating a trigger CREATE TRIGGER [dbo].UPDATEORGSCOUNTINCATEGORY_AFTERINSERT_FROMORGANISATIONS]   ON --here we mention the table name   [dbo].[Organisations] --here we can have insert, update, delete as trigger point --I want to execute my trigger when a record is inserted in organizations table (step-1) --then get the count of orgs in each category from organisationcategories table and save it in temp table(step-2) --then update the category table with orgs count in each category(step-3)   AFTER INSERT   AS   BEGIN   --we are updating the count bcz a listing or nonprofi can be approved or rejected then the count changes. --creating a temp table with two columns of int type DECLARE @temp TABLE ( CategoryId INT, NoofOrgs INT ) --inserting values into temp table INSERT INTO @temp SELECT * from (         --here we are selecting these two columns select CategoryId,ISNULL(NoofOrgs , 0) AS 'NoofOrgs' from (   ...

Let us consider the below JSON as sample data, ex: [{ "info": [{ "id": 1, "name": "John", "surname": "Smith", "age": 25 }, { "id": 2, "name": "Abijal", "surname": "Smith", "age": 45 }, { "id": 3, "name": "Abhijit", "surname": "singh", "age": 41 }] }] To insert the above JSON we can use the below query, Query: declare @json varchar(max) =' [{ "info": [{ "id": 1, "name": "John", "surname": "Smith", "age": 25 }, { "id": 2, "name": "Abijal", "surname": "Smith", "age": 45 }, { "id": 3, "name": "Abhijit", "surname": "singh"...

Let us consider the below table, Table Data: In the above table, we have common fields like Work_Order and unit. Here every time a new record is inserted while inserting TotUnits, ETCUnits, RwUnits. So we need to get them in one row by eliminating null values as we have Work_Order and unit same for three of them as shown below, OUTPUT: Here we get data in a single row for every distinct Work_Order and unit. Query as Follows: declare @units table (     unit varchar(1000) ) declare @result table (     workorder varchar(1000),TotalUnits varchar(1000),ETCUnits varchar(1000),RwUnits varchar(1000),UnitID varchar(1000),Total varchar(1000) ) insert into @units SELECT distinct Unit FROM  te  --Cursor for iterating declare @tableCursor cursor,         @unit varchar(100) set @tableCursor = cursor for select * from @units open @tableCursor fetch next from @tableCursor into @uni...