logo
Tags down

shadow

tsql display value on first row of each group


By : Huan
Date : October 14 2020, 02:24 PM
I hope this helps you . Assuming I got two tables, one is trip and another one is amount table. , use the logic below
code :
create table #temp
(TripStartDateTime datetime,TripEndDateTime datetime
,DriverId int,Amount numeric(8,2))

insert into #temp values('2018-11-12 09:19','2018-11-12 09:44',112,6)
insert into #temp values('2018-11-12 09:57','2018-11-12 10:16',112,6)
insert into #temp values('2018-11-12 08:57','2018-11-12 09:16',112,6)
insert into #temp values('2018-11-13 13:23','2018-11-12 13:43',112,7)
insert into #temp values('2018-11-13 13:52','2018-11-12 13:57',112,7)
insert into #temp values('2018-11-13 12:52','2018-11-12 12:57',112,7)

select 
    a.TripStartDateTime
    ,a.TripEndDateTime
    ,a.DriverId 
    ,case when a.seq>1 then 0 else sum(a.Amount)over(partition by cast(tripstartdatetime as date)) end as Amount
from
(
select 
    ROW_NUMBER()over(partition by amount order by TripStartDateTime ) as seq,* 
from #temp
)a


Share : facebook icon twitter icon

TSQL Group by N Seconds


By : Nikhil Kumar K
Date : March 29 2020, 07:55 AM
wish helps you Try this:
DATEDIFF (ss ,'19700101' ,Time )) will give the amount of seconds since 01/01/1970 (you can choose another date that is less than all your dates). Divide it by ten to have a GROUP BY by each 10 seconds:
code :
SELECT DATEDIFF (ss ,'19700101' ,Time )/10, count(*) as hits
  FROM Log
 GROUP BY DATEDIFF (ss ,'19700101' ,Time )/10
SELECT convert(varchar, min(Time), 108) time,
       DATEDIFF (ss ,'19700101' ,Time )/10 sec_interval,
       count(*) as hits
  FROM Log
 GROUP BY DATEDIFF (ss ,'19700101' ,Time )/10

Group Between Keywords TSQL


By : Elisabeth Engel
Date : March 29 2020, 07:55 AM
seems to work fine Problem , Try this
code :
select
    f3.FruitName, f3.FruitCost, f.FruitName
from
    @Fruit f
        inner join
    (
        SELECT  *,
        (Select max(productid) from @Fruit f2 where FruitCost is null and ProductID<=f.ProductID) as fgroup
        FROM    @Fruit f
    ) f3
        on f.ProductID = f3.fgroup
where f3.FruitCost is not null  

TSQL - If Valid is true for at least 1 user in each group then return that group as Valid?


By : Joe Njuki
Date : March 29 2020, 07:55 AM
I hope this helps . Essentially I want to run a query that returns a list of all the DISTINCT Groups, and if Valid is true for at least 1 user in each group then return that group as true. Otherwise return that group as false. Many thanks , You can using row_number():
code :
select t.*
from (select t.*,
             row_number() over (partition by groupid
                                order by (case when valid = 'True' then 1 else 2 end)
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

TSQL - Any of Group By


By : Zhe Wang
Date : March 29 2020, 07:55 AM
Any of those help Actually, I ended up creating a temporary table, selecting all the ids to it.
Then, upon realizing that duplication was around 20%, I disabled all other indexes on the main table, deleted the duplicated rows and rebuilt the indexes...

TSQL GROUP BY in recursive CTE


By : Audi
Date : March 29 2020, 07:55 AM
I hope this helps . Is there a workaround to use GROUP BY inside a looping CTE or there is a workaround?
shadow
Privacy Policy - Terms - Contact Us © voile276.org