Help with SQL Query

Hi Guys,

I need "little help" (in my books) with my SQL Query.

I have a table which lists the total transactions entered by each staff member.
All the transactions are saved with the staffID of the staff member and the date of the transaction.

Date Amount Staff
1/1/2005 12 STaff1
1/1/2005 2 STaff2
1/1/2005 1 STaff3
1/1/2005 5 STaff1

Now the problem is that I need to select All the transactions from the table grouped on the basis of data and staff member,

so essentially its should look like

Date Total Staff1 Staff2 Staff3
1/1/2005 24 12 11 1

Moreover the number of staff members can be anything, so basically I can't even use sub queries.

I have been working on this one for a while now, and still don't know what to do... any help will be greatly appreciated.

ThanxYou will need a dynamic pivot (cross-tab) function - something like:

http://www.sqlteam.com/item.asp?ItemID=2955|||hi ehorn,

Thanks for your help... worked like a charm.. :)

Cheers!

help with SQL procedure conversion

Hi Guys, I need some in SQL conversion from Oracle to SQL Server...Here is the procedure in T-SQL..When I run the below SQL in SQL Server, it is going in infinite loop. When I click stop, I am getting the error ......
"Invalid length parameter passed to the substring function."
at the following line
SELECT @.RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@.UserRoles_in, 1, CHARINDEX(',', @.UserRoles_in) - 1))
----
DECLARE @.objid_in INT
DECLARE @.objclass_in INT
DECLARE @.userid_in INT
DECLARE @.userRoles_in VARCHAR(3000)
DECLARE @.RoleID_in INT
DECLARE @.cnt INT

DECLARE csr CURSOR FOR
SELECT * FROM objectACL
OPEN csr
WHILE (0 = 0)
BEGIN --(

fetch NEXT FROM csr INTO @.objid_in, @.objclass_in, @.userid_in, @.userRoles_in
IF (@.@.FETCH_STATUS = -1)
BREAK
SELECT @.UserRoles_in = SUBSTRING(@.UserRoles_in, 2, LEN(@.UserRoles_in))
WHILE (0 = 0)
BEGIN --(
SELECT @.RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@.UserRoles_in, 1, CHARINDEX(',', @.UserRoles_in) - 1))
SELECT @.cnt = COUNT(*) FROM nodetable WHERE objtype = 21 AND id = @.RoleId_in
IF ( @.cnt = 0 )
BEGIN
INSERT INTO error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@.RoleID_in AS VARCHAR) )
END
SELECT @.UserRoles_in = SUBSTRING(@.UserRoles_in, LEN(@.RoleID_in) + 2, LEN(@.UserRoles_in))
IF ( @.UserRoles_in is null )
BEGIN
BREAK
END
END --)
END --)
close csr
DEALLOCATE csr
GO
------

Corresponding procedure in Oracle
-----
declare
cursor csr is select * from objectACL;

objid_in number;
objclass_in number;
userid_in number;
userRoles_in varchar2(3000);
RoleID_in number;
cnt number;

begin
open csr;
loop
fetch csr into objid_in, objclass_in, userid_in, userRoles_in;
exit when csr%notfound;

UserRoles_in := substr(UserRoles_in, 2);

loop
RoleID_in := to_number(substr(UserRoles_in, 1, instr(UserRoles_in, ',')-1));
select count(1) into cnt from nodetable where objtype=21 and id=RoleId_in;
if (cnt =0) then
insert into error_report values ('ObjectACL', '0', 'UserRoles refering to Non-existing Role : '||RoleId_in);
end if;

UserRoles_in := substr(userRoles_in, length(RoleId_in)+2);

if (userRoles_in is null) then
exit;
end if;
end loop;
end loop;
close csr;
end;
/
------Dear Lord, PSQL is a sucky language. That is pretty near unreadable.

Do yourself a favor and don't even try to convert this into TSQL directly. Oracle developers love cursors, but set-based operations are almost always easier to debug and run faster. I'd better dollars to doughnut holes you don't even need a cursor for this.

Post your table layout and a description of what you are trying to do.

Help with select statement

Hi guys,

I have the following data

CaseNumber ConnectionToCase PersonID

-

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00002 L 355

00003 F 577

00003 J 235

00003 C 744

00003 K 563

00005 A 501

00005 K 455

00009 R 500

00009 6 017

00009 K 011

I would like to select all columns based on casenumber that contains only ConnectionToCase = '6'.

Therefore i need a query that will return this result:

CaseNumber ConnectionToCase PersonID

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00009 R 500

00009 6 017

00009 K 011

How can i do that? thanks guys

Jul.

Something like this should work:

Code Snippet

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber IN ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||

See if this works for you:

Code Snippet

select a.CaseNumber, a.ConnectionToCase, a.PersonID

from casedata a

inner join casedata b

on a.CaseNumber = b.CaseNumber

and b.ConnectionToCase = '6'

|||

Here you go....

Code Snippet

Create Table #casedata (

[CaseNumber] Varchar(100) ,

[ConnectionToCase] Varchar(100) ,

[PersonID] Varchar(100)

);

Insert Into #casedata Values('00001','A','500');

Insert Into #casedata Values('00001','J','235');

