How to replace zero if data group by hour is null ?

Question

0.00/5 (No votes)

See more:

I am grouping my data into hours,

C#

public List<Emails> valuefunc()
   {
       List<Emails> o = new List<Emails>();

       var res = from p in email
                 where p.CurrentStatus=="Completed"
                 group p by new { weekday = p.SubmitDate.Value.DayOfWeek,Hour=p.SubmitDate.Value.Hour } into GRP
                 orderby  GRP.Key.weekday
                 select new  Emails
                 {
                   label= GRP.Key.weekday.ToString() ,
                   label2 = " H " +GRP.Key.Hour ,
                   Values=GRP.Count()
                 };


       o.AddRange(res);
       return o;

   }

output:

C#

label   Hour    Values 
Sunday   H 0       2 
Sunday  H 1       4 
Sunday  H 3       1 
Sunday  H 6       4 
Sunday  H 7      16 
Sunday  H 9      25 
Sunday  H 20     2 
Sunday  H 22     5 
Monday  H 1      7 
Monday  H 2      3 
Monday  H 6      2

but my question is if specific hour the value is null how can we get that hour with zero count. like here H2 is null so it does not display how can i get H2 with zero count if null.

Posted 19-Nov-14 1:06am

shaprpuff

Add a Solution

Comments

Tomas Takac 19-Nov-14 7:27am

I think there's no magic trick to do this. The data is simply not there. You have to go through the list and fill in the gaps.

shaprpuff 23-Nov-14 2:16am

You are right actually for those hours data does not exists and by your suggestion i create a function to fill the gaps after getting the list.

public List<emails> FillingMissingValues(List<emails> list)
{

if (list.Count>0)
{
int[] arr = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };

for (int i = 0; i < arr.Length; i++)
{
int s = arr[i];
int g = list.Where(x => x.ID == s).Count();
string title = list[0].label2;

if (g == 0)
{

Emails missingItem = new Emails{ ID = s, label = title, label2 = "H" + s, Values = 0 };
list.Insert(s, missingItem);
}
}

}

return list;
}

Thanks alot.......

Nathan Minier 19-Nov-14 7:30am

This should work:
label2 = " H " + (GRP.Key.Hour ?? "0") ,

Tomas Takac 19-Nov-14 8:05am

How can SubmitDate.Value.Hour be ever null? Assuming SubmitDate is Nullable<datetime> of course.

Nathan Minier 19-Nov-14 8:15am

I have no idea what is backing the p.SubmitDate field. It could be a DateTime object or a DateTime? object. It could be a custom struct or class. That's a bit up to the person asking the question, and reading the question it seems clear that SubmitDate.Value.Hour is nullable, and that code will work in that scenario.

And if it's not, then the entire premise of the question is flawed.

Tomas Takac 19-Nov-14 8:31am

I meant to write Nullable<DateTime> ... but I agree with the last sentence.

Hasham Ahmad 19-Nov-14 14:06pm

you can make another object with hour values in it and then query it with the left join

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-12-14T10:59:00

Several times i've read your question...
Finally, i think i got what you're trying to achieve:

C#

//created in LinqPad 4.51.03(AnyCPU)
void Main()
{
	List<email> emails = new List<email>{
		new email(new DateTime(2014,12,14,7,0,0), "Completed"),
		new email(new DateTime(2014,12,14,7,15,0), "Completed"),
		new email(new DateTime(2014,12,14,7,30,0), "Completed"),
		new email(new DateTime(2014,12,14,8,5,0), "Incomplete"),
		new email(new DateTime(2014,12,14,9,12,0), "Completed"),
		new email(new DateTime(2014,12,14,9,52,0), "Completed"),
		new email(new DateTime(2014,12,14,10,20,0), "Incomplete"),
		new email(new DateTime(2014,12,14,11,28,0), "Completed"),
		new email(new DateTime(2014,12,14,12,36,0), "Incomplete"),
		new email(new DateTime(2014,12,14,13,44,0), "Completed"),
		new email(new DateTime(2014,12,14,13,49,0), "Completed"),
		new email(new DateTime(2014,12,14,13,54,0), "Completed"),
		new email(new DateTime(2014,12,14,14,52,0), "Incomplete"),
		new email(new DateTime(2014,12,14,15,8,0), "Completed"),
		new email(new DateTime(2014,12,14,15,15,0), "Incomplete")
		};
		
	//start date
	DateTime startDate = new DateTime(2014,12,14,6,0,0);
	//end date
	DateTime endDate = new DateTime(2014,12,14,16,0,0);
	int hdiff = ((TimeSpan) (endDate-startDate)).Hours; 
	hdiff++;
	//define Hours; fill
	List<datetime> hours = new List<datetime>();
	for(int i=0; i<hdiff;>	{
		hours.Add(startDate.AddHours(i));
	}
		
	var qry = from hr in hours
		select new{
				Date = hr.ToString("yyyy-MM-dd HH"),
				Count = (emails.Count(e=>e.SubmitDate.ToString("yyyy-MM-dd HH")==hr.ToString("yyyy-MM-dd HH") && e.CurrentStatus=="Completed"))
			};
	
	qry.Dump();
}

// Define other methods and classes here
class email
{
	private DateTime aSubmitDate;
	private string aStatus = "Incomplete";

	public email(DateTime _SubmitDate, string _Status)
	{
		aSubmitDate = _SubmitDate;
		aStatus = _Status;
	}
	
	public DateTime SubmitDate
	{
		get{return aSubmitDate;}
		set{aSubmitDate = value;}
	}
	
	public string CurrentStatus
	{
		get{return aStatus;}
		set{aStatus = value;}
	}
}</datetime></datetime></email></email>

Result:

Date             Count
2014-12-14 06    0 
2014-12-14 07    3 
2014-12-14 08    0 
2014-12-14 09    2 
2014-12-14 10    0 
2014-12-14 11    1 
2014-12-14 12    0 
2014-12-14 13    3 
2014-12-14 14    0 
2014-12-14 15    1 
2014-12-14 16    0