kmc10314
asked on
SQL Server Loop and Group By
Hello, Experts.
I want to do following for SQL Server looping.
I have several dates that needs to be broken down to intervals depending on days between.
For example, lets say I have some expired dates
01-01-13
01-15-13
01-30-13
02-02-13
03-15-13
03-22-13
I need that to be broken down into this format
Dates Condition Good From Group
01-01-13 | Good | N/A | 1
01-15-13 | Good | 01-01-13 | 1
01-30-13 | Good | 01-01-13 | 1
02-02-13 | Good | N/A | 2
02-17-13 | Good | 02-17-13 | 2
03-15-13 | Good | N/A | 3
03-22-13 | Good | 03-15-13 | 3
Can you guys help me how to do this in SQL Server 2005?
Thank you in advance!
I want to do following for SQL Server looping.
I have several dates that needs to be broken down to intervals depending on days between.
For example, lets say I have some expired dates
01-01-13
01-15-13
01-30-13
02-02-13
03-15-13
03-22-13
I need that to be broken down into this format
Dates Condition Good From Group
01-01-13 | Good | N/A | 1
01-15-13 | Good | 01-01-13 | 1
01-30-13 | Good | 01-01-13 | 1
02-02-13 | Good | N/A | 2
02-17-13 | Good | 02-17-13 | 2
03-15-13 | Good | N/A | 3
03-22-13 | Good | 03-15-13 | 3
Can you guys help me how to do this in SQL Server 2005?
Thank you in advance!
Please clearly and explicitly state the rule(s) to be used in determining the grouping and the value for the 'Good From' column.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Only critical point will be starting point.
For example, I used 30 days.
So if the initial starting point was 01-01-13, it will be grouped under 01-01-13 until 01-31-13.
Then it can restart on any date after that.
Let's say if it started on 02-02-13, the grouping will last until 03-04-13.
For example, I used 30 days.
So if the initial starting point was 01-01-13, it will be grouped under 01-01-13 until 01-31-13.
Then it can restart on any date after that.
Let's say if it started on 02-02-13, the grouping will last until 03-04-13.
ASKER
I'll try this tomorrow and see how it works out.
Quick question though.
Would it work if the dates are random? (i.e. some months may not have dates at all)
Quick question though.
Would it work if the dates are random? (i.e. some months may not have dates at all)
You're going to have to add more specifics--and a better example--around that thirty day rule.
As for random dates, my approach will work just fine, so long as the frame of reference is the calendar month. So, the earliest month automatically gets Group 1, the next earliest Group 2, etc.
As for random dates, my approach will work just fine, so long as the frame of reference is the calendar month. So, the earliest month automatically gets Group 1, the next earliest Group 2, etc.
ASKER
Hello,
I have tried it and it seems like it's always grouping at 1st of the month.
However, that is not the case for this example.
I did realize I made a mistake on the example above, but the concept should be same.
(Good From date for 2-17-13 should actually be 2-02-13)
Good From Date or the initial date of the group does is not always the 1st of the month.
As you see the, the groups can start on 2nd or 15th of the month.
01-01-13 | Good | N/A | 1 -> Good From Date 1
01-15-13 | Good | 01-01-13 | 1
01-30-13 | Good | 01-01-13 | 1
02-02-13 | Good | N/A | 2 -> Good From Date 2
02-17-13 | Good | 02-02-13 | 2
03-15-13 | Good | N/A | 3 -> Good From Date 3
03-22-13 | Good | 03-15-13 | 3
I have tried it and it seems like it's always grouping at 1st of the month.
However, that is not the case for this example.
I did realize I made a mistake on the example above, but the concept should be same.
(Good From date for 2-17-13 should actually be 2-02-13)
Good From Date or the initial date of the group does is not always the 1st of the month.
As you see the, the groups can start on 2nd or 15th of the month.
01-01-13 | Good | N/A | 1 -> Good From Date 1
01-15-13 | Good | 01-01-13 | 1
01-30-13 | Good | 01-01-13 | 1
02-02-13 | Good | N/A | 2 -> Good From Date 2
02-17-13 | Good | 02-02-13 | 2
03-15-13 | Good | N/A | 3 -> Good From Date 3
03-22-13 | Good | 03-15-13 | 3
ASKER
NM, it did work.
Thank you for your help!
Thank you for your help!
ASKER
One more question, though
I just realized if the "Good From" Date starts from middle of the month and is stretched to next month, it does not pick up
For example,
03-15-13 | Good | N/A | 3
03-22-13 | Good | 03-15-13 | 3 -> Works
04-10-13 | Good | 03-15-13 | 3 -> Does not Work.
Can you help me with this?
I just realized if the "Good From" Date starts from middle of the month and is stretched to next month, it does not pick up
For example,
03-15-13 | Good | N/A | 3
03-22-13 | Good | 03-15-13 | 3 -> Works
04-10-13 | Good | 03-15-13 | 3 -> Does not Work.
Can you help me with this?