Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Day Calendar C#

Posted on 2014-10-05
6
249 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 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