Expanding Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Expanding Dates

Author

Topic

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-23 : 08:10:48

Hey guys, I have a question about expanding a date range using sql.

What I have is a list of acct numbers that correspond with any where from 1 to 12 different monthly statement dates (1/1/2001 - 12/1/2001).

I am trying to write a query so that I can take this:

Acct# MediaType StmtStartDate StmtEndDate NotRecd
00001 statement 01/01/2009 12/01/2009 12

To this:

Acct# MediaType StmtStartDate Not Recd
00001 statement 01/01/2009 1
00001 statement 02/01/2009 1
00001 statement 03/01/2009 1
00001 statement 04/01/2009 1
00001 statement 05/01/2009 1
00001 statement 06/01/2009 1
00001 statement 07/01/2009 1
00001 statement 08/01/2009 1
00001 statement 09/01/2009 1
00001 statement 10/01/2009 1
00001 statement 11/01/2009 1
00001 statement 12/01/2009 1

There are roughly 3000 accounts total that need to be broke out like this.

Any help would be much appreciated.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-23 : 08:36:45

Something like this maybe...? ->

SELECT 
	a.Acct, 
	a.MediaType, 
	StmtStartDate = MIN(a.StmtStartDate), 
	StmtEndDate = MAX(b.StmtStartDate), 
	NotRecd = DATEDIFF(month, MIN(a.StmtStartDate), MAX(b.StmtStartDate))
FROM myTable a
	LEFT OUTER JOIN myTable b
		ON a.Acct = b.Acct
			AND a.MediType = b.MediaType
			AND a.StmtStartDate < b.StmtStartDate 
GROUP BY a.Acct, a.MediaType

- Lumbago
My blog-> http://thefirstsql.com

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-23 : 08:59:23

I am getting an error at the = mark between stmtstartdate and MIN for some reason. Not sure why.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-23 : 10:21:30

[code]DECLARE @Sample TABLE
(
Acct CHAR(5) PRIMARY KEY,
MediaType VARCHAR(20) NOT NULL,
StmtStartDate DATE NOT NULL,
StmtEndDate DATE NOT NULL,
NotRecd SMALLINT NOT NULL
);

INSERT @Sample
VALUES ('00001', 'Statement', '20090101', '20090112', 12);

-- SwePeso
SELECT s.Acct,
s.MediaType,
DATEADD(DAY, v.Number, s.StmtStartDate) AS StmtStartDate,
1 AS NotRecD
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND s.NotRecd - 1[/code]

N 56°04'39.26"
E 12°55'05.63"

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-23 : 16:50:10

This worked for me, I do have another question since that did work, If I have a long list of accts that I am wanting to do this for, how could I get those mulitple accts in. I can upload the accts into my sandbox from an exel file but not sure how to go from there.

maybe:

insert @ sample
values (sandbox.fightingtiger\acctnumbers);

Or how would you suggest taking what is above and applying that to several hundred accts?

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-24 : 08:28:59

I guess I am trying to figure out how I can load all the acct numbers I have into the query to have the broken account accordingly. I tweaked what you sent to see if it would work with mulitple lines of information and it did, but since I have such a large list of acct numbers I was trying to figure out a way to load those values with out posting them all in like above. Any thoughts?

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-24 : 12:06:19

Nvm, I actually figured it out on my own and got the query to run correctly. I tweaked what SwePeso posted by just using the the inner join part and DateAdd part attached to the created table I was orignially using and was able to get all the accounts I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources