Post reply

Need Help with SQL Query

  • June 23, 2016 at 7:16 am

    #314660

    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

    #1886522

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 23, 2016 at 7:36 am

    #1886527

    Thank you very much

  • June 23, 2016 at 7:45 am

    #1886532

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply