Date Parameter Issue When Created From Query

Aug 27, 2007

I have a date parameter for a report that is intended to search 'within the last X week/month...'. The SQL for this is basically WHERE [datefield] >= @dateparam AND [datefield] <= GETDATE().

The @dateparam is defined in the report as 'From query', the value field is 'thedate', and the label field is 'thetext', and the dataset is:





Date Query dataset


SELECT GETDATE()-7 as thedate, '1 Week' as thetext, 1 as theorder
UNION
SELECT GETDATE()-14 as thedate,'2 Weeks' as thetext, 2 as theorder
UNION
SELECT GETDATE()-21 as thedate,'3 Weeks' as thetext, 3 as theorder
UNION
SELECT GETDATE()-31 as thedate,'1 Month' as thetext, 4 as theorder
UNION
SELECT GETDATE()-62 as thedate,'2 Months' as thetext, 5 as theorder
UNION
SELECT GETDATE()-(31*3) as thedate,'3 Months' as thetext, 6 as theorder
UNION
SELECT GETDATE()-(31*6) as thedate,'6 Months' as thetext, 7 as theorder
UNION
SELECT GETDATE()-365 as thedate,'1 Year' as thetext, 8 as theorder
ORDER BY 3
When I go to run the report and selecting a value for the date parameter, there is never any output, and the data selection combo box just gets reset to show <select a value>.

What am I doing wrong?

View 3 Replies


ADVERTISEMENT

Using A Dynamically Created Parameter In Another Dataset Query

Jul 27, 2007



It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:

reportMonth - Just a non-queried list of months for the user to select
reportYear - Generated using the following query from dataset "Years":

declare @curYear int
set @curYear = 2000
declare @yearTable table (repYear int not null)
while @curYear <= year(getdate())
begin
insert into @yearTable(repYear) values (@curYear)
set @curYear = @curYear + 1
end
select * from @yearTable

This generated a list like:

2000
2001
2002
etc.

I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.

Thanks

View 10 Replies View Related

Date Query Parameter

Feb 19, 2007

Hi There,

I've only just starting working with RSS2005. I've started designing basic reports that pulls info from our HEAT DB (incident/change logging system).

One of the report requirements is to be able to pull reports for calls that were logged for specific day/date, using the calllog.recvddate field in the HEAT DB.

What i would like to know is how do i do this ? i.e. how do i setup the date query parameter so that the user can select a date from the calender picker that returns all calls logged for the selected date ? How do i link the date parameter to the calllog.recdvddate field ?

Thanks,

RSS2005 NOOB

View 3 Replies View Related

Analysis :: Less Than Date Value From Parameter For MDS Query

Jun 25, 2015

I don't have much knowledge on MDX queries. Here, requirement is to get the data less than the date passed from the parameter(Date - IV Financial].[calendar YQMD].[Date]).

I am able to create the below query(through Query designer) which is giving the data EQUALS to operation. But, I need the same with LESS THAN.

SELECT NON EMPTY { [Measures].[InventTrans Quantity] } ON COLUMNS,
NON EMPTY { ([Items].[Item Id].[Item Id].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@DateIVFinancialCalendarYQMD, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( { [Inventory].[Is posted].&[Posted] } ) ON COLUMNS

[Code] ....

View 3 Replies View Related

Passing Date Parameter To Oracle Query

Aug 16, 2006

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is


WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')
When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.
But when I run the report from Preview, I get no results at all no matter what format I use in the function.
Any chance any of you have seen this and know how to work with it?

View 11 Replies View Related

Date Created Defualt Value

Jul 1, 2007

 in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!

View 2 Replies View Related

Get Created Date From A File

Apr 3, 2008

Hi Guys,
Im new using SSIS and i just created a data flow that reads from multiple flat files (with a mutipleflatfiles conn manager) and inserts the files data to an sql server (with an ole db destination)

The thing is i need to insert the created date property of each file im importing and i dont know how to do this.

I've read i could do this with a script but im not very familiar to it.

Could someone help me there?

View 2 Replies View Related

Index Created Date

Dec 10, 2007



Hi All,

Is there anyway to get the date an index was created on a table? I can see that there is a created date column on various system tables (sys.objects etc) but I can't find one related to an index.

Thanks

Rich

View 3 Replies View Related

Analysis :: How To Save Parameter From A Report Created In Browse View Under Cube

Nov 10, 2015

After connected to Analysis Services and following the path below:

Analysis Service - Databases - Cubes

I create the parameters for a custom report after right click on a specific Cube and select Browse to arrive at a view where i can define dimensions and parameters as well as drag and drop measure to the report rows and columns. The left top File bottom only give me a Save Selection or Save ALL option. However, i'm not sure where it saves it to or if it saves the settings for this custom report at all.

I pull this report monthly and it's a pain to recreate all the parameters. How to save this template for future pulls or maybe even incorporate the pull into excel for monthly refresh instead?

View 3 Replies View Related

UPDATE Only Records That Were Created After A Date

Sep 5, 2007

I have a text file that I am importing into my database.

Within this textfile there is a field named "Order_Date". Date Format is like this: 20070905

I want to run this DTS once a day, updating only the records that were created on "today's" date.

For example something like this:

UPDATE NAME
WHERE Order_Date = ????? (I want a variable that is todays date in 20070905 format)

View 7 Replies View Related

Simple Date A DB Was Created On The Server

Apr 13, 2008



Well you would think it was pretty simple. If I create a Database on Server a last week, take a backup of that database and put it on server b today, the CRDate in sysdatabases still shows the date of last week instead of today. I need to find the actual date for all 150+ servers from sql 7 - sql 2005 in our domain. Any ideas? I had heard that maybe SQL DMO had an object that might be helpfull but I have not been able to look it up anywhere or see how to use it using sp_oa procs.

Any help would be greatly appreciated.

View 3 Replies View Related

How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters

Oct 15, 2007



Hi,

I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.

My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?



Other question :

Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.


Thanks !

JF

View 7 Replies View Related

How To Automate Date & Time Stamp In A FILE Name Created Via DTS

Dec 13, 2001

Hi,

I would like to know as to how can I put a Current Date & Time stamp on a FILE NAME automatically which is created by a DTS package.

E.g. I create a file named ABC.TXT daily. How do I get this file to have the current Date stamp so that the file name is ABC121201.TXT without any user intervention.

Thanks for the help!

View 1 Replies View Related

SQL 2012 :: Database Schema Creation Date And User Who Created It

Nov 12, 2014

Query to find the date/time when a database schema was created and who created it.

View 2 Replies View Related

Analysis :: How To Sort A Year In A Dimension Date Created In SSAS

Apr 22, 2015

I create a Dimension Date using SSAS 2008 but when i execute the dimension and i go to see the result i have this result:the result is not sorted..what i need is having the result order by year i mean i have Calendrier 2020,Calendrier 2019 ...

View 6 Replies View Related

SSRS Date Parameter - Showing Date Format On Right Hand Side

Jul 24, 2007

I am having report parameter end date I am showing the default value "5/21/2007 11:59:59 PM" in the end date paramter. And also I want to show date time format "(MM/DD/YYYY HH:MMS)" in the right hand side of the parameter. How to do this?



Thank You

View 1 Replies View Related

Table , Column Created, Modified Date In Sql Server 2000 Or 2005

Oct 10, 2007



Hi

Any one please tell me is there any possible way to identify the table modified date.

I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates.
Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.

I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date
can any one help

View 8 Replies View Related

Date Parameter In SSRS - Allow Drop Down For A Date Range To Be Selected

Aug 11, 2013

Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.

I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?

View 18 Replies View Related

SQL Server 2008 :: Find Date Created And Modified Values Of All Files In A Location

Jun 11, 2015

Script to find the details of creation date and modified date of all files located in a path?

I wanna write few custom messages before I delete some files from a path.

View 9 Replies View Related

Unable To Set Current Date In Date Picker Parameter

Nov 21, 2007

Hi,
I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.

Regards
Nataraj.C



View 2 Replies View Related

Date Time Format For Date Parameter

Jan 24, 2008



Hello,

I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.

how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000

thank you
Kiran

View 5 Replies View Related

Transact SQL :: Expand Date Within Date Parameter

Jul 23, 2015

I am working into a t-sql where i need to expand the date within a date parameter. For example:

Start Date = '1/1/2014'
End Date = '12/31/2014'

I want to expand this as:

01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up to 
12/2014

View 4 Replies View Related

Get New Database Created Then Running Script To Created Tables / Relationships

Jun 29, 2015

trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication. 

E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".

From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go   CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?

View 3 Replies View Related

Condition On Query Created Field

Sep 28, 2007

Hi all,

i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice?

Thanks.

select a.vchcompanyname, count(*) as total
from company a inner join incident b
on b.iownerid = a.icompanyid
where b.iincidentcategory = 1
and a.icompanytypecode = 102165
--and total > 2 (Wont recognise Total)
group by b.iownerid, a.icompanyid, a.vchcompanyname
order by 2 desc

View 3 Replies View Related

Problem With Query On A View Created By A Union

Jun 27, 2006

Hi,

I have a view V1 created as a plain UNION from 2 tables:

SELECT * FROM T1
UNION
SELECT * FROM T2

If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned.
However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.

Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?

thx,
smiley61

View 2 Replies View Related

Using Newly Created Column For Doing Further Calculations In SAME Query

Apr 12, 2015

I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1

First 3 Columns are available within the Original Table_1

The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.

View 4 Replies View Related

Filter Dynamically Created Data Tables Using Sql Query

May 24, 2008

Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose,
I have four data tables with the same structure but records may be
different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I
want to that two records by filtering all tables and sum of rates and
want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance. 

View 2 Replies View Related

Adding Default Value To An Already Created Table Using Query Analyzer

Aug 9, 2004

Hello,

How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?

Thanks in advance,
Uday.

View 6 Replies View Related

Transact SQL :: LDAP Query Checking When Created Within Last 7 Days

Nov 6, 2015

I currently have an ldap query in a stored procedure that is working fine but is checking the 'whenCreated' attribute against a hardcoded data.

SELECT * FROM OpenQuery (
  ADSI,  
  'SELECT  whenCreated,
  whenChanged,
  telephoneNumber,

[Code] .....

How can I modify the hardcoded date (''20130101000000.0Z'') to check against current_date - 7 days?

View 7 Replies View Related

Transact SQL :: Retrieve Currently Created Date From Master Table To Load Into Parent And Child Table

May 12, 2015

In Master tabel i have these date datas

2015-05-10

2015-05-11

2015-05-12

SO when i try to load from  Master table to parent and child table i am using using expresssion like 

select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON 
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND 
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE())  and convert(date,B.Ftpdate) = convert(date,getdate()) ;

If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.

My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.

If i run this expression to remote server  i cannot change the system date in server.

while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.

What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.

View 10 Replies View Related

SQL Server 2014 :: Using Newly Created Column For Doing Further Calculations In SAME Query

Apr 12, 2015

I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.

View 8 Replies View Related

Created View With Join Query - Unable To Fetch Details

Feb 12, 2014

I created a view with a simple join query

there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as

"select orderid from joins where orderid=NULL"

Query :

create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid

View 1 Replies View Related

Stored Procedure | Selects Some Data |query The Results It Created And Output

Dec 2, 2005

I can create 1 view of a database then use that views results to query

but i want do this with a stored procedure
pass some data to it, so it selects some data

query the results it created and output this data.

ie


pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output

)


select from a database with the firstpassed value

Select *
From TableName
Where ID = @FirstValue

Then
using the results from the above select

Select *
From theResultsOfAbove
Where ID = @SecondValue

any ideas would be fabo !

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved