SENARIO_2

My input table is like…………

Stuno Marks address
1001 1 US
1002 1 US
1003 1 UK
1004 1 UK
1005 2 LANDON
1006 2 LONDON
1007 3 BRAZAIL
1008 3 KENADA

MY OUPUT LIKE……………….

Stuno Marks address
1001 1 US
1003 1 UK
1005 2 LANDON
1007 3 BRAZAIL
1008 3 KENADA

ANS:

IF EXISTS(SELECT *FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='STU_DE')

 BEGIN

   DROP TABLE STU_DE

 END

CREATE TABLE STU_DE(STU_ID INT,MARKS INT,ADDR VARCHAR(50))

INSERT INTO STU_DE VALUES (1001,1,'US'),(1002,1,'US'),(1003,1,'UK')

INSERT INTO STU_DE VALUES (1004,1,'UK'),(1005,2,'LONDON'),(1006,2,'LONDON'),(1007,3,'BRAZAIL'),(1008,3,'KENADA')

SELECT STU_ID,MARKS,ADDR from(

select STU_ID,MARKS,ADDR, row_number() Over( partition by MARKS,ADDR order by STU_ID )R from STU_DE)a WHERE a.R =1 

;WITH CTE AS( SELECT STU_ID,MARKS,ADDR,ROW_NUMBER() OVER (PARTITION BY MARKS,ADDR ORDER BY STU_ID) AS ROW_NO FROM STU_DE)

SELECT STU_ID,MARKS,ADDR FROM CTE WHERE ROW_NO=1

SIMPLE SENORIO_1

Inputs
--------
Column 1 Column 2
01-Jan-2015 20
02-Jan-2015 30
04-Jan-2015 40
05-Jan-2015 10
07-Jan-2015 10

Write a Query to Display All Input values (Column A & Column B) + All missing values without using any TEMP table.

Output Expected
-----------------------
Column 1 Column 2
01-Jan-2015 20
02-Jan-2015 30
03-Jan-2015 Null
04-Jan-2015 40
05-Jan-2015 10
06-Jan-2015 Null
07-Jan-2015 10

ANS:

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='sample_b')

begin

drop table sample_b

end

create table sample_b(column1 date,column2 int)

insert into sample_b values('01-Jan-2015',20),('02-Jan-2015',30),('04-Jan-2015',40),('05-Jan-2015',10),('07-Jan-2015',10)

declare @min_date date

 select @min_date=MIN(column1) from sample_b

--print @min_date

if object_id('tempdb..#tempe') is not null

begin

drop table #tempe

end

create table #tempe(dat date,id int)

declare @max_date date 

select @max_date=MAX(column1) from sample_b

declare @colummn int

--select @date

while (@min_date < = @max_date)

begin

   select @colummn=column2 from sample_b where column1= @min_date

insert into #tempe (dat,id)

select distinct  @min_date,@colummn 

 select @min_date=(select dateadd(dd,1,@min_date))

end

--select * from sample_b

select DISTINCT A.DAT,b.COLUMN2 from #tempe a

left join 

   sample_b b

   on b.column1=a.dat

   --where b.column2 is  null