Query a dynamic table...HELP!

Hello,

I am trying to write a query to pull data from a table that is system generated daily at midnight. I tried:

declare @.tablename varchar(50) set @.tablename = (select name from sysobjects where crdate = '05/01/2003') select * from @.tablename

But I get an error on 'select * from @.tablename' (must declare @.tablename)

Any assistance is GREATLY APPRECIATED!!!!Please check the answer in

http://forums.databasejournal.com/showthread.php?threadid=31446

Queries and wildcards

I am trying to write a query with a wildcard as part of it. the table that I
am looking at is mostly text (they are smdr logs from my phone system) I
currently use access to write the queries connected to SQL server. in the
example below I need to put 1150 and 1152 for each of the t10xx numbers. I
would like to use a wildcard for the called_party but it does not work I have
tried *,#, and a few others but no success. any ideas would be helpful.
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party = N'1150') OR
(calling_party = N't1052') AND (called_party = N'1150') OR
(calling_party = N't1053') AND (called_party = N'1150') OR
(calling_party = N't1054') AND (called_party = N'1150') OR
(calling_party = N't1055') AND (called_party = N'1150') OR
(calling_party = N't1056') AND (called_party = N'1150') OR
(calling_party = N't1057') AND (called_party = N'1150') OR
(calling_party = N't1058') AND (called_party = N'1150') OR
(calling_party = N't1059') AND (called_party = N'1150') OR
(calling_party = N't1060') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party = N'1152') OR
(calling_party = N't1052') AND (called_party = N'1152') OR
(calling_party = N't1053') AND (called_party = N'1152') OR
(calling_party = N't1054') AND (called_party = N'1152') OR
(calling_party = N't1055') AND (called_party = N'1152') OR
(calling_party = N't1056') AND (called_party = N'1152') OR
(calling_party = N't1057') AND (called_party = N'1152') OR
(calling_party = N't1058') AND (called_party = N'1152') OR
(calling_party = N't1059') AND (called_party = N'1152') OR
(calling_party = N't1060') AND (called_party = N'1152')The wild card characters supported in T-SQL are '%' and _ (underscore)
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bill Farinella" <BillFarinella@.discussions.microsoft.com> wrote in message
news:A375B699-7ECF-4F4E-8DD2-F395D8B7C897@.microsoft.com...
>I am trying to write a query with a wildcard as part of it. the table that
>I
> am looking at is mostly text (they are smdr logs from my phone system) I
> currently use access to write the queries connected to SQL server. in the
> example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> would like to use a wildcard for the called_party but it does not work I
> have
> tried *,#, and a few others but no success. any ideas would be helpful.
>
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
> (calling_party = N't1051') AND (called_party => N'1150') OR
> (calling_party = N't1052') AND (called_party => N'1150') OR
> (calling_party = N't1053') AND (called_party => N'1150') OR
> (calling_party = N't1054') AND (called_party => N'1150') OR
> (calling_party = N't1055') AND (called_party => N'1150') OR
> (calling_party = N't1056') AND (called_party => N'1150') OR
> (calling_party = N't1057') AND (called_party => N'1150') OR
> (calling_party = N't1058') AND (called_party => N'1150') OR
> (calling_party = N't1059') AND (called_party => N'1150') OR
> (calling_party = N't1060') AND (called_party => N'1150') OR
> (calling_party = N't1051') AND (called_party => N'1152') OR
> (calling_party = N't1052') AND (called_party => N'1152') OR
> (calling_party = N't1053') AND (called_party => N'1152') OR
> (calling_party = N't1054') AND (called_party => N'1152') OR
> (calling_party = N't1055') AND (called_party => N'1152') OR
> (calling_party = N't1056') AND (called_party => N'1152') OR
> (calling_party = N't1057') AND (called_party => N'1152') OR
> (calling_party = N't1058') AND (called_party => N'1152') OR
> (calling_party = N't1059') AND (called_party => N'1152') OR
> (calling_party = N't1060') AND (called_party => N'1152')
>|||On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
>I am trying to write a query with a wildcard as part of it. the table that I
>am looking at is mostly text (they are smdr logs from my phone system) I
>currently use access to write the queries connected to SQL server. in the
>example below I need to put 1150 and 1152 for each of the t10xx numbers. I
>would like to use a wildcard for the called_party but it does not work I have
>tried *,#, and a few others but no success. any ideas would be helpful.
(snip)
Hi Bill,
In this case, you might want to use IN instead of wildcards:
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
AND called_part IN (N'1150', N'1152')
Note: the ellipsis in the query above should be substituted by the full
list of valued you want to search for in calling_party.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wow, Thanks That worked great!! :) I wish I posted this sooner now. It could
have saved me days worth of work.
Thanks again,
Bill
"Hugo Kornelis" wrote:
> On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
> >I am trying to write a query with a wildcard as part of it. the table that I
> >am looking at is mostly text (they are smdr logs from my phone system) I
> >currently use access to write the queries connected to SQL server. in the
> >example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> >would like to use a wildcard for the called_party but it does not work I have
> >tried *,#, and a few others but no success. any ideas would be helpful.
> (snip)
> Hi Bill,
> In this case, you might want to use IN instead of wildcards:
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
> AND called_part IN (N'1150', N'1152')
> Note: the ellipsis in the query above should be substituted by the full
> list of valued you want to search for in calling_party.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Queries and wildcards

