none
list all the dates between two date columns in entity RRS feed

  • Question

  • my TABLE1 look like this.

        FromDate              ToDate                  leaveType       empid

    2015-01-03        2015-01-05                    19                  1

    2015-01-03        2015-01-03                    19                  2

    2015-01-03        2015-01-03                    20                  3

    2015-01-04        2015-01-04                    19                  2

    2015-01-08        2015-01-08                    22                  1

    2015-01-05        2015-01-06                    19                  5

    2015-01-03        2015-01-03                    10                  6

    2015-01-08        2015-01-08                    19                  6


    iI need the Result some how like this below table.


    if Table1 FromDate is 2015-01-03 and ToDate is 2015-01-05 for the EmpId 1 means, then the result table should

    have the result like,

             date         leaveType       empid

    2015-01-03        19                    1,2

    2015-01-04        19                    1,2

    2015-01-05        19                    1,5

    2015-01-06        19                     5

    2015-01-08        19                     6

    i have achieved this through sql server query. but i need the same result in  "Linq to Entities."

    Please help me in advance and thanks for the quickest help.

    Wednesday, September 30, 2015 6:13 PM

All replies

  • Hi Alexis,

    What is the date column in result table mean? FromDate or ToDate in origin data source? Please describe more about the this linq issue.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 1, 2015 4:05 AM
    Moderator
  • WITH tmp AS (
        SELECT  [EmployeeID], FromDate AS [Date], [ToDate], LeaveStatusTyp
        FROM    [Leave]
        UNION ALL
        SELECT  tmp.[EmployeeID], DATEADD(DAY,1,tmp.[Date]), tmp.[ToDate], LeaveStatusTyp
        FROM    tmp
        WHERE   tmp.[Date] < tmp.[ToDate]
    )

    SELECT tmp.[EmployeeID], tmp.[Date], tmp.LeaveStatusTyp
    FROM tmp where tmp.[Date]='2015-09-04' and tmp.LeaveStatusTyp in (19, 20, 64) 
    ORDER BY tmp.[EmployeeID], tmp.[Date] 
    OPTION (MAXRECURSION 0) 
    Thursday, October 1, 2015 12:56 PM
  • when i'm trying to post am image for sql table, it always says, you can insert images only after we verified your account. then, when it will happen and how can i verify my account ?.. tell me clearly.
    Saturday, October 3, 2015 5:23 AM
  • when i'm trying to post am image for sql table, it always says, you can insert images only after we verified your account. then, when it will happen and how can i verify my account ?.. tell me clearly.

    upload to your OneDrive and share image link here.

    I think you still need explain more about the database structure and your requirement.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Sunday, October 4, 2015 9:12 AM
    Moderator
  • i cannot even give a link also,

    it says ytou cant add link untill we verify your account.. what can i do ?

    Sunday, October 4, 2015 10:21 AM
  • i want a linq query against a table which gives a result set as the two date columns give result as between dates.

    and it will group as based on emp id. take a look at basetable first row.

    id  fromdate         todate          leavetype    isfirsthalf    issecondhalf

    1  2015-01-01   2015-01-03       20                  0           1

    2  2015-01-01   2015-01-03       20                  0           0

    3  2015-01-01   2015-01-03       19                  1           0

    4  2015-01-01   2015-01-03       19                  0           1

    and i want the result like,

    ResultDate      leaveType       result

    2015-01-01    19                 3 have isfirstHalf, 4

    2015-01-01     20                1 , 2

    2015-01-02     19                3,4

    2015-01-02     20                 1, 2

    2015-01-03     19                 3, 4 have issecondhalf

    2015-01-03     20                 1 have issecondhalf, 2

    i need this resultset in linq query

    Sunday, October 4, 2015 10:59 AM

  •             DateTime previousMonth = leaveDate.AddDays(-35);
                DateTime nextMonth = leaveDate.AddDays(+35);
                List<LeaveRequests> leaveRequestDate = new List<LeaveRequests>();
                using (HRMSDatabaseDataContext context = new HRMSDatabaseDataContext())
                {
                    
                    var leaveRequest = context.Leaves
                                        .Where(l=>l.ToDate>=previousMonth && (l.LeaveStatusTyp==19 || l.LeaveStatusTyp==20 || l.LeaveStatusTyp==64))
                                        .Select(x => new { x.EmployeeID, x.FromDate, x.LeaveStatusTyp }).ToList();
                    var dateGroup = (from t in leaveRequest
                                  group t by new { resultDate = t.FromDate, leaveStatusTyp = t.LeaveStatusTyp } into g
                                  select new { resultDate = g.Key.resultDate, leaveStatusTyp = g.Key.leaveStatusTyp, EmpId = g.AsEnumerable() })
                                 .ToList()
                                 .Select(q => new { resultDate = q.resultDate, leaveStatusTyp = q.leaveStatusTyp, EmpId = q.EmpId.Aggregate("", (acc, t) => (acc == "" ? "" : acc + ",") + t.EmployeeID) });

                    foreach (var leaveType in dateGroup)
                    {

                        LeaveRequests lr = new LeaveRequests { date=leaveType.resultDate, id=leaveType.EmpId, leaveStatusType=leaveType.leaveStatusTyp };
                        
                            leaveRequestDate.Add(lr);
                    }
                    
                    return leaveRequestDate;
    Monday, October 5, 2015 12:38 PM
  •  $(function () {
                $("ul.level1 li").before(function () {
                    $(this).stop().animate({ opacity: 0.7, width: "130px" }, "slow");
                }, function () {
                    $(this).stop().animate({ opacity: 1, width: "130px" }, "slow");
                });
            });
    Thursday, October 8, 2015 12:25 PM
  • $(document).ready(function() {


        // Create Event manually 

        // $('#create-event').click(function(){
        // var vj=$('#write-event').val();
        // add_event(vj);
        //});

        document.getElementById('write-event').onkeypress = function(e) 
        {
            var event = e || window.event;
            var charCode = event.which || event.keyCode;

            if ( charCode == '13' ) 
            {
                var vj=$('#write-event').val();
                add_event(vj);

            }
        }

        function add_event(vj)
        {
            if(vj=="")
            {
                return;
            }
            var eventColor=$('.event-color').val();
            $('#external-events ul').prepend('<li data-class="'+eventColor+'" class="external-event list-group-item '+eventColor+' list-group-item">'+vj+' </li>')
            $('#write-event').val('');

            initialize_events();

        }
        function ToJavaScriptDate(value) {
            var pattern = /Date\(([^)]+)\)/;
            var results = pattern.exec(value);
            var dt = new Date(parseFloat(results[1]));
            return dt.getFullYear() + "-" + ("0" + (dt.getMonth() + 1)).slice(-2) + "-" + ("0" + dt.getDate()).slice(-2);
        }


        /* initialize the external events
    -----------------------------------------------------------------*/
        function initialize_events()
        {
            $('#external-events ul li.external-event').each(function() {

                // create an Event Object (http://arshaw.com/fullcalendar/docs/event_data/Event_Object/)
                // it doesn't need to have a start or end
                var eventObject = {
                    title: $.trim($(this).text()) // use the element's text as the event title
                };

                // store the Event Object in the DOM element so we can get to it later
                $(this).data('eventObject', eventObject);

                // make the event draggable using jQuery UI
                $(this).draggable({
                    zIndex: 999,
                    revert: true,      // will cause the event to go back to its
                    revertDuration: 0  //  original position after the drag
                });

            });
        }

        initialize_events();
        var date = new Date();
        var d = date.getDate();
        var m = date.getMonth();
        var y = date.getFullYear();

        var calendar = $('#calendar').fullCalendar({
            header: {
                nbsp;           center: 'title',
                right: 'month,agendaWeek,agendaDay'
            },
            //selectable: true,
            //selectHelper: true,
            //select: function(start, end, allDay) {
            // var title = prompt('Event Title:');
            // if (title) {
            // calendar.fullCalendar('renderEvent',
            // {
            // title: title,
            // start: start,
            // end: end,
            // allDay: allDay
            // },
            // true // make the event "stick"
            // );
            // }
            // calendar.fullCalendar('unselect');
            //},
            //editable: false,
            //droppable: false, // this allows things to be dropped onto the calendar !!!
            //drop: function(date, allDay) { // this function is called when something is dropped

            // // retrieve the dropped element's stored Event Object
            // var originalEventObject = $(this).data('eventObject');

            // // we need to copy it, so that multiple events don't have a reference to the same object
            // var copiedEventObject = $.extend({}, originalEventObject);

            // // assign it the date that was reported
            // copiedEventObject.start = date;
            // copiedEventObject.allDay = allDay;
            // copiedEventObject.className = $(this).data('class');


            // // render the event on the calendar
            // // the last `true` argument determines if the event "sticks" (http://arshaw.com/fullcalendar/docs/event_rendering/renderEvent/)
            // $('#calendar').fullCalendar('renderEvent', copiedEventObject, true);

            // // is the "remove after drop" checkbox checked?
            // if ($('#drop-remove').is(':checked')) {
            // // if so, remove the element from the "Draggable Events" list
            // $(this).remove();
            // }

            //},
            //selectable: true,
            // selectHelper: true,
            // select: function(start, end, allDay) {
            // var title = prompt('Event Title:');
            // if (title) {
            // calendar.fullCalendar('renderEvent',
            // {
            // title: title,
            // start: start,
            // end: end,
            // allDay: allDay
            // },
            // true // make the event "stick"
            // );
            // }
            // calendar.fullCalendar('unselect');
            // },
            //events: [
            // {
            // title: 'All Day Event',
            // start: new Date(y, m, 1)
            // },

            // {
            // id: 999,
            // title: 'Repeating Event',
            // start: new Date(y, m, d+4, 16, 0),
            // allDay: false,
            // className:'bg-success'
            // },
            // {
            // title: 'Meeting',
            // start: new Date(y, m, d, 10, 30),
            // allDay: false,
            // className:'bg-info'
            // },

            // {
            // title: 'Click for Google',
            // start: new Date(y, m, 28),
            // end: new Date(y, m, 29),
            // url: 'http://google.com/'
            // }
            //]


            events: function(start, end, callback, day) {
                $.ajax({
                    type: "POST",
                    url: "LeaveCalendar.aspx/getLeaveRequestDate",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (data) {
                        var events = [];
                        var obj = data.d;
                        $.each(obj, function (index, row) {
                        //$(obj.event).each(function () {
                            var start = ToJavaScriptDate($(this).attr('date'));
                            var id = $(this).attr('id');
                            var status = $(this).attr('leaveStatusType');
                            var text;

                            if (status == 19) {
                                text = "Leave Requested";

                            }
                            else if (status == 20) {
                                text = "Leave Approved";

                            }
                            else if (status == 64) {
                                text = "Leave Accepted";

                            }
                            events.push({
                                title: text,
                                start: start,
                                end: start,
                                id: id

                            });
                        });
           },

           failure: function (data) {
           alert("fail");
       },
       error: function (data) {
           alert("Error");
       }

       });
      
                $('#calendar').fullCalendar('renderEvent', events, true);
                callback(events);

    },
         

    });

    });
    Friday, October 9, 2015 2:09 PM
  • eventMouseover: function (calEvent, jsEvent) {
                                    var tooltip = '<div class="tooltipevent" style="width:auto;height:auto;padding:10px;border-radius:10px;background-color:black;color:white;;z-index:10001;">' + '</br>' + SplitString(calEvent.id) + '</br>';
                                    var title = tooltip.replace(/\,/g, '');
                                    $("body").append(title);
                                    $(this).mouseover(function (e) {
                                        $(this).css('z-index', 10000);
                                        $('.tooltipevent').fadeIn('500');
                                        $('.tooltipevent').fadeTo('10', 1.9);
                                    }).mousemove(function (e) {
                                        $('.tooltipevent').css('top', e.pageY - 40 );
                                        $('.tooltipevent').css('left', e.pageX + 40);
                                    });
                                },

                                eventMouseout: function (calEvent, jsEvent) {
                                    $(this).css('z-index', 8);
                                    $('.tooltipevent').remove();
                                },
    Thursday, October 15, 2015 2:08 PM

  •             var tooltip = '<div class="tooltipevent">'  + SplitString(calEvent.id) + '</div>';
                var title = tooltip.replace(/\,/g, '');
                // var tool = '<span  class="fc-event DeleteEvent title" ><span class="smallipop-hint"></span></span>';

                $(this).append(title);

                $('.fc-event').smallipop({
                    preferredPosition: 'right',
                    theme: 'black',                
                    popupOffset: 10,
                    invertAnimation: true
                }, $('.tooltipevent'));
                $('.tooltipevent').remove();
               
            },

            eventMouseout: function (calEvent, jsEvent) {
                $(this).css('z-index', 8);
                $('.tooltipevent').remove();
            },
    Monday, October 19, 2015 1:20 PM