Counting active listings by month and year

Question

I am currently working with real-estate data, and each Listing entity has a ListingDate and a CloseDate. What I am currently trying to do is to count how many Listings are active in a given month and year (group by year and month).

So for an example if Listing1 had an ListingDate of 05/01/2020 and a CloseDate of 08/01/2020, there would be 1 Active count for May, June, July, and August and a year total of 4.

I am using EF and LINQ, and was wondering if I could solve it somehow.

Any help or advice is appreciated.

score 0 · Answer 1 · answered Jan 20 '22 at 21:11

0

Let's assume that date is given in DateTime structs. (You can parse text input to DateTime, check this) We can iterate over a List containing Listing entities, and perform a check to see if given date is in the range of ListingDate and ClosingDate. If the check succeeds, copy the entity to another list.

DateTime query = ...;
List<Listing> list = ...;
List<Listing> pass = new();

foreach (Listing entity in list)
{
    if (entity.ListingTime < query && query < entity.ClosingTime)
        pass.Add(entity)
}

While checking whether the query is in range, we could've used DateTime.Compare() but less than/greater than operators make the statement easier to read.

answered Jan 20 '22 at 21:11

fyb

145
13

Hi @fybalaban thank you for the response. So I was able to retrieve all the active listings in a given `DateTime`, but I was stuck on counting how many of those listings where active in a given month and year. I was thinking of using a dictionary to help with the count but I just can't get my head around the actual counting. `var dict = new Dictionary(); public class ActiveListings { public int Month { get; set; } public int Count { get; set; } }` – Shawn Jan 20 '22 at 21:27
@Shawn how can you retrieve all active listings in a given DateTime but not in a given month and year? And what do you mean by 'counting'? Can you please elaborate? – fyb Jan 20 '22 at 21:30
To give more context, I am creating a bar graph and each month is represented by a bar (counting all the active listings for that specific month) and is group by different years. So for an example Listing1 has a ListingDate of 05/01/2020 and a CloseDate of 08/01/2020, the graph would need to show 1 active listing for May, June, July, and August (since it was active during those months). With that said, what I am trying to figure out is how many listings were active (grouped by months and years) throughout the course of a given timespan – Shawn Jan 20 '22 at 21:43

score 0 · Accepted Answer · answered Jan 21 '22 at 00:48

Sure you can; if you map listings to each month in which it's active, you can then simply group the results by month and get the counts trivially. Thus, the trickiest part is to just come up with the month DateTime values, which isn't that tricky.

Extension method to get month DateTimes from a start and end date:

public static IEnumerable<DateTime> GetMonths(this DateTime startDate, DateTime endDate)
{
    var monthDiff = (endDate.Month - startDate.Month) + (12 * (endDate.Year - startDate.Year));
    var startMonth = new DateTime(startDate.Year, startDate.Month, 1);
    
    return Enumerable.Range(0, monthDiff + 1)
        .Select(i => startMonth.AddMonths(i));
}

Create lookup:

var listingsByMonth = listings
    .SelectMany(l =>
        {
            return l.ListingDate.GetMonths(l.ClosingDate.AddDays(-1)) // assuming closing date is exclusive
                .Select(dt => new KeyValuePair<DateTime, Listing>(dt, l));
        })
    .ToLookup(kvp => kvp.Key, kvp => kvp.Value);

Demonstration of results:

foreach(var g in listingsByMonth)
{
    Console.WriteLine($"{g.Key:yyyy-MM}: {g.Count()}");
}

Fiddle

Counting active listings by month and year

2 Answers2