Post reply

How to get WTD,MTD and QTD in sql server

  • April 2, 2022 at 10:11 am

    #4009697

    Hello All,

    I have table sales where I have daily ,Weekly and monthly sales Data.

    The columns are TrendType,Date and Sales

    So here my requirement is I need to create a query on my table to calculate the last 12 months, 12 weeks and last 30 days along with MTD,WTD and QTD data.

    So here the trendtype for MTD should show as monthly, trendtype for WTD should show as Weekly and trendtype for QTD should show as Quarterly as these comes when we select the respected trend types in our reports.(Ex: if I select monthly then I can see last 12 months and MTD also)

    Could any one please help how to do

  • April 2, 2022 at 4:28 pm

    #4009800

    It would be helpful if you'd post the CREATE TABLE statement and a few rows of data in an INSERT/VALUES fashion.  We also need to know what day of the week is the "first day of the week" for you.

    We also need to know if the "first week" of any given starts at the first of the year regardless of the day of the week, which causes "partial weeks" that are less than 7 days in duration. Same question for the last week of the year v.s. the last day of the year.

    It would also be helpful if you could post an example of the expected output if you expect all the spans to be on a single report.

     

    --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 2 posts - 1 through 1 (of 1 total)

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