我的查询的语法是好的，但不是输出，这是很奇怪的。

The syntax of my query is good but not the output and it's really strange.

我有以下表格：

| AppointID | UserID | AppointSet | AppointResolved |  AppointStatus | AppointmentDatetime
|     1     |   1    |  3/1/2011  |   3/1/2011      |      1         |     3/15/2011
|     2     |   1    |  3/2/2011  |   3/5/2011      |      4         |     3/16/2011
|     3     |   1    |  3/2/2011  |   3/11/2011     |      2         |     3/11/2011
|     4     |   1    |  3/3/2011  |   3/7/2011      |      3         |     3/25/2011

ApponintStatus是一个字节，其中1是套，2是出席3是经重组和4已取消。 AppointDatetime为它是约会设定日期

ApponintStatus is a byte where 1 is for set, 2 is for attended, 3 is for rescheduled and 4 is for cancelled. AppointDatetime is the date for which is appointment is set.

我想要做的是创建下面的输出，通过一天都很重要活动。

What I'm trying to do is create the following output that counts the activity by day.

|    Date     |   Set   |   Attended   |   Rescheduled   |   Cancelled   |
|  3/1/2011   |    1    |              |                 |               |
|  3/2/2011   |    2    |              |                 |               |
|  3/3/2011   |    1    |              |                 |               |
|  3/5/2011   |         |              |                 |      1        |
|  3/7/2011   |         |      1       |                 |               |
|  3/11/2011  |         |              |        1        |               |

这是我到目前为止所。 TheDate是一个月，我在查询中的一个日期（即通过3月4日，应退还三月表）

This is what I have so far. TheDate is a date within the month I'm querying for (ie, pass March 4th and should return the March table)

var r = from appnt in MyDC.LeadsAppointments
        where appnt.UserID == TheUserID
        where (appnt.AppointResolved.Year == TheDate.Year && appnt.AppointResolved.Month == TheDate.Month) ||
        (appnt.AppointSet.Year == TheDate.Year && appnt.AppointSet.Month == TheDate.Month)
        group appnt by appnt.AppointResolved.Date into daygroups
        select new ViewMonthlyActivityModel()
        {
            ViewDate = (from d in daygroups
                        select daygroups.Key.Date).First(), // Problem here: need to get dates for instances where an appointment is set but none are resolved

            CountTotalSetOnDay = (from c in daygroups
                                  where c.AppointSet.Date == daygroups.Key // Problem here
                                  select c.AppointID).Count(),

            CountAttendedOnDay = (from c in daygroups
                                  where c.AppointResolved.Date == daygroups.Key.Date
                                  where c.AppointStatus == 2
                                  select c.AppointID).Count(),

的一个问题是，CountTotalSetOnDay正在恢复那些被设置并解决在同一天的仅计数;另一个问题是，ViewDate需要返回的所有日期：有当没有预约设定日期，但约会出席，改期或取消，反之亦然，有日期，当约会设置，但没有得到解决。

One of the problem is that CountTotalSetOnDay is returning only the count of those that are set and resolved the same day; the other problem is that ViewDate needs to return all the dates: there are dates when there are no appointments set but appointments attended, rescheduled or cancelled, and vice versa, there are dates when appointments are set but none are resolved.

现在我有2个查询运行这一点，我加入了结果：一个查询返回设置任命和任命解决了其他的回报。不过，我仍然停留在一个查询的解决方案，一个读。

For now I'm running this with 2 queries and I join the results: one query returns the appointments set and the other returns the appointments resolved. However, I'm still stuck on the solution with one query in one read.

有什么建议？

感谢。

推荐答案

我想用2个读查询的解决方案工作得很好，并代表现在。

I guess the solution with 2 read queries works just fine and will stand for now.