Category: Script

—This Script Will return your agg in (Y-M-D)

Set Dateformat DMY

GO

DECLARE @date datetime,
@tmpdate datetime,
@years int, @months int,
@days int SELECT @date = ’01/10/1985′
SELECT @tmpdate = @date SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) – CASE
WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date)
= MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE()))
THEN 1 ELSE 0 END SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) – CASE WHEN DAY(@date) > DAY(GETDATE())
THEN 1 ELSE 0 END SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE()) SELECT @years, @months, @days
—Use the following formula to calculate a person’s age in years
DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = ’01/10/1985′
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

–Notes:
–Substitute “getdate()” for the @yyyymmdd variable if you want to determine the person’s age right now.
–The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.

select datediff (year, convert (datetime, ’01/10/1985′), getdate())

DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME

SELECT @CurrentDate = ’02/09/2011′, @BirthDate = ’01/10/1985′

SELECT
DATEDIFF(YY, @BirthDate, @CurrentDate) –
CASE
WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1
ELSE 0
END

How to Generate Zero without using Any Numbers in T-SQL

select count(*) where ‘a’=’b’
select count(*)-count(*)
select count(cast(null as int))
select Ascii(‘Y’)-Ascii(‘Y’)

Example 1

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 – @Value2))) AS MaxColumn
Example 2

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END As MaxColumn

Example 3

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
DECLARE @VALUE3  DECIMAL(5,2)= 15
DECLARE @RESULT INT
SELECT @RESULT = (SELECT  CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn)
SELECT CASE WHEN @VALUE3 > @RESULT THEN @VALUE3 ELSE @RESULT END AS MaxColumn

We all know how to use Data Manipulation
Language to Insert, Delete and Update data on a SQL Server table. Sometimes we
need to insert too much data using insert statements. There are traditional and
cumbersome methods to insert huge amounts of data. Now, SQL Server 2008 provides
a new method to insert data to SQL Server tables, which simplifies the data
insertion. This article is going to illustrate different methods to insert data
to a table, including the new Row Value Constructor.

Traditionally we have three methods to insert data

Method 1

USE [master]
GO
/****** Object:  Database [MyTestDB]
Script Date: 01/09/2011 ******/
IF  EXISTS (SELECT name FROM sys.databases
WHERE name = N’MyTestDB’)
DROP DATABASE [MyTestDB]
GO
Create database MyTestDB
Go
Use [MyTestDB]
Go
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[MyTest1]’)
AND type in (N’U’))
DROP TABLE [dbo].[MyTest1]
GO
USE [MyTestDB]
GO
/****** Object:  Table [dbo].[MyTest1]
Script Date: 01/09/2011 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTest1](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Now let’s add 5 rows of data using a traditional ANSI insert SQL statement . Here we are going to use the INSERT SQL Statement with a VALUE clause to insert data.

insert into MyTest1 (id ,fname ,lname , salary) values (1 , ‘Mostafa’, ‘Elmasry’, 150000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (2 , ‘Mohamed’, ‘Osman’, 250000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (3 , ‘Mohamed’, ‘Omar’, 120000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (4 , ‘Mahmoud’, ‘Darwish’, 151000.00)
insert into MyTest1 (id ,fname ,lname , salary) values (5 , ‘Khaled’, ‘Jamal’ , 150300.00)

Method 2

CREATE TABLE [dbo].[MyTest2](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) ON [PRIMARY]

Now let’s add 5 rows of data using a traditional insert SQL statement . Here we are going to use the INSERT SQL Statement with a SELECT clause to insert data.

insert into MyTest1 select 1 , ‘Mostafa’, ‘Elmasry’, 150000.00
insert into MyTest1 select 2 , ‘Mohamed’, ‘Osman’, 250000.00
insert into MyTest1 select 3 , ‘Mohamed’, ‘Omar’, 120000.00
insert into MyTest1 select 4 , ‘Mahmoud’, ‘Darwish’, 151000.00
insert into MyTest1 select 5 , ‘Khaled’, ‘Jamal’ , 150300.00

Method 3

CREATE TABLE [dbo].[MyTest3](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
) GO

Now let’s add 5 rows of data using a traditional insert SQL statement . Here we are going to use the INSERT SQL Statement with a SELECT and UNION clause to insert data.

insert into MyTest3
select 1 , ‘mostafa’ , ‘Elmasry’ , 150000.00
union select 2 , ‘Mohamed’ , ‘Osman’ , 250000.00
union select 3 , ‘mohamed’ , ‘omar’ , 120000.00
union select 4 , ‘mohamed’ , ‘Ali’ , 151000.00
union select 5 , ‘mohamed’ , ‘na7la’ , 150300.00
*****—-Method 4—–******

CREATE TABLE [dbo].[MyTest4](
[Id] [int] NULL,
[Fname] [varchar](100) NULL,
[Lname] [varchar](100) NULL,
[salary] [money] NULL
)

insert into MyTest4 (id ,fname ,lname , salary) values
(1 , ‘A’ , ‘B’ , 12.00),
(2 , ‘C’ , ‘D’ , 13.00),
(3 , ‘E’ , ‘F’ , 14.00),
(4 , ‘G’ , ‘H’ , 15.00),
(5 , ‘I’ , ‘J’ , 16.00)

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”

————————————————————————————————-

select
number,
case when number%(3*5)=0 then ‘Mostafa Mohamed Elmasry’
when number%5=0 then ‘Elmasry’
when number%3=0 then ‘Mostafa’
else
cast(number as varchar(10))
end as MyName
from
master..spt_values
where
type=’p’ and number between 1 and 100
———————————————————————
WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < 100
)
SELECT
CASE
WHEN Number % 3 = 0 AND Number % 5 = 0 THEN ‘Mostafa Mohamed Elmasry’
WHEN Number % 3 = 0 THEN ‘Mostafa’
WHEN Number % 5 = 0 THEN ‘Elmasry’
ELSE CONVERT(VARCHAR(3), Number)
END as myname
FROM Numbers
ORDER BY Number
———————————————————————-
DECLARE @counter INT
DECLARE @output VARCHAR(8)
SET @counter = 1
WHILE @counter < 101
BEGIN
SET @output = ”
IF @counter % 3 = 0
SET @output = ‘Fizz’
IF @counter % 5 = 0
SET @output = @output + ‘Buzz’
IF @COUNTER %(3*5) = 0
SET @OUTPUT = ‘Mostafa Elmasry’
IF @output = ”
SET @output = @counter
PRINT @output
SET @counter = @counter + 1
END
———————————————————–
DECLARE @num INT = 100

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

;WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)

SELECT TOP (@num)
Number,
CASE
WHEN Number % 15 = 0 THEN ‘FizzBuzz’
WHEN Number % 3 = 0 THEN ‘Fizz’
WHEN Number % 5 = 0 THEN ‘Buzz’
ELSE CONVERT(VARCHAR(7), Number)
END AS FizzBuzz
INTO #FizzBuzz
FROM Numbers

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

SELECT FizzBuzz FROM #FizzBuzz ORDER BY Number

DROP TABLE #FizzBuzz