PW, you are saying that

declare @StartDate datetime

declare @EndDate datetime

select @StartDate = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 11, 101), 101)

select @EndDate = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 10, 101), 101)

SELECT ARJOBHD.INVNO, ARJOB.PRICE, ARJOB.FRTAMT,...

If I understand you correctly, I have seen a similar problem before... Does the following work?

select dt1.username, dt2.template from

(

select INGRES_USERNAME as username, count(*) as cnt from user_access

where

IsNumeric(Right(INGRES_USERNAME,3)) = 1

group by...

So that wasn't the problem  If

select dbo.fnc_GetManagerForOrgIdAndDate(VSS.IFA, 56, VSS.OriginalSourceCreatedDate), dbo.fnc_GetManagerForOrgIdAndDate(VSS.IFA, 45, VSS.OriginalSourceCreatedDate)

from....

returns the same, and it shouldn't, then it must be the logic in...

So the difference between the two function calls is @BusinessRoleId, which again means that the @BusinessRoles table is different. Could the problem be that the select in your function sometimes returns...

"it might be worth setting 2 variables to the required period start/end dates."

That's more or less what Joe Nakanishi suggested, isn't it?

You are probably thinking on jratwork in the thread

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

I have a suggestion which gives you what you want in your test case. It involves creating a user-defined function called sortFunction, which again uses a Numbers table. Create the...

You might want to check out this thread:

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

If you add "where a.ContryCode != b.ContryCode" then we have the same solution, more or less

How about this?

declare @acc_transaction table(

  TransID int,

  TransDate datetime,

  SocietyID int,

  TransType varchar(10),

  MobileNo varchar(20),

  Amount int

)

insert @acc_transaction select 1, '20-Dec-05', 1, 'Lodge', 12345, 100

insert @acc_transaction select 2, '20-Dec-05',...

You should get an error message when you execute the sql above, I think...

Anyway, could you post some test data along with the expected result...

Try this:

declare @ContryCodes table (ID int, ContryCode varchar(2))

insert @ContryCodes select 1, 'SE'

insert @ContryCodes select 2, 'GB'

insert @ContryCodes select 3, 'FR'

declare @Transactions table (TransactionID int, ContryCode varchar(2), DescriptionText varchar(10))

insert @Transactions select...

You will probably need COUNT(distinct O1.OrderId), COUNT(distinct O2.OrderId) to get that working.... I like John's solution, however:

select

CustomerId,

sum(case when OrderDate between @d1 and @d2 then 1 else 0 end),

sum(case...

Try this trick (which I have learned here at SSC ):

select dateadd(d, datediff(d, '1900', '2005-12-22 14:22'), '1900')

You could also try the following:

-- Create test data

declare @MemberStatus table

(

MemberNbr integer not null,

EffectiveDate int not null,

Status1 char(1) not null,

Status2 char(2) not null,

Status3 char(2) not null

)

insert into @MemberStatus

(MemberNbr ,Status1 ,Status2...