C# SLQ
hello everybody
i have issue...
i have table in form:
event id | event category | event start | event end
_________________________________________
1 | 1 | 2012-12-16 02:02:55 | 2012-12-16 02:05:55
2 | 2 | 2012-12-16 02:05:56 | 2012-12-16 02:55:25
3 | 1 | 2012-12-16 02:55:26 | 2012-12-16 03:02:55
4 | 2 | 2012-12-16 03:02:56 | 2012-12-16 03:07:26
5 | 1 | 2012-12-16 03:07:27 | 2012-12-16 04:02:55
what need archive following:
i need somehow reult saying:
time interval | category | duration
_________________________________________________________________________
2012-12-16 02:00:00 -03:00:00 | 1 |(180+34+240) 454s
2012-12-16 02:00:00 -03:00:00 | 2 |2969s
..........
please, give me hint how make this...thanks lot upfront....
another twist on problems i've done lots of times before. comparing 2 time spans "start , end time" 1 hour range "start range end range". can have 1 6 different combinations must consider.
1) start time , end times less start range. there no hours overlap results zero.
2) start time , end times greater end range. again there no hours overlap results zero.
3) start time greater start range , end time less end range. duration end time minus start time
4) start time greater start range , end time is greater end range. duration end range minus start time
5) the start time is less start range , end time less end range. duration end time minus start range.
6) the start time is less start range , end time is greater end range. duration end range minus start range.
using system; using system.collections.generic; using system.componentmodel; using system.data; using system.drawing; using system.linq; using system.text; using system.windows.forms; namespace windowsformsapplication1 { public partial class form1 : form { list<list<object>> inputs = new list<list<object>>() { new list<object> {1,1, datetime.parse("2012-12-16 02:02:55"), datetime.parse("2012-12-16 02:05:55")}, new list<object> {2,2, datetime.parse("2012-12-16 02:05:56"), datetime.parse("2012-12-16 02:55:25")}, new list<object> {3,1, datetime.parse("2012-12-16 02:55:26"), datetime.parse("2012-12-16 03:02:55")}, new list<object> {4,2, datetime.parse("2012-12-16 03:02:56"), datetime.parse("2012-12-16 03:07:26")}, new list<object> {5,1, datetime.parse("2012-12-16 03:07:27"), datetime.parse("2012-12-16 04:02:55")}, }; public form1() { initializecomponent(); datatable table1 = new datatable(); table1.columns.add("event id", typeof(int)); table1.columns.add("event category", typeof(int)); table1.columns.add("event start", typeof(datetime)); table1.columns.add("event end", typeof(datetime)); foreach (list<object> input in inputs) { datarow newrow = table1.rows.add(); newrow.itemarray = input.toarray(); } datagridview1.datasource = table1; dictionary<int, list<datarow>> dict = table1.asenumerable() .groupby(x => x.field<int>("event category"), y => y) .todictionary(x => x.key, y => y.tolist()); datatable table2 = new datatable(); table2.columns.add("time interval ", typeof(string)); table2.columns.add("category", typeof(string)); table2.columns.add("duration seconds", typeof(int)); foreach (int category in dict.keys) { list<datarow> rows = dict[category]; datetime starttime = rows.select(x => x.field<datetime>("event start")).tolist().min(); datetime endtime = rows.select(x => x.field<datetime>("event end")).tolist().max(); int numberofhours = (int)(endtime - starttime).totalhours; datetime starthour = new datetime(starttime.year,starttime.month,starttime.day, starttime.hour,0,0); (int hourcount = 0; hourcount <= numberofhours; hourcount++) { datetime hour = starthour.addhours(hourcount); int duration = countsecondswithin(hour, hour.addhours(1), starttime, endtime); if (duration > 0) { datarow newrow = table2.rows.add(); newrow.itemarray = new object[] { string.format("{0} - {1}", hour.tostring("yyyy-mm-dd hh:mm:ss"), hour.addhours(1).tostring("hh:mm:ss")), category, duration }; } } } datagridview2.datasource = table2; } int countsecondswithin(datetime startrange, datetime endrange, datetime starttime, datetime endtime) { int results = 0; if ((starttime >= startrange) && (starttime < endrange)) { if (endtime <= endrange) { results = (int)(endtime - starttime).totalseconds; } else { results = (int)(endrange - starttime).totalseconds; } return results; } if ((endtime < endrange) && (endtime > startrange)) { if (starttime >= startrange) { results = (int)(endtime - startrange).totalseconds; } else { results = (int)(endrange - startrange).totalseconds; } return results; } return results; } } }
Visual Studio Languages , .NET Framework > Visual C#
Comments
Post a Comment