Solved

t-sql - how to without a cursor implementation

Posted on 2009-03-30
6
487 Views
Last Modified: 2012-05-06
Here is a very straightford sql to get me counts from the group by clause:

select
count(*) col1
from
tsys..tbl_base
group
by
base_month,
base_year

It returns:

col1
238875
239221
239454
239702
239965
240292
240708
241174
241624
241999
242426


Now without using a cursor I want to add a column to the result set to show the change in count.
Can you show me the sql that might accomplish this?
0
Comment
Question by:michaelrobertfrench
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:BryanMI
ID: 24020576
Perhaps I'm not understanding the question...  much like what you did, I built this sample.  Can you tell me what else you're needing to return?
CREATE TABLE #Test (Month int, Year int, data varchar(10))
 

SET NOCOUNT ON
 

INSERT #Test (Month, Year, data) VALUES (12, 2008, 'test1')

INSERT #Test (Month, Year, data) VALUES (12, 2008, 'test2')

INSERT #Test (Month, Year, data) VALUES (12, 2008, 'test3')
 

INSERT #Test (Month, Year, data) VALUES (11, 2007, 'test4')

INSERT #Test (Month, Year, data) VALUES (11, 2007, 'test5')

INSERT #Test (Month, Year, data) VALUES (11, 2007, 'test6')

INSERT #Test (Month, Year, data) VALUES (11, 2007, 'test7')
 

INSERT #Test (Month, Year, data) VALUES (10, 2007, 'test8')

INSERT #Test (Month, Year, data) VALUES (10, 2007, 'test9')
 

SET NOCOUNT OFF
 

--RETURNS RECORD COUNT BY YEAR

SELECT Count(*) As RecordTotal, Year FROM #Test GROUP BY Year
 

--RETURNS RECORD COUNT BY MONTH AND YEAR

SELECT Count(*) As RecordTotal, Year, Month FROM #Test GROUP BY Year, Month
 

DROP TABLE #Test

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24021177
what version of sql server are you using?
0
 

Author Comment

by:michaelrobertfrench
ID: 24022461
sql 2000
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:michaelrobertfrench
ID: 24022517
The result set would look like this:

col1      change_from_prior period
238875      
239221      346
239454      233
239702      248
239965      263
240292      327
240708      416
241174      466
241624      450
241999      375
242426      427

The change_from_prior_period is what I need the sql to provide.  An easy implementation with a cursor or a while loop but I want sql returning this result set.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24022590
this should do:
declare @t table ( col1 int, r int identity )

set nocount on

insert into @t (col1)

select count(*) col1

from tsys..tbl_base

group by base_year, base_month

order by base_year, base_month 
 

select t.col1, t.col1 - p.col1

  from @t t

  left join @t p

    on p.r = t.r-1

 order by t.r

Open in new window

0
 

Author Closing Comment

by:michaelrobertfrench
ID: 31564402
Yes - Thank You Very Much
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 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

11 Experts available now in Live!

Get 1:1 Help Now