Solved

Day Calendar C#

Posted on 2014-10-05
6
244 Views
Last Modified: 2014-10-06
I have been working on this sample project for a week now but I am not having luck. I am trying to create a day calendar in c#.

Here is my table layout
ID    StartTime            EndTime            Description           Subject
           08:15                   09:30                                               Meeting
           09:30                   10:30                                              Workout
          12:00                    13:00                                                Lunch
          12:00                    13:00                                                Meeting with John
          13:15                    14:30                                               Meeting Offsite

My other table is a Time table that lists times of the day in hour, minute, and second.

The SQL Statement below lists the appointment in gridview. However duplicate times show up when there is an duplicate appointment. Second. How can I show times without being specific. Like in my SQL Statement below, I list the times every 15 minutes. The appointment does not show on the gridview if it is in anything but 15 minute intervals.

Also when I have the appointments listed I would like to highlight the cells. The code below only highlight the first set of times and nothing else.

SELECT MHour, MMinutes, ApptSub FROM (SELECT row_number() over (partition by MMinutes order by MMinutes, ApptTimes) as SeqNum, MHour, MMinutes, ApptTimes, CASE WHEN ApptSub IS NULL THEN '1' ELSE ApptSub END AS ApptSub FROM (SELECT TimeTable.MHour, TimeTable.MMinutes, CASE WHEN Appt.ApptStart IS NULL THEN 'N' ELSE Appt.ApptStart END AS ApptStart, CASE WHEN Appt_1.ApptEndDisplay IS NULL THEN 'N' ELSE Appt_1.ApptEndDisplay END AS ApptEnd, Appt.ApptSubject, Appt_1.ApptSubject AS ApptSubject2 FROM (SELECT DISTINCT MilitaryHour + ':00' AS MHour, MilitaryHour + ':' + Minute AS MMinutes FROM  dim_Time WHERE (Minute IN ('00', '15', '30', '45'))) AS TimeTable LEFT OUTER JOIN Appt AS Appt_1 ON TimeTable.MMinutes = Appt_1.ApptEndDisplay LEFT OUTER JOIN Appt ON TimeTable.MMinutes = Appt.ApptStart) T1 Cross Apply (VALUES (ApptStart, ApptSubject),(ApptEnd, ApptSubject2))c (ApptTimes, ApptSub)) T2 WHERE SEQNUM = '1' OR ApptTimes != 'N' ORDER BY MHour, MMinutes, ApptTimes ASC, SeqNum DESC

Open in new window

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            for (int i = 0; i < 1; i++)
            {
                bool begin = false;
                foreach (GridViewRow row in GridView1.Rows)
                {
                    if (row.Cells[i + 2].Text != "1")
                    {
                        row.Cells[i + 2].BackColor = System.Drawing.Color.Blue;
                        if (!begin) begin = true;
                        else
                            break;
                    }
                    if (begin) row.Cells[i + 2].BackColor = System.Drawing.Color.Blue;
                }
            }
        }

Open in new window

0
Comment
Question by:RecipeDan
  • 3
  • 3
6 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40362851
>>"duplicate times show up when there is an duplicate appointment. "
what else would you expect SQL to do for this?

In my calendar I can have more then one appointment at some points in a day, they might overlap slightly (in which case I might leave a meeting early to catch the next, or join the next meeting late) - so I would not be happy with the result if you assumed there is only going to be a single appointment at any point in time.

You can eliminate repetition in the resultset - but you have to compromise. e.g. some data gets provided as comma separated strings.

>>"The appointment does not show on the gridview if it is in anything but 15 minute intervals."
use "time ranges"; not exact matches

Instead of
FROM (
            SELECT DISTINCT
                  MilitaryHour + ':00'        AS MHour
                , MilitaryHour + ':' + Minute AS MMinutes
            FROM dim_Time
            WHERE (Minute IN ('00', '15', '30', '45'))
      ) AS TimeTable
      LEFT OUTER JOIN Appt AS Appt_1
                  ON TimeTable.MMinutes = Appt_1.ApptEndDisplay
      LEFT OUTER JOIN Appt
                  ON TimeTable.MMinutes = Appt.ApptStart

Open in new window


try something like this (which will certainly need testing)
FROM (
            SELECT DISTINCT
                  MilitaryHour + ':00'               AS MHour
                , MilitaryHour + ':' + Minute        AS trstart
                , MilitaryHour + ':' +
                ( CASE WHEN Minute = '00' then '14'
                       WHEN Minute = '15' then '29'
                       WHEN Minute = '30' then '44'
                       WHEN Minute = '45' then '59'
                  END )                              AS trend
            FROM dim_Time
            WHERE (Minute IN ('00', '15', '30', '45'))
      ) AS TimeTable
      LEFT OUTER JOIN Appt
                  ON Appt.ApptStart between TimeTable.trstart and TimeTable.trend
                  OR Appt.ApptEndDisplay between TimeTable.trstart and TimeTable.trend
                  OR TimeTable.trend between Appt.ApptStart and Appt.ApptEndDisplay

