Help with SQL Mapping

First time trying to import a pretty simple XML file into a SQL
table. I've been reading annotated XSD file docs for the past 3 hours
and it's just not clear to me. Here's my XML
<?xml version="1.0" standalone="yes"?>
<InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>12345</documentnumber>
<invoicebalance>99.99</invoicebalance>
<invoicenumber>INV123</invoicenumber>
<invoicetrantype>FS</invoicetrantype>
</InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>444</documentnumber>
<invoicebalance>88.88</invoicebalance>
<invoicenumber>INV345</invoicenumber>
<invoicetrantype>AB</invoicetrantype>
</InvoiceBalanceData>
</InvoiceBalanceData>
Can someone help me with my annotated XSD file? tia.
On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> First time trying to import a pretty simple XML file into a SQL
> table. I've been reading annotated XSD file docs for the past 3 hours
> and it's just not clear to me. Here's my XML
> <?xml version="1.0" standalone="yes"?>
> <InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>12345</documentnumber>
> <invoicebalance>99.99</invoicebalance>
> <invoicenumber>INV123</invoicenumber>
> <invoicetrantype>FS</invoicetrantype>
> </InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>444</documentnumber>
> <invoicebalance>88.88</invoicebalance>
> <invoicenumber>INV345</invoicenumber>
> <invoicetrantype>AB</invoicetrantype>
> </InvoiceBalanceData>
> </InvoiceBalanceData>
> Can someone help me with my annotated XSD file? tia.
I should add that after I attempt to import the XML, the script
finishes successfully (this is from a DTS package), but no rows are
imported into the table.
|||I would process it as attribute centric
declare @.xml xml
SET @.xml =
'<root>
<InvoiceBalanceData documentnumber="12345" invoicebalance="99.99"
invoicenumber="INV123" invoicetrantype="FS" />
<InvoiceBalanceData documentnumber="444" invoicebalance="88.88"
invoicenumber="INV345" invoicetrantype="AB" />
</root>'
select @.xml
SELECT
[inv].[ref].value('@.documentnumber', 'int'),
[inv].[ref].value('@.invoicebalance', 'money')
FROM @.xml.nodes('/root/InvoiceBalanceData') [inv]([ref])
See SQL Server BOL as well.
-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @.docHandle int
DECLARE @.xmlDocument nvarchar(max) -- or xml type
SET @.xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@.docHandle, N'/ROOT/Customers')
WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@.docHandle, N'//Orders')
WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@.docHandle, N'/ROOT/Customers/Orders') WITH
(CustomerID nchar(5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @.docHandle
"Larry Bud" <larrybud2002@.yahoo.com> wrote in message
news:6ebab460-5eda-4c48-a4be-1ef478130ea3@.i29g2000prf.googlegroups.com...
> On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> I should add that after I attempt to import the XML, the script
> finishes successfully (this is from a DTS package), but no rows are
> imported into the table.

Help with SQL Mapping

First time trying to import a pretty simple XML file into a SQL
table. I've been reading annotated XSD file docs for the past 3 hours
and it's just not clear to me. Here's my XML
<?xml version="1.0" standalone="yes"?>
<InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>12345</documentnumber>
<invoicebalance>99.99</invoicebalance>
<invoicenumber>INV123</invoicenumber>
<invoicetrantype>FS</invoicetrantype>
</InvoiceBalanceData>
<InvoiceBalanceData>
<documentnumber>444</documentnumber>
<invoicebalance>88.88</invoicebalance>
<invoicenumber>INV345</invoicenumber>
<invoicetrantype>AB</invoicetrantype>
</InvoiceBalanceData>
</InvoiceBalanceData>
Can someone help me with my annotated XSD file? tia.On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> First time trying to import a pretty simple XML file into a SQL
> table. I've been reading annotated XSD file docs for the past 3 hours
> and it's just not clear to me. Here's my XML
> <?xml version="1.0" standalone="yes"?>
> <InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>12345</documentnumber>
> <invoicebalance>99.99</invoicebalance>
> <invoicenumber>INV123</invoicenumber>
> <invoicetrantype>FS</invoicetrantype>
> </InvoiceBalanceData>
> <InvoiceBalanceData>
> <documentnumber>444</documentnumber>
> <invoicebalance>88.88</invoicebalance>
> <invoicenumber>INV345</invoicenumber>
> <invoicetrantype>AB</invoicetrantype>
> </InvoiceBalanceData>
> </InvoiceBalanceData>
> Can someone help me with my annotated XSD file? tia.
I should add that after I attempt to import the XML, the script
finishes successfully (this is from a DTS package), but no rows are
imported into the table.|||I would process it as attribute centric
declare @.xml xml
SET @.xml =
'<root>
<InvoiceBalanceData documentnumber="12345" invoicebalance="99.99"
invoicenumber="INV123" invoicetrantype="FS" />
<InvoiceBalanceData documentnumber="444" invoicebalance="88.88"
invoicenumber="INV345" invoicetrantype="AB" />
</root>'
select @.xml
SELECT
[inv].[ref].value('@.documentnumber', 'int'),
[inv].[ref].value('@.invoicebalance', 'money')
FROM @.xml.nodes('/root/InvoiceBalanceData') [inv]([ref])
See SQL Server BOL as well.
-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @.docHandle int
DECLARE @.xmlDocument nvarchar(max) -- or xml type
SET @.xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@.docHandle, N'/ROOT/Customers')
WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@.docHandle, N'//Orders')
WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@.docHandle, N'/ROOT/Customers/Orders') WITH
(CustomerID nchar(5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @.docHandle
"Larry Bud" <larrybud2002@.yahoo.com> wrote in message
news:6ebab460-5eda-4c48-a4be-1ef478130ea3@.i29g2000prf.googlegroups.com...
> On Dec 13, 10:31 am, Larry Bud <larrybud2...@.yahoo.com> wrote:
> I should add that after I attempt to import the XML, the script
> finishes successfully (this is from a DTS package), but no rows are
> imported into the table.

HELP with SQL Date Function

Is there a Function that would just give me the DATE and not the DATE & TIME? I am trying to group by VRUServiceDate but since this is a smalldatetime data type, the grouping does not work by date because the time shows.

Thank you.

SELECT
VRUServiceDate AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY VRUServiceDate,EmployeeID
ORDER BY EmployeeID,VRUServiceDatei would use the CONVERT function --

SELECT
CONVERT(CHAR(10),VRUServiceDate,126) AS [Service Date],
EmployeeID AS [Rep ID],
SUM(VRUDriveMiles) AS [Miles Traveled]
FROM WorkOrder
WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
GROUP BY CONVERT(CHAR(10),VRUServiceDate,126),EmployeeID
ORDER BY EmployeeID,CONVERT(CHAR(10),VRUServiceDate,126)

if the expression does not work in the ORDER BY, use the ordinal form instead -- ORDER BY 2,1

rudy
http://r937.com/|||It worked great but I had to change 126 to 101. Thanks!!

Help with SQL 6.5

I know this is an old version but the customer cannot upgrade at this time due to a Mac software issue. I am a newbie so forgive me if I ramble. Here is my question.

Windows Server 2003
SQL running version 6.5
Log size =1998 MB
Log Avail=600 MB
No maint. plan set up on this, and when I try to create one it warns me about running a maint. plan on a DB that is larger than 400 MB. When I try and trucate logs in EM seems like it runs but the size stays the same. The customer restarts the SQL service and users are then able to log in.

THe database used to run on a NT 4.0 box up till 6 months ago when it was moved to the 2003 box. It ran fine up till last week. The customer tells me that people have been getting errors logging in. In the event viewer the following error reports.

Event Type: Error
Event Source: MSSQLServer
Event Category: (2)
Event ID: 17060
Date: 8/4/2006
Time: 7:51:51 AM
User: N/A
Computer: AUX-SERVER
Description:
The description for Event ID ( 17060 ) in Source ( MSSQLServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error : 701, Severity: 17, State: 2, There is insufficient system memory to run this query..
Data:
0000: bd 02 00 00 11 00 00 00 ......
0008: 00 00 00 00 07 00 00 00 ......
0010: 6d 61 73 74 65 72 00 master.
Any ideas? Thanks!!What are the errors that the users are getting?

And the all important question, what changed, and who changed it?|||There is insufficient system memory to run this query..I am not sure what changed...|||Is this happening every day, or does it take a few days to "build up"? Also, when it does happen, does everyone get the error message, or do a few people manage to get in, while others are locked out?|||Ok, there are a few different ways to solve this problem.

The underlying problem has to do with how SQL 6.5 allocates memory. There are issues with the way SQL 6.5 works in Windows 2000 and later releases.

The easy solution is to buy a copy of Microsoft Virtual Server, install that onto the box you're using to run SQL 6.5, then create a virtual machine and install Windows NT 4.0 in that virtual machine. At this point, you've got the problem contained and can manage it easily and effectively.

A much more difficult solution (but requiring no additional software or licenses) is to simply work to configure the SQL 6.5 instance so that it uses a fixed amount of memory, then adjust the XP settings in the registry so that they don't strangle themselves when they hit those limits. This isn't usually hard, but it is rather complex and it requires someone that really knows SQL 6.5 and its memory usage... It is not a job for someone without a lot of experience.

There are a number of other possible solutions, but they all have associated risks. You'll have to decide which one suits your needs best if you decide to head down any of these paths.

-PatP|||To answer Mcrowley...it happens every cpl days...all are not able to log in...|||Oh yeah, one relatively simple way to solve this problem if you can afford daily reboots is to reboot the machine every day. This works around the memory allocation problem by not allowing the machine to reach the threshold where it can't effectively allocate memory anymore.

If you can afford the daily reboots, then the simple answer is to just schedule a script to restart (http://www.microsoft.com/technet/scriptcenter/scripts/desktop/state/dmstvb07.mspx) the server.

-PatP|||the machine has been running for a few months, configured the same way, with no problems...why now did it start acting up? Took that long to build up? Total server memory is 1 gig. SQL Server is set up with 32768 (2K blocks) of memory. Like I said I am now well versed in SQL then alone version 6.5!|||The underlying problem depends on the number of occurances of certain behaviors. In other words the problem occurs after the ill-behaved code executes a certain number of times... That number depends on the hardware configuration, device drivers, services, etc.

You've probably just reached the point where the threshold is now low enough to become a "pain point" while it hadn't been one before. This could be because of hardware changes, patches, or even network changes that forced loading additional software/drivers that were configured but not used in the past.

-PatP|||Thanks Pat...would setting up a maintenance plan help for this database? When I try to set one up it warns against setting one up on database's larger than 400mb.|||Setting up a maintenance plan might or might not help with database performance, but it won't do diddly for helping with memory problems. If my analysis of what's causing the machine (SQL Server anyway) to become non-responsive is correct, then a maintenance plan won't make any difference.

SQL 6.5 is a much simpler creature than its successors. The maintenance plans were not too effective, and it was EASY to code a script that did a much better job, especially for databases over about 300 Mb or so. You could ensure basic database health with just two commands DBCC CHECKDB, and DBCC CHECKALLOC, but you still needed to keep an eye on the database on a regular basis to "keep the wheels on the bus"

-PatP

Help with smalldateTime

Hello everyone,
I have in a table a field smalldatetime. The problem is that when I
select from table I need only the date without the time.
Is it possible not to show the time?
For example 06/02/2006 00:00:00 to be 06/02/2006.
Thanks a lotselect CONVERT(varchar(10), GETDATE(), 103)
HTH. Ryan
"Pumkin" <PopClaudia@.gmail.com> wrote in message
news:1139570322.012554.43930@.g47g2000cwa.googlegroups.com...
> Hello everyone,
> I have in a table a field smalldatetime. The problem is that when I
> select from table I need only the date without the time.
> Is it possible not to show the time?
> For example 06/02/2006 00:00:00 to be 06/02/2006.
> Thanks a lot
>|||Thanks a lot. It worked wonderfully

help with select

I have 300 records in one table. I want to select that in 3 times, each time
100 rows.
Anyone have some idea how to do that'
So, I would have 3 queries and each query would get 100 records.
If anyone can help...
Thanks!!Here's an example.
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
Select top 100 * from yourtable order by yourprimarykey
If you want the resultsets to be guaranteed to be identical, then I'd use a
temporary table first and do the 3 selects from that.
Select top 100 * into #yourtemptable from yourtable order by yourprimarykey
select * from #yourtemptable
select * from #yourtemptable
select * from #yourtemptable
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||(or use REPEATABLEREAD if you need consistency).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0A32_01C6CC1D.EA4583E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perhaps you want to retrieve the 300 rows, by getting 100 rows at a =time.
If so,
-- First 100
SELECT TOP 100 {ColumnList}
FROM MyTable
ORDER BY {SortValue}
-- Second 100
SELECT TOP 100 {ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN ( SELECT TOP 100 PKeyValue {ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100 {ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN ( SELECT TOP 200 PKeyValue {ColumnList}
FROM MyTable
ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"BJ" <bernard@.hi.hinet.hr> wrote in message =news:ed460v$cuh$1@.magcargo.vodatel.hr...
>I have 300 records in one table. I want to select that in 3 times, each =time
> 100 rows.
> Anyone have some idea how to do that'
> So, I would have 3 queries and each query would get 100 records.
> > If anyone can help...
> > Thanks!! > >
--=_NextPart_000_0A32_01C6CC1D.EA4583E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Perhaps you want to retrieve the 300 =rows, by getting 100 rows at a time.
If so,
-- First 100
SELECT TOP 100
{ColumnList}
FROM MyTable
ORDER BY ={SortValue}
-- Second 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN = ( SELECT TOP =100 PKeyValue
{ColumnList}
=FROM MyTable
=ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Third 100
SELECT TOP 100
{ColumnList}
FROM MyTable
WHERE PKeyValue NOT IN = ( SELECT TOP =200 PKeyValue
{ColumnList}
=FROM MyTable
=ORDER BY {SortValue}
)
ORDER BY {SortValue}
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"BJ" =wrote in message news:ed460v$cuh$1@.magcargo.vodatel.hr...>I have 300 =records in one table. I want to select that in 3 times, each time> 100 =rows.> Anyone have some idea how to do that'> So, I would have 3 =queries and each query would get 100 records.> > If anyone can help...> > Thanks!! > =>

---

--=_NextPart_000_0A32_01C6CC1D.EA4583E0--

Help with search code

I have been using the following code on a search page for some time, is has worked very well. We recently changed our database to support multiple addresses for each client. So I added the INNER JOIN on the tblClientAddresses. But now when I try to search on a ID I get an ambiguous cloumn name error on the ID. Can anyone see how I could correct this?

Thanks for any suggestions,

Sub BindDataForPaging(ByVal sortExpr As String)
 Dim MyConnection As SqlConnection
 Dim MySQLAdapter As SqlDataAdapter
 Dim DS As DataSet
 Dim ConnectStr As String
 Dim WhereClause As String
 Dim SelectStatement As String
 If SearchLastName.Text = "" And SearchFirstName.Text = "" And _
 SearchID.Text = "" And SearchCompanyName.Text = "" And _
 SearchSal1.Text = "" And SearchComment.Text = "" And SearchAddress.Text = "" Then
 Message.Text = "You didn't enter any search parameters. Try Again."
 Exit Sub
 End If
 WhereClause = "Where "
 If SearchLastName.Text <> "" Then
 WhereClause = WhereClause & "[LastName] Like '" & _
 SearchLastName.Text & "%" & "' AND "
 End If
 If SearchFirstName.Text <> "" Then
 WhereClause = WhereClause & "[FirstName] Like '" & _
 SearchFirstName.Text & "%" & "' AND "
 End If
 If SearchID.Text <> "" Then
 WhereClause = WhereClause & "[ID] = " & _
 SearchID.Text & " AND "
 End If
 If SearchCompanyName.Text <> "" Then
 WhereClause = WhereClause & "[CompanyName] Like '" & _
 SearchCompanyName.Text & "%" & "' AND "
 End If
 If SearchSal1.Text <> "" Then
 WhereClause = WhereClause & "[Sal1] Like '" & _
 SearchSal1.Text & "%" & "' AND "
 End If
 If SearchComment.Text <> "" Then
 WhereClause = WhereClause & "[Comments] Like '" & "%" & _
 SearchComment.Text & "%" & "' AND "
 End If
 If SearchAddress.Text <> "" Then
 WhereClause = WhereClause & "[Address] Like '" & "%" & _
 SearchAddress.Text & "%" & "' AND "
 End If
 If ClientTypeDrop.SelectedItem.Text <> "" Then
 WhereClause = WhereClause & "[CLientType] Like '" & "%" & _
 ClientTypeDrop.SelectedItem.Text & "%" & "' AND "
 End If
 If Right(WhereClause, 4) = "AND " Then
 WhereClause = Left(WhereClause, Len(WhereClause) - 4)
 End If
 SelectStatement = "Select *,A.Address FROM tblClients INNER JOIN dbo.tblClientAddresses A ON dbo.tblClients.ID = A.ID " & WhereClause & " ORDER BY " & sortExpr
 Try
 ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
 MyConnection = New SqlConnection(ConnectStr)
 MySQLAdapter = New SqlDataAdapter(SelectStatement, MyConnection)
 DS = New DataSet
 MySQLAdapter.Fill(DS)
 MyDataGrid.DataSource = DS
 MyDataGrid.DataBind()
 Catch objException As SqlException
 Dim objError As SqlError
 For Each objError In objException.Errors
 Response.Write(objError.Message)
 Next
 End Try
 End Sub

WhereClause = WhereClause & "[ID] = " & _

Which tables ID do you mean?|||I tried both as below and got a runtime error, but it is the ID from tblClients that I would like.

WhereClause = WhereClause & "tblClients.[ID] = " & _

Thank you,|||That's exactly what you need to do. You're still getting an error - and its still the same error? Can you just print out the resulting query rather than all the string concats?|||Try this instead:

WhereClause = WhereClause & "dbo.tblClients.[ID] = " & _

If this doesn't work, we need to see what SelectStatement contains exactly. Plus an exact error message would be helpful.

Terrisql

help with Query please

Hi,
I have a table with (sports) results, containing an userID, EventId and
a Time for each result recorded.
How do I select a list ordered from fastest to slowest containing the
fastest time for each userID recorded ? The challenge here, that I don't
understand how to do is to not get a list of all results for an event,
but only a single entry for each UserID with this userIDs fastest time.
Is there any way to do this except looping through each user ID from my
front end code and selecting the fastest time and build a dataset from
this that I order from a dataview ? Was hoping this could be done in SQL
rather than my VB .net code.
Any help appreciated.
Niclas
*** Sent via Developersdex http://www.examnotes.net ***It is hard to suggest something without seeing the code
SELECT * FROM Users WHERE datetime_column=
(SELECT MAX(datetime_column) FROM Users U WHERE U.userid=Users.userid)
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If I understand your requirements correctly,
you can do this. Note that this will give you
all UserIDs that share the same fastest time
for an event.
SELECT s.UserID,
s.EventID,
s.RecordedTime
FROM SportsResults s
WHERE s.RecordedTime IN (SELECT MIN(s2.RecordedTime)
FROM SportsResults s2
WHERE s.EventID=s2.EventID)|||On Wed, 10 May 2006 01:23:02 -0700, Niclas wrote:

>Hi,
>I have a table with (sports) results, containing an userID, EventId and
>a Time for each result recorded.
>How do I select a list ordered from fastest to slowest containing the
>fastest time for each userID recorded ? The challenge here, that I don't
>understand how to do is to not get a list of all results for an event,
>but only a single entry for each UserID with this userIDs fastest time.
Hi Niclas,
SELECT userID, MIN([Time]) AS FastestTime
FROM YourTable
GROUP BY userID
ORDER BY FastestTime ASC
(Based on lots of assumptions - see www.aspfaq.com/5006 if I answered
the wrong question).
Hugo Kornelis, SQL Server MVP|||This is a fairly straight forward group by. See if this approach works for
you (If I understand your requirements correctly). Regardless of whether or
not this works, check out these links for a quick SQL overview. I think you
will find them helpful.
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
Select userID
, EventId
min(Time) as BestTime
from MyTable
group by userID
, EventId
Order by BestTime
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Just what I needed, works OK.
Thanks
Niclas
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uJZvYhDdGHA.4312@.TK2MSFTNGP05.phx.gbl...
> This is a fairly straight forward group by. See if this approach works
> for
> you (If I understand your requirements correctly). Regardless of whether
> or
> not this works, check out these links for a quick SQL overview. I think
> you
> will find them helpful.
> http://www.w3schools.com/sql/sql_intro.asp
> http://sqlzoo.net/
> Select userID
> , EventId
> min(Time) as BestTime
> from MyTable
> group by userID
> , EventId
> Order by BestTime
>
> "Niclas" <NOSpam@.Notmail.com> wrote in message
> news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
>

Help with query

Hi,
I have a stored procedure (posted below) that returns a club ranking list
with fatsest to slowest time for a swim club based on
Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based on
the @.Rowcount variable passed in.
I would like to expand this query to return a the (single) fastest time per
strokeID held in the BBMD_Strokes table. I understand that I could use a
CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the same
query , but have read that CURSORS should be avoided due to poor
performance. Is there a prefered option to solve this ?
Niclas
CREATE procedure dbo.BBMD_GetEventRecord
@.StrokeID int,
@.DistanceID int,
@.CourseID int,
@.GenderID int,
@.AgeID int,
@.RowCount int
AS
Set ROWCOUNT @.Rowcount
SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', ' +
U.firstname as Swimmer,
R.Result,G.GalaName, G.StartDate,X.DOB
FROM BBMD_Results R
JOIN BBMD_Events E ON R.Eventid=E.EventID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
JOIN Users U ON R.UserID=U.UserID
JOIN BBMD_ExtUser X ON R.USERID=X.UserID
JOIN (SELECT R.UserID,MIN(R.Result) as RES
FROM BBMD_Results R
JOIN BBMD_Events E ON R.EventID=E.EventID
JOIN BBMD_ExtUser X ON R.UserID=X.UserID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
WHERE
E.StrokeID=@.StrokeID AND
E.Distanceid=@.DistanceID AND
E.Genderid=@.GenderID AND
E.Courseid=@.CourseID AND
R.Resulttypeid=1 AND
DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
< 100 * MONTH(X.DOB) + DAY(X.DOB)
THEN 1 ELSE 0 END
BETWEEN (SELECT YearMin From BBMD_YearGroups
WHERE YearGroupID= @.Age)
AND
(SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
Group By R.UserID) AS MinR ON minR.Res=R.result
AND minR.UserID=R.UserID
GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
S.StrokeName,D.DistanceName, G.StartDate,X.DOB
ORDER BY RESULT
GOPlease send the table DDL and sample data as INSERT statements, and what the
expected output looks like. Without that information, we are guessing and
the quality of help is sub-optimal..
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:%23roRJf5lGHA.1488@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a stored procedure (posted below) that returns a club ranking list
> with fatsest to slowest time for a swim club based on
> Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based
> on the @.Rowcount variable passed in.
> I would like to expand this query to return a the (single) fastest time
> per strokeID held in the BBMD_Strokes table. I understand that I could use
> a CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the
> same query , but have read that CURSORS should be avoided due to poor
> performance. Is there a prefered option to solve this ?
> Niclas
> CREATE procedure dbo.BBMD_GetEventRecord
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
> AS
> Set ROWCOUNT @.Rowcount
> SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', '
> + U.firstname as Swimmer,
> R.Result,G.GalaName, G.StartDate,X.DOB
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.Eventid=E.EventID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
> JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
> JOIN Users U ON R.UserID=U.UserID
> JOIN BBMD_ExtUser X ON R.USERID=X.UserID
> JOIN (SELECT R.UserID,MIN(R.Result) as RES
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.EventID=E.EventID
> JOIN BBMD_ExtUser X ON R.UserID=X.UserID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> WHERE
> E.StrokeID=@.StrokeID AND
> E.Distanceid=@.DistanceID AND
> E.Genderid=@.GenderID AND
> E.Courseid=@.CourseID AND
> R.Resulttypeid=1 AND
> DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
> WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
> < 100 * MONTH(X.DOB) + DAY(X.DOB)
> THEN 1 ELSE 0 END
> BETWEEN (SELECT YearMin From BBMD_YearGroups
> WHERE YearGroupID= @.Age)
> AND
> (SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
> Group By R.UserID) AS MinR ON minR.Res=R.result
> AND minR.UserID=R.UserID
> GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
> S.StrokeName,D.DistanceName, G.StartDate,X.DOB
> ORDER BY RESULT
> GO
>|||
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
Why is everything in your world an identifier? Explain what an
"age_id" is? Likewise, what is a gender_id? Gee, everyone else uses
an ISO gender_code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Help with Query

Hello and Thanks in advance,

I am trying to get the percentage that each row contributes to the total rows for a given time frame on a given line (Select ScrapCat, ScrapLbs/Sum(ScrapLbs ... Group by Category, LineNum) as percentage). We have 11 categories for each line for each day. The percentage for each category would be the sum of all rows for that category for that line that time frame divided the sum of all rows for all categories for that line and time frame.

A return would look like this
Category......ProLine.......ScrapLbs.....Sum( ScrapLbs)...Percentage...
CateA----1-----.25----2.0-----.125--
CateB----1-----.35----2.0-----.175--
CateC----1-----.5----2.0-----.25--
etc
CateA----2-----.25----1.0-----.25--
CateB----2-----.50----1.0-----.5--
etc
Table looks like this

ProDate ..............smalldatetime
ProLine ...............int
Category.............char
ScrapLbs ............float
ProShift ..............int

is this possible?
Thanks,
LeeCan you post the DDL and some sample base data

Like CREATE TABLE myTable99 (Col1 int, ..ect

And sample data that would put the data in to the table, like

INSERT INTO myTable99 (Col1, col2, ect)
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada

That way we can execute the code, set up a test bed and figure it out...

but this kinda throws me right away..

ScrapLbs.....Sum(ScrapLbs)...

How can you have the sum of something, and 1 occurance of something on the same row?|||Sorry my boss shifted my focus!

Hopefully this is what you need.

What I need to do is sum all the scrap for each line for the date range (Sum( lbs) as LineTotal group by line then sum(Category) as EachCategory group by line and Category then divide EachCategory by LineTotal

EachCategory/LineTotal = EachCategory is what percent of Total Line Scrap

Create Table tblScrap
{
thaDate smalldatetime
Category varchar 15
lbs float
LineNum int
Shift int
}
Insert tblScrap Values ( ' 10/29/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2004',DA , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/29/2005',DT , 0.5, 1 ,1)
Insert tblScrap Values ( ' 10/29/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2007',Longs , 3.4, 1 ,1)
Insert tblScrap Values ( ' 10/29/2008',Bent , 1.48, 1 ,1)
Insert tblScrap Values ( ' 10/29/2009',NTA , 4.44, 1 ,1)
Insert tblScrap Values ( ' 10/29/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2011',Caps , 2.36, 1 ,1)
Insert tblScrap Values ( ' 10/29/2012',Paper , 5.26, 1 ,1)
Insert tblScrap Values ( ' 10/29/2013',NAPS , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/28/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2004',DA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2005',DT , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2007',Longs , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2008',Bent , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2009',NTA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2011',Caps , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2012',Paper , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2013',NAPS , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2004',DA , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2005',DT , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2007',Longs , 7.16, 1 ,1)
Insert tblScrap Values ( ' 10/27/2008',Bent , 1.84, 1 ,1)
Insert tblScrap Values ( ' 10/27/2009',NTA , 2.24, 1 ,1)
Insert tblScrap Values ( ' 10/27/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2011',Caps , 3.92, 1 ,1)
Insert tblScrap Values ( ' 10/27/2012',Paper , 7.86, 1 ,1)
Insert tblScrap Values ( ' 10/27/2013',NAPS , 1.76, 1 ,1)|||I'm not sure I fully understood what you are looking for.
This query groups by date and category with the percentage for each category in relation to the total of the day.

SELECT thaDate,
Category,
SUM(lbs) ScrapLbs,
(SELECT NULLIF(SUM(lbs), 0) FROM tblScrap WHERE thaDate = TS.thaDate) SumScrapLbs,
SUM(lbs) / (SELECT NULLIF(SUM(lbs), 0) WHERE thaDate = TS.thaDate) Percentage
FROM tblScrap TS
GROUP BY thaDate, Category

Hope this helps.

Cheers,
Robert|||clinel,

Still not sure what you mean. sum(Category)? Category is a character field. Also, what date ranges?
This should get you started:

select LineCatTotals.LineNum, LineCatTotals.Category, LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select LineNum, sum(lbs) Linelbs from tblScrap group by LineNum) LineTotals
inner join (select LineNum, Category, sum(lbs) LineCatlbs from tblScrap group by LineNum, Category) LineCatTotals
on LineTotals.LineNum = LineCatTotals.LineNum

Add groupings by date or daterange if you want them.

blindman|||I'm sorry,
Category is the label for each category of scrap. I want to sum the lbs of scrap or each category would be a better term. Then I want to sum all lbs of scrap by line to get a line total and then divide the category total (for that line) by the line total to get the percent that each category contributes to the line total. Whew!

As far as date range goes, I will give the user the ability to give a beginning and ending date and I want to find the percentage for that date range.

My bad on the sum of category; I see now how I took a confusing thing and made it even more so.

Thanks for both the patience and help,
Lee|||Thanks to all,
It appears that what Blindman had sent me is what I needed. I was actually able to figure out where to set the critera for the date range. Now I just need to figure out what is going on because I have several reports that I think that this type query will fit the need.

Thanks again,
Lee|||If you'd like, post your final query and we can make sure you implemented the date-range criteria in the most efficient manner.

blindman|||As I am new to this and have had no formal and very little time to read very much, this is how I handled what you gave me.

I created a stored procedure (so I could set the critera for date range easily) and a I am allowing the user to set the begin and end and call it from an asp.

Here is how I handled the date range.

@.begdate smalldatetime, @.enddate smalldatetime
AS

select LineCatTotals.ProLine,
LineCatTotals.ScrapCat,
LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select ProLine, sum(Scraplbs) Linelbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine) LineTotals
inner join (select ProLine, ScrapCat, sum(Scraplbs) LineCatlbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine, ScrapCat) LineCatTotals
on LineTotals.ProLine = LineCatTotals.ProLine Order by LineCatTotals.ProLine
GO

Now that you are looking over this, is it possible to select a total from another table and divide the Line total (scraplbs) by the production total from another table (Select Sum(Production) From tblProduction Where EntryDate Between @.begdate AND @.enddate) LineCatTotals.LineCatlbs/Sum(Production) ? Both tables could be linked on ProLine.|||Looks good to me.

Yes, you can add more subqueries to do additional calculations. It is generally more efficient to run your process as a single query, but if the query gets too confusing then consider breaking it up into separate statements that load temporary tables or table variables with summarized data. Then finish with a query that links these temporary tables to get the answer you need.

blindman

Help With Query

Hi All,

I am trying to build a report where show all the numbers of orders completed and orders which are completed on time. However, I have a hard time to figure out how to build the where clause since I want to get all the completed orders and at the same time, I also want to count the completed orders on time. Can anyone help me with this query?

This is my query right now:

SELECT COUNT(o.OrderID)
FROM Order o
WHERE MONTH(o.InvoiceDT) = @.Month

TIA

hi,

first of all is there any field like order_to_be_finished_by (date) & finished_on(data) fields or not in your table.

Select Count(OrderID) As OrdersCompletedOnTime from table

where convert(varchar, to_be_Finished_Date, 103) = convert(varchar, finished_on, 103)

& second query

Select Count(OrderID) As OrdersCompletedOnTime from table

where finished_on is not null

hope it helps

regards,

satish.

|||

Thank you satish,

So, it is would be two different select statement in one single stored procedures.

|||

yep,

regards,

satish

Help with new server registration

I am trying for the first time to learn what to do and how to use SQLServer. I am following instructions in Books on Line to make a New Server Registration. The instructions read as follows:

Connecting to Servers

The toolbar of the Registered Servers component has buttons for the Database Engine, Analysis Services, Reporting Services, SQL Server Mobile, and Integration Services. You can register any of these server types for convenient management. Try this exercise to register the AdventureWorks database.

To register the AdventureWorks database

On the Registered Servers toolbar, click Database Engine if necessary. (It may already be selected.)

Right-click Database Engine, point to New, and then click Server Registration. The New Server Registration dialog box opens.

In the Server name text box, type the name of your SQL Server instance.

In the Registered server name box, type AdventureWorks.

On the Connection Properties tab, in the Connect to database list, select AdventureWorks, and then click Save.

I did steps 1 and 2 no problem. At step 3, for server name I typed MARKSDESKTOP\SQLEXPRESS

At step 4 I typed: Adventureworks

At step 5 I went to Connection Properties and at the "Connect to database drop down box there were 2 choices: <default> or <browse server> (not Adventureworks). If I click on browse server, The browse server for Database window pops up but Adventureworks is not listed there either.

I did a search on my C drive and there are lots of Adventureworks files present so I must have downloaded the database OK.

Does anyone know where I go from here to connect to the Adventureworks database so I can continue with this tutorial?

Please help. Thanks

Mark

Hi,

did you attach the database on the registered server first. if you instaleld the databse via msi, the database is not automatically attached.

1. Register the server first (without any set database, it uses the default then)
2. Right click in the server explorer on "Connect" --> Object Explorer
3. Naviagte on the object explorer to Databases, right click and select Attach..
4. Click add and select the Adventureworks MDF file, click ok and you are done, you should see the adventureworks db now in user databases.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||do you mind telling me what books or website you are using to learn SQL? I am in the process of learning it myself. Thanks in advance.|||

I can't imagine that you know less than me but so far I have been going through the following tutorial:

http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx?notification_id=1721521&message_id=1721521

The amount of good it is doing is questionable. If you have any other suggestions, let me know.

Good luck.

Help with multiple Left Joins

Hi All,
this is my first time posting here as i cannot find the answer myself.
I have couple tables i want to join and i can't seem to get it right. I
have the following tables:
Part: (Part ID), PartDescription
Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
I want a query of the Available qty >0 for every part we have. When i
do a query like this, i get 5363 records.
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART left outer JOIN
dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
Then i want to add a column for this query, the inventory_Trans.Qty
that has type =O. I tried the query below and it doesn't
work...obviously ican't inner join again from PART_WAREHOUSE as it
does the left join based on that table, so this wouldn't work:
SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM dbo.PART
left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_IS
left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
(INVENTORY_TRANS.TYPE='O')
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost
I tried using this, but i am not familiar with this syntax and i am
getting errors.
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1, PART p2
LEFT JOIN
dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN
dbo.INVENTORY_TRANS as i on p2.ID =
dbo.INVENTORY_TRANS.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
p1.unit_material_cost
Order by p1.warehouse_Id
so i am out of ideas. Can anyone enlighten me about how to do this:'
thank you so much in advance.Although this probably isn't the answer that you are looking for, but I'm
wondering why you are using the GROUP BY clause in your query? You typically
use GROUP BY when using an aggregate function in the SELECT statement, such
as COUNT. Try running the second and third queries without the GROUP BY
clause.
Try
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN dbo.INVENTORY_TRANS as i on w.ID = i.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
Order by p1.warehouse_Id
"lytung@.gmail.com" wrote:

> Hi All,
> this is my first time posting here as i cannot find the answer myself.
> I have couple tables i want to join and i can't seem to get it right. I
> have the following tables:
> Part: (Part ID), PartDescription
> Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
> Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
> I want a query of the Available qty >0 for every part we have. When i
> do a query like this, i get 5363 records.
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
>
> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
>
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART
> left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_IS
> left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
> (INVENTORY_TRANS.TYPE='O')
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
> I tried using this, but i am not familiar with this syntax and i am
> getting errors.
> SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
> w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
> FROM PART p1, PART p2
> LEFT JOIN
> dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
> LEFT JOIN
> dbo.INVENTORY_TRANS as i on p2.ID =
> dbo.INVENTORY_TRANS.PART_ID
> WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
> group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
> p1.unit_material_cost
> Order by p1.warehouse_Id
>
> so i am out of ideas. Can anyone enlighten me about how to do this:'
> thank you so much in advance.
>|||no that gave me an error.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'warehouse_Id'.
But even if that wo rked the logic doens't make sense.
I want the second query to be based on the first query. Maybe its not
about doing two joins but what i probably need is a transaction query.
First i need this:
SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
where ( w.AVAILABLE_QTY > 0)
Then i need the i.type='O' (from inventory_trans) based on those
results. I hope this make sense!|||Hi
> SELECT dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
> dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM dbo.PART left outer JOIN
> dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
is it possible that part with given ID doesn't belong to a part_warehouse?
in other words can you get null as warehouse_id in above query?
the second thing - group by clause here is really not necessary

> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work...obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
how about this?
SELECT pw.WAREHOUSE_ID, p.ID, p.DESCRIPTION, p.UNIT_MATERIAL_COST,
pw.AVAILABLE_QTY
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
WHERE (pw.AVAILABLE_QTY > 0)
AND (itr.TYPE='O')
HTH
Peter|||Hi Peter,
thanks for replying. The query you gave me ended up with too many
records. You are right, i dont need the group by statement.
Part_Warehouse has 2 Primary Keys: Part_ID, and WAREHOUSE_ID
you skipped out the PART_WAREHOUSE join to PART. I guess for this join
it doesn't have to be a left join, but it has to be joined. The second
join has to be left, which you did...
I am getting with mixing the joins. What is the general rule
of multiple joins? does the second join depend on the previous join? or
can they be independent?|||use parentheses to prioritize joins. the outer table is joined to result of
join in parentheses.
can you show the ddl of these tables and some sample data and describe
result you would like to obtain?
part_warehouse is a table that relates parts and warehouses?
> you skipped out the PART_WAREHOUSE join to PART. I guess for this join
> it doesn't have to be a left join, but it has to be joined. The second
> join has to be left, which you did...
FROM dbo.PART p left outer JOIN
( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID
) ON p.ID = pw.PART_ID
no, I left joined PART to the result of inner join between PART_WAREHOUSE
and INVENTORY_TRANS.
again, do you have PARTs without WAREHOUSEs?
peter

help with join please

(first off sorry if in wrong forum -- if so please let me know which is
best)
Its been a long time since I have done this and need some help! Basically I
have two tables that I want to join using a query that works in both SQL 7
and Jet. Here is the select statement I am using:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime, [Employee] Text;
SELECT L.WorkDate, L.Pay, L.Tips, A.AdvAmount
FROM tblLabor AS L LEFT JOIN tblAdvances AS A ON (L.WorkDate =
A.AdvanceDate) AND (L.Employee = A.Employee)
WHERE L.WorkDate BETWEEN [StartDate] AND [EndDate] AND L.Employee =
[Employee]
ORDER BY L.WorkDate
This works great except when I have more than one record in tblLabor with
the same date & employee (like a split shift) -- because then a single
records from tblAdvances gets joined to each record in tblLabor with the
same date.
I hope this makes sense and thanks in advance for your help!
DianaWilliams
I'm not sure that understand you. Can you post DDL + smaple data + expected
result?
Perhaps you need to use INNER JOIN instead of LEFT.
"Williams" <DianaValdezW@.prodigy.net.mx> wrote in message
news:OWYWvFHHFHA.2456@.TK2MSFTNGP09.phx.gbl...
> (first off sorry if in wrong forum -- if so please let me know which is
> best)
> Its been a long time since I have done this and need some help! Basically
I
> have two tables that I want to join using a query that works in both SQL 7
> and Jet. Here is the select statement I am using:
> PARAMETERS [StartDate] DateTime, [EndDate] DateTime, [Employee] Text;
> SELECT L.WorkDate, L.Pay, L.Tips, A.AdvAmount
> FROM tblLabor AS L LEFT JOIN tblAdvances AS A ON (L.WorkDate =
> A.AdvanceDate) AND (L.Employee = A.Employee)
> WHERE L.WorkDate BETWEEN [StartDate] AND [EndDate] AND L.Employee =
> [Employee]
> ORDER BY L.WorkDate
> This works great except when I have more than one record in tblLabor with
> the same date & employee (like a split shift) -- because then a single
> records from tblAdvances gets joined to each record in tblLabor with the
> same date.
> I hope this makes sense and thanks in advance for your help!
> Diana
>

Help with installation of SQL Server 2000 SP4

Hi,

I was wondering if anyone might be able to help me here. To be honest, this is my first time i have encountered SQL Server 2000 SP4. I need some guidance as to how to install and set up the SQL Server 2000 SP4. I have done it with SQL Server 2005 Express Edition and it's (I think) a lot easier to install and get it going.

My computer runs on windows XP Pro (windows 32bit). I got confused when i got to the following microsoft webpage - http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&DisplayLang=en. I downloaded SQL2000-KB884525-SP4-x86-ENU.EXE. And when running the setup.bat, the message came up saying "SQL Server 2000 is not installed on this machine. setup will now exit". What do i need to have the SQL Server 2000 installed on my machine?

Thank you in advance

What you tried to download is just the service pack, if you want to use SQL Server 2000 you either have to buy an edition (the developer edition is quite cheap) or get the MSDE which is the equivalent to the SQL Server Express Edition. MSDE was not bundled with the service pack like Express is, so you first have to install the SQL Server MSDE and then afterwards the SP4. Hope thing are clearer now.

HTH; Jens Suessmeyer.

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

Hi Jens,

Thank you for your support.

I've downloaded MSDE2000A.exe package. I run the setup.exe by typing in the command line as follows

setup SAPWD=hello INSTANCENAME="InstanceName" TARGETDIR="C:\MyInstanceFolder"

setup /settings "MyParameters.ini" SAPWD=hello

I've got an error message saying "The instance name specified is invalid".

I thought i have specified it to be InstanceName.

Can you help please?

Thank you in advance

|||

Where are you getting the error information, at the first commandline call, or the second. If it is the second, send the ini file over.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de