I have a simple table with a few rows of data. I would like to create
a report that would print the records left to right (meaning; next
record would go into a next column and not down into the new row).
What is the best way to accomplish this?
I have a report created with two columns and the fields are in both,
but it prints out the same data twice in both columns.
Any help is greately appreciated!You may create mutli column reports which sounds like what you are looking
for... There is an example at www.msbicentral.com
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"muris" <rmuris@.hotmail.com> wrote in message
news:1126021920.722602.103050@.g44g2000cwa.googlegroups.com...
>I have a simple table with a few rows of data. I would like to create
> a report that would print the records left to right (meaning; next
> record would go into a next column and not down into the new row).
> What is the best way to accomplish this?
> I have a report created with two columns and the fields are in both,
> but it prints out the same data twice in both columns.
> Any help is greately appreciated!
>sql
Showing posts with label record. Show all posts
Showing posts with label record. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Primary Key Issue
This is not my problem.
http://support.microsoft.com/default.aspx?scid=kb;en-
us;813494&Product=sql2k
I need to know how to prevent a replicated record from
being re-replicated. I know there is a flag. HELP!!!
>--Original Message--
>Running SQL 2K & Win 2K
>I have 16 servers that use trans. replication to a
>central server...
>I want to replicate all data to the central server, this
>is not a problem (Trans. Pub 1). Next, I want to filter
>the data and replicate location specific data to each
>server, this also is not a problem(Trans. Pub 2).
>The problem arises when Pub 2 inserts data on Server A,
>then Pub 1 attempts to insert it back on the central
>server. The distribution agent errors out with a
>Violation of PRIMARY KEY constrain error. How can I
>prevent Pub 1 from attempting to reinsert the records?
>.
>
..
you may be running into a loopback condition. If so use the
loopback_detection switch on sp_addsubscription, and set it to true.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:391a01c4a567$3e9a9870$a301280a@.phx.gbl...
> This is not my problem.
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;813494&Product=sql2k
> I need to know how to prevent a replicated record from
> being re-replicated. I know there is a flag. HELP!!!
> .
>
http://support.microsoft.com/default.aspx?scid=kb;en-
us;813494&Product=sql2k
I need to know how to prevent a replicated record from
being re-replicated. I know there is a flag. HELP!!!
>--Original Message--
>Running SQL 2K & Win 2K
>I have 16 servers that use trans. replication to a
>central server...
>I want to replicate all data to the central server, this
>is not a problem (Trans. Pub 1). Next, I want to filter
>the data and replicate location specific data to each
>server, this also is not a problem(Trans. Pub 2).
>The problem arises when Pub 2 inserts data on Server A,
>then Pub 1 attempts to insert it back on the central
>server. The distribution agent errors out with a
>Violation of PRIMARY KEY constrain error. How can I
>prevent Pub 1 from attempting to reinsert the records?
>.
>
..
you may be running into a loopback condition. If so use the
loopback_detection switch on sp_addsubscription, and set it to true.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:391a01c4a567$3e9a9870$a301280a@.phx.gbl...
> This is not my problem.
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;813494&Product=sql2k
> I need to know how to prevent a replicated record from
> being re-replicated. I know there is a flag. HELP!!!
> .
>
Tuesday, March 20, 2012
PRIMARY KEY constraint problem
I have an odd problem on something that used to work fine.
I have an SP that inserts a record into a table (Contract) with two keyed fields.
The keys are as follows:
ContractID and SeqID (Sequence)
These two keys make the records unique.
Ex:
ContractID SeqID
12345 1
12345 2
12345 3
etc...
Several weeks of using this procedure have been fine. Suddenly I started getting this error:
Violation of PRIMARY KEY constraint 'PK_contract'. Cannot insert duplicate key in object 'Contract'.
The statement has been terminated.
I verified that the values do not violate the constraints. In fact, I can type the exact information into the table directly without a problem.
Has anybody experienced this before?
Any help would be apprciated!
Here is the code in the SP;
CREATE PROCEDURE bcipNewContractSeq @.ContractID Char(10 )AS
DECLARE @.MaxSeqID int
DECLARE @.NewSeqID int
SELECT @.MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @.ContractID
SET @.NewSeqID = @.MaxSeqID + 1
--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @.ContractID
GOFirst...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here|||Originally posted by Brett Kaiser
First...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
No trigger changes or constraint chnages (I checked them) . I am the only person modifying this database. Script is attached.|||What does the sproc bcipCreateCommitment do?
It's in the insert trigger...|||Originally posted by Brett Kaiser
What does the sproc bcipCreateCommitment do?
It's in the insert trigger...
Inserts a record into a table (tblCommitment) based in the INSERTED Contract record. Inserts the ContractID and SeqID. Commitment level defaults to 0 and CommitLevelID is the IDENTITY - incremental by 1:
SCRIPT...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCommitment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCommitment]
GO
CREATE TABLE [dbo].[tblCommitment] (
[CommitLevelID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SeqID] [int] NOT NULL ,
[CommitLevel] [int] NOT NULL ,
[SysDateEntered] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [PK_tblCommitment] PRIMARY KEY CLUSTERED
(
[CommitLevelID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [DF_tblCommitment_CommitLevel] DEFAULT (0) FOR [CommitLevel],
CONSTRAINT [DF_tblCommitment_SysDateEntered] DEFAULT (getdate()) FOR [SysDateEntered]
GO|||I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...|||Originally posted by Brett Kaiser
I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...
>>>>>>>>>>>>>>>>>>>
I see it now! You pegged it. "After further review of the play..."
In test it worked fine and I may not have done more than one additional and now in the production where there is more than one record being created it is going to grab more than one. The answer to my problem is to use the previous SeqID in the where clause to pull ONE record only for the copy.
I need to get more sleep...
Thanks for your time to help the SQL'y impaired!
RLM|||Don't mention it...but why SELECT FROM the table at all...except to get the name...
Seems like your table is 2nd normal form though...
You should try to avoid repetitive data...should probably be in a separate table...
Try this...
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT TOP 1 @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
And why aren't you using IDENTITY?|||Originally posted by rmetz
>>>>>>>>>>>>>>>>>>>
I need to get more sleep...
RLM
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.|||Originally posted by smasanam
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||The reason for the redundant data is due to "Inherited Database application". Under normal circumstances (meaning my design) I would not have had this. The table should be broken in two with the Contract table being the "Header" record and the sequence entries in another table as "Contract Details" therefore eliminating the need to cary over the extra baggage...in a"perfect world".
As for testing...I do my best with the amount of time I am given. The app was in a beta test mode when the problem occured so we didn't damage anything too badly. The fix however only took this addition " AND SeqID = @.MaxSeqID" to pull the last unique record for the copy.
BTW, this app is going to be re-written and you can rest assured that proper normalization will be exercised.
Thanks for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
Cheers!
>>>>>>>>>>>>>>>>>>>>>>>>>
Originally posted by Brett Kaiser
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||Originally posted by rmetz
for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
You telling me?
Hell, There been times...don't get me started...
I have an SP that inserts a record into a table (Contract) with two keyed fields.
The keys are as follows:
ContractID and SeqID (Sequence)
These two keys make the records unique.
Ex:
ContractID SeqID
12345 1
12345 2
12345 3
etc...
Several weeks of using this procedure have been fine. Suddenly I started getting this error:
Violation of PRIMARY KEY constraint 'PK_contract'. Cannot insert duplicate key in object 'Contract'.
The statement has been terminated.
I verified that the values do not violate the constraints. In fact, I can type the exact information into the table directly without a problem.
Has anybody experienced this before?
Any help would be apprciated!
Here is the code in the SP;
CREATE PROCEDURE bcipNewContractSeq @.ContractID Char(10 )AS
DECLARE @.MaxSeqID int
DECLARE @.NewSeqID int
SELECT @.MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @.ContractID
SET @.NewSeqID = @.MaxSeqID + 1
--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @.ContractID
GOFirst...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here|||Originally posted by Brett Kaiser
First...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
No trigger changes or constraint chnages (I checked them) . I am the only person modifying this database. Script is attached.|||What does the sproc bcipCreateCommitment do?
It's in the insert trigger...|||Originally posted by Brett Kaiser
What does the sproc bcipCreateCommitment do?
It's in the insert trigger...
Inserts a record into a table (tblCommitment) based in the INSERTED Contract record. Inserts the ContractID and SeqID. Commitment level defaults to 0 and CommitLevelID is the IDENTITY - incremental by 1:
SCRIPT...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCommitment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCommitment]
GO
CREATE TABLE [dbo].[tblCommitment] (
[CommitLevelID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SeqID] [int] NOT NULL ,
[CommitLevel] [int] NOT NULL ,
[SysDateEntered] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [PK_tblCommitment] PRIMARY KEY CLUSTERED
(
[CommitLevelID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [DF_tblCommitment_CommitLevel] DEFAULT (0) FOR [CommitLevel],
CONSTRAINT [DF_tblCommitment_SysDateEntered] DEFAULT (getdate()) FOR [SysDateEntered]
GO|||I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...|||Originally posted by Brett Kaiser
I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...
>>>>>>>>>>>>>>>>>>>
I see it now! You pegged it. "After further review of the play..."
In test it worked fine and I may not have done more than one additional and now in the production where there is more than one record being created it is going to grab more than one. The answer to my problem is to use the previous SeqID in the where clause to pull ONE record only for the copy.
I need to get more sleep...
Thanks for your time to help the SQL'y impaired!
RLM|||Don't mention it...but why SELECT FROM the table at all...except to get the name...
Seems like your table is 2nd normal form though...
You should try to avoid repetitive data...should probably be in a separate table...
Try this...
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT TOP 1 @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
And why aren't you using IDENTITY?|||Originally posted by rmetz
>>>>>>>>>>>>>>>>>>>
I need to get more sleep...
RLM
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.|||Originally posted by smasanam
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||The reason for the redundant data is due to "Inherited Database application". Under normal circumstances (meaning my design) I would not have had this. The table should be broken in two with the Contract table being the "Header" record and the sequence entries in another table as "Contract Details" therefore eliminating the need to cary over the extra baggage...in a"perfect world".
As for testing...I do my best with the amount of time I am given. The app was in a beta test mode when the problem occured so we didn't damage anything too badly. The fix however only took this addition " AND SeqID = @.MaxSeqID" to pull the last unique record for the copy.
BTW, this app is going to be re-written and you can rest assured that proper normalization will be exercised.
Thanks for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
Cheers!
>>>>>>>>>>>>>>>>>>>>>>>>>
Originally posted by Brett Kaiser
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||Originally posted by rmetz
for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
You telling me?
Hell, There been times...don't get me started...
Primary key at the beggning of each record
Are primary keys always the first columns in each record? What's the
di
vantage of having them for example in the middle of the records? what
happens?
ThanksOnly cosmetics. No technical difference. Check out what standards you want t
o follow, if there
already is a standard in place etc. From a technical standpoint, column orde
ring is irrelevant
(since no-one should do SELECT * or INSERT without a column name list in pro
duction code). Most
people find tables easier to read with PK as the first column though, I'm gu
ilty as charged, for
instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"J-T" <J-T@.microsft.com> wrote in message news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...[col
or=darkred]
> Are primary keys always the first columns in each record? What's the di
vantage of having them
> for example in the middle of the records? what happens?
> Thanks
>[/color]|||As an add-on to Tibor's statement...the ordering of the columns used in the
PK IS important as SQL Server will automatically add a clustered index
(default) for each PK...first column if composite key is used should be most
selective.
HTH
J
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
> Only cosmetics. No technical difference. Check out what standards you want
> to follow, if there already is a standard in place etc. From a technical
> standpoint, column ordering is irrelevant (since no-one should do SELECT *
> or INSERT without a column name list in production code). Most people find
> tables easier to read with PK as the first column though, I'm guilty as
> charged, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "J-T" <J-T@.microsft.com> wrote in message
> news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...
>|||<
Are primary keys always the first columns in each record?
>
Not necessarily.
<
What's the divantage of having them for example in the middle of the
records? what happens?
>
To my knowledge, there are no physical divantages. However, people
are familiar with the first columns being the primary key.
When IBM made available the Indexed Sequential Access Method (ISAM) in
the 1960s, there was a restriction that the unqiue index bits had to be
at the start of the record.
This restriction has now become a tradition.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***|||what do yuo mean by *most selective*?
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
> As an add-on to Tibor's statement...the ordering of the columns used in
> the PK IS important as SQL Server will automatically add a clustered index
> (default) for each PK...first column if composite key is used should be
> most selective.
> HTH
> J
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
>|||most unique - increases the likelyhood that the index will be utilized to
increase the performance of queries.
"J-T" <J-T@.microsft.com> wrote in message
news:uiqdy2RbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> what do yuo mean by *most selective*?
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
>
di
vantage of having them for example in the middle of the records? whathappens?
ThanksOnly cosmetics. No technical difference. Check out what standards you want t
o follow, if there
already is a standard in place etc. From a technical standpoint, column orde
ring is irrelevant
(since no-one should do SELECT * or INSERT without a column name list in pro
duction code). Most
people find tables easier to read with PK as the first column though, I'm gu
ilty as charged, for
instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"J-T" <J-T@.microsft.com> wrote in message news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...[col
or=darkred]
> Are primary keys always the first columns in each record? What's the di
vantage of having them
> for example in the middle of the records? what happens?
> Thanks
>[/color]|||As an add-on to Tibor's statement...the ordering of the columns used in the
PK IS important as SQL Server will automatically add a clustered index
(default) for each PK...first column if composite key is used should be most
selective.
HTH
J
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
> Only cosmetics. No technical difference. Check out what standards you want
> to follow, if there already is a standard in place etc. From a technical
> standpoint, column ordering is irrelevant (since no-one should do SELECT *
> or INSERT without a column name list in production code). Most people find
> tables easier to read with PK as the first column though, I'm guilty as
> charged, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "J-T" <J-T@.microsft.com> wrote in message
> news:O5lObkRbFHA.3932@.TK2MSFTNGP12.phx.gbl...
>|||<
Are primary keys always the first columns in each record?
>
Not necessarily.
<
What's the di
vantage of having them for example in the middle of therecords? what happens?
>
To my knowledge, there are no physical di
vantages. However, peopleare familiar with the first columns being the primary key.
When IBM made available the Indexed Sequential Access Method (ISAM) in
the 1960s, there was a restriction that the unqiue index bits had to be
at the start of the record.
This restriction has now become a tradition.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***|||what do yuo mean by *most selective*?
Thanks
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
> As an add-on to Tibor's statement...the ordering of the columns used in
> the PK IS important as SQL Server will automatically add a clustered index
> (default) for each PK...first column if composite key is used should be
> most selective.
> HTH
> J
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23J0uHoRbFHA.1660@.tk2msftngp13.phx.gbl...
>|||most unique - increases the likelyhood that the index will be utilized to
increase the performance of queries.
"J-T" <J-T@.microsft.com> wrote in message
news:uiqdy2RbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> what do yuo mean by *most selective*?
> Thanks
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eLiOQsRbFHA.1504@.TK2MSFTNGP15.phx.gbl...
>
Wednesday, March 7, 2012
Previous value best method
I'm not really strong in SQL. My goal is to compare the beginning mileage of a vehicle record with it's previous ending mileage reading. I have something that works, but it feels clunky. I wonder if there is a better method, ie a join. Here's what I have:
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.Unfortunately, this is just a clunky thing to do in SQL. You can try this and see if it is any faster. One lest nested subquery...
select Current.Trolley_num,
Current.Date,
Current.Speedo_start,
Current.Speedo_end,
Previous.Speedo_end
from Daily_Trolley Current
inner join --PriorReadings
(select DTA.Trolley_num,
DTA.Date,
Max(DTB.Date) as PreviousDate
from Daily_Trolley DTA
left outer join Daily_Trolley DTB
on DTA.Trolley_num = DTB.Trolley_num
and DTA.Date > DTB.Date
group by DTA.Trolley_num,
DTA.Date) PriorReadings
on Current.Trolley_num = PriorReadings.Trolley_num
and Current.Date = PriorReadings.Date
left outer join Daily_Trolley Previous
on PriorReadings.Trolley_num = Previous.Trolley_num
and PriorReadings.PreviousDate = Previous.Date|||Forgive me if this is a duplicate; I got an error posting a reply and it's not showing up. This is the third try.
Thanks blindman. QA didn't like "Current" as an alias, but it worked fine when I changed that. Both our versions return records so quickly that no time is registered in the execution time window in QA. There are only 10k records in this table though. However, your version lets me calculate the difference; mine wouldn't (not directly anyway).
I may modify both to work against another table with several hundred thousand records, and see how they compare. I suspect yours will be faster due to the join instead of subquery.
Thanks again.
SELECT A.Trolley_num, A.Date, A.Speedo_start, A.Speedo_end,
(SELECT B.Speedo_end FROM Daily_Trolley AS B
WHERE B.Trolley_num = A.Trolley_num
AND B.Date =
(SELECT Max(Date) FROM Daily_Trolley AS C WHERE C.Trolley_num = A.Trolley_num
And C.Date < '1/23/2005')) AS PrevSpeedoEnd
FROM Daily_Trolley AS A
WHERE A.Date='1/23/2005'
ps: I inherited this db; I'm aware that "Date" should not have been used as a field name.Unfortunately, this is just a clunky thing to do in SQL. You can try this and see if it is any faster. One lest nested subquery...
select Current.Trolley_num,
Current.Date,
Current.Speedo_start,
Current.Speedo_end,
Previous.Speedo_end
from Daily_Trolley Current
inner join --PriorReadings
(select DTA.Trolley_num,
DTA.Date,
Max(DTB.Date) as PreviousDate
from Daily_Trolley DTA
left outer join Daily_Trolley DTB
on DTA.Trolley_num = DTB.Trolley_num
and DTA.Date > DTB.Date
group by DTA.Trolley_num,
DTA.Date) PriorReadings
on Current.Trolley_num = PriorReadings.Trolley_num
and Current.Date = PriorReadings.Date
left outer join Daily_Trolley Previous
on PriorReadings.Trolley_num = Previous.Trolley_num
and PriorReadings.PreviousDate = Previous.Date|||Forgive me if this is a duplicate; I got an error posting a reply and it's not showing up. This is the third try.
Thanks blindman. QA didn't like "Current" as an alias, but it worked fine when I changed that. Both our versions return records so quickly that no time is registered in the execution time window in QA. There are only 10k records in this table though. However, your version lets me calculate the difference; mine wouldn't (not directly anyway).
I may modify both to work against another table with several hundred thousand records, and see how they compare. I suspect yours will be faster due to the join instead of subquery.
Thanks again.
Saturday, February 25, 2012
Previous and next ID
I'd like to find the previous and next record of a table based on the numeri
c
value of an identity column. For example, consider the following sample dat
a:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
ThanksTry:
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
I'd like to find the previous and next record of a table based on the
numeric
value of an identity column. For example, consider the following sample
data:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
Thanks|||Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:
> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return thos
e
> two values, but I'm banging my head against the wall. If someone could gi
ve
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||select recid
(select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
(select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
from theTable t1
on SQL 2K5 use row_number()|||How about :
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FB304593-6491-434F-8315-A4E69D7BDE1D@.microsoft.com...
Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:
> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return
> those
> two values, but I'm banging my head against the wall. If someone could
> give
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||That did it Alexander. Thanks.
"Alexander Kuznetsov" wrote:
> select recid
> (select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
> (select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
> from theTable t1
> on SQL 2K5 use row_number()
>|||Do you know the differences in rows and records? Do you now that you
are mimicking a magnetic tape file in SQL? Why did you use the
proprietary SELECT TOP syntax?
SELECT MIN(F1.foo_id) AS prev_tape_position,
MAX(F2.foo_id) AS next_tape_position
FROM FakeTape AS F1, FakeTape AS F2
WHERE F1.foo_id > @.current_tape_position
AND F2.foo_id < @.current_tape_position;
I am not usre what you want to do when the imaginary read head is on
the first or last "record".
c
value of an identity column. For example, consider the following sample dat
a:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
ThanksTry:
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
I'd like to find the previous and next record of a table based on the
numeric
value of an identity column. For example, consider the following sample
data:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
Thanks|||Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:
> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return thos
e
> two values, but I'm banging my head against the wall. If someone could gi
ve
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||select recid
(select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
(select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
from theTable t1
on SQL 2K5 use row_number()|||How about :
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FB304593-6491-434F-8315-A4E69D7BDE1D@.microsoft.com...
Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:
> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return
> those
> two values, but I'm banging my head against the wall. If someone could
> give
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||That did it Alexander. Thanks.
"Alexander Kuznetsov" wrote:
> select recid
> (select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
> (select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
> from theTable t1
> on SQL 2K5 use row_number()
>|||Do you know the differences in rows and records? Do you now that you
are mimicking a magnetic tape file in SQL? Why did you use the
proprietary SELECT TOP syntax?
SELECT MIN(F1.foo_id) AS prev_tape_position,
MAX(F2.foo_id) AS next_tape_position
FROM FakeTape AS F1, FakeTape AS F2
WHERE F1.foo_id > @.current_tape_position
AND F2.foo_id < @.current_tape_position;
I am not usre what you want to do when the imaginary read head is on
the first or last "record".
Preview only returning one record of report
I'm new to reporting services so I assume this is a stupid question however here it goes.
I created a report that will produce a simple customer invoice. I use a stored procedure to return the data. When I run the dataset in the data section it returns 5 records. When I run the report in preview mode it only returns the first record of data in the report. I did notice that when I drag and drop the dataset fields into a report it appends the record with "First(Fields!..." I assume that this will only return the first record which it appears to do. When I remove the "First" it only returns the last record in the report. How do I return all 5 of the records in the preview pane. The paging section in the preview pane in all scenerios always says 1of1 with the next page arrow grayed out.
Thanks in advance..
Hi,
use tables in stead of text boxes to visualize tabular data.
Cheers,
Yani
|||I don't want tabular data.
I want Name, Address, City State Zip of customer #1 on Page 1 Followed by detail (I use tabular data here) for that customer
Name, Address, City State Zip of customer #2 on Page 2 Followed by detail for that customer
All I get is Customet #1 (Or #5 if I remove the first stated in the original question..)
Etc..
|||Okay,
did u try setting up all controls related to a customer into aListControl.
The contained info by a List Control is repeated for reach data row, this way you could achieve your need.
Cheers
|||I'm totally lost at your suggestion??
I want to produce 5 invoices.
The Stored procedure returns data for those 5 invoices.( Name, address, city, state, zip etc..)
The report should return 5 pages with a customer invoice on each page so I can print them.
I'm not sure where a list control come into play here.
|||If You don't want ur report in a tabular form then you will have to use list control.Drop textboxes into
the list control .
|||Thank you,
I think I got it now.
One additional question I have with Formatting and Printing. Is there any type of formatting control for printing. In my research so far It appears that printing directly from the control is not an option. For now I will print to a PDF and then print. The problem I have is the PDF is creating what appears to be about a 1" margin on the page. I need the reports to print with about a .25" margin. Is there any place to control this. I will be doing checks next and the positiong will become more critical.
Thanks in advance.
|||
Go to Report Menu->Report properties -> Layout Tab.
I think this will help.
May
Subscribe to:
Posts (Atom)