I have some solution for SQL Server, SQL Server Reporting Services (SSRS), SQL Server Integration Services (SSIS) and some programming of .NET.
Friday, 10 October 2014
SQL - Get First Value of Every Category | Using While Loop
create table #temp
(id int,
monthDate date,
value int)
insert into #temp values(1,'1/1/2014',10)
insert into #temp values(1,'1/2/2014',15)
insert into #temp values(1,'1/3/2014',20)
insert into #temp values(2,'1/4/2014',25)
insert into #temp values(2,'1/5/2014',19)
declare @min int,@max int
select @min=MIN(ID) from #temp
select @max=MAX(ID) from #temp
select * from #temp
select top 0 * into #res
from #temp
while(@min<=@max)
begin
declare @minDT date
set @minDT=(select MIN(MonthDate) from #temp where id=@min)
insert into #res
select *
from #temp
where ID=@min
and Convert(Date,monthDate,103)=Convert(Date,@minDT,103)
set @min=@min+1
end
select * from #res
drop table #res
drop table #temp
Result:
(id int,
monthDate date,
value int)
insert into #temp values(1,'1/1/2014',10)
insert into #temp values(1,'1/2/2014',15)
insert into #temp values(1,'1/3/2014',20)
insert into #temp values(2,'1/4/2014',25)
insert into #temp values(2,'1/5/2014',19)
declare @min int,@max int
select @min=MIN(ID) from #temp
select @max=MAX(ID) from #temp
select * from #temp
select top 0 * into #res
from #temp
while(@min<=@max)
begin
declare @minDT date
set @minDT=(select MIN(MonthDate) from #temp where id=@min)
insert into #res
select *
from #temp
where ID=@min
and Convert(Date,monthDate,103)=Convert(Date,@minDT,103)
set @min=@min+1
end
select * from #res
drop table #res
drop table #temp
Result:
Thursday, 9 October 2014
Monday, 6 October 2014
Subscribe to:
Posts (Atom)