Migrating to SQL 2005: table

Showing posts with label table. Show all posts

Wednesday, March 28, 2012

Mirrored Records?

Hi I need some SQL script help. Need script to delete all table rows that
are duplicates in mirror image. Table has 2 columns, ColumnA and ColumnB.
Row1: ColumnA = x, ColumnB = y
Row2: ColumnA = y, ColumnB = x
Those 2 rows are exactly the same for me. Need a script that will delete
Row2 and all other rows where they're mirrored duplicates.
Thanks DarenDaren,
Once you do this, you should consider putting a constraint on the
table to enforce ColumnA <= ColumnB so this doesn't happen again.
It's a bad model if there are two ways of representing the same facts.
delete from T as T1
where ColumnA > ColumnB
and exists (
select * from T as T2
where T2.ColumnA = T1.ColumnB
and T2.ColumnB = T1.ColumnA
)
If you want to delete only these "mirror duplicates"
when they exist for the same customer/transaction/whatever,
you will need something like
delete from T as T1
where ColumnA > ColumnB
and exists (
select * from T as T2
where T2.customer = T1.customer
and T2.ColumnA = T1.ColumnB
and T2.ColumnB = T1.ColumnA
)
Steve Kass
Drew University
Daren Hawes wrote:

>Hi I need some SQL script help. Need script to delete all table rows that
>are duplicates in mirror image. Table has 2 columns, ColumnA and ColumnB.
>Row1: ColumnA = x, ColumnB = y
>Row2: ColumnA = y, ColumnB = x
>Those 2 rows are exactly the same for me. Need a script that will delete
>Row2 and all other rows where they're mirrored duplicates.
>Thanks Daren
>
>|||Thanks. I am a little

with T and T2
Do I need to create a new Table? I have added the actual names below.
Column a = FromID ; Column b = ToID
CREATE TABLE [dbo].[tbl_Matrix] (
[FareID] [int] IDENTITY (1, 1) NOT NULL ,
[FromID] [int] NULL ,
[ToID] [int] NULL ,
[PriceCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Matrix] ADD
CONSTRAINT [PK_tbl_Matrix] PRIMARY KEY CLUSTERED
(
[FareID]
) ON [PRIMARY]
GO
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23eKPQBxZFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Daren,
> Once you do this, you should consider putting a constraint on the
> table to enforce ColumnA <= ColumnB so this doesn't happen again.
> It's a bad model if there are two ways of representing the same facts.
> delete from T as T1
> where ColumnA > ColumnB
> and exists (
> select * from T as T2
> where T2.ColumnA = T1.ColumnB
> and T2.ColumnB = T1.ColumnA
> )
> If you want to delete only these "mirror duplicates"
> when they exist for the same customer/transaction/whatever,
> you will need something like
> delete from T as T1
> where ColumnA > ColumnB
> and exists (
> select * from T as T2
> where T2.customer = T1.customer
> and T2.ColumnA = T1.ColumnB
> and T2.ColumnB = T1.ColumnA
> )
> Steve Kass
> Drew University
>
> Daren Hawes wrote:
>|||Try the following untested DELETE statement:
DELETE FROM tbl_Matrix
WHERE EXISTS
(SELECT *
FROM tbl_Matrix AS T
WHERE T.fromid = tbl_Matrix.fromid
AND T.toid = tbl_Matrix.toid
AND T.fareid < tbl_Matrix.fareid)
Now make FromID and ToID not nullable and add a unqiue constraint on those
two columns.
David Portas
SQL Server MVP
--|||Thanks for the reply Dave (I'm working with Daren),
Tryed that 1, deleted nothing, modified it to this...
DELETE FROM tbl_Matrix
WHERE EXISTS
(SELECT *
FROM tbl_Matrix AS T
WHERE T.fromid = tbl_Matrix.toid
AND T.toid = tbl_Matrix.fromid)
...and it deleted everything.
This is a copy of the script we've used to create the table's data, perhaps
if we wrote this script a bit better, wouldn't need another script to clean
it up...
declare @.Counter int
declare @.Counter2 int
select @.Counter=1
select @.Counter2=1
while @.Counter < 136
begin
while @.Counter2 < 136
Begin
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
set @.Counter2 = @.Counter2 + 1
End
set @.Counter = @.Counter + 1
set @.Counter2 = 1
end
Regards,
Offal Eater
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
> Try the following untested DELETE statement:
> DELETE FROM tbl_Matrix
> WHERE EXISTS
> (SELECT *
> FROM tbl_Matrix AS T
> WHERE T.fromid = tbl_Matrix.fromid
> AND T.toid = tbl_Matrix.toid
> AND T.fareid < tbl_Matrix.fareid)
> Now make FromID and ToID not nullable and add a unqiue constraint on those
> two columns.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for the reply Dave (I'm working with Daren),
Not having any luck with these scripts, I've included the script we've used
to create the table. Perhaps if we wrote this differently, wouldn't have
need for the 'clean-up' script.
declare @.Counter int
declare @.Counter2 int
select @.Counter=1
select @.Counter2=1
while @.Counter < 136
begin
while @.Counter2 < 136
Begin
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
set @.Counter2 = @.Counter2 + 1
End
set @.Counter = @.Counter + 1
set @.Counter2 = 1
end
Any feedback would be great.
Thx,
Offal Eater
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
> Try the following untested DELETE statement:
> DELETE FROM tbl_Matrix
> WHERE EXISTS
> (SELECT *
> FROM tbl_Matrix AS T
> WHERE T.fromid = tbl_Matrix.fromid
> AND T.toid = tbl_Matrix.toid
> AND T.fareid < tbl_Matrix.fareid)
> Now make FromID and ToID not nullable and add a unqiue constraint on those
> two columns.
> --
> David Portas
> SQL Server MVP
> --
>|||T1 and T2 are table aliases. You need to compare each
row of T with the rest of T, and to distinguish the "this row"
table and the "rest of" table, you need the aliases:
Since FareID is the primary key, this will do
delete from tbl_Matrix as T1
where ToID > FromID
and exists (
select * from tbl_Matrix as T2
where T2.FromID = T1.ToID
and T2.ToID = T1.FromID
)
This will arbitrarily remove the one of the two mirror duplicates
for which ToID < FromID. If you want to remove, say, the
one with the smaller FareID, you could do this instead:
delete from tbl_Matrix as T1
where exists (
select * from tbl_Matrix as T2
where T2.FromID = T1.ToID
and T2.ToID = T1.FromID
and T2.FareID > T1.FareID
)
Seeing your table now, I'd recommend you change the primary
key to (FromID, ToID), and add a table constraint to enforce
FromID < ToID, or at least put a UNIQUE constraint on
(FromID, ToID), and make those two columns NOT NULL (if
not all columns). I don't see much use to the column FareID,
actually, but removing it might mess up other thing you rely on.
There could be other problems, too. You could have many rows
with the same FromID, ToID pair in the same order, but with
different FareID values. So you might want to do this:
delete from tbl_Matrix as T1
where exists (
select * from tbl_Matrix as T2
where (
T2.FromID = T1.ToID
and T2.ToID = T1.FromID
) or (
T2.FromID = T1.FromID
and T2.ToID = T1.ToID
)
and T2.FareID > T1.FareID
)
If you have rows where FromID = ToID, you can delete them
separately. I assume those would make no sense, but you have
nothing to prevent that from occurring right now.
SK
Daren Hawes wrote:

>Thanks. I am a little

with T and T2
>Do I need to create a new Table? I have added the actual names below.
>Column a = FromID ; Column b = ToID
>CREATE TABLE [dbo].[tbl_Matrix] (
> [FareID] [int] IDENTITY (1, 1) NOT NULL ,
> [FromID] [int] NULL ,
> [ToID] [int] NULL ,
> [PriceCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[tbl_Matrix] ADD
> CONSTRAINT [PK_tbl_Matrix] PRIMARY KEY CLUSTERED
> (
> [FareID]
> ) ON [PRIMARY]
>GO
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23eKPQBxZFHA.2212@.TK2MSFTNGP14.phx.gbl...
>
>
>|||> Tryed that 1, deleted nothing, modified it to this...
I ran your script and it didn't generate any duplicates so there is nothing
to delete - the combination of (FromID, ToID) is already unique (18225
rows). If you have something different in your data then please post a few
INSERT statements to generate some sample data that we can use to test it
out.

> This is a copy of the script we've used to create the table's data,
> perhaps if we wrote this script a bit better, wouldn't need another script
> to clean it up...
I posted a different solution to this in reply to Daren's post earlier
today.
David Portas
SQL Server MVP
--|||Here's a much better way to create the table. It uses
just one insert and no loops, and has much less chance of
off-by-one errors:
-- create a temporary table full of integers
declare @.Ints table (
IntVal int primary key
)
insert into @.Ints
select OrderID-10247
from Northwind..Orders
where OrderID-10247 between 1 and 136
-- Insert everything at once:
insert into dbo.tbl_Matrix(FromID, ToID)
select N1.IntVal, N2.IntVal
from @.Ints as N1 join @.Ints as N2
on N1.IntVal between 1 and 136
and N2.IntVal between N1.IntVal and 136
-- or N2.IntVal between N1.IntVal+1 and 136
If you want loops, but no mirror duplicates, replace
set @.Counter2 = 1
with
set @.Counter2 = @.Counter
This will continue to give you rows where FromID = ToID. If you don't
want those, start @.Counter2 one higher. Here's a guess, cleaned up
a bit, but I'd never trust this as much as the first suggestion I gave.
declare @.Counter int
declare @.Counter2 int
select @.Counter=0
while @.Counter < 135
begin
set @.Counter = @.Counter + 1
set @.Counter2 = @.Counter
while @.Counter2 < 135
Begin
set @.Counter2 = @.Counter2 + 1
Insert into dbo.tbl_Matrix (FromID,ToID)
Values (@.Counter,@.Counter2)
End
end
SK
Offal Eater wrote:

>Thanks for the reply Dave (I'm working with Daren),
>Not having any luck with these scripts, I've included the script we've used
>to create the table. Perhaps if we wrote this differently, wouldn't have
>need for the 'clean-up' script.
>declare @.Counter int
>declare @.Counter2 int
>select @.Counter=1
>select @.Counter2=1
>while @.Counter < 136
>begin
> while @.Counter2 < 136
> Begin
> Insert into dbo.tbl_Matrix (FromID,ToID)
> Values (@.Counter,@.Counter2)
> set @.Counter2 = @.Counter2 + 1
> End
>set @.Counter = @.Counter + 1
>set @.Counter2 = 1
>end
>Any feedback would be great.
>Thx,
>Offal Eater
>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>news:xZ6dndpRhvr8_gPfRVn-sw@.giganews.com...
>
>
>|||David,
I don't think the OP wants both (a, b) and (b, a). The 18225 rows
from the script are all (a,b) where a and b are between 1 and 135.
One way to eliminate the "mirror duplicates" is to generate only pairs
where a <= b (or perhaps a < b). There should be either 9180 or
9045 rows, depending on whether = is included.
SK
David Portas wrote:

>I ran your script and it didn't generate any duplicates so there is nothing
>to delete - the combination of (FromID, ToID) is already unique (18225
>rows). If you have something different in your data then please post a few
>INSERT statements to generate some sample data that we can use to test it
>out.
>
>
>I posted a different solution to this in reply to Daren's post earlier
>today.
>
>

Monday, March 26, 2012

Mirror a view to a table

I would like to replicate a single view to a table that is stored on another db server (connected as linked server object).
Is there a way to imitate the behavior of a trigger (insert, delete, update) for a view?
I could assign the triggers to the table that provides the primary key.
So I could handle insert, delete events.
But what about updates that affect row in other tables that are used in this view?

Code Snippet

CREATE TRIGGER mirror_tableA_insert
ON [TESTDB].[dbo].[tableA]
FOR INSERT
AS
BEGIN
set nocount on
SET XACT_ABORT ON
set REMOTE_PROC_TRANSACTIONS off
INSERT INTO OPENQUERY(TESTLINKED, 'SELECT * FROM tableA')

SELECT *
FROM [TESTDB].[dbo].[myView] orig
INNER JOIN inserted i
ON i.prim = orig.prim

END

Thanks in advance for any hints!

Marcus

Use replication service instead of using the Trigger.

|||

It is not totally clear what you are attempting to accomplish.

Why are you using OPENQUERY instead of a LinkedServer?

And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

|||

Arnie Rowland wrote:

It is not totally clear what you are attempting to accomplish.
Why are you using OPENQUERY instead of a LinkedServer?
And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

I would like to replicate this view to a MySQL database that is used for a website.

Basically it's data synchronization job. So all dml statements that affect this view should trigger a procedure that synchronizes the MySQL table with the view on the SQLServer.

Yes I managed to set up a linked server for the MySQL DB through the MyODBC 3.51 driver.
First I tried to copy all the rows in this view to a MySQL table using a INSERT INTO OPENQUERY statement.

But a trigger can't be added to a view.

Manivannan.D.Sekaran wrote:
Use replication service instead of using the Trigger.

Yes the replication service would be the way to go when the target server (subscriber) would be a DB2 or Oracle database. But my target datatbase is MySQL (using it for a website).
Maybe it's possible to define this OLE DB data source as subscriber?

|||

You can't do directly, but there are some thrid party tools available. C-JDBC: Clustered JDBC is one of the tool.(http://c-jdbc.objectweb.org/)

|||

Marc Cicero wrote:

But a trigger can't be added to a view.

As I wrote earlier, you may wish to explore an INSTEAD OF TRIGGER.

An INSTEAD OF TRIGGER can work on a VIEW.

Minute table?

I got no responses to the 'Complex query' thread, which probably
contained too much information. I'll try simplifying it:
I have 2 tables
create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
smalldatetime, MI_RequiredStaff tinyint)
this table contains the required staff between certain time.
MI_RequiredStaff is the column that contains the number of employees
required at that time.
create table PlannedWork (PW_ID int, PW_Date smalldatetime,
PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
this table contains times when an employee will work.
Now I'm looking for a query which can give me the Minima which aren't
fullfilled for a certain day. Of course if an employee is used to fill
a certain minima he/she can't be used for another one.
I have no clue on how to begin with this. But I was thinking maybe
it's possible to use something like a minute-table (like you have a
table with all the days). I don't know if that has ever been used and
if it's appropriate for this.
Thanks in advance,
Stijn Verrept.Stijn,
There is no 'easy' fix for this. I feel you need to identify the smallest
period of time to use as slots, unless you have already defined this in the
XX_BeginTime and XX_EndTime inasmuch that such times will always match for
any time of the day (e.g. a standard 3 shift system commencing at 0700 and
running a stright 8 hours each). Any such system will need to be the same fo
r
both the employee and required time slots.
Once identified, you can create a temp table of the required slots for a
given period of time (be it a single shift, day, w

, etc) and then update
those records with the counts of staff matching that shift.
Example: I use a straight 3 shift system of 0700-1500, 1500-2300 and
2300-0700.
Day 1 requires 3,5,2 (respectively).
The Temp table will have Date, Start, End, Required, Planned columns.
The Date, Start, End, Required columns are populated immediately from the
Minima table, and then each row is updated with the count of Employtees
matching those times/dates from the PlannedWork table.
The final act is to calculate against the Required and Planned columns to
determine overages or shortfalls in the numbers.
The whole code would be contained in a Stored Procedure with the relevent
Params to determine any period of time.
This is a *very* basic and simplified version of what you may find to the a
solution, but you could find it beneficial to break the problem down into
steps as above to assist you.
Hope this assists,
Tony
"Stijn Verrept" wrote:

> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
>|||Stijn Verrept wrote:
> I got no responses to the 'Complex query' thread, which probably
> contained too much information. I'll try simplifying it:
> I have 2 tables
> create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime
> smalldatetime, MI_RequiredStaff tinyint)
> this table contains the required staff between certain time.
> MI_RequiredStaff is the column that contains the number of employees
> required at that time.
> create table PlannedWork (PW_ID int, PW_Date smalldatetime,
> PW_BeginTime smalldatetime, PW_EndTime smalldatetime, MI_StaffID int)
> this table contains times when an employee will work.
> Now I'm looking for a query which can give me the Minima which aren't
> fullfilled for a certain day. Of course if an employee is used to fill
> a certain minima he/she can't be used for another one.
> I have no clue on how to begin with this. But I was thinking maybe
> it's possible to use something like a minute-table (like you have a
> table with all the days). I don't know if that has ever been used and
> if it's appropriate for this.
> --
> Thanks in advance,
> Stijn Verrept.
Hi Stijn,
No keys at all in your tables? Is every column really nullable or do
you expect us to guess? Surely you could do better...
Assuming you add some sensible keys and constraints and assuming you
don't allow PlannedWork for any employee to overlap (easily prevented
with a trigger), try something like the following query.
I don't know why you would put date and time in separate columns so
I've ignored PW_Date altogether.
SELECT mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
SUM(DATEDIFF(MINUTE,work_start,work_end)
) AS work_time
FROM
(SELECT M.mi_id, M.mi_begintime, M.mi_endtime, M.mi_requiredstaff,
mi_requiredstaff*DATEDIFF(MINUTE, mi_begintime, mi_endtime)
AS required_time,
CASE WHEN M.mi_begintime > P.pw_begintime
THEN M.mi_begintime ELSE P.pw_begintime END AS work_start,
CASE WHEN M.mi_endtime < P.pw_endtime
THEN M.mi_endtime ELSE P.pw_endtime END AS work_end
FROM Minima AS M
LEFT JOIN PlannedWork AS P
ON M.mi_begintime < P.pw_endtime
AND M.mi_endtime > P.pw_begintime) AS T
GROUP BY mi_id, mi_begintime, mi_endtime, mi_requiredstaff,
required_time
HAVING SUM(DATEDIFF(MINUTE,work_start,work_end)
)< required_time ;
However, this won't prevent an employee being allocated to more than
one Minima. To do that I think you will have to pre-allocate employees
to each Minima on a best-fit basis. I don't think you'll be able to
find the best-fits using only declarative SQL because I'm pretty
certain this is an NP-complexity problem - I think there's even a
mathematical name for it.
I discussed a logically similar best-fit problem in the following
thread. My solution could help you get an arbitrary match between
Minima and PlannedWork but an arbitrary match I'd suggest isn't what
you are looking for. I guess you'll need to build a table of possible
assignments and then pull out the minimum cost ones. If you post some
sample data someone may be able to help further.
http://groups.google.co.uk/group/co...2c682dab331565c
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Tony Scott wrote:

> There is no 'easy' fix for this. I feel you need to identify the
> smallest period of time to use as slots, unless you have already
> defined this in the XX_BeginTime and XX_EndTime inasmuch that such
> times will always match for any time of the day (e.g. a standard 3
> shift system commencing at 0700 and running a stright 8 hours each).
> Any such system will need to be the same for both the employee and
> required time slots.
This could indeed simplify things (if the employee and required slots
are the same. I'm going to ask if this will be the case.

> This is a very basic and simplified version of what you may find to
> the a solution, but you could find it beneficial to break the problem
> down into steps as above to assist you.
True, thanks a lot!
Stijn Verrept.|||David Portas wrote:

> No keys at all in your tables? Is every column really nullable or do
> you expect us to guess? Surely you could do better...
Indeed I can, and I also did (see the 'complex query' thread) however I
got no replies there and now I get 2. Sometimes it's better to provide
less info ;). The tables are of course completely fictional, just to
give the general idea of the problem.

> However, this won't prevent an employee being allocated to more than
> one Minima.

> To do that I think you will have to pre-allocate employees
> to each Minima on a best-fit basis. I don't think you'll be able to
> find the best-fits using only declarative SQL because I'm pretty
> certain this is an NP-complexity problem - I think there's even a
> mathematical name for it.
Thank you very much, I'm going to look up that NP-complexity problem
(which sounds complex ;). And look into the thread you provided! The
possible assignments indeed seems a good idea, the benefit is I would
only need to do this for the Minima and the ServiceGratings which will
speed up the final query.
Thanks again,
Stijn Verrept.|||No problem at all.
Just as a sideline to this, if you should find that the 'slots' do not
match, then you may be forced to use 'compromise' or 'lowest denominator'
slots, those being the smallest unit of time that can fit within both sets o
f
data. Example would be that if the Employees and shifts always start on the
hour and work in hours, then the unit would be hours, and you would then nee
d
to use a single row per hour. If the employees or shifts could start on the
half-hour, then that would be the unit of measure.
I would be interested in your findings and final solution, as this is the
type of problem I deal with day-in and day-out
Tony
"Stijn Verrept" wrote:

> Tony Scott wrote:
>
> This could indeed simplify things (if the employee and required slots
> are the same. I'm going to ask if this will be the case.
>
> True, thanks a lot!
> --
> Stijn Verrept.
>

MINUS operation between table date ranges, is my algorithm sound?

Hi everyone, i was hoping someone could help me.
i am looking to implement a MINUS operation between two tables which contain
date ranges.
table a:
[start] [finish] [group]
1 10 0
18 19 1
23 26 2
28 31 3
table b:
[start] [finish]
4 5
18 18
25 28
Result of table a - table b:
[start] [finish]
1 3
6 10
19 19
23 24
29 31
Would anyone know how to implement this? I have done this by firstly
performing an AND operation between the two tables:
table a AND table b = table c (here the grouping colums tells me which group
from table a the date range is associated with
[start] [finish] [group]
4 5 0
18 18 1
25 26 2
28 28 3
Then I throw table a and c into another table (d). Here I assign:
start date of table a: status = 1
end date of table a: status = 0
start date of table b: status = 0
end date of table b: status = 1
start of date range in table a: boundary = 0
end of date range for table a: boundary = 2
boundary = 1 for all data coming from table b
table d:
[date] [status] [group] [boundary]
1 1 0 1
10 0 0 3
18 1 1 1
19 0 1 3
23 1 2 1
26 0 2 3
28 1 3 1
31 0 3 3
4 0 0 2
5 1 0 2
18 0 1 2
18 1 1 2
25 0 2 2
26 1 2 2
28 0 3 2
28 1 3 2
I then sort using the SQL statement: order by 3,1,2 to get:
table d:
[date] [status] [group] [boundary]
1 1 0 1
4 0 0 2
5 1 0 2
10 0 0 3
18 1 1 1
18 0 1 2
18 1 1 2
19 0 1 3
23 1 2 1
25 0 2 2
26 1 2 2
26 0 2 3
28 1 3 1
28 0 3 2
28 1 3 2
31 0 3 3
I then update the table according to:
case
when boundary = 2 and status = 0 then data = date -1
when boundary = 2 and status = 1 then data = date +1
end
and I get:
table d:
[date] [status] [group] [boundary]
1 1 0 1
3 0 0 2
6 1 0 2
10 0 0 3
18 1 1 1
17 0 1 2
19 1 1 2
19 0 1 3
23 1 2 1
24 0 2 2
27 1 2 2
26 0 2 3
28 1 3 1
27 0 3 2
29 1 3 2
31 0 3 3
I then filter the table and look for adjacient row pairs where row(i)=1 and
row(i+1)=0 :
table d:
[date] [status] [group] [boundary]
1 1 0 1
3 0 0 2
6 1 0 2
10 0 0 3
18 1 1 1
17 0 1 2
19 1 1 2
19 0 1 3
23 1 2 1
24 0 2 2
27 1 2 2
26 0 2 3
28 1 3 1
27 0 3 2
29 1 3 2
31 0 3 3
I delete those pairs where (date(i) < date(i+1) and boundary(i) >
boundary(i+1) to get:
table d:
[date] [status] [group] [boundary]
1 1 0 1
3 0 0 2
6 1 0 2
10 0 0 3
19 1 1 2
19 0 1 3
23 1 2 1
24 0 2 2
29 1 3 2
31 0 3 3
This then gives me the resultant table im looking for:
table d recast:
[start] [finish]
1 3
6 10
19 19
23 24
29 31
Is my approach sound? I am yet to encode the above in SQL. Maybe there is a
faster approach to getting this result?
Any help most appreciated!
cheers, peter"peter walker" <pwalker@.nospam.com> wrote in message
news:ucoD$XLGGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi everyone, i was hoping someone could help me.
> i am looking to implement a MINUS operation between two tables which
> contain date ranges.
> table a:
> [start] [finish] [group]
> 1 10 0
> 18 19 1
> 23 26 2
> 28 31 3
>
> table b:
> [start] [finish]
> 4 5
> 18 18
> 25 28
> Result of table a - table b:
> [start] [finish]
> 1 3
> 6 10
> 19 19
> 23 24
> 29 31
> Would anyone know how to implement this? I have done this by firstly
Read my signature to see how much information you left out.
Let's assume your tables look like this:
CREATE TABLE a (start INTEGER NOT NULL PRIMARY KEY /* ' Was not specified
*/, finish INTEGER NOT NULL, CHECK (start<=finish) /* ? Was not specified
*/, grp INTEGER NOT NULL /* "GROUP" is a reserved word - not a good column
name */);
CREATE TABLE b (start INTEGER NOT NULL PRIMARY KEY /* ' */, finish INTEGER
NOT NULL, CHECK (start<=finish));
Your sample data:
INSERT INTO a (start,finish,grp)
SELECT 1, 10, 0 UNION ALL
SELECT 18, 19, 1 UNION ALL
SELECT 23, 26, 2 UNION ALL
SELECT 28, 31, 3;
INSERT INTO b (start, finish)
SELECT 4, 5 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 25, 28;
I'll also assume you have a table of numbers - all integers from 0 to some
arbitrarily large number. One way to get the missing numbers would be like
this:
SELECT n.num
FROM numbers AS n
LEFT JOIN b
ON n.num BETWEEN b.start AND b.finish
WHERE b.start IS NULL
AND n.num BETWEEN
(SELECT MIN(start)
FROM a) AND
(SELECT MAX(finish)
FROM a);
I don't quite understand the significance of the "group" column here. Please
give us a better spec if you need more help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi, thanks for the post. Im the original poster of this topic (at work
using a friend's account).
Please disregard the [group] column in my initial description of the
problem. I used a [group] column within my algorithm. In effect I would
like to know the best way to implement a subtraction of date ranges as
follows:
table a:
[start] [finish]
1 10
18 19
23 26
28 31
table b:
[start] [finish]
4 5
18 18
25 28
Result of table a - table b:
[start] [finish]
1 3
6 10
19 19
23 24
29 31
in effect, if a date range in table_b intersects a date range in
table_a, then that data in the intersection is removed from the date
range in table_a
for example, the following are example cases where we subtract from a
range in table_a where there are ranges in table_b which intersect with
that range in table_a:
1. {23....37} - {25...29} = {23...24}
2. {23....37} - {26...32} = {23...25}, {33...37}
3. {23....37} - [ {25...27} , {31...33} ] = {23...24}, {28...30},
{34...37}
Any help on this would be great!
Many thanks.
peter|||Peter,
this is very ugly :)
but the SELECT statement should work
i use a table of natural numbers proposed by David
David, pardon me for the use of your idea :)
SELECT MIN(U.seq) as start, MAX(U.seq) as finish
FROM (SELECT G.seq, G.seq - COUNT(*)
FROM (SELECT GS.seq
FROM TableA AS GA, Sequence AS GS
WHERE GS.seq BETWEEN GA.start AND GA.finish
AND NOT EXISTS(SELECT *
FROM TableB AS GB, Sequence AS GSS
WHERE GSS.seq BETWEEN GB.start AND
GB.finish
AND GSS.seq = GS.seq)) AS G,
(SELECT LS.seq
FROM TableA AS LA, Sequence AS LS
WHERE LS.seq BETWEEN LA.start AND LA.finish
AND NOT EXISTS(SELECT *
FROM TableB AS LB, Sequence AS LSS
WHERE LSS.seq BETWEEN LB.start AND
LB.finish
AND LSS.seq = LS.seq)) AS L
WHERE L.seq <= G.seq
GROUP BY G.seq) AS U(seq, gb)
GROUP BY U.gb;
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)|||On Sat, 14 Jan 2006 12:55:36 +1000, peter walker wrote:

>Hi everyone, i was hoping someone could help me.
(snip)
Hi Peter,
I just posted a reply to your first thread about this issue.
Hugo Kornelis, SQL Server MVP|||Hello There,
I hope this might solve your problem.
Create Table TableA
(
[start] int,
[finish] int,
[group] int
)
Go
Insert into TableA
Select 1 ,10 ,0
Union All
Select 18, 19 ,1
Union All
Select 23, 26 ,2
Union All
Select 28 ,31 ,3
Go
Create Table TableB
(
[start] int,
[finish] int
)
Go
Insert into TableB
Select 4 ,5
Union All
Select 18 ,18
Union All
Select 25 ,28
Go
Create View vwTmpData
As
Select * From (
Select 1 N
Union All
Select 2
Union All
Select 3
Union All
Select 4
Union All
Select 5
Union All
Select 6
Union All
Select 7
Union All
Select 8
Union All
Select 9
Union All
Select 10
Union All
Select 11
Union All
Select 12
Union All
Select 13
Union All
Select 14
Union All
Select 15
Union All
Select 16
Union All
Select 17
Union All
Select 18
Union All
Select 19
Union All
Select 20
Union All
Select 21
Union All
Select 22
Union All
Select 23
Union All
Select 24
Union All
Select 25
Union All
Select 26
Union All
Select 27
Union All
Select 28
Union All
Select 29
Union All
Select 30
Union All
Select 31
Union All
Select 32
Union All
Select 33
) Seq Inner Join TableA T1 On N Between T1.start and T1.finish
Where N Not In (Select Start From tableB Union Select Finish From
TableB)
Go
Select identity(int,1,1) N1,* into tmpData From vwTmpData
Update tmpData Set [group] = [group] + 1
Where N - N1> 0
Select Min(N) Start,Max(N) Finish From tmpData Group by [group]
Drop Table tmpData
Drop View vwTmpData
Drop Table TableA
Drop Table TableB
With Warm regards
Jatinder Singh

Minor Table Insert help

i have the following code, it all works how i want it to bar the first time it runs, when i run the program and insert the data the first time, it inserts the data twice, all other times only once or the update.

$dbh=mysql_connect ("localhost", "twqwwsoy_user", "iiyama") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("twqwwsoy_resources");

$count="SELECT COUNT(message) FROM Diary";
$result = mysql_query($count);
$co = mysql_result($result,$x);

if ($co == 0) {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {

$count="SELECT COUNT(username) FROM Diary WHERE username = '$username' AND day_id = '$day'";
$result = mysql_query($count);
$co1 = mysql_result($result,$x);
echo "$co1";
}

if ($co1 == 1) {
$SQL= "UPDATE Diary SET message = '$message' WHERE username = '$username' AND day_id= '$day'";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}
else {
$SQL= "INSERT INTO Diary (username, day_id, message) VALUES('$username', '$day', '$message')";
$result = @.mysql_query ($SQL) or die('query error ' . mysql_error());
}I don't speak MySQL, but it seems like this: when the table 'diary' is empty, you are running the script for the first time:co = 0 and co1 = 0

IF co = 0 (yes, it is) THEN
INSERT INTO diary ... -> this is executed
ELSE
SELECT COUNT ... -> this is not executed
END IF

IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this statement performs second insertRunning the script second (and every other) time:IF co = 0 (no, it isn't) THEN
INSERT INTO diary ...
ELSE
SELECT COUNT ... -> it is executed and co1 = 2
END IF

IF co1 = 1 (no, it isn't) THEN
UPDATE diary ... -> this is not executed
ELSE
INSERT INTO diary ... -> this is executed
END IFIf I'm not wrong, this is what happens. But, you didn't say what you wanted to happen ... Anyway, I guess you'll have to adjust logic a little bit.

Friday, March 23, 2012

Mining Model?

Hi,

I'm working on a project to create a mining model.I have one "flattened" table with a productID (key) and related attributes. Example columns are weight, color, price, units sold last year, product category, product rating (top seller, etc) and similar columns, about 40 in total. There are about 15,000 products and same number of rows in the table.

The objective is to pass a ProductID (that also exists in the table) and get back the top N products that most resemble the source product based on all the attributes.

Any advice on which models I should test and how to set up the models would be much appreciated. Also if there is a similar example/sample out there, please let me know. I downloaded the MovieClick sample, but that doesn't work in my case as I only have the equivalent of the Customers table.

Thanks in Advance.

This is actually a K-Nearest Neighbor problem, for which we don't ship an implementation for SQL Server Analysis Services (although someone may have written a plug in). I could imagine that you could use the fuzzy matching transform in Integration Services to do this, however. To do so you would just use your product table as the reference table, send in the fields of the product of interest, then sort the results by the match confidence.

HTH

-Jamie

|||

Thanks Jamie, I'll try fuzzy matching in SSIS. I'm also thinking of writing my own code to calculate based on KNN in TSQL. I'm not sure if the performance will be acceptable though especially as I need to pass in up to 5 products, evaluate based on their combined attributes and get a resultset back with top N matches.

Is there a third party software out there that can do this?

|||When using the fuzzy components in SSIS, do be aware that they are string matching components, evaluating the edit distance between two string. So, for example, "911" will be pretty much as close a match to "999" as "199" - so for any columns where you need to evaluate similarity of values you may need to use other components such as the Derived Column to perform the calculation.|||

Hi Donald,

Can you explain what you mean by Derived column. How would I make it work to calculate distances for numeric columns/attributes.

Thanks.

|||I don't know of a 3rd party KNN, but you would likely get better performance implementing your own algorithm using our plug-in interfaces in C#.|||

Jamie/Donald

Thanks for your responses. My C# is not that strong. For now I'll write something in SQL and if I can create a model that works, I'll convert that into a plug-in.

This is the plan. Normalize all attributes using mean and std dev. Apply the nearest neighbour algorithm using Euclidean distance. Also experiment with arbitrary weights for attributes or maybe use correlation to filter out some of the weaker attributes, not sure how that will work though at this point.

Any suggestions are welcome. Also, Is there a sample plug-in in C# out there I can use as template. Is there any way I can mold this table to use Association Rules or any other built-in algorithm.

Regards,

Asim.

|||

There are tutorials here for writing managed plug-in algorithms: http://www.sqlserverdatamining.com/DMCommunity/Tutorials/default.aspx

You may need to register (free!) to get access to the links.

minimum value

i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA

minimum value

i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero
"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
sql

minimum value

minimum price on earliest date

Ok my first posted question :
(This is related to a travel website)

I have the following table layout :

CREATE TABLE "public"."package" (
"id" BIGINT NOT NULL,
"accom_code" VARCHAR(4) NOT NULL,
"start_date" DATE NOT NULL,
"end_date" DATE NOT NULL,
"pricing_type" VARCHAR(2),
"indic_price" NUMERIC(7,2),
"unit_price" NUMERIC(7,2),
"adult_age_max_cnt" INTEGER,
CONSTRAINT "package_pkey" PRIMARY KEY("id")
) WITH OIDS;

The package table contains a list (a very large one) for holiday accomodation packages.

What i'm trying to get is the following :

The MINIMUM price using the following for "price" :

CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.unit_price
END AS NUMERIC(7,2))

AND

The minimum date (i.e. nearest start date) greater than today
WHERE the start_date equals the minimum start_date and the price equals the minimum price.

Any thoughts?

With any luck I will be able to give you an test data insert for this.

This should probably be posted in the POSTGRESQL section however I feel that it is a more general SQL question than anything else.What I have thus far :

SELECT p.accom_code
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code

At the present time i'm getting 10 records (as expected). However I believe these records are WRONG as the minimum price doesn't necessarily match up with the minimum date for a particular (correct me i'm wrong here). How do I go about correcting this?|||Ooh think I nearly got it, can someone verify this :

SELECT p.accom_code
,x.min_start
,MIN(CAST (CASE p.pricing_type
WHEN 'UN' THEN p.unit_price
WHEN 'PA' THEN p.indic_price*p.adult_age_max_cnt
ELSE p.indic_price
END AS NUMERIC(7,2))) as min_price
FROM package p
JOIN (
SELECT p.accom_code,MIN(p.start_date) as min_start
FROM package p
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code
) x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
WHERE p.accom_code IN ('DDNA','ADE9','CGHH','ASEC','BDB9','HGMD','CMEF', 'BGDE','YRB5','BJAM')
AND p.duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
GROUP BY p.accom_code,x.min_start

In theory it should give the cheapest price on the earliest start date for each of the 10 accomodation types.

Edit : Just to finish it off I needed the start_date out of it as well ;) (added x.min_start to select and group clause)|||looks okay to me

i would not use the same alias "p" in more than one place in the query

and i would probably remove one of the following:AND p.duration = 7
AND p.duration = 7;)|||LOL oh yeah. I've cleaned it up in the above post now.
However I do have one question to finish it off (and this annoys me about of lot of SQL i've done in the past) :
Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections? I hope that question makes sense.|||Is there any way to remove the duplicate WHERE clauses in a query like thisdefine a view|||Btw, your queries won't work at all in SQL because you used delimited schema/table/column names in the CREATE TABLE statement (i.e. double-quotes) but not in your query.|||That DDL was written in PMS for Postgres so it's a pure copy. I did mention that I was using Postgresql in my first post.

What I DID want to be standard was the SQL query I was doing. :D|||Well, then you have to use delimited schema/table/column names in your query - as I said.|||I have come across a problem with the query above. If two records calculate the same minimum price for one accom_code I can get two results when joined to itself.
i.e. min price and min date is £189 and 25/05/07
when self-joined total price = £567 / 3 adults = £189
and total price = £378 / 2 adults = £189

They both match the minimum price and date and thus both records are output. DOH!

As a quick hack (and I don't like this method) I did a distinct and ordered by total price minimum first.|||Is there any way to remove the duplicate WHERE clauses in a query like this, and somehow apply it only once but for both sections?
For this purpose, SQL99 defines "common table expressions", i.e., a "WITH" subclause of a select statement. Not yet available in the current version of PostgreSQL but coming soon (8.4 probably) ;)
(DB2, Oracle and SQLServer already have them in place.)
Your query, with CTEs, would become:WITH p AS
( SELECT accom_code, start_date,
MIN(CAST (CASE pricing_type
WHEN 'UN' THEN unit_price
WHEN 'PA' THEN indic_price*adult_age_max_cnt
ELSE indic_price
END AS NUMERIC(7,2))) as min_price
FROM package
WHERE accom_code IN ('DDNA','ADE9','CGHH','ASEC',
'BDB9','HGMD','CMEF','BGDE','YRB5','BJAM')
AND duration = 7
AND start_date > current_date
AND start_date < current_date + interval '28 day'
),
x AS
( SELECT p.accom_code,MIN(p.start_date) as min_start
FROM p
GROUP BY p.accom_code
)
SELECT p.accom_code,x.min_start,p.min_price
FROM p INNER JOIN x
ON p.accom_code = x.accom_code AND x.min_start = p.start_date
GROUP BY p.accom_code,x.min_start
These are effectively "local view definitions", cf suggestion by r937.
(Note that in the above query, the SELECT .. p.min_price is not compatible with the GROUP BY -- maybe add p.min_price to the GROUP BY? Or replace it by "MIN(p.min_price)"?)sql

Wednesday, March 21, 2012

Minimum Conflict Resolver

Hi,
I tried to use 'Minimum Conflict Resolver' for Orders table of Northwind.
I entered EmployeeID as 'the information needed by the resolver' and also
modified this column for a same row in two different subscribers.
I updated one of them to 1 and the other to 5. I expected 1 become the
winner but the merge agent fails with an error regarding EmployeeID.
Should I expect anything different or the resolver needs other information
than EmployeeID?
Any help would be greatly appreciated.
Leila
are you using column level tracking?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leila" <leilas@.hotpop.com> wrote in message
news:ez2ezm7YFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I tried to use 'Minimum Conflict Resolver' for Orders table of Northwind.
> I entered EmployeeID as 'the information needed by the resolver' and also
> modified this column for a same row in two different subscribers.
> I updated one of them to 1 and the other to 5. I expected 1 become the
> winner but the merge agent fails with an error regarding EmployeeID.
> Should I expect anything different or the resolver needs other information
> than EmployeeID?
> Any help would be greatly appreciated.
> Leila
>
|||If you mean the item "treat changes to the same row as a conflict", I have
tried both items. This is the error:
The specified conflict resolution column 'employeeid' could not be found.
(Source: Merge Process (Agent); Error number: -2147467259)
Leila
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OZ3m0cQZFHA.1404@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> are you using column level tracking?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Leila" <leilas@.hotpop.com> wrote in message
> news:ez2ezm7YFHA.3320@.TK2MSFTNGP12.phx.gbl...
Northwind.[vbcol=seagreen]
also[vbcol=seagreen]
information
>
|||I'm getting the same error. I will be reporting this to MS - it looks like a
bug.
I'll try to follow up with you using your hotpop email address if this is
legit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eHXSnzUZFHA.3184@.TK2MSFTNGP15.phx.gbl...
> If you mean the item "treat changes to the same row as a conflict", I have
> tried both items. This is the error:
> The specified conflict resolution column 'employeeid' could not be found.
> (Source: Merge Process (Agent); Error number: -2147467259)
> Leila
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OZ3m0cQZFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Northwind.
> also
> information
>
|||trying a simpler table
However, a more simple table, i.e.
create table mergetest
(pk int not null identity constraint primarykey primary key,
charcol1 char(20),
intcol int)
will work i.e.
update mergetest set charcol1='publisher', intcol=2 where pk=1
update northwindsub.dbo.mergetest set charcol1='subscriber', intcol=1 where
pk=1
works where the northwindsub conflict wins (if the intcol column is used as
the basis of the column for the minimum conflict resolver).
Not sure what the problem is.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uLaM4NYZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> I'm getting the same error. I will be reporting this to MS - it looks like
a[vbcol=seagreen]
> bug.
> I'll try to follow up with you using your hotpop email address if this is
> legit.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eHXSnzUZFHA.3184@.TK2MSFTNGP15.phx.gbl...
have[vbcol=seagreen]
found.[vbcol=seagreen]
the
>
|||Thanks indeed!
Please keep me informed: leilas@.hotpop.com
Leila
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uLaM4NYZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> I'm getting the same error. I will be reporting this to MS - it looks like
a[vbcol=seagreen]
> bug.
> I'll try to follow up with you using your hotpop email address if this is
> legit.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eHXSnzUZFHA.3184@.TK2MSFTNGP15.phx.gbl...
have[vbcol=seagreen]
found.[vbcol=seagreen]
the
>

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.

> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the enti
re
> index using DBCC DBREINDEX. These apply to both clustered and non-cluster
ed
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a low
er
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate fr
ee
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.
Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.

> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.
|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:

Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.

Monday, March 19, 2012

Min(), MAX() Question

Hi,
I have two tables : Table1, Table2
CREATE TABLE [dbo].[Table1] (
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SaleDate] [datetime] NULL ,
[Price] [decimal](18, 2) NULL ,
[Customer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReportDate] [datetime] NULL ,
[ReportPrice] [decimal](18, 2) NULL ,
[Customer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-- And here is some data
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Jan 1 2005
12:00:00:000AM',10.50,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Jan 1 2005
12:00:00:000AM',9.50,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 2 2005
12:00:00:000AM',9.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 1 2005
12:00:00:000AM',6.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 2 2005
12:00:00:000AM',7.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Oct 10 2005
12:00:00:000AM',30.00,'001')
INSERT INTO [Table2]
([Product],[ReportDate],[ReportPrice],[C
ustomer])VALUES('A','May 1 2005
12:00:00:000AM',0,'001')
I need a query to update ReportPrice From Table2 with the Maximum Saledate
from table1 less then ReportDate from Table2 and the price=Min for that
date for that specific date.
This is my query
UPDATE t2 SET t2.ReportPrice=t1.Price FROM
(
Select Max(SaleDate) as SaleDate,Min(Price) AS PRICE ,Customer,Product From
Table1
GROUP BY Customer,Product) as t1,Table2 as t2
WHERE t1.SaleDate< t2.ReportDate and t1.Product = t2.Product and
t1.Customer=t2.Customer
Select * FROM Table1
Select * FROM Table2
, but is wrong .. I get 6 instead of 7 . I need 7 to be the ReportPrice.
Thanks guys !Do:
UPDATE table2
SET ReportPrice =
( SELECT MIN( Price ) FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1 t1
WHERE table1.Product = t1.Product
AND table1.Customer = t1.Customer ) )
WHERE EXISTS
( SELECT * FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer ) ;
With a t-SQL TOP clause in the subquery, it should be a bit more simpler
though:
UPDATE table2
SET ReportPrice = (
SELECT TOP 1 Price FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
ORDER BY table1.SaleDate DESC, Price )
WHERE ...
Anith|||Is not working !
The ideea is good but i need ReportPrice to be 7. In this case will be 30 .
I need somewhere a condition MAX( SaleDate ) <ReportDate
Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ef4W3Z2pFHA.3104@.TK2MSFTNGP12.phx.gbl...
> Do:
> UPDATE table2
> SET ReportPrice =
> ( SELECT MIN( Price ) FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1
> t1
> WHERE table1.Product = t1.Product
> AND table1.Customer =
> t1.Customer ) )
> WHERE EXISTS
> ( SELECT * FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer ) ;
> With a t-SQL TOP clause in the subquery, it should be a bit more simpler
> though:
> UPDATE table2
> SET ReportPrice = (
> SELECT TOP 1 Price FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> ORDER BY table1.SaleDate DESC, Price )
> WHERE ...
> --
> Anith
>|||Thanks!
Is working now !
UPDATE table2
SET ReportPrice =
( SELECT MIN( Price ) FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1 t1
WHERE table1.Product = t1.Product
AND table1.Customer = t1.Customer and
t1.SaleDate<Table2.ReportDate )
)
WHERE EXISTS
( SELECT * FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer ) ;
Select * FROM Table1
Select * FROM Table2
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ef4W3Z2pFHA.3104@.TK2MSFTNGP12.phx.gbl...
> Do:
> UPDATE table2
> SET ReportPrice =
> ( SELECT MIN( Price ) FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1
> t1
> WHERE table1.Product = t1.Product
> AND table1.Customer =
> t1.Customer ) )
> WHERE EXISTS
> ( SELECT * FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer ) ;
> With a t-SQL TOP clause in the subquery, it should be a bit more simpler
> though:
> UPDATE table2
> SET ReportPrice = (
> SELECT TOP 1 Price FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> ORDER BY table1.SaleDate DESC, Price )
> WHERE ...
> --
> Anith
>

Min within a Group query

Hi
We have a table structure for storing away Hires in a SQL Server database.
Related to this table is another table that stores events/logs that have
occurred on the Hires table. I.e. Record Created, Modified, Price Changed,
etc. We are trying to do a query that will return the first log for each
hire and then return a few extra fields too. The basic table structure is
below
Hires
--
HireID
ClientID
Status
Cancelled
HireLog
--
HireLogID
HireID
LogDate
Comment
EventType
OperatorID
At first I thought we could do the following:
SELECT dbo.Hires.HireID, MIN(dbo.HireLog.HireLogID) AS HireLogID,
dbo.HireLog.LogDate, dbo.HireLog.Comment, dbo.HireLog.EventType,
dbo.HireLog.OperatorID
FROM dbo.Hires INNER JOIN dbo.HireLog ON dbo.Hires.HireID =
dbo.HireLog.HireID
GROUP BY dbo.Hires.HireID, dbo.HireLog.LogDate, dbo.HireLog.Comment,
dbo.HireLog.EventType, dbo.HireLog.OperatorID
ORDER BY dbo.Hires.HireID DESC
This works great with just the Hire ID field and the Min(HireLogID), but as
soon as you add the other fields the Group By causes the query to return all
the other Logs for the Hire too.
Is there any way around it?What is HireLogID? If that's an IDENTITY column then it's probably
unwise to rely on it to determine the earliest row. The reason is that
you don't always have full control over the order in which IDENTITY
values are assigned. IDENTITY should be used only as an arbitrary
surrogate key without any ascribed business meaning.
In this case it looks like you'll wanr to use LogDate to determine the
first row for each Hire. Declare (hireid, logdate) as unique to ensure
you have a unique sequence.
SELECT hireid, hirelogid, logdate, comment, eventtype, operatorid
FROM HireLog AS L
WHERE logdate =
(SELECT MIN(logdate)
FROM HireLog
WHERE hireid =L.hireid)
David Portas
SQL Server MVP
--|||Chris,
Just one minor, (probably unnecessary) addition..
If you are storing Date and TIme in LogDate, then the chance of anyone
recording two records in HireLog with the same HireID and LogDate is very
unlikely, and probabl;y impossible, ignore this, David's solution should wor
k
fine...
but if your application logic is only storing the date, without the time
portion, in logDate, then you will need to handle the case where are multipl
e
records with the same value for both HireID and LogDate.
The only way to do that, given your schema, is to use the HireLogID as a
discriminant. (David's comment about no guarantees as to which is REALLY
earliest apply here, but, if you don;t have the time portion of the date
stored, then there's no way to distinquish among multiple records on a
specific day anyway.)
Select hirelogid, hireid, logdate,
comment, eventtype, operatorid
From HireLog L
Where hirelogid =
(Select Min(hirelogid)
From HireLog
Where hireid = L.hireid
And logdate = (Select Min(LogDate)
From HireLog
Where hireid = L.hireid))
"David Portas" wrote:

> What is HireLogID? If that's an IDENTITY column then it's probably
> unwise to rely on it to determine the earliest row. The reason is that
> you don't always have full control over the order in which IDENTITY
> values are assigned. IDENTITY should be used only as an arbitrary
> surrogate key without any ascribed business meaning.
> In this case it looks like you'll wanr to use LogDate to determine the
> first row for each Hire. Declare (hireid, logdate) as unique to ensure
> you have a unique sequence.
> SELECT hireid, hirelogid, logdate, comment, eventtype, operatorid
> FROM HireLog AS L
> WHERE logdate =
> (SELECT MIN(logdate)
> FROM HireLog
> WHERE hireid =L.hireid)
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for you help.
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110365299.387914.40640@.g14g2000cwa.googlegroups.com...
> What is HireLogID? If that's an IDENTITY column then it's probably
> unwise to rely on it to determine the earliest row. The reason is that
> you don't always have full control over the order in which IDENTITY
> values are assigned. IDENTITY should be used only as an arbitrary
> surrogate key without any ascribed business meaning.
> In this case it looks like you'll wanr to use LogDate to determine the
> first row for each Hire. Declare (hireid, logdate) as unique to ensure
> you have a unique sequence.
> SELECT hireid, hirelogid, logdate, comment, eventtype, operatorid
> FROM HireLog AS L
> WHERE logdate =
> (SELECT MIN(logdate)
> FROM HireLog
> WHERE hireid =L.hireid)
> --
> David Portas
> SQL Server MVP
> --
>|||> but, if you don;t have the time portion of the date
> stored, then there's no way to distinquish among multiple records on
a
> specific day anyway
... and therefore the business requirement to display only the earliest
row would be fatally flawed, and anyway, what would be the natural key
of the table in that scenario? That is indeed the price you pay for
tables without proper keys.
David Portas
SQL Server MVP
--|||This is a date + time field.
Thanks.
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110367501.066963.91030@.z14g2000cwz.googlegroups.com...
> a
> ... and therefore the business requirement to display only the earliest
> row would be fatally flawed, and anyway, what would be the natural key
> of the table in that scenario? That is indeed the price you pay for
> tables without proper keys.
> --
> David Portas
> SQL Server MVP
> --
>

Min value

CurrentStage resides in the BUSLOCATION table and UserStage resides in the USERLOCATION table.

I need to find out the MIN difference between these two columns

i.e UserStage-CurrentStage=SHOULD BE THE MINIMUM VALUE.

Userstage is just one value but then it shud be subtracted from all the CurrentStage values in the table and the CurrentStage value which gives me the least difference should be extracted out.

Hope i didn't confuse u all too much :P anyone any ideas?so something like

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage = min(t1.UserStage - t2.CurrentStage )

yeah???|||you might need to go to...

select t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is
Where t1.UserStage - t2.CurrentStage =
(select min(t1.UserStage - t2.CurrentStage from
BUSLocation as t1
join UserLocation as t2 on whatever your join is )

not sure though....|||Do u think my code would work?

SELECT CurrentStage, PlateNbr from tblBusLocation a, tblUserLocation b
Where a.CurrentStage < b.UserStageNbr AND a.CurrentStage= (SELECT MIN(b.UserStageNbr-a.CurrentStage))|||Sorry here is an updated code. Is there a problem with the MIN function?

SELECT t1.CurrentStage, t1.PlateNbr

From tblBusLocation t1, tblUserLocation t2

Where t1.CurrentStage < t2.UserStageNbr AND

t1.CurrentStage =

(SELECT t1.CurrentStage from tblBusLocation t1, tblUserLocation t2

where MIN (t2.UserStageNbr - t1.CurrentStage)
)|||SELECT t1.CurrentStage, t1.PlateNbr
, MIN (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1
, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
group
by t1.CurrentStage, t1.PlateNbr

rudy|||this might be way off but...

select top 1 (t2.UserStageNbr - t1.CurrentStage)
From tblBusLocation t1, tblUserLocation t2
Where t1.CurrentStage < t2.UserStageNbr
Order by (t2.UserStageNbr - t1.CurrentStage)

???

MIN and MAX strange results

I want to get the MIN and MAX value of a table column from a specified period of time. I execute a query and it return the result. The problem is that the values returned by MIN and MAX are not always correct!!

This is the result table

Date Statement From To

1 2007-01-03 00:00:00 Invoice 1 2 Correct
2 2007-01-04 00:00:00 Receipt 1 1 Correct
3 2007-01-04 00:00:00 Invoice 10 9 Wrong
4 2007-01-05 00:00:00 Receipt 2 5 Correct
5 2007-01-05 00:00:00 Invoice 100 99 Wrong
6 2007-01-08 00:00:00 Invoice 124 175 Correct
7 2007-01-09 00:00:00 Invoice 176 224 Correct
8 2007-01-10 00:00:00 Invoice 225 265 Correct

From =From Statement Number

To= To statement Number

The odd behavior happens when the number of digits changes. If the range of the column is 1 digit ie from 0 to 9 the values reported are ok. If the digits change then there is a problem as in line 3 and 5.

Any ideas why this odd behavior happens?

rectis:

I think you need to provide (1) the SQL Statement that is not working correctly and the definition of the table (or at least the relevant columns). My knee-jerk guess would be that you are coming to grief because your "From" and "To" fields are defined as varchar instead of numeric (or integer).

If in fact your "from" and "to" fields are defined as varchar you first need to make a determination to the usage of these fields -- that is see if the definition needs to be modified such that columns are reformatted into numeric (or integer) columns. You may need to compute your max as MIN(CONVERT(INTEGER, FROM)) and MAX(CONVERT(INTEGER,TO))

|||You are right. Thank you very much. I think my brain was stopped.The field was defined as varchar. Now the values are ok!

Monday, March 12, 2012

Millisecond values missing when inserting datetime into datetime column of sql Server

Hi,
I'm inserting a datetime values into sql server 2000 from c#

SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime

C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows Idea ["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?

thanks,
Mani

Look at this post: http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

You'll have to use a CAST or a CONVERT in that INSERT statement to the format you desire.

|||

You have got the SQL Server part right but the .NET type you are using the wrong data type, to get milliseconds you have to use INT32, INT64 or Double the later two does not exist in SQL Server so you have to do conversion. I have found you two links with ready to use code, pay close attention to the string and formatting code. Hope this helps.

http://blogs.msdn.com/kathykam/archive/2006/09/29/.NET-Format-String-102_3A00_-DateTime-Format-String.aspx

http://authors.aspalliance.com/aspxtreme/sys/datetimeclass.aspx

million rows user table and growing

For our web site, we now have a million users and we have one user table
right now..
We would like to consider scaling this out as we grow to 5 -10 million users
and not have it in one table.
Every time a user logs on or changes profile,etc, we dont want to cause
contention or blocking on this table.. so whats the best way to go about
this ?
If i partition the table, whats the best way to go about partitioning it ?
Thanks> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
As long as you have appropriate indexing, I wouldn't expect performance or
concurrency problems regardless of table size. A few million rows really
isn't that large nowadays and, in my option, doesn't warrant partitioning.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Perhaps your company should consider hiring an experienced DBA' One that
can guide you proactively instead of reactively and successfully get you to
the level you wish to achieve.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Focus on indexing strategy , statistics , as 1 million rows is not that
much.
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks

Wednesday, March 28, 2012

Mirrored Records?

Monday, March 26, 2012

Mirror a view to a table

Minute table?

MINUS operation between table date ranges, is my algorithm sound?

Minor Table Insert help

Friday, March 23, 2012

Mining Model?

minimum value

minimum value

minimum value

minimum price on earliest date

Wednesday, March 21, 2012

Minimum Conflict Resolver

Minimizing Fragmentation of table having Non-clustered Index.

Minimizing Fragmentation of table having Non-clustered Index.

Minimizing Fragmentation of table having Non-clustered Index.

Monday, March 19, 2012

Min(), MAX() Question

Min within a Group query

Min value

MIN and MAX strange results

Monday, March 12, 2012

Millisecond values missing when inserting datetime into datetime column of sql Server

million rows user table and growing

Migrating to SQL 2005

Blog Archive

About Me