Need help converting a select query into a case statement

I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')

I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
LauraConvert a SELECT statement into a CASE statement?

I haven't a clue what you are intending to do.|||This is the query that I am dealing with:

SELECT PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice AS InvoiceNumber,
CASE WHEN (AR.InvoiceDate = '')
THEN '0'
ELSE DATEDIFF(day, AR.InvoiceDate, ********* need to insert case statement to find the end of the period ************** ) END AS DaysOld,
SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount WHEN LedgerAR.TransType = 'CR' AND
LedgerAR.SubType = 'T' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) AS InvoiceBalance,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'I' THEN - 1 * LedgerAR.Amount ELSE 0 END) AS Interest,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'R' THEN LedgerAR.Amount ELSE 0 END) AS Retainage,
SUM(CASE WHEN (LedgerAR.WBS2 <> '9001' AND AR.Period = '200408') THEN - 1 * LedgerAR.Amount ELSE 0 END) AS total
FROM PR LEFT OUTER JOIN
AR ON AR.WBS1 = PR.WBS1 AND PR.WBS2 = '' AND PR.WBS3 = '' LEFT JOIN
LedgerAR ON AR.WBS1 = LedgerAR.WBS1 AND AR.WBS2 = LedgerAR.WBS2 AND AR.WBS3 = LedgerAR.WBS3 AND
AR.Invoice = LedgerAR.Invoice LEFT JOIN
LD ON PR.WBS1 = LD.WBS1 AND PR.WBS2 = LD.WBS2 AND PR.WBS3 = LD.WBS3 LEFT JOIN
CFGDates ON CFGDates.Period = AR.Period
WHERE (LedgerAR.TransType = 'IN') AND (LedgerAR.SubType <> ' X ' OR
LedgerAR.SubType IS NULL) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND (LedgerAR.Amount <> 0) AND
(AR.PaidPeriod > 200408) AND (PR.WBS1 = '001-298') OR
(LedgerAR.TransType = ' CR ') AND (LedgerAR.SubType IN (' R ', ' T ')) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND
(LedgerAR.Amount <> 0) AND (PR.WBS1 = '001 - 298 ')
GROUP BY PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice, Ar.Period, CFGDates.AccountPdEnd
HAVING SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) <> 0

Within the DateDiff function I need to find the end of the period using a case statment such as

Case When AR.Period='200408' Then Min(CFGDates.AccountPdEnd) Else '0' End As EndPeriod

The problem is that I am having a hard time integrating this into the top query. By using the case statement that I wrote above, I get an error stating that there is a problem near 'As' . I am wondering if there is a way to perform a case statement within a case statement.

Hope that clears things up.

Thanks,
Laura|||that's right, you cannot assign an alias to a value if that value is inside a function

just drop the As EndPeriod

oh, and note that you are looking for the difference in days between AR.InvoiceDate and the value of this case expression, so make sure not to use quotes around the zero if indeed you want december 31, 1899, which seems rather unlikely, but that is, i think, the date that corresponds to zero|||Here is a CASE statement that incorporates your logic:

CASE WHEN (AR.InvoiceDate = '') THEN '0'
WHEN AR.Period='200408' THEN DATEDIFF(day, AR.InvoiceDate, Min(CFGDates.AccountPdEnd))
ELSE DATEDIFF(day, AR.InvoiceDate, 0)
END AS DaysOld

...but whether it will integrate with the rest of your SQL statement, I do not know.|||blindman, that's the same as the nested version (you are allowed to nest CASEs, you know)

that date equal to an empty string gives me the creeps

and the quotes around the zero don't make me feel too hot either

also, i just noticed, CFGDates.AccountPdEnd is in the GROUP BY, so why even bother finding the MIN for it in the DATEDIFF, eh|||Oh, I certainly didn't mean to imply that was ALL that was wrong with her code.

What gives me the heebie-jeebies about it is all the hard-coded values.

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:

> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--

Need help combining views; DDL included

I've solved this issue by creating 3 views but I'd rather do it in 1 SELECT
if possible.
Given my data I want to select duplicate securities based on the cusip field
in the Securities table where the cusip does not exist in the Positions
table. To rephrase I want duplicate securities that are not held.
Given my sample data I want to return one record with the cusip value 'E'
Thanks to anyone who could help.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
CREATE TABLE [dbo].[Positions] (
[AccountID] [int] NOT NULL ,
[SecurityID] [int] NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[AccountID],
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
(
[SecurityID]
) REFERENCES [dbo].[Securities] (
[SecurityID]
)
GO
INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)Try This:
Select CusIP, Count(*)
From Securities
Where SecurityID Not In
(Select SecurityID
From Positions)
Group By CusIP
Having Count(*) > 1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||What sulld happen in case that you add this to your sample data:
INSERT INTO Securities (SecurityID,CUSIP) VALUES (8,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,8,10)
Would "E" still satisfy your request?
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Anyway,
If your asnwer is Yes:
Solution 1:
select
sec.CUSIP
from Positions pos right outer join Securities sec
on pos.SecurityID=sec.SecurityID
where pos.AccountID is null
group by sec.CUSIP
having count(sec.CUSIP)>1
Solution 2:
select
sec.CUSIP
from Securities sec
where sec.SecurityID not in
(
select pos.SecurityID from Positions pos
)
group by sec.CUSIP
having count(sec.CUSIP)>1
If your answer is NO:
select CUSIP
from Securities
where CUSIP not in
(
select sec.CUSIP
from Positions pos join Securities sec
on pos.SecurityID=sec.SecurityID
)
group by CUSIP
having count(CUSIP)>1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Terri
See Itzik Ben-Gan's script about duplicates
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Terri" <terri@.cybernets.com> wrote in message
news:d3f1h9$ocg$1@.reader2.nmix.net...
> I've solved this issue by creating 3 views but I'd rather do it in 1
SELECT
> if possible.
> Given my data I want to select duplicate securities based on the cusip
field
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>

Need Help 1505 Error

Hello All
I have a SQL 6.5 Server running on Service Pack 5a with a hotfix.
select @.@.version returns:
Microsoft SQL Server 6.50 - 6.50.451 (Intel X86)
Jul 1 1999 01:11:25
Copyright (c) 1988-1997 Microsoft Corporation

I am getting an error something like this:
Error 1505 Create unique index aborted on duplicate key. Primary key is '847474 '

When this is happening is after copying some data and during the creation of an index. I have 2 tables one called ITEM_INVENTORY the other ITEM_INVENTORY_WORK. The only difference between them is that the WORK table has an identity column.

I use a stored procedure to perform the following steps:
1. Truncate the table ITEM_INVENTORY
2. Drop all the indexes on ITEM_INVENTORY
3. Copy the data from the ITEM_INVENTORY_WORK table to the ITEM_INVENTORY table in 10000 row chunks using:
BEGIN TRAN
INSERT INTO ITEM_INVENTORY
SELECT <(COLUMN LIST)>
FROM ITEM_INVENTORY_WORK
COMMIT TRAN
4. Recreate all the indexes.

During step 4 (recreate indexes) the process fails and returns a 1505 error while creating the UNIQUE index: CREATE UNIQUE INDEX ITEM_INVENTORY_idx1 ON dbo.ITEM_INVENTORY(IC_ITEM_NO, CORP_CODE)

Now here is the kicker. There are no duplicates. I've checked and re-cheked and there are no duplicate records. I think this may be a bug, but I don't know. Has anyone encountered this problem and have any ideas on how to solve it?It's not about duplicare records - it's about duplicate IC_ITEM_NO, CORP_CODE.

Run this:

select IC_ITEM_NO, CORP_CODE
from ITEM_INVENTORY
group by IC_ITEM_NO, CORP_CODE
having count(*) > 1

See what It returns. There are no miracles|||Hi I ran the query:
select IC_ITEM_NO, CORP_CODE
from ITEM_INVENTORY
group by IC_ITEM_NO, CORP_CODE
having count(*) > 1

and I get nothing back. Like I said no duplicates.
The other crazy thing is the WORK table is truncated and recreated each day in a similar way but it does not error when recreating the index.

Regards
CaptainEstock|||Try this:
drop table ITEM_INVENTORY
select * into WORK

drop IDENTITY column
recrate indexes

Do not forget to set DB option to allow Bulk copy/select into|||Sorry,

select * into item_inventory
from work|||I can't drop columns in SQL 6.5

I might be able to do a SELECT with the column names...sql

Need Help - I Have Select Distict Problems

I'm having an issue with the following sql statement:

1SELECT Property.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice,2 Media.ThumbView3FROM PropertyLEFTOUTER JOIN4 MediaON Property.PropertyID = Media.PropertyID

I'd like to be able to select by the distinct Media.PropertyID instead of the Property.PropertyID.

Hi,

would you like to select only distinct Media.PropertyID only or distinct Media.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice, Media.ThumbView?

either way, just including a distinct keyword infront of your select query would be enough:

select distinct Media.PropertyID, Property.ListingEndDate, Property.ListingName, Property.StreetAddress, Property.City, Property.SalePrice, Media.ThumbView from ....

if propertyID is a repeating field you can use the GROUP BY clause to get what you want...

select Media.PropertyID, max(Property.ListingEndDate), max(Property.ListingName), max(Property.StreetAddress), max(Property.City) ...
from ...
GROUP BY Media.PropertyID

Hope this helps...

|||

I don't understand what I'm doing wrong with these select statements.

1SELECT2 Property.PropertyID,3COUNT(Property.PropertyID)AS PropertyCount,4SUM(CAST(Units.SpaceAvailableAS int))AS TotalSpace5FROM6 Property7INNERJOIN8 UnitsON Property.PropertyID = Units.PropertyID9GROUP BY10 Property.PropertyID

I get back:

PropertyID = 48

PropertyCount = 4<-- Not what I'm expect. I'm looking for: PropertyCount = 1

TotalSpace = 12500

What am I doing wrong here? 

|||

I take it I should have used a sub select statement.

- lot's to learn...

|||

eterry28:

I take it I should have used a sub select statement.

Hi,

im assuming you want only rows where PropertyCount = 1
you can simply use the HAVING clause:

SELECT
Property.PropertyID,
COUNT(Property.PropertyID)AS PropertyCount,
SUM(CAST(Units.SpaceAvailableAS int))AS TotalSpace
FROM
Property
INNERJOIN
UnitsON Property.PropertyID = Units.PropertyID
GROUP BY
Property.PropertyID
HAVING
COUNT(Property.PropertyID) = 1

Hope this helps...

Need Extra Rows

Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What I
need is to return an additional row when the earliest dtmready is after today.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query are
returned to a java program at a level where I do not have the ability to
create a new row. So, it would be ideal if I could create the sql that
returns a row with a dtmready of today with a count of 0 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> Background: I am running SQL Server 2000 SP4 and the results of the query
> are
> returned to a java program at a level where I do not have the ability to
> create a new row. So, it would be ideal if I could create the sql that
> returns a row with a dtmready of today with a count of 0 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>> Here is the basic sql I am trying to implement:
>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via SQLMonster.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
>>Hi
>>The easiest way to do this is to use a calendar table see
>>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>>CROSS JOIN dbo.calendar c
>>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>>u.rmpropid = '123'
>>GROUP BY v.classid, c.dt
>>ORDER BY c.dt
>>The cross join will get all date and classid combinations.
>>John
>> Here is the basic sql I am trying to implement:
>>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each
>> classid
>> that has a minimum dtmready > today.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>> Hi John. Thanks for the response. I do not think I will be allowed to
>> create
>[quoted text clipped - 27 lines]
>> classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1

Need Dynamic SQL Select turotrial

Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order.

Thank you

Do you mean full text searching? (Searching for a particular word within a lot of text)

|||

I have a database column 'Description' varchar(600) that contains product descriptions. I need to search it looking for any of the keywords contained in a Keyword string.

Example Keyword string - sail boat hardware

I need it to find all rows WHERE 'sail' or 'boat' or 'hardware' is found.

Thank you

|||

That form of search is more a full text search rather than a dynamic search. The latter is searching on one to many columns as selected at runtime.

|||

I am using a shared SQL2000 server. What I want to do is find a way in ASP.Net to take the keyword string entered and convert it into a Sql2000 select statement like below.

Keywords entered: sail boat hardware

SELECT Company, Phone, Description
FROM tblCompanyInfo
WHERE (tbl.CompanyInfo.Description LIKE '%' + "sail" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "boat" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "hardware" + '%')

|||

Look athttp://msdn2.microsoft.com/en-gb/sql/aa336289.aspx

|||

I am actualy looking for an ASP routine that splits the Keyword string and creates the OR elements for a Sql2000 compatible Sql SELECT command. I have looked at the many CONTAINS options discussed in the article but did not find any that offer a split like solution. As for a full text search, that option is not available at least not in the shared SQL environment that I am using.

Thank you

|||

In which case you need to roll your own text search solutions.

Create a Noise words table to contain words that you do not want indexed.

|||

Looks like someone should create a new ASP.Net SearchControl. A bunch of us out here could sure use it.

Thank you for the follow up.

sql

Need correction...

I want to display a list of member after filtering two condition...

1) Select MID from TableMember where AID value EQUAL to Dropdownlist.value
2) Select MID from TableStatus where AID NOT EQUAL to Label.Text

I try something like this... is not working... Can anyone correct the statement for me...

"Select * from VIEW1 where MID in (Select MID from TableMember where Aid='" & Ddl1.SelectedItem.Value & "') and MID not in(select Mid from TableStatus where Aid <>'" & Label.Text & "')"

Thank you...do TableMember and TableStatus have the same MID column ?

if so, try

select tablemember.* from tablemember,tablestatus
where tablemember.mid=tablestatus.mid and
tabmemember.aid=@.dpval and tablestatus.aid <> @.label

and pass the parameters accordingly.

hth|||Yes, TableMember and TableStatus have the same MID column

But one more thing is I am using a VIEW from SQL Server.

Which I think you have left out ?|||treat it as a table and add another join stmt to join the view with the other tables...

hth

Need Better Performance

Dear friends,
I am having 10GB for my database. In my table having 10 Millian records per day. I want to select records per date and by product status. So,

1. How can I design my Database initialization paramet ?

2. How can I tune my database for better performance.

My Machine Configuration :

Windows 2003 Server OS,1 GB Ram, 80 GB Hard Disk, 3.99 GHz

We need more details about your hardware and environment, along with your table schema and one of the queries you want to tune.

Having a single hard drive for a SQL Server instance is going to be a big bottleneck.

Need basic SQL query help

Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.

The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
The two fields I need to use to identify unique records are:
serial_IP and site_id
In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.

thanks

noclueAs those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as

SELECT DISTINCT serial_IP, site_id FROM your_table;

However, regarding your next request (selecting records with the latest date), there's need to use a subquery:SELECT DISTINCT t.serial_IP, t.site_id
FROM your_table t
WHERE t.date_column = (SELECT max(t1.date_column)
FROM your_table t1
WHERE t1.serial_IP = t.serial_IP
AND t1.site_id = t.site_id
);The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.

Need Basic Help

At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.Hi
You can use VFP as a front end.
For a starter try checking out:
http://msdn.microsoft.com/archive/d...asp?frame=true
http://msdn.microsoft.com/library/d...asp?frame=true
http://msdn.microsoft.com/library/d...tio
n.asp
John
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> At my workplace our ERP software in on a VFP database. I have learned
> quite
> a bit about extracting data with the select statements from the VFP
> database. We are in the talks of converting the the SQL version of our
> ERP
> and I have some very basic questions since I am extremely new to the SQL
> world.
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>|||Hi,
You'll get much better help posting questions like this in a FoxPro
newsgroup. Many FoxPro developers use SQL Server as a back end for their
applications and have much experience with it. SQL Server is a database
only - it has no front end to produce forms.
You can use the exact same SQL commands that you use in Query Analyzer to
access SQL Server via SQL Pass-through. Please look at SQLConnect,
SQLStringConnect, and SQLExec in the VFP Help.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>

Need Basic Help

At my workplace our ERP software in on a VFP database. I have learned quite
a bit about extracting data with the select statements from the VFP
database. We are in the talks of converting the the SQL version of our ERP
and I have some very basic questions since I am extremely new to the SQL
world.
I have created executable forms in VFP that queries the VFP tables for
certain users. Can I continue to use these forms or do I need to go to
another software. Is my understanding correct that SQL cannot create forms
like the VFP does? If I can use the VFP interface what is the Select syntax
to access the SQL database from within the VFP interface?
The VFP looks like this: Select 'field names' from 'database name'!'table
name'
What would the SQL look like? I can use the Query Analyzer to do Select
statements but I would like to be able to query the SQL database from within
the VFP interface. Thanks for your help.
Hi
You can use VFP as a front end.
For a starter try checking out:
http://msdn.microsoft.com/archive/de...asp?frame=true
http://msdn.microsoft.com/library/de...asp?frame=true
http://msdn.microsoft.com/library/de...pplication.asp
John
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> At my workplace our ERP software in on a VFP database. I have learned
> quite
> a bit about extracting data with the select statements from the VFP
> database. We are in the talks of converting the the SQL version of our
> ERP
> and I have some very basic questions since I am extremely new to the SQL
> world.
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>
|||Hi,
You'll get much better help posting questions like this in a FoxPro
newsgroup. Many FoxPro developers use SQL Server as a back end for their
applications and have much experience with it. SQL Server is a database
only - it has no front end to produce forms.
You can use the exact same SQL commands that you use in Query Analyzer to
access SQL Server via SQL Pass-through. Please look at SQLConnect,
SQLStringConnect, and SQLExec in the VFP Help.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"Preacher Man" <nospam> wrote in message
news:%23Fbln$GwFHA.3548@.tk2msftngp13.phx.gbl...
> I have created executable forms in VFP that queries the VFP tables for
> certain users. Can I continue to use these forms or do I need to go to
> another software. Is my understanding correct that SQL cannot create
> forms
> like the VFP does? If I can use the VFP interface what is the Select
> syntax
> to access the SQL database from within the VFP interface?
> The VFP looks like this: Select 'field names' from 'database name'!'table
> name'
> What would the SQL look like? I can use the Query Analyzer to do Select
> statements but I would like to be able to query the SQL database from
> within
> the VFP interface. Thanks for your help.
>
>

Need assistance with Duplicate Select Statment

Any help would be appreciated here. My question is two part:

1.) Select duplicates that match any of a number of columns for example...

The email is the same OR

The homephone is the same OR

The mobilephone is the same OR

The address1 is the same

The uniqueID is ConsIntID

My Select Statement: (Which does not work)

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS

WHERE CONSINTID IN (SELECT CONSINTID

FROM CONSULTANTS

HAVING COUNT(HOMEPHONE) > 1 OR COUNT(MOBILEPHONE) >1 OR etc.... )

-

Once the Select Duplicates Statement is corrected I will insert those into a ConsultantsDupe Table

The Second Question is: If I have more then one duplicate my stored_procedure fails

Here is my cursor and logic. The problem is getting multiple results in the subquery and I don't know the best way to correct this.

DECLARE DUPES CURSOR FOR

SELECT CONSINTID, FIRSTNAME, LASTNAME, EMAIL1, ADDRESS1, HOMEPHONE, MOBILEPHONE, CREATEDATE, USERIDS

FROM CONSULTANTS_DUPS

ORDER BY CREATEDATE DESC

OPEN DUPES

FETCH NEXT FROM DUPES

INTO @.CONSINTID, @.FIRSTNAME, @.LASTNAME, @.EMAIL1, @.ADDRESS1, @.HOMEPHONE, @.MOBILEPHONE, @.CREATEdATE, @.USERIDS

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.FOUNDCONSINTID = (SELECT DISTINCT CONSINTID FROM CONSULTANTS_DUPS

WHERE CONSINTID <> @.CONSINTID AND HOMEPHONE = @.HOMEPHONE "NEED TO ADD COMPARISON FOR MOBILEPHONE, EMAIL ADDRESS HERE") <== This can provide more then one result which messes me up.

I'll take a couple shots at this. Hopefully somebody can post something even better. You'll have to forgive me. Without any sample tables I had to "shoot from the hip" and write it without any syntax checking.

SELECT * FROM Consultants C

WHERE EXISTS (SELECT * FROM Consultants CPhone

WHERE (C.HomePhone = CPhone.HomePhone

OR C.HomePhone = CPhone.CellPhone

OR C.CellPhone = CPhone.HomePhone

OR C.CellPhone = CPhone.CellPhone)

AND C.CONSINTID <> CPhone.CONSINTID

)

OR EXISTS (SELECT * FROM Consultants CEmail

WHERE C.EMAIL1 = CEmail.EMAIL1

AND C.CONSINTID <> EMAIL1.CONSINTID)

OR EXISTS ...

Here's another shot:

SELECT * FROM Consultants

WHERE HomePhone IN (SELECT HomePhone

FROM Consultants

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT CellPhone

FROM Consultants

WHERE CellPhone IS NOT NULL

GROUP BY CellPhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

This method suffers from the fact that you can't check HomePhone and CellPhone at the same time (with UNION ALL) because of a contact has the same phone number listed as home and cell then it could show up as a duplicate.

Here's where you might take the previous query as a natural transition to cross-check home phone against cell phone:

SELECT * FROM Consultants C

WHERE HomePhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR CellPhone IN (SELECT HomePhone

FROM (SELECT CONSINTID, HomePhone

FROM Consultants UNION

SELECT CONSINTID, CellPhone

FROM Consultants) SubQ

WHERE HomePhone IS NOT NULL

GROUP BY HomePhone

HAVING COUNT(*) > 1)

OR Email1 IN (SELECT Email1

FROM Consultants

WHERE Email1 IS NOT NULL

GROUP BY Email1

HAVING COUNT(*) > 1)

You may also be better off with multiple SELECT statements as it's hard to tell the cause of your duplicate in a query like this. Keep in mind that all of these queries are very read-intensive as you're dealing with multiple scans across your table. You may benefit, instead, by placing your duplicates in a temporary table (or table variable) and then querying against the temporary table.

|||something like this:

select *
from consultants
where consintid in
(select consintid
from consultants
group by email
having count(*)>1
union all
select consintid

from consultants

group by homephone

having count(*)>1

union all
select consintid

from consultants

group by cellphone

having count(*)>1

union all
select consintid

from consultants

group by address1

having count(*)>1
)|||

Jared,

Yours worked! This gives me what I need for the duplicate temp table and the assistance is greatly appreciated.

Any feedback on the second portion?

Need alernative to cursor

Hey guys and gals - here's my current code

DECLARE @.uID varchar(12)

DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
FROM gvAbsence

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @.uID

WHILE @.@.FETCH_STATUS = 0 BEGIN
UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6) ELSE Floor(Rand() * 5) END
WHERE unique_identifier = @.uID

FETCH NEXT FROM MyCursor
INTO @.uID
END

CLOSE MyCursor
DEALLOCATE MyCursor

SELECT *
FROM gvAbsence

Which as you can see, sucks.
It cursors through nearly 4K records assigning a new random number (0-5) to an employees holiday_brought_forward. Naturally, this is an absolute ballache... I just can't think of a better solution at current so any advice you can give would be greatly appreciated :cool:

To put it simply, I want to assign a random number between 0 and 5 (inclusive) to each record in the table gvAbsence

-GeorgeWhich bit you stuck with? Version of SS?
ss5k method of getting a random number between 0 and 5. Not sure if it is the best way of course. With a suitable bit of SQLery I think you can get a random number per person.

select *
from
(select number
, rn = ROW_NUMBER() OVER (ORDER BY newid())
from dbo.numbers
where number between 1 and 5) AS der_t
where rn = 1

Did you try googling?|||SS 2K.
And the above code works how I want it to, but I was wondering if there was a better alternative to using a cursor?

--Return random number between 0 and 5 inc
SELECT Floor(Rand() * 6)

(Can Rand() return 1?)

I've just progressed on with my problem with the cursor and all is well, apart from it taking a full minute to cursor through and apply other updates etc.|||Define random for me:
Does the code need to generate a different number for a particular member of staf each time it is run? Does it need to generate an even distribution for the 4k records? Basically - do you need a truly random number?|||I am creating a test environment and to replicate the scenario I needed to assign all sorts of different values to certain fields (in this case holiday_bought_forward) to that I could test a bunch of different permeatations of the data.

It does not need to do it every time the code is run, except when I need to reset the data (and during testing the data will need reset a good number of times).

No, does not need to have an even distribution across the 4K records, truly random will do just fine ;)

It is not a high priority issue as it's only for the test environment but speeding things up a bit would be nice :p

I don't like cursors all that much (you guys have always told me not to use them!) so I was looking into what alternatives exist

Thanks Poots|||Does gvAbsence have a primary key? If so, extract them into a 2 column table, and when you extract them use the RAND function to generate your random value.

Since the RAND function returns a random float value between 0 and one, you will have to multiply by 5 and take the integer value (lookup FLOOR in BOL)

Hopefully this will get you started.

EDIT: Once you have this table, you can update gvAbsence with the random value from the table for a quick reset that will restore identical values each and every time you need a reset.|||My understanding was that rand won't work in a set based query.|||If I was to keep my cursor, I could use something like a counter instead of a random number for consistancy.
Something like

DECLARE @.Counter int
SET @.Counter = 0

UPDATE gvAbsence
SET holiday_brought_forward = @.Counter % 5
WHERE unique_identifier = @.uID

SET @. Counter = Counter + 1

I dunno, I still don't like cursoring...|||Aha! So nothing like random at all!

This is much easier now - we are deteriministic. Your people got a surrogate key (I know you like em ;))? Why not perform mod on that instead of an increment? Easy & set based.|||One issue I do have now is that the holiday_bought_forward has some constraints; after 5 years of service you're entitled to an extra days holiday -this means you can only bring over maximum of 4 days. (basically a total of 30).

I've actually finished my script now, but I'm simply not happy with the cursor. Like I said - this is only test data that I'm creating to replicate live stuff - so it doesn't have to be the same every time - it simply has to contain permeatations of every possibility (which it does).

If anyone's interested in seeing the redicuous script I had to write for this, just let me know :p|||Let's create some test data

select 'unique_identifier'=id, 'years_of_service'=id, 'holiday_brought_forward'=0
into #gvAbsence from sysobjects where id<20

Instead of using the the rand() function that does not work as expected use a numbers table e.g.

select top 6 seqno=identity(int) into #numbers from syscolumns

And instead of hard coding data into your code lets use a holiday_constraints table.
Now if the conditions chance you don't have to update code, just the data in your table.

create table #holiday_constraints (maxdays int, yrlow int, yrhigh int)
insert into #holiday_constraints select
6,0,5 union all select
5,6,999999999

Then the update.
Note: An unnecessary where clause in the sub select to force execution for every row.

update #gvAbsence
SET holiday_brought_forward =
(select top 1 seqno from #numbers c where seqno<=b.maxdays and a.unique_identifier=a.unique_identifier order by newid())
from #gvAbsence a, #holiday_constraints b
where a.years_of_service between b.yrlow and b.yrhigh

select * from #gvAbsence|||Unfortunately I'm using SQL Server 2000; TOP won't work.|||Also, there are more rules involved and the added problem of generating a random number between -5 and a conditional top value
I'm thinking that in this case a cursor might be my only solution.

I apologise, I don't think I made my problem/question very clear from the beginning but thanks everyone for your help! My final mark up of the cursor is below - the really clever line is the SET ;) Lovely formula right thar!

DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
,holiday_entitlement
,holiday_brought_forward
FROM gvAbsence

OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf

SET @.Min = -5
WHILE @.@.FETCH_STATUS = 0 BEGIN
SET @.Max = CASE @.Ent + @.Bf
WHEN 30 THEN 0
WHEN 29 THEN 1
WHEN 28 THEN 2
WHEN 27 THEN 3
WHEN 26 THEN 4
WHEN 25 THEN 5 END
UPDATE gvAbsence
SET holiday_bought = Floor(Rand() * (@.Max - @.Min + 1)) + @.Min
WHERE unique_identifier = @.uID

FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf
END
CLOSE MyCursor
DEALLOCATE MyCursor|||Unfortunately I'm using SQL Server 2000; TOP won't work.Howdya figure that?|||I didn't think TOP was implemented in 2000?
Any time I try run a TOP statement in QA I get

SELECT TOP 5 surname
FROM employee
ORDER BY surname ASC
--
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.

Errors!|||From SQL 2K BoL:
Limiting Result Sets Using TOP and PERCENT

The TOP clause limits the number of rows returned in the result set.
TOP n [PERCENT]
n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */TOP 15 PERCENT /* Return the top 15% of the result set. */.If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.The top keyword changed in 2005 only in so far as you can use a variable.|||Then how come I can't execute SELECT TOP statements? :confused:|||Have you checked the compatability level of the database?|||Works for me

USE Northwind
GO

SELECT TOP 5 LastName
FROM employees
ORDER BY LastName ASC
GO|||Ummm...assuming your "algorithym" is correct, why couldn't you just do

USE Northwind
GO

CREATE TABLE myTable99(unique_identifier int, years_of_service int, holiday_brought_forward int)
GO

INSERT INTO myTable99(unique_identifier, years_of_service)
SELECT 1, 1 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 30
GO

UPDATE g
SET holiday_brought_forward =
CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6)
ELSE Floor(Rand() * 5) END
FROM myTable99 g
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||... because rand() will return the same value for all rows.:)|||Not necessarily...

select case floor (rand()*5)
when 1 then 1
when 2 then 2
when 3 then 3
when 4 then 4
else 5 end
from [table of your choice]

This came up in a very nice SQL puzzle posted here some time ago. For those of you who are interested...

select case floor (rand()*5)
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]

EDIT: Due to the nature of the "oddity", it may not be a truly random sampling. Sounds like it may be random enough for you, though.|||Well pummel my piles with a paddle. What is that about then? The case statement causes the rand to be re-evaluated for each row rather than once for the set? And what's the deal with 'WTF'? I mean - wtf? You got a link?|||Actually, rand() is invoked on each row of the output. This is why inline user defined functions can get you in to piles of problems that will pummel your performance paddle(s) past pertinent perturbation.

Sadly, I do not have a link to the original puzzle. It may have been lost in the troubles that DBForums was going through some time back when posts just up and disappeared. Besides, it will likely be a good exercise for you to figure out what is going on in there.

DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.|||Oh noblet - I forgot about the 0 :rolleyes:

But anyway:

select case 1
when 1 then floor (rand()*5) end
from sys.databases

select case 1
when 2 then NULL else floor (rand()*5) end
fromsys.databases

select case floor (rand()*5)
when -1 then NULL else floor (rand()*5) end
from sys.databases

Coooooooooooooooo.|||DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.Ah jolly good! I was wondering what subtle distinction you were making :)|||tsk tsk tsk. Perhaps I should have taken the 0 into consideration, as well...

select case floor (rand()*5)
when 0 then '0'
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]

Extend as necessary to convince yourself this is not so easy as all that.|||neat trick! this seems to work as well without the need for laying out all the possibilities in the case:

select case when name is not null then rand() else NULL end from sys.objects

all you need is something to check in the when clause that will always be true, but that the optimizer won't optimize away. 1=1 won't work. That is, this is broken:

select case when 1=1 then rand() else NULL end from sys.objects|||UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN
CASE WHEN 1=1 THEN Floor(Rand() * 6) ELSE NULL END
ELSE
CASE WHEN 1=1 THEN Floor(Rand() * 5) ELSE NULL END
END

Does not give the desired result - (same problem as before)

UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*5)
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END

Same goes for this *sigh*
I can understand the use of CASE statements to make sure every row is evaluated individualy - but that doesn't appear to be the case when running the above...

this might sound silly but in other languages you need to use

Randomize

Would that apply here? *shrugs*
thanks for the input so far :)|||Can you create a derived table and update from that rather than directly with the case statement?|||Do you mean something like this?

IF EXISTS (SELECT NULL FROM sysobjects WHERE type='U' AND name='gvVals') BEGIN
DROP TABLE gvVals
END

CREATE TABLE gvVals
(
bought_forward int
)

INSERT INTO gvVals(bought_forward) VALUES(1)
INSERT INTO gvVals(bought_forward) VALUES(2)
INSERT INTO gvVals(bought_forward) VALUES(3)
INSERT INTO gvVals(bought_forward) VALUES(4)
INSERT INTO gvVals(bought_forward) VALUES(5)
INSERT INTO gvVals(bought_forward) VALUES(6)

UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END
FROM gvVals

Holiday Brought Forward
--
5.0
5.0
5.0
5.0
5.0
etc|||Text file containing the cursored SQL uploaded for your amusement / berrating ;)
EDIT: and I guess comments, too|||I could not get different values for rand() using the case statement on SS2K
Generating a random value from newid() seems to work
UPDATE gvAbsence
SET holiday_brought_forward =
right(convert(char(2),(convert(int, (convert(varbinary(1),right(newid(),1))))) ),1)/10.*5+1|||pdreyer - wow, that appears to work (and quickly!)
But I'm afraid I have absolutely no idea what it is doing ;)
/10.*5+1 ?? I removed the point and it didn't work - what's that doing :)

