how to get this out put

Hi i have a table call
EmpTran looks like this
EmpId StDate TranID
1 NULL 124
1 01/01/2007 145
2 01/02/2004 147
3 NULL 7844

i have some condition to apply this table like
i need to get all EmpId where StDate is NULL
But problem is if EmpId get repeated more than onece then i need to check if any of that record has got any StDate if it is then i need to exclude that every record
EmpId StDate TranID
1 NULL 24 -should not include cos same EmpId got StDate in the second row
1 01/01/2007 45 -should not include cos this EmpId got StDate
2 NULL 147 -should include cos StDate IS NULL
2 NULL 187 -should include cos StDate IS NULL
3 NULL 7844 -should include cos he got one row and StDate IS NUL

so once the conditions satisfy output looks like this
EmpId StDate TranID
2 NULL 147
2 NULL 187
3 NULL 7844

Any Idea ?
regards
suis

Maybe something like:

Code Snippet

declare @.mockup table
( EmpId integer,
StDate datetime,
TranID integer
)

insert into @.mockup
select 1, null, 124 union all
select 1, '1/1/7', 145 union all
select 2, null, 147 union all
select 2, null, 187 union all
select 3, null, 7844
--select * from @.mockup

select * from @.mockup a
where StDate is null
and not exists
( select 0 from @.mockup b
where a.EmpId = b.EmpId
and StDate is not null
)

/*

EmpId StDate TranID
-- - --
2 NULL 147
2 NULL 187
3 NULL 7844
*/

|||Hi Kent,
That's a great SQL ,
Thanks very much for u r fast response,still i am in a SQL learing period,
I think in the future i might be able to write my own complex SQL without any problems.
This is a nice forum to learn and sort out problems.

many thanks to Microsoft team,
best regards
suis|||

This should get you your desired output:

Code Snippet

SET NOCOUNT ON

DECLARE @.EmpTran table
( EmpId int,
StDate datetime,
TranID int
)

INSERT INTO @.EmpTran VALUES ( 1, NULL, 24 )
INSERT INTO @.EmpTran VALUES ( 1, '01/01/2007', 45 )
INSERT INTO @.EmpTran VALUES ( 2, NULL, 147 )
INSERT INTO @.EmpTran VALUES ( 2, NULL, 187 )
INSERT INTO @.EmpTran VALUES ( 3, NULL, 7844 )

SELECT *
FROM @.EmpTran
WHERE EmpID IN (SELECT EmpID
FROM @.EmpTran
GROUP BY EmpID
HAVING max( isnull( StDate, 0 ) ) = 0
)

EmpId StDate TranID
-- --
2 NULL 147
2 NULL 187
3 NULL 7844

how to get this out put

hi all

this is my quary

SELECT RFVDM_CODE,RFVAL_REFNO,DESCRIPTION
FROM REFERENCEVALUES
GROUP BY RFVDM_CODE,RFVAL_REFNO,DESCRIPTION
ORDER BY RFVDM_CODE

this quary provde a out put like

RFVDM_CODE RFVAL_REFNO DESCRIPTION
ACTDF 2004485 Caseload
ACTDF 2004486 Assessment
ACTDF 2004487 Outpatient appointment
ACTDF 2004488 Contact
ACTDF 2004620 Not Specified
ACTIV 7 Not Specified
ACTIV 217 Book Outpatient Appointment.
ACTPE 8 Not Specified
ACTPE 221 Contact
ACTPE 222 Care Plan Review

i need to get this out put like this

RFVDM_CODE RFVAL_REFNO DESCRIPTION
ACTDF 2004485 Caseload

2004486 Assessment

2004487 Outpatient appointment

2004488 Contact

2004620 Not Specified

ACTIV 7 Not Specified

217 Book Outpatient Appointment.

ACTPE 8 Not Specified

221 Contact

222 Care Plan Review

can i do this

any idea

thanx

its not possible in any single statement. Yes you can do it in SQL Server but not advisable. These kind of formating should be the responsiblity of FE. Do it in FE and its much more flexible.

Madhu

|||

Hi,

Try this one:

select case num when 1 then RFVDM_CODE else '' end RFVDM_CODE, RFVAL_REFNO, DESCRIPTION

from

(select *,

ROW_NUMBER() OVER(Partition By RFVDM_CODE ORDER BY RFVDM_CODE asc) num

from ReferenceValues) s

Although i agree that it should be done on the front end, becuase it's a user layout thing, this table actually has a diferent meaning than the one with the repeated values.

Regards,

Gert-Jan

|||

Gert solution is possible only in SQL Server 2005. mind it.. Thought there are method ,i strongly feel that this should be handled at the FE.

Madhu

|||

Hi thank you.

that is working finely. if there any duplicate values then how we sort out that from this quary.

regards
Nirangasql

How to get this out put

Hi i have a small question

when i use this SQL bellow

select EmpId,Type,Description from Employee
it will return me this out put

EmpId Type Description
01 Private Private company owner
02 Self Home based
03 Self Home based

Now my requirement is to get this out put

EmpId Type Description
01 Private
02 Self Home based
03 Self Home based

i need to get the description of the employee when employee type only 'Self'
rest of the employee Type should leave it blank ?
how do i do this task ?

regards
suis

Try the example below.

Chris

Code Snippet

DECLARE @.MyTable TABLE

(

[EmpID] CHAR(2),

[Type] CHAR(10),

[Description] VARCHAR(50)

)

INSERT INTO @.MyTable([EmpID], [Type], [Description])

SELECT '01', 'Private', 'Private company owner' UNION ALL

SELECT '02', 'Self', 'Home based' UNION ALL

SELECT '03', 'Self', 'Home based'

SELECT [EmpID],

[Type],

CASE WHEN [Type] = 'Self' THEN [Description]

ELSE ''

END AS [Description]

FROM @.MyTable

|||Hi chris
thank you very much for u r quick response,
i could manage to sort out my problem using u r comments,
thank you very much again for this forum
regards
suis

how to get this into a table?

Hi all,

Can any body please suggest me how to get the result from the following procedure into a table.

sp_help_job @.Execution_status=1

I just cannot get the result from this procedure into a table

Thanking in advance

Jacx

To get the o/p of a sp to a table the table should already be existing. The output columns should match the table structure. if the table exists

then

Insert into Tablename Exec yoursp

Madhu

|||

i will give u a simple solution. now ifu see the o./p of sp_help_job @.Execution_status=1 then u can see that there are many column. So the simple method is , first findout the script for sp_Hlep_job. for that run sp_helptext sp_help_job.When u run this query, u will get the source code of sp_help_job, and u can see the data type or you can make a wrapper which will automatically create a table for u

Madhu

|||

this is the script for the table i created for geting the results

CREATE TABLE [Tbl_JobDET] (
[Job_id] [uniqueidentifier] NULL ,
[Originating_server] [varchar] (255) NULL ,
[Name] [varchar] (255) NULL ,
[enabled] [int] NULL ,
[description] [varchar] (255) NULL ,
[start_step_id] [int] NULL ,
[Category] [varchar] (255) NULL ,
[owner] [varchar] (255) NULL ,
[notify_level_email] [int] NULL ,
[notify_level_netsend] [int] NULL ,
[notify_level_page] [int] NULL ,
[notify_email_operator] [int] NULL ,
[notify_netsend_operator] [int] NULL ,
[delete_level] [int] NULL ,
[date_created] [datetime] NULL ,
[date_modified] [datetime] NULL ,
[Version_number] [int] NULL ,
[last_run_date] [int] NULL ,
[last_run_time] [int] NULL ,
[last_run_outcome] [int] NULL ,
[next_run_date] [int] NULL ,
[next_run_time] [int] NULL ,
[next_run_schedule_id] [int] NULL ,
[current_execution_status] [int] NULL ,
[current_execution_step] [int] NULL ,
[current_retry_attempt] [int] NULL ,
[has_step] [int] NULL ,
[hsa_schedule] [int] NULL ,
[has_target] [int] NULL ,
[type] [int] NULL
) ON [PRIMARY]
GO

and i tried geting the results using this

insert tbl_jobDET
EXEC sp_help_job @.execution_status=1

and this is the Error i got

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

Any suggestions on how can i resolve this error and get the result into the table ?

|||

Jacx,

A possible solution could be creating a linked server pointing to itself (loopback) and using:

select *

into #t

from openquery(Loopback, 'set fmtonly off; exec msdb.dbo.sp_help_job @.execution_status = 1')

go

select *

from #t

go

drop table #t

go

AMB

|||Jacx,

I was looking for the same, and came across this page:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=259078

It contains this suggestion, which works great:

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @.execution_status=4')

You may have to change your connection string or sp_help_job parameters...

how to get this into a table?

Hi all,

Can any body please suggest me how to get the result from the following procedure into a table.

sp_help_job @.Execution_status=1

I just cannot get the result from this procedure into a table

Thanking in advance

Jacx

To get the o/p of a sp to a table the table should already be existing. The output columns should match the table structure. if the table exists

then

Insert into Tablename Exec yoursp

Madhu

|||

i will give u a simple solution. now ifu see the o./p of sp_help_job @.Execution_status=1 then u can see that there are many column. So the simple method is , first findout the script for sp_Hlep_job. for that run sp_helptext sp_help_job.When u run this query, u will get the source code of sp_help_job, and u can see the data type or you can make a wrapper which will automatically create a table for u

Madhu

|||

this is the script for the table i created for geting the results

CREATE TABLE [Tbl_JobDET] (
[Job_id] [uniqueidentifier] NULL ,
[Originating_server] [varchar] (255) NULL ,
[Name] [varchar] (255) NULL ,
[enabled] [int] NULL ,
[description] [varchar] (255) NULL ,
[start_step_id] [int] NULL ,
[Category] [varchar] (255) NULL ,
[owner] [varchar] (255) NULL ,
[notify_level_email] [int] NULL ,
[notify_level_netsend] [int] NULL ,
[notify_level_page] [int] NULL ,
[notify_email_operator] [int] NULL ,
[notify_netsend_operator] [int] NULL ,
[delete_level] [int] NULL ,
[date_created] [datetime] NULL ,
[date_modified] [datetime] NULL ,
[Version_number] [int] NULL ,
[last_run_date] [int] NULL ,
[last_run_time] [int] NULL ,
[last_run_outcome] [int] NULL ,
[next_run_date] [int] NULL ,
[next_run_time] [int] NULL ,
[next_run_schedule_id] [int] NULL ,
[current_execution_status] [int] NULL ,
[current_execution_step] [int] NULL ,
[current_retry_attempt] [int] NULL ,
[has_step] [int] NULL ,
[hsa_schedule] [int] NULL ,
[has_target] [int] NULL ,
[type] [int] NULL
) ON [PRIMARY]
GO

and i tried geting the results using this

insert tbl_jobDET
EXEC sp_help_job @.execution_status=1

and this is the Error i got

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

Any suggestions on how can i resolve this error and get the result into the table ?

|||

Jacx,

A possible solution could be creating a linked server pointing to itself (loopback) and using:

select *

into #t

from openquery(Loopback, 'set fmtonly off; exec msdb.dbo.sp_help_job @.execution_status = 1')

go

select *

from #t

go

drop table #t

go

AMB

|||Jacx,

I was looking for the same, and came across this page:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=259078

It contains this suggestion, which works great:

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @.execution_status=4')

You may have to change your connection string or sp_help_job parameters...

How to get the Windows login name?

How to get the Windows login name inside SQL Server 2005 when the SQL Server
2005 is accessed thru terminal service? Cannot use .Net.Additional Information:
SQL Server 2005 is connected using SQL Authentication.
"Peter" wrote:

> How to get the Windows login name inside SQL Server 2005 when the SQL Serv
er
> 2005 is accessed thru terminal service? Cannot use .Net.|||You can't. SQL Server only knows the user as the SQL login.
-Sue
On Thu, 8 Mar 2007 16:52:03 -0800, Peter
<Peter@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Additional Information:
>SQL Server 2005 is connected using SQL Authentication.
>
>
>"Peter" wrote:
>|||Peter,
What client protocol are you using?
Chris
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:6442C4B1-5DC4-41F9-BCA6-410A3D95F50B@.microsoft.com...[vbcol=seagreen]
> Additional Information:
> SQL Server 2005 is connected using SQL Authentication.
>
>
> "Peter" wrote:
>

How to get the windows current user currently logged from SQL Server

Hi I am using exec master..xp_cmdshell "ECHO %USERNAME%" to get the windows
user name currently logged in. But it gives me the NULL value. I even tried
exec master..xp_cmdshell "ECHO SET %USERNAME%" but it doesnt help me.
Does any body have idea how to get the windows user name who is currently lo
gged from SQL Server.
Thanks for you help.
HemanthTry suser_sname()
Ray Higdon MCSE, MCDBA, CCNA
--
"Hemanth" <kamishetty@.hotmail.com> wrote in message
news:8843EE8B-D217-4DA1-A47F-D7D19804C819@.microsoft.com...
> Hi I am using exec master..xp_cmdshell "ECHO %USERNAME%" to get the
windows user name currently logged in. But it gives me the NULL value. I
even tried exec master..xp_cmdshell "ECHO SET %USERNAME%" but it doesnt
help me.
> Does any body have idea how to get the windows user name who is currently
logged from SQL Server.
> Thanks for you help.
> Hemanth
>sql