Hi. Hope you are doing good.
I’ve an input data table as follows:
I need to prepare an output data table by grouping release names and also calculate two more columns as follows:
I’m thinking that I should use database select, groupby queries but not sure how to do this. Can someone please help me to create that output data table?
Hi @Surya_Narayana_Korivipadu ,
Tried to create linq but it’s only calculating seconds, needs some modification.
Check once and let me know how exactly you need data in Total Time.
Please mention example.
(From r In dtInput.AsEnumerable
Group r By k = r(“ReleaseName”).tostring Into grp = Group
Let sumstart = grp.Sum(Function (x) x.Field(Of Date)(“StartTime”).TimeOfDay.TotalSeconds)
Let sumend = grp.Sum(Function (x) x.Field(Of Date)(“EndTime”).TimeOfDay.TotalSeconds)
Let subtract = CInt(sumstart) - CInt(sumend)
Let ra = New Object() {grp(0)(“ReleaseName”),subtract,grp.Count}
Select dtOutput.Rows.Add(ra)).CopyToDataTable
test.xaml (7.3 KB)
Thank you for your reply. I’ll try this
I want total time as a timespan HH:mm:ss. That too endtime-starttime should be calculated for every row in the group and then these results should be added.
just replace subtract with TimeSpan.FromSeconds(subtract)
I’ve one more doubt of this substraction. I feel that @ermanoj3101 query adds all start-times in a group and adds all end-times in a group and then calculates difference of these sums
I need to calculate end time-start time for each row in the group and then sum these values.
Please try the below linq
(From row In inputDT
Group row By rn = row("ReleaseName").ToString
Into grp = Group
Let dur = grp.Sum(Function (x) DateTime.Parse(x("EndTime").ToString).TimeOfDay.TotalSeconds -DateTime.Parse(x("StartTime").ToString).TimeOfDay.TotalSeconds)
Let ra = New Object() {grp(0)("ReleaseName"),TimeSpan.FromSeconds(dur),grp.Count}
Select outDT.Rows.Add(ra)).CopyToDataTable
Groupby TimeSpan Count.xaml (7.5 KB)
I thought that’s what you want, anyways you can check @kumar.varun2 query.
Thanks.
Thank you @kumar.varun2 . I’ll try this
Thanks @kumar.varun2 . I used your linq, converted dateTime to ticks instead of seconds and then used Timespan.fromTicks() and it’s working perfectly.
I’m not much aware of Linq. If I want to build such linq expressions, do you know where can I find good tutorials?
LINQ Learning
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.