Update Hourly Data count via Database Records

Question

I want to retrieve the records from the table in database, and from the TIMESTAMP, to put the counts in their appropriate hourly intervals. It works when say the first occurs AFTER 0730 hr and continues throughout the 24-hour. However, say when the first entry occurs in the midnight at 0430 hr, and continues through 0730 hr, then the counts gets wrong. how should I modify the attached code to cater for different cases?

thanks.

TAN WhoAMI · Answer

yes the query in linqpad and the application are correct except that earliest entry of the record needs to be retrieved and count starts from there. What I actually want to achieve is this: 1) Get the earliest time count. 2) Go the the next earliest time count and increment the count.. 3) And the loop continues within the 24-hour timeframe.

Iftikar Hussain · Answer

not sure check your query directly in linqpad and compare the result

TAN WhoAMI · Answer

but adding the date comparison does not help in anyway.. I just need to get the minimum time as the first entry and continue from there....

Iftikar Hussain · Answer

I am just giving you the example you need pass the current date or desired date before creating the time list

TAN WhoAMI · Answer

I could not input in the date in the StartTime = DateTime.Parse(8/13/2013 07:30) because every other set of data has different dates. I think the issue is to get the earliest time and use that as the first input... would you able to join.me?

Iftikar Hussain · Answer

in the time object you need to include List timeCals=new List (); TimeCal timeCal_0=new TimeCal() { StartTime = DateTime.Parse(8/13/2013 07:30) EndTime = DateTime.Parse( 8/13/2013 08:30)}; timeCals.Add(timeCal_0); TimeCal timeCal_1 = new TimeCal() { StartTime = DateTime.Parse( 8/13/2013 08:30) EndTime = DateTime.Parse( 8/13/2013 09:30)}; timeCals.Add(timeCal_1); so on I believe your database column TIMESTAMP has got date also Regards Iftikar

TAN WhoAMI · Answer

I only need to put them into its appropriate hourly slot should I include the date comparison? take note the first entry can be anytime.. I attached the sample data here...

Iftikar Hussain · Answer

yes no need to display first 7:30 hour in your output but you count will be considered from 7:30 to 8:30 and result will be shown against 8:30 hour and so on. I am not sure why the count is coming wrong. Is it possible to compare date also. So that from DB it will check data on that particular date and time. For eg if you want to check for today then your compare date time will be start like this 08/13/2013 07:30 08/13/2013 08:30 08/13/2013 09:30 08/13/2013 10:30 08/13/2013 11:30 and so on This need to be considered only for getting the count  while displaying you can display without date only Regards Iftikar

TAN WhoAMI · Answer

I am not sure whether I would be able to describe well the problems to solve. Basically the first entry of the count can start anytime within the 24-hour interval . (that 0430 hr is just an example). when it first starts AFTER 0730 (goes into 0830 slot hour 1) and continues throughout the count is correct. but when it starts BEFORE 0730 (goes into 0730 slot hour 24) and continue the count is wrong... I think I need to get the first entry correct that is why I have this: var MO_Date = (from p in context.PPL_PRODUCT_TESTID2 where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE ==0036 && p.TESTFLAG ==1 select p.TIMESTAMP). Min() ; see attached zip file for the correct and wrong data. It first starts from 0730 (hour 24) and continues up to 0830 (hour 1)and the count is wrong. maybe I should only have one 0730 (first hour) to avoid confusion what do you think? My main purpose here is to retrieve those records from the table in the database and put them accordingly to its correct time slot and get its count. I hope I am clear.

Iftikar Hussain · Answer

Hi You want to get count between 4:30 to 7:30? Regards Iftikar