question

Pinkett avatar image
Pinkett asked

Reporting Services and Date functions and Date Parameters

Hi, I'm not very good with using date functions at an advanced level. However, a client of mine has reuested that i create a report(SSIS 2008), that connects to the HEAT database(Change Management System). I need to set up a date parameter where the date entered will collect all the data from two days before, so i would use =dateadd(dd, -2, @reportdate) @ReportDate being my parameter. Now i need to write a query that will sum the closing balance and the open calls during that period as well as the opening balance. The closing balance and the opening balance shoudl correspond. Please advise. Regards, Pinkett
date
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
I would get the stored procedure you use to return the data for the report to do the work, so get the user to select the date parameter and manipulate as you've described, please see my example below using AdventureWorks2008. select * from HumanResources.EmployeePayHistory where RateChangeDate = dateadd(dd,-2,@Date) The one thing you will need to be careful of is which type of field you have used whether it's Date or Datetime, as if it's datetime you may need to consider the timings aswell.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

also need to consider the data type and whether the time part is significant.
2 Likes 2 ·
Thanks a mil Annette. Just have another question, my query seems to be incorrect because i need to present the report every two days. Now my closing balance from the previous generated Dates, must be the same for the next opening balance. How so i get around that? Thanks
0 Likes 0 ·
Pinkett avatar image
Pinkett answered
ALTER PROCEDURE [dbo].[usp_RptProc1]( @EndDate Datetime) AS DECLARE @StartDate Datetime SET @StartDate = @EndDate -2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; select Sum(OpenBalance) +sum(NewCalls) - sum(CloseDuringPeriod) as ClosingBalance ,Priority, convert(char(10),periodEnddate,121) periodEnddate,Service from ( SELECT CallLog.CallID, CallLog.CustType, CallLog.CallType, CallLog.CallStatus, CallLog.Environment, CallLog.Service, CallLog.CallDesc, CallLog.HeatReleaseRef, 'P' + CallLog.Priority Priority, CallLog.RecvdDate,RecvdTime,CallLog.ClosedDate,ClosedTime,ResolvedTime, case when (CallStatus = 'Open' and CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00') or (CallLog.ClosedDate + ' ' + ClosedTime >= convert(varchar(10),@StartDate,121) + ' 00:00:00' and CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00') or (CallLog.ResolvedDate + ' ' + ResolvedTime >= convert(varchar(10),@StartDate,121) + ' 00:00:00' and CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00') then 1 else 0 end as OpenBalance , case when (CallLog.ClosedDate + ' ' + ClosedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59') or (CallLog.ResolvedDate + ' ' + ResolvedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59')then 1 else 0 end as CloseDuringPeriod, case when CallLog.RecvdDate + ' ' + RecvdTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + '23:59:59' then 1 else 0 end as NewCalls ,@StartDate as periodStartDate ,@EndDate as periodEnddate FROM HEAT.dbo.CallLog CallLog WHERE --CallLog.Service='Equities' -- (CallLog.Service Like 'Bonds%' OR CallLog.Service='Equities' OR CallLog.Service='Money Markets' OR Calllog.Service='BND') AND (CallLog.Environment Like 'Prod%') AND (CallLog.RecvdDate + ' ' + RecvdTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59' ) or (CallLog.ClosedDate + ' ' + ClosedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59') or (CallLog.ResolvedDate + ' ' + ResolvedTime between convert(varchar(10),@StartDate,121) + ' 00:00:00' and convert(varchar(10),@EndDate,121) + ' 23:59:59' ) or (CallLog.RecvdDate + ' ' + RecvdTime < convert(varchar(10),@StartDate,121) + ' 00:00:00' and CallLog.CallStatus = 'Open')) and to sum up all my open calls i have another stored procedure: ALTER PROCEDURE [dbo].[usp_RptProc2](@EndDate Datetime) AS DECLARE @StartDate Datetime set @StartDate= @EndDate - 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT CallLog.CallID, CallLog.CustType, CallLog.CallType, CallLog.CallStatus, CallLog.Environment, CallLog.Service, CallLog.CallDesc, CallLog.HeatReleaseRef, 'P' + CallLog.Priority Priority,CallLog.RecvdDate,CallLog.ClosedDate, case when (CallStatus = 'Open' and CallLog.RecvdDate < @StartDate) or (CallLog.ClosedDate >= @StartDate and CallLog.RecvdDate < @StartDate) or (CallLog.ResolvedDate >= @StartDate and CallLog.RecvdDate < @StartDate )then 1 else 0 end as OpenBalance, case when --(CallStatus in ('Closed','Resolved')) or (CallLog.ClosedDate between @StartDate and @EndDate) or (CallLog.ResolvedDate between @StartDate and @EndDate)then 1 else 0 end as CloseDuringPeriod, case when CallLog.RecvdDate between @StartDate and @EndDate then 1 else 0 end as NewCalls FROM HEAT.dbo.CallLog CallLog WHERE --CallLog.Service='Equities' -- (CallLog.Service Like 'Bonds%' OR CallLog.Service='Equities' OR CallLog.Service='Money Markets' OR Calllog.Service='BND') AND (CallLog.Environment Like 'Prod%') --AND (CallLog.CallStatus<>'Closed' --And (CallLog.CallStatus<>'Resolved') AND ((CallLog.RecvdDate between @StartDate and @EndDate) or (CallLog.ClosedDate between @StartDate and @EndDate) or (CallLog.ResolvedDate between @StartDate and @EndDate) or (CallLog.RecvdDate < @StartDate and CallLog.CallStatus = 'Open')) ORDER BY CallLog.CallID,CallLog.Service, CallLog.CustType, CallLog.CallType What i'm trying to achieve is to get two days worth of data at a time. what am i doing wrong? Today's closing balance must be tomorrow's opening balance
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.