Jack & Rxm,

My other there parameters @Start, @End, @NextStart, @NextEnd are dependent on @StartDate (2014-06-01). How can I set the other three parameters (@End, @NextStart, @NextEnd) in SSRS expression as same way as T-SQL?

set @Start = DATEADD( MM, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()),0))

Set @End = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))

Set @NextStart = DATEADD(MONTH, 1, @Start)

Set @NextEnd = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +2, 0))

Here is the T-Sql code:

declare @Start as date

declare @End as date

declare @NextStart as date

Declare @NextEnd as date

set @Start = DATEADD( MM, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()),0))

Set @End = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))

Set @NextStart = DATEADD(MONTH, 1, @Start)

Set @NextEnd = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +2, 0))

Select J.Job, cast(ce.sStartDate as Date) 'PrintDate', Cast(j.lastshippeddate as date) 'ShipDate', Cast(i.invdate as date) 'InvoiceDate', i.InvNumber, I.CompanyName 'Customer', I.TotalInvAmount'Total Inv Amount'

from JobExtra J

Join JobMaster m (nolock) on J.JobNumber=m.JobNumber

Join InvoiceHeader I (nolock) on I.JobNumber = J.JobNumber

Join (select MIN (CE.RecID) 'Record', ce.sJobNumber 'Job' from JobExtra J

left join CostEntry CE (nolock) on CE.sJobNumber=J.JobNumber

left join JobExtra JE (nolock) on J.JobNumber=JE.JobNumber

left join [InvoiceHeader] I (nolock) on I.JobNumber = JE.JobNumber

where (CE.sDepartmentCode='AG')

and (cast (CE.sStartDate as date) between @Start and @End)

and (cast (J.LastShippedDate as date) between @NextStart and @NextEnd)

group by ce.sJobNumber

) t on t.Job = i.JobNumber

Join CostEntry CE (nolock) on CE.sJobNumber=t.Job and ce.RecID = t.record

where CAST (I.InvDate as DATE) not between @Start and @End

Thanks