Help with SQL

I have a table with columsn employee number, col1, col2, col3, col4, col5. Call this table 'OLDTABLE'
eg of a record:

Employee number | Col1 | Col2 | Col3 | Col4 | Col5
---------------
123455 x y z a b

Call this new table 'NEWTABLE'. I have to tranfer the data from the above table in a new one that shud look like employee number, col. the above example shud look like:

Employee number | Col
--------
123455 x
123455 y
123455 z
123455 a
123455 b

I was looking for a SQL for such a task.

ThanksINSERT INTO NEW
SELECT EmpId, Col1
FROM OLD
UNION ALL
SELECT EmpId, Col2
FROM OLD
UNION ALL
SELECT EmpId, Col3
FROM OLD
UNION ALL
SELECT EmpId, Col4
FROM OLD
UNION ALL
SELECT EmpId, Col5
FROM OLD|||thanks great brettsql

Help with Select statement

Help me with the following query.
I have employee table as follows
EmpID (Number)
FullName (Text)
ReportTo(Number)
ReportTo field contain number from EmpID
Sample Data from the table
EmpIDFullNameReports To
1Nancy Davolio2
2Andrew Fuller
3Janet Leverling2
4Margaret Peacock2
5Steven Buchanan2
6Michael Suyama5
7Robert King5
8Laura Callahan2
9Anne Dodsworth5
10Andrew Leverling3
11Michael Miller5
12Robert Davolio3
13Nancy Suyama6
14Margaret King7
I would like to create a single query (T-SQL), when I pick an
employee ID, I would like to have the entire list of employees come
under that employee including that employee and all below him/her.
Example. If I pick ID = 5, should get the following list.
EmpIDFullNameReports To
5Steven Buchanan2
6Michael Suyama5
7Robert King5
9Anne Dodsworth5
11Michael Miller5
13Nancy Suyama6
14Margaret King7
The list above shows all staff who report to ID 5 directly, But I want very
who are reporting to staff reporting to staff report to ID 5 and below in the
hieratical organization.
How do I do that query?
Thanks
Harry
Harry J Nathan
see
http://www.mindsdoor.net/SQLTsql/Ret...Hierarchy.html
"Harry J Nathan" wrote:

> Help me with the following query.
> I have employee table as follows
> EmpID (Number)
> FullName (Text)
> ReportTo(Number)
> ReportTo field contain number from EmpID
> Sample Data from the table
> EmpIDFullNameReports To
> 1Nancy Davolio2
> 2Andrew Fuller
> 3Janet Leverling2
> 4Margaret Peacock2
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 8Laura Callahan2
> 9Anne Dodsworth5
> 10Andrew Leverling3
> 11Michael Miller5
> 12Robert Davolio3
> 13Nancy Suyama6
> 14Margaret King7
>
> I would like to create a single query (T-SQL), when I pick an
> employee ID, I would like to have the entire list of employees come
> under that employee including that employee and all below him/her.
> Example. If I pick ID = 5, should get the following list.
> EmpIDFullNameReports To
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 9Anne Dodsworth5
> 11Michael Miller5
> 13Nancy Suyama6
> 14Margaret King7
> The list above shows all staff who report to ID 5 directly, But I want very
> who are reporting to staff reporting to staff report to ID 5 and below in the
> hieratical organization.
> How do I do that query?
> Thanks
> Harry
> --
> Harry J Nathan

Help with Query

Hi Everyone,

I need help writing the following query. I have a table named DeptHistory that stores an employee's department history:

DeptHistory [DeptID(pk),EmpID(fk),StartDate,EndDate]

(EndDate will be NULL for the current department)

I need to write a query that will return all the department that employee was in between Jan 2007 to Jun 2007

Example:

--

Employee Name: Sam Costa

Service Dept 5/1/2006 - 12/5/2006

Parts Dept 12/6/2006 - 3/1/2007

Dispatch Dept 3/2/2007 - NULL

--

So the query should return Parts Dept and Dispatch Dept

Thank You,

-Sam

Code Snippet

select DeptID

from DeptHistory dh

where isnull(year(EndDate), 2007) = 2007

and EmpID = 1

|||

Hi DaleJ,

Thank you for your response. The query you provided does not check which depts the employee was in for a specifed date range.

|||

Well, If the end date isn't in 2007 (or null) then they weren't in that dept between jan 2007 and jun 2007.

|||

try the following query..

Code Snippet

Create Table #data (

[Dept] Varchar(100) ,

[SDate] datetime ,

[EDate] datetime

);

Insert Into #data Values('Service Dept','5/1/2006','12/5/2006');

Insert Into #data Values('Parts Dept','12/6/2006','3/1/2007');

Insert Into #data Values('Dispatch Dept','3/2/2007',NULL);

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or EDate between '01/01/2007' and '6/30/2007'

|||

what if an employee was employeed at a dept for the entire duration?

Code Snippet

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or isnull(EDate, getdate()) between '01/01/2007' and '6/30/2007'

or (SDate < '01/01/2007' and isnull(EDate, getdate()) > '6/30/2007')

also... for each end date, you'll have to check for null.

|||

As the approach below demonstrates, it is only necessary to determine if someone's StartDate is before the end of the period, and that their EndDate was sometime after the beginning of the period. (The current date is substitued for the NULL values.)

Code Snippet

SET NOCOUNT ON

DECLARE @.DeptHistory table
( RowID int IDENTITY,
DeptID int,
EmpID int,
StartDate smalldatetime,
EndDate smalldatetime
)

DECLARE @.Employee table
( EmpID int IDENTITY,
EmpName varchar(20)
)

DECLARE @.Department table
( DeptID int IDENTITY,
DeptName varchar(20)
)

INSERT INTO @.Employee VALUES ( 'Sam Costa' )
INSERT INTO @.Employee VALUES ( 'Bilbo Baggins' )

INSERT INTO @.Department VALUES ( 'Service' )
INSERT INTO @.Department VALUES ( 'Parts' )
INSERT INTO @.Department VALUES ( 'Dispatch' )

INSERT INTO @.DeptHistory VALUES ( 1, 1, '5/1/2006', '12/5/2006' )
INSERT INTO @.DeptHistory VALUES ( 1, 2, '5/1/2006', NULL )
INSERT INTO @.DeptHistory VALUES ( 2, 1, '12/6/2006', '3/1/2007' )
INSERT INTO @.DeptHistory VALUES ( 3, 1, '3/2/2007', NULL )

SELECT
d.DeptName,
e.EmpName,
h.StartDate,
h.EndDate
FROM @.DeptHistory h
JOIN @.Employee e
ON h.EmpID = e.EmpID
JOIN @.Department d
ON h.DeptID = d.DeptID
WHERE ( h.StartDate < '2007/07/01'
AND isnull( h.EndDate, getdate() ) >= '2007/01/01'
)

DeptName EmpName StartDate EndDate
-- -- -- -
Service Bilbo Baggins 2006-05-01 NULL
Parts Sam Costa 2006-12-06 2007-03-01
Dispatch Sam Costa 2007-03-02 NULL

(Output edited for display.)

help with nested Query

Hi
I have 2 tables. The first has employee information and the second has
payroll information. I need to find out people who are not in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter it and find
out for each payroll.
I don't think i have explained it very well so here is the data set.
hope someone can help me with this.
Thanks in advance
prit

Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333

TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333

Required RESULT required(Missing employees from payroll)
IDNumSSN
1333333333
2111111111I think this could be what you're looking for:

SELECT I.idnum, E.ssn
FROM
(SELECT DISTINCT idnum
FROM PayrollDetail) AS I
CROSS JOIN Employees AS E
LEFT JOIN PayrollDetail AS D
ON I.idnum = D.idnum
AND E.ssn = D.ssn
WHERE D.idnum IS NULL

If you have another table for the entity represented by Idnum then use that
table in place of the derived table "I".

--
David Portas
SQL Server MVP
--

Help with my query

Hi,

I would like to get the data which return employee code associate with the order that is already assigned to them. But on those orders, I also would like to create different column counting how many orders are finished, and how many orders are still in process.

Can anyone help me with the query?

=============================================================

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

==============================================================

Above query will return the number of orders assigned to employee, however, I also need to get how many orders finished and how many orders still in process.

Thanks in advance.

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount,

SUM(CASE WHERE oa.OrderStatus='Finished' THEN 1 ELSE 0 END) AS Finished,

SUM(CASE WHERE oa.OrderStatus='InProcess' THEN 1 ELSE 0 END) AS InProcess
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

Help with my first report!

Hi Team,

I am trying to create my first report here. My report will show the employee name with corresponding order assigned to them. I already created one datasets which returned the employee name, then I created another one which take the employee name and returned count of the order.

My questions is:

1) is that possible to passing the parameter from the first datasets which contain of customer name?

2) is there any better way of doing this (maybe using one datasets instead of two).

Any respond I will really appreciate.

Anyone please!|||

Make one dataset with query like this:

select empName, count(orderID) as orderCount
from employees
inner join orders on employees.name = orders.employeeName

But better if you use employee ID rather than his name for joining tables.

|||Thank you.

Help with LinkTarget

I am trying to access the Employee Summary Report in reporting services examples using URL access.

I want to open the sub-reports in this report to be opened in a different window.

Here is the url I am using

http://ReportServer/ReportService2005.asmx/?%2fAdventureWorks Sample Reports%2fEmployee Sales Summary&rs:Command=Render&rc:LinkTarget=_blank

I was assuming that when I click the sub-report it will open in a browser since the LinkTargete is to _blank.

Please let me know why this is not working .

Thanks!!

SqlNew

The LinkTarget device setting is for the links inside the report, e.g. for drilling down. Instead, consider a javascript code:

=void(window.open('http://localhost/ReportServer?/Adventure Works Sample Reports/Employee Sales Summary','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))"

|||

Hi,

thanks for the information. I trying to using this asp.net web application where i am building the url dynamically and passing it to a iframe. when i click on the report it opens in the iframe and when i click on the subreport i want subreport to open in a new browser.

thanks

SqlNew

|||

So the main report is loading in an iframe, correct? By subreport I assume you mean a drillthrough report which is displayed after the user clicks on a hyperlink on the main report, correct? If so, the LinkTarget should open up in a new window. Note that your URL link is wrong. It should be:

http://<machinename>/ReportServer?/AdventureWorks Sample Reports/Employee Sales Summary&rs:Command=Render&rc:LinkTarget=_blank

If still no new window, try the javascript approach.

|||

Hello Teo Lachev,

I corrected the URL still i am not able to open the subreport in a new window. I tried setting the src of the iframe with the javascript I get a page cannot be displayed error.

here is the src set to the iframe.

src='javascript:void(window.open('http://<machinename>/ReportServer/ReportService2005.asmx/?%2fAdventureWorks Sample Reports%2fTerritory Sales Drilldown&rs:Command=Render&rc:LinkTarget=_blank','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))'

this is set in a method is side the code of asp,net page as follows where GETURL is the method which builds the url.

String ifrDis= "";

ifrDis += "<iframe width=100% height=800px frameborder=0 scrolling=no id='if_displayreport' marginwidth=\"0\" marginheight=\"0\" vspace=\"0\" hspace=\"0\" style=\"overflow=hidden;\" src='http://pics.10026.com/?src= ";

ifrDis += void(window.open('" + GETURL() + "','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))' ";

ifrDis += " runat=server/>";

disArea.InnerHtml = ifrDis;

Please let me know if i am missing something.

Thanks!!

SqlNew

|||

Again, the URL is wrong. Should be

http://localhost/ReportServer?/Adventure Works Sample Reports/Employee Sales Summary

Open up IE and make sure that the URL works.

|||

hello,

I think I typed the URL wrong, I am able to open the report from IE but this happens when i set the url for the iframe.

thanks!!

|||I have spent some time trying to figure out what was wrong, and basically it would appear that, if your iFrame has ANY style information (through the style attribute or stylesheet) then IE will NOT obey the LinkTarget.

My solution is place the iFrame within a DIV tag if you need to apply any style information.

It is absolutely shocking that something like this breaks it, but that's the IE we know and love/hate.

Help with LinkTarget

I am trying to access the Employee Summary Report in reporting services examples using URL access.

I want to open the sub-reports in this report to be opened in a different window.

Here is the url I am using

http://ReportServer/ReportService2005.asmx/?%2fAdventureWorks Sample Reports%2fEmployee Sales Summary&rs:Command=Render&rc:LinkTarget=_blank

I was assuming that when I click the sub-report it will open in a browser since the LinkTargete is to _blank.

Please let me know why this is not working .

Thanks!!

SqlNew

The LinkTarget device setting is for the links inside the report, e.g. for drilling down. Instead, consider a javascript code:

=void(window.open('http://localhost/ReportServer?/Adventure Works Sample Reports/Employee Sales Summary','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))"

|||

Hi,

thanks for the information. I trying to using this asp.net web application where i am building the url dynamically and passing it to a iframe. when i click on the report it opens in the iframe and when i click on the subreport i want subreport to open in a new browser.

thanks

SqlNew

|||

So the main report is loading in an iframe, correct? By subreport I assume you mean a drillthrough report which is displayed after the user clicks on a hyperlink on the main report, correct? If so, the LinkTarget should open up in a new window. Note that your URL link is wrong. It should be:

http://<machinename>/ReportServer?/AdventureWorks Sample Reports/Employee Sales Summary&rs:Command=Render&rc:LinkTarget=_blank

If still no new window, try the javascript approach.

|||

Hello Teo Lachev,

I corrected the URL still i am not able to open the subreport in a new window. I tried setting the src of the iframe with the javascript I get a page cannot be displayed error.

here is the src set to the iframe.

src='javascript:void(window.open('http://<machinename>/ReportServer/ReportService2005.asmx/?%2fAdventureWorks Sample Reports%2fTerritory Sales Drilldown&rs:Command=Render&rc:LinkTarget=_blank','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))'

this is set in a method is side the code of asp,net page as follows where GETURL is the method which builds the url.

String ifrDis= "";

ifrDis += "<iframe width=100% height=800px frameborder=0 scrolling=no id='if_displayreport' marginwidth=\"0\" marginheight=\"0\" vspace=\"0\" hspace=\"0\" style=\"overflow=hidden;\" src='http://pics.10026.com/?src= ";

ifrDis += void(window.open('" + GETURL() + "','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))' ";

ifrDis += " runat=server/>";

disArea.InnerHtml = ifrDis;

Please let me know if i am missing something.

Thanks!!

SqlNew

|||

Again, the URL is wrong. Should be

http://localhost/ReportServer?/Adventure Works Sample Reports/Employee Sales Summary

Open up IE and make sure that the URL works.

|||

hello,

I think I typed the URL wrong, I am able to open the report from IE but this happens when i set the url for the iframe.

thanks!!

|||I have spent some time trying to figure out what was wrong, and basically it would appear that, if your iFrame has ANY style information (through the style attribute or stylesheet) then IE will NOT obey the LinkTarget.

My solution is place the iFrame within a DIV tag if you need to apply any style information.

It is absolutely shocking that something like this breaks it, but that's the IE we know and love/hate.

Help with LinkTarget

I am trying to access the Employee Summary Report in reporting services examples using URL access.

I want to open the sub-reports in this report to be opened in a different window.

Here is the url I am using

http://ReportServer/ReportService2005.asmx/?%2fAdventureWorks Sample Reports%2fEmployee Sales Summary&rs:Command=Render&rc:LinkTarget=_blank

I was assuming that when I click the sub-report it will open in a browser since the LinkTargete is to _blank.

Please let me know why this is not working .

Thanks!!

SqlNew

The LinkTarget device setting is for the links inside the report, e.g. for drilling down. Instead, consider a javascript code:

=void(window.open('http://localhost/ReportServer?/Adventure Works Sample Reports/Employee Sales Summary','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))"

|||

Hi,

thanks for the information. I trying to using this asp.net web application where i am building the url dynamically and passing it to a iframe. when i click on the report it opens in the iframe and when i click on the subreport i want subreport to open in a new browser.

thanks

SqlNew

|||

So the main report is loading in an iframe, correct? By subreport I assume you mean a drillthrough report which is displayed after the user clicks on a hyperlink on the main report, correct? If so, the LinkTarget should open up in a new window. Note that your URL link is wrong. It should be:

http://<machinename>/ReportServer?/AdventureWorks Sample Reports/Employee Sales Summary&rs:Command=Render&rc:LinkTarget=_blank

If still no new window, try the javascript approach.

|||

Hello Teo Lachev,

I corrected the URL still i am not able to open the subreport in a new window. I tried setting the src of the iframe with the javascript I get a page cannot be displayed error.

here is the src set to the iframe.

src='javascript:void(window.open('http://<machinename>/ReportServer/ReportService2005.asmx/?%2fAdventureWorks Sample Reports%2fTerritory Sales Drilldown&rs:Command=Render&rc:LinkTarget=_blank','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))'

this is set in a method is side the code of asp,net page as follows where GETURL is the method which builds the url.

String ifrDis= "";

ifrDis += "<iframe width=100% height=800px frameborder=0 scrolling=no id='if_displayreport' marginwidth=\"0\" marginheight=\"0\" vspace=\"0\" hspace=\"0\" style=\"overflow=hidden;\" src='http://pics.10026.com/?src= ";

ifrDis += void(window.open('" + GETURL() + "','_blank', 'location=no,toolbar=no,left=100,top=100,height=600,width=800'))' ";

ifrDis += " runat=server/>";

disArea.InnerHtml = ifrDis;

Please let me know if i am missing something.

Thanks!!

SqlNew

|||

Again, the URL is wrong. Should be

http://localhost/ReportServer?/Adventure Works Sample Reports/Employee Sales Summary

Open up IE and make sure that the URL works.

|||

hello,

I think I typed the URL wrong, I am able to open the report from IE but this happens when i set the url for the iframe.

thanks!!

|||I have spent some time trying to figure out what was wrong, and basically it would appear that, if your iFrame has ANY style information (through the style attribute or stylesheet) then IE will NOT obey the LinkTarget.

My solution is place the iFrame within a DIV tag if you need to apply any style information.

It is absolutely shocking that something like this breaks it, but that's the IE we know and love/hate.