Question for SwePeso/board - 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

Question for SwePeso/board

Author

Topic

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-24 : 12:33:58

I have one question for you (Or any one that may can help me with this) on the query you helped me with in the other thread 'Expanding Dates'

This is what you posted and I took it and made some changes and got it to work for me but I am trying to add a few more things to make the query run excatly how I need it.

SELECT s.Acct,
s.MediaType,
DATEADD(Month, v.Number, s.StmtStartDate) AS StmtStartDate,
1 AS NotRecD
FROM @Sample AS s
-- Anything after this I don't understand what you are doing and can't make any changes to what things I am adding
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND s.NotRecd - 1

The only thing else I am trying to add is on the DATEADD function above I want to be able to subtract by the month as well instead of just adding extra months.

Any help would be much appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-24 : 12:48:16

The part below your comment in red retrieves numbers from master.dbo.spt_values. The JOIN condition limits it to type='P' (procedures) to eliminate duplicate values from other types. The BETWEEN condition limits the values to be less than the NotRecd column in your Sample table.

You can add another column that subtracts dates by simply putting a minus sign in the DATEADD function:

DATEADD(Month, -v.Number, s.StmtStartDate) AS OtherStartDate,

However, if you want those dates to be rows instead of columns, you'll need a different query. Please clarify if that's what you need.

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-24 : 13:48:35

Sorry about that, yes I am wanting to make them into rows.

I have it where it goes from
ACCTnumber AcctID Invoice MediaType MediaTypeID Stmtstartdate stmtenddate Notrecd
000000001 100 XXXX Statement 1 10/01/2009 09/01/2010 12

to go to this
ACCTnumber AcctID Invoice MediaType MediaTypeID Stmtstartdate Notrecd
000000001 100 XXXX Statement 1 10/01/2009 12
000000001 100 XXXX Statement 1 11/01/2009 12
000000001 100 XXXX Statement 1 12/01/2009 12
000000001 100 XXXX Statement 1 01/01/2010 12
000000001 100 XXXX Statement 1 02/01/2010 12
000000001 100 XXXX Statement 1 03/01/2010 12
000000001 100 XXXX Statement 1 04/01/2010 12
000000001 100 XXXX Statement 1 05/01/2010 12
000000001 100 XXXX Statement 1 06/01/2010 12
000000001 100 XXXX Statement 1 07/01/2010 12
000000001 100 XXXX Statement 1 08/01/2010 12
000000001 100 XXXX Statement 1 09/01/2010 12

What I need to is the same thing but in reverse starting from the stmtenddate if that makes sense

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

for that isnt it the same thing with only an order by added to end? I think it should suffice so far as values of all other fields remain same as per you posted data


SELECT s.Acct,
s.MediaType,
DATEADD(Month, 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
ORDER BY StmtStartDate DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-24 : 15:48:59

I figured it out actually after playing around with it for a few hours. Does exactly what I need it to now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:53:25

ok..cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources