Data Structure for Date Time Selection

Hi All,
We have a table which looks like the following:
ID (int)
Status (int)
Elgiblity (int)
EligibleTime (datetime)
Counter (int)
Flag (bit)
Typically our queries look like
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Since we're doing a lot of selects based on time + status codes... are
there any database structures suited for this?
Also, the values in this table are updated quite often... how do ensure
the indexes are update to date?
We load and use about 200K rows of data per day. We keep about 45 days
worth of data in the database. We would lilke to return the data as fast
as possible... does anyone have suggestions on how to store the data?
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Are you concerned about performance of the query? Do the users complain that
they have to wait for a request?
What are the inedexes defined on the table?
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
> Hi All,
> We have a table which looks like the following:
> ID (int)
> Status (int)
> Elgiblity (int)
> EligibleTime (datetime)
> Counter (int)
> Flag (bit)
> Typically our queries look like
> SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
> Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
> = 0 AND Counter < 2
> Since we're doing a lot of selects based on time + status codes... are
> there any database structures suited for this?
> Also, the values in this table are updated quite often... how do ensure
> the indexes are update to date?
> We load and use about 200K rows of data per day. We keep about 45 days
> worth of data in the database. We would lilke to return the data as fast
> as possible... does anyone have suggestions on how to store the data?
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
to auto update statistics, use database option
AUTO_UPDATE_STATISTICS
>from bol
When set to ON, existing statistics are automatically updated when the
statistics become out-of-date because the data in the tables has changed.
When set to OFF, existing statistics are not automatically updated; instead,
statistics can be manually updated. For more information, see Statistical
Information.
By default, AUTO_UPDATE_STATISTICS is set to ON.
The status of this option can be determined by examining the
IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
To know when exactly statistics are updated
DBCC SHOW_STATISTICS
To updat statistics
UPDATE STATISTICS table | view
[
index
| ( statistics_name [ ,...n ] )
]
[ WITH
[
[ FULLSCAN ]
| SAMPLE number { PERCENT | ROWS } ]
| RESAMPLE
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
]
Regards
R.D
"Uri Dimant" wrote:

> Lucas
> Are you concerned about performance of the query? Do the users complain th
at
> they have to wait for a request?
> What are the inedexes defined on the table?
>
> "Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
> news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
>
>|||SORRY AT WRONG PLACE
"R.D" wrote:
> Hi
> to auto update statistics, use database option
> AUTO_UPDATE_STATISTICS
> When set to ON, existing statistics are automatically updated when the
> statistics become out-of-date because the data in the tables has changed.
> When set to OFF, existing statistics are not automatically updated; instea
d,
> statistics can be manually updated. For more information, see Statistical
> Information.
> By default, AUTO_UPDATE_STATISTICS is set to ON.
> The status of this option can be determined by examining the
> IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
> To know when exactly statistics are updated
> DBCC SHOW_STATISTICS
> To updat statistics
> UPDATE STATISTICS table | view
> [
> index
> | ( statistics_name [ ,...n ] )
> ]
> [ WITH
> [
> [ FULLSCAN ]
> | SAMPLE number { PERCENT | ROWS } ]
> | RESAMPLE
> ]
> [ [ , ] [ ALL | COLUMNS | INDEX ]
> [ [ , ] NORECOMPUTE ]
> ]
> Regards
> R.D
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:

> Lucas
> Are you concerned about performance of the query? Do the users
> complain that they have to wait for a request?
> What are the inedexes defined on the table?
Hi,
It's an application that runs against the table... the application
places phone calls on behalf of records in the database - we need to put
out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
Right now there are a combination of factors:
1. Table Structure Could be Optimized
2. hardware could be better
As you see in my original post, the table is pretty much a flat file
(there are actually more columns - like 30+ but they're parameter
columns)... I'm just wondering if you guys have any pointers on how to
store datetime data in a way that is easily selectable.
Thanks!
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Lucas
can be bit specific
is it the problem of insertion or select statement to retrive it.
pl.Post script
Regards
R.D
"Lucas Tam" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:
>
> Hi,
> It's an application that runs against the table... the application
> places phone calls on behalf of records in the database - we need to put
> out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
> Right now there are a combination of factors:
> 1. Table Structure Could be Optimized
> 2. hardware could be better
> As you see in my original post, the table is pretty much a flat file
> (there are actually more columns - like 30+ but they're parameter
> columns)... I'm just wondering if you guys have any pointers on how to
> store datetime data in a way that is easily selectable.
> Thanks!
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
>|||examnotes <RD@.discussions.microsoft.com> wrote in
news:B0959AEA-AB2D-48A9-922D-5E0528AB5CE3@.microsoft.com:

> Lucas
> can be bit specific
> is it the problem of insertion or select statement to retrive it.
> pl.Post script
> Regards
> R.D
Problem with select statement:
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Something like that can take a while... and use a lot of resources.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Data selection not happening for a date because of time format

hello all,

i am making a query which select the data again a particuler date.

I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.

I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.

Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?

Please anyone help me!

Thanks in advance!

to get all of today's rows use this

SELECT *
FROM YourTable
WHERE YourDateColumn >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks you SQL_Menace for writing to me!

your query did exactly that I needed.

Thanks indeed!

|||

SQL_Menace, I have another question that is closely related to the previous one.

I am searching for the records between two dates.

Here is my query(modified after your reply):

SELECT * FROM Mytable WHERE(DateEntered >=DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(srchdate) + "')+0, 0) AND DateEntered <= DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(todate) + "')+0, 0))

DateEntered is my date column, and Convert.ToDateTime(srchdate), Convert.ToDateTime(todate) are the values given by the user at runtime.

Problem here is the same. I insert 10 records today(2006-07-14 18:16:33) but when I provide yesterday(2006-07-13 19:16:33) as FROM DATE and today(2006-07-14 18:16:33) as TO DATE as parameter to search beteen these two dates, query returns nothing i.e. count is zero(0).

How can I achieve this?

Thank you!

Data Report

I am creating a report using the Data Report in VB. The data is taken from two tables with a certain date criterea, that is, between two
dates. The date is formatted as MMMM yyyy. My problem is that the layout of the report does not match the data from the table. To make
my problem clear, the report layout is as follows:

Month1 Month2 Month3
Name W1 W2 W3 W4 W5 W1 W2 W3 W4 W5 W1 W2 W3 W4 W5

where W1, W2 etc are the weeks of the month.

Now, let me give you the structure of my table. It is as follows:

Name Month W1 W2 W3 W4 W5

The Query is as follows:

"SELECT DISTINCT EMPNAME, AH1, AH2, AH3, AH4, AH5 FROM STAFFMASTER, STAFFHRS WHERE " & _
"STAFFMASTER.EMPCODE=STAFFHRS.EMPCODE AND STAFFHRS.[PROJECT]='" & cbo1.Text & _
"' AND STAFFHRS.[MONTH & YEAR]>=#" & Format(DTP1.Value, "MMMM yyyy") & _
"# AND STAFFHRS.[MONTH & YEAR]<=#" & Format(DTP2.Value, "MMMM yyyy") & "#"

Now, how will I distribute the data in my report? One important thing, I am building dynamic data report. Please help.

ThanksLooks like it's more of a SQL issue and not Crystal.

Converting rows into columns is a complicated process and needs either a stored procedure or more than one sql statement.. it may need a temp table or dummy table to collect data first and then group it accordingly.

The fast forward solution is this :

.
.
.
Select space(30) "my1", space(30) "my2" , space(30) "my3", h.monthyear "my4", space(30)"my5", space(30) "my6", space(30) "my7",
space(30) "my7", space(30) "my8", space(30) "my9", space(30) "my10", space(30) "my11", space(30) "my12",
sum(h.ah1) april_Ah1, sum(h.ah2) april_Ah2, sum(h.ah3) april_Ah3, sum(h.ah4) april_Ah4, sum(h.ah5) april_Ah5,
0 may_Ah1, 0 may_Ah2, 0 may_Ah3, 0 may_Ah4, 0 may_Ah5, m.empcode
From staffmaster m, staffhrs h
where m.empcode = h.empcode
group by m.empcode, h.monthyear
having h.monthyear = "April 2004" and between (h.monthyear, start_date, end_date)
union all
Select space(30) "my1", space(30) "my2" , space(30) "my3", space(30) "my4", h.monthyear "my5", space(30) "my6", space(30) "my7",
space(30) "my7", space(30) "my8", space(30) "my9", space(30) "my10", space(30) "my11", space(30) "my12",
sum(h.ah1) april_Ah1, sum(h.ah2) april_Ah2, sum(h.ah3) april_Ah3, sum(h.ah4) april_Ah4, sum(h.ah5) april_Ah5,
sum(h.ah1) may_Ah1, sum(h.ah2) may_Ah2, sum(h.ah3) may_Ah3, sum(h.ah4) may_Ah4, sum(h.ah5) may_Ah5,m.empcode
From staffmaster m, staffhrs h
where m.empcode = h.empcode
group by m.empcode, h.monthyear
having h.monthyear = "May 2004" and between (h.monthyear, start_date, end_date)
.
.
.
for each month write a separate sql and just do union all that's going to give you data in column format : like this

my1 my2 my3 my4 my5 my6 my7 my7 my8 my9 my10 my11 my12 april_Ah1 april_Ah2 april_Ah3 april_Ah4 april_Ah5 may_Ah1 may_Ah2 may_Ah3 may_Ah4 may_Ah5 empcode
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -------
April 2004 40 80 120 160 200 0 0 0 0 0 001
May 2004 40 80 120 160 200 40 80 120 160 200 001
May 2004 40 80 120 160 200 40 80 120 160 200 002
May 2004 40 80 120 160 200 40 80 120 160 200 003

Finally in crystal report you just have to group it based on employee code so that per empoyee there would be only one record in your report.

Thanks
Guru
:wave:|||Ah! This is exactly what I wanted. But what is 'space' in the SQL. And also my1, my2, etc. Also, my table structure is as follows:

Table1:

Code
Month
AH1
AH2
AH3
AH4
AH5

Table2:

Code
Name

The retrieved recordset will be as follows:

Name
Month
AH1
AH2
AH3
AH4
AH5

The report I wish to display is as follows:

Name Month1 AH1 AH2 AH3 AH4 AH5 Month2 AH2 AH3 AH4 AH5

I hope the SQL that you mentioned will display as the above.

Thanks

Thanks|||Hi,

I would like to now what database are you using. I have given you sample of MSSql server ?

Also what do u store in month field ? Just give an actual example because you want to retrieve it based on dates ? Also are you trying to retrieve only one yrs. data or multiple yrs ?

Thanks
Guru
:confused:|||Just wondering how can you display 72 fields in one line. For each month you need to display this info in one line is that true ?

Are you trying to print list of all employees and man hrs worked ?

Thanks

:(|||Originally posted by dilemma
Hi,

I would like to now what database are you using. I have given you sample of MSSql server ?

Also what do u store in month field ? Just give an actual example because you want to retrieve it based on dates ? Also are you trying to retrieve only one yrs. data or multiple yrs ?

Thanks
Guru
:confused:

I use MS Access.

In month field I store the date formatted as "MMMM yyyy".

Example:

Name Month_Year W1 W2 W3 W4 W5
John May 2004 40 40 40 40 24

The user wants to display in the record, the data of the month he picks from a DateTimePicker control and two months ahead of that month. So, for example, if he picks December 2004, then the data of January 2005 and February 2005 should also be displayed.

Thanks|||Originally posted by dilemma
Just wondering how can you display 72 fields in one line. For each month you need to display this info in one line is that true ?

Are you trying to print list of all employees and man hrs worked ?

Thanks

:(

Not 72 fields. There will be only 19 fields, that is, the Name field is displayed only once.

That's right. All employees working on a particular project and the man hours assigned to them. I am attaching a picture of the report how it should be displayed.

Thanks|||Looking at the complexity of your problem If I were you I would do this. Assuming you are planning to create report in Crystal, create report with 3 sub reports for each month. 1 for Current month. 2 for II'nd month. 3 for III'rd month. Add these all 3 reports in one report as a subreport. They each will have different where criteria . eg the current month report would have criteria to fetch only current months data and similarly II'nd one retrieves only II'nd months data and III'rd guy retrieves III'rd months data.

Thanks
:wave:|||That sounds interesting. I am totally new to Crystal Reports. Can you help me on how to create the crystal subreport?

Thanks|||It's pretty easy. Create 3 reports to retrieve records for each month based on appropriate criteria. ONce done create 4'th report and add these 3 reports as subreports using crystal's subreporting features. Input parameter of these reports would be date.

Creation of reports in Crystal is also fairly easy. Just go through help. It has pretty good information.

Thanks|||Thanks for your reply.

Mine is a multiuser system. The database is going to be in the network. So I perfer using the .Dsr as report and not the .rpt. Also, should I use the wizard or should I go for runtime reports?

Thanks.|||I am sorry. I do not know what .Dsr is ? You could use wizard to create crystal report if you are a beginner. I didn't get last part runtime reports ? Doesn't matter even if you have multiuser environment. There is a parameter to open report in exclusive mode or temp copy mode. In your case you would be opening your report in temporary mode. That's it.

Thanks
Sanjay|||.Drs is a report created using the RDC. I have read some articles on Crystal Reporting that the .rpt files increases the size of the compiled program.

Is the parameter to open the report in exclusive mode set inside the crystal report application or is set within the vb code?

Thanks|||There is no doubt that .rpt files increase the size of the package. To open report in temp mode or exclusive mode you need to add something called OpenReport. The syntax is :

Function OpenReport (pFileName As String, [OpenMethod]) As Report

Where,
The OpenMethod parameter can take the values "0" (open by default) or "1" (open temporary copy).

If you do not provide this parameter the report is opened exclusively and it cannot be opened a second time

Thanks|||Ah! That is a very good information. So you suggest that I use .rpt? Thanks for the info.

Thankssql

data recovery

Hello All,
Is there a way to restore/recover data from an earlier date if you donot have a backup. The data was accidentally deleted from a table.
Thanks in advance
Hugo
Unfortunately, if you do not have a backup (and more importantly are using transaction log backups (to be able to restore to a specific point-in-time)), then unfortunately there is not a way to recover the database back to it's state before the deletion.
|||I wrote an article on this topic a couple of days ago:
http://www.karaszi.com/sqlserver/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"hugo" <anonymous@.discussions.microsoft.com> wrote in message
news:249BA122-C703-4B60-8695-16D7A01EBD82@.microsoft.com...
> Hello All,
> Is there a way to restore/recover data from an earlier date if you donot have a backup. The data was
accidentally deleted from a table.
> Thanks in advance
> Hugo
>
sql

data recovery

Hello All
Is there a way to restore/recover data from an earlier date if you donot have a backup. The data was accidentally deleted from a table
Thanks in advanc
HugUnfortunately, if you do not have a backup (and more importantly are using transaction log backups (to be able to restore to a specific point-in-time)), then unfortunately there is not a way to recover the database back to it's state before the deletion.|||I wrote an article on this topic a couple of days ago:
http://www.karaszi.com/sqlserver/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"hugo" <anonymous@.discussions.microsoft.com> wrote in message
news:249BA122-C703-4B60-8695-16D7A01EBD82@.microsoft.com...
> Hello All,
> Is there a way to restore/recover data from an earlier date if you donot have a backup. The data was
accidentally deleted from a table.
> Thanks in advance
> Hugo
>

data recovery

Hello All,
Is there a way to restore/recover data from an earlier date if you donot hav
e a backup. The data was accidentally deleted from a table.
Thanks in advance
HugoUnfortunately, if you do not have a backup (and more importantly are using t
ransaction log backups (to be able to restore to a specific point-in-time)),
then unfortunately there is not a way to recover the database back to it's
state before the deletion.|||I wrote an article on this topic a couple of days ago:
http://www.karaszi.com/sqlserver/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"hugo" <anonymous@.discussions.microsoft.com> wrote in message
news:249BA122-C703-4B60-8695-16D7A01EBD82@.microsoft.com...
> Hello All,
> Is there a way to restore/recover data from an earlier date if you donot have a ba
ckup. The data was
accidentally deleted from a table.
> Thanks in advance
> Hugo
>

Data Parameter Question

Hi All:
Reporting Services newbie here. I have a query that has a fieldcalled Date. I have created two parameters in my report namedStart Date and End Date.
I need to know the proper syntax to build an expression that willreturn a recordset that contains all records with dates between mystart and end dates.
How do I accomplish this? And also, does anyone know of aresource anywhere that lays out examples of the syntax for variousexpressions used in Reporting Services. The help documentationisn't very clear.
Thanks
Brennan

What you need involve Time Interval and it is not implemented in SQL Server now but I found a UDF(User Defined Function) that can do it. Use it and you will get what you need, I would also look at the stored procs in the Time Tracker starter kit it will have time interval cals. Try the link below for the code. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||you can either call a stored proc that takes the startdate and enddate as parameters and gives you the result set or you can get the entire result set from the table and use filters to filter out the data between the dates. I'd recommend using a stored proc.|||

I am not sure if this is what you need but like most suggest you should use a stored proceedure but the syntax for the query would look like:

SELECT column_name FROM table_nameWHERE column_nameBETWEEN @.start_dateAND @.end_date

You can just try this directly in the report designer before making a stored procedure for it.

Note: @.start_date and @.end_date will be the value the user put in the parameter box.

|||Hi,
I have a similar question as you do, in fact I'm tryning to filter my report results between two dates, and I'm trying to do this directly in the Report designer using two report parameters, for the begin and for the end date. My problem is that the data type available for date parameters is "Date Time", and I want my parameter formated as a Date ( I don′t want to do it on the query because the parameter mus be shown as a date in the report) .Is there a way of doing it? Or I must set the parameters as strings, and cast them in the query? If so, how can I send an error message if the dates are not well fomated?
Thanks in advance for any help!
|||I didnt understand your question. Can you rephrase ?|||

Yes, what I wrote before was a little bit confuse,

In fact I have 2 different questions:

The first one is, can I have a report parameter configured as a Date instead of Date Time? (the data types available for the report parameters don't include the type Date).
The second question is, how can I send a pop-up with an error message in my report ? For example, if the parameter value inserted by the user is not valid for my report.

Thanks,
Cat.

|||

Hello:
Yes, just CAST the datetime value provided by the parameter within RS within SQL to the date - but thre is more to it than that!
The date passed as a parameter could be in any number of many formats - YYYY.DD/DD or YYYY-MM-DD, etc.
The dates based upon the culture or the dates stored within the data base!
This "date" thing drives me nuts at times in that I have to support Thai (which their year is 543 years ahead of the western calendar) but this is what I do and this may or may not be of help.
In my SQL data base I have dates that are in YYYY-MM-DD Hour:Minute:Sec, YYYYMMDD, MM/DD/YY, MM/DD/YYYY, DD/MM/YYYY, DD/MM/YY, etc. I did not create this mess but inherited it from programmers who developed the system in Thailand and Korea which I used the data within this database for another application -- I get really frustrated at times.
So, I define all date parameters in RS as strings. When an RS report is launched and the date parameter is displayed I provide a drop down list of available parameters which are:
NONE, Today, Yesterday, Beginning of Week, BI -Weekly, Beginning of Month, Monthly, Bi-Monthly, Quarterly, Yearly not in this particular order but nevertheless.
If they select NONE - I provide and additional two(2) parameters which allow them to enter the date in YYYYMMDD format.
I have also created a table in SQL called a Calendar Time Dimension (which is mandatory for Analysis Services).
The Calendar Time Dimension has a day for each day of the year and I have populated for 1995 through 2015.
The Calendar Time Dimension file has for each date within the year(s) that I populate the following:
Time Key which has a value for (eg. todays date 20050904) in the following formats:
YYYYMMDD, YYMMDD, MMDDYY, MMDDYYYY, DDMMYY, DDMMYYYY, MM/DD/YYYY, DD/MM/YYYY, including the Thai date which ifor today i s 20050904 + 543 (years) = 25480904, etc. I have all of the weirdo formats defined.
Also in the Calendar Time Dimension is the Calendar Week, Calendar Quarter, Fiscal Quarter, Month of Year, Week within Month, etc.
So when a user enters their own date via the date parameter (and elects not to select the any other parameter such as Today) within RS (a call to a stored procedure) I validate against the Calendar Time Dimension and if the date is invalid I provide back an error message via RS that indicates the date is invalid.
So my point (even though LOOONG WINDED) is that you should consider to manipulate the dates within a stored procedure based upon anything entered by the user within the date parameter within RS and validate against a similar Calendar Time Dimension table within the data base. So when a date is valid you use a JOIN to the Calendar Time Dimension to filter out the records you want within SQL. Another added benefit is that this really improves performance in that you are not filtering or comparing dates within each record within the DB.
If the user selects TODAY, Yesterday, Weekly etc. I have UDF's that generate the date ranges I use against the Calendar Time Dimension to process only the records I need against the table I go after in the Stored Procedure for the respective RS program.
If you want I can give you a stored procedure to create the Calendar TIme Dimension and then you can go from there.
Best Regards,

|||Thanks a lot for your post!
Cat.

Data of different dates

I want to make a report which is using parameter field of a single date. But the same time I have some fields those have to show some data out of the parameter field. And the grouping is same.

For example, a coloum show data of the date which is slected but another field have to show data of the current month but Less and equal to the selected field.

ThankyouYour best bet is to create one as a subreport to the other. These 2 reports link by the parameter field, but use different filter.

That's all I can say with the info you provide.

Good luck.

Data Issues

I have a static report that has 2 parameters begin date and end date. The
report has been running fine for several months but today we had a major data
problem. The query returns dollar amounts based on sales, When running the
report for 12/29/204 to 1/5/05 we received 3 million in sales, which we knew
was incorrect. We then queried the tables directly through PL/SQL developer
and came up with the correct numbers. We then stopped and started the
reporting service's service and reposted the report but we came back with the
bad sales number. Out of curiosity I ran the report for dates in the future
such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
checked the tables were hitting and of course their was no data. This was all
in the morning. I just ran the report again this afternoon and now it is
correct. Does anyone have a clue as to why this might be happening?Is the report cached or run on a schedule?..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zach" <Zach@.discussions.microsoft.com> wrote in message
news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
> I have a static report that has 2 parameters begin date and end date.
The
> report has been running fine for several months but today we had a major
data
> problem. The query returns dollar amounts based on sales, When running
the
> report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
knew
> was incorrect. We then queried the tables directly through PL/SQL
developer
> and came up with the correct numbers. We then stopped and started the
> reporting service's service and reposted the report but we came back with
the
> bad sales number. Out of curiosity I ran the report for dates in the
future
> such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> checked the tables were hitting and of course their was no data. This was
all
> in the morning. I just ran the report again this afternoon and now it is
> correct. Does anyone have a clue as to why this might be happening?|||No. The end user just clicks on the report and keys in the begin and end
dates.
"Wayne Snyder" wrote:
> Is the report cached or run on a schedule?..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Zach" <Zach@.discussions.microsoft.com> wrote in message
> news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
> > I have a static report that has 2 parameters begin date and end date.
> The
> > report has been running fine for several months but today we had a major
> data
> > problem. The query returns dollar amounts based on sales, When running
> the
> > report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
> knew
> > was incorrect. We then queried the tables directly through PL/SQL
> developer
> > and came up with the correct numbers. We then stopped and started the
> > reporting service's service and reposted the report but we came back with
> the
> > bad sales number. Out of curiosity I ran the report for dates in the
> future
> > such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> > checked the tables were hitting and of course their was no data. This was
> all
> > in the morning. I just ran the report again this afternoon and now it is
> > correct. Does anyone have a clue as to why this might be happening?
>
>|||It's that pesky Y2K005 issue...
Can you run a trace of the SQL activity, and check what is being passed as
parameters? Are the dates being processed as strings?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Zach" <Zach@.discussions.microsoft.com> wrote in message
news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
>I have a static report that has 2 parameters begin date and end date.
>The
> report has been running fine for several months but today we had a major
> data
> problem. The query returns dollar amounts based on sales, When running
> the
> report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
> knew
> was incorrect. We then queried the tables directly through PL/SQL
> developer
> and came up with the correct numbers. We then stopped and started the
> reporting service's service and reposted the report but we came back with
> the
> bad sales number. Out of curiosity I ran the report for dates in the
> future
> such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> checked the tables were hitting and of course their was no data. This was
> all
> in the morning. I just ran the report again this afternoon and now it is
> correct. Does anyone have a clue as to why this might be happening?|||I just took a look at your code in the other thread. You're creating
heartache for yourself by breaking apart the dates. Wayne gave you good
advice about using the DateAdd function.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Zach" <Zach@.discussions.microsoft.com> wrote in message
news:391A179E-B8F2-4E62-81D6-7FF4ED92C9D1@.microsoft.com...
>I have a static report that has 2 parameters begin date and end date.
>The
> report has been running fine for several months but today we had a major
> data
> problem. The query returns dollar amounts based on sales, When running
> the
> report for 12/29/204 to 1/5/05 we received 3 million in sales, which we
> knew
> was incorrect. We then queried the tables directly through PL/SQL
> developer
> and came up with the correct numbers. We then stopped and started the
> reporting service's service and reposted the report but we came back with
> the
> bad sales number. Out of curiosity I ran the report for dates in the
> future
> such as 1/10/05, 1/15/05 etc. and came back with sales numbers. Again I
> checked the tables were hitting and of course their was no data. This was
> all
> in the morning. I just ran the report again this afternoon and now it is
> correct. Does anyone have a clue as to why this might be happening?

data from 2 datasets in one table

i have 2 datasets in my report.

dataset1 brings back:

date shirts pants

4/1 3 5

4/2 4 6

4/3 2 9

dataset2 brings back:

date shoes boots

4/1 7 4

4/2 3 2

4/3 9 8

i want to to have a report that shows:

date shirts pants shoes boots

4/1 3 5 7 4

4/2 4 6 3 2

4/3 2 9 9 8

when i added a table in the layout tab, and drag in fields from the 2 datasets, for the dataset1, i would get fields like "=Fields!shirts.Value". but, when i drag in fields from dataset2, it shows up like "=Sum(Fields!shoes.Value, "dataset1")".

i don't want the sum, i just want the value. when i take out the "sum" part of the field expression, i get an error that says:

[rsFieldReference] The Value expression for the textbox ‘shoes_1’ refers to the field ‘shoes’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

any advice?

thank you much :-)

I'm not sure about combining the datasets, but could you instead return the data in a single dataset, adding a column for the garment type (pants, shoes)? Then you could use a matrix to display your results.

Simone

|||i can't combine the datasets into a single dataset b/c the data comes from 2 different databases/data sources.|||

Joining of datasets within RS2005 is not supported. See the following message link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=218824&SiteID=1

What is your databasource type (SQL Server?).

|||yes, dataset1 data comes from database1 from sql server and dataset2 data comes from database2 also from sql server. thanks.|||

I'm not sure how much control (or what permissions) you have on your server, but you can join tables across databases to create a single result set. You need to specify the database name in the join:

Code Snippet

select * from database1.dbo.tableA

union

select * from database2.dbo.tableB

Simone

|||yeah, i had a feeling you were going down that route. unfortunately, the two databases are on different servers and the dba has no intention of creating a link server. thanks for your help, though!

Data Formating for export

I need to have a date that is exporting as this 2005-05-18 to export as
5/18/2005.
Is there anyway to write this into the query?I figured out my own answer CONVERT(char(10), DATE , 101)
"Diane" wrote:

> I need to have a date that is exporting as this 2005-05-18 to export as
> 5/18/2005.
> Is there anyway to write this into the query?|||convert(varchar(10),'2005-05-18',101)
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Diane" wrote:

> I need to have a date that is exporting as this 2005-05-18 to export as
> 5/18/2005.
> Is there anyway to write this into the query?