"Lam Nguyen" <LamNguyen@discussions.microsoft.com> wrote in message
news:EAF9F716-540F-4682-B3A3-DC19E633B4AE@microsoft.com...
> Hi all,
>
> I need to get the desire results below but get stuck and on the sequel
> statement. Any help would greatly appreciate.
> Also there is a business rule describe below.
>
>
> DROP TABLE #Temp
> go
> Create table #Temp
> (
> Lead_History_id INT NULL,
> Header_id INT NULL,
> Prod_Type Char(1) NULL,
> Dis_Id_After INT NULL,
> His_Change_Dt DATETIME NULL
> )
> GO
> INSERT #Temp VALUES (8243234, 1034399, 'A', 1, '2006-06-12
> 13:39:24:077')
> INSERT #Temp VALUES (8243235, 1034399, 'A', 106, '2006-06-12
> 13:39:50:042')
> INSERT #Temp VALUES (8243236, 1034399, 'A', 1, '2006-06-12
> 13:41:21:624')
> INSERT #Temp VALUES (8243240, 1034400, 'A', 1, '2006-06-12 14:22:36:022')
> INSERT #Temp VALUES (8243350, 1034562, 'A', 1, '2006-06-12
> 13:36:30:042')
> INSERT #Temp VALUES (8243351, 1034562, 'A', 107, '2006-06-12
> 14:37:25:461')
> INSERT #Temp VALUES (8243352, 1034562, 'A', 108, '2006-06-12
> 14:40:14:425')
> INSERT #Temp VALUES (8243353, 1034562, 'A', 1, '2006-06-12
> 14:42:19:571')
> INSERT #Temp VALUES (8243355, 1135414, 'A', 1, '2006-06-12
> 15:22:22:571')
> INSERT #Temp VALUES (8243356, 1135414, 'A', 110, '2006-06-12
> 15:25:33:426')
> go
>
> select *
> from #temp
> go
>
> -- Businss rules: Only return (row) the latest Lead_history_id or
> His_Change_Dt with Dis_id_after = 1.
> If you look at header_id = 1135414. Its has 1 and 110
> since the last row has Dis_id_after = 110
> then do not return these.
>
> --note:Lead_History_id is identity column.
>
> --Result want:
>
> Lead_History_id Header_id Prod_Type Dis_Id_After His_Change_Dt
> --------------- ----------- --------- ------------ -----------------------
> 8243236 1034399 A 1 2006-06-12 13:41:21.623
> 8243240 1034400 A 1 2006-06-12 14:22:36.023
> 8243353 1034562 A 1 2006-06-12 14:42:19.570
>
>
> --Testing and is not working yet...
>
>
> SELECT t1.*
> FROM #temp AS t1
> WHERE t1.Lead_History_id = (SELECT MAX(Lead_History_id)
> FROM #temp AS t2
> WHERE t1.Lead_History_id =
> t2.Lead_History_id
> AND t1.header_id = t2.Header_id
> AND t2.Dis_id_After = 1);
> go

"oj" wrote:

> this should do:
>
> select *
> from #Temp t1
> where Lead_History_id=(select top 1 Lead_History_id
> from #Temp t2
> where t2.Header_id=t1.Header_id
> order by His_Change_Dt desc)
> and t1.Dis_Id_After=1
>
> --
> -oj
>
>
>
> "Lam Nguyen" <LamNguyen@discussions.microsoft.com> wrote in message
> news:EAF9F716-540F-4682-B3A3-DC19E633B4AE@microsoft.com...
> > Hi all,
> >
> > I need to get the desire results below but get stuck and on the sequel
> > statement. Any help would greatly appreciate.
> > Also there is a business rule describe below.
> >
> >
> > DROP TABLE #Temp
> > go
> > Create table #Temp
> > (
> > Lead_History_id INT NULL,
> > Header_id INT NULL,
> > Prod_Type Char(1) NULL,
> > Dis_Id_After INT NULL,
> > His_Change_Dt DATETIME NULL
> > )
> > GO
> > INSERT #Temp VALUES (8243234, 1034399, 'A', 1, '2006-06-12
> > 13:39:24:077')
> > INSERT #Temp VALUES (8243235, 1034399, 'A', 106, '2006-06-12
> > 13:39:50:042')
> > INSERT #Temp VALUES (8243236, 1034399, 'A', 1, '2006-06-12
> > 13:41:21:624')
> > INSERT #Temp VALUES (8243240, 1034400, 'A', 1, '2006-06-12 14:22:36:022')
> > INSERT #Temp VALUES (8243350, 1034562, 'A', 1, '2006-06-12
> > 13:36:30:042')
> > INSERT #Temp VALUES (8243351, 1034562, 'A', 107, '2006-06-12
> > 14:37:25:461')
> > INSERT #Temp VALUES (8243352, 1034562, 'A', 108, '2006-06-12
> > 14:40:14:425')
> > INSERT #Temp VALUES (8243353, 1034562, 'A', 1, '2006-06-12
> > 14:42:19:571')
> > INSERT #Temp VALUES (8243355, 1135414, 'A', 1, '2006-06-12
> > 15:22:22:571')
> > INSERT #Temp VALUES (8243356, 1135414, 'A', 110, '2006-06-12
> > 15:25:33:426')
> > go
> >
> > select *
> > from #temp
> > go
> >
> > -- Businss rules: Only return (row) the latest Lead_history_id or
> > His_Change_Dt with Dis_id_after = 1.
> > If you look at header_id = 1135414. Its has 1 and 110
> > since the last row has Dis_id_after = 110
> > then do not return these.
> >
> > --note:Lead_History_id is identity column.
> >
> > --Result want:
> >
> > Lead_History_id Header_id Prod_Type Dis_Id_After His_Change_Dt
> > --------------- ----------- --------- ------------ -----------------------
> > 8243236 1034399 A 1 2006-06-12 13:41:21.623
> > 8243240 1034400 A 1 2006-06-12 14:22:36.023
> > 8243353 1034562 A 1 2006-06-12 14:42:19.570
> >
> >
> > --Testing and is not working yet...
> >
> >
> > SELECT t1.*
> > FROM #temp AS t1
> > WHERE t1.Lead_History_id = (SELECT MAX(Lead_History_id)
> > FROM #temp AS t2
> > WHERE t1.Lead_History_id =
> > t2.Lead_History_id
> > AND t1.header_id = t2.Header_id
> > AND t2.Dis_id_After = 1);
> > go
>
>