Home LINQ - query on query results (some complex one)
Reply: 0

LINQ - query on query results (some complex one)

user1662 Published in May 21, 2018, 5:02 pm

Need some help in writing LINQ from the following SQL. The main problem is double grouping. I'm stacked in the second grouping

group by s.[e-year], s.[e-month]

Don't know how to implement.

Thanks a lot.

select s.[e-year], s.[e-month], count(s.projectid) 'projects entranced',
          (select count(subquery.CustomerTypeID) from
              (select count(ap.ProjectID) as 'count', c.CustomerTypeID FROM Logging_ProjectsEntrances] pe
              inner join users u on pe.userid = u.userid
              inner join Companies c on u.CompanyId = c.CompanyID
              inner join AssignedProjects up on pe.ProjectID = up.ProjectID
              inner join Projects p on up.ProjectID = p.ProjectID
              where ap.ProductID = 1 and year(pe.EntranceDate) = s.[e-year] and MONTH(pe.entrancedate) = s.[e-month] and c.CustomerTypeID = 2
              group by ap.ProjectID, c.CustomerTypeID) subquery
          group by subquery.CustomerTypeID
      select YEAR(pe.EntranceDate) as 'e-year', MONTH(pe.EntranceDate) as 'e-month', up.ProjectID as 'projectid' 
      FROM Logging_ProjectsEntrances pe
      inner join AssignedProjects ap on pe.ProjectID = ap.ProjectID
      inner join Projects p on ap.ProjectID = p.ProjectID
      where ap.ProductID = 1
      group by year(pe.EntranceDate), month(pe.EntranceDate), ap.ProjectID
  ) as s
  group by s.[e-year], s.[e-month]
  order by s.[e-year] desc , s.[e-month] desc
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317695 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO