Date range ...

Hi,
I have to count the number of invoices between two dates (starting date and ending date).
I have a time dimension, and a another with the two dates.
I've created a measure :

Code Snippet

CREATE MEMBER [CTNAV].[Measures].[Nb]
AS count(
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Starting Date ].CurrentMember.MemberValue >=
[Time].[Date].CurrentMember.MemberValue
)
)

The problem is that if I display all the dates, each measure is at 0 but the total is ok (>0).

Sorry for my english.

Thanks for your help.

date queries

I have written a report that contains multiple queries that displays the
number of applications we submit month by month to various entities. I wrote
the report last year and have been asked to create it again this year.
I know that this is going to be require every year, and I don't want to
write it again.
My problem is that in order to get the month by month data, I had to write
"WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
tDocHistory.ChangeDate)), CONVERT(CHAR(4),
DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
I would like to be able to allow my users to select the year they wish to
view.
I wanted to replace the 2008 with % in the statement:
(tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
and then filter the datepart for the year by writing:
HAVING (tDocHistory.NewStatus = 'wv' OR
tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
tDocHistory.ChangeDate))
LIKE @.Year)
Do you know any way I could make this work?
--
SamyraWhy dont you use report parameter to select the date.
On Jan 12, 8:28=A0pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> I have written a report that contains multiple queries that displays the
> number of applications we submit month by month to various entities. =A0I =wrote
> the report last year and have been asked to create it again this year.
> I know that this is going to be require every year, and I don't want to
> write it again.
> My problem is that in order to get the month by month data, I had to =A0wr=ite
> "WHERE =A0 =A0 (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.ChangeDate)), CONV=ERT(CHAR(4),
> DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> I would like to be able to allow my users to select the year they wish to
> view.
> I wanted to replace the 2008 with % in the statement:
> =A0 (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> and then filter the datepart for the year by writing:
> HAVING =A0 =A0 =A0(tDocHistory.NewStatus =3D 'wv' OR
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.NewStatus =3D 'dcs=') AND (tCompany.CoName
> LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> tDocHistory.ChangeDate))
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LIKE @.Year)
> Do you know any way I could make this work?
> --
> Samyra|||I would love to, but the report is built with text boxes with a separate
query in each box.
I am looking at a table that contains historical data for each record in the
database, not current data, so I wasn't able to build a table or a matrix
report that would display the data they wanted to see.
--
Samyra
"Sridar K" wrote:
> Why dont you use report parameter to select the date.
> On Jan 12, 8:28 pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> > I have written a report that contains multiple queries that displays the
> > number of applications we submit month by month to various entities. I wrote
> > the report last year and have been asked to create it again this year.
> >
> > I know that this is going to be require every year, and I don't want to
> > write it again.
> >
> > My problem is that in order to get the month by month data, I had to write
> >
> > "WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> > GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> > tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> > tDocHistory.ChangeDate)), CONVERT(CHAR(4),
> > DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> >
> > I would like to be able to allow my users to select the year they wish to
> > view.
> >
> > I wanted to replace the 2008 with % in the statement:
> > (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> >
> > and then filter the datepart for the year by writing:
> >
> > HAVING (tDocHistory.NewStatus = 'wv' OR
> > tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
> > LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> > tDocHistory.ChangeDate))
> > LIKE @.Year)
> >
> > Do you know any way I could make this work?
> >
> > --
> > Samyra
>

Date function

Is it possible to retrive the number of days between two dates.
I'm aware of the DateDiff function but I can't figure out how to calculate the number days between two columns containing dates in a table.
All help is welcome
Regrads OISselect datediff (dd,col1,col2) from your table|||declare @.fd1 datetime, @.fd2 datetime

set @.fd1 = '20040513'
set @.fd2 = getdate()

select datediff(d, @.fd1, @.fd2)|||It works perfect :)

Thanks alot both of you|||Try to use BOL next time. Your example described in detail

Date format

I am trying to convert a number string into a date. I am using:
to_date(settdate,'yyyy/mm/dd') "New date"
in my select statement. I get the error "input value not long enough for date format"
Why do I get the error?
Thanks.You are talking about "number string". Is your variable "settdate" a Julian date ? Can you give us an example value ?

This works for me :

declare
a varchar2(100);
b date;
begin
a := '2450450';
b := to_date(a,'J');
dbms_output.put_line(to_char(b,'DD(MM/YYYY'));
end;
/

SQL>
01/01/1997

SQL>PL/SQL procedure successfully completed.

Date dimension Sort order

Hi-

I have a date dimension with year (number), month(Month name), and day(number) as part of a cube which I'm generating an offline cube from.

While browsing the cube in either VS or in the offline cube, through Excel, the months end up sorted alphabetically (April, August...) and the days of the month are sorted in the fashion of 1,10,11...2,20,21...

Anyone have any suggestions or ideas of what I'm doing wrong?

Thanks,

Tristan

You should mark your days as 01, 02, 3... to avoid the order issue you are seeing.

For the month, you should create a Month of year attribute (Jan - 01, Feb - 02, etc...) and then use the sort property ont he month attribute to sort it by the Month of Year attribute....

|||That worked perfect! Thanks much!

Date difference in months

Hi to all,

I want to make an sql query that will subtract two dates and the difference will be number in months.

i have this table TestTable with Fields ACost, Adate, AMonth

my formula is this X = (Now-Adate), should give me number in months
and Y = (ACost/AMonth)
i want to get the product of X and Y, (X*Y).

how can i make this in sql views?

can anyone help me with this?

thanks a lot!

best regards,

saldiboyhow about

create view testview as

select ((datediff(m,Adate, getdate())) * (ACost/Amonth)) as val from testTable|||you're genius man!

thanks!|||Just beware that DateDiff() actually counts boundaries, not units. So for example there is a month between 2006-02-28 and 2006-03-02, which occaisionally surprises folks.

-PatP

Date Diff

I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1
Syntax error converting date time from character string". can someone please help.

SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime),
CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime))
AS StopMinutes,
BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA,
BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME,
OQTCOD, BAADES, PCS, WGT, Tractor, Driver
FROM dbo.JCI_Delivery_Report

Make sure you are constructing the datetime string correctly. You have to make sure the string you are constructing is a string SQL Server understands as a datetime; otherwise you're going to get a casting error.

German Afanador

|||Can you explain what you are trying to do?|||

SUBSTRING(SCHTIME, 3,4) should be SUBSTRING(SCHTIME, 3,2)

SUBSTRING(ACTTIME, 3,4) should be SUBSTRING(ACTTIME, 3,2)

and while I think it's bad that you've created text fields in your database for dates and times instead of storing them as they should be (in datetime format), this will get you closer to what you want.

Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
OwenHi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegroups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards

Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
OwenHi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegroups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards

Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
Owen
Hi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegro ups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>
|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards