Display row value as a TABLE column using SQL select statement

Requirement:

Summarize PIFAmt column divide by Summarize Factors column by StateName column and diplay prior & current year data as a table column by auto and property business values.

Table Structure:






Table Data:






You could avoid the following errors when you execute the enclosed SQL select statement and display the result as I explained in my requirement.

Errors:

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

Server: Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SQL Select Statement:

SELECT STATENAME,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_CURR_YR,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) /
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_CURR_YR
FROM FACT
WHERE BUSINESS IN ('AUTO', 'PROPERTY') AND EFFYEAR >= 2006 AND
SEGMENT = (CASE WHEN BUSINESS = 'AUTO' THEN SEGMENT ELSE 'HOMEOWNERS' END)
GROUP BY STATENAME

GO

Results:

An Introduction to Clustered and Non-Clustered Index Data Structures

When I first started using SQL Server as a novice, I was initially confused as to the differences between clustered and non-clustered indexes. As a developer, and new DBA, I took it upon myself to learn everything I could about these index types, and when they should be used. This article is a result of my learning and experience, and explains the differences between clustered and non-clustered index data structures for the DBA or developer new to SQL Server. If you are new to SQL Server, I hope you find this article useful.

As you read this article, if you choose, you can cut and paste the code I have provided in order to more fully understand and appreciate the differences between clustered and non-clustered indexes.

When To Use the Order By Clause

When I was using SQL Server, I was not using the "order by" clause properly. I always say myself that when I want the data in a particular order, I should use order by clause in select statement otherwise leave to the SQL Server. I presumed MS-SQL Server will return the data in its own order. I was not even thinking that am I using and doing correctly. In my initially days in my career, I was happy to retrieve the data and not knowing how it comes. It is the nature for the software engineers at the premature stage or using new command or tool or technology. They will not knowing properly or doing enough research of the each command before commence using the command. I would say it could be because of competition in the IT field and immature in IT field.

Where do we get the word "Order?" I have heard and seen in so many places the use of "Order" word. I have come across the listed places the "Order" word used regularly.


When an applicant applies for a student visa, visa officer will politely inform to the application order the applications and come back.
When a procurer gives a draft to the registrar officer, officer will politely return back the draft and request to order the draft.
Firm will display the designation in a proper order.
Construct a house; we will clean and even the land before establishing the Pillar.



The ultimate aim is "First IN has to first come OUT"

I have decided and kept in my mind that somewhere down the line in my IT career, I should know when I should use "order by" clause in the select statement. I know it will be on one FINE DAY". I will spend some time on "order by" command. That one fine day is TODAY to explore myself and find the ways to use the "order by" clause. This could be useful for a new developer and dba to SQL Server to avoid confusion on using "order by clause" and knowing when to use "order by" clause command.

To come along with me in this article, I have paste the script and result of the output. Example of the script can be used in a sql server to understand completely after reading the article.

User-defined data type dependencies

MSSQLServer has got sp_depends system sp to display the dependencies for a stored procedure, table and view. sp_depends does not support to display the User-defined data type dependencies. For custom database, we will create user defined data types (UDDT) and add this UDDT to a column/variable in a table/stored procedure. This sp (UDTDepends) will help to display the User-defined data type dependencies in a database for a Tables and Stored Procedure.

Compile this sp in a database and execute the same. It will display the dependencies for the user defined data types if it exists in a database.

Criticize are welcome.

if exists (select * from sysobjects where id = object_id(N'[dbo].[UDTDepends]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UDTDepends]
GO

Create Proc UDTDepends
As

SET NOCOUNT ON

SELECT so.name As "ObjectName", sc.name As "ColumnName", type_name(st.xusertype) As "UserDefinedDataType",
type_name(sc.xtype) As "DataType", st.length As "DataLength"

FROM sysobjects so, syscolumns sc, systypes st

WHERE so.xtype In ('U', 'P') and sc.id = so.id and sc.xusertype = st.xusertype and
sc.usertype Not In (0, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 19, 20, 21, 22, 23, 24, 80)

ORDER BY so.xtype desc, so.name, st.name

SET NOCOUNT OFF

Stabilize Index

This sp is used to update index in a database. This sp has got three optional varchar parameters.
First parameter will take the tablename. Second parameter will take the indexname. Third parameter will take the fillfactor.

The following option we can execute the sp in a user database.

1. exec StabilizeIndex
It will update all index in a user database

2. StabilizeIndex 'TableName'
It will update all index in a table

3. StabilizeIndex 'TableName', 'IndexName'
It will update an index in a table.

4.StabilizeIndex 'TableName', 'IndexName', ‘FillFactor’
It will update an index in a table with fill factor.

Note: This is not used in system database.

/* To Execute the sp in various options
--StabilizeIndex
--StabilizeIndex 'TableName'
--StabilizeIndex 'TableName', 'IndexName'
--StabilizeIndex 'TableName', 'IndexName', 'FillFactor'
*/
if exists (select * from sysobjects where id = object_id(N'[dbo].[StabilizeIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StabilizeIndex]
GO

Create Proc StabilizeIndex
(
@TableName Varchar(100) = '',
@IndexName Varchar(100) = '',
@FillFactor Varchar(3) = ''
)
As

Declare @TableID Int, @ObjectName Varchar(100), @IndName Varchar(100), @SQL Varchar(8000)

SET NOCOUNT ON

IF Len(@TableName) = 0
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U'
ORDER BY name
End
Else
Begin
DECLARE table_cursor CURSOR FOR
SELECT id, name
FROM sysobjects
WHERE xtype = 'U' and id = object_id(@TableName)
ORDER BY name
End

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName

WHILE @@FETCH_STATUS = 0
BEGIN

IF Len(@IndexName) = 0
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and indid > 0
End
Else
Begin
DECLARE index_cursor CURSOR FOR
SELECT name
FROM sysindexes
WHERE id = @TableID and name = @IndexName and indid > 0
End
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @IndName

WHILE @@FETCH_STATUS = 0
BEGIN
IF Len(@FillFactor) = 0
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ')'
End
Else
Begin
Set @SQL = 'DBCC DBREINDEX (' + @ObjectName + ',' + @IndName + ',' + @FillFactor + ')'
End

Exec (@SQL)

FETCH NEXT FROM index_cursor INTO @IndName

END

CLOSE index_cursor
DEALLOCATE index_cursor

FETCH NEXT FROM table_cursor
INTO @TableID, @ObjectName
END

CLOSE table_cursor
DEALLOCATE table_cursor

SET NOCOUNT OFF