Here is few of my SQL ServerTechnical Answer .
- Round function in SQL Server
ANS:
select Round((10*100)/115,0),
(CAST(10*100 AS decimal(10,2))/115),
ROUND(CAST(10*100 AS decimal(10,2))/115,0),
FLOOR(CAST(10*100 AS decimal(10,2))/115),
CEILING(CAST(10*100 AS decimal(10,2))/115)
- How to Pass Column as parameter to SQL Server Stored Procedure?
-- exec USP_GdRateFixedPosts 'Item_Code,item_Name','2014-12-12 08:18:14.740','2014-12-12 08:18:14.740'
Create PROCEDURE [dbo].[USP_GdRateFixedPosts ]
(
@MyColumns AS NVARCHAR(MAX) = '' ,
@fromDateTime AS DateTime,
@ToDateTime as DateTime )
AS BEGIN DECLARE @SQLquery AS NVARCHAR(MAX)
set @SQLquery = N'SELECT ' + @MyColumns + N' FROM GdRateFixedPosts where MonitorDateTime BETWEEN ' where MonitorDateTime BETWEEN ' + @fromDateTime + ' and ' + @ToDateTime + '' exec sp_executesql @SQLquery;
END
- How to Pass More than one name as parameter to where condition to SQL Server Stored Procedure?
ALTER PROCEDURE [dbo].[sp_Sample] @param1 VARCHAR(100),@name varchar(30)
ASBegin DECLARE @Sql NVARCHAR(MAX)
---- SET @param1 = Replace(@param1, ',', ''',''')
SET @Sql = '(select * from tblsample where name='''+ @name +''' and [Escalate to] IN ('''+ @param1 + '''))' exec sp_executesql @Sql
End
- Pivot Query using Join Query?
-- Create Table
Create Table EmpDetails
(
Emp_Id int,
Emp_Name varchar(40),
Allowance_Names varchar(40),
Allowance_Values varchar(40),
)
--Sample data Insert
Insert into EmpDetails Values(1,'Emp1', 'AllowNames1', 'Values1')
Insert into EmpDetails Values(1,'Emp1', 'AllowNames2', 'Values2')
Insert into EmpDetails Values(1,'Emp1', 'AllowNames3', 'Values3')
Insert into EmpDetails Values(1,'Emp1', 'AllowNames4', 'Values4')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames1', 'Values2_1')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames2', 'Values2_2')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames3', 'Values2_3')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames4', 'Values2_4')
create table EmpSalary(
Emp_Id int,
Allowance_Names varchar(40),
Allowance_Value int)
--Sampel Insert Query
insert into EmpSalary values(1,'AllowNames1',500 )
insert into EmpSalary values(1,'AllowNames1',47 )
insert into EmpSalary values(1,'AllowNames2',400 )
insert into EmpSalary values(1,'AllowNames3',700 )
insert into EmpSalary values(1,'AllowNames4',45 )
insert into EmpSalary values(2,'AllowNames2',50 )
insert into EmpSalary values(2,'AllowNames3',340 )
insert into EmpSalary values(2,'AllowNames4',145 )
-- here is your pivot query
-- Pivot Select Query
DECLARE @MyColumns AS NVARCHAR(MAX),
@SQLquery AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(Allowance_Names)
FROM EmpDetails
GROUP BY Allowance_Names
ORDER BY Allowance_Names
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @SQLquery = N'SELECT Emp_Id,Emp_Name,' + @MyColumns + N' from ( SELECT A.Emp_Id as Emp_Id, A.Emp_Name as Emp_Name, B.Allowance_Value as values2, B.Allowance_Names as Allowance_Names FROM EmpDetails A Inner JOIN EmpSalary B ON A.Emp_Id=B.Emp_Id AND A.Allowance_Names=B.Allowance_Names ) x pivot ( SUM(values2) for Allowance_Names in (' ( SELECT
A.Emp_Id as Emp_Id, A.Emp_Name as Emp_Name,
B.Allowance_Value as values2,
B.Allowance_Names as Allowance_Names FROM EmpDetails A Inner JOIN EmpSalary B ON A.Emp_Id=B.Emp_Id AND A.Allowance_Names=B.Allowance_Names ) x pivot
( SUM(values2) for Allowance_Names in (' + @MyColumns + N') ) p ' ) p 'exec sp_executesql @SQLquery;
- Group by and Having Clause in SQL?
ANS:
Create TableCreate Table A ( ID char(6), CP char(2), CPDate Datetime) --Sample data Insert Insert into A Values('A00001', 'A0', '10/10/2014') Insert into A Values('A10000', 'A0', '10/10/2014') Insert into A Values('A10000', 'A1', '10/11/2014') Insert into A Values('A10000', 'A2', '11/11/2014') Insert into A Values('A10000', 'A3', '11/11/2014') Insert into A Values('A10000', 'A4', '12/15/2014') Insert into A Values('A10000', 'A5', '12/17/2014') Insert into A Values('A20000', 'A0', '10/10/2014') Insert into A Values('A20000', 'A1', '10/12/2014') Insert into A Values('A20000', 'A2', '11/13/2014') Insert into A Values('A20000', 'A3', '11/14/2014') Insert into A Values('A20000', 'A4', '12/15/2014') Insert into A Values('A20000', 'A5', '12/17/2014') Insert into A Values('A20000', 'A6', '12/17/2014') Insert into A Values('A30000', 'A0', '10/10/2014') Insert into A Values('A30000', 'A1', '10/12/2014') Insert into A Values('A30000', 'A2 ', '11/01/2014') -- Select Query SELECT ID,count(CP) FROM A where CPDate between DateAdd(mm,-1,CPDate) and getdate() GROUP BY ID HAVING count(CP)<4 order by ID;
- How to get last 5 records from today in SQL?
ANS:
-- this sample query to display today and 5 days before from today select getdate(),DATEADD(dd,-5,getdate()) -- This sample query will return result between 5 days SELECT * FROM YourTable_Name WHEREYOURDateColumn BETWEEN DATEADD(dd,-5,getdate()) and getdate()
- Join Table and get Count result in SQL?
ANS:
-- Create Table CREATE TABLE [dbo].[orders]( [order_id] [int] NOT NULL, [order_date] DateTime NULL) CREATE TABLE [dbo].[drivers]( [driver_id] [int] NOT NULL, [driver_name] varchar(50) NULL) CREATE TABLE [dbo].[notifications]( [driver_id] [int] NOT NULL, [order_id] [int] NOT NULL, [order_delivery] varchar(50) NULL) GO-- Sample Insert INSERT [dbo].[orders] VALUES (1, getdate()) INSERT [dbo].[orders] VALUES (2, DATEADD(day,30,getdate()) ) INSERT [dbo].[orders] VALUES (3, DATEADD(day,15,getdate()) ) INSERT [dbo].[drivers] VALUES (1, 'Driver1') INSERT [dbo].[drivers] VALUES (2, 'Driver2') INSERT [dbo].[drivers] VALUES (3, 'Driver3') INSERT [dbo].[drivers] VALUES (4, 'Driver4') INSERT [dbo].[notifications] VALUES (1,1, 'YES') INSERT [dbo].[notifications] VALUES (1,1, 'YES') INSERT [dbo].[notifications] VALUES (1,2, 'YES') INSERT [dbo].[notifications] VALUES (1,3, 'YES') INSERT [dbo].[notifications] VALUES (1,4, 'YES') INSERT [dbo].[notifications] VALUES (2,1, 'YES') INSERT [dbo].[notifications] VALUES (2,2, 'YES') INSERT [dbo].[notifications] VALUES (2,2, 'YES') INSERT [dbo].[notifications] VALUES (2,2, 'YES') INSERT [dbo].[notifications] VALUES (2,3, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,3, 'YES') --------------- -- Your select Query Declare @StartDate datetime= getdate(), @endate datetime=DATEADD(day,30,getdate()) Select D.Driver_Name , Count(N.order_delivery) TotalCounts FROM notifications N Left Outer JOIN orders O ON N.order_id=O.Order_id Left Outer JOIN drivers D ON N.driver_id=D.driver_id Where O.order_date between @StartDate and @endate Group BY D.Driver_Name
- Split String Like below format in SQL?
ANS:
declare @s varchar(500) = 'Welcome to Shanu Blog'
Welcome
Welcome To
Welcome To Shanu
Welcome to Shanu Blog
declare @Str varchar(8000) = 'Welcome to Shanu Blog',
@delimiter as varchar(10)=' ',
@OldStr varchar(8000) ='' declare @ival int
declare @splits varchar(8000)
IF OBJECT_ID('tempdb..#temptable') IS NOT NULL
DROP TABLE #temptable
CREATE TABLE #temptable
(
Items VARCHAR(500)
)
select @ival = 1
if len(@Str)<1 or @Str is null return
while @ival!= 0
begin
set @ival = charindex(@Delimiter,@Str)
if @ival!=0
set @splits = left(@Str,@ival - 1)
else
set @splits = @Str
set @OldStr=@OldStr + ' ' + @splits
if(len(@splits)>0)
insert into #temptable(Items) values(@OldStr)
-- select @splits into #temptable
set @Str = right(@Str,len(@Str) - @ival)
if len(@Str) = 0 break
end
select * from #temptable
- SQL Query to display warning message before the Expiry date?
ANS:
declare @Expireye datetime, @eDate datetime; select @Expireye = '12/25/2014 08:00:00' select @Expireye as expireDate,getdate() as todaysdate,DATEADD(day,30,getdate())as TodatedatePlus30days, case when DATEADD(day,30,getdate()) < @Expireye Then 'False' Else 'true' END as ExpiryStatus
- SQL Trigger for Insert and Update?
ANS:
CREATE TRIGGER [dbo].[TgrLog] ON [dbo].[tblLotAssign] FOR INSERT, UPDATEASBEGIN
- Work time Shift Hourly output from 8 AM to 9 PM using SQL Query?
ANS:
declare @sDate datetime, @eDate datetime; select @sDate = '11/4/2014 08:00:00' , @eDate = '11/4/2014 20:00:00'; --select @sDate StartDate,@eDate EndDate ;with cte as ( select @sDate StartDate ,datepart(hour, @sDate) StartTime , datepart(hour, dateadd(Hour, 1, @sDate)) as EndTime ,convert(varchar(8),datepart(hour, @sDate)) + ' - ' + convert(varchar(8),datepart(hour, dateadd(Hour, 1, @sDate))) as Hours union all select dateadd(Hour, 1, StartDate) ,datepart(hour, dateadd(Hour, 1, StartDate)) StartTime , datepart(hour, dateadd(Hour, 2, StartDate)) as EndTime ,convert(varchar(8),datepart(hour, dateadd(Hour, 1, StartDate))) + ' - ' + convert(varchar(8),datepart(hour, dateadd(Hour, 2, StartDate))) as Hours FROM cte WHERE dateadd(Hour, 1, StartDate)<= @eDate ) select * from cte option (maxrecursion 0)
- How to find Missing Number in SQL Query?
ANS:
-- I have created a Sample Table for you create table K_RT_Dailyentryretail ( SNO int not null primary key, Branch varchar(10) ); -- here insert sample data to the table with missing no. insert into K_RT_Dailyentryretail (SNO,Branch) values (1,'ABC'), (2,'ABC'), (3,'ABC'), (5,'ABC'), (6,'ABC'), (8,'ABC'), (10,'ABC') --select * from K_RT_Dailyentryretail -- Here is the query to select only the missing No from the given table. select KRD.SNO + 1 as MissingNos from K_RT_Dailyentryretail as KRD left outer join K_RT_Dailyentryretail as LKRD on KRD.SNO + 1 = LKRD.SNO where LKRD.SNO is null
- Calculate Hours Work by SQL Query?
ANS: I have used dateAdd and Datediff
DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME -- Set Start and End date
SET @START_DATE ='2014-07-02 07:59'
SET @END_DATE = '2014-07-02 18:03'
-- Get the Result in HH:MI:SS:MMM(24H) format
SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff
- Computing in SQL Queries?
ANS:
-- Create Table
CREATE TABLE [dbo].[TableA](
[ID] [INT] NOT NULL,
[TotalProductSum] [INT] NOT NULL) ON [PRIMARY]
GOCREATE TABLE [dbo].[TableB](
[ID] [INT] NOT NULL,
[Products] [varchar](30) NOT NULL,
[Value] [INT] NOT NULL) ON [PRIMARY]
-- Insert Sample Data
INSERT INTO [dbo].[TableA]
([ID],[TotalProductSum])
VALUES (11,1000)
INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
VALUES (11 ,'10' ,80)
INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
VALUES (11 ,'20' ,30)
INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
VALUES (11 ,'30' ,45)
INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
VALUES (11 ,'40' ,56)
-- Select query
select * from TableA
select * from TableB
-- Here isw the select query with your result :
SELECT A.ID,A.Products,A.Value,B.TotalproductSum- SUM(a.value) OVER (ORDER BY A.Products) NewTotalProductSum
from TableB as A INNER JOIN TableA AS B
ON A.ID=B.ID
ORDER BY a.id;
- Display Months between tow dates in new column using Function in SQL ?
ANS:
-- Create Function
-- Create Function
CREATE FUNCTION [dbo].FUN_GETMONTH(@date1 datetime, @date2 datetime)
RETURNS VARCHAR(1000)
ASBEGIN
declare @tmp varchar(MAX)
SET @tmp = '';with cte as (
select datename(month,@date1) as [Month_Name],@date1 as dat
union all
select datename(month,DateAdd(Month,1,dat)),DateAdd(Month,1,dat) from cte
where convert(char(6),DateAdd(Month,1,dat),112) <= convert(char(6),@date2,112))
select @tmp = @tmp + CASE WHEN [Month_Name]='01' THEN 'JAN' WHEN [Month_Name]='02' THEN 'FEB' WHEN [Month_Name]='03' THEN 'MAR' WHEN [Month_Name]='04' THEN 'APR' WHEN [Month_Name]='05' THEN 'MAY' WHEN [Month_Name]='06' THEN 'JUN' WHEN [Month_Name]='07' THEN 'JUL' WHEN [Month_Name]='08' THEN 'AUG' WHEN [Month_Name]='09' THEN 'SEP' WHEN [Month_Name]='10' THEN 'OCT' WHEN [Month_Name]='11' THEN 'NOV' WHEN [Month_Name]='12' THEN 'DEC' END + ', ' from CTE
option (maxrecursion 0)
RETURN @tmp;
END-- Run this below query to see the result.
DECLARE @TBL TABLE
(
STRARTDATE DATETIME,
ENDDATE DATETIME
)
INSERT INTO @TBL(STRARTDATE, ENDDATE)
SELECT '2014-01-20', '2014-04-20'
UNION ALL
SELECT '2014-05-20', '2014-09-20'
UNION ALL
SELECT '2014-07-20', '2014-07-20'
UNION ALL
SELECT '2014-06-20', '2014-11-20'
select STRARTDATE,ENDDATE, dbo.FUN_GETMONTH(STRARTDATE, ENDDATE) AS MONTHS from @TBL
