Email subscription

I have couple of reports on email subscription. These are exception reports
that get delivered if there are records in the report. The question is that
an empty report gets delivered if there are no records. Is there any way to
stop email delivery on a subscription when no data is returned in the report.
--
Thanks
RK BalajiCurrently the only way to do this is through data driven subscriptions. You
need to have the data driven subscription query return no rows if the report
will be empty.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"RK Balaji" <RKBalaji@.discussions.microsoft.com> wrote in message
news:B0621348-831B-40D6-979B-4EB867984B0E@.microsoft.com...
>I have couple of reports on email subscription. These are exception reports
> that get delivered if there are records in the report. The question is
> that
> an empty report gets delivered if there are no records. Is there any way
> to
> stop email delivery on a subscription when no data is returned in the
> report.
> --
> Thanks
> RK Balaji

Email report ONLY if there are records ?

Any idea how to have a Report Subscription only email the report or link if
there are records returned by the reports query or dataset?You would need to do this in a data driven subscription.
Brian Welcker
MSFT
"steve" <steve@.discussions.microsoft.com> wrote in message
news:74944DC2-1C3F-44C0-9E6B-1625D6641E0B@.microsoft.com...
> Any idea how to have a Report Subscription only email the report or link
> if
> there are records returned by the reports query or dataset?
>sql

Email report (or link), only if there are records to be viewed.

Any idea how to have a Report Subscription only email the report or link if
there are records returned by the reports query or dataset?The only way I've seen to do this is with a data driven subscription.
Unfortunately, DDS is not available in non-Enterprise edition
installations. If you can't do DDS, the best you can do is use an
expression like ="No data available to report" in the NoRows property.
The subscription email still goes out but the report contains an
explanatory message instead of a blank page.
HTH
toolman
Corvallis wrote:
> Any idea how to have a Report Subscription only email the report or link if
> there are records returned by the reports query or dataset?|||Hi Corvallis,
Were you able to do this? Have the same need to email the
report ONLY if there are any records. Please let us know.
Best regards,
Steve.
"Corvallis" wrote:
> Any idea how to have a Report Subscription only email the report or link if
> there are records returned by the reports query or dataset?|||Hi Toolman,
Do you have any examples of this
? We need the same logic. Am looking at the step 3 window and the place to
put this in but am not sure.
Thanks, Steve.
"toolman" wrote:
> The only way I've seen to do this is with a data driven subscription.
> Unfortunately, DDS is not available in non-Enterprise edition
> installations. If you can't do DDS, the best you can do is use an
> expression like ="No data available to report" in the NoRows property.
> The subscription email still goes out but the report contains an
> explanatory message instead of a blank page.
> HTH
> toolman
> Corvallis wrote:
> > Any idea how to have a Report Subscription only email the report or link if
> > there are records returned by the reports query or dataset?
>

email details from SQL Server agent based on query results?

Hello,

I want to remove records from the database automatically, BUT it is very important for me to know (email) if there were records or not and how many are affected by the automatic task...

My job is scheduled to run every day and does something like:

DELETE from RadioactiveMaterials

WHERE (NotExplosive = true)

SET @.rowcount_var = @.@.ROWCOUNT

I would like to get detail on the deleted records, how can I produce the email?

If you are using SQL 2005, using Books Online, refer to the Topic: 'Database Mail How-to Topics'.

If you are using SQL 2000, I suggest that you check out xp_smtp_mail from here. Most folks will recommend NOT to use MAPI mail on a SQL Server.

|||

Arnie Rowland wrote:

If you are using SQL 2005, using Books Online, refer to the Topic: 'Database Mail How-to Topics'.

Database Mail How-to Topics:

http://msdn2.microsoft.com/en-us/library/ms188298.aspx

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

Email adddress format validation question

Trying to find the best way to vaildate email formats when importing records to result tables etc... the first seems to run faster, but it seems too easy, like something is getting missed.

Does anyone know if using:

WHERE email LIKE '%@.%.%'

instead of using:

WHERE
( CHARINDEX(' ',LTRIM(RTRIM([email]))) = 0
AND LEFT(LTRIM([email]),1) <> '@.'
AND RIGHT(RTRIM([email]),1) <> '.'
AND CHARINDEX('.',[email],CHARINDEX('@.',[email])) - CHARINDEX('@.',[email]) > 1
AND LEN(LTRIM(RTRIM([email]))) - LEN(REPLACE(LTRIM(RTRIM([email])),'@.','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([email])))) >= 3
AND (CHARINDEX('.@.',[email]) = 0 AND CHARINDEX('..',[email]) = 0) )

is any better, or are they both pretty much bring back the same result sets?

Thanks for sharing your insight.Looks like T-SQL from where I am standing. Moving to SQL Server. Any mod spot something I missed please relocate.|||The latter is more thorough but restrictive. You may also get false positives (for example my email address ends .uk). You don't need to use LEFT and RIGHT - you can use LIKE (especially for the LEFT since it is then sargable) so it could be more efficient. Perhaps create more of a mask (off the top of my head)

LIKE '_%@._%.__%'
Min 1 char then @. then min 1 char then . then min 2 chars.

HTH|||most people use regex for this kind of thing, although you don't have that option in t-sql.

The regex for the RFC 822 definition of what constitutes a valid address is amusing, see link below.

http://www.regular-expressions.info/email.html|||Great insights! I didnt think about the .uk etc.... Thanks! And I've never used regex before. Thanks so much for the link! I will read up on it! Thank you both!!!!!

EM - Current Activity

I manage a data warehouse. Each month I potentially update
millions of records (only if they've changed from the
previous month). You can imagine this update process is
quite time consuming. I've got the tables spread accross 2
to 3 drives via File Groups. The table I update from is
either a data type table variable, temp table or a table
on it's on drive with a clustered index. I've got 2 gigs
of RAM for SQL (Std Edition)but a total of 4, SQL's
priority is boosted, plenty of hard drive space, 4
processors. In EM's current activity, under Physical IO I
seem to have a bottleneck. My wait times for one process
(multiple instances) can easily be in the millions. When I
use Perf Mon to view disk activity (read\write) at least
one usually 2 drives are maxed out. Any thoughts on how I
can improve performance?Liz,
Well one way is ensure the log file is on a separate Raid 1 than anything
else. When you say you have the data spread across 3 drives do you mean 3
individual physical drives or 3 drive array's? 2 or 3 physical drives are
usually not enough to get good performance out of a typical DW application.
The more drives the better. For updates it is very important to have the
log on a separate array than the data. You can also try doing the updates
in smaller batches and see if that speeds things up but you will still get
high disk activity.
--
Andrew J. Kelly
SQL Server MVP
"Liz" <anonymous@.discussions.microsoft.com> wrote in message
news:257201c3af73$a15507b0$a601280a@.phx.gbl...
> I manage a data warehouse. Each month I potentially update
> millions of records (only if they've changed from the
> previous month). You can imagine this update process is
> quite time consuming. I've got the tables spread accross 2
> to 3 drives via File Groups. The table I update from is
> either a data type table variable, temp table or a table
> on it's on drive with a clustered index. I've got 2 gigs
> of RAM for SQL (Std Edition)but a total of 4, SQL's
> priority is boosted, plenty of hard drive space, 4
> processors. In EM's current activity, under Physical IO I
> seem to have a bottleneck. My wait times for one process
> (multiple instances) can easily be in the millions. When I
> use Perf Mon to view disk activity (read\write) at least
> one usually 2 drives are maxed out. Any thoughts on how I
> can improve performance?

Else statement is not working in the stored procedure

I have a stored procedure that needs to populate the fields of records for tblBag_data from the tblshipping_sched if not found it looks in the tblshipment_history. But it not checking the history table(2nd table). Please help!

CREATE Procedure spUpdate_bag_data
@.t1 int OUT
AS

declare @.work_ord_num char(9), @.two char(7), @.work_ord_line_num char(3), @.cust_num char(5), @.cust_name char(50), @.apple_part_num char(12), @.apple_catalog_num char(28);

Declare update_bag CURSOR
FOR
SELECT work_ord_num, work_ord_line_num
FROM tblBag_data
WHERE cust_num IS NULL;

OPEN update_bag
FETCH NEXT FROM update_bag INTO @.work_ord_num, @.work_ord_line_num

WHILE @.@.FETCH_STATUS = 0 --and @.counter<30
BEGIN
--set @.counter = @.counter + 1
SET @.two = LEFT(@.work_ord_num,6) + '%'
set @.cust_num = '';

SELECT @.cust_num = cust_num, @.cust_name = cust_name, @.apple_part_num = apple_part_num, @.apple_catalog_num = apple_catalog_num
FROM tblShipping_sched
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;



IF @.@.RowCount > 0
BEGIN

UPDATE tblBag_data
SET cust_num = @.cust_num, cust_name = @.cust_name, apple_part_num = @.apple_part_num, apple_catalog_num = @.apple_catalog_num
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;
END

ELSE
BEGIN

SELECT cust_num = @.cust_num, cust_name =@.cust_name, apple_part_num =@.apple_part_num, apple_catalog_num = @.apple_catalog_num FROM tblShipment_history
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;

IF @.cust_num IS NOT NULL and len(@.cust_num)= 5
UPDATE tblBag_data SET cust_num = @.cust_num, cust_name = @.cust_name, apple_part_num = @.apple_part_num, apple_catalog_num = @.apple_catalog_num
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;

END

FETCH NEXT FROM update_bag INTO @.work_ord_num, @.work_ord_line_num
END

close update_bag
deallocate update_bag

return(1)Why are you using a cursor? There's no need.

Also if
work_ord_num AND wrk_ord_line_num

are not the primary or a unique constraint to
FROM tblShipping_sched

Then you can get back multiple rows...and your assingment to the variables will be the last one returned...

And since there is no input variable to the sproc, it means you'll be doing every row in the table every time you run it...
well where cust_num is null

Need to see the DDL for the three tables...(sample data wouldn't hurt either)|||Originally posted by Brett Kaiser
Why are you using a cursor? There's no need.

Also if
work_ord_num AND wrk_ord_line_num

are not the primary or a unique constraint to
FROM tblShipping_sched

Then you can get back multiple rows...and your assingment to the variables will be the last one returned...

And since there is no input variable to the sproc, it means you'll be doing every row in the table every time you run it...
well where cust_num is null

Need to see the DDL for the three tables...(sample data wouldn't hurt either)

Hi Brett,

I have a tblBag_data that needs four fields populated from the tblshippping_sched or tblShipment_history. The stored procedure is takes the work_ord_num and work_ord_line_num in tblBag_data and match them to the tblshipping_sched if the cust_num is null. It loops thru the tblshipping_sched for that record if it finds the record it populate the four fields(cust_name, cust_num..)in the tblBag_data. But if it doesn't find it it suppose to go to tblShipment_history table and loops thru for the same record and populates the tblBag_data once it finds it.

The If statement seems to be working fine. But else is definitely not working. If there is better way to write this without cursor please provide some sample code.

Thank you.
I hope it|||Sorry...work got in the way...

How about:

UPDATE l
SET cust_num = r.cust_num
, cust_name = r.cust_name
, apple_part_num = r.apple_part_num
, apple_catalog_num = r.apple_catalog_num
FROM tblBagData l
INNER JOIN tblBagData r
ON r.work_ord_num like LEFT(l.work_ord_num,6) + '%'
AND r.work_ord_line_num = l.work_ord_line_num
WHERE cust_num IS NULL

And you don't even have to worry if it finds it ot not because youcan then just do the second query, because the cust_num will still be null|||Originally posted by Brett Kaiser
Sorry...work got in the way...

How about:

UPDATE l
SET cust_num = r.cust_num
, cust_name = r.cust_name
, apple_part_num = r.apple_part_num
, apple_catalog_num = r.apple_catalog_num
FROM tblBagData l
INNER JOIN tblBagData r
ON r.work_ord_num like LEFT(l.work_ord_num,6) + '%'
AND r.work_ord_line_num = l.work_ord_line_num
WHERE cust_num IS NULL

And you don't even have to worry if it finds it ot not because youcan then just do the second query, because the cust_num will still be null

Hmm... This might be a solution.
I'll give it a try.
Thanks!

eliminating duplicate records from a table

Hi All

I am having problem in selecting the data from oracle. The problem is there are multiple enteries per customer, I want to select the latest updated value. I know the customer ids which has multiple entries corresssponding to them.

The columns are Cust_ID, Update_Date, Cust_Name,Cust_address.

Select ADDRESS_LINE1, LAST_UPDATE_DATE,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP,COUNTRY

from Customer_Table s where s.cust_id in (101,102,103,104,105,106)

Group By ADDRESS_LINE1, LAST_UPDATE_DATE,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP,COUNTRY

having .........(no idea)

I know this is not correct as I am still getting multiple records.

Any help will be useful.

Well, we don't really do oracle around these parts, but...

You should be able to say

count(*) > 1

in the having clause to get groups where there are > 1 rows in there.

|||

I guess that the later means the one with latest LAST_UPDATE. I do not know if this works for "oracle", but it does for SQL Server.

select *

from Customer_Table s

where s.cust_id in (101,102,103,104,105,106)

and last_update = (

select max(a.last_update)

from Customer_Table as a

where a.cust_id = s.cust_id

)

go

AMB

|||

Thanks for replying..I am sorry fro posting oracle query here..

I found the solution.....Thanks a lot...

Eliminating Duplicate Records

Hi

How can i eliminate duplicate records from a single table, when i use a query that links multipla tables together?

There is a surrogate key TABLE1 and i just can't seem to wrap my head around the layout.

TABLE1

ID | memQuestionaireID | QuestscoreID | DateOfAssessment | etc.

TABLE2
memQuestionaireID | MemberID | Answer1 | Answer2 | etc.

TABLE3

MemberID | Name | Surname | IDNo | etc.

t1.memQuestionaireID = t2.memQuestionaireID

t2.MemberID = t3.MemberID

That's how the tables link, obvious enough. How can i delete duplicate records from TABLE1 where MemberID occurs more than once and the t1.DateOfAssessment = MAX(DateOfAssessment)

The ID is unique, but there are duplicate memberID's with different date of assessments, i want to take the last used date of assessment and keep only that one, and remove the rest from the table?

I'm sure the query is simple, i'm just too stupid to see how to solve it!!

Any help would be greatly appreciated.

I'm not very clued up on how to remove records or update records, Please help.

Kind Regards
Carel Greaves

See if this does what you need...

Code Snippet

DELETE t1

FROM Table1 t1

INNER JOIN Table2

on t1.memQuestionaireID = t2.memQuestionaireID

INNER JOIN

(

SELECT t2b.MemberID, max(t1b.DateOfAssessment) as DateOfAssessment

FROM table1 t1b

INNER JOIN table2 t2b

ON t1b.memQuestionaireID = t2b.memQuestionaireID

AND t2b.MemberID in

(

SELECT MemberID

FROM Table3

GROUP BY MemberID

HAVING count(*)>1

)

GROUP BY t2b.MemberID

) as list

ON t1.DateOfAssessment < list.DateOfAssessment

and t2.MemberID = list.MemberID

To view what will be delete w/o actually deleting, just change the first line from DELETE t1 to SELECT t1.*

|||

I'm a bit confused Carel,

Call me dense today, but it seems like the MemberID 'should' be in Table1 since it is the Member that has a memQuestionaireID, has a QuestScoreID and a DateOfAssessment. Then for Table2, it is the Questionaire that has a set of answers, etc. Please help me out here...

It seems like your Table1 has a one-to-many relationship with Table2. I'm assuming that a Member may have multiple instances of the same questionnaire (re-takes, etc.)

Is one questionnaire (MemQuestionnaireID) with a QuestScoreID and a DateOfAssessment directly linked to one or more set(s) of answers in Table2?

Is the QuestScoreID changed upon each re-take? (Shouldn't the score be directly related to a set of answers?)

I ask this because it seems from the design you presented that perhaps you want to also delete rows from Table2...

|||

I know the database structure is a bit stupid. Not my design!

I am only trying to remove duplicate entries in the QuestScore table.

Problem:

A member fills in a Questionaire - MemQuestionaires

The Querstionaire's Score is placed in the QuestScore Table.

There are duplicate entries in the QuestScore table with regards to a questionaireID from the memQuestionaires Table

If a duplicate exists, then the members can't log into there profiles anymore, so i need to remove the duplicates so that the members can access their profiles again.

I'm quite new to all of this, so any help would be greatly appreciated

Kind Regards

Carel Greaves.

|||

You gave us Table1, Table2, Table3...I'm guessing Table1 is QuestScore?

Did my code above work? If not, what was not quite right?

Are there multiple entries just in Table1, or is it Table3?

Please clarify and maybe post some sample data...

|||

Okay, so i'm a bito f a moron :-) he he

Here is the code i used to find the duplicates

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*

FROM questscore q, memQuestionaires m, Members me

WHERE q.memquestionaireID = m.memquestionaireID

AND m.MemberiD = me.memberid

AND me.ClientID = 124

ANd m.questionaireid = 85

AND q.sectionid >= 1000000

ORDER BY me.memberID

Here is my results:

MemberID memQuestionaireID QuestScoreID AnswerID AnswerValue DateCompleted 10005772 7615 273308 0 -500 50:59.7 10005789 11124 267771 0 -500 25:16.0 10005798 11129 259150 0 -500 44:41.1 10005798 11129 261032 0 -500 01:21.3 10005807 8214 237464 0 -500 59:18.1 10005819 11142 293317 0 -500 43:09.4

i have marked the duplicate values in red that i want to eliminate.

I need to eliminate only the record with the oldest date, and keep the newer one.

Hopefully when i've gone for my SQL course then i will only be half a moron :-) he he

Thanks for helping me out guys.

Kind Regards

Carel Greaves

|||

Wow, talk about a 'bolixed' up database...

I'm still confused, in your query above, you have a WHERE clause that indicates the results should be filtered down to QuestionaireID = 85, but your duplicate records are for memQuestionaireID 11129. What is the difference between QuestionaireID and memQuestionaireID? How did that happen?

Also, the DateCompleted is obviously not a date, but a number of hours. Is that the number of hours since some event? How are the hours determined?

Which row is the 'newer one'?

It would be very helpful, and keep us from wasting our time, if you would post the DDL for each of the tables, as well as some sample data for each table (in the form of INSERT statements.)

As you seen, you have folks sincerely attempting to help you, and to this point wasting our time because we just don't have the 'full' picture. You've inherited a very 'odd' database, and you need to help us so we can better help you.

|||

Here is sample data in the Tables.

Member Table

med_aid MemberID ClientID Name Surname Username Password NULL 10000000 1 NiftyName NiftySurname his test NULL 10000001 5 NiftyTest2 Surname2 nifty@.ybo.oa JBCYWO NULL 10000002 5 KeithTest WilsonTest willi willi

memQuestionaires Table

memQuestionaireID MemberID QuestionaireID LastPage Paused Complete MaxPages Sent LastSaved InitMail ReceiveMail UpdateDate 871 10000000 85 1 0 0 1 1 0 1 1 2007/02/2506:23 872 10000001 85 1 0 0 1 1 0 1 1 2007/02/2506:23 873 10000002 85 1 0 0 1 1 0 1 1 2007/02/2506:23

QuestScore Table

QuestScoreID memQuestionaireID AnswerID AnswerValue SectionID Page QuestType AnswerText AnswerShort Updatedate DefaultValue DateCompleted 4641 871 0 -9 361 1 9 NULL NULL

2007/02/0722:31

4642 872 0 -5 362 1 6 NULL NULL

2007/02/0722:31

4643 873 0 0 345 1 2 NULL NULL

2007/02/0722:31

When i executed this statement, i only used these three tables.

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*
FROM questscore q, memQuestionaires m, Members me
WHERE q.memquestionaireID = m.memquestionaireID
AND m.MemberiD = me.memberid
AND me.ClientID = 124
ANd m.questionaireid = 85
AND q.sectionid >= 1000000
ORDER BY me.memberID

I am only trying to remove the duplicate records for where ClientID = 124

AND QuestionaireID = 85

as for the 1000000, there it is just to narrow down the search for myself, there are a lot of duplicates.

There are a lot of members -> Members Table

Each member can fill in a number of questionaires -> QuestionaireID on memQuestionaires Table in this case 85

And all the questionaire's data is stored in the QuestScore Table which is linked to the memQuestionaires table using the memQuestionairesID.

There seems to be duplicate values in the QuestScore Table referring to the members.

The date completed is an actual DATETIME field in the database, i used excel to transfer the data to here. The newer date will be a actual date with the time in the database i.e. 2007/05/23 02:05:11

All the fields get inserted into the database via a application which i haven't seen.

I'm new to the company and am trying to figure a lot of it out for myself too.

If there is any more information you need, please don't hesitate to ask.

|||try this one,

DECLARE @.QuestScore TABLE (
QuestScoreID int
, memQuestionaireID int
, DateCompleted smalldatetime
)

DECLARE @.MemQuestionaire TABLE (
MemQuestionaireID int
, MemberID int
, QuestionaireID int
, UpdateDate smalldatetime
)

INSERT
INTO @.QuestScore
SELECT 1, 1, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 2, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 3, dateadd(day,-3, getdate())

INSERT
INTO @.MemQuestionaire
SELECT 1, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 2, 5, dateadd(day,-3, getdate())

select *
from @.memquestionaire

select *
from @.questscore

declare @.tobedeleted table( -- create a table var for the data to be deleted
memquestionaireid int
, datecompleted smalldatetime
)

insert
into @.tobedeleted -- the memquestionaireid and datecompleted to be deleted
select MIN(qq.memquestionaireid) as memquestionaireid -- just in case if they have the same date, get the lowest id
, mq.datecompleted
from @.questscore qq inner join
(
select m.memberid
, m.questionaireid
, MIN(q.datecompleted) as datecompleted -- delete the lowest date
from @.memquestionaire m inner join
@.questscore q on m.memquestionaireid = q.memquestionaireid
group by
m.memberid
, m.questionaireid
having count(m.memberid) > 1 -- member having more than 1 quest score
) mq on qq.datecompleted = mq.datecompleted
group by
mq.datecompleted

delete @.questscore -- delete in questscore
from @.questscore q inner join
@.tobedeleted dq on q.memquestionaireid = dq.memquestionaireid
and q.datecompleted = dq.datecompleted

delete @.memquestionaire -- delete in memquestionaire
from @.memquestionaire m inner join
@.tobedeleted dq on m.memquestionaireid = dq.memquestionaireid

select *
from @.questscore

select *
from @.memquestionaire|||

Thanks, that's awesome, that's why i come to you guys for help.

Another quick question, that i think it might be easier to do, i just can't do it. (I just thought of it now)

If i added memQuestionaireID and SectionID together to create a unique field, and then filter out the duplicates by taking out the old dates within the duplicates it might be easier, but how would i go about accomplishing this?

I don't know how to compare the dates against itself to get keep the new date and get rid of the old date?

sorry for all of this i'm just trying to learn from all the things myself?

|||

Thanks, that was a start.

Here is an example of the 'best' form for DDL and sample data. In this fashion, each person that wants to try and help you can just run this code and have 'your' problem to work with. Without this, folks are turned away because it takes so much time and effort to duplicate your situation -and helps to keep us from running off on tangents that don't really help you.

Now if you would only add to the sample data so that examples of the duplicate records you want to find and delete are represented, we 'should' be able to help you.

Code Snippet

DECLARE @.Members table
( med_aid int,
MemberID int,
ClientID int,
Name varchar(20),
Surname varchar(20),
Username varchar(100),
Password varchar(20)
)

INSERT INTO @.Members VALUES ( NULL, 10000000, 1, 'NiftyName', 'NiftySurname', 'his', 'test' )
INSERT INTO @.Members VALUES ( NULL, 10000001, 5, 'NiftyTest2', 'Surname2', 'nifty@.ybo.oa', 'JBCYWO' )
INSERT INTO @.Members VALUES ( NULL, 10000002, 5, 'KeithTest', 'WilsonTest', 'willi', 'willi' )

DECLARE @.memQuestionaires table
( memQuestionaireID int,
MemberID int,
QuestionaireID int,
LastPage int,
Paused int,
Complete int,
MaxPages int,
Sent int,
LastSaved int,
InitMail int,
ReceiveMail int,
UpdateDate datetime
)

INSERT INTO @.memQuestionaires VALUES ( 871, 10000000, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 872, 10000001, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 873, 10000002, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )

DECLARE @.QuestScore table
( QuestScoreID int,
memQuestionaireID int,
AnswerID int,
AnswerValue int,
SectionID int,
Page int,
QuestType int,
AnswerText int,
AnswerShort int,
Updatedate int,
DefaultValue int,
DateCompleted datetime
)

INSERT INTO @.QuestScore VALUES ( 4641, 871, 0, -9, 361, 1, 9, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4642, 872, 0, -5, 362, 1, 6, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4643, 873, 0, 0, 345, 1, 2, '', '', NULL, NULL, '2007/02/07 22:31' )

|||very clean example arnie.. i wonder how your desk looks like |||

Sorry i'm sending you guys on a wild goose chase like this, and thanks a lot Arnie, i'd buy you a case of beer if i knew where you were.

I'm really learning a lot from this!

I just thought that it might be easier, if i had to add the memQuestionaireID and SectionID together in the QuestScore table to get a single unique value and remove the duplicates based on the newer unique value according to the Date, i.e. Only removing the old dates.

Sorry about this, i only saw it now.

That way i'm only using a single table.

|||

Thanks,

And my desk is under the stuff somewhere, or so it was a few months ago...

And UPS serves Portland, OR! But it would be much more enjoyable to share, so if you can wrangle it, start lobbying management to let you come to the PASS (Professional Association for SQL Server) Conference in September. (http://sqlpass.org/)

|||

Cummon guys, i'm a newbie at SQL Server in South Africa and it's late at night and my CEO's say i can't go home until i get this problem sorted out.

Please could you guys help me out quick?

Eliminate top and bottom percent of records

I have a temp table that is being populated and below is a representation of the data.Basically, for each mile_code and rep combination there will be ‘X’ number of records.(In the example below there are 30 records.)What I need to do is eliminate the top and bottom 10% (This could be any percentage and I want to be able to change that easily if I have to.) of DeltaT for each mile_code and rep combination and then average the remaining values in DeltaT for each mile_code and rep combination.

I’ve tried a bunch of different approaches and none give me what I’m looking for.Any ideas or solutions?

mile_code DeltaT rep close_date

Approach 1 Coleman 6/5/2007 16:52

Approach 1 COLEMAN 6/5/2007 14:32

Approach 1 COLEMAN 6/5/2007 9:26

Approach 21 COLEMAN 5/31/2007 14:07

Approach 14 COLEMAN 5/31/2007 9:53

Approach 7 Coleman 5/30/2007 16:12

Approach 36 COLEMAN 5/29/2007 15:53

Approach 8 COLEMAN 5/29/2007 13:54

Approach 8 COLEMAN 5/29/2007 13:23

Approach 8 COLEMAN 5/29/2007 9:00

… (20 more records)

Interview 1 Coleman 6/5/2007 16:52

Interview 1 COLEMAN 6/5/2007 14:32

Interview 1 COLEMAN 6/5/2007 9:26

Interview 8 COLEMAN 5/29/2007 13:54

Interview 8 COLEMAN 5/29/2007 9:00

Interview 14 COLEMAN 5/25/2007 8:37

Interview 61 COLEMAN 5/24/2007 10:57

Interview 14 COLEMAN 5/23/2007 9:32

Interview 26 COLEMAN 5/18/2007 9:39

Interview 23 Coleman 5/14/2007 13:25

… (20 more records)

Followed by 30 each of the other 3 milecodes for Coleman

Approach 0 DOLAN 6/6/2007 9:27

Approach 1 DOLAN 6/5/2007 13:39

Approach 1 DOLAN 6/5/2007 12:20

Approach 1 DOLAN 6/5/2007 11:37

Approach 92 DOLAN 6/5/2007 11:12

Approach 91 DOLAN 6/5/2007 9:44

Approach 2 Dolan 6/4/2007 11:53

Approach 2 DOLAN 6/4/2007 11:11

Approach 5 Dolan 6/1/2007 16:07

Approach 177 DOLAN 6/1/2007 10:58

… (20 more records)

Interview 96 DOLAN 6/5/2007 16:38

Interview 167 DOLAN 6/5/2007 14:24

Interview 2 Dolan 6/4/2007 14:54

Interview 2 DOLAN 6/4/2007 11:11

Interview 5 Dolan 6/1/2007 16:07

Interview 27 DOLAN 6/1/2007 14:48

Interview 323 DOLAN 6/1/2007 13:49

Interview 15 Dolan 6/1/2007 11:48

Interview 224 DOLAN 5/31/2007 16:40

Interview 126 Dolan 5/30/2007 15:02

… (20 more records)

…

Here is one way.

Code Snippet

select *

from tb t1

where t1.deltat in (select top 90 percent t2.deltat from tb t2 where t2.mile_code=t1.mile_code order by t2.deltat asc)

and t1.deltat in (select top 90 percent t3.deltat from tb t3 where t3.mile_code=t1.mile_code order by t3.deltat desc)

|||

What should be the criteria to eliminate the top and bottom 10 % of [DeltaT], other than [mile_code] and [rep]?

Should we sort by [mile_code], [rep] and [DeltaT] and then eliminate the top and bottom 10%?

AMB

|||I thought of this on the way to work this morning and realized the way I described what I wanted was incorrect. If the data is sorted by DeltaT for each mile_code and rep combination then in this example (30 records for each group and 10% gone from top to bottom) the first 3 records and last 3 records should be removed. So it doesn't matter what the values of DeltaT are. If there were fractions caused by the number of records chosen and/or the percentage then I'd want it to just round to the nearest whole number.|||

Try:

Code Snippet

createtable #t (

mile_code varchar(25)notnull,

rep varchar(25)notnull,

deltat intnotnull

)

go

declare @.i int, @.j int

declare @.mile_code varchar(25)

declare @.rep varchar(25)

set @.j = 1

while @.j <= 2

begin

set @.rep =

case

when @.j = 1 then'COLEMAN'

when @.j = 2 then'DOLAN '

end

set @.i = 1

while @.i <= 60

begin

set @.mile_code =

case

when @.i between 1 and 30 then'Approach'

when @.i between 31 and 60 then'Interview'

end

insertinto #t values(@.mile_code, @.rep,(abs(checksum(newid()))% 100)+ 1)

set @.i = @.i + 1

end

set @.j = @.j + 1

end

go

declare @.percent int

set @.percent = 10

;with cte

as

(

select

mile_code,

rep,

deltat,

((row_number()over(partitionby mile_code, rep orderby deltat))* 100.00)/(count(*)over(partitionby mile_code, rep))as pct

from

#t

)

select

mile_code,

rep,

avg(deltat)as avg_deltat

from

cte

where

pct between @.percent and (100.00 - @.percent)

groupby

mile_code,

rep

orderby

mile_code,

rep

go

droptable #t

go

AMB

|||

I am not perfectly clear about what you are trying to do, but here is a solution that removes the top 10 percent using a CTE and a couple of aggregates. Note this will only work in 2005...

;with rowCounts as (

select row_number() over (partition by mile_code,rep order by closeDate) as rowNum, --gives a row number per group, ordered by close date
count(*) over (partition by mile_code,rep) as totalRows, --gets total rows by mile_code and rep. This is how I do the work later
mile_code,rep, deltaT, closeDate
from #t) --this is the same as his #t, plus the close date. I will repeat it after this explanation

select mile_code,rep, deltaT, closeDate, rowNum, totalRows
from rowCounts
where rowNum > totalRows * (@.percent / 100.0) --removes the bottom 10 percent
and rowNum < totalRows - (totalRows * (@.percent / 100.0)) -- removes the top

The whole code (thanks to hunchback):

set nocount on
create table #t (
mile_code varchar(25) not null,
rep varchar(25) not null,
deltat int not null,
closeDate datetime
)
go
declare @.i int, @.j int
declare @.mile_code varchar(25)
declare @.rep varchar(25)
declare @.closeDate datetime
set @.j = 1
while @.j <= 2
begin
set @.rep =
case
when @.j = 1 then 'COLEMAN'
when @.j = 2 then 'DOLAN '
end
set @.i = 1

while @.i <= 60
begin
set @.mile_code =
case
when @.i between 1 and 22 then 'Approach'
when @.i between 23 and 60 then 'Interview'
end
insert into #t values(@.mile_code, @.rep, (abs(checksum(newid())) % 100) + 1,
dateadd(day,(abs(checksum(newid())) % 100) + 1,'20070101'))
set @.i = @.i + 1
end
set @.j = @.j + 1
end

go

declare @.percent int
set @.percent = 10
;with rowCounts as (
select row_number() over (partition by mile_code,rep order by closeDate) as rowNum,
count(*) over (partition by mile_code,rep) as totalRows,
mile_code,rep, deltaT, closeDate
from #t)
select mile_code,rep, deltaT, closeDate, rowNum, totalRows
from rowCounts
where rowNum > totalRows * (@.percent / 100.0)
and rowNum < totalRows - (totalRows * (@.percent / 100.0))

drop table #t

go

|||here's another alternative,

again, thanks to hunchback for the test data

create table #t (
mile_code varchar(25) not null,
rep varchar(25) not null,
deltat int not null
)
go

declare @.i int, @.j int
declare @.mile_code varchar(25)
declare @.rep varchar(25)
set @.j = 1

while @.j <= 2
begin
set @.rep =
case
when @.j = 1 then 'COLEMAN'
when @.j = 2 then 'DOLAN '
end

set @.i = 1

while @.i <= 60
begin
set @.mile_code =
case
when @.i between 1 and 30 then 'Approach'
when @.i between 31 and 60 then 'Interview'
end

insert into #t values(@.mile_code, @.rep, (abs(checksum(newid())) % 100) + 1)
set @.i = @.i + 1
end

set @.j = @.j + 1
end
go

SELECT IDENTITY(int,1,1) AS row_number
, *
INTO #a
FROM #t
ORDER BY
mile_code
, rep
, deltat

DECLARE @.percnt int
SET @.percnt = 10

SELECT a.mile_code
, a.rep
, a.deltat
, row_number - minrow AS grp_row_number
, maxrow * (@.percnt/100.0) as countpergrp
FROM #a a INNER JOIN
(
SELECT mile_code
, rep
, MIN(row_number) - 1 AS minrow
, COUNT(*) AS maxrow
FROM #a
GROUP BY
mile_code
, rep
) b on a.mile_code = b.mile_code
and a.rep = b.rep
WHERE row_number - minrow between (maxrow * (@.percnt/100.0)) and (maxrow - (maxrow * (@.percnt/100.0)))

DROP TABLE #t
DROP TABLE #a
GO|||

Thanks everyone. Still have a little more work to do to get the final result before putting it into producton (Have to add some other calculations but will do that by joining in another temp table or something.) but all of your input helped a lot. Here is what I ended up with thanks to your help:

DECLARE @.percent int,

@.recentindex int

SET @.percent = 10

SET @.recentindex = 45

Then crate temp table #miledata to get the last 'x' number of records for each rep in each mile_code that they have completed. Do this based off of the @.recentindex variable.

Then use either of the following to get results. Haven't decided which I'll put into production yet.

OPTION A

/*This creates a common table expression to run our aggregate query against.*/

;WITH cte_AveRecords AS

(

SELECT row_number() OVER(partition BY mile_code, rep ORDER BY deltaT) AS rowNum, --gives a row number per group, ordered by deltaT

COUNT(*) OVER (partition BY mile_code, rep) AS totalRows, --gets total rows by mile_code and rep.

mile_code,

rep,

deltaT,

recentindex

FROM#miledata

)

/*This deterimines the average per mile_code and rep by selecting the rows that fall above the low percent and less than or equal to the upper percent.*/

SELECT c.mile_code,

c.rep,

CAST(AVG(CAST(a.deltaT AS Decimal(5,1))) AS Decimal(5,1)) AS AvgTime

FROM

(

SELECT mile_code, rep, deltaT, recentindex, rowNum, totalRows

FROM cte_AveRecords

WHERE rowNum > totalRows * (@.percent / 100.0) --removes the bottom 'x' percent

AND rowNum < totalRows - (totalRows * (@.percent / 100.0)) -- removes the top

)a

JOIN cte_AveRecords c ON a.rep = c.rep AND a.mile_code = c.mile_code

GROUP BY c.mile_code,

c.rep

ORDER BY c.rep,

c.mile_code

OPTION B

/*This creates a common table expression to run our aggregate query against.*/

;WITH cte_AveRecords AS

(

SELECT

mile_code,

rep,

deltat,

/*This assigns a percentage to each row number in the mile_code and rep group.See explaination A below*/

((row_number() over(partition by mile_code, rep order by deltat)) * 100.00) / (count(*) over(partition by mile_code, rep)) as pct

FROM #miledata

)

/*This deterimines the average per mile_code and rep based on what we populated the CTE with..*/

SELECT mile_code,

rep,

CAST(AVG(CAST(deltat AS Decimal(5,1))) AS Decimal(5,1)) AS avg_deltat

FROM cte_AveRecords

WHERE pct > @.percent and pct < (100.00 - @.percent)

GROUP BY mile_code,

rep

ORDER BY rep,

mile_code

GO

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Tex
tdata),217)
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regardsDid you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Tex
tdata),217) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx
.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Tex
tdata),217)
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards|||Thanks Vinod

>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techin...ctdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:17fe001c44a19$f48dfb80$a301280a@.phx
.gbl...
substring[vbcol=seagreen]
exec%',Textdata),217)[vbcol=seagreen]
>
>.
>

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217 )
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regards
Did you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217 ) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Textdata),217 )
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards
|||Thanks Vinod

>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinf...tdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
substring[vbcol=seagreen]
exec%',Textdata),217)
>
>.
>

Eliminate records

Hello,
The output of the following query returns two identical
records, i need to eliminate all records that are
identical but if i use the "distinct" before the substring
function its returned one error.
This is the query that i'm using:
select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217)
as [TextData],
objid
into DestinationT
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star'
Thanks,
Best regardsDid you try using:
Select DISTINCT Derived.* into DestinationT FROM
( select EvClassDesc,
STime,
substring(Textdata,patindex('%exec%',Textdata),217) as [TextData],
objid
from OriginalT
where objid = 1111111 and EvClassDesc = 'SP:Star') Derived
... (untested)
WIll this help?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
> Hello,
> The output of the following query returns two identical
> records, i need to eliminate all records that are
> identical but if i use the "distinct" before the substring
> function its returned one error.
> This is the query that i'm using:
> select EvClassDesc,
> STime,
> substring(Textdata,patindex('%exec%',Textdata),217)
> as [TextData],
> objid
> into DestinationT
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star'
> Thanks,
> Best regards|||Thanks Vinod
>--Original Message--
>Did you try using:
>Select DISTINCT Derived.* into DestinationT FROM
>( select EvClassDesc,
> STime,
> substring(Textdata,patindex('%
exec%',Textdata),217) as [TextData],
> objid
> from OriginalT
> where objid = 1111111 and EvClassDesc = 'SP:Star')
Derived
>... (untested)
>WIll this help?
>--
>HTH,
>Vinod Kumar
>MCSE, DBA, MCAD, MCSD
>http://www.extremeexperts.com
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:17fe001c44a19$f48dfb80$a301280a@.phx.gbl...
>> Hello,
>> The output of the following query returns two identical
>> records, i need to eliminate all records that are
>> identical but if i use the "distinct" before the
substring
>> function its returned one error.
>> This is the query that i'm using:
>> select EvClassDesc,
>> STime,
>> substring(Textdata,patindex('%
exec%',Textdata),217)
>> as [TextData],
>> objid
>> into DestinationT
>> from OriginalT
>> where objid = 1111111 and EvClassDesc = 'SP:Star'
>> Thanks,
>> Best regards
>
>.
>

Eliminate Duplicate Records

Hi

How can i eliminate duplicate records from a single table?

There is a surrogate key in the table and i just can't seem to wrap my head around the layout.

ID | MemberID | Name | Surname | DateOfAssessment | etc.

The ID is unique, but there are duplicate memberID's with different date of assessments, i want to take the last used date of assessment and keep only that one, and remove the rest from the table?

I'm sure the query is simple, i'm just too stupid to see how to solve it!!

Any help would be greatly appreciated.

Kind Regards
Carel Greaves

This should give you an idea of one method to accomplish your goal.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( [ID] int IDENTITY,
MemberID int,
[Name] varchar(20),
SurName varchar(20),
DateOfAssessment datetime
)

INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/25' )
INSERT INTO @.MyTable VALUES ( 3, 'Mary', 'Smith', '2007/05/26' )
INSERT INTO @.MyTable VALUES ( 4, 'Susy', 'Williams', '2007/05/22' )
INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/05/26' )
INSERT INTO @.MyTable VALUES ( 2, 'Bill', 'Jones', '2007/04/26' )

SELECT *
FROM @.MyTable

DELETE @.MyTable
FROM @.MyTable m
JOIN (SELECT
MemberID,
DateOfAssessment = max( DateOfAssessment )
FROM @.MyTable
GROUP BY MemberID ) dt
ON ( m.MemberID = dt.MemberID
AND m.DateOfAssessment <> dt.DateOfAssessment
)

SELECT *
FROM @.MyTable

ID MemberID Name SurName DateOfAssessment
--
2 3 Mary Smith 2007-05-26 00:00:00.000
3 4 Susy Williams 2007-05-22 00:00:00.000
4 2 Bill Jones 2007-05-26 00:00:00.000

|||
Thanks you very much.

Eliminate duplicate records

department table got duplicate data......

when i insert data from department to employee table, i want to eliminate duplicate and insert unique data.

department

emp_id emp_name emp_address

10 mary melville,ny

10 mary longisland,ny

11 linsy sugarland,tx

12 sam fairfax,va

12 sam dice,va

i want result in employee table....How can i get it with fastest query...i got tons of record to insert.

emp_id emp_name emp_address

10 mary melville,ny

11 linsy sugarland,tx

12 sam fairfax,va

insert into employee(emp_id,emp_name,emp_address)
select emp_id, max(emp_name), max(emp_address)
from department

where emp_id is not null

If you are using SQL Server 2005, give a look to the ROW_NUMBER() functon in books online. One technique is to create a "sequence number" for each grouping using the ROW_NUMBER() function and to select the records that have a generated "sequence number" of 1.

Also, something like this can be done:

Code Snippet

declare @.mockup table
( emp_id integer,
emp_name varchar(10),
emp_address varchar(25)
)
insert into @.mockup
select 10, 'mary', 'melville,ny' union all
select 10, 'mary', 'longisland,ny' union all
select 11, 'linsy', 'sugarland,tx' union all
select 12, 'sam', 'fairfax,va' union all
select 12, 'sam', 'dice,va'
--select * from @.mockup

select emp_id,
emp_name,
( select max(emp_address)
from @.mockup b
where a.emp_id = b.emp_id
) as emp_address
from @.mockup a
group by emp_id, emp_name

/*
emp_id emp_name emp_address
-- - -
10 mary melville,ny
11 linsy sugarland,tx
12 sam fairfax,va
*/

|||

For SS2005:

Code Snippet

create table #dept( emp_id int, emp_name varchar(50), emp_address varchar(50) )

insert into #dept

select 10, 'mary', 'melville,ny'

union all select 10, 'mary', 'longisland,ny'

union all select 11, 'linsy', 'sugarland,tx'

union all select 12, 'sam', 'fairfax,va'

union all select 12, 'sam', 'dice,va'

select emp_id, emp_name, emp_address

from

(

select *, row_number() over (partition by emp_id order by emp_id, emp_name desc, emp_address desc) as rn

from #dept

) emp

where rn = 1

For SS2000:

Code Snippet

select emp_id, max(emp_name) as emp_name, max(emp_address) as emp_address

from #dept

group by emp_id

|||

The trick here is to get a list of the records you want to keep. However, using MAX on character fields isn't the way to go (unless you don't care which record to keep). Hopefully you've got a column which gives some sort of order to the data (eg datecreated) so you can choose the most recent one, otherwise its a guessing game as to which one is "keepable"

So assuming you have one... ;-)

INSERT INTO employee (emp_id, name, emp_address)

SELECT d.emp_id, d.name, d.emp_address
FROM department d

INNER JOIN

(SELECT emp_id, MAX(datecreated) AS dt

FROM department

GROUP BY emp_id) AS dist

ON d.emp_id = dist.emp_id and d.datecreated = dist.dt

HTH!

|||

when i use max function....i get this error....

i get an error like this.....
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

|||When you try the insert? Check that your column definitions in the department and employee tables match. You may have a smaller field in the destination table...|||

The problem isn't with max(), it's a mismatch between column definitions in your source and your target.

|||

Getting this error...

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'datecreated'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'datecreated'.

|||

I guess you don't have a column matching that in your table ;-)

My example was based upon the presumption that you had a field such as that in your table. You won't be able to just copy and paste it and get results. It was based upon the following DDL

CREATE TABLE departments
(emp_id int, emp_name varchar(100), emp_address varchar(100), datecreated datetime)

Basically, if you do have a field (in my example datecreated) that tells you which record is the one you want to keep then you can use a variation on my example. If you don't have one and you're happy for the choice to be made based arbitarilly then go with Dales option. His can be used straight out of the box.

Sorry for any confusion.

|||

Is there any internal field of table record i can use?.

like in oracle, do we have system variable/ records created along with insert of data in a row in sql server?.

so can i use that date created field?.

|||

I'm afraid not. If you don't have an explicit date field you can't use my example.

If the ddl of your table is the same as in Dales example, you should go with one of his excellent suggestions.

|||

i used dale solution...max thing.

select emp_id, max(emp_name) as emp_name, max(emp_address) as emp_address

from #dept

group by emp_id

works fine today.

Efficiently Inserting 1 Million records

I have an app that needs to insert 1 million records into a table. The tabl
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:

> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan

Efficient way to transfer huge amount of records

Hi All,

I used a data flow task, and when trying to transfer data from a OLE DB Source (records ~ 75 lac) to a destination OLE DB Source, SSIS fails at the middle giving an error saying the Transaction log got filled, try again after clearing the same.

My query is what is the most efficient way to transfer say records more than 50 lac ensuring that it doesn't fail in the middle?

Thanks in Advance,

Mithun.

To avoid the error you need to increase transaction log size in SQL Server - it is SQL's transaction log, not SSIS'. I believe you can also decrease the batch size in OLE DB destination properties.

The most efficient way is SQL Destination - if the destination server is on the same machine where package runs, it is faster than OLE DB Destination.|||

Thanks for the comments!! I'll try out the options.

|||

Michael,

But if you need to copy from .xls source into a table you can't use Sql Destination.

Correct me if I'm going wrong.

|||

enric vives wrote:

Michael,

But if you need to copy from .xls source into a table you can't use Sql Destination.

Correct me if I'm going wrong.

That's wrong. The source of the data is irrelevant. There are some pre-requisites to using SQL Server Destination and the April 2006 drop of BOL tells you all you need to know.

-Jamie