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 dump 200 columns to a flat file

All,

I need to dump a table with more than 200 columns to a flat file. I tired in SSIS with the flat file destination, but it only allows 84 columns. What should I do if I want to dump all the columns to a text or cvs file.

Thanks in advance

Try the Export Data Wizard in SSMS.

Right-click on the database-->Tasks-->Export Data...

-Jamie

|||

Hi, Jamie,

I have to make it as part of my SSIS packages. Is there a way to do it within SSIS?

Thanks

|||

Yeah absolutely. Why not take the package constructed by the wizard and either:

1) Build your additional requirements into that package or

2) Copy and paste the objects from the wizard-created package into the package that you are trying to build.

-Jamie

|||Can you elaborate how the FF destination does not let you have more than 84 columns?

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 assistance

Am a newbie to SQL QUERIES (reporting) - Please assist
Scenario:
I have a simple table where I have columns like
empno
empname
latedate
reasonlate
justification
This table contains mulitple data for a single empno as you can see the
structure
for eg:
empno empname latedate reasonlate justification
101 Kevin 10/1/2005 Business Training day 1
101 Kevin 10/2/2005 Business Training day 2
101 Kevin 10/3/2005 Business Training day 3
103 Tracy 10/1/2005 Personal Sick
103 Tracy 10/3/2005 Business Seminar
...
...
I need to have a report like
Kevin (101)
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy
10/1/2005 Personal Sick
10/3/2005 Business Seminar
FYI: I also have another table as EmpMaster with empno and name, so
that I can fetch those data from there.
Someone please assist me in preparing this report
Thankyou in advance
Best Regards
ShaninThis script returns data for a specified employee:
declare @.emp_name <datatype>
set @.emp_name = 'Kevin'
select latedate
,reasonlate
,justification
from dbo.EmpMaster
inner join <other_table>
on <other_table>.empno = dbo.EmpMaster.empno
where (dbo.EmpMaster.empname = @.emp_name)
You could design a procedure based on the query above.
To help you find a better solution we'd need to see DDL, more sample data
and preferably a more elaborate description of expected results.
ML|||Thank you ML for the reply
I actually need to use this script in an ASP file and generate a
report, I will not be able use hard coding, should be dynamic.
I forgot to provide the information that the core data is in a table
called HRDATA where I have those columns
empno
empname
latedate
reasonlate
justification
and have another master table viz. EmpMaster just in case we need to
join or fetch empno and empname alone
By the way what's DDL ?
Regards
Shanin|||DDL = Data Definition Language
For relevant info, please see:
http://www.aspfaq.com/etiquette.asp?id=5006
If you need to access data from a web page, then using a stored procedure is
the best way to do it. The procedure itself may not be dynamic, but the
purpose it serves is far from being "hard coded".
After you provide us with better specifications, we can help you design a
better solution.
Maybe this might also be a genuine opportunity to wipe the dust from your
copy of Books Online. :)
ML|||The specification was the one I provided above, ok let me be more
precise
I have a table viz. HRDATA andthe columns to be considered from this
are
empno - float(8)
empname - varchar(50)
loccode - varchar(50)
latedate - datetime
reasonlate-varchar(100)
justification-varchar(900)
approved-numeric(9)
now each time a employee submits his data from a ASP web form for a
certain date, a record is created in this table, hence for each date he
is late or absent he shall submit his justification, and hence the
records are like below
empno empname loccode latedate reasonlate justification
approved
101 Kevin JJ 10/1/2005 Business Training
day 1 1
101 Kevin JJ 10/2/2005 Business Training
day 2 1
101 Kevin JJ 10/3/2005 Business Training
day 3 1
103 Tracy OL 10/1/2005 Personal Sick
2
103 Tracy OL 10/3/2005 Business Seminar
1
now the HR Manager needs a report like the one below, grouped by
Empname, number and loccode
Kevin (101) - JJ
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3
Tracy (103) - OL
10/1/2005 Personal Sick
10/3/2005 Business Seminar
I have another master table viz. EmpMaster where I have all the
employees name, no, and loccode. Hope am repeating what I said in my
first post.
Ths is the actual scenario, I guess it's very simple for SQL GURUS..
Regards
Shanin|||Thankx guys, I have made it possible through ASP coding itself.
Regards
Shanin|||> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
The query below will provide the needed data. However, the grouping in your
specification is really report formatting (i.e. group section headers) and
is beyond the scope of SQL. The particulars depend on your reporting tool.
SELECT
empno,
empname,
loccode,
latedate,
reasonlate,
justification
FROM HRDATA
ORDER BY
empname,
empno,
loccode
The EmpMaster table isn't needed since HRDATA contains the required info.
In fact, redundant non-key data like empname is a big red flag in a
relational database. There are some cases where redundancy is deliberately
introduced but I get the feeling that poor design is the case here. Which
name would your HR Manager expect when you have different names in both
tables for the same employee (empno)?
In the future, please include the DDL scripts and sample data like the
following. Many of your fellow SQL Server community members will take the
time to develop and test a solution to your problem. Scripts eliminate
ambiguity and is quite time consuming to construct these scripts from
narrative.
CREATE TABLE HRDATA
(
empno float(8),
empname varchar(50),
loccode varchar(50),
latedate datetime,
reasonlate varchar(100),
justification varchar(900),
approved numeric(9)
)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/1/2005', 'Business', 'Training day 1', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/2/2005', 'Business', 'Training day 2', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/3/2005', 'Business', 'Training day 3', 1)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/1/2005', 'Personal', 'Sick', 2)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/3/2005', 'Business', 'Seminar', 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Cupid Shan" <shaninraja@.gmail.com> wrote in message
news:1130751379.384631.160800@.g44g2000cwa.googlegroups.com...
> The specification was the one I provided above, ok let me be more
> precise
> I have a table viz. HRDATA andthe columns to be considered from this
> are
> empno - float(8)
> empname - varchar(50)
> loccode - varchar(50)
> latedate - datetime
> reasonlate-varchar(100)
> justification-varchar(900)
> approved-numeric(9)
> now each time a employee submits his data from a ASP web form for a
> certain date, a record is created in this table, hence for each date he
> is late or absent he shall submit his justification, and hence the
> records are like below
> empno empname loccode latedate reasonlate justification
> approved
> 101 Kevin JJ 10/1/2005 Business Training
> day 1 1
> 101 Kevin JJ 10/2/2005 Business Training
> day 2 1
> 101 Kevin JJ 10/3/2005 Business Training
> day 3 1
> 103 Tracy OL 10/1/2005 Personal Sick
> 2
> 103 Tracy OL 10/3/2005 Business Seminar
> 1
> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
> Kevin (101) - JJ
> 10/1/2005 Business Training day 1
> 10/2/2005 Business Training day 2
> 10/3/2005 Business Training day 3
> Tracy (103) - OL
> 10/1/2005 Personal Sick
> 10/3/2005 Business Seminar
> I have another master table viz. EmpMaster where I have all the
> employees name, no, and loccode. Hope am repeating what I said in my
> first post.
> Ths is the actual scenario, I guess it's very simple for SQL GURUS..
> Regards
> Shanin
>

need advise on DELETE action.

Hi friends
i've a table that has 3 columns that refer to same parent table(foreign keys).am trying to following as part of action.

ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_BATCHTO FOREIGN KEY
(
FK_BATCHTO_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE NO ACTION
ON DELETE NO ACTION

GO
ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_FEESCALER FOREIGN KEY
(
FK_FEESCALER_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE set null
ON DELETE set null

GO
ALTER TABLE dbo.u_childTable ADD CONSTRAINT
FK_childTable_MASTER_SUBDEBT FOREIGN KEY
(
FK_SUBDEBT_MASTERID
) REFERENCES dbo.u_master
(
MASTERID
) ON UPDATE set null
ON DELETE set null

GO

but it wont let me complaining with following error
"Introducing

FOREIGN KEY constraint 'bl..blahh' on table 'childTable' may cause cycles or

multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO

ACTION, or modify other FOREIGN KEY constraints."

what am i missing here
You may only have one constraint and one refernece action for an action defined for the relation between two tables in your situation.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Please check out the Books Online topic below for restrictions on using cascading actions on referential constraints. They are same for SQL Server 2000 and 2005.

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

|||

Thanks for the post Jens.

i dont think i got the point but while investigation this issue i found following MSDN link

http://support.microsoft.com/kb/q321843/

above link says that

"the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree. "

but am not specifying any "cascade" as action. am setting either "SET NULL" or "NO ACTION".

much appreciated if you can guide me to understand this concept better.

|||or does it mean that i also need to check other foreign keys defined on this child table ?|||Any SET option (SET NULL or SET DEFAULT) is also a cascading action. So the restriction applies to SET NULL, SET DEFAULT and CASCADE. SET NULL|DEFAULT is a special case of cascade where the value is set to NULL or DEFAULT on column(s) respectively.|||

Thanks for the post Umachandar.

it also says same as my above link

" No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table."

is there any way to get the this tree graphically .

btw how would you handle such a scenario with database desgin ?

Thanks

|||You could get the tree using a recursive CTE in SQL Server 2005. The way to handle this if you hit the restrictions is to enforce the referential integrity rules (part of it) using triggers in SQL Server.|||>>The

way to handle this if you hit the restrictions is to enforce the

referential integrity rules (part of it) using triggers in SQL Server.

Thanks for this. Thats what we decided to do now.

>>You could get the tree using a recursive CTE in SQL Server 2005.
i know CTE but dont know how to use in this regard. can you give me some idea
Thanks again
|||

See below link for more details on how to write a recursive CTE:

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

|||Thanks for that.
Thats what i've been looking and am getting error

"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

btw am trying this
with joins as
(
select child,child_column from vJoins
where parent='mytable'
union all
select fkeys.child,fkeys.child_column from vJoins fkeys
inner join joins on fkeys.parent = joins.child
)
select * from joins

btw the view "vJoins" is nothing but list from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view

can you see what the error is?
Thanks for ur help|||

The error message happens because you are not specifying the termination condition for the recursion. See below for an example:

with consts (child_schema, child_table, parent_schema, parent_table)
as (
select ctu1.TABLE_SCHEMA, ctu1.TABLE_NAME, ctu2.TABLE_SCHEMA, ctu2.TABLE_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc
join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu1
on ctu1.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA

and ctu1.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu2
on ctu2.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA

and ctu2.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
),
const_tree (child_schema, child_table, parent_schema, parent_table, path, level)
as (

/* get all parents except for the self-referential ones, we will do that later */
select distinct parent_schema, parent_table
, cast(NULL as nvarchar(128)), cast(NULL as nvarchar(128))

, cast(N'' as nvarchar(4000)), cast(0 as int)
from consts
where not (child_schema = parent_schema and child_table = parent_table)
union all
select fk.child_schema, fk.child_table, fk.parent_schema, fk.parent_table
, p.path + '/' + quotename(p.child_schema) + '.' + quotename(p.child_table)
, p.level + 1
from consts as fk
join const_tree as p
on fk.parent_schema = p.child_schema
and fk.parent_table = p.child_table
)
select parent_schema, parent_table, child_schema, child_table, path, level
from const_tree
where level > 0
union all
select parent_schema, parent_table, child_schema, child_table, '/' + quotename(child_schema) + '.' + quotename(child_table), 0
from consts
where (child_schema = parent_schema and child_table = parent_table)
order by path;

|||

wonderful UmaChandar

will give it a try tomorrow and update you.

Thanks again :)

|||hi UmaChandar
i tried that and still getting same error with one of our main (bigger) databases
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

how can i troubleshoot it?
btw it works with one of our smaller databases which has very few joins.
Thank you very much|||It is possible that there are many ways to get cycles with referential constraints. The simple case with the self-referential constraint was easy to eliminate. You can usually determine the rows that cause the cycles by looking at the partial result set. Look for rows that have repeating values for example. Once you locate such rows, you need to identity how the cycle happens so that you could detect it in the recursive member of CTE.

Need a way to switch specific data from columns

Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort.

If you have an ID column all you would need to do is check if there are multiple records (count(*)> 1). if so keep the first one (min(id) or whichever you choose), delete the rest, get the two values into local variables and update the record in hand.
if you already made an attempt post some code and we can help you out.

Need a way to change report page width at runtime

I have a report whose columns will be hidden at runtime and the final width
of the table during runtime will be less than 10.5. However initially at
design time my table width is more than 10.5 and hence my report page width
is also more than 10.5. Thus when I export the page to pdf format in report
manager it adds a blank page for each and every normal page with data. is
there a way to modify the width of report page and in turn table width during
runtime, so that blank page can be avoided in pdf export?
I cannot use matrix or others due to various reasons. I can use only table
in my report. is there any other way to change report page width at runtime
without dealing with report rdl file (xml file) directly?
I am using Microsoft SQL Server 2005 Reporting Services.I should add, that I really mean I don't think you can change this on the fly
without writing a custom rendering extension.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"msdnuser" wrote:
> I have a report whose columns will be hidden at runtime and the final width
> of the table during runtime will be less than 10.5. However initially at
> design time my table width is more than 10.5 and hence my report page width
> is also more than 10.5. Thus when I export the page to pdf format in report
> manager it adds a blank page for each and every normal page with data. is
> there a way to modify the width of report page and in turn table width during
> runtime, so that blank page can be avoided in pdf export?
> I cannot use matrix or others due to various reasons. I can use only table
> in my report. is there any other way to change report page width at runtime
> without dealing with report rdl file (xml file) directly?
> I am using Microsoft SQL Server 2005 Reporting Services.
>|||Although I am not 100% on this - I don't think you can change this on the
fly...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"msdnuser" wrote:
> I have a report whose columns will be hidden at runtime and the final width
> of the table during runtime will be less than 10.5. However initially at
> design time my table width is more than 10.5 and hence my report page width
> is also more than 10.5. Thus when I export the page to pdf format in report
> manager it adds a blank page for each and every normal page with data. is
> there a way to modify the width of report page and in turn table width during
> runtime, so that blank page can be avoided in pdf export?
> I cannot use matrix or others due to various reasons. I can use only table
> in my report. is there any other way to change report page width at runtime
> without dealing with report rdl file (xml file) directly?
> I am using Microsoft SQL Server 2005 Reporting Services.
>|||Thank you very much for your response. However we dont have plans to write
our own rendering extension, so is it possible to parse rdl file before
rendering and change the column width, table width and page width? if that is
possible then is there any example that I can follow to proceed further?
"Wayne Snyder" wrote:
> I should add, that I really mean I don't think you can change this on the fly
> without writing a custom rendering extension.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "msdnuser" wrote:
> > I have a report whose columns will be hidden at runtime and the final width
> > of the table during runtime will be less than 10.5. However initially at
> > design time my table width is more than 10.5 and hence my report page width
> > is also more than 10.5. Thus when I export the page to pdf format in report
> > manager it adds a blank page for each and every normal page with data. is
> > there a way to modify the width of report page and in turn table width during
> > runtime, so that blank page can be avoided in pdf export?
> >
> > I cannot use matrix or others due to various reasons. I can use only table
> > in my report. is there any other way to change report page width at runtime
> > without dealing with report rdl file (xml file) directly?
> >
> > I am using Microsoft SQL Server 2005 Reporting Services.
> >|||Hello,
I agree with Wayne that this is not support in builtin rendering extension
and there is no method to change report during the runtime.
If you want to deal with rdl directly, you may want to deisgn a Web
applicaiton using reporting services API directly. You could create report
by using reportingservices.createreport method. You could change rdl by
using code and then create a report on the fly. The following link is for
your reference
http://www.codeproject.com/aspnet/SQLRSViewer.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Need a way to change report page width at runtime
>thread-index: AcYA/7QIipBaO+5iS6+A4tlScwLvPQ==>X-WBNR-Posting-Host: 148.134.37.3
>From: =?Utf-8?B?V2F5bmUgU255ZGVy?= <wayne.nospam.snyder@.mariner-usa.com>
>References: <F2169D52-5C30-4763-839B-6864316D1B14@.microsoft.com>
>Subject: RE: Need a way to change report page width at runtime
>Date: Wed, 14 Dec 2005 14:42:46 -0800
>Lines: 26
>Message-ID: <B995D838-F01C-4537-940C-94F85810C070@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65093
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Although I am not 100% on this - I don't think you can change this on the
>fly...
>--
>Wayne Snyder MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>I support the Professional Association for SQL Server ( PASS) and it''s
>community of SQL Professionals.
>
>"msdnuser" wrote:
>> I have a report whose columns will be hidden at runtime and the final
width
>> of the table during runtime will be less than 10.5. However initially at
>> design time my table width is more than 10.5 and hence my report page
width
>> is also more than 10.5. Thus when I export the page to pdf format in
report
>> manager it adds a blank page for each and every normal page with data.
is
>> there a way to modify the width of report page and in turn table width
during
>> runtime, so that blank page can be avoided in pdf export?
>> I cannot use matrix or others due to various reasons. I can use only
table
>> in my report. is there any other way to change report page width at
runtime
>> without dealing with report rdl file (xml file) directly?
>> I am using Microsoft SQL Server 2005 Reporting Services.
>

Need a Solution

Hi,

How can i define in SqlServer an way to make my identity's columns folow somekind of rule.

I need to define it in sqlserver not in my application code.
So basically what i need is to maintain the inserts i have , assuming that ids are automatic, but in the database being able to modyfing them.
Using trigers or other way, please help me... :)

thanks-.-I assume you don't want to use Sql Server's Identity="Yes" for an int field.

The alternative would be to use a Trigger that fires on Insert that will populate your identity column. The code inside the trigger can follow whatever rule you can code in Transact-Sql.

At least the last time I used Oracle you had to do it that way anyway.

CREATE TRIGGER [MyIdentifyTrigger] ON dbo.YourTable
FOR INSERT
AS
-- Figure out identify value and set column in "inserted" record to that value.

|||Hi,

No, my problem is that i have the Identity set to "Yes" in the fields. I've already seen that with SET IDENTITY_INSERT to ON i can explicit the value to the field with identity.

No, i just need to know the syntax of the trigger to do all of this, when i insert.

I need this because all my inserts in the application assumes auto ids, and now i need to control the value inserted in those field, but i cant go to the code, so must do this in database level.

thanks|||You should be able to accomplish this with an INSTEAD OF trigger. Something like this:

CREATE TRIGGER Insert_Data ON Test
INSTEAD OF INSERT
AS
DECLARE @.ID int, @.Col_SomeText varchar(50)
SELECT @.ID=ID, @.Col_SomeText = SomeText FROM inserted
IF @.ID IS NULL
 BEGIN
 BEGIN TRANSACTION
 SELECT @.ID = IDNumber FROM IDNumberTable HOLDLOCK
 UPDATE IDNumberTable SET IDNumber = IDNumber + 1
 COMMIT
 END
INSERT INTO Test VALUES(@.ID, @.Col_SomeText)

Terri

Need a list of Error Code

Hi,

When we configure a transformation to redirect the error record, SSIS gives 2 columns: 'error code' and 'error column'. In one article, I have seen how to get the error description using the 'error code'.

My questions are,

Where I can get list of all possible error codes? 'error column' gives the id of the column. How to get the name of the 'error column'?

Thanks.

Basically you need to know the metadata of the normal output, not that easy, but simon has wrapped this up in a Tx.

http://sqljunkies.com/WebLog/simons/archive/2005/12/15/SSIS_Enhanced_Error_Component.aspx

Some related info -

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

The SSIS error codes are listed in BOL. I also have them on my wiki, because BOL only got them in an update and I wanted them before that, and also you can add notes on the errors on the wiki.

nee stored procedure please help

Dear Experts,
I'm vinod, a junior DBA. working in HYD, india. i need one stored procedure.

Using one procedure i want to know the columns that are associated with the primary key and the name of the primary key.

The output should be as follows

Table Name PK_Constraint Name Columns

TABLE017 PK_TABLE009 COLUMN001

TABLE017 FK_TABLE009 COLUMN002

please send me the stored procedure, it will be a great help for me. thanks in advance.
vinod.mallolu@.exensys.com
vinodselect kcu.Table_Name, kcu.Constraint_Name, kcu.Column_Name from information_schema.table_constraints tc join information_schema.key_column_usage kcu
on tc.Constraint_Name = kcu.Constraint_Name and tc.Constraint_Type = 'PRIMARY KEY'

Mahesh|||i'M TRYING WITH THAT FRIEND. THANK YOU VERY MUCH FOR YOU REPLY

Nee help - How To get AVG in his situation?

Hi All,
Below is my initial query, that returns totals for each w within the
month specified. I hard coded the W Columns to make it easier to work
with. This works fine.
/*---*/
Select T1.DataSource AS [Service Line],
COUNT(T1.PoNumber) AS [Total Of PONumber],
Sum(Case When T1.ReqSubmitDate
Between '04/01/2005' AND '04/2/2005' Then 1 End) [04/2/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/03/2005' AND '04/9/2005' Then 1 End) [04/9/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/10/2005' AND '04/16/2005' Then 1 End) [04/16/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/17/2005' AND '04/23/2005' Then 1 End) [04/23/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/24/2005' AND '04/30/2005' Then 1 End) [04/30/2005]
FROM OPW AS T1,
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*)>1) AS T2
WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005'
AND '04/30/2005')
GROUP BY T1.DataSource
/*---*/
The current resulting data is:
[Service Line] [Total Of PONumber] [04/2/2005] [04/9/2005] [04/16/2005]
[04/23/2005] [04/30/2005]
EVPN 4 NULL NULL 4 NULL NULL
MNS 526 NULL 209 313 NULL 4
/*---*/
Now, one of my tasks assign to me is to find the average cycle time for each
w per [Service Line]. This will include the following fields to the
query:
REQCreateDate DateTime,
REQCreateTime VarChar(10),
ReqSubmitTime VarChar(10),
ReqSubmitDate DateTime (This one is already used within the above query)
So, I'm thinking I need to concatinate the following columns, then get the
total number per w and derive the average:
T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
I hope I am making sense and you can help m understand this.
Thanks for taking the time though.
John.John,
If you can provide some sample data and also show
the exact results you want, it will be easier to help. You
may know exactly what "average cycle time" means, but
I don't. Sample data will also help to show why and how
you are storing values like REQCreateTime as VarChar(10),
since the time columns appear to be important.
See http://www.aspfaq.com/etiquett_e.asp?id=5006
Steve Kass
Drew University
John Rugo wrote:

>Hi All,
>Below is my initial query, that returns totals for each w within the
>month specified. I hard coded the W Columns to make it easier to work
>with. This works fine.
>/*---*/
>Select T1.DataSource AS [Service Line],
> COUNT(T1.PoNumber) AS [Total Of PONumber],
> Sum(Case When T1.ReqSubmitDate
> Between '04/01/2005' AND '04/2/2005' Then 1 End) [04/2/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/03/2005' AND '04/9/2005' Then 1 End) [04/9/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/10/2005' AND '04/16/2005' Then 1 End) [04/16/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/17/2005' AND '04/23/2005' Then 1 End) [04/23/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/24/2005' AND '04/30/2005' Then 1 End) [04/30/2005]
>FROM OPW AS T1,
>(SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*)>1) AS T2
>WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005
'
>AND '04/30/2005')
>GROUP BY T1.DataSource
>/*---*/
>The current resulting data is:
>[Service Line] [Total Of PONumber] [04/2/2005] [04/9/2005] [04/16/2005]
>[04/23/2005] [04/30/2005]
>EVPN 4 NULL NULL 4 NULL NULL
>MNS 526 NULL 209 313 NULL 4
>/*---*/
>Now, one of my tasks assign to me is to find the average cycle time for eac
h
>w per [Service Line]. This will include the following fields to the
>query:
>REQCreateDate DateTime,
>REQCreateTime VarChar(10),
>ReqSubmitTime VarChar(10),
>ReqSubmitDate DateTime (This one is already used within the above query)
>So, I'm thinking I need to concatinate the following columns, then get the
>total number per w and derive the average:
>T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME) AS RC,
>T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME) AS RS
>I hope I am making sense and you can help m understand this.
>Thanks for taking the time though.
>John.
>
>|||The times are stored in a varchar format because they are derived solely
from an Excel Spreadsheet import that has the time in a spererate column,
and I was asked to mimic the data structure of the excel file. I don't like
it ether :(.
The Cycle Time is the Number of Minutes between ReqCreated Date/Time and
ReqSubmit Date/Time.
At the bottom of the my current message I have my newest version of the
query that at least shows the total Cycle Times per w. But I need to
show the averages, not the total.
Thanks very much for helping me.
/*Data*/
/*--*/
Select DataSource, PoNumber, REQCreateDate, REQCreateTime, ReqSubmitDate,
ReqSubmitTime
FROM OPW
DataSource | PoNumber | REQCreateDate | REQCreateTime | ReqSubmitDate |
ReqSubmitTime
EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82956 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82958 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
9:18:26
MNS PO82755 2005-03-30 00:00:00.000 4:42:27 2005-03-31 00:00:00.000 1:33:18
MNS PO82840 2005-04-13 00:00:00.000 3:31:16 2005-04-14 00:00:00.000 10:27:57
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82968 2005-04-15 00:00:00.000 2:35:37 2005-04-15 00:00:00.000 2:42:01
MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
/*Newest Query*/
/*--*/
Select T1.DataSource AS [Service Line],
COUNT(T1.PoNumber) AS [Total Of PONumber],
Sum(Case When T1.ReqSubmitDate
Between '04/01/2005' AND '04/2/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/2/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/03/2005' AND '04/9/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/9/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/10/2005' AND '04/16/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/16/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/17/2005' AND '04/23/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/23/2005],
Sum(Case When T1.ReqSubmitDate
Between '04/24/2005' AND '04/30/2005' Then
CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
(T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
Decimal(9,2))
End) [04/30/2005]
FROM OPW AS T1,
(SELECT PoNumber
FROM OPW
GROUP BY PoNumber
HAVING COUNT(*)>1) AS T2
WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005'
AND '04/30/2005')
GROUP BY T1.DataSource
/*--*/
"Steve Kass" <skass@.drew.edu> wrote in message
news:u8Ejbk$SFHA.3620@.TK2MSFTNGP09.phx.gbl...
> John,
> If you can provide some sample data and also show
> the exact results you want, it will be easier to help. You
> may know exactly what "average cycle time" means, but
> I don't. Sample data will also help to show why and how
> you are storing values like REQCreateTime as VarChar(10),
> since the time columns appear to be important.
> See http://www.aspfaq.com/etiquett_e.asp?id=5006
> Steve Kass
> Drew University
> John Rugo wrote:
>|||John,
Did you try using AVG() instead of SUM() ? To be safe from rounding
surprises, write any AVG() expression as AVG(1.0*(yourvalue)) if yourvalue
is an integer.
SK
John Rugo wrote:

>The times are stored in a varchar format because they are derived solely
>from an Excel Spreadsheet import that has the time in a spererate column,
>and I was asked to mimic the data structure of the excel file. I don't lik
e
>it ether :(.
>The Cycle Time is the Number of Minutes between ReqCreated Date/Time and
>ReqSubmit Date/Time.
>At the bottom of the my current message I have my newest version of the
>query that at least shows the total Cycle Times per w. But I need to
>show the averages, not the total.
>Thanks very much for helping me.
>/*Data*/
>/*--*/
>Select DataSource, PoNumber, REQCreateDate, REQCreateTime, ReqSubmitDate,
>ReqSubmitTime
>FROM OPW
>DataSource | PoNumber | REQCreateDate | REQCreateTime | ReqSubmitDate |
>ReqSubmitTime
>EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82956 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82958 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82957 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>EVPN PO82959 2005-04-14 00:00:00.000 11:39:58 2005-04-14 00:00:00.000
>9:18:26
>MNS PO82755 2005-03-30 00:00:00.000 4:42:27 2005-03-31 00:00:00.000 1:33:18
>MNS PO82840 2005-04-13 00:00:00.000 3:31:16 2005-04-14 00:00:00.000 10:27:5
7
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82968 2005-04-15 00:00:00.000 2:35:37 2005-04-15 00:00:00.000 2:42:01
>MNS PO81971 2005-03-29 00:00:00.000 4:36:08 2005-03-29 00:00:00.000 5:03:08
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>MNS PO82838 2005-04-14 00:00:00.000 3:37:07 2005-04-14 00:00:00.000 4:51:12
>/*Newest Query*/
>/*--*/
>Select T1.DataSource AS [Service Line],
> COUNT(T1.PoNumber) AS [Total Of PONumber],
> Sum(Case When T1.ReqSubmitDate
> Between '04/01/2005' AND '04/2/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/2/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/03/2005' AND '04/9/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/9/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/10/2005' AND '04/16/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/16/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/17/2005' AND '04/23/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/23/2005],
> Sum(Case When T1.ReqSubmitDate
> Between '04/24/2005' AND '04/30/2005' Then
> CAST(DateDiff(hh, (T1.REQCreateDate + CAST(T1.REQCreateTime AS DATETIME)),
> (T1.ReqSubmitDate + CAST(T1.ReqSubmitTime AS DATETIME))) As
>Decimal(9,2))
> End) [04/30/2005]
>FROM OPW AS T1,
>(SELECT PoNumber
> FROM OPW
> GROUP BY PoNumber
> HAVING COUNT(*)>1) AS T2
>WHERE (T1.PoNumber = T2.PoNumber) AND (T1.ReqSubmitDate BETWEEN '04/01/2005
'
>AND '04/30/2005')
> GROUP BY T1.DataSource
>/*--*/
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:u8Ejbk$SFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
>
>

Near exact matches, help needed

I'm currently working on a match function to compare two char based columns in differnet tables to create a join.

In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.

For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.

Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.

Thanks in advancePerhaps the SOUNDEX function could help you out: SOUNDEX - Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.