CODEWITHSUNDEEP
sqllinq-to-sql
Johnny
Johnny

Reputation: 1575

SQL & Linq To SQL Help

I have a table which is some thing like below..

Date             ID

 2009-07-01            1
 2009-07-01            2
 2009-07-01            3
 2009-08-01            4
 2009-08-01            5
 2009-08-01            6
 2009-09-01            7
 2009-09-01            8
 2009-10-01            9
 2009-10-01            10
 2009-11-01            11

....

Now I need to write a query which will show a output like below.

Date               Start            End
2009-07              1               3
2009-08              4               6
2009-09              7               8

...

How can I do this.. Any help would be highly appreciated Thanking In Advance Johnny

Upvotes: 0

Views: 49

Answers (3)

Daniel Renshaw
Daniel Renshaw

Reputation: 34187

This might be what you're looking for:

var a = from myObj in db.MyObjs
        group myObj by myObj.Date.ToString("yyyy-mm")
            into ymGroup
            select new { Date = ymGroup.Key, Start = ymGroup.Min(c => c.ID), End = ymGroup.Max(c => c.ID) };

Upvotes: 0

Shrewdroid
Shrewdroid

Reputation: 800

i am not an expert in this but you can try the following code

var yourRows = _entities.YourTable.Where(colid => ((colid.Id == 3) || (colid.Id == 6) || (colid.Id == 8)));
return View(yourRows);

hope it works for you!!

take care

Upvotes: 0

EgorBo
EgorBo

Reputation: 6142

    tableData.GroupBy(i => i.Date).Select(i => new
          {
              DateTime = i.Key,
              Start = i.Min(j => j.ID),
              End = i.Max(j => j.ID)
          });

Upvotes: 3

Related Questions