June 23, 2016 at 7:16 am
Hello All,
I'm having issue in writing the complex query to find the below use case:
Find the number of property views per branch within 1 month, 2 months, and 3 months of client registration in one query.
Attached is the ER diagram:
Any help is greatly appreciated.
Thanks,
Amol
June 23, 2016 at 7:22 am
here's my best guess.
Select
br.Branchno,
DATEADD(mm, DATEDIFF(mm,0,vw.viewdate), 0) As MonthViewed,
Count(*) As TotalViews
FROM Branch
INNER JOIN Registration rg ON br.Branchno = rg.Branchno
INNER JOIN Client cl ON rg.Clientno = cl.Clientno
INNER JOIN Viewing vw ON cl.Clientno = vw.ClientNo
Group By br.Branchno
ORDER BY br.Branchno,MonthViewed
Lowell
June 23, 2016 at 7:36 am
Thank you very much
June 23, 2016 at 7:45 am
reading it again, since you want to hav eby month, the date logic needs to be int he group by also:
Select
br.Branchno,
DATEADD(mm, DATEDIFF(mm,0,vw.viewdate), 0) As MonthViewed,
Count(*) As TotalViews
FROM Branch
INNER JOIN Registration rg ON br.Branchno = rg.Branchno
INNER JOIN Client cl ON rg.Clientno = cl.Clientno
INNER JOIN Viewing vw ON cl.Clientno = vw.ClientNo
Group By br.Branchno,DATEADD(mm, DATEDIFF(mm,0,vw.viewdate), 0)
ORDER BY br.Branchno,MonthViewed
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply