Results 1 to 4 of 4

Thread: query help

  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: query help

    I have a query which returns
    select srvName, convert (varchar (10), MaxDate, 101), DBName, UsedSpaceInGB from BSizesByMonthly (NOLOCK) where srvName
    like 'Test%' order by srvName, DBName, MaxDate

    srvName MaxDate DBName UsedSpaceInGB
    Test 07/31/2008 TestDB 7.53
    Test 08/31/2008 TestDB 4.91
    Test 09/30/2008 TestDB 18.88
    Test 08/31/2007 pubs 0.16
    Test 09/30/2007 pubs 0.22
    Test 10/31/2007 pubs 0.27

    I would like to get the delta of column 4(UsedSpaceInGB) for a give database in column 5(Change).

    srvName MaxDate DBName UsedSpaceInGB Change
    Test 7/31/2008 TestDB 7.53
    Test 08/31/2008 TestDB 4.91 2.61
    Test 09/30/2008 TestDB 18.88 -13.97
    Test 08/31/2007 pubs 0.16
    Test 09/30/2007 pubs 0.22 -0.06
    Test 10/31/2007 pubs 0.27 -0.05

    Any help is greately appreciated.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    create table #test1
    (srvName varchar(10),
     MaxDate date,
     DBName varchar(10),
     UsedSpaceInGB numeric(6, 2))
    
    
    insert into #test1
    select 'Test', '07/31/2008', 'TestDB', 7.53 union all 
    select 'Test', '08/31/2008', 'TestDB', 4.91 union all 
    select 'Test', '09/30/2008', 'TestDB', 18.88 union all 
    select 'Test', '08/31/2007', 'pubs', 0.16 union all 
    select 'Test', '09/30/2007', 'pubs', 0.22 union all 
    select 'Test', '10/31/2007', 'pubs', 0.27;
    
    
    with dbcte
    as
    (select row_number() over (order by MaxDate) as SampleNo, *
     from #test1)
    select a.Srvname, a.DBName, b.MaxDate as MinDate, a.MaxDate, a.UsedSpaceInGB - b.UsedSpaceInGB as "Change"
    from dbcte b join dbcte a 
    	on a.srvname = b.srvname
    	and a.dbname = b.dbname
    	and a.SampleNo - 1 = b.SampleNo
    order by b.MaxDate
    Last edited by MCrowley; 06-11-09 at 16:00.

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    getting error when it is reaching the code "dbcte"
    Msg 102, Level 15, State 1, Line 17
    Incorrect syntax near 'dbcte'.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Fixed the code above. It needed a semicolon before the CTE declaration.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •