How To Find The Length of Data in Text and Ntext Data type :P

Hi All,

We have string function Len for finding the length of a string with VARCHAR data type.

We can’t use the LEN function for Text and Ntext data type.

For this we have another function Datalength. And we can use LEN also for this, but if u wants to use LEN u need to CAST the Text datatype column. See the below example.   

EX:   

DATALENGTH(text_column)

 LEN (CAST (text_column as varchar))

 Thanks For Visiting Blog.

Generating Multiplication Matrix Dynamically In SQL

Hi All,

After a long time, i come up with some new stuff in T-SQL

ie. Generating Multiplication Matrix Dynamically By Passing a Input as Matrix size ,See below images so that you can easily understand :) .

Script For Above OUTPUT: (Here i am passing the 12 as my input)

DECLARE @k int = 12

IF(@K>0)

BEGIN

DECLARE @FirstPart VARCHAR(max),@SecondPart VARCHAR(MAX) 

SET @FirstPart  = 'DECLARE @i int = 1,@j int = 1,@k int = '+CAST(@k AS VARCHAR)+'  DECLARE @table table ([ ] int'

SET @SecondPart = 'SELECT @i AS [ ], @i*@j AS [1]'  

DECLARE @l INT= 1

WHILE(@l<=@k)

BEGIN

SET @FirstPart = @FirstPart+',['+CAST(@l AS VARCHAR)+'] INT'

IF(@l<@k)

BEGIN

SET @SecondPart = @SecondPart + ',@i*(@j+'+CAST(@l AS VARCHAR)+') AS ['+CAST(@l+1 AS VARCHAR)+']'

END

SET @l= @l+1

END

SET @FirstPart = @FirstPart+')'

DECLARE @ThirdPart VARCHAR(MAX) =

'WHILE (@i<=@k)

BEGIN

INSERT INTO @table '

+@SecondPart+

'SET @i = @i+'+CAST(1 AS VARCHAR)+'

END

SELECT * FROM @table

'

EXEC(@FirstPart+@ThirdPart)

END

ELSE

SELECT 'Sorry We Cant Process This Request' AS Alert

OUTPUT:

 Thanks For Visiting Blog.

Deleting Duplicate Records From The Table in Different Way

Hi All,

Here I am going to explain how to DELETE duplicate records from table using CTE and Row_Number()

Table and Data Script :
CREATE TABLE [dbo].[Deleting Duplicate Record](
[Id] [int] NULL,
[Name] [varchar](50) NULL,
[Department] [varchar](50) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (1, N'Santosh', N'BI', CAST(0x0000A1F701592FF6 AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (1, N'Santosh', N'BI', CAST(0x0000A1F70159578E AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (2, N'Santosh', N'MSSQL', CAST(0x0000A1F701597B6E AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (2, N'Santosh', N'MSSQL', CAST(0x0000A1F7015987FC AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (3, N'Krishna', N'JAVA', CAST(0x0000A1F70159BC02 AS DateTime))
INSERT [dbo].[Deleting Duplicate Record] ([Id], [Name], [Department], [CreatedDate]) VALUES (4, N'Krishna', N'SPRING', CAST(0x0000A1F70159D1F6 AS DateTime))
GO


Solution 1: Here i am Using CreatedDate column for order to give row number , by default it take ASC order.

;WITH Duplicate as
(SELECT *,row_number() OVER(partition by Id,Name,Department order by CreatedDate) as Numbering  from [Deleting Duplicate Record])

DELETE FROM Duplicate WHERE Numbering>1

Here i am deleting the records based on Createddate,means which row is inserted last that one is going to delete.

This is common and every one can DELETE rows based on the created date but I want to DELETE rows WITH OUT using the Createddate column.

Solution 2: Here i am Deleting the Duplicate rows WITH OUT using the CreatedDate Column,and deleting in inserted order only.

;WITH Duplicate as
(SELECT *,row_number() OVER(partition by Id,Name,Department order by (SELECT 0)) as Numbering  from [Deleting Duplicate Record])

DELETE FROM Duplicate WHERE Numbering>1

Here i am just adding SELECT 0 to Order by Clause so that it will give u the row number as inserted order.

this is little bit different way to delete duplicate records from the table.


Thanks For Visiting Blog.

With Out USING Union We Can Get the SUM Of Rows At Group Level and Whole Records At Bottom

Hi All,


Most of the time we will use the UNION to get the whole sum of records group by some column and showing at each group level sum.

But in this Case we need to hit the database two times , then also  we cant get the whole Records sum, for that again we need to add one more UNION.

So to over come this situation i used the ROLLUP. 

SCRIPT:

CREATE TABLE [dbo].[SUMING_Data](
[ID] [int] NULL,
[Amount] [int] NULL,
[NAME] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 100, N'x')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 200, N'y')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 300, N'z')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (2, 200, N'p')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (4, 200, N'q')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (2, 400, N'r')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (4, 600, N's')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (3, 100, N't')

GO

SELECT * FROM [SUMING_Data]

in the above screen each color represent the different group name

But i want out put like each group level sum and all rows sum at the bottom.

for that previously i used UNION 

THIS IS USING UNION, HERE WE NEED TO WRITE TWO QUIRES


SELECT * FROM SUMING_Data
UNION
SELECT [ID],sum([Amount]),'Total' FROM SUMING_Data GROUP BY [ID]

Problem:

In the above screen shot red line showing the individual amount ,and blue one showing the Group Total 
But if i want all the records total , again i need to add one more UNION and one more hit to the database 

To overcome the above problem follow the below query.

Solution:

WITH OUT USING UNION ALSO WE CAN DO THIS ,USING THIS OUR QUERY IS VERY SIMPLE

Method 1:

SELECT  ID, SUM(AMOUNT) Amount, ISNULL(NAME,'TOTAL')GroupLevel  
FROM    SUMING_Data  
GROUP BY  ID,NAME 
WITH  ROLLUP

if we are using  isnull query we will get below out put:

Method 2:

SELECT ID, SUM(AMOUNT) Amount, GROUPING(name) GroupLevel 
FROM  SUMING_Data  
GROUP BY  ID,name 
WITH  ROLLUP

If we are using the Grouping we will get below out put ,here 0 indicates the individual amount and 1 indicates the SUM amount and at the bottom u can find the Total Amount in both Outputs

This is all about RollUP in SQL 

Thanks For Visiting Blog.

How simple we can send Mail using SQL Server

Hi All ,

We can send email through SQL Server also,for this follow some simple steps.

Step 1:
          Open SQL Server Management Studio and then connect to server and go to  management tab in that right click on Database Mail then click on configure mail,then click on setup database mail then click on next.



Step 2:
           Now u can give profile name for the email and create new SMTP account , for that click on add and click on new account fill accordingly as shown in below image.

Step 3:
           Now manage profile Security and select u r's profile then click next and next and finsh.

Step 4:

           Now test the mail ,for that again right click on Database Mail and select Send Test E-Mail .and u will get email.

Thanks For Visiting Blog.

HOW TO USE CACHE TRANSFORM

            

           HOW EFFECTIVELY WE CAN USE CACHE TRANSFORM IN SSIS

Scenario:

• While using look up transformation, ur pointing to look up table which is available in database for checking the source data table.
• If u r checking for single source data table there is no problem because it only one time hit the database for getting the data from lookup table.
• But in real time scenario u may use more than one source table to look up on one single lookup table, in this situation database hits will increase(that many times it will hit the database for lookup table) and performance will automatically decreases.

To overcome this situation I used cache transformation in SSIS Data flow task.

Explanation:

Step 1:

           Drag and drop data flow task in control flow, open data flow and add oledb source and make all the database connection then choose the look up table from that database.

Step 2:

           Now add cache transformation to data flow and map oledb to cache and configure the cache connection manager like below.

        Double click on cache transformation and check the use file cache then select the .caw (cache file) file (for getting this .caw file open notepad and save as .caw extension.).

      After that go to column tab in cache connection manager to select one index column.

Step 3:

           Now add another data flow task in control flow and then open data flow and add oledb source for getting the source table. Add look up transformation to data flow and then map source table to look up then open look up and in cache mode select full cache then in connection type use cache connection manager.

Step 4:

           Now go to connection tab in look up and select that cache connection manager (already created connection to .caw file).

     

     Then go to columns tab and map on which column u r going to look up.

Then click on OK.

Now u can redirect the rows (matched and not matched) to desired destinations.

Finally:

             U can use same cache file so many times u want to look up with source tables so that it will hit the database only single time. 

In this above screen shot u can find the three different sources but having the one look up file so this reducing the database hits ,and increases the performance. 

THANKS FOR VISITING BLOG

FINDING TOP Nth RECORD FROM TABLE WITH OUT CHANGING THE ORDER OF RECORDS INSERTED

HI ALL,

FINDING TOP Nth RECORD FROM TABLE WITH OUT CHANGING THE ORDER OF RECORDS INSERTED (how they inserted in table same way i needed)

I already posted row_number() in my blog to get row number for each record using that logic with the help of sub query i am getting the result.

DATA:

Declare @test table(id int,name varchar(30))
insert into @test values(589,'ravi')
insert into @test values(689,'ramu')
insert into @test values(789,'rahul')
insert into @test values(525,'raghu')
insert into @test values(575,'ramesh')
insert into @test values(532,'ragav')
insert into @test values(556,'raju')
insert into @test values(500,'rita')
insert into @test values(501,'rama')
insert into @test values(505,'RIJJU')--10 Record while inserting the data to table
insert into @test values(589,'rajaini')
insert into @test values(456,'ranjith')
insert into @test values(123,'ramana')
select * from @test

QUERY:
SELECT S.* FROM(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0))AS RID,ID,NAME FROM @TEST)S WHERE S.RID=10

THANKS FOR VISITING BLOG :)

USING SUB QUERY OR CTE

HI ALL 

I AM HAVING THE REQUIREMENT LIKE CUSTOMER VISITING THE STORE SO MANY TIMES ,

THE MANAGER WANT TO KNOW THE EACH CUSTOMER HOW MANY TIMES VISITED THE STORE AND HOW MUCH AMOUNT HE SPENT ON THE GOODS 

WE CAN GET THE COUNT OF EACH CUSTOMER USING AGGREGATE FUNCTION BUT WE NEED TO SHOW THE COUNT OF EACH CUSTOMER BESIDE THE AMOUNT COLUMN.

FOR THIS I USED SUBQUERY OR CTE TO GET THIS REQUIREMENT

FIND THE BELOW SCRIPT FOR GETTING THIS RESULT

DECLARE @SUBQUERY TABLE(CUSTOMERID INT,AMOUNT INT)

INSERT INTO @SUBQUERY VALUES(1,500)

INSERT INTO @SUBQUERY VALUES(1,600)

INSERT INTO @SUBQUERY VALUES(1,700)

INSERT INTO @SUBQUERY VALUES(1,800)

INSERT INTO @SUBQUERY VALUES(2,500)

INSERT INTO @SUBQUERY VALUES(2,600)

INSERT INTO @SUBQUERY VALUES(3,700)

INSERT INTO @SUBQUERY VALUES(3,800)

INSERT INTO @SUBQUERY VALUES(4,700)

INSERT INTO @SUBQUERY VALUES(4,900)

INSERT INTO @SUBQUERY VALUES(4,200)

INSERT INTO @SUBQUERY VALUES(4,100)

INSERT INTO @SUBQUERY VALUES(4,300)

INSERT INTO @SUBQUERY VALUES(4,400)

--AGGREGATE QUERY:

SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID

--THIS IS USING SUBQUERY:

SELECT MA.*,SUB.NUMBEROFTIMESVISITED FROM (SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID)SUB

JOIN @SUBQUERY MA ON SUB.CUSTOMERID=MA.CUSTOMERID 

--THIS IS USING CTE:

;WITH CTE AS (SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID)

SELECT MA.*,CTE.NUMBEROFTIMESVISITED 

FROM CTE

JOIN @SUBQUERY MA ON CTE.CUSTOMERID=MA.CUSTOMERID 

THANKS FOR VISITING BLOG

Hi ALL

To day i am going to use the Replicate.
I am having the requirement like i need to genrate a pattern(employeeNumber) ,for this pattern we need to use two columns id and department id

Output:

        If  id is 1 and departmentid is 1 then i need out put like YH0010001

Rules:

1) first two characters(YH) are common for everyone.
2) next three digits should be department number(it having max three digit only ,if it is 1 u need to represent          as 001,if it is 2 then 002 ,if it is 23 then 023, if it 333 then 333 )
3) next four digits are id of the employee(it having max four digit only ,if it is 1 u need to represent as 0001,if it is 2 then 0002 ,if it is 23 then 0023, if it 333 then 0333 )

If id lenth is less than than the four digits then u need to keep Zero's
If Department number lenth is less than the three digits then u need to keep Zero's

so follow the above conditions and write a SQL.

DATA:

DECLARE @EMPLOYEE TABLE (ID VARCHAR(30),EMPNAME VARCHAR(30),DEPTID VARCHAR(30))
INSERT INTO @EMPLOYEE VALUES('1','SANTOSH','1')
INSERT INTO @EMPLOYEE VALUES('2','SUDEEP','2')
INSERT INTO @EMPLOYEE VALUES('33','KISHOR','12')
INSERT INTO @EMPLOYEE VALUES('444','PHANI','333')
INSERT INTO @EMPLOYEE VALUES('12','GAURAV','1')
INSERT INTO @EMPLOYEE VALUES('222','PRADEEP','2')
INSERT INTO @EMPLOYEE VALUES('4444','KRISHNA','3')


SELECT * FROM @EMPLOYEE

I NEED SECOND RESULT SET......


FINAL QUERY:


SELECT *,'YH'+REPLICATE('0',3-LEN(DEPTID))+DEPTID+REPLICATE('0',4-LEN(ID))+ID AS EMPLOYEEID FROM @EMPLOYEE

Explanation: 
    
for getting this result i used replicate which is a string function using this u can repeat the value for how many time s u want ....



THANKS FOR VISITING BLOG :) 

Validating Credit or Debit Card Using Small SQL Query

Hi All

Validating Credit or Debit Card using SQL


I have only the Expiry Date Of Credit/Debit Card in String formate like '2012-09' .

If i pass this as input and i need to get only the valid cards data.

declare @exptbl table(id int,expdate varchar(50))
insert into @exptbl values(1,'2012-09')
insert into @exptbl values(2,'2012-02')
insert into @exptbl values(3,'2012-05')
insert into @exptbl values(4,'2013-06')
insert into @exptbl values(5,'2013-07')

If u query the table variable you will get below out put ,according to our requirement  i  need to get only Marked Records.

I want Below output :

For Getting above output please use below query:

SELECT * FROM @EXPTBL

WHERE CONVERT(DATETIME,EXPDATE+'-01')>=CONVERT(DATETIME,CONVERT(VARCHAR(50),DATEPART(YYYY,GETDATE()))+'-'+CONVERT(VARCHAR(50),DATEPART(MM,GETDATE()))+'-01')

Thanks for Visiting the Blog

Santosh 

  

Getting Weather Report Using SSIS

Getting Weather report using SSIS through WEB Services.

URLS:

 Weather Web Service URL: http://www.webservicex.net/globalweather.asmx?WSDL

 WSDL File Data URL: http://www.webservicex.com/globalweather.asmx?wsdl 

For this Follow Below Simple Steps:

1) Create New SSIS Package.

2) In Control flow Drag the Web Service Task and configure the Connection. For that Double Click on Web service task u will get below screen

3) Click on HttpConnection u will Get  below Screen and Paste the Weather Web Service URL(URL Located in top) and click on Test Connection then click ok.

4) Next Click on WSDL file , for that first copy XML Data from Second URL( http://www.webservicex.com/globalweather.asmx?wsdl ) and paste in notepad and save as .WSDL  and map that file here.

 5) After adding the WSDL file goto Input tab and Select the Service,Method and give the input values as Variable(Create variables).Follow the below screen.

6) And finally Store the Weather Report in Variable.

7) Now Add Execute SQL Task in Control Flow and connect to the data base for getting the City and Country information from table (Find the table and data Script below) and paste this (Select City,Country  from CityandCountry where id=?) in SQLStatement.

8) Now Goto Prarmeter Mapping Tab and create a int type variable(Follow Below screen).

9) Next Goto Result Set tab and Map the Result to City and Country Variables.

10) Drag one more Execute SQL Task this is for Storing the data to Data base tables(Find the table Script below) and paste this ( INSERT INTO [dbo].[WeatherReport]

           ([Id]

           ,[City]

           ,[Country]

           ,[WeatherReport])

values(?,?,?,?) ) in SQLStatement(Follow the below screen).

11) And Goto Parameter Mapping and Again map all the variables(Follow Below screen).

12) And Finally Add ForLooooP to Control Flow and Configure this like Below.

13) And Execute the Package and check the Database table, u will found the XML formated Weather report. from that u can get the Weather Report Info.

Scrip for Tables:

CREATE TABLE [dbo].[CityandCountry](

[ID] [int] NOT NULL,

[City] [varchar](50) NULL,

[Country] [varchar](50) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[WeatherReport](

[Id] [int] NOT NULL,

[City] [varchar](50) NULL,

[Country] [varchar](50) NULL,

[WeatherReport] [varchar](max) NULL,

[DateOfReport] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT [dbo].[CityandCountry] ([ID], [City], [Country]) VALUES (1, N'Hyderabad', N'INDIA')

GO

INSERT [dbo].[CityandCountry] ([ID], [City], [Country]) VALUES (2, N'Delhi', N'INDIA')

GO

ALTER TABLE [dbo].[WeatherReport] ADD  CONSTRAINT [DF_WeatherReport_DateOfReport]  DEFAULT (getdate()) FOR [DateOfReport]

GO

Thanks For Visiting the Blog

Santosh 

How To Get The Row Number In Insert Order Only (Dnt Change The Order Of Data In The Table ) As New Cloumn

CREATE TABLE [dbo].[Rownumber](
[Name] [varchar](50) NULL
) ON [PRIMARY]

GO

INSERT [dbo].[Rownumber] ([Name]) VALUES ('Gaurav')
GO
INSERT [dbo].[Rownumber] ([Name]) VALUES ('Sudeep')
GO
INSERT [dbo].[Rownumber] ([Name]) VALUES ('Kishor')
GO
INSERT [dbo].[Rownumber] ([Name]) VALUES ('Santosh')
GO

I DNT WANT ABOVE OUT PUT, I WANT BELOW OUT PUT.....LETS TRY THIS

This is the Query for this output:

SELECT  Name,

                 ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS InsertOrderRowNumber

FROM     Rownumber