Msg 8134, Level 16, State 1, Line 1480 Divide by zero error encountered.

Error Message: Msg 8134, Level 16, State 1, Line 1480 Divide by zero error encountered.

What this error message tell us that we are trying to divide a numerator value by zero.

The best way to handle this kind of error in sql code is by using NULLIF or case statement.

USE NULLIF

Select Numerator/NULLIF(Denominator, 0) FROM table

In this above SQL statement, whenever Denominator is converted to 0 if there is missing value.

USE CASE Statement

Select CASE WHEN DENOMINATOR = 0 Then NULL ELSE Numerator/Denominator
FROM Table

checking gap in data with start and end date in SQL Server

--- Create table to hold data and show that logic works


Create Table #TempDateRange
(ID int
, Start_Date Date
, End_Date Date
)

Insert into #TempDateRange
(ID, Start_Date, End_Date)
SELECT 1, '2013-01-01', '2013-09-30'
UNION
SELECT 1, '2013-10-01', '2013-12-31'
UNION
SELECT 1, '2014-01-01', '2014-12-31'
UNION
SELECT 2,'2013-01-01', '2013-08-31'
UNION
SELECT 2,'2013-10-01', '2013-12-31'
UNION
SELECT 2, '2014-01-01', '2014-12-31'


----- Logic to club the Eligibility Record into one if there is a Continuous Enrollment for the Member

SELECT
       s1.ID
    ,  s1.Start_Date
, MIN(t1.End_Date) AS End_Date

INTO #Consolidated_Data
FROM #TempDateRange s1
INNER JOIN #TempDateRange t1
ON t1.ID = s1.ID
AND s1.Start_Date < = t1.End_Date
AND NOT EXISTS
(
SELECT *
FROM #TempDateRange t2
WHERE t2.ID = t1.ID
AND DATEADD(DAY, 1, t1.End_Date) > = t2.Start_Date
AND t1.End_Date < t2.End_Date
)

  WHERE NOT EXISTS
     (
   SELECT *
   FROM #TempDateRange s2
   WHERE s2.ID = s1.ID
   AND s1.Start_Date > s2.Start_Date
   AND DATEADD(DAY, - 1, s1.Start_Date) < = s2.End_Date
 )

GROUP BY s1.ID , s1.Start_Date
ORDER BY s1.ID , s1.Start_Date;


-- Ranking Based on Dates to Calculate GAP Calculation

SELECT
   ID,
Start_Date,
End_Date,
RANK() OVER ( PARTITION BY ID   ORDER BY Start_Date ASC,  End_Date ASC   ) RN,
RANK() OVER ( PARTITION BY ID   ORDER BY Start_Date DESC, End_Date DESC ) RN_Inverse,
' ' AS GAP
INTO #Consolidated_Data_Rank
FROM #Consolidated_Data;

Select * FROM #Consolidated_Data
Select * FROM #Consolidated_Data_Rank

sql calculate total number of day in a given year

To get accurate number of days in a given year, we have to check if the given year is a leap year or not.  To see if it is a leap year, the year part of date (for example 2016 of 02/24/2016) should be divisible by 4 and there should be no remainder.

So let's see how we do this is SQL (2008 and above version)

First we need to declare a variable (BIT Type) and default it to 0.

Declare @IsLeapYear BIT = 0;

Then declare date variable for which you need to calculate. In my case I am using getdate() to set to current year and we know it is leap year

Declare @MyYear Date = Getdate();

Now we add logic to check if it leap year or not

    IF (YEAR( @MyYear ) % 4 = 0 AND YEAR( @MyYear ) % 100 != 0) OR
        YEAR( @MyYear ) % 400 = 0
        SET @IsLeapYear = 1

This way we know if it is leapyear, it will set @IsLeapYear to 1 and if not it will be 0

 Select 365 + @IsLeapYear

This select statement will tell me number of days in a given year.

So you whole SQL statement should be something like this.

 DECLARE @IsLeapYear BIT = 0       , @MyYear DATE = getdate()

    IF (YEAR( @MyYear ) % 4 = 0 AND YEAR( @MyYear ) % 100 != 0) OR
        YEAR( @MyYear ) % 400 = 0
        SET @IsLeapYear = 1

    Select 365 + @IsLeapYear

2012 and above version



DECLARE @year AS INT
SET @year=2013
Select DATEPART(dy,DATEFROMPARTS(@Year,12,31))
AS [NumberofDaysInYear]

What is a Heap Table in SQL?

Definition of a heap

 "an untidy collection of things piled up haphazardly"

So when we hear the word heap table at an interview, generally we say "A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order."

To understand above statement, we have to fully understand concept on nonclustered index and how it work with a clustered index and without a clustered index against a table.

Some of you might ask then what is point of creating a nonclustered index on a table without a clustered index?

To understand this, let's go through what Microsoft says about Clustered and NonClustered index

• Clustered
  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
• Nonclustered
  • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. 

Source: Microsoft MSDN link 

These are important point to remember from above reference

• For a clustered table, the row locator is the clustered index key
• The pointer from an index row in a nonclustered index to a data row is called a row locator
• For a heap, a row locator is a pointer to the row

So the question is how many row will a query scan before finding the relevant record(s)? These index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

Coming back to heap table, we can define a table is a heap which does not have any order of data stored in it.

How to get first and last date of current year with day name

SELECT
StartOfYear = Convert(Varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),101)
,CalendarFirstDayName = datename(dw,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
,LastDayOfYear = Convert(Varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1),101)
,CalendarLastDayName = datename(DW,DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1))

Creating a Perfect Calendar table in SQL

 ;WITH CTE_DatesTable
AS
(
  SELECT CAST('20000101' as date) AS [date] --- Change this to your beginning time
  UNION ALL
  SELECT   DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '20041231' --- Change this to your end time
)
Select
DateKey = substring(Cast([date] as varchar(10)),1,4) +substring(Cast([date] as varchar(10)),6,2)+substring(Cast([date] as varchar(10)),9,2)
,CalendarDate = [Date]
,[date] AS CalendarYearMonthDate
,CalendarYearYYYY = substring(Cast([date] as varchar(10)),1,4)
,CalendarYearMonthYYYYMM = substring(Cast([date] as varchar(10)),1,4) +substring(Cast([date] as varchar(10)),6,2)
,CalendarYearDDMMYYYY =substring(Cast([date] as varchar(10)),9,2)+'/'+substring(Cast([date] as varchar(10)),6,2)+'/'+substring(Cast([date] as varchar(10)),1,4)
,CalendarYearMMDDYYY =substring(Cast([date] as varchar(10)),6,2)+'/'+substring(Cast([date] as varchar(10)),9,2)+'/'+substring(Cast([date] as varchar(10)),1,4)
,CalendarDayCode = Upper(substring(datename(dw,[Date]),1,3))
,CalendarDayName = datename(dw,[Date])
,CalendarYearWeekCode =Datename(yy,[Date])+ RIGHT(DateName(wk,[Date]),2)
,CalendarYearWeekNumber = RIGHT(DateName(wk,[Date]),2)
,CalendarYearMonthName = Datename(MM, [date])
,CalendarYearMonthCode = substring(cast(Datename(MM, [date]) as Varchar(10)),1,3)
,CalendarMonthYearCode = substring(cast(Datename(MM, [date]) as Varchar(10)),1,3)+'-'+substring(Cast([date] as varchar(10)),3,2)
,[CalendarQuarter]='Q'+ Cast(DATENAME(quarter, [date]) AS Varchar(1))
,CalendarQuarterYearCode = 'Q'+ Cast(DATENAME(quarter, [date]) AS Varchar(1))+'-'+substring(Cast([date] as varchar(10)),3,2)
,CalendarYearQuarterCode = substring(Cast([date] as varchar(10)),1,4)+'Q'+ Cast(DATENAME(quarter, [date]) AS Varchar(1))
,CalendarYearIsLeapYear = CASE WHEN (cast(year([date]) as int) % 4 = 0 AND cast(year([date]) as int) % 100 <> 0) OR cast(year([date]) as int) % 400 = 0 THEN 'Y' ELSE 'N' END
,FiscalYear = cast(case when month([date]) > 10 then year([date]) else year([date])-1 end as varchar(4))--Depending when your fiscal year begin- in this it begin on 11/1/YYYY




from CTE_DatesTable
OPTION (MAXRECURSION 0);

SQL Query: How to identify data with Beginning and Ending Character

Say that you have data like this in tour table and you are asked to find all the rows where last value in your result end in alph character such as ID 2, ID4 rows only.

Let's see how we can identify these rows using sql

Create sample data for this purpose.

CREATE table dbo.Test
( ID int IDENTITY,
 [Value] Varchar(10)
)
;
INSERT INTO dbo.TEST VALUES ('1000');
INSERT INTO dbo.TEST VALUES ('1000A');
INSERT INTO dbo.TEST VALUES ('B1000')
INSERT INTO dbo.TEST VALUES ('AAAAA')


SELECT * FROM dbo.Test

To identify rows which end in alpha character, use this query


Select * FROM dbo.Test
WHERE [Value] NOT LIKE  '%[0-9]'