Fetching Continuous Dates

Jun 20, 2008

hai,

set dateformat dmy
create table tbl_sampemptable
(
employeeid int,
StartDate datetime
)
declare @employeeid int
set @employeeid=1
declare @startdate datetime,@enddate datetime
while(@employeeid<=1000)
begin
 set @startdate='01/05/2008'
 set @enddate='31/05/2008'
 while(@startdate<=@enddate)
 begin
      if(@employeeid<>1 and @startdate<>'02/05/2008')
      insert into tbl_sampemptable values (@employeeid,@startdate)
             else if(@employeeid=1)
      insert into tbl_sampemptable values (@employeeid,@startdate)z
      set @startdate=dateadd(day,1,@startdate)
 end
set @employeeid=@employeeid+1
end
select * from tbl_sampemptable
drop table tbl_sampemptable
set dateformat mdy
 
 
i have to select records depending on @count parameter to this table.Depending on this parameter value it should fetch sequential dates.For example
if @count=2then result should be like this,
 
EmployeeID                 FromDate                 ToDate
1                                 01/05/2008               02/05/2008 
1                                 03/05/2008               04/05/2008
.
.
2                                 03/05/2008               04/05/2008 //note that here 01/05/2008 is  not  selected
                                                                                     because 02/05/2008 is missing
2                                 05/05/2008               06/05/2008
.
.
3                                03/05/2008               04/05/2008 //note that here 01/05/2008 is  not  selected
                                                                                     because 02/05/2008 is missing
3                                05/05/2008               06/05/2008
.
.
 
if @count=3 then result should be like this,
 
EmployeeID                 FromDate                 ToDate
1                                 01/05/2008               03/05/2008 
1                                 04/05/2008               06/05/2008
.
.
2                                 03/05/2008               05/05/2008 //note that here 01/05/2008 is  not  selected
                                                                                     because 02/05/2008 is missing
2                                 06/05/2008               08/05/2008
.
.
3                                03/05/2008               05/05/2008 //note that here 01/05/2008 is  not  selected
                                                                                     because 02/05/2008 is missing
3                                06/05/2008               08/05/2008
.
. how can i do this.please help me.thanks in advance

View 8 Replies


ADVERTISEMENT

Alert If Cpu Is Over 85% For 1 Min Continuous

May 26, 2008

Hi,

does anyone know if it is possible to set an alert like that ?
if cpu is over 85% and stay for more then 1min continuous over 85%then alert.

THX

View 15 Replies View Related

Continuous Merge Replication

Mar 7, 2008

I am fairly new to SQL 2005 and have taken over a migration project from 2000 to 2005 and one of our scheduled jobs seem to run forever, but do not have errors. This did not happen in the past so I was wondering if the agent settings for Replication Merge has the -Continuous parameter will the job ever complete or does it really run "continuously"?

View 1 Replies View Related

Get Minimum Day In A Continuous Series

Jun 6, 2006

Hi,

l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?




Date
-----
20/5
19/5
18/5
17/5
16/5
15/5
14/5
09/5
07/5
06/5
05/5

View 7 Replies View Related

Getting Data In Continuous Time

Nov 22, 2006

Hi,

The time column type is SMALLDATETIME. Data is inserted every minute, but it isn't occured always.

When I display the contents, I want show every time(minute). If a specific time is skipped, it should be display as data zero.

Someone help?

View 1 Replies View Related

Predicting A Continuous Value (run Minutes)

Oct 23, 2007

Hello,

I have a question regarding whether or not Data Mining can be utilized in a specific problem I have to solve.

Situation: I€™m going to simplify the problem by explaining it in terms of a €œpizza manufacturer€?. Suppose I wanted to predict the run minutes + downtime minutes (I use these to get an hourly rate: Pizzas/(run hrs + delay hrs) = Pizzas per hour) by looking at a set of input properties.


My properties could be something like the following:
# of Toppings
# of Special Pricing Stickers
Cardboard Box Indicator
Case Indicator (0 represents auto-casing, 1 represents putting in case by hand)
Machine Type (0 or 1€¦ 0 represents an older €“slower machine, 1 is newer)
Quantity of Run
(there could be up to 15 other properties that may or may not impact our rate)

Measured Values:
Run Minutes
Delay (down) minutes

Steps I€™ve Done So Far:
I€™ve created a couple different data mining models for this as I was unsure which one(s) to use. I checked the lift chart while feeding back in the original data set and my scatter plot appeared fairly inaccurate.

I've attempted to use Excel to create a linear regression, however my r squared value was always around .30. I decided to try to use SQL Server Data Mining to see if it could be something to help predict our accuracy better than a linear formula.


I've played with a couple different algorithms in Data Mining, and it appeared that none of them did exceptionally well with prediction. I even checked the lift chart using the same table as I used to train the model.

What algorithm(s) might work the best?
Can I reasonably expect a prediction within a fairly strict tolerance (I'm guessing the answer to this is: "yes, if your source data represents a consistent pattern")?
How can I best utilize Data Mining to give an answer like "historically, your run rate has been between these 2 values with a probability of X". I'm thinking I can utilize the predictprobability and stdev to some extent.

Any suggestions would be greatly appreciated.


If anyone needs further clarification, please let me know.

Thank you.

Regards,

Dan

View 2 Replies View Related

Continuous Attribute &&amp; Complexity_Penalty

Apr 27, 2007

I am having one question about discretization of continous attributes
values. How does it work? I need this information for my thesis. I have a
continous attribut, namely SKS, with range 0-20. When I use Microsoft
Decision Tree algorithm, this attribut split in SKS <= 18 and SKS > 18. I
want to know how does it find 18 as a number to split not the other.

One question again about Microsoft Decision Tree algorithm, about
Complexity_Penalty parameter. How does it affect the algorithm? For example,
if I set this value=0.1 what does it mean and how does it correspond with
growth tree?

Thanks a lot before for your kindness to answer my questions.. :-)

View 1 Replies View Related

Continuous Variable Prediction

Sep 19, 2007



Hi All,
I was wondering if there was a way to specify a range when training a model to predict continuous variables. For instance, the predicted variable can only have a range of 1 - 10.

Thanks

View 1 Replies View Related

Setting Up Continuous Trace File

Jan 29, 2002

Does anyone know exactly how to create a trace that runs continuously on a server and writes the data to a table? Now I know how to create a trace file with the profiler, but I want something set up so that I don't have to have the profiler running on the server all the time. As well as soemthing that will restart itself if the server is rebooted. I have been looking at these x(xp_trace.*) procedures. Is this the way to do it?

I have to trap login information in a table and have a scheduled job that runs once a month and look for specific data in the table and send out e-mails based on certain values.

I have written the procedure which does this I just need to know how to set up the trace so it runs in the background continuously.

Can anyone help?

Thanks............

View 1 Replies View Related

Replace All Integers With Continuous 6 Or More Occurrences With X

Oct 4, 2012

I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.

create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO

EXPECTED RESULT:

1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS

drop table t1

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

View 9 Replies View Related

Continuous Data Pump Using SSIS

Mar 21, 2008

Hi...My first post.

I'm currently creating a SSIS package that takes data from 3 unique databases. A SQL DB, FoxPro DB, and an Oracle DB. The data is pulled, cleansed and put into a single SQL 2005 table. The data is then pulled from this table every 15 minutes, formated in a given specification and uploaded to an ftp site. This part is done. My question is this:

This package needs to run around the clock, non-stop. How can package be set up to do this? It needs to pull data from the 3 DBs and put it in the common table, wait 15 minutes and do it again. Wait 15 more mintues and do it again. And so forth. A problem I'm having is I don't see a way to set up a SSIS package so that it runs around the clock.

On same premise, I have another issue. When I try to take data from the common table and there is nothing there, it causes an error. Is there some way that you can run a test like


SELECT * FROM _table_ WHERE is_sent = 0

if results == 0
{
wait 15 minutes and test again.
}
else if

{
write flat file, wait 15 minutes.
}

This has to be done in the Control Flow scope, so I can't use a conditional split. This is a pretty big deal as this needs to run around the clock. Thank you in advance for your assistance.

View 4 Replies View Related

Need Help Keeping Continuous IDs Using Merge Replication

Apr 11, 2007

I was wondering if anybody knows how to solve this problem. Here's the setup.



There is an ASP.NET application running on a local web server at the customer's location, it currently uses a MSDE backend database. There is a copy of the application on the customer's webhosting company so it can be accessed from outside the customer's location it is running on a full version of SQL Server 2000. We have this setup because the customer's ISP is not very reliable and the customer needs to be able to use the application even when their web connection is down. It is also used from outside their location by sales people and management and remote offices. The problem is we want to keep both databases synchronized together. We had been using Merge Replication which was working fine until the local ID jumped because it had run out of allocated identities. This causes a problem for their accounting because now there is a gap in the document's numbers.



Is there a way to have the identity field (or a generated document number) to remain continuous and unique across both databases? This needs to also work if one of the databases were to go down for a time or lose connectivity between the two servers. I'm looking for any option. We also have the option of upgrading the application to SQL Server 2005 if needed. Any ideas are appreciated.



Thanks,

Seth

View 6 Replies View Related

Continuous Merge Replication - Event Log

Aug 22, 2006

Hi

I have set up merge replication and it works nicely.

I have set it up to work continuously, because I thought that if it can't find the subscriber or is offline then that's fine it will just sync again when it's back on line.

This is true

BUT it keeps throwing lots of messages into the event log to tell me the merge has failed.

SO

a. Can i just turn off the error reporting

or

b. How can I get it to sync this way automatically on connection without the error messages



thanks

ICW

View 1 Replies View Related

Continuous Nonstop Pumping Of Data

Mar 4, 2007

I have one data pump in a series that was pumping in too many records. Doing an independent query of the source table, I found there was about 140,000 records. My pump uses a variable for the source query, nothing fancy just a simple SELECT * FROM table WHERE DateField > '4/6/2006 12:00:00AM'. The Destination is local on the SQL Server and is set by a variable, and does a fast load. When I went away and checked in BIDS while it was running (the data flow tab where you can see the record count) it was at 28,000,000 and still going!

Any ideas what could be causing this? As I say there are only 140,000 records and no joins in the query--is this a bug someone has run into before?

Thanks,

Kayda

View 2 Replies View Related

Continuous Predictable Problem In MDT Model

Dec 18, 2007

It seems i face a problem with the Microsoft Decision Trees model when i have a predictable variable that is continuous.
I have created the whole model according to the AdventureWorks tutorial (and it informs me that the same procedure is followed with a continuous variable) and i have flagged the variable as continuous. Even though everything seems be going well, the results i get are not correct (after a cross check with another project already done and checked).
Is there something i am missing or i skipped while creating the model? Any suggestions that may help me are appreciated
Thank you in advance

View 1 Replies View Related

Find Continuous Date Range In Sqlserver2005

Jan 17, 2008

 hi all
how to find the continuous date from the given date range in sqlserver 2005
e.g.
2007-01-27 and 2007-02-02 and output should be
2007-01-27 2007-01-282007-01-292007-01-302007-01-312007-02-012007-02-02any suggestion?
 
 
 
 
 

View 1 Replies View Related

Transact SQL :: Finding Continuous Members Script?

Aug 11, 2015

I am trying to find the members who are having monthstartdate continuously for 11 months ;

here in my example 123 wont have monthstartdate continuesly for 11 months it has break for february '2014-02-01'; where as 222 and 223 has continus 11 months , so i need to pull such members .finding out the members continuesly(enrolled) having 11 months.

Below is the sample data i am referring.

memid       MonthStartDate
123 2014-01-01
123 2014-03-01
123 2014-04-01
123 2014-05-01
123 2014-06-01
123 2014-07-01

[code]....

View 28 Replies View Related

DB Engine :: Timeout Exception After 15-20 Hours Of Continuous Run

Apr 23, 2015

I am getting SQL Time out exception after long run of 15-20 Hours, Please find the attachment for more details. My SQL queries  are not taking much time for execution , simple Update /Insert statements to local database.

Observed Activity manager also, looks fine. One more application connected to same database (Insert statements) works fine. In connection string I have modified Connection string ConnectTimeout = 2147483647; (max)

View 4 Replies View Related

Strange: Continuous Login Failure Errors

Jun 5, 2007

I have SQL 2000 Server that had a database called ABC and it has been moved to another server on 5-15-2007
I kept ABC database in Read_only mode for few days (just in case) on old server and finally dropped it on 5-20-2007 and I think I forgot to drop the associated logins. I started seeing login failure for user 'xyz' in error logs
When I first noticed the login failure error in SQL Server log for login xyz, I deleted xyz login but it did not stop the errors.

I have been trying from then and no luck in identifying the cause/ resolving this issue.
I have ran SQL profiler trace and caught the user hostnames, NTusername in few cases and Application Name and contacted the Application owner & user (who are in the trace) to stop windows service/ schedule jobs..anything that is pointing to old server but the bad luck is they are not aware of anything running or pointing to old server. The worst part is the user whose hostnames are shown in the trace have never used ABC database and do not have any idea.

Here is what I found in the profiler trace:
TextData LoginName NTUserName HostName ApplicationName
Login failed for user 'xyz' xyz AB00007 WAB000007 Microsoft (r) Windows Script Host

Today I have created the xyz login in the server and assigned model database with reader permission to see if log some different error but nothing new (the same login failure error)
Finally, I had no solution other than restoring the ABC database back to my old server and set it in to Read_Only mode to stop these errors and Now I see the login 'xyz' firing the query against the database

* Any hint or pointers why this is happening and any possible solution on this?

View 1 Replies View Related

How Handle Continuous Until Cancelled Date Field?

Aug 9, 2006

We have a table in our legacy database system representing health insurance polices. The customer can, and usually does, renew the policy after 12 months. The legacy database uses the renewal string "99/99/9999" to signify "continuous until cancelled", in other words, "forever", or until cancelled

We need to convert this legacy table into a sql 2005 table, which supports the concept of "forever". But how can we do this? ("99/99/999" is not a valid sql date type and we don't want to use varchar for dates.)





TIA,



barkingdog



View 1 Replies View Related

SQL Server 2012 :: Creating Continuous Primary Key Integer Value

Jun 26, 2014

Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.

Table name: SitePage

Column DataType
--------- -----------

PageID BigINT
PageName nchar(10)

Query to insert new record

DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag =0)
BEGIN
BEGIN TRY

[Code] ....

We don't want to use auto increment integer value for primary key because of the following reason

[URL] .....

We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables

We can't do trace flag 272

View 9 Replies View Related

Reporting Services :: Print On Continuous Paper On SSRS

Aug 21, 2015

Paper is 21cm x 9cmPrinter is Epson LX-300..When I set this paper size, SSRS turns orientation to landscape and prints as if clockwise right rotated!I tried creating custom paper on print server options without success. I also tried setting the same paper size in Report Builder and Print Server but failed again.

View 3 Replies View Related

Nested Fetching

May 21, 2008

Hi all!I'm trying to write a T-SQL statement that will allow me to do a maintenance for all user table for all databases on my server.This is what I got so far :DECLARE @cStatement varchar(255)DECLARE @dStatement varchar(255)DECLARE T_database CURSOR FOR SELECT '[' + CONVERT(varchar(64),name) + ']' FROM master.dbo.sysdatabases WHERE dbid>6SET nocount ONOPEN T_databaseFETCH NEXT FROM T_database INTO @dStatement WHILE (@@FETCH_STATUS <> -1)begin-- LOOP IMBRIQUÉ POUR PASSER AU TRAVERS DE CHAQUES ELEMENTS-- DE LA BASE DE DONNÉES ACTUELLE.EXEC ('DECLARE T_cursor CURSOR FOR SELECT ''UPDATE STATISTICS ['' + CONVERT(varchar(64),name) + '']'' FROM ' + @dStatement + '.dbo.sysobjects WHERE type = ''U''')OPEN T_cursorFETCH NEXT FROM T_curosr INTO @cStatementWHILE (@@FETCH_STATUS <> -1)beginPRINT(@cStatement)FETCH NEXT FROM T_cursor INTO @cStatementendDEALLOCATE T_cursorFETCH NEXT FROM T_database INTO @dStatement endDEALLOCATE T_databaseBut I get a sh**t load of errors :Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Please help!!! :beer:

View 14 Replies View Related

Fetching Of Data

Apr 14, 2007

hi,

I would like to know that, I have three instances of the same database at three different servers and I am trying to fetch the data from the select query. "select * from table_name"



I would like to know, whether the order of rows fetched by this query will be different on different servers of sql server or the same order of rows will be fetched.



For me the output is coming different on each server database with he same query . Pls let me know, is there any default order by or it takes it randomly.



Thanks

Gaurav Gupta



View 1 Replies View Related

Fetching An Id To Use In Another Task

Jan 24, 2007

Hi there,

i have a task that has a ole-db destination. it inserts some data to a table. it's always 1 record that is gonna be inserted.after that the task is finished. however i want to use that same record in the followup-task. is there a way to retrieve the id of that specific record.

Please note that the id of the record is an int-identity from sql-server itself.

at the moment i'm fiddling with "remembering" the inserted data and hopefully it will be a 100%match in the next task, but i have a dark feeling it's not 100%.

is there a common way how to approach this?

View 1 Replies View Related

SQL_NO_DATA When Fetching

May 6, 2008

Hi all,

While I was testing our application installation on Vista, I ran across this problem:

When a program on Vista is executing a SPROC is a SQL Server on Vista (actually same machine), the SQLFetch(m_hstmt) returns a SQL_NO_DATA.

When the exact same program is executed on XP SP2 client against the same Vista based SQL Server, it correctly returns the rows.

The build of the program was done on a MDAC 2.8 XP SP2. When I tried to install 2.8 on Vista machine it seemed to have failed.

Using DSN vs. DSNless connection string made no difference. Nothing of intest in SQL Logs either.

Any clues?

View 3 Replies View Related

Data Fetching... The Right Way.

Oct 12, 2007

Hi,

I'm currently developing an application using C# and SQL Server Express, in which one of the functionalities is to allow the user to browse through the existing products, wether by browsing one by one (next, previous, first, last) or performing a search (by product name, id, etc.). I'm currently using the default DataSource and TableAdapter aproach, but I found out that when I execute the TableAdapter.Fill() method, all the data in the table is fetched... what with 10,000 records it takes a bit to perform, specially on slower connections.

So my doubt is, what would be the best aproach to solve this problem? I'm considering loading first position, and then when the user presses the next button I'd fetch second position, etc, this using ROW_NUMBER(). I could even cache every other 10 records or something like that. Since it is possible to know any records row number (as long as the records are ordered) it would even work when the user performs a search and goes directly into row number 5000 for instance.

Would this aproach be a good practice, because that's all I want, to do things the right way? Thank you for your time

EDIT: I've noticed that when creating a SqlDataReader there is the option to provide a CommandBehavior, but I'm not quite undestanding how this could be used, would some tweaking in this area do the job?

View 3 Replies View Related

Fetching Data From SQL Server

May 10, 2008

Hi,
 I have a small Problem.
Lets say i have 6 rows in a table (two column month and MonthCost )naming jan,feb,mar,apr,may.jun and all of them have some data. but while displaying in Ui i need to show all the months from Jan to december and july to december with values as something, say 0
so the values displayed in gridview sholud be like this
Month MontCost 
Jan          1
Feb         1
Mar         1
.
.
.
Jul          0
Dec        0
. I tried it can be done using a temp table in db , but think of a scenaio where the i want to display for Number of times( 1 to 12 * 50). I don't want to  hit performance and i am uisng sql server 2000.
 Any hints or suggestions are welcomed.

View 7 Replies View Related

Error In Fetching Data

Mar 15, 2006

hello i'm using visual studio 2005 and asp.net 2 i have an error in the code that fetching data from databse in grid here is the code :Dim con As SqlConnection        con = New SqlConnection("Data Source=local;AttachDbFilename='D:New Folderhorushorus.mdf';Integrated Security=True")        Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM(dbo.students)ORDER BY RAND(CONVERT(varbinary(4), NEWID()))")        Dim cd As SqlDataAdapter        cd = New SqlDataAdapter(cmd, con)        Dim ds As New DataSet()        cd.Fill(ds, "students")        grid.DataSource = ds.Tables("students").DefaultView        grid.DataBind()that code is selecting random records from the database and showing it in grid the error is in the cd.fill(ds,"students") line so can any one help me in that problem.thanks       

View 14 Replies View Related

Fetching Data In A Datatable

Apr 21, 2006

hello, i'm using asp.net 2 with VB, i have a table in my db named 'exams' and i have 6 columns in this table one for question number and one for the question and 3 columns for 3 answers and a last column for the right answer, now in my page i want to show 7 questions and  each  question has 3 answers that i can choose between them i want the question appear in datatable and the answers in radiolist i have a little code for that but it shows an error it says "no row at position 1" so hope u guys can help : Dim con As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|aspnetdb.mdf;Integrated Security=True;User Instance=True")        Dim a As New ArrayList(8)        Dim i As New Integer        i = 0        Dim d As New DataSet()        Dim cmd As New SqlDataAdapter("select top(7) question from exams", con)        cmd.Fill(d, "exams")        a.Add(d.Tables.Item(0, 1))        i = i + 1        Dim ta As New DataTable        ta.Rows(i).Item(0).text = d.Tables(0).Rows(0)(i)and by the way this code is for the questions i still dont have the code for the answers that will appear in a radiolist hope u can help .thanks

View 3 Replies View Related

Fetching Record From Table

Sep 17, 2007

Hi,
I am facing a peculier problem. I have 3 records in my table. when i am trying to fetch the top 2 its able to fetch the records. but when trying fro moire than 2 (say top 3 or just the *) it say time out. can any one help me out in this regard.


Below is the error that I get when tryong to open the record from SSMS

SQL Execution Error.

Executed SQL statement: SELECT Transaction_ID, tril_gid, WorkGroup_ID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, LCID FROM Symp_TransactionHeader
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---------------------------
OK Help
---------------------------


Thanks,
Rahul Jha

View 2 Replies View Related

Fetching Duplicate Rows

Jan 14, 2004

Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me.....

Thanx in Advance

View 4 Replies View Related

I Am Getting Problem In Fetching Data Using C&#043;&#043;

May 21, 2007

Hi All,

I am getting problem in fetching data from SQL using C++ program.
How to Fetch SQL Unicode characters?
I am using ODBC driver calls in C++ to fetch SQL data.
It is working fine with character data.
But for Unicode data it displays “????� instead of data.
Can anybody please help?

Regards
Ketaki

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved