SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically back track records


Dynamically back track records

Author
Message
Kaniguan_SQL
Kaniguan_SQL
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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


Kaniguan_SQL
Kaniguan_SQL
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Mark Fitzgerald-331224
Mark Fitzgerald-331224
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4425 Visits: 1650
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 (...
Kaniguan_SQL
Kaniguan_SQL
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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".
Mark Fitzgerald-331224
Mark Fitzgerald-331224
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4425 Visits: 1650
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



Kaniguan_SQL
Kaniguan_SQL
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Kaniguan_SQL
Kaniguan_SQL
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Mark Fitzgerald-331224
Mark Fitzgerald-331224
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4425 Visits: 1650
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
Kaniguan_SQL
Kaniguan_SQL
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 101
Thanks Fitz. I will try this solution.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search