x

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
more ▼

asked Mar 26, 2012 at 01:35 PM in Default

Pinkett gravatar image

Pinkett
30 11 14 15

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Mar 26, 2012 at 03:22 PM

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

also need to consider the data type and whether the time part is significant.
Mar 27, 2012 at 08:39 AM Fatherjack ♦♦

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
Mar 28, 2012 at 10:33 AM Pinkett
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 28, 2012 at 01:18 PM

Pinkett gravatar image

Pinkett
30 11 14 15

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x28

asked: Mar 26, 2012 at 01:35 PM

Seen: 1454 times

Last Updated: Mar 28, 2012 at 01:18 PM