How to obtain week by week plus YTD totals

Question

How to obtain week by week plus YTD totals

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 1

simflex-897410 (5/17/2010)
Thanks a lot again, Goldie.
I am still struggling with the dynamic date. I will continue to try to figure it out.
It isn't making sense to me. I am more of .net programmer but that's not an excuse.

On the other hand, if you are a .NET programmer, why not write the report in .NET?

Get one dataset containing the values you need and compute the calculations and display in .NET.

It will be easier for you, and have less impact on the database as well.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

no, I am doing this with Sql Server Reporting Services; nothing to do with .net.

So, I build the sql using query analyzer. When I get the results I am after, then I go to ssrs.

That's the issue I am having here.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

simflex-897410 (5/17/2010)
I am still struggling with the dynamic date. I will continue to try to figure it out.
It isn't making sense to me. I am more of .net programmer but that's not an excuse.

Did you read the dynamic crosstab article that Lutz pointed out? It does tell you exactly how to do this stuff. If you did and you're still having problems, post your current rendition of the code and let's have a go at it toghether.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 4

simflex-897410 (5/17/2010)
no, I am doing this with Sql Server Reporting Services; nothing to do with .net.
So, I build the sql using query analyzer. When I get the results I am after, then I go to ssrs.
That's the issue I am having here.

Got it. Try the last one I posted. It should work for you.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 5

Goldie, that works a treat.

Thank you very much.

To all, I am very, very sorry, I fibbed.

I said i would give it all I got before coming back to but unfortunately, I stunk.

I know enough basic to embed in my code but I promise to keep working on it.

We used to have a SQL guy but the downturn reduced our workforce and we are now forced to do it ourselves.

Once again, I am extremely grateful for the assistance.

Goldie, rather than use WHERE EndDate BETWEEN @StartDate AND @EndDate, can I replace it with year so that users can just enter year to obtain the report for that year?

WayneS SSC Guru Points: 95460 More actions · Answer 6

simflex-897410 (5/17/2010)
... rather than use WHERE EndDate BETWEEN @StartDate AND @EndDate, can I replace it with year so that users can just enter year to obtain the report for that year?

When working with dates, you will probably be better off NOT using between; instead use:

WHERE EndDate >= @StartDate

AND EndDate < @EndDate

where EndDate is the next days date than what you want. The reason for this is precision/rounding.

If @EndDate = "20100430 23:59:59", and you have a value in the EndDate column of you table with "20100430 23:59:59.5", it won't be included.

If you change @EndDate to "20100430 23:59:59.997" (the max for a DateTime datatype), and the datatype changes to DateTime2 (which has a precision to the micro-second), you can miss things > "20100430 23:59:59.997" and < "20100430 23:59:59.9999999"

So, to handle ALL of these situations, set @EndDate to "20100501", and look for the value being less than this.

Edit: to answer your question, yes you can do this. But if the column is a DataTime datatype, you will have to use the YEAR() function around the EndDate column in your WHERE clause, which will mean that you will NOT be able to utilize indexes on this column. So, if you pass in the year, build the actual @StartDate / @EndDate variables and use those in your where clause.

i.e.

select @StartDate = convert(datetime, convert(char(4), @Year) + '0101'),

@EndDate = convert(datetime, convert(char(4), @Year+1) + '0101'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2