Search This Blog

Monday 21 March 2016

Calculate Age in SQL Server

CREATE FUNCTION fnCalculateAge
(
       @DOB DATETIME
)
RETURNS NVARCHAR(50)
AS
BEGIN
       DECLARE @date DATETIME,
                     @years INT,
                     @months INT,
                     @days INT
       SELECT @date = @DOB

       SELECT @years = DATEDIFF(YEAR, @date, GETDATE()) - CASE WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
       SELECT @date = DATEADD(YEAR, @years, @date)

       SELECT @months = DATEDIFF(MONTH, @date, GETDATE()) - CASE WHEN DAY(@DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END
       SELECT @date = DATEADD(MONTH, @months, @date)

       SELECT @days = DATEDIFF(DAY, @date, GETDATE())

       DECLARE @Age NVARCHAR(50)
       SET @Age = Cast(@years AS  NVARCHAR(4)) + ' Years ' + Cast(@months AS  NVARCHAR(2))+ ' Months ' +  Cast(@days AS  NVARCHAR(2))+ ' Days Old'

       RETURN @Age

End

SELECT dbo.fnCalculateAge('1990-03-20')'Your Age'
Your Age
26 Years 0 Months 1 Days Old

Posted by at No comments:
Labels:

GROUP BY With ROLLUP and CUBE in SQL Server

ROLLUP & CUBE are very important keywords from reporting point of view as per my understanding.
ROLLUP & CUBE are used along with GROUP BY clause. 

ROLLUP returns the item wise total and at the end returns all items total summary.
CUBE returns the item wise total and at the end returns items wise total summary.

CREATE TABLE #table
(
       PurchaseId int identity,
       ProductName varchar(50),
       Quantity int,
       PurchaseDate datetime
)

INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Nirma',100,'2016-03-21')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Ghadi',200,'2016-03-21')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Lifebouy',300,'2016-03-21')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Godrej No 1',400,'2016-03-21')

INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Ghadi',300,'2016-03-22')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Lifebouy',400,'2016-03-22')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Godrej No 1',500,'2016-03-22')

INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Nirma',300,'2016-03-23')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Lifebouy',500,'2016-03-23')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Godrej No 1',600,'2016-03-23')

INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Nirma',300,'2016-03-24')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Ghadi',400,'2016-03-24')
INSERT INTO #table(ProductName,Quantity,PurchaseDate)VALUES('Godrej No 1',600,'2016-03-24')


SELECT * FROM #table
PurchaseId
ProductName
Quantity
PurchaseDate
1
Nirma
100
2016-03-21
2
Ghadi
200
2016-03-21
3
Lifebouy
300
2016-03-21
4
Godrej No 1
400
2016-03-21
5
Ghadi
300
2016-03-22
6
Lifebouy
400
2016-03-22
7
Godrej No 1
500
2016-03-22
8
Nirma
300
2016-03-23
9
Lifebouy
500
2016-03-23
10
Godrej No 1
600
2016-03-23
11
Nirma
300
2016-03-24
12
Ghadi
400
2016-03-24
13
Godrej No 1
600
2016-03-24

Query with GROUP BY clause

SELECT ProductName,PurchaseDate,SUM(Quantity)'Total Quantity'
FROM #table
GROUP BY  ProductName,PurchaseDate
ProductName
PurchaseDate
Total Quantity
Ghadi
2016-03-21
200
Godrej No 1
2016-03-21
400
Lifebouy
2016-03-21
300
Nirma
2016-03-21
100
Ghadi
2016-03-22
300
Godrej No 1
2016-03-22
500
Lifebouy
2016-03-22
400
Godrej No 1
2016-03-23
600
Lifebouy
2016-03-23
500
Nirma
2016-03-23
300
Ghadi
2016-03-24
400
Godrej No 1
2016-03-24
600
Nirma
2016-03-24
300

Query with GROUP BY...WITH ROLLUP clause

ROLLUP returns the item wise total and at the end returns all items total summary.

SELECT ProductName,PurchaseDate,SUM(Quantity)'Total Quantity'
FROM #table
GROUP BY ProductName,PurchaseDate WITH ROLLUP
ProductName
PurchaseDate
Total Quantity
Ghadi
2016-03-21
200
Ghadi
2016-03-22
300
Ghadi
2016-03-24
400
Ghadi
NULL
900
Godrej No 1
2016-03-21
400
Godrej No 1
2016-03-22
500
Godrej No 1
2016-03-23
600
Godrej No 1
2016-03-24
600
Godrej No 1
NULL
2100
Lifebouy
2016-03-21
300
Lifebouy
2016-03-22
400
Lifebouy
2016-03-23
500
Lifebouy
NULL
1200
Nirma
2016-03-21
100
Nirma
2016-03-23
300
Nirma
2016-03-24
300
Nirma
NULL
700
NULL
NULL
4900


SELECT  ProductName,[21/03/2016],[22/03/2016],[23/03/2016],[24/03/2016],[ALL]
FROM (
       SELECT
       CASE WHEN GROUPING(ProductName) = 0 THEN ProductName ELSE 'ALL' END AS ProductName,
       CASE WHEN GROUPING(PurchaseDate) = 0 THEN CONVERT(VARCHAR(10),PurchaseDate,103) ELSE 'ALL'  END AS PurchaseDate,
       SUM(Quantity)'Quantity'
       FROM #table
       GROUP BY ProductName,PurchaseDate
       WITH ROLLUP
) AS tbl
PIVOT
(
       SUM(Quantity) FOR PurchaseDate IN ([21/03/2016],[22/03/2016],[23/03/2016],[24/03/2016],[ALL])
) AS P
ORDER BY ProductName DESC
ProductName
2016-03-21
22-03-2016
23-03-2016
24-03-2016
ALL
Nirma
1900-04-09
NULL
300
300
700
Lifebouy
1900-10-26
400
500
NULL
1200
Godrej No 1
1901-02-03
500
600
600
2100
Ghadi
1900-07-18
300
NULL
400
900
ALL
NULL
NULL
NULL
NULL
4900

Query with GROUP BY...WITH CUBE clause

CUBE returns the item wise total and at the end returns items wise total summary.

SELECT ProductName,Purchasedate,SUM(Quantity)'Total Quantity'
FROM #table
GROUP BY ProductName,Purchasedate WITH CUBE
ProductName
Purchasedate
Total Quantity
Ghadi
2016-03-21
200
Godrej No 1
2016-03-21
400
Lifebouy
2016-03-21
300
Nirma
2016-03-21
100
NULL
2016-03-21
1000
Ghadi
2016-03-22
300
Godrej No 1
2016-03-22
500
Lifebouy
2016-03-22
400
NULL
2016-03-22
1200
Godrej No 1
2016-03-23
600
Lifebouy
2016-03-23
500
Nirma
2016-03-23
300
NULL
2016-03-23
1400
Ghadi
2016-03-24
400
Godrej No 1
2016-03-24
600
Nirma
2016-03-24
300
NULL
2016-03-24
1300
NULL
NULL
4900
Ghadi
NULL
900
Godrej No 1
NULL
2100
Lifebouy
NULL
1200
Nirma
NULL
700


SELECT  ProductName,[21/03/2016],[22/03/2016],[23/03/2016],[24/03/2016],[ALL]
FROM (
       SELECT
       CASE WHEN GROUPING(ProductName) = 0 THEN ProductName ELSE 'ALL' END AS ProductName,
       CASE WHEN GROUPING(PurchaseDate) = 0 THEN CONVERT(VARCHAR(10),PurchaseDate,103) ELSE 'ALL'  END AS PurchaseDate,
       SUM(Quantity)'Quantity'
       FROM #table
       GROUP BY ProductName,PurchaseDate
       WITH CUBE
) AS tbl
PIVOT
(
       SUM(Quantity) FOR PurchaseDate IN ([21/03/2016],[22/03/2016],[23/03/2016],[24/03/2016],[ALL])
) AS P
ORDER BY ProductName DESC
ProductName
2016-03-21
22-03-2016
23-03-2016
24-03-2016
ALL
Nirma
100
NULL
300
300
700
Lifebouy
300
400
500
NULL
1200
Godrej No 1
400
500
600
600
2100
Ghadi
200
300
NULL
400
900
ALL
1000
1200
1400
1300
4900

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)