Dynamically back track records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Dynamically back track records

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-09-05 : 08:47:54

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-09-12 : 20:54:14

I tried this 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".
Whats wrong with my query. Thanks.

--dynamics date not working
IF @@ROWCOUNT < 0 
BEGIN
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources