• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

t-sql - how to without a cursor implementation

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
michaelrobertfrench
Asked:
michaelrobertfrench
  • 3
  • 2
1 Solution
 
BryanMICommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of sql server are you using?
0
 
michaelrobertfrenchAuthor Commented:
sql 2000
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
michaelrobertfrenchAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
michaelrobertfrenchAuthor Commented:
Yes - Thank You Very Much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now