copy database: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

How to create sql for retrieving 10 records per time in .NET

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.
ThanksAs a great man once said: Huh?

I'm sure that your posting made sense to you, but I don't even have a clue what you meant. Could you try to repost and fill in a few of the details that I'm missing? I'd like to help you if I could, but at the moment I don't understand enough of what you want to have a chance!

-PatP|||Hi,

as Pat said, this isn't very clear and you really should repost. I just want to comment on what you may need to consider mentioning.

Almost all databases are used by multiple users at the same time. Selecting the 10 first records in a particular order makes perfect sense, as does selecting record 11 to 20 of that same recordset. But selecting 10 and then (at a later time) selecting "the ten next" doesn't have any clear meaning since the data may well have changed in the meantime. So perhaps it's a good idea to clarify how you want your system to deal with the different change scenarios: records may have been added, removed or updated, or any mix of the three between the two queries.

One way to "page in the database":
Initialize:
- establish a single orderby column for your query (if you have several columns, combine them into one column yielding equivalent sort order)
- select the top 10 records
- persist the value of the orderby column for the last record. In ASP.NET, use ViewState for this.

Each round trip:
- use the value of the last record in the previous result set to select the next ten:
SELECT TOP 10 ... WHERE ... AND ordCol > @.lastValue ORDER BY ordCol
- persist the last value again

Of course, if you require to page the other way (previous page) you must also persist the value of the first record. Furthermore, since there's no BOTTOM keyword to mirror TOP, you must use reverse sort order (otherwise, if you did SELECT TOP 10 ... WHERE ordCol < @.firstVal, the first page would be returned even if you tried to get the previous one from, say, page 5). Since you still want to display in-order, fix this by selecting into a temp table and then select in-order from that one:

SELECT TOP 10 INTO #page ... WHERE ... AND ordCol < @.firstVal ORDER BY ordCol DESC
SELECT * FROM #page ORDER BY ordCol

I hope this helps!

Dag|||Or you could just set-up paging in a datagrid...|||Huh?

Just a guess...

You want to do paging

http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx

Make sure to say hi to Jeff for me
__________________|||I meant that you could use a .NET DataGrid control that allows you to view a recordset one page at a time. You can define how many records you want displayed on each page. Of course this deals with paging at the Application level - don't know if that's what he wants but just thought I'd mention it.

I don't have a clue about paging at the SQL Server level...:-)|||Sorry for unclear question. Yes, what i want to do is paging.
Brett: The URL http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx is unavailable
mmcdonald: Thanks for your suggestion but the user don't want the result be shown in datagrid :(

Monday, March 26, 2012

how to create key time column and key column for a case table and a nested table for time series

Hi, all experts here,

Thanks for your kind attention.

I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Can you please clarify if you're creating an OLAP mining model or a relational mining model?

Thanks

|||

Hi, Shuvro,

At the moment I am trying a create a relational mining model.

But it will be brilliant to hear from you for both how to create an OLAP mining model as well using case table and nested table with Time Series Algorithm.

Thanks and I am looking forward to hearing from you further for your advices.

With best regards,

Yours sincerely,

|||

Can you please post the schema of your case and nested table for the relational scenario?

For the OLAP mining model, the design would be on the same lines as the relational, but instead using the dimension attribute and the measure. The TIME KEY usually can be selected from your date dimension, depending on the granularity of your data, but you'll have to do some work to make it correctly sortable. (e.g. Month names are not a valid time key).

|||

You can mine just the fact table and ignore the date table. The fact table needs to have the date value anyway. Your model would look something like this

CREATE MINING STRUCTURE TS_Structure

(

[Date] LONG KEY TIME,

[Region] TEXT KEY,

Value1 DOUBLE CONTINUOUS PREDICT,

Value2 DOUBLE CONTINUOUS PREDICT

)

ALTER MINING STRUCTURE ADD MINING MODEL TS_Model

(

[Date],

[Region],

Value1 PREDICT,

Value2 PREDICT

) USING Microsoft_Time_Series

When using the tools, you would simply mark your source table as the "Case" table (not nested) and then mark both the [Date] column and the [Region] column as keys. The tools will figure it out.|||

Hi, Jamie,

Thanks for the advices.

With best regards,

Yours sincerely,

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql

How to Create Index

How to create Database link in MsSqlServer to Oracle8i

Hi All,
Can somebody please help me, how to create database link to Oracle from MsSqlServer200.

Ex:
If I want to view data in both databases at a time then query would be like the following:

dblink name : SqltoOraclelink
table Name : EMP (this could be available in both the databases)

query in MsSqlServer200 EnterpriseManger:
select empname, empno from EMP msemp, emp@.SqltoOracle oraemp where msemp.empname = oraemp.empname

Then this query results the rows from both the databases where empname is same in both the tables

My question is: How can I create this Database link "SqltoOraclelink" as I can do the same thing in Oracle.

As this is in urgency, it would be greate if somebody help us asap.

Thanks in Anticipation,
Regards,
RKThis article should help you out:
http://www.databasejournal.com/features/mssql/article.php/3290801

Wednesday, March 21, 2012

How to create an event driven shared schedule

We have a number of jobs that run nightly. Typically these complete long before the shared schedule runs. However, when they do not complete in time, we need to stop the shared schedule from running. How is this accomplished?

I have seen that it is possible to create event driven subscriptions but am not sure this accomplishes the same thing. Basically, I need to pause the shared schedule if a particular job has not completed successfully. Then, I need to run the shared schedule once the job has completed. None of the reports we are running are snapshot based. Would moving to this method resolve the issue? Any help is greatly appreciated.

I had the same issue. What I did was kick off the subscription SQL Agent job at the end of my ETL job. That way I know the import will always be done before the reports are published.

BobP

|||

That will have to be the solution we employ as well, at least until a better solution can be found.

Thanks!

Monday, March 19, 2012

How to create a No Records Found message

We have a report that we use to verify that two our systems are in
sync. The only time anyone needs to act on the report would be if
any
results were returned. I'm looking for a best practice that would
allow me to insert a "No Records Found" message if the results were
negative rather than simply just displaying a blank report.
Thanks,On Dec 5, 4:38 pm, mbailey256 <mbaileyst...@.yahoo.com> wrote:
> We have a report that we use to verify that two our systems are in
> sync. The only time anyone needs to act on the report would be if
> any
> results were returned. I'm looking for a best practice that would
> allow me to insert a "No Records Found" message if the results were
> negative rather than simply just displaying a blank report.
> Thanks,
As far as I can tell, only Charts and Subreport objects have a
"NoRows" Property that is used to display text when their associated
DataSets have no rows.
But, since a No Records Found is equivalent to having no rows of data,
you can explicitly try counting the rows. Try doing a TextBox at the
very top of your report with a Visible Expression of:
= ( Count( Fields!OneThatWeKnowIsNotNull.Value, "DataSet" ) = 0
When the Count is zero, the textbox is Visible, and anything in the
Text Expression will be seen.
-- Scott|||On Dec 5, 4:10 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 5, 4:38 pm, mbailey256 <mbaileyst...@.yahoo.com> wrote:
> > We have a report that we use to verify that two our systems are in
> > sync. The only time anyone needs to act on the report would be if
> > any
> > results were returned. I'm looking for a best practice that would
> > allow me to insert a "No Records Found" message if the results were
> > negative rather than simply just displaying a blank report.
> > Thanks,
> As far as I can tell, only Charts and Subreport objects have a
> "NoRows" Property that is used to display text when their associated
> DataSets have no rows.
> But, since a No Records Found is equivalent to having no rows of data,
> you can explicitly try counting the rows. Try doing a TextBox at the
> very top of your report with a Visible Expression of:
> = ( Count( Fields!OneThatWeKnowIsNotNull.Value, "DataSet" ) = 0
> When the Count is zero, the textbox is Visible, and anything in the
> Text Expression will be seen.
> -- Scott
Thanks Scott. I did find the NoRow property for the regular table,
but for my report with multiple tables, your solution fit perfectly!!!

Friday, March 9, 2012

How to create a datagrid for two no relationship tables

Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :

ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID

Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID

May I know is there solution for it?

Thank you very much!There is a relationship:WHERE A.ID = B.ID.

Does the query below run and return what you are expecting?


Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)as SomeName
From A inner join B ON (A.ID = B.ID)
Group by A.ID

|||No, the result set will be wrong because there is no relationship for A and B. Only some records for ID are the same. If I use the SQL query as you mention :

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) as SomeName

From A inner join B ON (A.ID = B.ID)

Group by A.ID

Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) will be always the minus because A will returns correct records, but B records are more than the correct ones if there is only one B.ID= A.ID. For examples, when ID = 2, there are 3 records for A as following and there is 1 record only for Table B when B.ID= 2. But you will see the query returns wrong result for B, there are 2 more. So the result for 'Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)' equal to 200, but in fact it should be 900.
A Table | B Table
ID A.APoints1 A.APoints2| Qty BPoints
2 100 200 1 300
2 100 300 1 300
2 100 400 1 300

That is why I can't directly use SQL query for it. Is there any way to use Dataset for it?|||But there is a relationship between A and B -- the ID.

You should be able to use a subquery and a LEFT OUTER JOIN to solve your problem within your query itself:


SELECT
 C.ID,
 C.SumAPoints - (ISNULL(B.Qty,0) * ISNULL(B.BPoints,0)) AS SomeName
FROM
(
SELECT
 A.ID,
 SUM(A.APoints1 + A.APoints2) AS SumAPoints
FROM
 A
GROUP BY
 A.ID
) AS C
LEFT OUTER JOIN B ON C.ID = B.ID

Terri|||Hi, if I use the SQL query as above, only C.SumAPoints is correct, but there are more than one records for each ID if the there are more than one records in Table B for the same ID. I need only one record for each ID as following :

ID Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)
1 10
2 20
3 40

but from you query, I get the result as :

ID SomeName
1 40
1 30
1 20 <-- Only this record is what I need to display

Do you think that I need to remove myself for all those records I don't need but inside the query from your script?

How to create a button in Reports

Hello every one,
I am using reports 10g.In the Run time parameter form after given the
values for the parameters, i want to click a button so that the report
can run. we already have an option in the menu bar of the Run time
parameter form to run the report but our customers specfically needs
the button option. So how to create a Button in the Run time parameter
form?
Can anyone please help me on this,
Thanks
Balaji.On Nov 6, 12:27 am, rampal...@.gmail.com wrote:
> Hello every one,
> I am using reports 10g.In the Run time parameter form after given the
> values for the parameters, i want to click a button so that the report
> can run. we already have an option in the menu bar of the Run time
> parameter form to run the report but our customers specfically needs
> the button option. So how to create a Button in the Run time parameter
> form?
> Can anyone please help me on this,
> Thanks
> Balaji.
If you want to mix HTML Forms with ReportingServices Reports, I
suggest using ASP.NET. This way:
1. the page renders on your IIS server and is callable from a URL
2. you can have an HTML form with Inputs & Submit button
3. the Submit posts back to the IIS server which returns a page with
a RS Object rendered as an IFRAME in the HTML (ReportViewer I think it
is called)
4. The IIS takes the parameters from the URL using Request objects,
then passes those as Parameters to the ReportViewer object, which then
calls the Reporting Services RDL file, which renders in the web page.
Down side is that the report cannot be deployed to the Reporting
Services Server, but can be Navigated to via a URL to the ASP.NET
folder on your web server.
-- Scott

Friday, March 30, 2012

How to create sql for retrieving 10 records per time in .NET

Monday, March 26, 2012

how to create key time column and key column for a case table and a nested table for time series

How to Create Index

How to Create Index

How to Create Index

How to create Database link in MsSqlServer to Oracle8i

Wednesday, March 21, 2012

How to create an event driven shared schedule

Monday, March 19, 2012

How to create a No Records Found message

Friday, March 9, 2012

How to create a datagrid for two no relationship tables

How to create a button in Reports

copy database

Blog Archive

About Me