Techaray

Shailendra Saxena's Technical Blog

Using Case in Sql Server when Date is Null

clock October 28, 2013 05:03 by author ShailendraSaxena

Using Case in SQL server when Date is null

 

CASE WHEN mst.ms_termdt IS NULL
    THEN  DATEDIFF(HH,mst.ms_tscheddt,getdate())
    ELSE DATEDIFF(HH,mst.ms_termdt,getdate()) END AS Time_From_Install
FROM tblmaster mst



Using Common Table Expressions

clock February 7, 2013 02:32 by author ShailendraSaxena

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

·  Create a recursive query.

·  Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

·  Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

·  Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE

 

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

Example

The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, SalesOrderID, and OrderDate) and is defined as the total number of sales orders per year for each salesperson.

Transact-SQL

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO


Collation issue : Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_PRC_CI_AI" in the equal to operation.

clock November 22, 2012 05:56 by author ShailendraSaxena

Hi,

I faces issue Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_PRC_CI_AI" in the equal to operation.

My application is developed for English and Chinese language.One of my report was working fine in English server but on chinese server it was giving error.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_PRC_CI_AI" in the equal to operation.

I analysed the report and find out that there is error in my sp which is not returning the values.

then updated my sp and in where clause where i was comparing the values from the equal to operator.
i put collate Chinese_PRC_CI_AI on both side and worked well.

I hope it will be helpfull to you.



Sql Server Function Returns date in YYYYMMDD Format

clock October 24, 2012 08:20 by author ShailendraSaxena

Create FUNCTION [dbo].[fn_YMDDate]
(
      @dateParam     varchar(10)
)
RETURNS Varchar(10)
AS
BEGIN
      DECLARE @Return Varchar(10)

      Declare @YYYY  varchar(4)
      Declare @MM  varchar(2)
      Declare @DD  varchar(2)
      Declare @TMP varchar(2)
      Declare @strRemain  varchar(10)
      Declare @strSeprator  varchar(1)
      Declare @strOutPutSeprator  varchar(1)


      if  CHARINDEX ('/',@dateParam ,1)>0
            Begin
                  set @strSeprator='/'
            End
      Else
      if  CHARINDEX ('-',@dateParam ,1)>0
            Begin
                  set @strSeprator='-'
            End

      if  CHARINDEX ('.',@dateParam ,1)>0
            Begin
                  set @strSeprator='.'
            End           
       set @strOutPutSeprator = '.'    

      if  CHARINDEX (@strSeprator,@dateParam ,1)>0
            Begin
                  if  CHARINDEX (@strSeprator,@dateParam ,1) > 4 /*YYYY/MM/DD Format*/
                        Begin

                              set @YYYY=SUBSTRING(@dateParam,1 ,CHARINDEX(@strSeprator,@dateParam,1))
                              set @strRemain=SUBSTRING(@dateParam, CHARINDEX (@strSeprator,@dateParam,1)+1,len(@dateParam))
                              set @MM=SUBSTRING(@strRemain,1 ,(CHARINDEX(@strSeprator,@strRemain,1)-1))
                              set @DD=SUBSTRING(@strRemain, CHARINDEX (@strSeprator,@strRemain,1)+1,len(@strRemain))
                              IF CAST(@MM As int) > 12
                                BEGIN
                                    SET @TMP = @MM
                                    SET @MM = @DD
                                    SET @DD = @TMP
                                END

                        END
                  else
                        Begin
                              set @DD=SUBSTRING(@dateParam,1 ,(CHARINDEX (@strSeprator,@dateParam,1)-1))
                              set @strRemain=SUBSTRING(@dateParam, CHARINDEX (@strSeprator,@dateParam,1)+1,len(@dateParam))
                              set @MM=SUBSTRING(@strRemain,1,(CHARINDEX(@strSeprator,@strRemain,1)-1))
                              set @YYYY=SUBSTRING(@strRemain, CHARINDEX (@strSeprator,@strRemain,1)+1,len(@strRemain))

                              IF CAST(@MM As int) > 12
                                BEGIN
                                    SET @TMP = @MM
                                    SET @MM = @DD
                                    SET @DD = @TMP
                                END
                             
                        End

                        if len(@MM)=1
                       Begin
                              Set @MM='0'+@MM
                        End
                        if len(@DD)=1
                        Begin
                              Set @DD='0' +@DD
                        End

                        SET @Return =  @YYYY + @strOutPutSeprator + @MM + @strOutPutSeprator + @DD

     END
      RETURN ISNULL(@Return,'')
END



How to Restore SQL Server 2005 Suspect Database

clock July 31, 2012 12:02 by author ShailendraSaxena

How to Restore SQL Server 2005 Suspect Database

 
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER


Search

Calendar

<<  June 2017  >>
SuMoTuWeThFrSa
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

Archive

Tags

Categories


Visitors

FlagCounter

Flag Counter

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign In