Getting duplicate records with count

SELECT Type, count(*) as TotalCount FROM tblAgentTran 
where kcode='4' group by type 
having count() > 1  order by COUNT() DESC

latest / Min /Max Value Group wise

select f.Kcode, f.RunningBalance
from (
  select Kcode,max(createddatetime) as latestdate
from tblAgentTran
group by Kcode
) as x inner join tblAgentTran as f on f.Kcode = x.Kcode and f.createddatetime = x.latestdate;

Hello All,

     From Recent Search , Vinay find good  & quick solution for inserting record from one old table to new table, with specific columns

 

 INSERT INTO new_table (columns....)
SELECT columns....
FROM initial_table where [condition]
using this query we can insert some records form old table to new table with given condition.
thanks vinay for your solution.

Hi All,

   Recently @ time of checking records in Database table with case sensitive.

  We are firing query like

        select * from Tablename where strPassword = 'Vinay343@'

  at the same time if we fire query like

        select * from Tablename where strPassword = 'VINAY343@'

 we are getting same result so technically me & vinay think that it is not good to get result if user send case sensitive input

 after searching we found the solution that if we want to check exact match use query like this

  select * from Tablename where strPassword COLLATE Latin1_General_CS_AS='Vinayshah0!'

here We get exact match as per the input

Thanks Vinay for sharing this information

Cheers!!!!

Calculate Age from Sql function

In one of my interview , interviewer asked me how should  you calculate age from date of birth.

     As i m not too strong in SQL server , i have started from .Net code  for calculating Age from  DOB.
     but not success ed in that.  after searching in books & discussing with friends[Vinay Shah]
     come to conclusion that we can get Age  from Sql Function DATEDIFF.

   in SQL Server 2005 u can use Datediff function

   Sample Query = select datediff (year, '1988-05-17 00:00:00.000' , getDate())

  from above query you will get the Age in year  OUTPUT = 24
 instead of  1988-05-17 00:00:00.000   you can enter your DOB & get your Age.


  in Oracle 

   select ROUND((SysDate - DOB)/365) as Age from cust_table

  here you we will get age of the customers where DOB should have Date DataType

      Using Datediff function we can calculate not only age in years but   also following

              

Unit of time	Query	Result
NANOSECOND	SELECT DATEDIFF(NANOSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133')	4321300
MICROSECOND	SELECT DATEDIFF(MICROSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133')	4321
MILLISECOND	SELECT DATEDIFF(MILLISECOND,'2011-09-23 17:15:22.004','2011-09-23 17:15:22.548')	544
SECOND	SELECT DATEDIFF(SECOND,'2011-09-23 17:15:30','2011-09-23 17:16:23')	53
MINUTE	SELECT DATEDIFF(MINUTE,'2011-09-23 18:03:23','2011-09-23 17:15:30')	-48
HOUR	SELECT DATEDIFF(HH,'2011-09-23 18:03:23','2011-09-23 20:15:30')	2
WEEK	SELECT DATEDIFF(WK,'09/23/2011 15:00:00','12/11/2011 14:00:00')	12
DAY	SELECT DATEDIFF(DD,'09/23/2011 15:00:00','08/02/2011 14:00:00')	-52
DAYOFYEAR	SELECT DATEDIFF(DY,'01/01/2011 15:00:00','08/02/2011 14:00:00')	213
MONTH	SELECT DATEDIFF(MM,'11/02/2011 15:00:00','01/01/2011 14:00:00')	-10
QUARTER	SELECT DATEDIFF(QQ,'01/02/2011 15:00:00','08/01/2011 14:00:00')	2
YEAR	SELECT DATEDIFF(YY,'01/02/2011 15:00:00','01/01/2016 14:00:00')	5

If we want to Calculate Sum of 2 tables count we can use following  query

SELECT SUM(c)
FROM (
  SELECT COUNT(id) AS c FROM table1
  UNION ALL
  SELECT COUNT(id) FROM table2
  ) as b

Delete Duplicate Rows

/* Create Table with 7 entries - 3 are duplicate entries */

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO