Please Advice !

Hi,

I am trying to solve this procedure.

Let me try to explain it...I am getting DEGREEID from one of the SELECT query . I want to OUTPUT (ie , COUNT) from procedure,the number of departments with the degreeid, got from the above query.

With below procedure, Since an employee can have multiple DEGREEID , the cursor is giving OUPTUT ie, COUNT for the LAST Degreeid. Eventhough the previous DEGREEID dont have any DEPARTMENT...but only for the LAST DEGREEID...!

How can I solve this.... whether I can solve this with CURSOR or I have to use someother way...Please advice me !

DATA
---
DEGREE_EARNED
-------
EMPID DEGREEID
-- ----
201 12
201 3
201 250
202 3
202 10
203 17

DEPARTMENT
-----
DEPID DEGREEID
-- ----
10 1 12
111 250
111 12
121 3
121 12
121 250
-----------



---------------------
DECLARE @.vchid int
DECLARE testcursor CURSOR FOR

SELECT degree_id
FROM degree_earned WHERE emp_id= @.empid

OPEN testcursor
FETCH NEXT FROM testcursor INTO @.vchid

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN
Select @.outresult = COUNT(*)
from
department
where degree_id = @.vchid

FETCH NEXT FROM testcursor INTO @.vchid
END
---------------------Can somebody help me,please (:|||So is what you want something like:EMPID DEGREEID DEPT_COUNT
-- --- ----
201 12 2
201 3 1
201 250 2
202 3 1
202 10 0
203 17 0I don't understand exactly what you want, so I can't be much help until I do. Sorry!

-PatP|||Hey Pat,

Thats what i wanted...Can u please show me , how to solve this...Thanks!|||USE Northwind
GO

CREATE TABLE DEGREE_EARNED(EMPID int, DEGREEID int)
CREATE TABLE DEPARTMENT(DEPID int, DEGREEID int)
GO
INSERT INTO DEGREE_EARNED(EMPID, DEGREEID)
SELECT 201, 12 UNION ALL
SELECT 201, 3 UNION ALL
SELECT 201, 250 UNION ALL
SELECT 202, 3 UNION ALL
SELECT 202, 10 UNION ALL
SELECT 203, 17

INSERT INTO DEPARTMENT(DEPID, DEGREEID)
SELECT 101, 12 UNION ALL
SELECT 111, 250 UNION ALL
SELECT 111, 12 UNION ALL
SELECT 121, 3 UNION ALL
SELECT 121, 12 UNION ALL
SELECT 121, 250
GO

SELECT A.DEGREEID, ISNULL(ROW_OCCURS,0)
FROM ( SELECT DISTINCT DEGREEID FROM DEGREE_EARNED) AS A
LEFT JOIN ( SELECT DEGREEID, COUNT(*) AS ROW_OCCURS
FROM DEPARTMENT GROUP BY DEGREEID) AS B
ON A.DEGREEID = B.DEGREEID
GO

--DROP TABLE DEGREE_EARNED
--DROP TABLE DEPARTMENT
GOsql

Plain text (fixed length field) report output?

I am desperate to get output from Reporting Services into a text
file. Any plugins, converters or technique that anybody knows of?On Aug 2, 12:20 pm, datam...@.gmail.com wrote:
> I am desperate to get output from Reporting Services into a text
> file. Any plugins, converters or technique that anybody knows of?
Outside of the export option of Reporting Services and the Report Mgr,
you would most likely need to design a custom asp.net application that
could output the results of an ADO.NET dataset. An SSRS report could
be executed programmatically (this link might help:
http://msdn2.microsoft.com/en-us/library/aa964126.aspx#sqldocum_topic7
). If exporting to PDF programmatically is an option, I would suggest
using the open source library iTextSharp (http://sourceforge.net/
projects/itextsharp/ ). Crystal Reports could be setup via a report
viewer control and cr.exe to programmatically export a report to text
via an application. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Pivot SQL Statement in SQL SERVER 2000

I have a table temp1 with the following data:
a b c d
10 11 888 991
10 11 888 992
How do I pivot this such that the output be
10 11 888 991 992
Thanks
harishWith the extremely limited information you've provided,
SELECT a,b,c,MIN(d),MAX(d)
FROM temp1
GROUP BY a,b,c
If you want a more complete answer, please provide a more complete question.
http://www.aspfaq.com/5006
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131137736.316980.221230@.g49g2000cwa.googlegroups.com...
>I have a table temp1 with the following data:
> a b c d
> 10 11 888 991
> 10 11 888 992
> How do I pivot this such that the output be
> 10 11 888 991 992
> Thanks
> harish
>|||Hey Thanks
But if i have more than 2 rows for one key (a,b,c) the this will not
work.
In that case how do I do it?|||> But if i have more than 2 rows for one key (a,b,c) the this will not
> work.
See how helpful more information would have been?

> In that case how do I do it?
http://www.aspfaq.com/2462
Again, if you want a more useful answer, provide a more detailed question.
See the link I posted in my previous response.|||Hey thanks a lot
that was helpful|||harish (harish.prabhala@.gmail.com) writes:
> But if i have more than 2 rows for one key (a,b,c) the this will not
> work.
> In that case how do I do it?
Books Online has a section on it:
Accessing and Changing Relational Data
Advanced Query Concepts
Transact-SQL Tips
Cross-Tab Reports
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The more things change the more they remain then same:)
Check out THE Rac utility @.
www.rac4sql.net