Help with complex query

Hi Everyone,

I need help writing the following query. I have to group my data by Department and have a field that will calculation the number of minutes that employee worked in that department. So basically I take the total number of minutes worked in the department and divide it by the total number of minutes the employee worked for the specified date range.

--

Agent Name: John Doe

Date Range: 1/1/2007 - 6/30/2007

RowID Work Minutes in Dept Total Work Minutes Dept

1 26355 52920 Service

2 9000 52920 Parts

3 17565 52920 Dispatch

Service = 26355 / 52920 = 0.499 = 50%

Parts = 9000 / 52920 = 0.17 = 17%

Dispatch= 17565 / 52920 = 0.33 = 33%

--

How can I accomplish this?

I am using SQL Server 2005 Express

Thank You

Assuming your table is the grouped sum's by department:

Code Snippet

create table #t1 (RowID int, [Work Minutes] int, [Total Work Minutes] int, Dept varchar(20) )

insert into #t1

select 1, 26355, 52920, 'Service'

union all select 2, 9000, 52920, 'Parts'

union all select 3, 17565, 52920, 'Dispatch'

select Dept, ' = ' + convert(varchar(15), [Work Minutes]) + ' / ' + convert(varchar(15), [Total Work Minutes]),

round(([Work Minutes]*100.00)/[Total Work Minutes], 0) as 'Percentage'

from #t1

|||

DaleJ,

The table data is not grouped. Thats what makes this query complex.

|||

SamCosta wrote:

DaleJ,

The table data is not grouped. Thats what makes this query complex.

Can you right click the tables that you are using and choose "Script Table As..." and "Create To.." and post those back here. Once we have your structure we can help further.|||

And some additional sample data.

It's not that difficult, but would like to get it right the first time (or two )

|||

Code Snippet

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),
Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

This query outputs:

EmpID DeptID AvgPLevel DeptWorkMI

28899 Service 2 17244

28899 Parts 3 9000

28899 Dispatch 1 27836

I need to then group the query results by EmpID to get a total Average Productivity Level

Result:

EmpID AvgPLevel

28899 2

How to calculate total productivity level:

(2 * 17244 / 54080) + (3 * 9000 / 54080) + (1 * 27836 / 54080) = 1.65 = Level 2

54080 is the total number of minutes worked in ALL departs. (17244 + 9000 + 27836 = 54080)

Thank You

|||

See if this does what you need:

Code Snippet

;WITH base

AS

(

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),

Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

),

Totals

AS

(

SELECT EmpID, SUM(DeptWorkMi) AS TotalMinutes

FROM base

GROUP BY EmpID

)

SELECT b.EmpID, ROUND(SUM(1.0 * b.AvgPLevel * b.DeptWorkMI / t.TotalMinutes), 0) as AvgPLevel

FROM base b

INNER JOIN Totals AS t

ON b.EmpID = t.EmpID

GROUP BY b.EmpID

|||

Thank you DaleJ. I was able to solve this problem using the CTE query example you provided.

Help with complex query

Hi Everyone,

I need some help writing a query that joins a table to a UNION query. I was wondering what is the most efficent way to do this.

Tables

Employees (EMPID, FULLNAME)

DailySchedules (SCHID,EMPID, SCHDATE,DEPTID)

GeneralSegments (GSID,EMPID,STARTTIME,STOPTIME)

DetailSegments (DSID,EMPID,STARTTIME,STOPTIME)

I need to join the records from GeneralSegments and DetailSegments THEN inner join DailySchedules and Employees.

Query must output:

EMPID, FULLNAME, SCHDATE,STARTTIME,STOPDTIME

Thank You

Can you provide some sample data (preferrably in the form of insert statements) along with a data representation of what you want for output?

Thanks.

|||

Untested, but, it should give you an idea

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
DailySchedules.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID

|||

Hi David,

That example will work. One question though, I have to display the next 14 days (2 weeks) on the web. Where should I place my WHERE clause? In the outer query or include the date range in both union statements?

ie. WHERE SCHDATE BETWEEN GETDATE() AND GETDATE() + 14

Thank You

|||

Is SCHDATE column present in the tables in the DailySchedule table or the segments tables? In any case, it doesn't matter. SQL Server will automatically roll the schdate predicate into the inner queries as well based on their reference. For example, if you want to filter on STARTTIME then you can include just one WHERE clause like below and it will be applied to both GeneralSegments & DetailSegments

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
d.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID
WHERE Segments.STARTTIME >= @.Start

sql

help with clr trigger

hi all.

i'm writing a simple clr trigger.

the trigger reads values from the INSERTED table.

and i use try - catch statement.

in the catch () i want to inesrt a new row to my Logtbl Table.

how can i insert row to a table in another tables trigger?

can you please give me a simple example?

thanks.

How about: "insert into Logtbl values(your values)" as a SqlCommand.

Niels
|||

should i use the same connection object for this sqlCommand?

|||Sure.

Niels

help with a User-Defined function to return a string from multiple records

I need some help with writing a User-Defined function in SQL Server 2000.
I would like to return a space-delimited string, which contains the column
data of several records from a table.
Here's an example:
table_fruit
id textid
-- --
1 APPLE
2 BANANA
4 ORANGE
8 PEAR
16 PLUM
My SQL query string uses a bitwise-AND (&) to determine which records to
return.
SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
So, for example, if I pass in the parameter @.param_fruits = 13, then I get
the following records back:
APPLE
ORANGE
PEAR
What I'd like to have is a User-Defined function that returns the data in a
concatenated space-delimited string like this:
APPLE ORANGE PEAR
I need help with writing this function. Thanks very much.To get a space delimited string, you should modify the statement as follows:
=====
-- Your function declarations etc
DECLARE @.returnString VARCHAR(8000)
SET @.returnString = ''
SELECT @.returnString = @.returnString + ' ' + textid
FROM table_fruit WHERE ([id] & @.param_fruits) > 0
RETURN (@.returnString)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I need some help with writing a User-Defined function in SQL Server 2000.
> I would like to return a space-delimited string, which contains the column
> data of several records from a table.
> Here's an example:
> table_fruit
> id textid
> -- --
> 1 APPLE
> 2 BANANA
> 4 ORANGE
> 8 PEAR
> 16 PLUM
>
> My SQL query string uses a bitwise-AND (&) to determine which records to
> return.
> SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> So, for example, if I pass in the parameter @.param_fruits = 13, then I get
> the following records back:
> APPLE
> ORANGE
> PEAR
> What I'd like to have is a User-Defined function that returns the data in
> a
> concatenated space-delimited string like this:
> APPLE ORANGE PEAR
> I need help with writing this function. Thanks very much.
>|||In addition, this method is unreliable and should be done on the client
side
As an alternative take a look at Erland's (if I remember well) example
CREATE PROCEDURE get_company_names_inline @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@.customers) s ON C.CustomerID = s.Value
go
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'
CREATE FUNCTION inline_split_me (@.param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @.param + ',', Number + 1,
charindex(',', ',' + @.param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @.param + ',') - 1
AND substring(',' + @.param + ',', Number, 1) = ',')
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
drop table numbers
drop function inline_split_me
drop proc get_company_names_inline
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
> follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Thanks very much! Worked great.
I had done the same query but had not declared the VARCHAR large enough, and
didn't use the SET statement to initialize the variable.
Thanks
Scott
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
column
get
in
>sql

Help with a loop.

I am writing a script that will go through all the database files on a
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?
What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:

> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:

> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> some
> only
> result,
>
>
|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return[vbcol=seagreen]
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]
|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =
Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:

> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>
|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need som
e
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result
,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of thes
e
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:

> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> some
> only
> result,
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of thes
e
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
[vbcol=seagreen]
> I assume you want to update the row if it already exists and insert the ro
w
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
>|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return[vbcol=seagreen]
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
>
of[vbcol=seagreen]
message[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
becuase[vbcol=seagreen]|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =
Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:

> Again, the COUNT(*) should do exactly what you need here... can you tell m
e
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders tab
le
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> multiple
> these
> way
> return
> of
> message
> need
> the
> becuase
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
[vbcol=seagreen]
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of th
e
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know
.
>
>
> "Adam Machanic" wrote:
>|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid =
> Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>|||What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
..
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:

> I am writing a vb.net application that calls a stored procedure and need s
ome
> help.
> I am writting the procedure to check if multiple records exists and the on
ly
> way I can figure it out is to use @.@.RowCount, but can't get the right resu
lt,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>

Help with @@Rowcount

I am writing a vb.net application that calls a stored procedure and need some
help.
I am writting the procedure to check if multiple records exists and the only
way I can figure it out is to use @.@.RowCount, but can't get the right result,
please help.
What I have now is
if exists(select c_driver from cartons where orderid = @.orderid and
@.@.Rowcount = 1)
update...
I tried using the following but got an error in my vb application becuase
the Procedure was returning rows
select c_driver from cartons where orderid = @.orderid
if @.@.Rowcount = 1
update...
Is there any way to use the above query without returning rows to VB?
Is there a way to write an exists to query @.@.Rowcount?What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
the last operation... I have a feeling you really want:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
update ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> I am writing a vb.net application that calls a stored procedure and need
some
> help.
> I am writting the procedure to check if multiple records exists and the
only
> way I can figure it out is to use @.@.RowCount, but can't get the right
result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I assume you want to update the row if it already exists and insert the row
if it doesn't exist. If so this should work...
UPDATE cartons SET
...
WHERE orderid = @.orderid
IF (@.@.ROWCOUNT = 0)
BEGIN
INSERT INTO cartons (...) VALUES (...)
END
Hope this helps.
Paul
"John Shepherd" wrote:
> I am writing a vb.net application that calls a stored procedure and need some
> help.
> I am writting the procedure to check if multiple records exists and the only
> way I can figure it out is to use @.@.RowCount, but can't get the right result,
> please help.
> What I have now is
> if exists(select c_driver from cartons where orderid = @.orderid and
> @.@.Rowcount = 1)
> update...
>
> I tried using the following but got an error in my vb application becuase
> the Procedure was returning rows
> select c_driver from cartons where orderid = @.orderid
> if @.@.Rowcount = 1
> update...
> Is there any way to use the above query without returning rows to VB?
> Is there a way to write an exists to query @.@.Rowcount?
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Adam Machanic" wrote:
> What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount of
> the last operation... I have a feeling you really want:
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> update ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > I am writing a vb.net application that calls a stored procedure and need
> some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the
> only
> > way I can figure it out is to use @.@.RowCount, but can't get the right
> result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >
>
>|||I tried the count() but I don't get what I need
what I need to do is determin is if a single driver is assigned to multiple
cartons within an order. for Instance say driver #1 was assigned to Carton
100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of these
cartons are in Order #1000
Order# Carton# Driver#
1000 100 1
1000 101 24
1000 102 24
What I need to get to is this without the select (becuase doing it this way
returns an error in my vb.net application because the select wants to return
rows)
select c_driver from Cartons where orderid = @.orderid
if @.@.RowCount = 1
--Only one driver exists for this order
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
if @.@.RowCount > 1
-- Multiple Driver exists for this Order
"Paul" wrote:
> I assume you want to update the row if it already exists and insert the row
> if it doesn't exist. If so this should work...
> UPDATE cartons SET
> ...
> WHERE orderid = @.orderid
> IF (@.@.ROWCOUNT = 0)
> BEGIN
> INSERT INTO cartons (...) VALUES (...)
> END
> Hope this helps.
> Paul
> "John Shepherd" wrote:
> > I am writing a vb.net application that calls a stored procedure and need some
> > help.
> >
> > I am writting the procedure to check if multiple records exists and the only
> > way I can figure it out is to use @.@.RowCount, but can't get the right result,
> > please help.
> >
> > What I have now is
> >
> > if exists(select c_driver from cartons where orderid = @.orderid and
> > @.@.Rowcount = 1)
> > update...
> >
> >
> > I tried using the following but got an error in my vb application becuase
> > the Procedure was returning rows
> >
> > select c_driver from cartons where orderid = @.orderid
> > if @.@.Rowcount = 1
> > update...
> >
> > Is there any way to use the above query without returning rows to VB?
> >
> > Is there a way to write an exists to query @.@.Rowcount?
> >
> >
> >|||Again, the COUNT(*) should do exactly what you need here... can you tell me
why this won't work for you:
if (select COUNT(*) from cartons where orderid = @.orderid) = 1
BEGIN
Update orders set oDriver = (select distinct c_driver from cartons
where orderid = @.orderid)
END
ELSE
BEGIN
-- do something else...
END
... Given that, however, I should ask why you're updating your orders table
with the driver from the cartons table? Why denormalize your data like
that?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> I tried the count() but I don't get what I need
> what I need to do is determin is if a single driver is assigned to
multiple
> cartons within an order. for Instance say driver #1 was assigned to Carton
> 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
these
> cartons are in Order #1000
> Order# Carton# Driver#
> 1000 100 1
> 1000 101 24
> 1000 102 24
> What I need to get to is this without the select (becuase doing it this
way
> returns an error in my vb.net application because the select wants to
return
> rows)
> select c_driver from Cartons where orderid = @.orderid
> if @.@.RowCount = 1
> --Only one driver exists for this order
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> if @.@.RowCount > 1
> -- Multiple Driver exists for this Order
>
> "Adam Machanic" wrote:
> > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
of
> > the last operation... I have a feeling you really want:
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > update ...
> >
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
message
> > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > I am writing a vb.net application that calls a stored procedure and
need
> > some
> > > help.
> > >
> > > I am writting the procedure to check if multiple records exists and
the
> > only
> > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > result,
> > > please help.
> > >
> > > What I have now is
> > >
> > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > @.@.Rowcount = 1)
> > > update...
> > >
> > >
> > > I tried using the following but got an error in my vb application
becuase
> > > the Procedure was returning rows
> > >
> > > select c_driver from cartons where orderid = @.orderid
> > > if @.@.Rowcount = 1
> > > update...
> > >
> > > Is there any way to use the above query without returning rows to VB?
> > >
> > > Is there a way to write an exists to query @.@.Rowcount?
> > >
> > >
> > >
> >
> >
> >|||The problem with count() is that for my example would return 3 rows, I can
only update when all cartons in the orders have the same driver#.
I left out the DISTINCT in my example it should be:
select DISTINCT c_driver from Cartons where orderid = @.orderid
If all drivers are the same for the cartons in the Order I should have 1
row, if there a seperate drivers I should have more than 1 row
The way the db is set up is to have one row for an order in the Orders
Table, ? Rows in Cartons table joined together on Orders.orderid =Cartons.Orderid based on the #of cartons shipped with the order. All of the
pricing and driver commissions are based on the total $'s charged from the
Orders table. So I need to update the orders table with the correct driver
information, but only if the driver is the same. Little round about I know.
"Adam Machanic" wrote:
> Again, the COUNT(*) should do exactly what you need here... can you tell me
> why this won't work for you:
>
> if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> BEGIN
> Update orders set oDriver = (select distinct c_driver from cartons
> where orderid = @.orderid)
> END
> ELSE
> BEGIN
> -- do something else...
> END
>
> ... Given that, however, I should ask why you're updating your orders table
> with the driver from the cartons table? Why denormalize your data like
> that?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > I tried the count() but I don't get what I need
> >
> > what I need to do is determin is if a single driver is assigned to
> multiple
> > cartons within an order. for Instance say driver #1 was assigned to Carton
> > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> these
> > cartons are in Order #1000
> >
> > Order# Carton# Driver#
> > 1000 100 1
> > 1000 101 24
> > 1000 102 24
> >
> > What I need to get to is this without the select (becuase doing it this
> way
> > returns an error in my vb.net application because the select wants to
> return
> > rows)
> >
> > select c_driver from Cartons where orderid = @.orderid
> > if @.@.RowCount = 1
> > --Only one driver exists for this order
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> >
> > if @.@.RowCount > 1
> > -- Multiple Driver exists for this Order
> >
> >
> > "Adam Machanic" wrote:
> >
> > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> of
> > > the last operation... I have a feeling you really want:
> > >
> > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > update ...
> > >
> > >
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --
> > >
> > >
> > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> message
> > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > I am writing a vb.net application that calls a stored procedure and
> need
> > > some
> > > > help.
> > > >
> > > > I am writting the procedure to check if multiple records exists and
> the
> > > only
> > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > result,
> > > > please help.
> > > >
> > > > What I have now is
> > > >
> > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > @.@.Rowcount = 1)
> > > > update...
> > > >
> > > >
> > > > I tried using the following but got an error in my vb application
> becuase
> > > > the Procedure was returning rows
> > > >
> > > > select c_driver from cartons where orderid = @.orderid
> > > > if @.@.Rowcount = 1
> > > > update...
> > > >
> > > > Is there any way to use the above query without returning rows to VB?
> > > >
> > > > Is there a way to write an exists to query @.@.Rowcount?
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>|||SELECT COUNT(DISTINCT(DriverNum)) FROM Cartons ?
Paul
"John Shepherd" wrote:
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I know.
>
>
> "Adam Machanic" wrote:
> > Again, the COUNT(*) should do exactly what you need here... can you tell me
> > why this won't work for you:
> >
> >
> > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > BEGIN
> > Update orders set oDriver = (select distinct c_driver from cartons
> > where orderid = @.orderid)
> > END
> > ELSE
> > BEGIN
> > -- do something else...
> > END
> >
> >
> > ... Given that, however, I should ask why you're updating your orders table
> > with the driver from the cartons table? Why denormalize your data like
> > that?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
> > news:41549C5D-63AA-46DA-92F4-A148A87D1143@.microsoft.com...
> > > I tried the count() but I don't get what I need
> > >
> > > what I need to do is determin is if a single driver is assigned to
> > multiple
> > > cartons within an order. for Instance say driver #1 was assigned to Carton
> > > 100 and driver #24 was assigned to Carton 101 and carton 102. All 3 of
> > these
> > > cartons are in Order #1000
> > >
> > > Order# Carton# Driver#
> > > 1000 100 1
> > > 1000 101 24
> > > 1000 102 24
> > >
> > > What I need to get to is this without the select (becuase doing it this
> > way
> > > returns an error in my vb.net application because the select wants to
> > return
> > > rows)
> > >
> > > select c_driver from Cartons where orderid = @.orderid
> > > if @.@.RowCount = 1
> > > --Only one driver exists for this order
> > > Update orders set oDriver = (select distinct c_driver from cartons
> > > where orderid = @.orderid)
> > >
> > > if @.@.RowCount > 1
> > > -- Multiple Driver exists for this Order
> > >
> > >
> > > "Adam Machanic" wrote:
> > >
> > > > What are you querying @.@.ROWCOUNT for? @.@.ROWCOUNT returns the rowcount
> > of
> > > > the last operation... I have a feeling you really want:
> > > >
> > > > if (select COUNT(*) from cartons where orderid = @.orderid) = 1
> > > > update ...
> > > >
> > > >
> > > >
> > > > --
> > > > Adam Machanic
> > > > SQL Server MVP
> > > > http://www.sqljunkies.com/weblog/amachanic
> > > > --
> > > >
> > > >
> > > > "John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in
> > message
> > > > news:7A9F67DA-3C32-445F-B9AD-8A69F4855292@.microsoft.com...
> > > > > I am writing a vb.net application that calls a stored procedure and
> > need
> > > > some
> > > > > help.
> > > > >
> > > > > I am writting the procedure to check if multiple records exists and
> > the
> > > > only
> > > > > way I can figure it out is to use @.@.RowCount, but can't get the right
> > > > result,
> > > > > please help.
> > > > >
> > > > > What I have now is
> > > > >
> > > > > if exists(select c_driver from cartons where orderid = @.orderid and
> > > > > @.@.Rowcount = 1)
> > > > > update...
> > > > >
> > > > >
> > > > > I tried using the following but got an error in my vb application
> > becuase
> > > > > the Procedure was returning rows
> > > > >
> > > > > select c_driver from cartons where orderid = @.orderid
> > > > > if @.@.Rowcount = 1
> > > > > update...
> > > > >
> > > > > Is there any way to use the above query without returning rows to VB?
> > > > >
> > > > > Is there a way to write an exists to query @.@.Rowcount?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Why don't you try COUNT(DISTINCT c_driver) ?
Rather than storing the data in two places (which has data integrity
implications), have you considered creating a view that will return the data
in the way you need it for reporting?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"John Shepherd" <JohnShepherd@.discussions.microsoft.com> wrote in message
news:871ECA2C-4846-4442-83FE-53C1657A3159@.microsoft.com...
> The problem with count() is that for my example would return 3 rows, I can
> only update when all cartons in the orders have the same driver#.
> I left out the DISTINCT in my example it should be:
> select DISTINCT c_driver from Cartons where orderid = @.orderid
> If all drivers are the same for the cartons in the Order I should have 1
> row, if there a seperate drivers I should have more than 1 row
> The way the db is set up is to have one row for an order in the Orders
> Table, ? Rows in Cartons table joined together on Orders.orderid => Cartons.Orderid based on the #of cartons shipped with the order. All of
the
> pricing and driver commissions are based on the total $'s charged from the
> Orders table. So I need to update the orders table with the correct driver
> information, but only if the driver is the same. Little round about I
know.
>

Help W/Disabling FK Constraints for Batch Operations

HI all,

I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.

Here's where I'm at:

-
CREATE PROCEDURE [dbo].[_InitializeDB]
AS

SET NOCOUNT ON

DECLARE @.name varchar(255)
DECLARE @.sql nvarchar(255)

DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'

OPEN tables
FETCH NEXT FROM tables INTO @.name
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.sql = 'ALTER TABLE ['+ @.name + '] NOCHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
SET @.sql = 'DISABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'TRUNCATE TABLE [' + @.name + ']'
EXEC sp_executesql @.sql
BEGIN TRY
SET @.sql = 'INSERT INTO [' + @.name + '] (Active) VALUES (0)'
EXEC sp_executeSQL @.sql
END TRY
BEGIN CATCH
PRINT @.sql + ':'
PRINT ERROR_MESSAGE()
END CATCH
SET @.sql = 'ENABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'ALTER TABLE ['+ @.name + '] CHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
FETCH NEXT FROM tables INTO @.name
END

CLOSE tables
DEALLOCATE tables
-

Running this Sproc produces (for the first ref'd table):

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.

If you dont wanna be bothered by the FK Constraints you have to drop them do your work and recreate them after you did your work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||TRUNCATE TABLE doesn't check the state of the FK constraint so this method will not work. You will have to drop and recreate the FK constraint for the truncate table to work.

help required in writing a query

hi all,

consider the following scenario:

I have 2 tables ModelSalesUnitCode and SalesUnitCode with following data:

ModelSalesUnit
Kis Type Salu_cd
PC 6 2AA01
WA 3 2AA01
Wa 3 3AA01

SalesUnitCode
Salu_cd Kis Type Salu_desc
2AA01 null null generic description
2AA01 WA 3 WA description
3AA01 WA 3 generic description

Now I write this query to get the salu_cd and their respective descriptions for the model with (kis = WA and Type = 3)

Select
a.kis,
a.type,
a.salu_cd,
b.salu_desc
From
modelsalesunitcode a, salesunitcode b
Where
b.salu_cd = a.salu_cd
And
a.kis = 'WA'
And
a.type = '3'

This gives me
kis type salu_cd salu_desc
WA 3 2AA01 generic description
WA 3 2AA01 WA description
WA 3 3AA01 generic description

is it possible to modify the above query in such a way that it returns

kis type salu_cd salu_desc
WA 3 2AA01 WA description
WA 3 3AA01 generic description

meaning where description exists for (Kis = WA and Type = 3) pick it up (WA Description in above case) if it does not exist then pick the generic description.

Thanx in advance
Omer

/Add clause in WHERE:

Select
a.kis,
a.type,
a.salu_cd,
b.salu_desc
From
modelsalesunitcode a, salesunitcode b
Where
b.salu_cd = a.salu_cd
And
a.kis = 'WA'
And
a.type = '3'
--------
AND b.kis IS NOT NULL
--------

Help required in writing a query

hi

Consider a table (ShogenValue) with following data:

Model_Id header_id Serial_no shogen_no si_val
4329 1 40001 A0001 1

4329 1 40001 B0001 2
4329 1 50300 B0001 3

4329 1 40001 C0001 4
4329 1 50300 C0001 5
4329 1 60001 C0001 6

Select
si_val
From
ShogenValue
Where
model_id = 4329
And
header_id = 1
And
serial_no = '60001'

Is it possible to write a modified version of above query which will bring the following result:

Model_Id header_id Serial_no shogen_no si_val
4329 1 40001 A0001 1
4329 1 50300 B0001 3
4329 1 60001 C0001 6

Meaning if a record exists for a particular shogen_no (Shogen_no C0001 has value corrosponding to '60001' in above query), pick it up, if it

does not exist (Shogen_no B0001 does not have value corrosponding to '60001' in above case) then pick a record with one level less serial

number ('50300' in above case) and so on.

Thanx
Omer ImtiazUse MAX to get the highest shogen_no for each Serial_no:

select serial_no, max(shogen_no) max_shogen_no
from shogen_value
where ...
group by serial_no;

Then join that to main query, e.g

select ...
from shogen_value
where (serial_no, shogen_no) in
(
select serial_no, max(shogen_no) max_shogen_no
from shogen_value
where ...
group by serial_no
);