Advice badly needed

I have just been given a ridiculous task... There is an existing SQL database that has one table with two fields. The first is an ID column, the second has information that I somehow need to retrieve. This is my dilemma. Here's something like how the ROWS look in this table:
__________________________________________________ ______________
PRACTICE: Practice 1 COMPLETE HISTORY REPORT PAGE:
DATE: AUG 18, 1973 TIME: 9:04:36
NULL
NULL
12-53-48 DOE JOHN BALANCE: .00
123 Street DR
Somewhere ST 12345

DATE DR PROC.. MD DIAG.. DESCRIPTION........... TRN.AMOUNT TR.BALAN
NULL
JOHN DOE
08/12/94 13 73630 1 959.7 FOOT-COMPLETE 3+ VIEWS 23.35 .00
08/23/94 13 *INS. FILED WITH TRAVELERS
08/29/94 13 39 PAYMENT - WORKER'S COMP. -23.35 .00
TOTAL DUE: .00
__________________________________________________ ______________

Each line is a seperate row in the table!?! Also, the lines with the dates, near the bottom, can have different numbers of rows (Sometimes several, sometimes hundreds) I don't know how this data was imported. It appears that I have 122000 accounts but they are spread across 3.5 million rows... I need to devise some way to take this data out.. Can anyone please give some advice??I'm having trouble understanding your explaination. Can you post the assignment as the instructor gave it to you?

-PatP|||Basically I've been asked to create an Intranet ASP.Net reporting feature for this data. I have to be able to split up this information into some sort of normalized relational structure. I need to be able to grab the start of each record (IE the practice Name row) then each row within this collection of rows. All of this data is in one column! there is no structure to it, as of now. I want to be able to seperate each account into other tables to give some structure... does that make sense?|||So you just have this text blob sitting all by its lonesome in a row, as though somebody dumped a chunk of XML in there? No database enforced schema at all?

Bleach!

-PatP|||I wish it was in one row...

Row 1: PRACTICE: Practice 1 COMPLETE HISTORY REPORT PAGE:
Row 2: DATE: AUG 18, 1973 TIME: 9:04:36
Row 3: NULL

See what I mean?! This is one field, one table.|||If at first you don't succeed, apply brute force. Cursor through the monstrosity, and make a structured schema from it. If worst comes to worst, plop the parts into a work table with pure character columns, then slice and dice them as needed.

What you've got now is a fiasco in the making. YOu have to enforce some structure onto it, soon!

-PatP|||Yes, structure has to be made of this. There are no updates being made to it. It is an old archive that someone imported from an Oracle DB from our pre-merger and it's just sitting there, needing connectivity for reporting. I had hoped to be able to do some types of nested queries to extract what I needed, adding them to another set of tables as I went. Mostly what I know about cursors are that I hate them and can't ever seem to get them to work correctly. As for making the structured schema from it.. would you be able to possibly point me towards any documentation on accomplishing that? Your answer seemed to be a bit above my head:)|||can't you use "text to columns" in excel and use <space> as the delimeter

or write a macro to do repeated keystrokes to structure the data as someone suggested previously|||The exact process varies a little bit, depending on what tools you are using to process your data heaps (the unstructured tables).

Hopefully you know how to interpret the heap. In other words there are either a fixed number of rows for each logical group, or there is some kind of "marker" that will show you where one group ends and another group begins. I assume that the sequence of the rows is fixed (they stay in the same order) and significant (that the order matters). Once you understand the structure of your heap, then you can start to make better sense of it.

You basically want to write some kind of loop that will process each group of related pieces of text (rows within your heap), and put them into a table with more meaningful rows and columns (probably one column for each row in the heap, unless some of them are useless and/or missing).

Without understanding exactly what you've got, I can't think of a way to come up with anything like a step-by-step solution. You'll have to find someone that understands the data to help you structure it.

-PatP|||Ok, from trying to work with this via TSQL using cursors, I've found too many potential errors caused by inconsistency of the original data input. I've now created a VB.Net Windows application to handle the data collection/transformation which encapsulates the transformed accounts into a class before attempting to insert into the new normalized relational database via stored procedure.

I have one question (for now) about form refreshing. The form that allows the user to begin the transformation counts the actual accounts to be worked, placing that number into a label. As each record is completed, I cast back to the form, reducing a label for accounts remaining and increasing a label for accounts complete, then refresh the form to reflect the changes. This process occurs rather quickly but seems to make an odd visual effect to the forms groupbox and listbox controls. They seem to shake as the transformation thread is working. Now, this application is really only to be used once, but I would like to know if this visual effect can be prevented when developing future reusable projects.|||Yes, it can, but I don't have an easy reference available. Maybe someone else has code or a URL they can post. If not, remind me next week and I'll find one for you.

-PatP|||Thanks Pat!

Advantages/Disadvantages of TEXT Column type in SQL 2005

Hello,
I know this question has been asked before, but I'd like to get the
opinions from others before I continue with my database. I am creating
a Database to track calls for a Tech Support dept. There are some
tables like tb_Call, tb_Ticket where I need to allow a user to enter a
long description if necessary. I am thinking of using the Text Field
type with a leght of 16. My question is what are the
advantages/divantages with this as opposed to using any other data
type. Any insight or recommendations you can provide will be greatly
appreciated!
Thanks in advance!The TEXT datatype is deprecated in SQL Server 2005. So do not use it for
any new work. Instead, use VARCHAR(MAX) -- that datatype has the same
maximum storage capacity as TEXT, and is a lot more flexible in terms of
being able to use string functions.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ed_p" <edp@.nomail.com> wrote in message
news:utvCvDY6FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I know this question has been asked before, but I'd like to get the
> opinions from others before I continue with my database. I am creating a
> Database to track calls for a Tech Support dept. There are some tables
> like tb_Call, tb_Ticket where I need to allow a user to enter a long
> description if necessary. I am thinking of using the Text Field type with
> a leght of 16. My question is what are the advantages/divantages with
> this as opposed to using any other data type. Any insight or
> recommendations you can provide will be greatly appreciated!
> Thanks in advance!|||
Adam Machanic wrote:

>The TEXT datatype is deprecated in SQL Server 2005. So do not use it for
>any new work. Instead, use VARCHAR(MAX) -- that datatype has the same
>maximum storage capacity as TEXT, and is a lot more flexible in terms of
>being able to use string functions.
>
>
It's worth noting that while for [text] the default is
to store data out-of-row, for the (max) types, the
default is to store data in-row. In some cases, this
can make a difference, and it may be useful to set the
table option so that the (max) type behaves like [text]
did:
exec sp_tableoption N'MyTable', 'large value types out of row', 'ON'
Steve Kass
Drew University|||In addition to the other posts, if you have several such columns, where each
will fit inside the
regular varchar or nvarchar limit: In 2005, you have page overflow, meaning
you can have for
instance two varchar(5000) each containing 5000 characters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ed_p" <edp@.nomail.com> wrote in message news:utvCvDY6FHA.1248@.TK2MSFTNGP14.phx.gbl...[colo
r=darkred]
> Hello,
> I know this question has been asked before, but I'd like to get the opinio
ns from others before I
> continue with my database. I am creating a Database to track calls for a
Tech Support dept.
> There are some tables like tb_Call, tb_Ticket where I need to allow a user
to enter a long
> description if necessary. I am thinking of using the Text Field type with
a leght of 16. My
> question is what are the advantages/divantages with this as opposed to
using any other data
> type. Any insight or recommendations you can provide will be greatly appr
eciated!
> Thanks in advance![/color]

advantages of Identity Column

Hi,
What would be possible advantages of having Identity column as primary key
than having another unique column as primary key.?http://www.aspfaq.com/2504
"mavrick_101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4648FD42-BB0A-4B09-B1C1-820F08FFA46E@.microsoft.com...
> Hi,
> What would be possible advantages of having Identity column as primary key
> than having another unique column as primary key.?
>

Advanced Query Question

What I am trying to accomplish is to return a resultset that would look
like this:
Company.Name Table1.Column Table2.Column Table3.Column
Company A y y y
Company B n y n
Company C n n y
Company D n n n
I am using 4 tables to do this (Company, Table1, Table2, Table3). What
makes this somewhat complex is that I need to filter on a specific date
range (ie. 5/1/2006 - 5/31/2006) and display a company even if nothing
is available for the date range entered (Company D). Each Table has
its own date column. So I would need to apply my date criteria to
Table1.Date, Table2.Date, and Table3.Date.
In my first attempt I was using an Outer Join to display all company
names even if nothing was available in the three columns. That didn't
seem to work because then Company D wouldn't be included in the output.
The primary/foreign key relationship on the tables is the CompanyId.
The primary CompanyId being in the Company table and the foreign
CompanyId being in Tables 1,2, & 3.
How can I write this SQL statement in order to get the above results?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***"JP" <jp@.nospam.com> wrote in message
news:OJoiMuahGHA.4368@.TK2MSFTNGP03.phx.gbl...
> What I am trying to accomplish is to return a resultset that would look
> like this:
> Company.Name Table1.Column Table2.Column Table3.Column
> Company A y y y
> Company B n y n
> Company C n n y
> Company D n n n
<snip>
CREATE TABLE #Company (CompanyID INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL)
CREATE TABLE #Table1 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table1 ADD
CONSTRAINT FK_Table1_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table2 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table2 ADD
CONSTRAINT FK_Table2_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table3 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table3 ADD
CONSTRAINT FK_Table3_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
INSERT INTO #Company (CompanyID, [Name])
SELECT 1, 'Microsoft'
UNION SELECT 2, 'Hooters'
UNION SELECT 3, 'Ca$h Munny Records'
UNION SELECT 4, 'Widgets LLC.'
UNION SELECT 5, 'Five Inc'
INSERT INTO #Table1 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-01-01'
UNION SELECT 1, 2, '2006-05-01'
UNION SELECT 3, 1, '2005-01-01'
UNION SELECT 5, 1, '2006-05-15'
INSERT INTO #Table2 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-31'
UNION SELECT 1, 2, '2006-01-01'
UNION SELECT 2, 1, '2005-04-01'
UNION SELECT 4, 1, '2006-05-15'
INSERT INTO #Table3 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-02'
UNION SELECT 2, 1, '2006-05-04'
UNION SELECT 3, 1, '2006-05-09'
UNION SELECT 4, 1, '2006-05-15'
UNION SELECT 5, 1, '2006-05-14'
UNION SELECT 2, 2, '2001-01-01'
SELECT DISTINCT c.CompanyID, c.[Name],
CASE WHEN t1.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table1_Column],
CASE WHEN t2.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table2_Column],
CASE WHEN t3.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table3_Column]
FROM #Company c
LEFT OUTER JOIN #Table1 t1
ON c.CompanyID = t1.CompanyID
AND (t1.[Date] >= '2006-05-01' AND t1.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table2 t2
ON c.CompanyID = t2.CompanyID
AND (t2.[Date] >= '2006-05-01' AND t2.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table3 t3
ON c.CompanyID = t3.CompanyID
AND (t3.[Date] >= '2006-05-01' AND t3.[Date] < '2006-06-01')
DROP TABLE #Table3
DROP TABLE #Table2
DROP TABLE #Table1
DROP TABLE #Company|||Thank you very much Mike!
*** Sent via Developersdex http://www.examnotes.net ***

Advanced Query

I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName

so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'.

I would imagine I would need a Declare @.Status nvarchar(15) -- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.

Add this as a column:

CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status

And remove AND (ev.personalScore IS NULL) from the WHERE clause.

Like:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName

Of course, that assumes that every employee can only have 0 or 1 records in the employeeeval table. If you want to test if they have 0 or >0 records, then it would be like this:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ISNULL(EvalCount,0)=0 THEN 'Not Started' ELSE 'In Progress' END AS Status

FROM Employee e

LEFT OUTER JOIN (SELECT COUNT(*) EvalCount,EmployeeID FROM EmployeeEval GROUP BY EmployeeID) ev ON e.EmployeeID = ev.EmployeeID

LEFT OUTER JOIN [User] u ON u.Department = e.DeptID

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName

|||

Gah, I think I finally understand what it is that you want. Questions I have:

Can there be more than 1 row in the EmployeeEval table for an employee?

It seems like someone (The employee) is starting an eval request, and you enter a row into the EmployeeEval table (With approved=0). The supervisor gets notified and they mark approved=1 when they pick up the request for eval. When the eval is completed, you then remove the row from EmployeeEval? Is that correct?

Can there ever be more than 1 row per employee in the eval table? What happens if an unapproved eval is in the table and they request again? Do you deny the request and not make another row? What if it's in progress (approved=1)? Deny and don't enter another row as well?

|||

thanks, but I had to place the approved column in place of the personalscore so it looks like this:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

As you stated above they can only have 0 or 1 records that's why I went w/the first one. Thanks for the help.

|||Everytime I get one thing working people keep requesting things I just don't have the experience to complete. I now need the ability w/the forementioned query this: I need to count all employees in each dept and if they match the # in the employeeeval table place in a simple text that says Reviews for Dept. 100% complete.

I've tried working ways of using the Count statement, but I'm lost again.|||

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

I'm starting to get a wee bit confused on what exactly you want back from the query. I'm having serious doubts that the above query is actually going to return what you want it to, but.. To add your last request you can do it two ways.

Add these to your FROM clause:

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)

Then add this to your columns:

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete

if you want the % to be accurate, or the following if you want it to 100% or some other message if not 100%:

CASE WHEN c1.cnt=c2.cnt THEN 'Reviews for Dept. 100% complete' ELSE 'Not done yet!' END As DeptComplete

And you should end up with something like:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||

The query is being used to create a dataset, which in turn is used to populate a crystal report. The users, however keep wanting new requests. So I'm forced to do this through my Dataset.

I really don't need a percentage brought back, I just need to know when the #'s match in the employeeeval table and employee table, and say either incomplete or complete.

When I ran your query it tells me 3 errors--

Server: Msg 174, Level 15, State 1, Line 3
The floor function requires 1 arguments.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'c1'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'c2'.

|||

Try:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||

nope now I'm getting the following:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

In the above however, I tried removing e.deptid and still got the same error. Where is it pulling Employee.DeptID from?

|||

Doh! My bad.

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeGROUP BY DeptID) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)GROUP BY DeptID) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||Thanks for being so patient with me, I have a few me questions. Can you break down a couple of things for me. What does the Cast(Floor portion of the code do? As well what does (SELECT COUNT(*) cnt,DeptID FROM Employee do? <--for this what does the cnt, DeptID do ? I imagine cnt is like a variable for C1 or C2 but I'm a little lost on it.

Another thing I would like to be able to do--If the number of employees in a specific dept = the number of employees in the Employeeeval table I would like to return something like --Reviews are 100% complete for this dept. I don't need the employee name or anything like what I brought back in the SELECT statement.--For this I realize I'll probably need a new procedure. So I'll work on that and if I need help I'm sure I can find help on here.|||

The CAST(FLOOR(c1/c2*100) AS varchar(3)) is two steps, the first FLOOR(c1/c2*100) takes c1 divides it by c2, multiplies it by 100, and then floor says to remove any fraction, always rounding down. (99.9 becomes 99, etc). Then once we have an integer (0-100), we then say take that integer and make it a varchar(3) which is a variable length string up to 3 bytes/characters long.

As for (SELECT COUNT(*) cnt,DeptID FROM Employee ...
I take the count of records where the DeptID is the same and create a derived table from the result. Specifically, I am calling the count of records 'cnt'. DeptID is the second column taken from Employee.

Think of it this way... If you do:

SELECT COUNT(*),DeptID FROM Employee GROUP BY DeptID and your employee table looks like:

DeptId/{other columns}
1/
1
2
2
3
3
3

Then the result of the query would be:
COUNT(*)/DeptID
2/1
2/2
3/3

Instead of allowing the first column name be "COUNT(*)" since it has no other real name, COUNT(*) cnt says that I want the count, but call the column 'cnt'.

That make any sense?

|||

Yes--I just wish I could totally grasp T-SQL--So I'm trying to do the portion of SQL I asked in my last post--is there a way to incorporate all depts in this? so I still show only employees who have ev.PersonalScore is NULL but show the All Departments --Kinda like this:

Dept 1 - Supervisor 1 - EmpName - EmployeeID - Status- DeptStatus

But if the dept has all employees w/value in PersonalScore and the counts match -- put Dept Status = 'Dept has all reviews complete' --See I get lost as to where I put this to try and attempt to make it work.

|||Ok so I got what I wanted for just the Dept. but I would like to incorporate it into my other query so I don't have to run a subreport--b/c that is a pain.

select distinct d.[description],
Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
from department d LEFT OUTER JOIN [user] u ON d.deptid = u.department
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)
ORDER BY d.[description]

I just wouldn't know how to place this in my SELECT for the previous query b/c I need to use the c1 & c2 with d.DeptID instead of Employee|||

Sure... I can help you, but since I'm not totally sure on what you want out of it, let me explain this part.

Take the last query you have, and run that in management studio, or query analyzer, etc. See the results you get back? You can call it a resultset, or you can call it a table. Technically, I believe it should be called a resultset, which is like a table, but it's ordered. In any case, remove the ORDER BY from it, and you should see that the results don't really change, except that now it's in no particular order anymore.

If you now think of that as being a "table" you can now select from, aggregate, join etc, this is how you can make some very complex queries possible. I can see how you might want to use something like this in different queries. You have two options now. You can take that whole query and put parenthesis around it, and use it just about anywhere you can use a table if you give it an alias. For example...

SELECT myalias.description, myalias.status
FROM (your query here) myalias
WHERE ...

You can also use it in joins like...

SELECT myalias.description,myalias.status
FROM (your query here) myalias
JOIN (some other table) myalias2 ON (some condition)
WHERE ...

You can also make your query a view, which basically saves it so you can reuse it easily like...

CREATE VIEW DepartmentStatusView AS
(your query here)

Then you can use it almost anywhere:
SELECT *
FROM DepartmentStatusView
WHERE ...

However, to be truly useful, you will need to include the DeptID in your query, since that is what you would most likely be using in a join condition, so lets go ahead and build something like that:

CREATE VIEW vw_DepartmentStatus AS
select d.*,Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
FROM department d
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)

Now, anytime you want the department status, you can simply join in vw_DepartmentStatus using the DeptID for the ON clause, and you can reference it. Infact, you can simply think of vw_DepartmentStatus as the departments table with an extra "Status" column at the end if that helps.

If you want to add the "Status" on any query that you had before, so long as you were using Department, you can simply change your reference from Department to vw_DepartmentStatus and now you can reference the Status column like going from:

SELECT (some fields)
FROM departments d
JOIN (some stuff)
WHERE (some conditions)

to:

SELECT (some fields),d.status
FROM vw_DepartmentStatus d
JOIN (some stuff)
WHERE (some conditions)

Note the only change was to add d.status as a column you wanted to return and the change from departments to vw_DepartmentStatus

Advance SQL question

Hello everybody,

i have a advance question about a specific sql problem:

My table A have for example 3 columns.
in the third column are words seperated by ~.

ID COL2 COL3
-----
1 ab test~dummy~ddd
2 cd testdata2~sjhfdg~sdf
3 ef sd~test
4 gh sd~cv

Now i want two lists:

1.) used Values for column 3:

Values
--
test
dummy
ddd
testdata2
sjhfdg
sdf
sd
cv

2.) used values plus ID
Value ID
----
test 1
test 3
sd 3
sd 4
cv 4
dummy 1
...

Is it posible to produce such a list with nearly one SQL -Statement or with
temporaly tables ?

Thanks in advance

T.Kindermann
Database Administrator

--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>Thomas Kindermann wrote:
> Is it posible to produce such a list with nearly one SQL -Statement ?

Yes, it is possible:

SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

SELECT ID, substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

This queries work with up to 250 words in each row.

However, it may be better to use other ways. For more informations, see
this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Razvan|||Am 23 Jun 2005 02:39:18 -0700 schrieb Razvan Socol:

> Thomas Kindermann wrote:
>> [1 zitierte Zeile ausgeblendet]
> Yes, it is possible:
> SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> SELECT ID, substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> This queries work with up to 250 words in each row.
> However, it may be better to use other ways. For more informations, see
> this excellent article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
> Razvan

GENIAL SUPER,

you are my good ;-))))))))

Thanks

Thomas
--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>

Advance SQL

Dear Expert,

How can I reuse the column, instead of select the whole things again.
Example as below :

select
column1 as A,
column2 as B,
column3 * A as C
from dummy ;

in MS SQL I have to do like this,

select
column1 as A,
column2 as B,
column3 * column1 as C
from dummy ;

Thanks

DesmondOn 1 Jun 2004 03:56:08 -0700, Desmond wrote:

>Dear Expert,
>How can I reuse the column, instead of select the whole things again.
>Example as below :
>select
> column1 as A,
> column2 as B,
> column3 * A as C
>from dummy ;
>in MS SQL I have to do like this,
>select
> column1 as A,
> column2 as B,
> column3 * column1 as C
>from dummy ;
>
>Thanks
>Desmond

Hi Desmond,

You can use a derived table:

select A, B, column3 * A as C
from (select column1 as A,
column2 as B,
column3
from dummy) AS t

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> How can I reuse the column, instead of selecting the whole thing
again. <<

You can put it into a VIEW or derived table:

SELECT a, b, a*column3 AS c
FROM (SELECT column1, column2, column3
FROM Dummy) AS X(a, b, column3);

But that is not your problem. The real problem is that you do not
understand how SQL -- real SQL -- works.

Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things when they can.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors
are there. The table expression> AS <correlation name> option allows
you give a name to this working table which you then have to use for
the rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The "AS"
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause has been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the
SELECT clause list, and the sorting is done there. The ORDER BY
clause cannot have expression in it, or references to other columns
because the result set has been converted into a sequential file
structure and that is what is being sorted.

As you can see, things happen "all at once" in SQL, not from left to
right as they would in a sequential file/proceudral language model. In
those languages, these two statements produce different results:
**READ (a, b, c) FROM File_X;
**READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.

ADODB.Recordset Invalid Column Name VB6

I'm trying to limit the recordset retuned in the sub below by a
variable called vcnumber. I'm not sure what the problem is but I keep
getting the error "Invalid Column Name" when I set sqlstring to "select
* from vc_names where vc_case_number =" & vcnumber. If I don't try to
limit the recordset returned and just set sqlstring to "select * from
vc_names", it works. If there is another\better way to do this, I am
open to suggestions. Thanks!
========================================
===================
Public Sub populatename()
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String
Dim sqlstring As String
Set adoConnection = CreateObject("ADODB.Connection")
Set adoRecordset = CreateObject("ADODB.Recordset")
connectString = "Provider=SQLOLEDB.1;Password=password;Persist Security
Info=True;User ID=user;Initial Catalog=db;Data Source=server"
adoConnection.Open connectString
sqlstring = "select * from vc_names where vc_case_number =" & vcnumber
adoRecordset.Open sqlstring, adoConnection, adOpenDynamic,
adLockOptimistic
Do Until adoRecordset.EOF
List2.AddItem adoRecordset!first_name & " " & adoRecordset!middle_name
& " " & adoRecordset!last_name
adoRecordset.MoveNext
Loop
adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
========================================
========================if vs_case_number is a char field, remember to add quotes i.e.
sqlstring = "select * from vc_names where vc_case_number = '" &
vcnumber & "'"

ADO.NET returns different colum value when compared to View results in SQL 2005 Management

I have a complex view in my sql 2005 database.

The view returns a column that could be null (as the result of a left outer join).

The coulmn that is returned is an integer.

Everything works fine if I run the view from SQL 2005 Management Studio.

My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.

Has anybody seen this behaviour before?

Any help appreciated.

Regards,

Paul.

Hi Paul,

From you description, I understand that when you query data from the database view, the destination column in the DataSet is always null.

This could be caused by a wrongly configured SqlDataAdapter or the mismatched target DataSet schema.

For example, if you're using a typed DataSet, the target column data type is in another type. In this case, data might be converted incorrectly. Also, please try to check the table mapping and column mapping for the SqlDataAdapter.

HTH.

|||

I've solved the problem

I changed the DB Connection to system admin and everything worked ok.

Looks like I've got a permissions issue with one or more of my SQL Functions.

Thanks for the help.

Regards,

Paul.

ADO error: this column does not exist

Table : Department

Col :

Department ID
Name
Description

CREATE PROCEDURE GetDepatmentID AS

SELECT Department ID, Name
FROM Department

RETURN

Trying to create strored procedure and get the error that "Department" column does not exist.

Please know verily new to this so have patienceSince you have a space character in your column name you should enclose the column name in square brackets whenever you refer to it in your SQL statements:

SELECT [Department ID], Name
FROM Department

FWIW Inever use space characters in my column names. I usually use Pascal casing (DepartmentID), and have used camel casing (departmentID) or an underscore (Department_ID) in the past.
Terri