Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)
did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear examnotes <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:

> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>

Easy Way Determine Hourly Integer Value from Field DateTime Format

What is an easy way to determine Hourly Integer value from a field with
datetime format.
Below is what I created which is not that simple.
Thanks,
declare @.var2 varchar(24)
declare @.var3 varchar(24)
select @.var2 = convert(varchar(16), getdate(),8)
select @.var3 = substring(@.var2,1,2)did you hear =?Utf-8?B?Sm9lIEsu?= <Joe K.@.discussions.microsoft.com> say
in news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com:
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>
select datepart (hh, getdate())
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Joe
select cast(convert(char(2),getdate(),114)as int)
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8C95E267-7405-4A02-9028-0DA11A049BD8@.microsoft.com...
> What is an easy way to determine Hourly Integer value from a field with
> datetime format.
> Below is what I created which is not that simple.
> Thanks,
> declare @.var2 varchar(24)
> declare @.var3 varchar(24)
> select @.var2 = convert(varchar(16), getdate(),8)
> select @.var3 = substring(@.var2,1,2)
>sql

Easy question

How do i format a date which is 9/1/2004 as Sept 04

I have tried giving

Format(Fields!GrowthDates.Value,"Y")

but i just get Y and not dates.

Thanks

Regards,

Karen

= Month(Fields!GrowthDates.Value) & " " & Year(Fields!GrowthDates.Value)

Easy question, easy answer. You want to put this in the format expression.

|||

Date.Now.ToString("MMM yy")

|||

Greg,

When i use that expression for 08/01/2004 its give 08 2004 and not Aug 2004 ,,, how can i get it to show Aug 2004

|||

I've never been able to get this function to work, but you would use MonthName. It always returns strange data for me. Maybe someone else can clear this up...

= MonthName(Month(Fields!GrowthDates.Value)) & "-" & Year(Fields!GrowthDates.Value)

Instead of September, I get SepAe0ber 2006

For October, I get OcAober

November - Nove0ber

Strange, huh?

|||

Greg i got it to work

=MonthName(Month(Fields!GrowthDates.Value),true)& " " & Year(Fields!GrowthDates.Value)

True = Abbreviate.

so if my data 08/01/2004 its gonna display Aug 2004

Hope this helps

Regards,

Karen

|||

Why dont you try ="MMM yyyy" in format expression.

Priyank

|||

I tried that in the report but on my X axis instead of the date i get MMM yyy which is of new use.

|||

I get bad abbreviations with that. Some are good but some are still strange...

August - AuA.D.

October OcA

It's almost as if the definitions for the months on my installation of reporting services are wrong.

|||

Priyank Pandey wrote:

Why dont you try ="MMM yyyy" in format expression.

Priyank

That did work, note the case. Thanks!

|||

Its showing up correct on mine....

Regards

Karen

|||

Can you pls mark it as ans.

Thanks!

Easy One

Hi,
I need to format part of an expression as money. The expression is ="Total
Aged Balance : " & SUM(Fields!Invoice_Total.Value).
How do I do that?
Thanks
JerryOk...got it...FORMATCURRENCY.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23aV%23sWuvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I need to format part of an expression as money. The expression is
> ="Total Aged Balance : " & SUM(Fields!Invoice_Total.Value).
> How do I do that?
> Thanks
> Jerry
>

Easy Convert Question

Will the following SQL allow me to see the results of the computed column in
decimal format even if the Columns used in the calculation are defind as int
:
Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
I have used it but I am not getting any decimal places. It is showing the
decimals as 0. If this is not the problem could you please make some
suggestions.
Thanks
AdamTry multiplying the arguments by 1.0, or explicitly converting the arguments
to decimal.
By arguments, I mean the individual pieces, not the result of the
calculation.
A
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:836A42B5-D2D1-480B-82A5-A167007B0123@.microsoft.com...
> Will the following SQL allow me to see the results of the computed column
> in
> decimal format even if the Columns used in the calculation are defind as
> int:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards]))
> 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||You have to change the formula instead.
((#Operator2.[Count] * 1000.00)/#Closed2.[Dyed Yards])
AMB
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||Can you try this:
Convert(Dec(10,5), ((#Operator2.[Count]*1000.0)/#Closed2.[Dyed Yards]))
'Count'
Perayu
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||How about something like this
select price/cast(code as money) from xyz
i.e. the denominator gets converted to 'money' type :)
Cheers,
JP (Just a programmer:))
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O515emXqFHA.2652@.tk2msftngp13.phx.gbl...
> Try multiplying the arguments by 1.0, or explicitly converting the
> arguments to decimal.
> By arguments, I mean the individual pieces, not the result of the
> calculation.
> A
>
>
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:836A42B5-D2D1-480B-82A5-A167007B0123@.microsoft.com...
>|||Thanks guys, it worked
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam|||OR
select price/cast(code as decimal(10,4)) from xyz
Cheers,
JP (Just a Programmer;))
--
"JP" <someone@.somewhere.com> wrote in message
news:ucbhzwXqFHA.3104@.TK2MSFTNGP12.phx.gbl...
> How about something like this
> select price/cast(code as money) from xyz
> i.e. the denominator gets converted to 'money' type :)
> Cheers,
> JP (Just a programmer:))
> --
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O515emXqFHA.2652@.tk2msftngp13.phx.gbl...
>|||hi AB
you can try it as:
#Operator2.[Count]*1000.0)/#Closed2.[Dyed Yards] as [count]
hope this will help u
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"A.B." wrote:

> Will the following SQL allow me to see the results of the computed column
in
> decimal format even if the Columns used in the calculation are defind as i
nt:
> Convert(Dec(10,5), ((#Operator2.[Count]*1000)/#Closed2.[Dyed Yards])) 'Count'
> I have used it but I am not getting any decimal places. It is showing the
> decimals as 0. If this is not the problem could you please make some
> suggestions.
> Thanks
> Adam

Dynamically changing position of Textbox

Hi,

In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.

Thanks in Advance,

Leks

What does the surrounding content in the layout look like? Do you have a "clear" region in which you could place a very large textbox, representing the all the page area in which the textbox could potentially be placed?

If so, you might be able to do this with padding. IOW, you would place this very large textbox in the layout and then you would pad the top and left to "push" the content of the text box into the customer's preferred position, at runtime.

This is possible because you can express the padding values as expressions. Think of the textbox's true top and left as an offset from 0,0, which you will subtract from the customer's input values (which will be parameters) to get the padded top and left at runtime.

HTH,

>L<

|||

Hi Lisa,

Thanks for your quick reply. But unfortunately my layout is not clear. It contains other data too that I need to position according to the input for them. For each item in the report we have to enter the position. I know this process is a bit tedious. but there is no other ways. They actualy need the report data such that it can be printed in many forms. Please reply as soon as possible.

Thank you dear,

Leks

|||

Hi Leks,

If you have to enter the position of each item in the report, then I think your best option is to edit the RDL on the fly, put your coordinates right in the report definition.

Are you comfortable with doing this? It may be quite straightward. Some things that would enter into it and might change what you have to do would include:

* -- What type of data region are you using (list/table) to contain the dynamic items?

* -- Are there additional items in the report that are *not* dynamically positioned, for example is this an order header you are trying to position so that it fits in a window envelope with a table of order lines that is relatively static?

>L<

|||

Hi Lisa,

Here all the data should change position dynamically. So Your solution shoud work. But I didnt get it. How we can change the rdl on the fly?

Thanking you,

Leks

|||

You can write a whole RDL on the fly if you want to. Look for an example called something like "generate RDL dynamically" on http://gotreportviewer.com/ and there is another example there that will show you how to LoadReportDefinition for a local report from a stream. If the report is a serverreport, you could use the SOAP API to upload the adjusted report definition instead.

In your case, I don't think that it will be necessary to write the whole RDL from scratch (although you will still have to load or upload your revised version). You can have a "template" RDL that you adjust, for each run or each customer, or as needed.

There are two ways to do an RDL template revision, the way I would do it and the way most developers would prefer to do it <g>. I prefer XSLT; in this case I might have a parameter in the XSLT for each positionable item in the report, or pass the entire set of adjustments in the form of a lookup document. If you prefer to load up the RDL in the DOM and select the textboxes as nodes to adjust each of them, this is pretty easy too.

Do you need more help than this, or is this what you're looking for?

>L<

|||

Hi Lisa,

Yes. This was what I was looking for. Now I know that using the XSLT we can position the textbox or any control dynamically.

Thank you so much Lisa,

Leks

|||

Great -- if you get stuck, holler. I am very willing to go the extra mile for other folks who like XSLT <s>.

>L<

Dynamically changing position of Textbox

Hi,

In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.

Thanks in Advance,

Leks

What does the surrounding content in the layout look like? Do you have a "clear" region in which you could place a very large textbox, representing the all the page area in which the textbox could potentially be placed?

If so, you might be able to do this with padding. IOW, you would place this very large textbox in the layout and then you would pad the top and left to "push" the content of the text box into the customer's preferred position, at runtime.

This is possible because you can express the padding values as expressions. Think of the textbox's true top and left as an offset from 0,0, which you will subtract from the customer's input values (which will be parameters) to get the padded top and left at runtime.

HTH,

>L<

|||

Hi Lisa,

Thanks for your quick reply. But unfortunately my layout is not clear. It contains other data too that I need to position according to the input for them. For each item in the report we have to enter the position. I know this process is a bit tedious. but there is no other ways. They actualy need the report data such that it can be printed in many forms. Please reply as soon as possible.

Thank you dear,

Leks

|||

Hi Leks,

If you have to enter the position of each item in the report, then I think your best option is to edit the RDL on the fly, put your coordinates right in the report definition.

Are you comfortable with doing this? It may be quite straightward. Some things that would enter into it and might change what you have to do would include:

* -- What type of data region are you using (list/table) to contain the dynamic items?

* -- Are there additional items in the report that are *not* dynamically positioned, for example is this an order header you are trying to position so that it fits in a window envelope with a table of order lines that is relatively static?

>L<

|||

Hi Lisa,

Here all the data should change position dynamically. So Your solution shoud work. But I didnt get it. How we can change the rdl on the fly?

Thanking you,

Leks

|||

You can write a whole RDL on the fly if you want to. Look for an example called something like "generate RDL dynamically" on http://gotreportviewer.com/ and there is another example there that will show you how to LoadReportDefinition for a local report from a stream. If the report is a serverreport, you could use the SOAP API to upload the adjusted report definition instead.

In your case, I don't think that it will be necessary to write the whole RDL from scratch (although you will still have to load or upload your revised version). You can have a "template" RDL that you adjust, for each run or each customer, or as needed.

There are two ways to do an RDL template revision, the way I would do it and the way most developers would prefer to do it <g>. I prefer XSLT; in this case I might have a parameter in the XSLT for each positionable item in the report, or pass the entire set of adjustments in the form of a lookup document. If you prefer to load up the RDL in the DOM and select the textboxes as nodes to adjust each of them, this is pretty easy too.

Do you need more help than this, or is this what you're looking for?

>L<

|||

Hi Lisa,

Yes. This was what I was looking for. Now I know that using the XSLT we can position the textbox or any control dynamically.

Thank you so much Lisa,

Leks

|||

Great -- if you get stuck, holler. I am very willing to go the extra mile for other folks who like XSLT <s>.

>L<

Dynamically change sort direction

Hello all! I have a report to be rendered in PDF format. On the report is a
list. I'm trying to dynamically change the sort direction of my list sorting
at run time, depending upon what parameter value is passed into the report
i.e. ascending or descending. Is this possible? I've tried editing my rdl
code numerous ways along these lines but it throws up an error no matter what:
<SortBy>
<SortExpression>Fields!sortby2.Value</SortExpression>
<Direction>=iif(Parameters!sortdir.Value = "Asc", Ascending,
Descending)</Direction>
</SortBy>
Any guidance would be greatly appreciated - either in doing it this way or
some other.
Thanks,
DaveDave,
I used this and it worked for me. I use two parameters, one telling
direction, one telling what column to sort by.
=iif(Parameters!Direction.Value="Ascending" AND
Parameters!SortBy.Value="quantity",Fields!quantity.Value, 0)
Put this in one column, and select Ascending in the other column.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:Dave@.discussions.microsoft.com:
> Hello all! I have a report to be rendered in PDF format. On the report is
> a
> list. I'm trying to dynamically change the sort direction of my list
> sorting
> at run time, depending upon what parameter value is passed into the report
> i.e. ascending or descending. Is this possible? I've tried editing my rdl
> code numerous ways along these lines but it throws up an error no matter
> what:
> <SortBy>
> <SortExpression>Fields!sortby2.Value</SortExpression>
> <Direction>=iif(Parameters!sortdir.Value = "Asc", Ascending,
> Descending)</Direction>
> </SortBy>
> Any guidance would be greatly appreciated - either in doing it this way or
> some other.
> Thanks,
> Dave

Dynamic way to change Font, Size, etc.

Hi,
I have one report format for 20 companies. Some of them require differnt
fonts and font sizes. I don't want to duplicate reports because they need
different font format. Is there a dymanic way to change font using one report
only? Thanks.
ChuckThere are two possible ways to do this:
1) Have your font settings in expressions that are populated by report
parameters or data. (It really works!) You can set default parameter
values.
2) Create a process that dynamically updates the RDL (report source) for
each company. This would mean inserting an extra step at either deployment
time or run time.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one
> report
> only? Thanks.
>
> Chuck|||Now I get some ideas to work on my project. Thanks so much for your reply.
-Chuck
"Jeff A. Stucker" wrote:
> There are two possible ways to do this:
> 1) Have your font settings in expressions that are populated by report
> parameters or data. (It really works!) You can set default parameter
> values.
> 2) Create a process that dynamically updates the RDL (report source) for
> each company. This would mean inserting an extra step at either deployment
> time or run time.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> > Hi,
> >
> > I have one report format for 20 companies. Some of them require differnt
> > fonts and font sizes. I don't want to duplicate reports because they need
> > different font format. Is there a dymanic way to change font using one
> > report
> > only? Thanks.
> >
> >
> > Chuck
>
>|||Another solution without having to add all those Parameters would be to
create a custom class that returns the font and font size etc. Then reference
the class in an expression. I have done this and it works great. Then you
could either distrubute a seperate class for each customer or have the class
read the information in from file and just distrubute a seperate file for
each.
See "Writing Custom Code" in the RS Help
Dave
"Chuck" wrote:
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one report
> only? Thanks.
>
> Chuck

Dynamic way to change font, font size, etc. in reports

Hi,
I have one report format for many clients. Some of them request different
fonts and font sizes. I don't want to duplicate reports because of differnt
fonts. Is there a dynamic way to change font or font size using only one
report format? Thanks.
ChuckChuck,
Custom code either embedded or as a custom assembly is the answer. This
article is a good starting point. http://odetocode.com/Articles/130.aspx.
Also, refer to the BOL for using custom assemblies.
You can use custom code to set font properties in your report.
"Chuck" wrote:
> Hi,
> I have one report format for many clients. Some of them request different
> fonts and font sizes. I don't want to duplicate reports because of differnt
> fonts. Is there a dynamic way to change font or font size using only one
> report format? Thanks.
>
> Chuck

dynamic sql to loop over fiscal years

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECT count(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECT count(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output...

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
... ...Create a table called ExperimentYears, populate it with ExperimentYear char(4), YearStart datetime, YearEnd datetime. Then do just one SELECT similar to yours:

SELECT count(*) as [Data Points], ExperimentYear
from tbl_experiment_data ted
inner join ExperimentYears ey
on ted.start_date between ey.YearStart and ey.YearEnd
and ted.completion_date between ey.YearStart and ey.YearEnd
where ted.status = 'CaseClosed'
group by ey.ExperimentYear|||We use a separate calendar table. something like this:

create table FiscalCalendar
(FiscalYear int,
StartDate datetime,
EndDate datetime)

This should reduce your query to something like

select count(*), fc.FiscalYear
from tbl_experiment_data a, FiscalCalendar fc
where a.startdate between fc.startdate and fc.enddate
and a.enddate between fc.startdate and fc.enddate
group by fc.fiscalyear

Been a while since I messed with this, so experiment with this for a bit. As a curiosity, what happens to experiments that start in one fiscal year and end in the next?|||as i'm re-reading my post now i can see that maybe it wouldn't even work as is because i have the count(*) without a 'group by'

still, i hope these sorts of mistakes can be overlooked as i ask for help.

it also occurred to me just now that maybe i could use a user-defined function that returns the value of the year as redefined by the "year" range above.

that way i could rewrite the query like this...

==============
SELECT count(*) as 'Data Points', getFiscalYear(start_date) as 'Experiment Date'
FROM tbl_experiment_data

WHERE DATEPART(YEAR, start_date) = getFiscalYear(start_date)
and DATEPART(YEAR, completion_date) = getFiscalYear(completion_date) and status = 'CaseClosed'

GROUP BY getFiscalYear(start_date)

ORDER BY getFiscalYear(start_date)

==============

any comments? criticisms? other ideas?

thanks again for reading ... and your input

oh, great! i just noticed the responses now too. thank you. i'll try these ideas out.|||SELECT count(*) as 'Data Points',
year(dateadd(d, 92, StartDate))-1 as 'Experiment Year'
FROM tbl_experiment_data
WHERE status = 'CaseClosed'

The year(dateadd(d, 92, StartDate))-1 function returns the experiment year by addint 92 days (Oct +Nov +Dec) and then subtracting 1 from the year. Note that if you just subtracted days you would have to account for leap years.

You will need to decide what to do if an experiment starts in one year and ends in the next. Your original code would skip those instances entirely.