How to get the last 13 month/year

[ofsouto]

Hi, dear

A date (GetDate()) is a stored procedure inside parameter.
I need to list the last 13 month/year.
I need to generate a query where I can get the result bellow:
01/2005
12/2004
11/2004
...
02/2004
01/2004
Is it possible? How can I get it?

Thank you very much

Obede
Brazil

 

[r937]

you can do this easily if you have an integers table

create table integers (i integer)
insert into integers (i) values (0) 
insert into integers (i) values (1) 
insert into integers (i) values (2) 
insert into integers (i) values (3) 
insert into integers (i) values (4) 
insert into integers (i) values (5) 
insert into integers (i) values (6) 
insert into integers (i) values (7) 
insert into integers (i) values (8) 
insert into integers (i) values (9)       
insert into integers (i) values (10)       
insert into integers (i) values (11)       
insert into integers (i) values (12)

then you can generate the dates you want like this:

select convert(char(3)
          ,dateadd(m,-i,getdate()),1)
     + convert(char(4)
          ,dateadd(m,-i,getdate()),102)
  from integers
 where i between 0 and 12
order by i

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ofsouto]

Is there another way without creating any table?

 

[r937]

there shore is!!!

select convert(char(3)
          ,dateadd(m,-i,getdate()),1)
     + convert(char(4)
          ,dateadd(m,-i,getdate()),102)
  from (
    select 0 as i
    union all select 1
    union all select 2
    union all select 3
    union all select 4
    union all select 5
    union all select 6
    union all select 7
    union all select 8
    union all select 9
    union all select 10
    union all select 11
    union all select 12
       ) as integers
order by i

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

ofsouto,

Are you querying data from a table or did you just want to create a list of months/dates as rudy has already shown you how to do?

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[ofsouto]

I'm querying data from a table.
I have the table bellow

CREATE TABLE [dbo].[AC_CARTEIRA] (
[ac_suplier_code] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ac_date] [smalldatetime] NOT NULL ,
[ac_client_code] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ac_category] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AC_CARTEIRA] ADD
CONSTRAINT [PK_AC_CARTEIRA] PRIMARY KEY NONCLUSTERED
(
[ac_suplier_code],
[ac_date]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

There is only one date per month [ac_date].
I intend to show all clients and their category evolution by month (last 13 months).

I intend to create the grid bellow

client
01/2005 12/2004 11/2004 ... 01/2004
000001 03 01 03 NC
000002 NC 10 10 10
...
000010 09 08 07

 

[r937]

you say there is only one date per month

which date is it, the first of the month?

also, i don't think you need to generate the 13 month/years

since you only have one date per month, just put a WHERE condition into the query to select all ac_dates that are within 13 months of today

as far as arranging your output into crosstab format, you're on your own



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)