Set date parameter - SQL Server Forums

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)

Set date parameter

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-09-02 : 09:29:24

Hi,

I have an SSRS parameter called Ordermonth, the current display "Sep-2013" combination of month and year.
This order month will change every 27th day of the month. So on Sept 27 this will be change to "Oct-2013". Evry 27th day will be the cut off date for the month. How will i do this in SSRS? When i tried this in my expression upon reaching the September , the result display Oct-2013 
=MonthName(Month(DateSerial(Year(Now), Month(Now)+1, 27)),3)&
"-"&Year(Today)

Here is what my SSRS looks:

From Date: 9/1/2013 To date:9/2/2013
FromTime: 6:00AM Totime: 6:00AM
OrderMonth: Sep-2013

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 10:07:27

=IIF(Day(Now()) > 27, MonthName(Month(Now())+1),MonthName(Month(Now())))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-09-03 : 08:25:03

Thanks Visakh its working now. just made some adjustment.

=IIF(Day(Now()) > 26,MonthName(Month(Now())+1,3)&"-"&Year(now()),MonthName(Month(Now()),3)&"-"&Year(now()))

Btw, Its possible to use case statement to loop the dates from Where clause instead of using while and Begin statement.
I need to work on if the current dates falls in Monday need to less 1 day else no changes from the date.
Or it possible to check if no records extracted and inserted to temporary table then need to less 1 day from the date.

below is my query.

see below sample:

From Date: 9/1/2013 To date:9/2/2013
FromTime: 6:00AM Totime: 6:00AM
OrderMonth: Sep-2013

 
SELECT *
FROM DATA1 PR1
---Loop back this date if there's no record found from Query, sunday has no transaction
--or use case statement

WHERE ??? if false on monday

     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,1,@PFromDate) AND  DATEADD(DAY,1,@PToDate)

ELSE

     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,0,@PFromDate) AND  DATEADD(DAY,0,@PToDate)

ENDCASE





-----------------My Main Query--------
 --Temporary Table
IF OBJECT_ID('tempdb.dbo.#PROD') IS NOT NULL

 

  DROP TABLE [dbo].[#PROD]

        CREATE TABLE  #PROD

      (
            XWRKCTRID NVARCHAR(20)                      
             ,XCREATEDBY NVARCHAR(10)
 
          ,XASUSTATUS INT
              ,XOPRNUM INT
              ,XCREATEDDATETIME Datetime
              ,XMODEL NVARCHAR(35)
      )



--Insert records      
INSERT INTO  #PROD
(XWRKCTRID  ,XCREATEDBY
                  ,XASUSTATUS
                  ,XOPRNUM
                  ,XCREATEDDATETIME
                  ,XMODEL
)

 

--Main Query
SELECT PR1.XWRKCTRID
                  ,PR1.XCREATEDBY
                  ,PR1.XASUSTATUS
                  ,PR1.XOPRNUM
                  ,PR1.XCREATEDDATETIME
                  ,substring(PT1.XITEMID,11,CHARINDEX('-',PT1.XITEMID,11)-11) as MODEL

FROM DATA1 PR1


---Loop back this date if there's no record found from Query, sunday has no transaction

--or use case statement
WHERE ??? if false on monday
     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,1,@PFromDate) AND  DATEADD(DAY,1,@PToDate)

ELSE

     CASE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,0,@PFromDate) AND  DATEADD(DAY,0,@PToDate)

ENDCASE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-03 : 09:25:49

if the current dates falls in Monday need to less 1 day else no changes from the date

you could use a where condition like


WHERE DATEADD(HOUR,convert(int,@timezoneOffset), PR.createddatetime) BETWEEN DATEADD(DAY,SIGN(DATEDIFF(dd,0,datefield) % 7) ,@PFromDate) AND  DATEADD(DAY,SIGN(DATEDIFF(dd,0,datefield) % 7),@PToDate)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-09-04 : 02:11:24

Hi VIsakh,

From Date: 9/2/2013 To date:9/3/2013
FromTime: 6:00AM Totime: 6:00AM
OrderMonth: Sep-2013

could not understand your given solution. kindly please explain. thanks.

My requirements for this query. if today is Monday i need to go back or less of 2 day to get the saturday records.
in my sample date parameter from 9/2 this falls as Monday 6:00am which we dont have yet transaction. that'w why i would like to extract the data of Saturday 8-31.
if tuesday will get the data for monday and so on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources