Post reply

query with CTE

  • July 6, 2019 at 10:47 am

    #3660424

    Hello, I need help to write a query with CTE.

    I wanna write a query for TotalPrice of Last Sunday of each month between some years.

  • July 6, 2019 at 9:11 pm

    #3660465

    Please give this a read and follow the advise on how to post a question.

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • July 7, 2019 at 9:30 am

    #3660516

    nooshin wrote:

    Hello, I need help to write a query with CTE. I wanna write a query for TotalPrice of Last Sunday of each month between some years.

    Why do you need a CTE to do that? Doesn't sound like you do. Homework assignment?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • July 7, 2019 at 2:46 pm

    #3660577

    nooshin wrote:

    Hello, I need help to write a query with CTE. I wanna write a query for TotalPrice of Last Sunday of each month between some years.

    Use a Tally Table or similar function to generate the 1st of each month within the given date range.  Then use the DATEDIFF /7*7 trick to find the previous Sunday, which will always be that last Sunday of the month.  Obviously, you'll need to offset each month by +1 month so that the previous month is actually the current month.

    From there, it should be easy for you to do the aggregates that you need.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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