Seems like an aweful lot of converts in there and I can't for the life of me understand the char convert!

Hehe, thanks for that - I'd love to implement it, but only if I can understand it first :D

EDIT: I have removed the +1 as I want to include zero values :) I love the fact it even does halves! I hadn't even considered that before :)
RE-EDIT: Not quite returning the values I need... I need 0-5 inclusive and 0.5's would be lovely (but not essential).
/10.*5 returns 0 - 4.5 inc
/10.*5+1 returns 1 - 5.5 inc|||Do you mean something like this?Nope :)

But it sounds like rand() is working differently in 2k anyway. I don't have any 2k boxes to play with and test so I'll drop out at this point.|||Awww but Pooooooooots!
Thanks for all your prior help bud - it is appreciated :)
and if that's not what you meant... what is! :p|||Did we not cover derived tables in your last thread (or thread but one)? Genuinly can't remember for sure...|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?

And did you see what I did before?

so I'll drop out at this point

I don't know why but I'm finding it harder and harder to ask questions here - easy enough to answer (some) but when I ask them I go into "newb" mode and yeah - it sucks :(|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?
No. But you might be able to do it as a sub select.

Derived table example:

UPDATE gvAbsence
SET holiday_brought_forward = derived_table.random_number
FROM dbo.gvVals
INNER JOIN
(SELECT MyPK
, CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END AS random_number
FROM dbo.gvVals) AS derived_table
ON derived_table.MyPK = gvVals.MyPk


And did you see what I did before?Dunno - what did you do?|||I have absolutely no idea what it is doing
it uses the ASCII code of a character from newid() as a random number (caveat: limited range but OK for your needs)
can't understand the char convertNot needed, should be removed.
what's the point doingIt ensure decimal arithmetic else the integer devide will result in zero
it even does halvesThat was not the intended result. I assumed the target field was an integer, So to get values 0-5 multiply by 6 and convert to int at the end. e.g.
select convert(int,right(ascii(newid()),1)/10.*6) from sysobjects

Need a way to exclude duplicate information

I've created a query:
SELECT DISTINCT co.CL_Desc,a.AN_Key,a.CL_Code,a.AT_Code,a.AN_Refer ence,a.AN_ToDo,a.An_Closed
,at2.CU_Value2,an1.CU_Value1,at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN Action a ON co.CL_Code = a.CL_Code /* Get actions */
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference

And it returns somthing like

CompanyA,Enquiry,04/09/2003
CompanyA,Contract,05/09/2003
ComanyB,Enquiry,04/09/2003

And so on
I know this is not true duplcates but for me its a problem. I only want the last record of each company to display being the most recent record. I thought I union query may have helped to solve this, but no luck has anyone got any ideas?What is your last record? The record with the highest ID per company? Select those ID's per company, and INNER JOIN the result with your original table!|||DB's general solution in code:
(I assumed that AN_Closed was the date value and that you wanted the latest one, rather than an ID value.) Because you are using OUTER joins, I think you need two levels of nesting to be sure you get all the data you want, but I couldn't test the code.

SELECT DISTINCT co.CL_Desc, a.AN_Key, a.CL_Code, a.AT_Code, a.AN_Reference, a.AN_ToDo, a.An_Closed, at2.CU_Value2, an1.CU_Value1, at3.CU_Value5 /* INNER JOIN FIELDS */
FROM clients co
LEFT OUTER JOIN /* Get last actions */
LEFT OUTER JOIN
(select An_Key, CL_Code, AT_Code, AN_Reference, AN_ToDo, AN_Closed
from Action
inner join (select CL_Code, max(AN_Closed) Last_AN_Closed from Action group by CL_Code) Last_Values
on Action.CL_Code = Last_Values.CL_Code and Action.AN_Closed = Last_Values.Last_An_Closed) a
ON co.CL_Code = a.CL_Code
LEFT OUTER JOIN ActionCustomTxt2 at2 ON a.AN_Key=at2.AN_Key /* Project checking */
LEFT OUTER JOIN ActionCustomNum1 an1 ON a.AN_Key=an1.AN_Key /* Date created */
LEFT OUTER JOIN AECTESUSER.ActionCustomTxt3 at3 ON a.AN_Key=at3.AN_Key /* Diagnostic levels */
WHERE AT_Code IN ('ENQUIRY','DIAGNOSTIC','ONWARD REFERRAL','CONSULTANCY/CONTRACT')
AND at2.CU_Value2='40201'
AND (an1.CU_Value1 BETWEEN 37851 AND 37857)
ORDER BY CL_Desc,a.AN_Reference

blindman