?
Solved

t-sql - how to without a cursor implementation

Posted on 2009-03-30
6
Medium Priority
?
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 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