April 15, 2014 at 10:03 am
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 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:
April 15, 2014 at 12:39 pm
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-2012758 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