I think there are some (ClientID = 1126) in table Receipts.
Change your SQL to:


SELECT Clients.ClientID, Clients.WholeName,
SUM(CASE WHEN Payments.Creditorid = 0 THEN Payments.Amount ELSE...

I think there are data double in table Receipts.
Please give some data for example.

or use these commands:

declare @Day varchar(255), @Sales int, @strSales varchar(1024)

CREATE TABLE #tmpSales ([Day] varchar(255), SalesQuantity int)
INSERT #tmpSales
SELECT 'Monday', 20...

I think you cannot use simple sql command to get result like that. You can use cursor to concatenate the sales quantity like below:

declare @Day varchar(255), @Sales int

create table #tmpSales...

Try code below


set nocount on
CREATE TABLE #tmpName (ID int, [Name] varchar(255))
CREATE TABLE #tmpRelation (ID int, ID_Parent int)

INSERT #tmpName (ID, [Name]) select 1, 'value1'
INSERT...

Can u give some data and the result you want?
or you can see http://www.daniweb.com/forums/thread114466.html.

I do not understand why if test2.id = 2 then the result will 1 NULL NULL NULL. if you want that result try below:

SELECT #test1.ID, case when #test1.id <> isNull(#test2.id,0) then null else name1...

create table #test1 (ID int, name1 varchar(255))
insert #test1 values (1, 'value1')


create table #test2 (ID int, name2 varchar(255))
insert #test2 values (1, 'value2')

create table #test3...

May be this SQL can solve this problem:

create table #tmpT (ABC char(1))

insert #tmpT
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select...

maybe you can use CASE ... WHEN ... END command like below:


select cast(case when exists (SELECT * FROM UserList WHERE UserName = 'Tester') then 1 else 0 end as bit)

Try this query below:

SELECT 'Testing' as [Title] , [MONEY1] , [MONEY2]
from (SELECT sum(Total) AS [MONEY1] FROM((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON...

Make it subquery like below:


select [Year], [Month], sum([Benefits Rating]) as [Benefits Rating], sum([Facilities Rating]) as [Facilities Rating]
from (...... your origin query ......) X
...

You cannot use just select command.
You must use cursor to list the time list.
Try and modify code below:


set nocount on
declare @CounterDate smalldatetime,
@MaxDate smalldatetime

create...

You can use table master.dbo.sysprocesses.
This system table contain connected users.

Try this command:

select T1.idx, T2.idy, T2.u
from Table1 T1 right join Table2 T2 on
T1.idy = T2.Idy and T1.idx = 'one'

change your command into:

select *
from (Select table_name
from information_schema.tables
where table_name= @table_name
) AliasName


change your...

Use this command

select sum(Total) as [Amount]
from
(
select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
union all
select Amount as Total...

select sum(Total)
from
(
select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
union all
select Amount as Total FROM TABLE3
) X

Try this code below:


select 1 as Col1, 'MT' as Col2, 30 as Col3, 'UK' as Col4, 0 as Col5, 70 as Col6, 'FR' as Col7
into #tmpX
union all
select 2 as Col1, 'SM' as Col2, 30 as Col3, 'IL' as...

Try this code below:

select top 1 *
from (select top 6 *
from #tmpSalary
order by Salary) X
order by Salary desc

Try code below:

declare @Ask varchar(255)

select @Ask = '100.100.2225.104'

create table #tmpAddr (
Addr varchar(255),
Inc int)

Change your Select check to:
charindex(' ' + uzip + ' ',' ' + 'S10 S11 S7 S17 S8 S3 S30' + ' ') > 0

This code will check 'S1' <> 'S10'

Try this code below:


select *
from #tmpX X inner join (select A, B from #tmpX group by A, B having count(*) > 1) Y on
X.A = Y.A and X.B = Y.B

Try this one:


select completed
from table
order by case when completed is null then 0 else 1 end, completed desc

You can combine your 2 query with 'UNION ALL' or 'UNION' like below:

SELECT * FROM TBL_Modules WHERE identifier IN ('1','2','3','4','5','6') ORDER BY Identifier
union all
SELECT m.* FROM...

select * from tb_TableName where AColumn like 'user1%'

create table #tmp (Code varchar(255), Price money, Date smalldatetime)

insert #tmp (Code, Price, Date)
select 'ProductA', 12.00, '12/10/2002' union all
select 'ProductA', 12.50, '01/15/2005'...

create table #tmp (Code varchar(255), Price money, Date smalldatetime)

insert #tmp (Code, Price, Date)
select 'ProductA', 12.00, '12/10/2002' union all
select 'ProductA', 12.50, '01/15/2005'...

Try this code.


SELECT DISTINCT
COALESCE (s3.ADM_CODE, s1.ADM_CODE) AS Expr1, COALESCE (s3.FULLNAME, COALESCE (COALESCE (CASE s2.shortform WHEN '' THEN NULL
...

Try this code

select c.name, isNull(doc_A.Doc_A, 0) as Doc_A, isNull(doc_B.Doc_B, 0) as Doc_B, isNull(doc_C.Doc_C, 0) as Doc_C, isNull(doc_D.Doc_D, 0) as Doc_D
from tUsers u inner join...

You cannot increase this limit. You must change your program. See http://www.daniweb.com/forums/thread114466.html. Hope this link can give you some idea.

select top 1 *
from (select *
from (select * from table where columname > '2') X
where columname > '4') Y
where columname > '6'
order by columname desc

update ABC
set XYZ = left(XYZ, 8) + 'A' + substring(XYZ, 9, 1000)
where XYZ like '%P' and XYZ not like '%A%'

I think you use Transaction. So you must start Distributed Transaction Coordinator on both server.

I think you can create identity columns.


create table #tmpTemp
(ID int identity(1,1),
Code varchar(255),
Name varchar(255))

insert #tmpTemp (Code, Name)
select 'A', 'AA' -->...

The correct code is:

select * from mail3 where email like '%+%' or email like '%*%'

Hope this code can help you.

1. Execute your first SP in query analyzer to show the columns list.

2. Create temporary table and the field must the same as first SP columns

create table...

Try this code in Query Analyser. It enough for small data. If you want to use for big data, modify the code.


set nocount on
create table #tmpName (ID int, [Name] varchar(255))
create table...

Try this code

select 1 as EmpID, 'John' as EmpName, '2007/01/01' as DateOfBirth
into #tmpData
union all
select 2 as EmpID, 'Anne' as EmpName, '2007/01/05' as DateOfBirth
union all
select 3...

you need to create table tb_IPList (IP varchar(20)) and code (in ASP) below:
<%
option explicit

Dim Conn as object
Dim RS as object

set Conn = createobject("ADODB.Connection")
Conn.Mode = 3...