T-SQL help with specialized groupings

  • OK, so I figure I need to use a cursor or some advanced windowing functions, to get what I'm after but I'm not seeing it. Could someone assist?

    So I have a result set that looks like this:

    inv_id md_amt date


    12758 0.00 2013-12-20

    12758 11.51 2013-12-17

    12758 10.51 2013-12-16

    12758 10.51 2013-10-22

    12758 10.51 2013-10-17

    12758 3.51 2013-09-06

    12758 0.00 2013-08-26

    12758 5.51 2013-08-04

    12758 3.51 2013-07-14

    12758 0.00 2013-06-02

    What I need is a way to tell how many distinct markdowns have been taken since the last time the md_amt was 0 for a give date.

    For instance if I queried today's date I would get 0, no markdowns have been taken md_amt is currently at 0. If I queried 12/18/2013 I would get 3.

    Any clue how I can go about doing this? I've tried various ranking functions but the fact that the md_amts are not necessarily unique between going back to 0 throws a wrench in things. :unsure:

  • badOedipus (4/15/2014)


    OK, so I figure I need to use a cursor or some advanced windowing functions, to get what I'm after but I'm not seeing it. Could someone assist?

    So I have a result set that looks like this:

    inv_id md_amt date


    12758 0.00 2013-12-20

    12758 11.51 2013-12-17

    12758 10.51 2013-12-16

    12758 10.51 2013-10-22

    12758 10.51 2013-10-17

    12758 3.51 2013-09-06

    12758 0.00 2013-08-26

    12758 5.51 2013-08-04

    12758 3.51 2013-07-14

    12758 0.00 2013-06-02

    What I need is a way to tell how many distinct markdowns have been taken since the last time the md_amt was 0 for a give date.

    For instance if I queried today's date I would get 0, no markdowns have been taken md_amt is currently at 0. If I queried 12/18/2013 I would get 3.

    Any clue how I can go about doing this? I've tried various ranking functions but the fact that the md_amts are not necessarily unique between going back to 0 throws a wrench in things. :unsure:

    You do NOT need a **cough**cursor**cough** here unless you want this process to be super slow.

    We can help you with a super fast set base solution. In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 2 posts - 1 through 1 (of 1 total)

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