Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Yearly Data from Selected Range

Author  Topic 

stsj
Starting Member

9 Posts
Posted - 2003-04-10 : 22:59:53
Hi all,
I have the Following Query
It is working fine if the first month is 01 and Second month is 12
but i want like this if the user selects 200103 to 200203 i want the
value of composite should be added from 200103 to 200203 in one row
and other row from 200203 to 200303.
like this if the user selects from 200004 to 200304 i have to get 3 rows..
that is Yearly data..so how can i modify the following query or any new query.


select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and '200312'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)


Thanks

shifis
Posting Yak Master

157 Posts
Posted - 2003-04-11 : 17:41:17
You can made the SQL stament has a string in your program and then made a Store procedure that execute your string.


CREATE PROCEDURE SP_EXEC1
@InsertString varchar(5000)
AS
exec ( @InsertString)
GO
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-04-11 : 18:00:30
quote:

You can made the SQL stament has a string in your program and then made a Store procedure that execute your string.


CREATE PROCEDURE SP_EXEC1
@InsertString varchar(5000)
AS
exec ( @InsertString)
GO



DON'T EVER CREATE A STORED PROCEDURE THAT DOES THIS. This allows any user who can execute the stored procedure to be able to do anything that he/she wants.

Shifis, please do not recommend to people to do this. This is very, very dangerous.

For example:
EXEC SP_EXEC1 'DELETE FROM Table1'

The user does not even need delete permission on Table1 in order to delete the data. The user only needs execute permission on the stored procedure.

I believe that it allows the user to perform drops as well:
EXEC SP_EXEC1 'DROP TABLE Table1'


Tara
Go to Top of Page

stsj
Starting Member

9 Posts
Posted - 2003-04-11 : 23:12:44
Hi All,

Thanks For u replies.

I think all are confused with my message , so i am Explaining it once again
I have the following table.

CREATE TABLE [dbo].[TestPG] (
[Composite] [int],
[Period] [datetime] )

Now user may select any period that is from

199904 to 200203 ---- 36 months---
99/04 to 00/03
00/04 to 01/03
01/04 to 02/03

199707 to 200106 ---- 48 months
97/07 to 98/06
98/07 to 99/06
99/07 to 00/06
00/07 to 01/06

200005 to 200204 ---- 24 months
00/05 to 01/04
01/05 to 02/04

200001 to 200012 ---- 12 months
00/01 to 00/12

199803 to 199902 ---- 12 months
98/03 to 99/02

200201 to 200212 ---- 12 months
02/01 to 02/12

etc----

Those are just example periods user have the choice of selecting any period..that is
only 12,24,36,48....months...multiples of 12...
Now i want the data of the composite column summing for every 12 months in the selected
range...like user selects this 199707 to 200106 ---- 48 months
i want the sum(composite) for the following periods....
97/07 to 98/06
98/07 to 99/06
99/07 to 00/06
00/07 to 01/06
all the ranges are not constant user may select any range...if we have 96 months means
i want 8 rows..of data.


sample data

insert into testpg values('5','1-1-2000')
insert into testpg values('5','1-2-2000')
insert into testpg values('5','1-3-2000')
insert into testpg values('5','1-4-2000')
insert into testpg values('5','1-5-2000')
insert into testpg values('5','1-6-2000')
insert into testpg values('5','1-7-2000')
insert into testpg values('5','1-8-2000')
insert into testpg values('5','1-9-2000')
insert into testpg values('5','1-10-2000')
insert into testpg values('5','1-11-2000')
insert into testpg values('5','1-12-2000')

insert into testpg values('5','1-1-2001')
insert into testpg values('5','1-2-2001')
insert into testpg values('5','1-3-2001')
insert into testpg values('5','1-4-2001')
insert into testpg values('5','1-5-2001')
insert into testpg values('5','1-6-2001')
insert into testpg values('5','1-7-2001')
insert into testpg values('5','1-8-2001')
insert into testpg values('5','1-9-2001')
insert into testpg values('5','1-10-2001')
insert into testpg values('5','1-11-2001')
insert into testpg values('5','1-12-2001')


insert into testpg values('5','1-1-2002')
insert into testpg values('5','1-2-2002')
insert into testpg values('5','1-3-2002')
insert into testpg values('5','1-4-2002')
insert into testpg values('5','1-5-2002')
insert into testpg values('5','1-6-2002')
insert into testpg values('5','1-7-2002')
insert into testpg values('5','1-8-2002')
insert into testpg values('5','1-9-2002')
insert into testpg values('5','1-10-2002')
insert into testpg values('5','1-11-2002')
insert into testpg values('5','1-12-2002')


insert into testpg values('5','1-1-2003')
insert into testpg values('5','1-2-2003')
insert into testpg values('5','1-3-2003')
insert into testpg values('5','1-4-2003')
insert into testpg values('5','1-5-2003')
insert into testpg values('5','1-6-2003')
insert into testpg values('5','1-7-2003')
insert into testpg values('5','1-8-2003')
insert into testpg values('5','1-9-2003')
insert into testpg values('5','1-10-2003')
insert into testpg values('5','1-11-2003')
insert into testpg values('5','1-12-2003')

insert into testpg values('5','1-1-1999')
insert into testpg values('5','1-2-1999')
insert into testpg values('5','1-3-1999')
insert into testpg values('5','1-4-1999')
insert into testpg values('5','1-5-1999')
insert into testpg values('5','1-6-1999')
insert into testpg values('5','1-7-1999')
insert into testpg values('5','1-8-1999')
insert into testpg values('5','1-9-1999')
insert into testpg values('5','1-10-1999')
insert into testpg values('5','1-11-1999')
insert into testpg values('5','1-12-1999')


insert into testpg values('5','1-1-1998')
insert into testpg values('5','1-2-1998')
insert into testpg values('5','1-3-1998')
insert into testpg values('5','1-4-1998')
insert into testpg values('5','1-5-1998')
insert into testpg values('5','1-6-1998')
insert into testpg values('5','1-7-1998')
insert into testpg values('5','1-8-1998')
insert into testpg values('5','1-9-1998')
insert into testpg values('5','1-10-1998')
insert into testpg values('5','1-11-1998')
insert into testpg values('5','1-12-1998')


insert into testpg values('5','1-1-1997')
insert into testpg values('5','1-2-1997')
insert into testpg values('5','1-3-1997')
insert into testpg values('5','1-4-1997')
insert into testpg values('5','1-5-1997')
insert into testpg values('5','1-6-1997')
insert into testpg values('5','1-7-1997')
insert into testpg values('5','1-8-1997')
insert into testpg values('5','1-9-1997')
insert into testpg values('5','1-10-1997')
insert into testpg values('5','1-11-1997')
insert into testpg values('5','1-12-1997')
Go to Top of Page

claire
Starting Member

19 Posts
Posted - 2003-04-12 : 21:41:33


you posted in many forums. here is my reply.

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/183/qid/523606
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site