Is it possible to design a reporting services report so that you can choose
the parameters that you are after (i.e. selecting from drop downs, etc... to
pass parameters to the underlying sql query)?
ThanksYes it is.
Behold:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_interactive_v1_50fn.asp
"farshad" wrote:
> Is it possible to design a reporting services report so that you can choose
> the parameters that you are after (i.e. selecting from drop downs, etc... to
> pass parameters to the underlying sql query)?
> Thankssql
Showing posts with label drop. Show all posts
Showing posts with label drop. Show all posts
Monday, March 26, 2012
query
drop table share
create table share
(
shid int identity ,
dt datetime,
price int
)
insert into share(dt,price) values('1-jan-2006',10)
insert into share(dt,price) values('2-jan-2006',11)
insert into share(dt,price) values('6-jan-2006',15)
insert into share(dt,price) values('4-jan-2006',9)
select * from share
output i want is
the difference in share prices from the previous day.
so
1 (11-10)
4 (15-11)
-6 (9-15)
select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
p2.price p2price
from
(select * from share
) as p2,
share p1
where p1.shid < p2.shid
order by p1.shid, p2.shid
thisis the closest i could come up with but it has duplicates.Try,
select b.price - a.price
from t1 as a inner join t1 as b
on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
[shid] then try:
select b.price - a.price
from t1 as a inner join t1 as b
on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
AMB
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||u r right. sorry for the mistake.
the query works fine.
the first query is what i was after.
i guess my approach of writing the query was wrong
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
> Try,
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
> Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
> [shid] then try:
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
>
> AMB
> "ichor" wrote:
>|||I think this should work:
select shid, dt, price - isnull((Select price from share b where b.shid =
a.shid - 1), 0) from share a
Hope this help...
Ed
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||can u explain why we need a b and c?
in the query?
"ichor" <ichor@.hotmail.com> wrote in message
news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>u r right. sorry for the mistake.
> the query works fine.
> the first query is what i was after.
> i guess my approach of writing the query was wrong
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
>|||We need to join a tabla to itself, so we need to alias the second reference
to it (b). The join condition is between a row in one side (b) and the a row
in the other side (a) where its [dt] value (in a) is equal to the previous of
current in b (the max([c.dt]) where [dt] < b.[dt]).
AMB
"ichor" wrote:
> can u explain why we need a b and c?
> in the query?
> "ichor" <ichor@.hotmail.com> wrote in message
> news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>
>|||could we have done this using only a and b and no c?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3AD01065-EBAA-4AAE-990C-E1F29FB8BD9B@.microsoft.com...
> We need to join a tabla to itself, so we need to alias the second
> reference
> to it (b). The join condition is between a row in one side (b) and the a
> row
> in the other side (a) where its [dt] value (in a) is equal to the previous
> of
> current in b (the max([c.dt]) where [dt] < b.[dt]).
>
> AMB
> "ichor" wrote:
>
create table share
(
shid int identity ,
dt datetime,
price int
)
insert into share(dt,price) values('1-jan-2006',10)
insert into share(dt,price) values('2-jan-2006',11)
insert into share(dt,price) values('6-jan-2006',15)
insert into share(dt,price) values('4-jan-2006',9)
select * from share
output i want is
the difference in share prices from the previous day.
so
1 (11-10)
4 (15-11)
-6 (9-15)
select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
p2.price p2price
from
(select * from share
) as p2,
share p1
where p1.shid < p2.shid
order by p1.shid, p2.shid
thisis the closest i could come up with but it has duplicates.Try,
select b.price - a.price
from t1 as a inner join t1 as b
on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
[shid] then try:
select b.price - a.price
from t1 as a inner join t1 as b
on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
AMB
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||u r right. sorry for the mistake.
the query works fine.
the first query is what i was after.
i guess my approach of writing the query was wrong
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
> Try,
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
> Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
> [shid] then try:
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
>
> AMB
> "ichor" wrote:
>|||I think this should work:
select shid, dt, price - isnull((Select price from share b where b.shid =
a.shid - 1), 0) from share a
Hope this help...
Ed
"ichor" wrote:
> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||can u explain why we need a b and c?
in the query?
"ichor" <ichor@.hotmail.com> wrote in message
news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>u r right. sorry for the mistake.
> the query works fine.
> the first query is what i was after.
> i guess my approach of writing the query was wrong
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
>|||We need to join a tabla to itself, so we need to alias the second reference
to it (b). The join condition is between a row in one side (b) and the a row
in the other side (a) where its [dt] value (in a) is equal to the previous of
current in b (the max([c.dt]) where [dt] < b.[dt]).
AMB
"ichor" wrote:
> can u explain why we need a b and c?
> in the query?
> "ichor" <ichor@.hotmail.com> wrote in message
> news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>
>|||could we have done this using only a and b and no c?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3AD01065-EBAA-4AAE-990C-E1F29FB8BD9B@.microsoft.com...
> We need to join a tabla to itself, so we need to alias the second
> reference
> to it (b). The join condition is between a row in one side (b) and the a
> row
> in the other side (a) where its [dt] value (in a) is equal to the previous
> of
> current in b (the max([c.dt]) where [dt] < b.[dt]).
>
> AMB
> "ichor" wrote:
>
Friday, March 9, 2012
QA database change take too much time
Hi,
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
Falik
To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
Falik
To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>
QA database change take too much time
Hi,
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
FalikAre the databases set to autoclose on your local SQL Server?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>|||To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
FalikAre the databases set to autoclose on your local SQL Server?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>|||To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>
QA database change take too much time
Hi,
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
FalikAre the databases set to autoclose on your local SQL Server?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>|||To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>
MSSQL 2K, SP4 on XP sp2
While connected to local SQL server, when I click on DatabBase drop down
Combo in Query Analyzer, it takes around 10+ seconds before displaying the
databases to select, however if I connect to another SQL server on my office
LAN, then list appears with no time.
appreciate your help to fix this.
Thanks
FalikAre the databases set to autoclose on your local SQL Server?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>|||To add on to Kalen;
You might be you have enabled the database option "AUTOCLOSE". THis will
close
the MDF and LDF as soon as the last user logs of the database. Again the MDF
and LDF will be opened once a user logins to the database.
How to check this option is checked:-
1. Enterprise manager -- Databases -- Select the database
2. Right click and select properties -- Choose options
3. Chek whether AUTOCLOSE option is "checked". If yes then remove it
This will ensure that MDF and LDF will never closed as soon as last user
logs off.
Thanks
Hari
"Falik Sher" <faliks@.hotmail.com> wrote in message
news:%23npQHWQFHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hi,
> MSSQL 2K, SP4 on XP sp2
> While connected to local SQL server, when I click on DatabBase drop down
> Combo in Query Analyzer, it takes around 10+ seconds before displaying the
> databases to select, however if I connect to another SQL server on my
> office LAN, then list appears with no time.
> appreciate your help to fix this.
> Thanks
> Falik
>
Subscribe to:
Posts (Atom)