Open in new window

0
 
LVL 1

Author Comment

by:RecipeDan
ID: 40362916
Thank you for your reply to my question and your response.

For duplicate times, I didn't think anything could be done. I don't like the way Outlook handles duplicate appointments; having them side by side. From my experience, people miss the second appointment because they don't read the whole line. I was thinking highlighting duplicate appointment times "red" this way it alerts the individual there are two appointments at the same time.  

I am not familiar with the "data gets provided as comma separated strings" that you spoke about? Can you show me an example.?

Here is my updated code with your suggestion, from what I can tell it works. When the appointment times get highlighted can  the subject only show on the start time?
SELECT MHour, ApptTimes, ApptSubject
FROM
(
SELECT row_number() over (partition by trtimes order by trtimes, ApptTimes) as SeqNum, MHour, trtimes, ApptTimes, ApptSubject
FROM
(
   SELECT MHour, trstart, trend, ApptStart, ApptEnd, ApptSubject
   FROM
   (
            SELECT DISTINCT
                  MilitaryHour + ':00'               AS MHour
                , MilitaryHour + ':' + Minute        AS trstart
                , MilitaryHour + ':' +
                ( CASE WHEN Minute = '00' then '14'
                       WHEN Minute = '15' then '29'
                       WHEN Minute = '30' then '44'
                       WHEN Minute = '45' then '59'
                  END )                              AS trend
            FROM dim_Time
            WHERE (Minute IN ('00', '15', '30', '45'))
      ) AS TimeTable
      LEFT OUTER JOIN Appt
                  ON Appt.ApptStart between TimeTable.trstart and TimeTable.trend
                  OR Appt.ApptEnd between TimeTable.trstart and TimeTable.trend
                  OR TimeTable.trend between Appt.ApptStart and Appt.ApptEnd
)
T1
Cross Apply 
(
VALUES
(trstart, ApptStart),
(trend, ApptEnd)
)
c (trtimes, ApptTimes)
)
T2
WHERE SEQNUM ='1'
ORDER BY MHour, ApptTimes

Open in new window


Here is my data from the above code. As you can see there are two appointments from 9:00-12:00.
MHour	ApptTimes	ApptSubject
00:00	NULL	NULL
00:00	NULL	NULL
00:00	NULL	NULL
00:00	NULL	NULL
00:00	NULL	NULL
00:00	NULL	NULL
00:00	NULL	NULL
00:00	NULL	NULL
01:00	NULL	NULL
01:00	NULL	NULL
01:00	NULL	NULL
01:00	NULL	NULL
01:00	NULL	NULL
01:00	NULL	NULL
01:00	NULL	NULL
01:00	NULL	NULL
02:00	NULL	NULL
02:00	NULL	NULL
02:00	NULL	NULL
02:00	NULL	NULL
02:00	NULL	NULL
02:00	NULL	NULL
02:00	NULL	NULL
02:00	NULL	NULL
03:00	NULL	NULL
03:00	NULL	NULL
03:00	NULL	NULL
03:00	NULL	NULL
03:00	NULL	NULL
03:00	NULL	NULL
03:00	NULL	NULL
03:00	NULL	NULL
04:00	NULL	NULL
04:00	NULL	NULL
04:00	NULL	NULL
04:00	NULL	NULL
04:00	NULL	NULL
04:00	NULL	NULL
04:00	NULL	NULL
04:00	NULL	NULL
05:00	NULL	NULL
05:00	NULL	NULL
05:00	NULL	NULL
05:00	NULL	NULL
05:00	NULL	NULL
05:00	NULL	NULL
05:00	NULL	NULL
05:00	NULL	NULL
06:00	NULL	NULL
06:00	NULL	NULL
06:00	NULL	NULL
06:00	NULL	NULL
06:00	NULL	NULL
06:00	NULL	NULL
06:00	NULL	NULL
06:00	NULL	NULL
07:00	NULL	NULL
07:00	NULL	NULL
07:00	NULL	NULL
07:00	NULL	NULL
07:00	NULL	NULL
07:00	NULL	NULL
07:00	NULL	NULL
07:00	NULL	NULL
08:00	NULL	NULL
08:00	NULL	NULL
08:00	NULL	NULL
08:00	NULL	NULL
08:00	NULL	NULL
08:00	NULL	NULL
08:00	NULL	NULL
08:00	NULL	NULL
09:00	09:00	Dan's Birthday
09:00	09:00	Dan's Birthday
09:00	09:00	Dan's Birthday
09:00	09:00	Dan's Birthday
09:00	12:00	Evaluatin
09:00	12:00	Evaluatin
09:00	12:00	Evaluatin
09:00	12:00	Evaluatin
10:00	09:00	Dan's Birthday
10:00	09:00	Dan's Birthday
10:00	09:00	Dan's Birthday
10:00	09:00	Dan's Birthday
10:00	12:00	Evaluatin
10:00	12:00	Evaluatin
10:00	12:00	Evaluatin
10:00	12:00	Evaluatin
11:00	09:00	Dan's Birthday
11:00	09:00	Dan's Birthday
11:00	09:00	Dan's Birthday
11:00	09:00	Dan's Birthday
11:00	12:00	Evaluatin
11:00	12:00	Evaluatin
11:00	12:00	Evaluatin
11:00	12:00	Evaluatin
12:00	09:00	Dan's Birthday
12:00	12:00	Dan's Birthday
12:00	12:00	Lunch Date
12:00	12:00	Lunch Date
12:00	12:00	Lunch Date
12:00	13:00	Lunch Date
12:00	13:00	Lunch Date
12:00	13:00	Lunch Date
13:00	NULL	NULL
13:00	NULL	NULL
13:00	NULL	NULL
13:00	NULL	NULL
13:00	NULL	NULL
13:00	NULL	NULL
13:00	12:00	Lunch Date
13:00	13:00	Lunch Date
14:00	14:00	Meeting
14:00	14:00	Meeting
14:00	14:00	Meeting
14:00	14:00	Meeting
14:00	15:00	Meeting
14:00	15:00	Meeting
14:00	15:00	Meeting
14:00	15:00	Meeting
15:00	14:00	Meeting
15:00	15:00	Meeting
15:00	15:00	Lou's Meeting
15:00	15:00	Lou's Meeting
15:00	15:00	Lou's Meeting
15:00	16:30	Lou's Meeting
15:00	16:30	Lou's Meeting
15:00	16:30	Lou's Meeting
16:00	NULL	NULL
16:00	NULL	NULL
16:00	15:00	Lou's Meeting
16:00	15:00	Lou's Meeting
16:00	15:00	Lou's Meeting
16:00	16:30	Lou's Meeting
16:00	16:30	Lou's Meeting
16:00	16:30	Lou's Meeting
17:00	NULL	NULL
17:00	NULL	NULL
17:00	NULL	NULL
17:00	NULL	NULL
17:00	NULL	NULL
17:00	NULL	NULL
17:00	NULL	NULL
17:00	NULL	NULL
18:00	NULL	NULL
18:00	NULL	NULL
18:00	NULL	NULL
18:00	NULL	NULL
18:00	NULL	NULL
18:00	NULL	NULL
18:00	NULL	NULL
18:00	NULL	NULL
19:00	NULL	NULL
19:00	NULL	NULL
19:00	NULL	NULL
19:00	NULL	NULL
19:00	NULL	NULL
19:00	NULL	NULL
19:00	NULL	NULL
19:00	NULL	NULL
20:00	NULL	NULL
20:00	NULL	NULL
20:00	NULL	NULL
20:00	NULL	NULL
20:00	NULL	NULL
20:00	NULL	NULL
20:00	NULL	NULL
20:00	NULL	NULL
21:00	NULL	NULL
21:00	NULL	NULL
21:00	NULL	NULL
21:00	NULL	NULL
21:00	NULL	NULL
21:00	NULL	NULL
21:00	NULL	NULL
21:00	NULL	NULL
22:00	NULL	NULL
22:00	NULL	NULL
22:00	NULL	NULL
22:00	NULL	NULL
22:00	NULL	NULL
22:00	NULL	NULL
22:00	NULL	NULL
22:00	NULL	NULL
23:00	NULL	NULL
23:00	NULL	NULL
23:00	NULL	NULL
23:00	NULL	NULL
23:00	NULL	NULL
23:00	NULL	NULL
23:00	NULL	NULL
23:00	NULL	NULL

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40362970
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:RecipeDan
ID: 40363522
Thank you for the link. I was also testing comma separated string on SQL Fiddle. I don't think need to use them for this project but its good to keep in mind.

Can you assist me with the onrowdatabound issue? The code below only highlights the first set data and stops. When I take the break out it highlights the whole column

  protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            for (int i = 0; i < 1; i++)
            {
                bool begin = false;
                foreach (GridViewRow row in GridView1.Rows)
                {
                    if (row.Cells[i + 2].Text != "1")
                    {
                        row.Cells[i + 2].BackColor = System.Drawing.Color.Blue;
                        if (!begin) begin = true;
                        else
                            break;
                    }
                    if (begin) row.Cells[i + 2].BackColor = System.Drawing.Color.Blue;
                }
            }
        }

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40363576
>>"Can you assist me with the onrowdatabound issue? "

regretfully I can't, is it Dutch or German?
0
 
LVL 1

Author Comment

by:RecipeDan
ID: 40363745
It is part of asp.net gridview. What I was trying to do is highlight between the start and end times of the appointment. That is ok though. You helped me a lot with the SQL Statement.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now