Solved

SQL Server Loop and Group By

Posted on 2013-01-30
8
176 Views
Last Modified: 2013-01-31
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!
0
Comment
Question by:kmc10314
  • 5
  • 3
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38837676
Please clearly and explicitly state the rule(s) to be used in determining the grouping and the value for the 'Good From' column.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38837993
Assuming that:
"Group" is determined by calendar month
Earliest date in a given month has no Good From value

Then this appears to do the trick:

CREATE TABLE SomeTable (Expiry datetime)

INSERT INTO SomeTable (Expiry) VALUES
('01-01-2013'),
('01-15-2013'),
('01-30-2013'),
('02-02-2013'),
('02-17-2013'),
('03-15-2013'),
('03-22-2013')

SELECT t1.Expiry AS Dates, 'Good' AS Condition, 
    CASE WHEN t1.Expiry = x.MinDate THEN NULL ELSE x.MinDate END AS [Good From],
    x.[Group]
FROM SomeTable t1 INNER JOIN
    (SELECT MIN(Expiry) AS MinDate, MAX(Expiry) AS MaxDate,
        ROW_NUMBER() OVER(ORDER BY DATEADD(month, DATEDIFF(month, 0, Expiry), 0)) AS [Group]
    FROM SomeTable
    GROUP BY DATEADD(month, DATEDIFF(month, 0, Expiry), 0)) x ON t1.Expiry >= x.MinDate AND t1.Expiry <= x.MaxDate
ORDER BY t1.Expiry

DROP TABLE SomeTable

Open in new window

0
 

Author Comment

by:kmc10314
ID: 38837994
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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:kmc10314
ID: 38838080
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)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38839205
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.
0
 

Author Comment

by:kmc10314
ID: 38839896
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
0
 

Author Comment

by:kmc10314
ID: 38839931
NM, it did work.
Thank you for your help!
0
 

Author Comment

by:kmc10314
ID: 38840199
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?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now