SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Dynamically back track records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

447 Posts

Posted - 09/05/2013 :  08:47:54  Show Profile  Reply with Quote
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

Edited by - Villanuev on 09/05/2013 08:48:42

Villanuev
Constraint Violating Yak Guru

447 Posts

Posted - 09/12/2013 :  20:54:14  Show Profile  Reply with Quote

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

Edited by - Villanuev on 09/12/2013 20:55:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000