Update Hourly Data count via Database Records

TAN WhoAMI

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.

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

TAN WhoAMI13y ago
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 Hussain13y ago
not sure, check your query directly in linqpad and compare the result
TAN WhoAMI13y ago
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 Hussain13y ago
I am just giving you the example, you need pass the current date or desired date before creating the time list
TAN WhoAMI13y ago
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 Hussain13y ago
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 WhoAMI13y ago
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 Hussain13y ago
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 WhoAMI13y ago
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 Hussain13y ago
Hi,
You want to get count between 4:30 to 7:30?

Regards,
Iftikar