Insert Into #casedata Values('00001','6','014');

Insert Into #casedata Values('00001','K','016');

Insert Into #casedata Values('00002','A','500');

Insert Into #casedata Values('00002','B','477');

Insert Into #casedata Values('00002','6','251');

Insert Into #casedata Values('00002','L','355');

Insert Into #casedata Values('00003','F','577');

Insert Into #casedata Values('00003','J','235');

Insert Into #casedata Values('00003','C','744');

Insert Into #casedata Values('00003','K','563');

Insert Into #casedata Values('00005','A','501');

Insert Into #casedata Values('00005','K','455');

Insert Into #casedata Values('00009','R','500');

Insert Into #casedata Values('00009','6','017');

Insert Into #casedata Values('00009','K','011');

Select

*

From

#casedata

where

[CaseNumber] in (

Select

[CaseNumber]

From

#casedata

Where [ConnectionToCase]='6'

)

|||

SELECT * FROM myTable

WHERE CaseNumber IN (SELECT CaseNumber FROM myTable WHERE ConnectionToCase = 6)

Adamus

|||

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've got this error when ran this query:

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber = ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||Thanks guys!!

Help with query

Hi guys, I wonder if I may ask for your help here.
I have two tables RoleNames and Roles where RoleNames are the available
roles and Roles are the Roles which the users 'may' have.
RoleName
ID int
RoleName char(10)
Roles
RoleID int
user_name NVCHAR
I need to find our what roles a give user does not have. I'm struggling with
what I am sure is a simple query. Any help is appreciated.Please post *real* DDL and sample data, so we don't have to guess what the
relationships between these two table are.
Also consider normalizing your model some more.
There are two entities and a many-to-many relationship so, I guess you're a
table short. Do not design tables until you have a sound concept and logical
model.
Entities:
1) users;
2) roles.
Relationships:
1) users in roles.
ML
http://milambda.blogspot.com/|||What makes you think this is a many to many relationship ?
RoleNames PK can appear Many Time in Roles FK. - This is pretty simple
stuff. I have not seen you come up with an answer to anything yet so please
stop replying to my posts unless you can offer some solution.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A09D1C25-9164-4C79-A900-12DDC7F343EA@.microsoft.com...
> Please post *real* DDL and sample data, so we don't have to guess what the
> relationships between these two table are.
> Also consider normalizing your model some more.
> There are two entities and a many-to-many relationship so, I guess you're
> a
> table short. Do not design tables until you have a sound concept and
> logical
> model.
> Entities:
> 1) users;
> 2) roles.
> Relationships:
> 1) users in roles.
>
> ML
> --
> http://milambda.blogspot.com/|||In fact just forget it, Ive worked it out for myself without your useless
input.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A09D1C25-9164-4C79-A900-12DDC7F343EA@.microsoft.com...
> Please post *real* DDL and sample data, so we don't have to guess what the
> relationships between these two table are.
> Also consider normalizing your model some more.
> There are two entities and a many-to-many relationship so, I guess you're
> a
> table short. Do not design tables until you have a sound concept and
> logical
> model.
> Entities:
> 1) users;
> 2) roles.
> Relationships:
> 1) users in roles.
>
> ML
> --
> http://milambda.blogspot.com/|||Good.
ML
http://milambda.blogspot.com/|||> stop replying to my posts
Ok.
ML
http://milambda.blogspot.com/

Help with my SP please

Hey guys, I have a question.
I have a table with 2 decimal fields 10,2.
In my sp i get those fields and divid them by an number.
I get like 5 or 6 0's trailing the calculation.
Ex: 191.6/2 I get 95.800000
Why?
Here is my sp:
CREATE PROCEDURE sp_SummaryReport
(
@.startdate datetime,
@.enddate datetime
)

AS
BEGIN

SELECT tblLegendReportAbv.ReportType AS ReportType,
SUM(tblSummaryData.Volume) AS Volume,
SUM(tblSummaryData.NetEffect)/COUNT(tblSummaryData.DataID) AS NetEffect,
SUM(tblSummaryData.GrossEffect)/COUNT(tblSummaryData.DataID) AS GrossEffect
FROM tblSummaryData
INNER JOIN tblLegendReportAbv ON LTRIM(RTRIM(LOWER(tblSummaryData.ReportType))) = LTRIM(RTRIM(LOWER(tblLegendReportAbv.ReportAbv)))
WHERE tblSummaryData.WeekEndDate BETWEEN @.startdate AND @.enddate
GROUP BY tblSummaryData.ReportType,tblLegendReportAbv.Repor tType

END
GO

Please helpThat's beacause the implicit conversion made by SQL Server when dividing to different types. For more info see "Data Type Precedence" in your SQL help file.

But this only an aesthetic problem, it can be easily solved with an explicit conversion or with a cast like this:

cast(191.6/2 as decimal(10,2))

For more info on cast and convert see "CAST and CONVERT" in your SQL help file.

Best regards!|||Thank you.

Help with insert sql statements...

Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.

Is there a way for the insert statements to be merged so that I won't be executing two statements?

Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)

Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)

Thanks.

If you really need to have it in one statement, you could put all of your logic into a stored procedure and simply call it from you application.