Comments by Member 11337367 (Top 196 by date)

both are not giving output..
WHERE
gsk.c_c2code = '00S204'
AND gsk_stk.lcc2code = '003004'
AND invrcvd.d_ldate >= '2019-08-29 00:00:00'
AND invrcvd.d_ldate <= '2019-08-29 24:59:59'
order by gsk_stk.lcc2code for this way its giving result

first i tried without + sign still it did not come then used this symbol

first i tried without + sign still it did not come

from that link only i generated this script but still failing to execute the script.can you pls elaborate the issue?so that ill know where exactly it went wrong?

thank you

yes my index name is index only

If i add date_format query keeps running only.it will not give result before execution stops by mysql sever

yeah actually query is running only not giving result only after adding second condition in where clause.index has got created on usc.c_stockiest_item_code column so why its not giving result

means query has not executed its in running mode

Total task=number of processor + 2.is it correct?

Thank for your solution. I have a question on point1 'SQL server are healthy' is this point about processor. Running task is dependent on number of processor being installed on machine?

Thanks for your reply.I have good experience in sql.i know the syntax of joins.Here my doubt is the programmer used parenthesis for single table like
(select * from table1) a
left outer join (select * from table2) ..

instead of writing like this

select * from table1 a
join table2 b...

yesh that's what I wanted, only non null values from all columns in a table

It should work for all type os scenarios
like any column or having non null values shoud come from queries

Suppose in this
select 5-'a'
select 4+3

if first statement thorws an error it should go to second statment

That person not there who created this script now..

I was given this scripts by somebody to go through the script. but am not able to understand this.

have ever worked on this scripts?

Can you tell me what is the difference between using volatile and stable .

Thanks for your answer..

Thanks for answering my question but i don't want to do in sql everything by ssis..Is it possible to do that?

Your command does't work..please see this structure

CREATE TABLE [dbo].[meghr](
[nos] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

Can you tell when we use #table and ##table in tsql?

It is not for home work just learn about things very clearly..

That question has been closed am not able able to see the answer now..please reopen that question.

I googled i did't get so posted my question here...

Can u give one example on this..?

i am getting syntax error..public function will work in sql?

I have 2012 sql server any fuction is there in sql 2012.?

thanks for your kind reply on this ..can u be more specific on this it would be helpful more

But starting "Status": "Success",
"Status Message": "Service Call Successful"
"chemist" this mesage will come know

But for @jsonchem am setting value know..that can't be null

No..in select statment returns null i want to display one mesaage like 'There is no data exist for this fscode'. like i want to display..is it possible..

thanks for the reply..
i want to display this message after this select @jsonchem ..how to write that?

That is past now what is the solution for that..

what is the use of having that command in sp?

directly i can use in trigger?

s.we have.

how to write command in comand prompt window?

I have updated my question..can now you look into my question..?

small doubt..61.16.165.200 this is not local network address u r telling?

I have just tried in cmd shell.I got same reply ie network path was not found

never tried in cmd sheell..where i have to give administrator privilage..

I have only database..sybase server is not installed in my system..In ssma it is asking server connection to migrate..So anyother solution is there..

select distinct code ,
STUFF((Select ','+days
from megh T1
where T1.code=T2.code
FOR XML PATH('')),1,1,'') from megh T2

Iam receving error for this

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.

How to alter this query i already written..

hii
if some column having null value is not coming
why is it like that

no..i have to include that column also..

if i want to add that column what i have to do?

can you tell me what you did for this column N_Visitorder when we gets error..

we can use varchar data type for that column..

both number and character ..

now u plese tell me how i can i add c_prefixTN into list..

Thank thats fine..i want to make this column c_prefixTN in the list
..

please take this


CREATE TABLE [dbo].[Tbl_ControlPanel_Mst](
[N_Srno] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[N_Visitorder] [int] NOT NULL,
[N_workedwithmcr] [int] NULL,
[N_MCLshow] [int] NOT NULL,
[N_MtpSbmMcr] [int] NOT NULL,
[N_MtpSbmDcr] [int] NOT NULL,
[N_MtpappMcr] [int] NOT NULL,
[N_MtpappDcr] [int] NOT NULL,
[N_Stpsubmitiondcr] [int] NULL,
[N_PriTargetOn] [int] NULL,
[N_Doctortime] [int] NULL,
[n_mscexpences] [int] NULL,
[N_SecTargeton] [int] NULL,
[N_mtpsubarea] [int] NOT NULL,
[c_prefixTN] [varchar](5) NULL,
[c_prefixGDN] [varchar](5) NULL,
[N_year] [int] NULL,
[N_secvalue] [int] NULL,
[N_Stpsubmitionmcr] [int] NULL,
[N_TargItemBrand] [int] NULL,
[n_closingday] [int] NULL,
[n_pri_inv_imp] [int] NULL,
[c_tacode] [varchar](50) NULL,
[c_dacode] [varchar](50) NULL,
[c_monthly] [varchar](10) NULL

this is the error..why u didn't metion n_srno column

Msg 8167, Level 16, State 1, Line 31
The type of column "c_tacode" conflicts with the type of other columns specified in the UNPIVOT list.

CREATE TABLE [dbo].[Tbl_ControlPanel_Mst](
[N_Srno] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[N_Visitorder] [int] NOT NULL,
[N_workedwithmcr] [int] NULL,
[N_MCLshow] [int] NOT NULL,
[N_MtpSbmMcr] [int] NOT NULL,
[N_MtpSbmDcr] [int] NOT NULL,
[N_MtpappMcr] [int] NOT NULL,
[N_MtpappDcr] [int] NOT NULL,
[N_Stpsubmitiondcr] [int] NULL,
[N_PriTargetOn] [int] NULL,
[N_Doctortime] [int] NULL,
[n_mscexpences] [int] NULL,
[N_SecTargeton] [int] NULL,
[N_mtpsubarea] [int] NOT NULL,
[c_prefixTN] [varchar](5) NULL,
[c_prefixGDN] [varchar](5) NULL,
[N_year] [int] NULL,
[N_secvalue] [int] NULL

where i have to write column datatype please tell me?

I didn't get the answer in that which i was looking for..so i have asked you..

can you show me to write for one column to convert to original column datatye..

please tell why it is being failed ..

I haven't asked how to take backup..i wanted how to truncate log file using cursor..

Hi.. please give me solution

I have checked pkey exist in tbl_sales table for 4 column..

that i have added dude..

when adding fkey iam getting error like below

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Tbl_Sales' that match the referencing column list in the foreign key 'FK_Tbl_Sales_Details_Tbl_Sals'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

how to re add fkey once drop from table..

please update solution for my question...

but not able to reset fkeys after droping from table
following error is showing..

alter table Tbl_Sales_Details drop constraint FK_Tbl_Sales_Details_Tbl_Sales
alter table Tbl_Sales_Details add constraint FK_Tbl_Sales_Details_Tbl_Sales foreign key(C_DEP_CODE) references Tbl_Sales(C_DEP_CODE)
alter table Tbl_Sales_Details add constraint FK_Tbl_Sales_Details_Tbl_Sales foreign key(c_year) references Tbl_Sales(c_year)
alter table Tbl_Sales_Details add constraint FK_Tbl_Sales_Details_Tbl_Sales foreign key(c_prefix) references Tbl_Sales(c_prefix)
alter table Tbl_Sales_Details add constraint FK_Tbl_Sales_Details_Tbl_Sales foreign key(N_SRNO) references Tbl_Sales(N_SRNO)

error is likt in below

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Tbl_Sales' that match the referencing column list in the foreign key 'FK_Tbl_Sales_Details_Tbl_Sales'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

I have dropped pkey but not leaving to reset it again for the same table..some error statement showing that duplicate value is exist..

error is like below
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Tbl_Sales' and the index name 'PK__Tbl_Sale__361A64DB1B10F934'. The duplicate key value is (DEP0001, 2015, TS-SRT-VS-, 32).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.




please solve this..

one more i want that month under that i nee those values like in below format

empcode april,may,june
111 1,2 3,4 5,6
112 7,8 10 21,24
like this if it is possible pls help me out

doc fscode may april june july august
C22175 FSO102 NULL NULL NULL 14,21,3,30 NULL
C22177 FSO102 NULL NULL NULL 11,18,4 NULL
C22179 FSO102 NULL NULL NULL NULL 17,19
C22182 FSO102 13,29 NULL NULL NULL NULL
C22183 FSO102 20,27 NULL NULL NULL NULL
C22187 FSO102 NULL NULL NULL NULL 10,21,3
C22188 FSO102 NULL 3 NULL NULL NULL

Can u pls explain your query since iam not able to understand that..what is the use of stuff and for xml u defined..pls know me what it does?

please give query to make that..

adm001 is code and it has to repeate no of day in the month i.e august 01 to august 20.
same another code adm002 has to repeate 20 times
etc

it is not working..:(

I have to remove like this..for ex:
befor
set @tempheader=@filepath+'header.txt'
set @tempdetail=@filepath+'detail.txt'

after
set @tempheader=@filepath+header.txt
set @tempdetail=@filepath+detail.txt

One more is there...

DECLARE @databasename nvarchar(200)
DECLARE @sql nvarchar(max)
DECLARE curDatabases CURSOR FOR
SELECT name
FROM sys.databases
WHERE owner_sid <> 1; -- use relevant condition to include only desired databases

OPEN curDatabases

FETCH NEXT FROM curDatabases INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @databasename
SET @sql = 'use ' + @databasename + '
exec (''alter proc getmonthwiseusercount
@year varchar(50),
@month varchar(50),
@filepath varchar(56)
as
begin
declare @sql varchar(max)
declare @ch char(2)
set @ch=CHAR(13)+CHAR(10)
declare @header varchar(max)
declare @tempheader varchar(500)
declare @tempdetail varchar(500)

set @tempheader=@filepath+'header.txt'
set @tempdetail=@filepath+'detail.txt'

set @header='select ''''empcode'''' as empcode,''''fsname'''' as fsname,
''''ntype'''' as ntype,''''desg'''' as desg,''''userid'''' as userid,''''dcr_count'''' dcrcount
'
set @header='exec master..xp_cmdshell ''bcp "'+@header+'" queryout "'+@tempheader+'" -S -d apex_gmc -T -c '''
set @header=REPLACE(@header,@ch,'')
exec (@header)


set @sql='select f.C_EmpNo as EmpCode,f.C_Name as FSName,ua.N_Type as NType,ua.c_sh_name as Desg,
ui.C_UserID as UserID,isnull(dwr.dcrcount,0) as DCR_Count
from
Tbl_FS_Mst f
join Tbl_User_Access ua on f.N_Type=ua.N_Type
join Tbl_User_Info ui on f.C_EmpNo=ui.C_Code
left join
(
select c_fs_code,COUNT(distinct n_srno) as dcrcount
from tbl_dwr d
where MONTH(d.D_Date_Report)='+convert(varchar,@month)+' and YEAR(d.D_Date_Report)='+convert(varchar,@year)+'
group by c_fs_code
)dwr on dwr.C_FS_Code=f.C_Code
where f.C_EmpNo<>''''000000'''' or dwr.dcrcount is not null

order by f.C_EmpNo,f.N_Type,f.C_Name
'

set @sql='master..xp_cmdshell ''bcp "'+ @sql +'" queryout "'+@tempdetail+'" -S -d apex_gmc -T -c'''
set @sql=REPLACE(@sql,@ch,' ')
exec(@sql)

declare @mergedata varchar(max)
declare @filename varchar(500)
set @filename='D:apexusercount.xls'

set @mergedata='master..xp_cmdshell ''copy '+@tempheader+' + '+@tempdetail+' '+@filename+''''

exec (@mergedata)

declare @delfiles varchar(500)

set @delfiles='exec master..xp_cmdshell ''del '+@tempheader+''''

exec (@delfiles)

set @delfiles='exec master..xp_cmdshell ''del '+@tempdetail+''''

exec (@delfiles)
end


end''
)'
EXEC sp_executesql @stmt = @sql

FETCH NEXT FROM curDatabases INTO @databasename
END
CLOSE curDatabases;
DEALLOCATE curDatabases;

ia getting error for this

error is lik in below

Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'header'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '''.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'bcp'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near '000000'.
Msg 102, Level 15, State 1, Line 71
Incorrect syntax near 'del'.
Msg 102, Level 15, State 1, Line 75
Incorrect syntax near 'del'.

I have two doubt
please u tell me what is the use of sp_executesql command and how u could take @stmt without declaring them.

How to write stored procedure in double quote..

if i want to create in multiple database how..?

i dont know specific word for that..when writing select *from tbl_ here in window table name coming na..that is not coming in my systam..why

like that so many values are there more than thousands values please tell me to update in one shot..

i have posted new question but still not yet answered..pls help me out

Copy direction must be either 'in', 'out' or 'format'.
usage: BCP {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
[-d database name]
NULL

this error is showing

no different error..
Msg 3159, Level 16, State 1, Line 2
The tail of the log for the database "z" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

No...still error is same..

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'E:\z.bck'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

where i have to give path?

I first executed in tbl_sales but same error throwing..there is no solution for this issue..

error for this statment..
Msg 201, Level 16, State 4, Procedure sp_MSforeachdb, Line 0
Procedure or function 'sp_MSforeachdb' expects parameter '@command1', which was not supplied.
Msg 911, Level 16, State 1, Line 2
Database '?' does not exist. Make sure that the name is entered correctly.
Msg 102, Level 15, State 1, Procedure parentupdate, Line 13
Incorrect syntax near 'N_ptype'.
Msg 156, Level 15, State 1, Procedure parentupdate, Line 13
Incorrect syntax near the keyword 'where'.

your point is corect in my case both column having minimum size not exceeding the data size value..

there is no values there like that in the table..

is there query analyzer exist in ms sql?

Please help me out how to analyze my query..

Could you explain me how to execute in sql profiler beacuse i never tried this thing earlier.

exec master.sys.sp_MSforeachdb
'
use [?];
if exists
begin
create proc toupdateuserlimit
@totaluser int,
@msgcode varchar(10)

as
begin

update tbl_controlpanel_mst set n_webuserLimit=@totaluser+(select max(n_webuserLimit) from tbl_controlpanel_mst)

if @@ERROR<>0
begin
set @msgcode=''9012''
end
else
begin
set @msgcode=''90012''
end
end
end';


when executing this query ian getting error like in below..
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'begin'.
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@totaluser".
Msg 137, Level 15, State 1, Line 15
Must declare the scalar variable "@msgcode".
Msg 137, Level 15, State 1, Line 19
Must declare the scalar variable "@msgcode".
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'begin'.
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@totaluser".
Msg 137, Level 15, State 1, Line 15
Must declare the scalar variable "@msgcode".
Msg 137, Level 15, State 1, Line 19
Must declare the scalar variable "@msgcode".
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'begin'.
Msg 137, Level 15, State 2, Line 12

I removed that.still same error is existing.

This query for execute one procedure in all databases.So what i can do for corect the query..?

I want to change database name each time but i dont want to do like that ..it should return all disabled trigger from all database..

I want to check in all database in one shot..

update sal set invoice=prefix+srno
but this is giving error...

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

i have just given two name ,but it is there for so many people

here subto column code and sub from column code should not to come for more than one times..

name srno rowid subfrom subto
ash 1 1 sa01 sa02
ash 1 2 sa01 sa03
ash 1 3 sa01 sa43
ash 1 4 sa01 sa02
ash 1 5 sa01 sa07
ash 1 6 sa01 sa02
raj 1 1 sa02 sa17
raj 1 2 sa02 sa89
raj 1 3 sa02 sa17
raj 1 4 sa02 sa02
raj 1 5 sa02 sa90
raj 1 6 sa02 sa89

to whom you are asking..?

k.i did like that..after executing the sp iam getting error like below
Msg 911, Level 16, State 1, Line 1
Database 'H' does not exist. Make sure that the name is entered correctly.

while executing error...
Could not find stored procedure 'usp_FindTableNameInAllDatabase'.

Could you tell me why have been used sys.partitions and sys.schema...?

but count coming wrong..it should be coming according to dbname right..?

databasename usercount
SYMPHONY 1055
wingsfmcg 1055
AquaLife 1055
BioVitamin 1055

k..this is my qeury
create table #tempcount (tablename nvarchar(128), record_count bigint)
EXEC sp_msforeachdb
'insert #tempcount select ''?'' , count(*) from tbl_user_info'
select * from #tempcount
drop table #tempcount

here iam getting same count for all databases..but it is not coming database wise...i want for each database and that table count

In your link am not getting ..It can be possible to use like in my queries?

sa057524
sa057525
sa057526
sa057527
sa057528
sa057529
sa0575210
sa0575211
sa0575212

but number coming like this..it is wrong..

Msg 243, Level 16, State 1, Line 1
Type NVARACHAR is not a defined system type.
error for ur query

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

please anyone can show me how to write soundex using like ...

could you correct my queries..am getting error for that like below
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

could you show me how to write that..

yes

Can you show me how to write down..

It will give only count of max number of times repeated vales..I want value of that

Ya,I need something else that which is maximum time repeated values in column cr,leave columns

In my system Microsoft.Jet.OLEDB.4.0 not been installed.

CREATE TABLE [dbo].[tbl_item_rate](
[c_Item_code] [varchar](10) NOT NULL,
[n_rate] [numeric](18, 2) NULL,
[n_mrp] [numeric](18, 2) NULL,
[n_std_rate] [numeric](18, 2) NULL,
[n_pts_rate] [numeric](18, 2) NULL,
[d_date_from] [datetime] NOT NULL,
[d_date_to] [datetime] NULL,
CONSTRAINT [PK_tbl_item_rate] PRIMARY KEY CLUSTERED
(
[c_Item_code] ASC,
[d_date_from] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[r2](
[itemcode] [varchar](50) NULL,
[itemname] [varchar](50) NULL,
[shortname] [varchar](50) NULL,
[itemtype] [float] NULL,
[companyname] [varchar](50) NULL,
[description] [varchar](150) NULL,
[pack] [float] NULL,
[pricetoretailer] [float] NULL,
[mrp] [float] NULL,
[standardrate] [float] NULL,
[pricetostockiest] [float] NULL,
[brandcode] [nvarchar](255) NULL,
[categorycode] [nvarchar](255) NULL,
[divisioncode] [varchar](50) NULL,
[groupcode] [nvarchar](255) NULL,
[nrvrate] [nvarchar](255) NULL,
[manufacturercode] [nvarchar](255) NULL,
[qtyperstrip] [nvarchar](255) NULL
) ON [PRIMARY]

I don't understand the error..Please tell that

I made notnull then also error getting..
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tbl_item_rate' and the index name 'PK_tbl_item_rate'. The duplicate key value is (038512076).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

I have dropped primary key but not able to add again due to the error am getting..

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'tbl_item_rate'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

CREATE TABLE [dbo].[tbl_item_rate](
[c_Item_code] [varchar](10) NOT NULL,
[n_rate] [numeric](18, 2) NULL,
[n_mrp] [numeric](18, 2) NULL,
[n_std_rate] [numeric](18, 2) NULL,
[n_pts_rate] [numeric](18, 2) NULL,
[d_date_from] [datetime] NOT NULL,
[d_date_to] [datetime] NULL,
CONSTRAINT [PK_tbl_item_rate] PRIMARY KEY CLUSTERED
(
[c_Item_code] ASC,
[d_date_from] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

alter table tbl_item_rate alter column c_item_code varchar(25)
this query am executing but error throwing..

error id that
Msg 5074, Level 16, State 1, Line 1
The object 'PK_tbl_item_rate' is dependent on column 'c_item_code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN c_item_code failed because one or more objects access this column.

programitcally.

same result no changes

This is the correct answer for my problem..thank you ..

In tbl_fs_mst table have more than 1000 record with diffrent values..I want to insert select query to tbl_fs_mst

C_DSC_Code date
NULL 2015-03-17 00:00:00.000
D044633 2014-05-16 00:00:00.000
D34670 2013-10-29 00:00:00.000
D34687 2014-05-22 00:00:00.000
D34690 2014-11-26 00:00:00.000
D34712 2014-12-05 00:00:00.000
D34713 2015-01-20 00:00:00.000
D34716 2013-07-30 00:00:00.000
D34739 2014-06-10 00:00:00.000
D34741 2014-12-06 00:00:00.000
D34746 2014-12-01 00:00:00.000
D34747 2014-06-11 00:00:00.000
D34748 2014-04-04 00:00:00.000
D34749 2014-06-10 00:00:00.000
D34750 2014-06-19 00:00:00.000
D34755 2015-01-23 00:00:00.000
D34756 2014-02-14 00:00:00.000
D34757 2013-10-31 00:00:00.000
D34759 2014-04-23 00:00:00.000
D34763 2013-11-15 00:00:00.000
D34765 2014-06-03 00:00:00.000
D34766 2014-01-24 00:00:00.000
D34768 2013-10-24 00:00:00.000
D34769 2013-11-19 00:00:00.000
D34770 2014-05-08 00:00:00.000
D34772 2014-04-28 00:00:00.000
D34778 2013-11-20 00:00:00.000
D34779 2014-06-10 00:00:00.000
D34781 2013-11-26 00:00:00.000
D34782 2014-02-15 00:00:00.000
D34784 2014-02-17 00:00:00.000
D34785 2014-04-28 00:00:00.000
D34787 2015-01-08 00:00:00.000
D34796 2014-12-12 00:00:00.000
D34799 2014-12-12 00:00:00.000
D34801 2014-12-12 00:00:00.000
D34816 2014-08-20 00:00:00.000
D34817 2014-12-01 00:00:00.000
D34819 2013-11-12 00:00:00.000
D34822 2013-11-19 00:00:00.000
D34824 2015-01-07 00:00:00.000
D34825 2013-07-26 00:00:00.000
D34827 2015-01-23 00:00:00.000
D34834 2015-01-28 00:00:00.000
D34848 2015-02-16 00:00:00.000

This is the output we got..

No changes except only one column coming more with doctor code column as null...

there is no data in that table.I want to insert from maven database table to other db table..

could you tell whats wrong in my script..?

I am getting error while updating it is not updating too

there is no logic behind this i just given example for you..you take any number but it has to be coming according to doctor code

We have more that 2000 doctor code like that...

Table having more than 2000 records..We cant do the case condition for each doctor code.Is there any alternative solution for this issue..

I have removed last three update statement from the query..still result has not been changed..same error getting...
An error occurred while executing batch. Error message is: Error creating window handle.

Can you check and give solution for my query..

In my query what i have to change or how can i alter my query..

I have one table in that 4 column are there out of 4 columns only one column having two times repeated values..I want to delete duplicate value..how it is possible
consider Table A is there col1,col2,col3,col4
col1 will have two times values

I want to take out only 2 digit after point(.)..

No..I just want to know how your query is works..

Ya.That is updated..

Could you tell me how this query will works..what sub string command will l do..

I want to update that column .not only select

same error getting
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to bigint.

is there any qvery to import..

microsoft sql..

not my sql.it is sql

Microsoft.Jet.OLEDB.12.0.

It is microsoft sql.

we are connectig one server in that microsoft excel is not installed.

I want in excel

error throwing

first table i have extracted but am not able to get the dat in second table

see i have more than 1000 rows in each column how can i write select statment for 1000 times

Nithing i have started till now

database normalisation

there last three column is tbl_core_product and rest of that is tbl_cust_div table..can u find now?

Tbl_cust_div table Tbl_core_product table
c_cust_code c_div_code c_fs_code d_date c_grade d_date_to n_per C_ref_no d_modified C_Doc_Code c_Product_Code n_priority
D94113 DI0001 MSR007 00:00.0 G00004 NULL 0 49:40.2 D94113 BR00006 1
D94113 DI0001 MSR007 00:00.0 G00004 NULL 0 49:40.2 D94113 BR00044 2
D94113 DI0001 MSR007 00:00.0 G00004 NULL 0 49:40.2 D94113 BR00044 2
D94114 DI0001 MSR007 00:00.0 G00003 NULL 0 49:40.2 D94114 BR00044 3
D94114 DI0001 MSR007 00:00.0 G00003 NULL 0 49:40.2 D94114 BR00022 2
D94114 DI0001 MSR007 00:00.0 G00003 NULL 0 49:40.2 D94114 BR00022 2
D94114 DI0001 MSR007 00:00.0 G00003 NULL 0 49:40.2 D94114 BR00008 1
D94115 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94115 BR00007 1
D94115 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94115 BR00008 2
D94115 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94115 BR00008 2
D94116 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94116 BR00047 2
D94116 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94116 BR00008 1
D94117 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94117 BR00014 3
D94117 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94117 BR00054 2
D94117 DI0001 MSR007 00:00.0 G00001 NULL 0 49:40.2 D94117 BR00054 2
D94118 DI0001 MSR007 00:00.0 G00004 NULL 0 49:40.2 D94118 BR00008 1
D94118 DI0001 MSR007 00:00.0 G00004 NULL 0 49:40.2 D94118 BR00008 1

one prooblem i have already used join fuction to get that data which i have posted..now i how to write your query in between my prevoius query..my query is like this

select * from Tbl_Cust_Div cd
join tbl_core_product cp on cp.C_Doc_Code=cd.c_cust_code
where
cd.c_fs_code='MSR007' and cd.d_date_to is null

No thanks..

It is onlly one table how can i make table 1 and table 2

code items prority
D94335 BR00012 1
D94335 BR00012 1
D94336 BR00052 2
D94336 BR00052 2
D94337 BR00001 1
D94337 BR00001 1

Like this i want to extract the data

Ok.Just now i have posted one question did u see that?

code items prority
D94334 BR00004 1
D94335 BR00012 1
D94335 BR00049 2
D94335 BR00008 3
D94335 BR00047 4
D94336 I00001 4
D94336 BR00054 5
D94336 BR00052 2
D94336 BR00052 2
D94337 BR00001 1
D94337 BR00001 1

I want to get the details of doctor which having same item code multiple times..

this is my query
select ROW_NUMBER() over(order by emp ),* from m
order by emp,typ

see my first column it is coming wrong order..
rowno da emp typ pl psl
2 2015-01-23 J00483 2 10 10
3 2015-01-23 J00483 3 15 15
1 2015-01-23 J00483 4 128 128
5 2015-01-23 J00648 2 13 13
4 2015-01-23 J00648 3 7 7
6 2015-01-23 J00648 4 40 40
8 2015-01-23 J00649 2 10 10
9 2015-01-23 J00649 3 15 15
7 2015-01-23 J00649 4 180 180
11 2015-01-23 J00681 2 10 10
10 2015-01-23 J00681 3 15 15
12 2015-01-23 J00681 4 180 180

No thanks i got the answer for this issue..

k.select doctorcode,[1],[2],[3]
from
(
select c.doctorcode,it.C_Code CODE,ROW_NUMBER() over(partition by c.doctorcode order by it.c_code)rn from cor c
join Tbl_Item_Mst it on it.C_SH_Name=c.[Item 1]
where it.N_Type=9

)d
pivot
(
max(it.c_code)
for rn in ([1],[2],[3])
)piv;

iam getting error
like
The column prefix 'it' does not match with a table name or alias name used in the query.

In this format i want to fetch.
Doctor item1 item2 item3
------ ----- ---- ----

it is very urgent please help me out

I have one table in that there is one doctor column and doctors item columns are there.I want to find how many items is there for particular doctor..
doccode itemcode
D90104 BR00039
D90105 BR00035
D90106 BR00003
D90106 BR00032
D90106 BR00033
D90107 BR00034
D90108 BR00041
D90110 BR00031
D90111 BR00042
D90112 BR00003
D90112 BR00032
D90112 BR00033
D90113 BR00038
D90114 BR00017
D90114 BR00036
D90117 BR00034
D90118 BR00003
D90118 BR00032
D90118 BR00033
D90120 BR00039
D90121 BR00031
D90122 BR00003
D90122 BR00032
D90122 BR00033
D90124 BR00042
D90125 BR00035
D90126 BR00038
D90128 BR00041
D90130 BR00039
D90131 BR00035
D90132 BR00003
D90132 BR00032
D90132 BR00033
D90133 BR00034
D90134 BR00041
D90135 BR00038
D90136 BR00041
D90137 BR00042
D90138 BR00003
D90138 BR00032
D90138 BR00033
D90139 BR00038
D90140 BR00008
D90143 BR00041
D90144 BR00038
D90145 BR00041
D90146 BR00042
D90147 BR00003
D90147 BR00032
D90147 BR00033
D90148 BR00038
D90149 BR00008
D90151 BR00039
D90152 BR00035
D90153 BR00003
D90153 BR00032
D90153 BR00033
D90154 BR00034
D90155 BR00041
D90156 BR00038
D90157 BR00041
D90158 BR00042
D90159 BR00041
D90160 BR00041
D90161 BR00034
D90162 BR00034
D90163 BR00034
D90164 BR00034
D90165 BR00041
D90169 BR00041
D90170 BR00042
D90171 BR00003
D90171 BR00032
D90171 BR00033
D90172 BR00038
D90173 BR00008
D90176 BR00035
D90177 BR00003
D90177 BR00032
D90177 BR00033
D90178 BR00034
D90179 BR00041
D90180 BR00038
D90181 BR00041
D90182 BR00042
D90183 BR00003
D90183 BR00032
D90183 BR00033
D90184 BR00038
D90185 BR00008
D90188 BR00035
D90189 BR00003
D90189 BR00032
D90189 BR00033
D90190 BR00034
D90191 BR00041
D90192 BR00038
D90193 BR00041
D90194 BR00042
D90195 BR00003
D90195 BR00032
D90195 BR00033
D90196 BR00038
D90197 BR00008
D90200 BR00035
D90201 BR00003
D90201 BR00032
D90201 BR00033
D90202 BR00034
D90203 BR00041
D90204 BR00038
D90205 BR00041
D90206 BR00042
D90207 BR00003
D90207 BR00032
D90207 BR00033
D90208 BR00038
D90209 BR00008
D90212 BR00035
D90213 BR00003
D90213 BR00032
D90213 BR00033
D90214 BR00034
D90215 BR00041
D90216 BR00038
D90217 BR00041
D90218 BR00042
D90219 BR00003
D90219 BR00032
D90219 BR00033
D90220 BR00038
D90221 BR00008
D90224 BR00008
D90225 BR00038
D90226 BR00003
D90226 BR00032
D90226 BR00033
D90227 BR00003
D90227 BR00032
D90227 BR00033
D90228 BR00038
D90229 BR00008
D90232 BR00035
D90233 BR00003
D90233 BR00032
D90233 BR00033
D90234 BR00034
D90235 BR00041
D90236 BR00038
D90238 BR00031
D90374 BR00039
D90377 BR00041
D90378 BR00034
D90379 BR00003
D90379 BR00032
D90379 BR00033
D90380 BR00017
D90380 BR00036
D90382 BR00038
D90383 BR00038
D90384 BR00031
D90385 BR00041
D90386 BR00017
D90386 BR00036
D90387 BR00038
D90388 BR00038
D90389 BR00039
D90390 BR00003
D90390 BR00032
D90390 BR00033
D90391 BR00041
D90392 BR00042
D90393 BR00038
D90396 BR00041
D90397 BR00003
D90397 BR00032
D90397 BR00033
D90398 BR00042
D90401 BR00017
D90401 BR00036
D90404 BR00031
D90405 BR00017
D90405 BR00036
D90406 BR00031
D90407 BR00031
D90408 BR00038
D90409 BR00034
D90410 BR00034
D90411 BR00031
D90412 BR00034
D90413 BR00038
D90414 BR00031
D90415 BR00039
D90416 BR00003
D90416 BR00032
D90416 BR00033
D90417 BR00039
D90418 BR00034
D90419 BR00035
D90420 BR00034
D90421 BR00041
D90423 BR00003
D90423 BR00032
D90423 BR00033
D90424 BR00031
D90425 BR00017
D90425 BR00036
D90426 BR00035
D90427 BR00035
D90428 BR00017
D90428 BR00036
D90429 BR00034
D90430 BR00041
D90431 BR00041
D90432 BR00039
D90433 BR00017
D90433 BR00036
D90434 BR00038
D90435 BR00017
D90435 BR00036
D90437 BR00041
D90438 BR00017
D90438 BR00036
D90439 BR00034
D90440 BR00017
D90440 BR00036
D90441 BR00017
D90441 BR00036
D90442 BR00017
D90442 BR00036
D90443 BR00017
D90443 BR00036
D90444 BR00017
D90444 BR00036
D90445 BR00017
D90445 BR00036
D90446 BR00017
D90446 BR00036
D90447 BR00017
D90447 BR00036
D90448 BR00034
D90449 BR00017
D90449 BR00036
D90450 BR00003
D90450 BR00032
D90450 BR00033
D90451 BR00035
D90452 BR00017
D90452 BR00036
D90453 BR00017
D90453 BR00036
D90454 BR00038
D90455 BR00031
D90456 BR00017
D90456 BR00036
D90457 B

One doubt is there in your 2nd solution you have used with..can elaborate me what is the use of having with ..

Now it is working..thanks a lot..

for last select statment is not corect..
Incorrect syntax near the keyword 'select'.

if i want student name in order by.

One more thing we could use order by in this quey..if it is possible very i have write that..

Thank you very much this what am exactly looking for..My long time issue has been resolved by ur answer...

Please help me out..

no just u give sub1 i can understand..

Please can you write that using pivot...?

can you writen give using pivot am unable to write that

I was getting error while executing
Invalid length parameter passed to the LEFT or SUBSTRING function.

No..it is one procedure i want to execute in sql 2008..

one more is that

C063151
C063152
C063153
C063154
C063155
C063156
C063157
C063158
C063159
C0631510
C0631511
C0631512
C0631513
C0631514
C0631515
C0631516
C0631517
C0631518
C0631519
C0631520
C0631521
C0631522
C0631523
C0631524
C0631525
C0631526
C0631527
C0631528
C0631529
C0631530
C0631531
C0631532
C0631533
C0631534
C0631535
C0631536
C0631537
C0631538
C0631539
C0631540
C0631541
C0631542
C0631543
C0631544
C0631545
C0631546
C0631547
C0631548
C0631549
C0631550
C0631551
C0631552
C0631553
C0631554
C0631555
C0631556
C0631557
C0631558
C0631559
C0631560
C0631561
C0631562
C0631563
C0631564
C0631565
C0631566
C0631567
C0631568
C0631569
C0631570
C0631571
C0631572
C0631573
C0631574
C0631575
C0631576
C0631577
C0631578
C0631579
C0631580
C0631581
C0631582
C0631583
C0631584
C0631585
C0631586
C0631587
C0631588
C0631589
C0631590
C0631591
C0631592
C0631593
C0631594
C0631595
C0631596
C0631597
C0631598
C0631599
C06315100
C06315101
C06315102
C06315103
C06315104
C06315105
C06315106
C06315107
C06315108
C06315109
C06315110
C06315111
C06315112
C06315113
C06315114
C06315115
C06315116
C06315117
C06315118
C06315119
C06315120
C06315121
C06315122
C06315123
C06315124
C06315125
C06315126
C06315127
C06315128
C06315129
C06315130
C06315131
C06315132
C06315133
C06315134
C06315135
C06315136
C06315137
C06315138
C06315139
C06315140
C06315141
C06315142
C06315143
C06315144
C06315145
C06315146
C06315147
C06315148
C06315149
C06315150
C06315151
C06315152
C06315153
C06315154
C06315155
C06315156
C06315157
C06315158
C06315159
C06315160
C06315161
C06315162
C06315163
C06315164
C06315165
C06315166
C06315167
C06315168
C06315169
C06315170
C06315171
C06315172
C06315173
C06315174
C06315175
C06315176
C06315177
C06315178
C06315179
C06315180
C06315181
C06315182
C06315183
C06315184
C06315185
C06315186
C06315187
C06315188
C06315189
C06315190
C06315191
C06315192
C06315193
C06315194
C06315195
C06315196
C06315197
C06315198
C06315199
C06315200
C06315201
C06315202
C06315203
C06315204
C06315205
C06315206
C06315207
C06315208
C06315209
C06315210
C06315211
C06315212
C06315213
C06315214
C06315215
C06315216
C06315217
C06315218
C06315219
C06315220
C06315221
C06315222
C06315223
C06315224
C06315225
C06315226
C06315227
C06315228
C06315229
C06315230
C06315231
C06315232
C06315233
C06315234
C06315235
C06315236
C06315237
C06315238
C06315239
C06315240
C06315241
C06315242
C06315243
C06315244
C06315245
C06315246
C06315247
C06315248
C06315249
C06315250
C06315251
C06315252
C06315253
C06315254
C06315255
C06315256
C06315257
C06315258
C06315259
C06315260
C06315261
C06315262
C06315263
C06315264
C06315265
C06315266
C06315267
C06315268
C06315269
C06315270
C06315271
C06315272
C06315273
C06315274
C06315275
C06315276
C06315277
C06315278
C06315279
C06315280
C06315281
C06315282
C06315283
C06315284
C06315285
C06315286
C06315287
C06315288
C06315289
C06315290
C06315291
C06315292
C06315293
C06315294
C06315295
C06315296
C06315297
C06315298
C06315299
C06315300
C06315301
C06315302
C06315303
C06315304
C06315305
C06315306
C06315307
C06315308
C06315309
C06315310
C06315311
C06315312
C06315313
C06315314
C06315315
C06315316
C06315317
C06315318
C06315319
C06315320
C06315321
C06315322
C06315323
C06315324
C06315325
C06315326
C06315327
C06315328
C06315329
C06315330
C06315331
C06315332
C06315333
C06315334
C06315335
C06315336
C06315337
C06315338
C06315339
C06315340
C06315341
C06315342
C06315343
C06315344
C06315345
C06315346
C06315347
C06315348
C06315349
C06315350
C06315351
C06315352
C06315353
C06315354
C06315355
C06315356
C06315357
C06315358
C06315359
C06315360
C06315361
C06315362
C06315363
C06315364
C06315365
C06315366
C06315367
C06315368
C06315369
C06315370
C06315371
C06315372
C06315373
C06315374
C06315375
C06315376
C06315377
C06315378
C06315379
C06315380
C06315381
C06315382
C06315383
C06315384
C06315385
C06315386
C06315387
C06315388
C06315389
C06315390
C06315391
C06315392
C06315393
C06315394
C06315395
C06315396
C06315397
C06315398
C06315399
C06315400
C06315401
C06315402
C06315403
C06315404
C06315405

it is not correct..
pls corect this als

select statment will execute but update is not executing..some error throwing
Invalid column name 'C_Code'.