I am trying to write a query with a wildcard as part of it. the table that I
am looking at is mostly text (they are smdr logs from my phone system) I
currently use access to write the queries connected to SQL server. in the
example below I need to put 1150 and 1152 for each of the t10xx numbers. I
would like to use a wildcard for the called_party but it does not work I hav
e
tried *,#, and a few others but no success. any ideas would be helpful.
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1150') OR
(calling_party = N't1052') AND (called_party =
N'1150') OR
(calling_party = N't1053') AND (called_party =
N'1150') OR
(calling_party = N't1054') AND (called_party =
N'1150') OR
(calling_party = N't1055') AND (called_party =
N'1150') OR
(calling_party = N't1056') AND (called_party =
N'1150') OR
(calling_party = N't1057') AND (called_party =
N'1150') OR
(calling_party = N't1058') AND (called_party =
N'1150') OR
(calling_party = N't1059') AND (called_party =
N'1150') OR
(calling_party = N't1060') AND (called_party =
N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1152') OR
(calling_party = N't1052') AND (called_party =
N'1152') OR
(calling_party = N't1053') AND (called_party =
N'1152') OR
(calling_party = N't1054') AND (called_party =
N'1152') OR
(calling_party = N't1055') AND (called_party =
N'1152') OR
(calling_party = N't1056') AND (called_party =
N'1152') OR
(calling_party = N't1057') AND (called_party =
N'1152') OR
(calling_party = N't1058') AND (called_party =
N'1152') OR
(calling_party = N't1059') AND (called_party =
N'1152') OR
(calling_party = N't1060') AND (called_party = N'1152')The wild card characters supported in T-SQL are '%' and _ (underscore)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bill Farinella" <BillFarinella@.discussions.microsoft.com> wrote in message
news:A375B699-7ECF-4F4E-8DD2-F395D8B7C897@.microsoft.com...
>I am trying to write a query with a wildcard as part of it. the table that
>I
> am looking at is mostly text (they are smdr logs from my phone system) I
> currently use access to write the queries connected to SQL server. in the
> example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> would like to use a wildcard for the called_party but it does not work I
> have
> tried *,#, and a few others but no success. any ideas would be helpful.
>
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1150') OR
> (calling_party = N't1052') AND (called_party =
> N'1150') OR
> (calling_party = N't1053') AND (called_party =
> N'1150') OR
> (calling_party = N't1054') AND (called_party =
> N'1150') OR
> (calling_party = N't1055') AND (called_party =
> N'1150') OR
> (calling_party = N't1056') AND (called_party =
> N'1150') OR
> (calling_party = N't1057') AND (called_party =
> N'1150') OR
> (calling_party = N't1058') AND (called_party =
> N'1150') OR
> (calling_party = N't1059') AND (called_party =
> N'1150') OR
> (calling_party = N't1060') AND (called_party =
> N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1152') OR
> (calling_party = N't1052') AND (called_party =
> N'1152') OR
> (calling_party = N't1053') AND (called_party =
> N'1152') OR
> (calling_party = N't1054') AND (called_party =
> N'1152') OR
> (calling_party = N't1055') AND (called_party =
> N'1152') OR
> (calling_party = N't1056') AND (called_party =
> N'1152') OR
> (calling_party = N't1057') AND (called_party =
> N'1152') OR
> (calling_party = N't1058') AND (called_party =
> N'1152') OR
> (calling_party = N't1059') AND (called_party =
> N'1152') OR
> (calling_party = N't1060') AND (called_party =
> N'1152')
>|||On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:

>I am trying to write a query with a wildcard as part of it. the table that
I
>am looking at is mostly text (they are smdr logs from my phone system) I
>currently use access to write the queries connected to SQL server. in the
>example below I need to put 1150 and 1152 for each of the t10xx numbers. I
>would like to use a wildcard for the called_party but it does not work I ha
ve
>tried *,#, and a few others but no success. any ideas would be helpful.
(snip)
Hi Bill,
In this case, you might want to use IN instead of wildcards:
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
AND called_part IN (N'1150', N'1152')
Note: the ellipsis in the query above should be substituted by the full
list of valued you want to search for in calling_party.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wow, Thanks That worked great!! I wish I posted this sooner now. It could
have saved me days worth of work.
Thanks again,
Bill
"Hugo Kornelis" wrote:

> On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
>
> (snip)
> Hi Bill,
> In this case, you might want to use IN instead of wildcards:
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
> AND called_part IN (N'1150', N'1152')
> Note: the ellipsis in the query above should be substituted by the full
> list of valued you want to search for in calling_party.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Queries and wildcards

I am trying to write a query with a wildcard as part of it. the table that I
am looking at is mostly text (they are smdr logs from my phone system) I
currently use access to write the queries connected to SQL server. in the
example below I need to put 1150 and 1152 for each of the t10xx numbers. I
would like to use a wildcard for the called_party but it does not work I have
tried *,#, and a few others but no success. any ideas would be helpful.
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1150') OR
(calling_party = N't1052') AND (called_party =
N'1150') OR
(calling_party = N't1053') AND (called_party =
N'1150') OR
(calling_party = N't1054') AND (called_party =
N'1150') OR
(calling_party = N't1055') AND (called_party =
N'1150') OR
(calling_party = N't1056') AND (called_party =
N'1150') OR
(calling_party = N't1057') AND (called_party =
N'1150') OR
(calling_party = N't1058') AND (called_party =
N'1150') OR
(calling_party = N't1059') AND (called_party =
N'1150') OR
(calling_party = N't1060') AND (called_party =
N'1150') OR
(calling_party = N't1051') AND (called_party =
N'1152') OR
(calling_party = N't1052') AND (called_party =
N'1152') OR
(calling_party = N't1053') AND (called_party =
N'1152') OR
(calling_party = N't1054') AND (called_party =
N'1152') OR
(calling_party = N't1055') AND (called_party =
N'1152') OR
(calling_party = N't1056') AND (called_party =
N'1152') OR
(calling_party = N't1057') AND (called_party =
N'1152') OR
(calling_party = N't1058') AND (called_party =
N'1152') OR
(calling_party = N't1059') AND (called_party =
N'1152') OR
(calling_party = N't1060') AND (called_party = N'1152')
The wild card characters supported in T-SQL are '%' and _ (underscore)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bill Farinella" <BillFarinella@.discussions.microsoft.com> wrote in message
news:A375B699-7ECF-4F4E-8DD2-F395D8B7C897@.microsoft.com...
>I am trying to write a query with a wildcard as part of it. the table that
>I
> am looking at is mostly text (they are smdr logs from my phone system) I
> currently use access to write the queries connected to SQL server. in the
> example below I need to put 1150 and 1152 for each of the t10xx numbers. I
> would like to use a wildcard for the called_party but it does not work I
> have
> tried *,#, and a few others but no success. any ideas would be helpful.
>
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE (calling_party = N't1050') AND (called_party = N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1150') OR
> (calling_party = N't1052') AND (called_party =
> N'1150') OR
> (calling_party = N't1053') AND (called_party =
> N'1150') OR
> (calling_party = N't1054') AND (called_party =
> N'1150') OR
> (calling_party = N't1055') AND (called_party =
> N'1150') OR
> (calling_party = N't1056') AND (called_party =
> N'1150') OR
> (calling_party = N't1057') AND (called_party =
> N'1150') OR
> (calling_party = N't1058') AND (called_party =
> N'1150') OR
> (calling_party = N't1059') AND (called_party =
> N'1150') OR
> (calling_party = N't1060') AND (called_party =
> N'1150') OR
> (calling_party = N't1051') AND (called_party =
> N'1152') OR
> (calling_party = N't1052') AND (called_party =
> N'1152') OR
> (calling_party = N't1053') AND (called_party =
> N'1152') OR
> (calling_party = N't1054') AND (called_party =
> N'1152') OR
> (calling_party = N't1055') AND (called_party =
> N'1152') OR
> (calling_party = N't1056') AND (called_party =
> N'1152') OR
> (calling_party = N't1057') AND (called_party =
> N'1152') OR
> (calling_party = N't1058') AND (called_party =
> N'1152') OR
> (calling_party = N't1059') AND (called_party =
> N'1152') OR
> (calling_party = N't1060') AND (called_party =
> N'1152')
>
|||On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:

>I am trying to write a query with a wildcard as part of it. the table that I
>am looking at is mostly text (they are smdr logs from my phone system) I
>currently use access to write the queries connected to SQL server. in the
>example below I need to put 1150 and 1152 for each of the t10xx numbers. I
>would like to use a wildcard for the called_party but it does not work I have
>tried *,#, and a few others but no success. any ideas would be helpful.
(snip)
Hi Bill,
In this case, you might want to use IN instead of wildcards:
SELECT date, start_time, calling_party, called_party, rx_calls
FROM dbo.[All Phone Logs]
WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
AND called_part IN (N'1150', N'1152')
Note: the ellipsis in the query above should be substituted by the full
list of valued you want to search for in calling_party.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Wow, Thanks That worked great!! I wish I posted this sooner now. It could
have saved me days worth of work.
Thanks again,
Bill
"Hugo Kornelis" wrote:

> On Wed, 23 Nov 2005 07:30:11 -0800, Bill Farinella wrote:
> (snip)
> Hi Bill,
> In this case, you might want to use IN instead of wildcards:
> SELECT date, start_time, calling_party, called_party, rx_calls
> FROM dbo.[All Phone Logs]
> WHERE calling_party IN (N't1050', N't1051', ..., N't1060')
> AND called_part IN (N'1150', N'1152')
> Note: the ellipsis in the query above should be substituted by the full
> list of valued you want to search for in calling_party.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

queries and reporting from corporative database non IT staff

Hi! we have no Business Intelligence solution in my job and the general manager asked for a query and reports system where he (and other section managers and non IT staff) could query the corporative database (we work in healthcare, so it's mostly about patients, clinic histories, treatments) so I will propose them SQL SERVER 2005. I'll tell you my general idea and maybe you could tell me if I'm right: Through Integration Services we will load our data from the corporative database (it's Borland's InterBase) and generate a datawarehouse (now we don't have a datawarehouse) then through Reporting Services we can query this datawarehouse to generate different reports (this reports can be generated for non-technical staff, but staff who knows quite a lot about for example the different treatments that different groups of patients follow, so this staff may want to query about which patients followed which treatment in a period of time and generate by himself a report about that). And through Analysis Services we could (in a future) generate some OLAP solutions, Data Mining etc

But for the query and report system it could be enough to start with Integration Services and Reporting Services? it's very important for this system to work efficiently in terms of time: the non-technical staff generates the query and in seconds receives the report result... some of this non IT staff who will use the system knows exactly which information we house in each InterBase table, so through this system they would like to generate their own queries and reports (this is a different level of non-IT staff who is in between a Medical doctor and a Software engineer, this staff does have some technical background specially in SQL.

So my idea is to have an SQL SERVER 2005 to which this staff gets connected via LAN (all this system runs locally in one corporative place, will not run through Internet) feeded by the InterBase corporative database and delivering efficiently queries and reports... is this possible? could I test this using the trial free 6 months version? and after that if everything's working fine how much will I have to pay for 15 persons to access the SQL SERVER 2005 for keeping using the system?

This sounds like a good solution. I believe there are three stages of data presentation at a company: Data storage and retrieval, a good reporting strategy, and then Business Intelligence. SQL Server 2005 contains all the tools you need to do all three. The engine (and good application programming) handles number one, SSIS and Reporting Services handles number two, and the new Analysis Services handles number three.

You can read more about what I think on this here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=159&rl=1

If this answers your question, make sure to mark it "answered" so that others can quickly locate it.

Buck Woody
http://www.buckwoody.com

|||

Thanks!! just one question to get the general idea: in the Data storage and retrieval part is when you create the datawarehouse? so the original data is somehow "rearranged" for the Reporting Services to query the data more efficiently..

I don't understand how the data is queried efficiently

|||

Good question - if you'll check out my series of articles I referenced in the last post, you'll see that the data warehouse is a different structure than your data store. I think you can get the reports you're looking for out of the ODS layer I talk about. Check out those articles and I think you'll find what you're looking for.

Buck

Queried parameters

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

Qualification of system tables in queries

I found an "undocumented" stored procedure that returns the size of the
tempdb called sp_tempdbspace - this stored procedure queries two different
tables to compute the allocated size of tempdb as well as figuring out how
much of that size is being used.
My question is about the queries used in this stored procedure - the query
that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but the
query that accesses sysindexes qualifies the table as "tempdb..sysindexes".
I am trying to figure out why these two queries use different qualifications
for these system tables - I suspect that there is a reason for this
difference other than cosmetic or stylistic reasons. I have tried to do
research through web searches, but have not really found anything that looks
to be conclusive.
Any answers or pointers in resolving this would be appreciated.My bet is that it's just a different style (maybe two different people
worked on the query, or maybe the same person at different times). Since
the stored procedure is always called with dbo either implicitly or
explicitly, it doesn't make a whole lot of difference. Especially if it is
just a cosmetic issue, in an undocumented procedure, I wouldn't worry about
it... I would worry more about relying on this stored procedure
(undocumented stored procedures can be changed, or dropped from the product
altogether, without any notice).
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
Perhaps they were written by two different programmers. Best practice is to
fully qualify the name. And **NEVER** rely on any script written by MS or
shipped with MS to be a good example of best practices.|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons.
AFAIK, the best practice is to always owner-qualify objects. This can
reduce stored procedure recompilations and avoid ambiguity.
Although there could be a special case for system object access, the more
likely reason for the omission of 'dbo' here is a simple oversight.
Hope this helps.
Dan Guzman
SQL Server MVP
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>

Q: Trigger - reference to fields in "inserted" ?

Hi all,

I have a ranking system where I wish to update the ranking every time a result is reported. Performance is no issue what-so-ever. More specifically, two players are to devide their points after each series of games to reflect the fraction of over-all games that each player have won.

I've written the trigger below, but Visual Web Developer 2005 Express (SQL Server 2005 Express) complains about the references to the 'inserted'-table.

I find it very difficult to transform the code below to something that looks like the examples found in documentation.

Could someone get me started in the right direction?

Thanks in advance,

Anders

create trigger result_insertonresult

after insert as

begin

declare@.won1as int

declare@.won2as int

declare@.oldRank1as float

declare@.oldRank2as float

declare@.oldranksumas float

select@.won1 =sum(wongames1)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.won2 =sum(wongames2)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.oldrank1 = RankfromRankingInfowherememberid = inserted.playerid1

select@.oldrank2 = RankfromRankingInfowherememberid = inserted.playerid2

set@.oldranksum = @.oldrank1 + @.oldrank2

updaterankingInfosetRank = @.won1 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player1

updaterankingInfosetRank = @.won2 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player2

end

Hello Anders,

tha fact is theinserted table is but a table, so you need JOIN-ing to it as you would for any standard table...

Hope this helps. -LV

Q: report statements

Hello,
We have a dental practice that uses SQL server as a database and a practice
management system. I need develop a report by using Reporting Services to be
able to print statements per patient. Is there any reports already developed
for healthcare companies so that I can check and see what exactly I need to
include and how to do that?
Thanks,doesn't have specific health care reports, but maybe you might get some ideas.
http://www.microsoft.com/sql/reporting/downloads/default.asp
"JIM.H." wrote:
> Hello,
> We have a dental practice that uses SQL server as a database and a practice
> management system. I need develop a report by using Reporting Services to be
> able to print statements per patient. Is there any reports already developed
> for healthcare companies so that I can check and see what exactly I need to
> include and how to do that?
> Thanks,
>