Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamically back track records Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:34 AM
Points: 37, Visits: 101
Hi,

Just made changes with my requirements.
I need to dynamically back track 1 day at a time in extracting of records if my query does not found any records. actually i made already a query i though its working but need to change dynamically. i though only Sunday that we don't have any transaction but in case there's a holiday which that fall in week days which i did not consider this scenario that's why i change my Sql statement. just stuck up for this requirements. Thanks in advance. your help is very much appreciated.


With my solution, i encounter error

Incorrect syntax near '@PFromDate'
Cannot use a BREAK statement outside the scope of a WHILE statement.

Here is my sample and DDL

Create table #SampleData(Prodid nvarchar(35), model nvarchar(35), Trandate datetime)
Go
Insert into #SampleData
Select 'TRC003328042','DRDRZR2BLK','2013-08-31 04:30:33.000' union all---Saturday
Select 'TRC003327670','DRDRZR2BLK','2013-08-31 02:40:23.000'union all---Saturday
Select 'TRC003327822','DROIDRZR','2013-08-31 05:17:28.000'union all---Saturday
Select 'TRC003328342','DROIDRZR','2013-08-31 08:10:27.000'union all---Saturday
Select 'TRC003328387','DROIDRZR','2013-08-31 09:22:01.000'union all---Saturday
Select 'TRC003322836','DRDRZRHDBLK','2013-08-30 23:40:19.000'union all---Saturday
Select 'TRC003326586','DROIDBIO2','2013-08-31 05:50:47.000'union all---Saturday
Select 'TRC003325460','DROIDBIO2','2013-08-30 22:36:50.000'union all---Saturday
Select 'TRC003328994','DRDRZR2WHT','2013-09-02 04:33:44.000'union all-- Monday
Select 'TRC003328998','DRDRZR2WHT','2013-09-02 06:37:13.000'-- Monday



--Created SSRS Parameter
Declare @FromDate datetime, @ToDate datetime, @rowcount int,
@Fromtime nvarchar(6), @Totime nvarchar(6) ,@timezoneOffset int
set @FromDate='9/2/2013' --Monday
set @ToDate='9/3/2013'
Set @Fromtime='6:00 AM'
Set @Totime='6:00 AM'
set @timezoneOffset=8
set @rowcount=0


--Add in Dataset Properties->Parameter
Parameter name--------Parameter value
@PFromDate=Parameters!FromDate.Value &" "&Parameters!FromTime.Value
@PToDate=Parameters!ToDate.Value &" "&Parameters!ToTime.Value



--Text Query expression

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE [dbo].[#Temp]

Create table #Temp (Prodid nvarchar(35), model nvarchar(35), Trandate datetime)


While (1=1)
BEGIN

Insert into #Temp(Prodid,model,Trandate)
select Prodid, model, Trandate from #SampleData
Where
DATEADD(HOUR,convert(int,@timezoneOffset), Trandate)
BETWEEN DATEADD(DAY,0,@PFromDate) AND DATEADD(DAY,0,@PToDate)

IF @ROWCOUNT < 0
BEGIN
--got an error from this portion
@PFromDate=@PFromDate -1
@PToDate=@PToDate-1
END
ELSE
BREAK
END


--Query validation to get the final result set
With CTE AS
(
select * from #Temp
)
SELECT * from CTE

Post #1491765
Posted Friday, September 6, 2013 12:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:34 AM
Points: 37, Visits: 101
got an error compling my query in query designer for my SSRS report.

Got an error , Incorrect syntax near '@PFromDate' . what wrong with this error.
i already declare this parameter to dataset properties/parameter.
even i setting up a SET keyword still have this error (Must declare scalar variable..@PFfromDate)
how could fixed this problem? yourhelp is very much appreciated. thanks.


IF @rowcount=0
BEGIN
@PFromDate=@PFromDate-1
@PToDate=@PToDate-1
END
Post #1492047
Posted Friday, September 6, 2013 2:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:11 PM
Points: 3,206, Visits: 1,399
As you are using TSQL at that point :

@PFromDate=@PFromDate -1
@PToDate=@PToDate-1

needs the word SET

SET @PFromDate=@PFromDate -1
SET @PToDate=@PToDate-1

As an additional point the WITH statement of the CTE later on needs to have a ; between it and the preceding code so best to put

;WITH CTE (...



Post #1492077
Posted Friday, September 6, 2013 3:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:34 AM
Points: 37, Visits: 101
Yes. I'm using t-SQL but inside the SSRS reporting tool. Tried already to place a keyword Set @PFromDate but still got an error. i already stuck for a few days for this issue. your help is very much appreciated. thanks for your reply.

Herewith is the error encounter from my query.
Compiling my query from dataset properties->text query. the result is..

Must declare the scalar variable "@PFromDate".
Must declare the scalar variable "@PFromDate".
Must declare the scalar variable "@PToDate".
Must declare the scalar variable "@PFromDate".
Post #1492096
Posted Friday, September 6, 2013 6:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:11 PM
Points: 3,206, Visits: 1,399
Try this...I have assumed that @PToDate and @PFromDate are parameters set within SSRS. If so then they cannot be set within the query text.

Workaround : create two additional TSQL variables called @PFromDateInside and @PToDateInside. Set these initially to the parameter values then increment these variables within the WHILE.

Create table #SampleData(Prodid nvarchar(35), model nvarchar(35), Trandate datetime)
Go
Insert into #SampleData
Select 'TRC003328042','DRDRZR2BLK','2013-08-31 04:30:33.000' union all---Saturday
Select 'TRC003327670','DRDRZR2BLK','2013-08-31 02:40:23.000'union all---Saturday
Select 'TRC003327822','DROIDRZR','2013-08-31 05:17:28.000'union all---Saturday
Select 'TRC003328342','DROIDRZR','2013-08-31 08:10:27.000'union all---Saturday
Select 'TRC003328387','DROIDRZR','2013-08-31 09:22:01.000'union all---Saturday
Select 'TRC003322836','DRDRZRHDBLK','2013-08-30 23:40:19.000'union all---Saturday
Select 'TRC003326586','DROIDBIO2','2013-08-31 05:50:47.000'union all---Saturday
Select 'TRC003325460','DROIDBIO2','2013-08-30 22:36:50.000'union all---Saturday
Select 'TRC003328994','DRDRZR2WHT','2013-09-02 04:33:44.000'union all-- Monday
Select 'TRC003328998','DRDRZR2WHT','2013-09-02 06:37:13.000'-- Monday



--Created SSRS Parameter
Declare @FromDate datetime, @ToDate datetime, @rowcount int,
@Fromtime nvarchar(6), @Totime nvarchar(6) ,@timezoneOffset int
set @FromDate='9/2/2013' --Monday
set @ToDate='9/3/2013'
Set @Fromtime='6:00 AM'
Set @Totime='6:00 AM'
set @timezoneOffset=8
set @rowcount=0


/***** Assume this is being performed in SSRS
@PFromDate=Parameters!FromDate.Value &" "&Parameters!FromTime.Value
@PToDate=Parameters!ToDate.Value &" "&Parameters!ToTime.Value
*****/

DECLARE @PFromDateInside datetime
DECLARE @PToDateInside datetime
SET @PFromDateInside = @PFromDate
SET @PToDateInside = @PToDate

--Text Query expression

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE [dbo].[#Temp]

Create table #Temp (Prodid nvarchar(35), model nvarchar(35), Trandate datetime)


While (1=1)
BEGIN

Insert into #Temp(Prodid,model,Trandate)
select Prodid, model, Trandate from #SampleData
Where
DATEADD(HOUR,convert(int,@timezoneOffset), Trandate)
BETWEEN DATEADD(DAY,0,@PFromDateInside) AND DATEADD(DAY,0,@PToDateInside)

IF @ROWCOUNT < 0
BEGIN
--got an error from this portion
SET @PFromDateInside=@PFromDateInside -1
SET @PToDateInside=@PToDateInside-1
END
ELSE
BREAK
END


--Query validation to get the final result set
With CTE AS
(
select * from #Temp
)
SELECT * from CTE


Post #1492194
Posted Sunday, September 8, 2013 8:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:34 AM
Points: 37, Visits: 101
Hi Mark,

Sorry for my late reply.
I tried your solution upon running the query if my condition for @@rowcount is < 0 dynamically the date did not process the less 1 day but when change to @@rowcount=0 it is working. the problem when compiling the SSRS
it takes time and then goes hang with message "Query Designer: not responding". thanks.

--dynamics date not working
IF @@ROWCOUNT < 0
BEGIN
--got an error from this portion
SET @PFromDateInside=@PFromDateInside -1
SET @PToDateInside=@PToDateInside-1

--working
IF @@ROWCOUNT = 0
BEGIN
--got an error from this portion
SET @PFromDateInside=@PFromDateInside -1
SET @PToDateInside=@PToDateInside-1
Post #1492646
Posted Sunday, September 15, 2013 8:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:34 AM
Points: 37, Visits: 101
Why it is my query designer keeps hanging (not responding)
compiling this query. any idea guys. thanks.


While (1=1)
BEGIN
INSERT inTO ...
SELECT * FROM WHERE

IF @@ROWCOUNT = 0
BEGIN
SET @PFromDateInside=@PFromDateInside -1
SET @PToDateInside=@PToDateInside-1
ENDIF
ELSE
BREAK
END

OR


While (1=1)
BEGIN
INSERT inTO ...
SELECT * FROM WHERE

IF @@ROWCOUNT >=0
BREAK
ELSE
BEGIN
SET @PFromDateInside=@PFromDateInside -1
SET @PToDateInside=@PToDateInside-1
END
END
Post #1494919
Posted Monday, September 16, 2013 1:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 11, 2014 2:11 PM
Points: 3,206, Visits: 1,399
OK the loop is causing the problem.

Taking a look at the original logic, you would like to return the rows from the #SampleData table where the trandate is between a parameterised start and end dates. If no rows are returned then move the start and end dates back until rows are returned.

It is probably easier to work out the latest period for which rows would be returned using a query such as below:

select max(TranDate) as LatestDateTime
from #sampledata
where Trandate between dateadd(dd,-10,@PFromDate) and @PToDate

I have selected to move the earliest date back by 10 days just in case there is a large break between transactions.

The SSRS query would then look something like the below query to return all #sampledata rows from the last day.

select * from #sampledata
where convert(date,TranDate) =
(select convert(date,max(TranDate)) as LatestDate
from #sampledata
where Trandate between dateadd(dd,-10,@PFromDate) and @PToDate)

This would remove the requirement for the loop, the #Temp and the CTE.

Fitz
Post #1494944
Posted Monday, September 16, 2013 3:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 3:34 AM
Points: 37, Visits: 101
Thanks Fitz. I will try this solution.
Post #1494975
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse