Category

SQL

Select next and previous id of record in sql with where condition

By | SQL

If you have a table named with Student and having records, and you want all records with next and previous PKId or id with each records and you can use lag and lead functions.

SELECT 
Student.PKId
Student.Name,
Student.Address,
NextPrev.PreviousId,  
NextPrev.NextId  
FROM Student
                   OUTER APPLY  
                   (select PreviousId,NextId  
                    from (  
                        select   
                               lag(PKId) OVER (ORDER BY Student.PKId DESC) as PreviousId,  
                               PKId,  
                               lead(PKId) OVER (ORDER BY Student.PKId DESC) as NextId  
                        from Student WHERE Status=1 --Here Condition should be matched with main condition
                    ) as t  
                    WHERE PKId=@PKId) AS NextPrev  
                                           WHERE Student.Status=1 AND Student.Name='Veer' --you can remove this condition if you want all records


--or simply with all records 

SELECT 
Student.PKId
Student.Name,
Student.Address,
NextPrev.PreviousId,  
NextPrev.NextId  
FROM Student
                   OUTER APPLY  
                   (select PreviousId,NextId  
                    from (  
                        select   
                               lag(PKId) OVER (ORDER BY Student.PKId DESC) as PreviousId,  
                               PKId,  
                               lead(PKId) OVER (ORDER BY Student.PKId DESC) as NextId  
                        from Student
                    ) as t  
                    WHERE PKId=@PKId) AS NextPrev  

Function For Comma separate string to Column in SQL Server

By | SQL

Here is a user defined function that we have created to convert comma separate string values to Table Column,
Mostly we use for IN statement in Select QUERY like

select * from Emp WHERE PKId IN(fn_SplitParameters('1,2,3',','))

CREATE FUNCTION [dbo].[fn_SplitParameters](@String varchar(max), @Delimiter char(1))           
returns @temptable TABLE (ID int,items varchar(max))           
as           
begin           
declare @idx int           
declare @slice varchar(max)           
 declare @ID int           
  set  @ID=0       
select @idx = 1           
if len(@String)<1 or @String is null  return           
           
while @idx!= 0           
begin           
set @idx = charindex(@Delimiter,@String)           
set @ID=@ID+1  
if @idx!=0           
set @slice = left(@String,@idx - 1)           
else           
set @slice = @String     
if(len(@slice)>0)      
insert into @temptable(ID,Items) values(@ID,@slice)           
set @String = right(@String,len(@String) - @idx)           
 if len(@String) = 0 break           
end       
return           
end

XML Data Insert,Update with MERGE in sql server

By | MVC, SQL
First of all you have to select XML data into a temptable, that will use as SOURCE table in merger statement.

SELECT
		ISNULL(x.v.value('Id[1]', 'int'), '') AS Id,
		ISNULL(x.v.value('Name[1]', 'bit'), '') AS Name,
		ISNULL(x.v.value('Address[1]', 'bit'), '') AS Address,
		ISNULL(x.v.value('Status[1]', 'nvarchar(max)'), '') AS Status,
		ISNULL(x.v.value('MoNumber[1]', 'nvarchar(max)'), '') AS MoNumber,
		ISNULL(x.v.value('Location[1]', 'nvarchar(max)'), '') AS Location,
		ISNULL(x.v.value('Email[1]', 'nvarchar(max)'), '') AS Email
		INTO #TempData
	FROM @XMLData.nodes('/ArrayOfEmployeeAccessModel/EmployeeModel') x (v);

And then you can user MERGER Statement to insert or update according to condition.

MERGE tbl_UpdatedEmployee AS TARGET
		USING #TempData AS SOURCE 
		ON (
		TARGET.PKId = SOURCE.Id 
		) 
		WHEN MATCHED  THEN 
		UPDATE 
		SET TARGET.Name = SOURCE.Name,
                    TARGET.Address= SOURCE.Address,
			
		WHEN NOT MATCHED BY TARGET THEN 
		INSERT (Name,Address,Status,MoNumber,Location,Email) 
		VALUES (SOURCE.Name,SOURCE.Address,SOURCE.Status,SOURCE.MoNumber,SOURCE.Location,SOURCE.Email)
		
Also if you want to get INSERTED or UPDATED rows or data than you can get as resultset, but for this first of all you have to declare a table variable on top then you can get output as table and return as resultset.
DECLARE @output TABLE (Id int,Email VARCHAR(50),Password NVARCHAR(10))
 In Above statement you can declare the columns name which you want to take as output columns, above defined a table variable.
 And now you have to add a statement at the last of merge statement, and you have to put ; (semicolon) at last of OUTPUT data statement.

MERGE tbl_UpdatedEmployee AS TARGET
		USING #TempData AS SOURCE 
		ON (
		TARGET.PKId = SOURCE.Id 
		) 
		WHEN MATCHED  THEN 
		UPDATE 
		SET TARGET.Name = SOURCE.Name,
                    TARGET.Address= SOURCE.Address,
			
		WHEN NOT MATCHED BY TARGET THEN 
		INSERT (Name,Address,Status,MoNumber,Location,Email) 
		VALUES (SOURCE.Name,SOURCE.Address,SOURCE.Status,SOURCE.MoNumber,SOURCE.Location,SOURCE.Email)

               OUTPUT INSERTED.Id,INSERTED.Address,INSERTED.Status INTO @output;
And now you can select your table variable as resultset.
SELECT * FROM @output

For More Detail about MERGE Visit Microsoft MSDN Weblink
Link

Insert Multiple ROWS in One SQL Query.

By | SQL | No Comments

INSERT INTO [dbo].[tbl_Page]
           ([PageTitle]
           ,[Link]
           ,[Status]
           ,[CreatedDate]
           ,[Description])
     VALUES
           ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo'),
		   ('demo','demo',1,'12/11/2019','demo');

Here remamber to put comma at end of value secton expect of last section of value, at the end of last section of value, you have to put semicollon to end the insert statement.

See the image for tested result.

Get data of current month only in sql

By | SQL | No Comments

This is very Simple, Just get month of both of dates which dates you want to compare but both dates should be in date format or you have to cast in date format

get month of any date MONTH() method

MONTH('2019/02/22')

it will return only month of this date, just pass date in month method.

if your date in varchar or string format than you should cast in date format

CAST('2019/02/22'AS DATE)

Complete Example Is

MONTH(CAST(YourDate AS DATE)) = MONTH(GETDATE())

Select multiple count in one result set in sql and C#.

By | C#, Insight Database, ORM, SQL | No Comments

Here, you can select multiple counts from different – 2 tables in one result set. with multiple coulumn names.

i used insight databse ORM in C#.

First i created a Model in c# where result set will bind with Columns name and model property name.

public class AnalyticsModel
    {
        public int totalAppointments { get; set; }
        public int totalPrescriptionRenewal { get; set; }
        public int totalNumberOfDropInsPerDay { get; set; }
        public int totalRequest { get; set; }
    }

And Now My Store Procedure in SQL

CREATE PROC [dbo].[AnalyticsTotal]
AS
BEGIN 
	SET NOCOUNT ON;

	SELECT
	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,3) AND Status=1) AS [totalAppointments],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType=2 AND Status=1) AS [totalPrescriptionRenewal],

	(SELECT COUNT(*) FROM Appointment WHERE IsDropIn=1 AND Status=1) AS [totalNumberOfDropInsPerDay],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,2,3) AND Status=1) AS [totalRequest]

END

And in last i got my result set in my DataClass.

var Result = DB.QuerySql<AnalyticsModel>(@"Exec [dbo].[AnalyticsTotal]").FirstOrDefault();

Here QuerySql is the method of Insight Database ORM.

You can Install in your visual studio from nuget package manager.

Install-Package Insight.Database -Version 6.2.8

Get only time or only Date from a datetime string in SQL

By | SQL | No Comments

If you have a datetime in string format or as varchar, than you can cast this string or varchar in datetime format.

And if you want to retrieve only date or only time from datetime than query is below.

CAST() method, you can use for conversation from any format to any other format in SQL

Ex:-


CAST(’01/14/2019′ AS DATETIME)

CAST(’01/14/2019′ AS TIME)

CAST(’01/14/2019′ AS DATE)