How to remove time from date?

Hello,
I have a table (T1) that has a field that holds a date with a time. In
another table (T2) I have records related to T1 on an ID field (one T1 to
many T2). T2 has a date field also, but this field does not have time (ie
the time part is midnight). I want to join the two tables on the ID and
Date but the time portions of the dates are not equal so they do not match.
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND T1.Date = T2.Date
Does anyone know how I can set the time portion of T1.Date to midnight in
the query (not in the table) so the join will work.
I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
want the time part dropped or midnight.
Thanks
EdmundHi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry|||I don't know why I did not think of this. It was staring right at me.
Thanks Barry. I've gone for CONVERT(CHAR(10),T1.Date, 120)) as it will
implicitly convert to DATETIME with date as midnight.
Edmund.
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1149517628.941905.154220@.h76g2000cwa.googlegroups.com...
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry
>|||try this:
CONVERT(VARCHAR(10), T1.date, 126)
Barry wrote:
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry|||Edmund,
You might want to chk this article :: http://www.aspfaq.com/show.asp?id=2460
Extract from that article:
--
For example, to get today's date in YYYYMMDD format, you currently need to
call the following:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
What does the 112 mean? Nothing. It's just an arbitrary number representing
this specific format.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Edmund" wrote:

> Hello,
> I have a table (T1) that has a field that holds a date with a time. In
> another table (T2) I have records related to T1 on an ID field (one T1 to
> many T2). T2 has a date field also, but this field does not have time (ie
> the time part is midnight). I want to join the two tables on the ID and
> Date but the time portions of the dates are not equal so they do not match
.
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND T1.Date = T2.Date
> Does anyone know how I can set the time portion of T1.Date to midnight in
> the query (not in the table) so the join will work.
> I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
> want the time part dropped or midnight.
> Thanks
> Edmund
>
>|||Edmund,
To truly remove time from datetime and not just "hide" it:
SELECT CAST(DATEDIFF(DAY,0,getDate()) AS DATETIME)
Returns:
2006-06-06 00:00:00.000
Mark|||A slightly better way to do it than using the CONVERT() function calls
would be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d,
DATEDIFF(d, 0, T2.Date), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web
article about datetime data including a short section on stripping time
info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
*mike hodgson*
http://sqlnerd.blogspot.com
Barry wrote:

>Hi,
>Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
>(Varchar(13), T2.Date, 112)
>
>HTH
>Barry
>
>|||Mike, this is very good.
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:u4N4C8PiGHA.1612@.
TK2MSFTNGP04.phx.gbl...
A slightly better way to do it than using the CONVERT() function calls would
be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d, DATEDIFF(d, 0, T2.Da
te), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web article a
bout datetime data including a short section on stripping time info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
mike hodgson
http://sqlnerd.blogspot.com
Barry wrote:
Hi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry

How to remove the records

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
Order by Name

The above will select all the records from InPatient and will retrieve either the status code ('A') or blank ('') from PTList, depending on whether or not the john has marked the record as "his".

What I want to do is to return the same dataset, but WITHOUT any records belonging to john.

I can't use <>'John' because 14 others might have the silly thing as active at the same time. What I'd really like to do is

Select * from
(the above)
Where Status=Null

I know it can be done, but I don't know how. Any ideas?I am guessing you want one of the following.

Tim S

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
WHERE PTList.Status IS NULL
Order by Name

Second Guess

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
WHERE NOT EXISTS
( SELECT * FROM PTList WHERE
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
)
Order by Name|||yup, it was the where not exists.

I'd already tried a variant on the other one, it returned nothing, but I probably set it up incorrectly.

Just tried the other way, also. Worked like a charm. I'm not positive, butg from somewhere I got the idea that the Exists works something like an IN statement? That would imply that the first way would be faster ... at least in machine terms.

Thank you

how to remove duplicate records from incoming textfiles

Is there a way to check if duplicates exists in the incoming textfiles?

Run the text file through a sort transformation and select the "remove duplicates" check box.|||using sort exceution seem to be too slow....is there any other way?|||

sureshv wrote:

using sort exceution seem to be too slow....is there any other way?

Yep,

Some related links for you that will help:

A distinct component please

(http://blogs.conchango.com/jamiethomson/archive/2006/12/08/SSIS_3A00_-A-distinct-component-please.aspx)

How to get Distinct Count in SSIS

(http://sqlblog.com/blogs/marco_russo/archive/2007/03/09/how-to-get-distinct-count-in-ssis.aspx)

NSort (High performance sort component that contains functionality to remove duplicates)

(http://www.ordinal.com/NsortSSIS.pdf)

"Distinct" related posts from the SSIS Search Macro

(http://search.live.com/results.aspx?q=distinct&form=QBRE&q1=macro%3Ajamiet.ssis)

-Jamie

|||You could also load that flat file as is to a staging table in SQL server then use a select distinct from that staging table.

How to remove blank spaces in records?

We imported approximately 2.9 million records from our mainframe serverinto our SQL Server but have run into a problem. The data in afew of the fields contains both leading and trailing spaces. Anexample of the data would be like this, using periods to representspaces:
What we have:
..1A02938....
What we need:
1A02938 (no spaces)
Is there some sort of algorithm I can run on the data to removethose spaces? The problem is coming up when trying to perform aSELECT query. We try something like:
SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938" but we get zeroresults because of the spaces in the database. The datatype ofthe filed is char(20) because we need some flexibility on the size ofthe data stored.
Any assistance would be greatly appreciated.
Try this link for packages and code to use to clean the data before you import it into SQL Server. Hope this helps.
http://www.sqldts.com|||

To select and insert the data you can use LTRIM(RTRIM(Pano20)) = "1A02938"

What you might want to do is run an update on the data.
UPDATE PCPIPTo SET PANO20 = LTRIM(RTRIM(PAN020))
This way, you dont have to trim the field each time and get the performance hit.

Nick

|||nick,
Thanks for the response. It took us about 3 hours toimport the data from the AS/400 so if we can just change the data whileit's in the table it would make life a lot easier. I'll try thatupdate query and see how it works.
Thanks!
|||If 1A03928 is indicative of the data stored in that column, I suggestchanging the datatype from char(20) to varchar(20) as well.|||nick,
Thanks for the help. That did the trick! I didn'tneed to do the RTRIM, just the LTRIM. But that makes my life alot easier.

How to remember last record read

Hi,

Can you guys see if there's a solution to this problem?

I have a database from which I have to read each record and process
that record. New records are being added all the time, so I need to go
back and check for new records and process them.

However:

-- there is no 'identity' column in the database design (so I cannot
keep track of the last record read by use of a numeric variable)

-- I am not allowed to update the database (so I cannot flag the
records I have read).

My problem is: how can I know which records I have already read and
which ones I haven't read yet? I don't want to process records twice
and don't want to miss any records.

Is there a known solution to this problem? Any ideas?

Thanks.Use a filed which has the datetime of type. Every time a record is
inserted update this with latest datetime. When viewing the record get
the latest of this field and check whether this different from the
record by getting the latest datetime from the table again

Madhivanan|||On 2 Mar 2005 22:36:48 -0800, francisds@.hotmail.com wrote:

(snip)
>My problem is: how can I know which records I have already read and
>which ones I haven't read yet? I don't want to process records twice
>and don't want to miss any records.

Hi francisds,

Madhivanan already pointed out the possibility to use a datetime column
with the date a row is inserted in the table. If such a column is not
available and you're not allowed to add one, here are a few other
options.

* You say that you're not allowed to update the database. Is that just
this table? If so, you can create a new table to hold the ey values of
all rows you already have processed.

* If you're not allowed to add tables to the database either, you could
consider keeping track of rows already processed in a table in another
database. This is quite a hack, though. It can be hard to keep the
databases synchronized, especially if you ever have to restore to a
backup.

* Yet another option would be to use a trigger on the table. Either do
the complete processing in that trigger (if it's short and can't fail),
or write rows with the primary keys of the inserted rows to another
table that you can use to decide which rows to process. Note that even
this trigger will slow down the insert process somewhat!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(francisds@.hotmail.com) writes:
> Can you guys see if there's a solution to this problem?
> I have a database from which I have to read each record and process
> that record. New records are being added all the time, so I need to go
> back and check for new records and process them.
> However:
> -- there is no 'identity' column in the database design (so I cannot
> keep track of the last record read by use of a numeric variable)
> -- I am not allowed to update the database (so I cannot flag the
> records I have read).
> My problem is: how can I know which records I have already read and
> which ones I haven't read yet? I don't want to process records twice
> and don't want to miss any records.
> Is there a known solution to this problem? Any ideas?

So where does your process live? Is it a stored procedure or a client
program? If it's stored procedure maybe a temp table would do.

More information about this process and the circumstances would be
needed to give a good answer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for the suggestions. I am not sure they will work yet, but
it does give me hope of finding a solution. I still need to digest
them.

As for the Erland's question. I am writing an application which runs
on a separate PC from the SQL Server database which holds the table.

The table contains records of a business event (for example, a
payment). Each time an event occurs, then a record describing that
event is appended in the table. My application needs to process all
those records.

Again, my problem is, I am not allowed to update the table (it's a
stupid political issue, not a technical one). I checked and it has no
identity field.

Since the table has a column containing the datetime of the event that
occured, I tried using the datetime to remember the last event I
processed. Unfortunately, I found out that events can be generated
from many points and there is no guarantee that the records of that
event will be saved in the order they occured (IOW, an event that
occured at 2:30pm could be saved before an event that occured at 2:15).

-Francis

Erland Sommarskog wrote:
> So where does your process live? Is it a stored procedure or a client
> program? If it's stored procedure maybe a temp table would do.
> More information about this process and the circumstances would be
> needed to give a good answer.|||> * You say that you're not allowed to update the database. Is that
just
> this table? If so, you can create a new table to hold the ey values
of
> all rows you already have processed.

Hi Hugo,

Novice question: what do you mean by the "value" of a row? How do I
get that?

I discovered that there is a column of 'uniqueidentifier' data type. I
could use this probably, although since the database generates
thousands of records a day, the list of processed keys will be fairly
large.

The trigger suggestion is intriguing. I might be able to use it for
another project, but not for this since I cannot change the database
(uhm, I haven't used triggers before, but I assume it requires
modifying the database, at least to save a trigger?)

Thanks.

-Francis|||(francisds@.hotmail.com) writes:
> Thank you for the suggestions. I am not sure they will work yet, but
> it does give me hope of finding a solution. I still need to digest
> them.
> As for the Erland's question. I am writing an application which runs
> on a separate PC from the SQL Server database which holds the table.
> The table contains records of a business event (for example, a
> payment). Each time an event occurs, then a record describing that
> event is appended in the table. My application needs to process all
> those records.

OK, so in theory you could just keep track in memory of which records
you have processed. But if you application crashes, you will lose
the knowledge of what you have processed. Then again, that depends on
what your processing consists of. If you have to redo the processing in
case of a crash that's the right thing.

Else your alternative appears to be to write to a file on the machine
you application runs on. When your application starts, you read the
file to see what you already have processed.

The challenge is two have a two-phase commit between the file and
the processing. If you write to the file too soon, you mail fail to
process a record in case of a restart. If you write too late, you may
reprocess a record on a restart.

Then again, if your processing involves some external mechanism, you
have the same problem, if you were to update the table.

> Again, my problem is, I am not allowed to update the table (it's a
> stupid political issue, not a technical one). I checked and it has no
> identity field.
> Since the table has a column containing the datetime of the event that
> occured, I tried using the datetime to remember the last event I
> processed. Unfortunately, I found out that events can be generated
> from many points and there is no guarantee that the records of that
> event will be saved in the order they occured (IOW, an event that
> occured at 2:30pm could be saved before an event that occured at 2:15).

You mentioned in another post that there is a uniqueidentifier column.
Thus the table has a primary key. An identity column had been a little
easier to work with since it monotonic, and a uniqueidentifier is not.
But you could use a combination of datetime and uniqueidentifer. The
nice with the datetime column, is that it permits you prune the log
of processed records easily.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 4 Mar 2005 10:58:19 -0800, francisds@.hotmail.com wrote:

>> * You say that you're not allowed to update the database. Is that
>just
>> this table? If so, you can create a new table to hold the ey values
>of
>> all rows you already have processed.
>Hi Hugo,
>Novice question: what do you mean by the "value" of a row? How do I
>get that?

Hi Francis,

My fault. I should have written "you can create a new table to hold the
values of the primary key columns in the rows you already have
processed".

>I discovered that there is a column of 'uniqueidentifier' data type. I
>could use this probably, although since the database generates
>thousands of records a day, the list of processed keys will be fairly
>large.

You could only use this if you were allowed to add a column to the
table. And if you are allowed to do that, than there are better
solutions than uniqueidentifier.

>The trigger suggestion is intriguing. I might be able to use it for
>another project, but not for this since I cannot change the database
>(uhm, I haven't used triggers before, but I assume it requires
>modifying the database, at least to save a trigger?)

Yes, the trigger is stored in the database. But I'm not sure exactly
what you may and may not change, as in other messages you wrote that you
are not allowed to update the table. The trigger is not part of the
table, it's stored seperately in the same database.

BTW, have you already considered how to handle updates to a row that you
have already processed?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You mentioned in another post that there is a uniqueidentifier
column.
> Thus the table has a primary key. An identity column had been a
little
> easier to work with since it monotonic, and a uniqueidentifier is
not.
> But you could use a combination of datetime and uniqueidentifer. The
> nice with the datetime column, is that it permits you prune the log
> of processed records easily.

As I wrote earlier, I am not guaranteed about the order of arrival of
the records. How do you figure I might use the datetime in combination
with the uniqueidentifier?

-Francis|||> >I discovered that there is a column of 'uniqueidentifier' data type.
I
> You could only use this if you were allowed to add a column to the
> table. And if you are allowed to do that, than there are better
> solutions than uniqueidentifier.

Oh, I meant to say that I discovered that the table I am supposed to
read has a column of uniqueidentifiers, not that I can create one for
my use.

> Yes, the trigger is stored in the database. But I'm not sure exactly
> what you may and may not change, as in other messages you wrote that
you
> are not allowed to update the table. The trigger is not part of the
> table, it's stored seperately in the same database.

I can't touch the database at all. :-(

> BTW, have you already considered how to handle updates to a row that
you
> have already processed?

Good alert. It's not needed for this application (the events are
mechanically generated and no one will be modifying the data).

(If it makes things clearer, the database I am reading is a Cisco
CallManager CDR table).

http://www.cisco.com/en/US/products...html #wp33440

-Francis|||(francisds@.hotmail.com) writes:
> As I wrote earlier, I am not guaranteed about the order of arrival of
> the records. How do you figure I might use the datetime in combination
> with the uniqueidentifier?

Sorry, I missed that part about the arrivals being out of order.

However, I supposed that you can assume that records arriving are not,
say, 24 hours old?

So you would write all GUIDs you processed to a file, together with
their datetime value. When you determine whether you have processed
a row or not, you first look at the datevalue, and if it's older than
24 hours (or two hours or whatever), you discard the record. If it's
newer, you look up the GUID in the file. (Probably you should have a
copy in memory for quick access. Every once in a while you would rewrite
the file and drop entires that are too old.

The main reason for this is to keep the local file down in size.

But you could also have a local SQL Server database that you write to. In
such case there would be no need for expiring old data. If the volume is
considerable so that several entries per second are generated, a database
may be the only option. (Or more precisely, you need an indexed store.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 4 Mar 2005 21:58:26 -0800, francisds@.hotmail.com wrote:

(snip)
>I can't touch the database at all. :-(

Hi Francis,

In that case, you'll have to make do with a kludge.

I think the best way to handle this is to keep a list of the primary key
values of all rows you have already processed. You could store this in
another database, either on the same SQL Server instance or on another
instance. Depending on the environment where your process runs, using
another method might be better. Erland already made some suggestions.

(snip)
>(If it makes things clearer, the database I am reading is a Cisco
>CallManager CDR table).
>http://www.cisco.com/en/US/products...html #wp33440

Based on a very quikc scan of the description, I'd say that you could
use either the pkid (based on the description, I agree that this is
probably a uniqueidentifier), or the combination of globalCallID_callID
and globalCallID_callManagerID. The latter might be the better choice,
as (if I understand the description correctly), the callID is assigned
sequentially on each server (and I assume that each server maps to a
callManagerID). You might find that you only need to store a last
processed callID for each callManagerID.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi All,

We are in process of discussing these solutions. I may later post here
the final solution we adopt. If I don't, thought you might want to
know that I am considering the two options suggested:

- "You might find that you only need to store a last
processed callID for each callManagerID. "

- Remembering the last datetime of last record, querying for new
records with a later datetime (though including those that are 15
minutes earlier, just to make sure), and keeping a list of processed
pkids to prevent duplicate processing. This private list will be
purged around the time of each query, removing those that are old
enough.

Thanks much guys !

-Francis

how to reconcile two tables using cursor.

Hi! I'm new here.
Here's my problem.

How can u reconcile two tables using cursor?
I would like to create a new table reflecting the matching records of the two tables I've compared.
how will i do this?
please help.Heya, ssshhh. Welcome to TSDN!

What database server software are you running?|||thnx for the welcome.
I'm using ms sql server 2000.

im doing a reconciliation report.
how can i insert the matching records into a new table?|||

Quote:

Originally Posted by ssshhh

thnx for the welcome.
I'm using ms sql server 2000.

im doing a reconciliation report.
how can i insert the matching records into a new table?

Now that we know your RDBM, we can direct you to the proper place to post.
That would be in the SQL Server/MSSQL Forum.

Also, please take some time to check out our Posting Guidelines. Thanks.|||Moving to the MS SQL forum...|||am i not in the mssql forum already?|||Use joins for your purpose and create the table based on that.

If u want to continue the process create a view instead.|||Heya, ssshhh.

Quote:

Originally Posted by ssshhh

am i not in the mssql forum already?

You are now ~_^

How to reader header and footer in EBCDIC file?

I have data arriving in fixed-width EBCDIC format. Each file contains one or more groups of records. Before and after each group there is a header/footer, which is not in the same layout as the records that it describes. Header, record and footer each have a different layout to the other but are consistent within themselves.

Thankfully the one thing header, footer and record layout have in common is their length, so at the moment, using the appropriate code page in the Flat File Connection Manager, I'm able to read all the columns as strings. The headers and footers just come through, albeit a bit weird looking, and I can filter them out with a conditional splt.

However, the header contains information that needs to be appended to each record in the group. Does anyone have any suggestions about how to achieve this? I'm trying to avoid developing a custom data source for this task but, if there's no other way, has anyone done it and do they have any tips?

You should search this forum for discussions on topics similar to this.

You can read the file twice, using two data flows. The first time, to extract just the header and to store its values in a table, perhaps. The second time, to extract just the detail records and to join to the table from the first run to pull in the information you need.

Or, you can use a script task to read through the file. Or a script component inside the data flow. You may have to read in the entire line as one big string though.|||I would read the file twice as does not involved custome code. I knmow John(Jwelch) has some sample code on his blog so that may be another resource apart from searching the forum

how to read multiple records in a xml file using openxml

I have a xml like:

<Member ID="123">
<DateBorrowed>11-01-2006</DateBorrowed>
<Book ID="222"
Title=""ABC">
<Category> Fiction</Category>
</Book>
<Book ID="333"
Title=""ABu">
<Category>Children</Category>
</Book>
......
</Member>

I used OPENXML to read into MS SQL server database table, but it only reads first book. What should I do to read all three books, and insert 2 lines into the table:

MemberID | DateBorrowed | BookID | BookTitle | BookCategory
123 11-01-2006 222 ABC Fiction
123 11-01-2006 333 ABu Children
......

My code following only read first book:

CREATE PROCEDURE sp_insert_RefundCorrectionAcceptance AS

DECLARE @.iTree int
DECLARE @.xmlFile VARCHAR(2000)

EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlFile,
SELECT *
FROM OPENXML (@.iTree,'root', 3)
WITH (
MemberID decimal '/Member/@.ID',
DateBorrowed datetime 'DateBorrowed/text()',
BookID decimal '/book/@.id',
bookTitle varchar(200) '/book/@.title',
bookCat varchar(100) '/book/category/text()'

)
EXEC sp_xml_removedocument @.iTree
GO

Thanks

I hop it will work for you.........

Code Snippet

DECLARE @.iTree int

DECLARE @.xmlFile VARCHAR(2000)

set @.xmlFile='

<Member ID="123">

<DateBorrowed>11-01-2006</DateBorrowed>

<Book ID="222" Title="ABC">

<Category> Fiction</Category>

</Book>

<Book ID="333" Title="ABu">

<Category>Children</Category>

</Book>

<Book ID="334" Title="XYZ">

<Category>XYZ</Category>

</Book>

</Member>

'

EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlFile

SELECT * FROM OPENXML (@.iTree,'/Member/Book/Category',2)

WITH (

MemberID decimal 'http://@.ID',

DateBorrowed datetime 'http://DateBorrowed/text()',

BookID decimal '../@.ID',

bookTitle varchar(200) '../@.Title',

bookCat varchar(100) 'text()'

)

EXEC sp_xml_removedocument @.iTree

GO

Happy Programming Dear...

How to read log Files

Some records got deleted from our DB. We don't have any triggers to log this delete transaction. Is there anyway to find out from log files or someother places to find out about this deletion. Is this information stored in transaction log. We do complete backup every night. So I think the transaction log will be truncated after the complete backup right?
Please advise on this.

thanksIf recovery mode is Full then Lumigent Log Explorer (http://www.lumigent.com)(FULL VERSION) can be installed and the specific transaction can be identified. If recovery mode is Simple, Log Explorer can still be used, but chances of recovering the transaction are much-much slimmer.

How to read block of rows from database tables

have created a Database Application in Java and display all the records in tabular format of one Table. This table have Millions of Rows, If I run Select * from Table, then my Machine not responding, so Now I wants to add paging of 1000 rows at one time.

Is there are any option/query to read block of rows at one time and then query again for next page ?

i.e In MYSQL have LIMIT clause with Select Statement

Please let me know..

Database : SQL Server 2000/2005,

Thanks in Advance
Laxmilal

Whenever you use Select statement you must use WHERE condition to limit the rows.

Eg.

Select col1,col2.... From Tablename WHERE someid between 1 and 1000

Madhu

|||

You 'Machine not responding' is most likely due to waiting for millions of rows to come back from the server.

You really need to limit the quantity of data that you are requesting from SQL Server. Without WHERE clause criteria that enforces limits, you are unnecessarily wasting bandwidth (and time) as more data than you need for the operation at hand is being transported to the client application.

With SQL 2005, you may wish to explore using the new ROW_NUMBER() function to assist in retrieving specific 'blocks' of rows.

Referring to Books Online, Topic: ROW_NUMBER()

Example:

USE AdventureWorks; GO

WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;(You should be able to adapt this concept for your needs.)

how to quick query duplicate records?

any idea?

quick query duplicate records (speicifed fields.value are same) using T-SQL?

depends on how many columns you've got in the table but as an example:

say you have a table called Customer with one column called CustomerID then this simple query will tell you.

SELECT CustomerID, COUNT(*)

FROM Customer

GROUP BY CustomerID

HAVING COUNT(*) > 1

ORDER BY 2 DESC

|||

Thanks your reply what i want is

display duplicate records depends on multi-column

regards

|||

just add the extra columns in to the SELECT and GROUP BY clauses and it will work for multiple columns.

even better, can u post an example of the data that you are wanting to look for duplicates in?

|||

At the same time if you table own identity field you could use another query. Keeping on mind identity value you drop the one own less value.

I'm sorry but I don't have that query

|||

Because i want to remove duplicate records,so have to display first i think

I found an article

http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp

I think it's cool, sharing

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in messag
e
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
>

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>
|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
>

How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
--
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> > I have edited a single company name in the Northwoods Customers
> > table so the CompanyName value begins with a lower case character.
> >
> > When using Query Analyzer to run against Northwoods the following
> > query returns 4 records: the one I edited with the lower case a and 3
> > others where the company's name begins with upper case A
> >
> > SELECT * FROM Customers
> > WHERE CompanyName LIKE 'a%'
> >
> > Thus, case insensitivity appears to be the T-SQL default. How would
> > I get only those records where CompanyName begins with lower case
> > characters?
> >
> > BTW: I'm building one of those alphanumerice linked character menus
> > that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> > have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> > *all* company names whether they begin with an upper case letter
> > or a lower case letter.
> >
> > Using [Aa]% functioned as described above but then I realized I misled
> > myself as I was not able to get *only* records with a company name that
> > began with the lower case character when using [a]% or a%.
> >
> >
> > --
> > <%= Clinton Gallagher
> > A/E/C Consulting, Web Design, e-Commerce Software Development
> > Wauwatosa, Milwaukee County, Wisconsin USA
> > NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> > URL http://www.metromilwaukee.com/clintongallagher/
> >
> >
>

How to query on a date range, display matching records but also display companies with no matchi

OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.

I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.

Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

Jambi,

you can basically write an Union Sql query

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)

UNION ALL

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site

WHERE some 7 other company exist

GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

The union statement will return your date range and the other 7 company data if they exist.

Ham

How to query on a date range, display matching records but also display companies with no ma

OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.

I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.

Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

Jambi,

you can basically write an Union Sql query

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)

UNION ALL

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site

WHERE some 7 other company exist

GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

The union statement will return your date range and the other 7 company data if they exist.

Ham

How to query for records that have not been replicated

Hi,

I am using Merge Replication for synchronizing a bunch of laptops
running MSDE with a central SQL Server 2000 production server.

This database is used by an application that needs to know which
records have not yet been replicated, at any givem time.

Here's a simple scenario:

User A in laptop X starts his copy of the application. He syncs his
MSDE database through the application, to match the current central
servers' data. He then proceeds to add new records through the
application and, after he is done, he synchronizes his database with
the central server, through the application.

At any given time, the user might kill the application. He might add
some records today, a few more tomorrow or in a week and should be able
to change and delete records that have not yet been merged into the
production server.

The problem is that SQL Server does not have an easy way to tell which
records in the subscription tables have been inserted but not yet
replicated. I have been using the following query:

SELECT rowguid
FROM [MyTable]
INNER JOIN (
SELECT rowguid AS rep_id
FROM msmerge_contents
WHERE generation = joinchangegen AND generation = (
SELECT MAX(generation)
FROM msmerge_genhistory )
) dtable
ON rowguid_rep = dtable.rep_id

This query works for most cases but fails to identify the first record
created immediatly after a synchronization event.

What I would like to know is if there is a correct way of identifying
records which are "not-yet-replicated" on a given table that is part of
a merge replication process like the one described above.
Thanks in advance.<miltonmoura@.gmail.com> wrote in message
news:1104839914.237873.95740@.f14g2000cwb.googlegro ups.com...
> Hi,
> I am using Merge Replication for synchronizing a bunch of laptops
> running MSDE with a central SQL Server 2000 production server.

<snip
> What I would like to know is if there is a correct way of identifying
> records which are "not-yet-replicated" on a given table that is part of
> a merge replication process like the one described above.
> Thanks in advance.

I have no idea myself, but you might get a better response in
microsoft.public.sqlserver.replication.

Simon

How to query for a number only?

I have a varchar column, I want to find the records where the value is a
number only.
TIA
CDSee if this helps:
What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"CD" wrote:

> I have a varchar column, I want to find the records where the value is a
> number only.
> TIA
> CD
>
>|||Hi
just see if this helps:
SELECT *
FROM <table>
WHERE ISNUMERIC(<Column> ) = 1
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"CD" wrote:

> I have a varchar column, I want to find the records where the value is a
> number only.
> TIA
> CD
>
>|||CD
CREATE TABLE #Test
(
col VARCHAR(10) NOT NULL
)
INSERT INTO #Test VALUES ('GDGS12')
INSERT INTO #Test VALUES ('566')
INSERT INTO #Test VALUES ('GGG')
SELECT * FROM #Test WHERE PATINDEX('%[A-Z]%',col) =0
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:ONdeU2CbFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a varchar column, I want to find the records where the value is a
> number only.
> TIA
> CD
>|||Here's a UDF that checks if a value is really numeric.
Sorry, I don't remember who to give credit to for the function.
CREATE FUNCTION dbo.IsReallyNumeric
(@.num VARCHAR(19))
RETURNS BIT
BEGIN
RETURN CASE
WHEN LEFT(@.num,1) LIKE '[-0-9+.]'
AND PATINDEX('%[^0-9.]%', SUBSTRING(@.num, 2, 18)) = 0
AND LEN(@.num) - LEN(REPLACE(@.num, '.', '')) <=1
THEN 1
ELSE 0
END
END
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:ONdeU2CbFHA.3120@.TK2MSFTNGP12.phx.gbl...
>I have a varchar column, I want to find the records where the value is a
>number only.
> TIA
> CD
>