September 5, 2013 at 7:03 am
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
September 6, 2013 at 12:15 am
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
September 6, 2013 at 2:04 am
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 (...
September 6, 2013 at 3:08 am
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".
September 6, 2013 at 6:39 am
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
September 8, 2013 at 8:49 pm
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
September 15, 2013 at 8:16 pm
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
September 16, 2013 at 1:18 am
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
September 16, 2013 at 3:36 am
Thanks Fitz. I will try this solution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply