1.
To get the Parent id list of an advisor
Here it is :declare @id int=7;declare @tmp varchar(max)='';declare @t1 table(advisor_id int,name varchar(50),parent_id int)insert into @t1 values(1,'Griff',0),(2,'christian',1),(3 ,'AArti',2),(4,'king_fisher',3),(5,'jason',4),(6,'smith',5),(7, 'willis' ...
|
Amir Mahfoozi
|
Updated: 28 Dec 2013
Rating: 5.00/5
( (2 votes))
|
|
|
2.
Update Column Defination issue
Why haven't you googled that error message ?To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Clear the Prevent saving changes that require the table to be re-created check box.Pasted from here[^]
|
Amir Mahfoozi
|
Updated: 20 Dec 2013
Rating: 5.00/5
( (1 vote))
|
|
|
3.
How to get top 1 record from unique parentid and batch id
Here it is :select t.fileid, t.parentid, batchid from(select *, row_number() over (partition by parentid, batchid order by fileid) rno from tablename) twhere rno =1Replace tablename with your table name.Good Luck
|
Amir Mahfoozi
|
Updated: 20 Dec 2013
Rating:
( (No votes))
|
|
|
4.
Summing Each ROW using Cursor in Sql
This should solve your problem but I don't know why I got different result ! Had you computed them manually ?declare @tbl1 as table(balance decimal(18,2),month int)insert into @tbl1 values (-1308998.02, 1)insert into @tbl1 values ( -990000, 2)insert into @tbl1 values (...
|
Amir Mahfoozi
|
Updated: 9 Dec 2013
Rating:
( (No votes))
|
|
|
5.
UNPIVOTING A TABLE IN SQL SERVER 2008
Here it is :declare @table1 table (id int,prodname nvarchar(10),energy int,protein int,calcium int)insert into @table1 values (1,'ABC', 156, 134, 195)insert into @table1 values (2,'DEF', 123, 345, 345)insert into @table1 values (3,'ERT', 134, 345, 456)select *...
Database Development »
SQL Server »
SQL Server 2008
|
Amir Mahfoozi
|
Updated: 8 Dec 2013
Rating: 5.00/5
( (1 vote))
|
|
|
6.
left join in multiple tables
This will give you what you want. But I supposed that table1 has all the Ids.select id, (select sum(amount) from table1 st1 where st1.id=tbl1.id) t1, (select sum(amount) from table2 st2 where st2.id=tbl1.id) t2, (select sum(amount) from table3 st3 where...
|
Amir Mahfoozi
|
Updated: 7 Dec 2013
Rating: 1.67/5
( (3 votes))
|
|
|
7.
Select records having two columns have similar value
Here is a simple solution :(I supposed that your table name is vendor)select v2.vendorcode from vendor v1, vendor v2where v1.vendorcodev2.vendorcode and v1.bankaccountno = v2.bankaccountno and v1.pan_number = v2.pan_numberGood Luck
|
Amir Mahfoozi
|
Updated: 2 Dec 2013
Rating:
( (No votes))
|
|
|
8.
Sum With an addtion column in sql server
Here it is :(I supposed that your table name is Rep)with A as(SELECT [Name],[Month],[Number] ,row_number() over (partition by name , month order by name, month ) rno FROM Rep)select name , month, number, (select sum(number) from a a2 where a2.rno
|
Amir Mahfoozi
|
Updated: 1 Dec 2013
Rating: 5.00/5
( (2 votes))
|
|
|
9.
Sql statement help - not sure whether it is possible to do it in one sql statement
You can produce table a and b cartesian product and then subtract the c set from it to see the result. For subtraction use except operator. for more study read set functions of TSQL :http://technet.microsoft.com/en-us/library/ms188055.aspx[^]So your SQL is this :select aid, bid from a,b...
Database Development »
SQL Server »
SQL Server 2008
|
Amir Mahfoozi
|
Updated: 30 Nov 2013
Rating: 5.00/5
( (1 vote))
|
|
|
10.
how to group the table by its column item
Here is a pivot approach :declare @data table (name varchar(50),[group] varchar(50))insert into @data (NAME, [GROUP])values ('AAA', 'A'), ('AAA', 'B'), ('AAA', 'A'), ('AAA', 'C'), ('BBB', 'A'), ('BBB', 'B'), ('BBB', 'B')select name, A, B, Cfrom (select name, [group],...
|
Amir Mahfoozi
|
Updated: 12 Nov 2013
Rating:
( (No votes))
|
|
|
11.
Reading Cell Value and Checking in SQL
Check this one :select case when charindex('/', '345/576')>0 then '345/576' else 'error' endReplace '345/576' with a field name and add from statement and other things...Hope its clear.
|
Amir Mahfoozi
|
Updated: 5 Nov 2013
Rating:
( (No votes))
|
|
|
12.
Sql server date format conversion
Hi Sunil,If the timezone part is important to you, here is one solution :select convert(datetime, convert(datetimeoffset, stuff(substring('Thu, 31 Oct 2013 10:51:18 +0000',5,27),26,0,':'),1),1);You can change the timezone part to see different outputs.If all of the time zones are...
|
Amir Mahfoozi
|
Updated: 1 Nov 2013
Rating:
( (No votes))
|
|
|
.
()
|
|
Updated:
Rating:
()
|
|
|
13.
Assigning a value to variable in Dynamic SQL
Hi,It seems that your answer is here :http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable[^]Good Luck
|
Amir Mahfoozi
|
Updated: 6 May 2013
Rating:
( (No votes))
|
|
|
.
()
|
|
Updated:
Rating:
()
|
|
|
.
()
|
|
Updated:
Rating:
()
|
|
|
.
()
|
|
Updated:
Rating:
()
|
|
|
.
()
|
|
Updated:
Rating:
()
|
|
|
14.
how to get an output to a variable (@incode) from exec (query) command
Hi,Does this page help you :http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable[^]Good Luck.
|
Amir Mahfoozi
|
Updated: 4 Jan 2013
Rating:
( (No votes))
|
|
|
.
()
|
|
Updated:
Rating:
()
|
Submit an article or tip