Can this kind of report be done?

Hi,
I'm using SQL 2005 and I'm currently trying to create a report using VS
2005. I'm pulling the data from a CRM 3 database.
So far it is going pretty well. I got a good base but I'm stuck.
I want to create a report similar to the Sales Pipeline report. The problem
with that report is that you can only generate revenue based only on 1 date
(the close date). I would like to generate the same kind of report but based
on weekly revenue.
So I added 3 fields to the database for that table: 2 date fields
(contractstart and contractend) and a number field (contractlenght).
I know that I can use a simple formula to divide the revenue by the lenght
of the contract.
Say I have a contract that start on 3/11 and end on 4/7. It will last 4
weeks.
Is there a way to make it generate a column for each week? It that case it
would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for all the
dates mentioned in the start/end fields.
Also, for some reason my date only fields include a time stamp. How can I
remove that?
Thanks!
SimonYou can use matrix, but will be difficult to manipulate for your weekly
columns, instead I would suggest, you do all these things using sql query
itself and bring just the results to the report layout.
Amarnath, MCTS
"Simon Renshaw" wrote:
> Hi,
> I'm using SQL 2005 and I'm currently trying to create a report using VS
> 2005. I'm pulling the data from a CRM 3 database.
> So far it is going pretty well. I got a good base but I'm stuck.
> I want to create a report similar to the Sales Pipeline report. The problem
> with that report is that you can only generate revenue based only on 1 date
> (the close date). I would like to generate the same kind of report but based
> on weekly revenue.
> So I added 3 fields to the database for that table: 2 date fields
> (contractstart and contractend) and a number field (contractlenght).
> I know that I can use a simple formula to divide the revenue by the lenght
> of the contract.
> Say I have a contract that start on 3/11 and end on 4/7. It will last 4
> weeks.
> Is there a way to make it generate a column for each week? It that case it
> would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for all the
> dates mentioned in the start/end fields.
> Also, for some reason my date only fields include a time stamp. How can I
> remove that?
> Thanks!
> Simon
>
>
>|||On Mar 8, 11:47 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> You can use matrix, but will be difficult to manipulate for your weekly
> columns, instead I would suggest, you do all these things using sql query
> itself and bring just the results to the report layout.
> Amarnath, MCTS
> "Simon Renshaw" wrote:
> > Hi,
> > I'm using SQL 2005 and I'm currently trying to create a report using VS
> > 2005. I'm pulling the data from a CRM 3 database.
> > So far it is going pretty well. I got a good base but I'm stuck.
> > I want to create a report similar to the Sales Pipeline report. The problem
> > with that report is that you can only generate revenue based only on 1 date
> > (the close date). I would like to generate the same kind of report but based
> > on weekly revenue.
> > So I added 3 fields to the database for that table: 2 date fields
> > (contractstart and contractend) and a number field (contractlenght).
> > I know that I can use a simple formula to divide the revenue by the lenght
> > of the contract.
> > Say I have a contract that start on 3/11 and end on 4/7. It will last 4
> > weeks.
> > Is there a way to make it generate a column for each week? It that case it
> > would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for all the
> > dates mentioned in the start/end fields.
> > Also, for some reason my date only fields include a time stamp. How can I
> > remove that?
> > Thanks!
> > Simon
I believe you will have to use a combination of a SQL stored procedure
(preferrably for the improved performance) and a matrix. As long as
you are only pivoting one column (in your case the week column), I
wouldn't foresee there being a problem. One thing I would suggest
though is to require the user to obtain a set period type (i.e., data
listed by week all the time, or by day all the time; but not both/
either one, as this will get extremely complicated query-wise (and
most likely performance-wise as well)). When you refer to your date-
only fields, are you referring to it in a table or the report? If in a
table, I believe that you will have to stick w/the defaults of the
smalldatetime format (which includes standard midnight time) unless
you want to cast dateparts as characters and then recast them (once
concatenated) to datetime again. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Well, we got it using a function.
Only thing left is to fix the date/time problem.
Thanks for the pointers!
Simon
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1173443216.891594.153580@.h3g2000cwc.googlegroups.com...
> On Mar 8, 11:47 pm, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
>> You can use matrix, but will be difficult to manipulate for your weekly
>> columns, instead I would suggest, you do all these things using sql query
>> itself and bring just the results to the report layout.
>> Amarnath, MCTS
>> "Simon Renshaw" wrote:
>> > Hi,
>> > I'm using SQL 2005 and I'm currently trying to create a report using VS
>> > 2005. I'm pulling the data from a CRM 3 database.
>> > So far it is going pretty well. I got a good base but I'm stuck.
>> > I want to create a report similar to the Sales Pipeline report. The
>> > problem
>> > with that report is that you can only generate revenue based only on 1
>> > date
>> > (the close date). I would like to generate the same kind of report but
>> > based
>> > on weekly revenue.
>> > So I added 3 fields to the database for that table: 2 date fields
>> > (contractstart and contractend) and a number field (contractlenght).
>> > I know that I can use a simple formula to divide the revenue by the
>> > lenght
>> > of the contract.
>> > Say I have a contract that start on 3/11 and end on 4/7. It will last 4
>> > weeks.
>> > Is there a way to make it generate a column for each week? It that case
>> > it
>> > would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for
>> > all the
>> > dates mentioned in the start/end fields.
>> > Also, for some reason my date only fields include a time stamp. How can
>> > I
>> > remove that?
>> > Thanks!
>> > Simon
>
> I believe you will have to use a combination of a SQL stored procedure
> (preferrably for the improved performance) and a matrix. As long as
> you are only pivoting one column (in your case the week column), I
> wouldn't foresee there being a problem. One thing I would suggest
> though is to require the user to obtain a set period type (i.e., data
> listed by week all the time, or by day all the time; but not both/
> either one, as this will get extremely complicated query-wise (and
> most likely performance-wise as well)). When you refer to your date-
> only fields, are you referring to it in a table or the report? If in a
> table, I believe that you will have to stick w/the defaults of the
> smalldatetime format (which includes standard midnight time) unless
> you want to cast dateparts as characters and then recast them (once
> concatenated) to datetime again. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Well, also fixed the date/time problem within the query.
"Simon Renshaw" <simon@.benchmarkconsulting.com> wrote in message
news:eFFTfVNZHHA.5080@.TK2MSFTNGP02.phx.gbl...
> Well, we got it using a function.
> Only thing left is to fix the date/time problem.
> Thanks for the pointers!
> Simon
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1173443216.891594.153580@.h3g2000cwc.googlegroups.com...
>> On Mar 8, 11:47 pm, Amarnath <Amarn...@.discussions.microsoft.com>
>> wrote:
>> You can use matrix, but will be difficult to manipulate for your weekly
>> columns, instead I would suggest, you do all these things using sql
>> query
>> itself and bring just the results to the report layout.
>> Amarnath, MCTS
>> "Simon Renshaw" wrote:
>> > Hi,
>> > I'm using SQL 2005 and I'm currently trying to create a report using
>> > VS
>> > 2005. I'm pulling the data from a CRM 3 database.
>> > So far it is going pretty well. I got a good base but I'm stuck.
>> > I want to create a report similar to the Sales Pipeline report. The
>> > problem
>> > with that report is that you can only generate revenue based only on 1
>> > date
>> > (the close date). I would like to generate the same kind of report but
>> > based
>> > on weekly revenue.
>> > So I added 3 fields to the database for that table: 2 date fields
>> > (contractstart and contractend) and a number field (contractlenght).
>> > I know that I can use a simple formula to divide the revenue by the
>> > lenght
>> > of the contract.
>> > Say I have a contract that start on 3/11 and end on 4/7. It will last
>> > 4
>> > weeks.
>> > Is there a way to make it generate a column for each week? It that
>> > case it
>> > would be for 3/11, 3/18/, 3/25 and 4/1. It would need to do this for
>> > all the
>> > dates mentioned in the start/end fields.
>> > Also, for some reason my date only fields include a time stamp. How
>> > can I
>> > remove that?
>> > Thanks!
>> > Simon
>>
>> I believe you will have to use a combination of a SQL stored procedure
>> (preferrably for the improved performance) and a matrix. As long as
>> you are only pivoting one column (in your case the week column), I
>> wouldn't foresee there being a problem. One thing I would suggest
>> though is to require the user to obtain a set period type (i.e., data
>> listed by week all the time, or by day all the time; but not both/
>> either one, as this will get extremely complicated query-wise (and
>> most likely performance-wise as well)). When you refer to your date-
>> only fields, are you referring to it in a table or the report? If in a
>> table, I believe that you will have to stick w/the defaults of the
>> smalldatetime format (which includes standard midnight time) unless
>> you want to cast dateparts as characters and then recast them (once
>> concatenated) to datetime again. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>

Can one exec a stored procedure in a case statement in a stored procedure?

Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***No, although if you can convert the proc into a user-defined function, you
could use the UDF.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Colette Horter" <coletten@.gmail.com> wrote in message
news:e67fw6RlGHA.1340@.TK2MSFTNGP02.phx.gbl...
Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***|||>> Can one execute a stored procedure and get a value back from it in a CASE stat
ement [sic] in a stored procedure? <<
Learn the basics and life is so easy:
There is no CASE statement in SQL. There is a CASE expression. What
does an expression do? It returns a scalar value of a known data type.
Does a stored procedure return a scalar value of a known data type?
NO! But a scalar function call does!

>From you psuedo-code posting, it looks like you also need to learn is
that rows are not records; fields are not columns; tables are not
files. You might also want to learn Standard SQL's AS for alias
assignments, too. That will make your code portable and readable to
the next guy to maintain it.
I will ignore the remark about pulling out your hair :)|||>>> Does a stored procedure return a scalar value of a known data type?
YES !! The data type is INTEGER.
I mean - really, please read the manual for MICROSOFT SQL SERVER and be
aware of the group you are posting to which is for MICROSOFT SQL SERVER.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150904673.628439.120580@.g10g2000cwb.googlegroups.com...
> Learn the basics and life is so easy:
> There is no CASE statement in SQL. There is a CASE expression. What
> does an expression do? It returns a scalar value of a known data type.
> Does a stored procedure return a scalar value of a known data type?
> NO! But a scalar function call does!
>
> that rows are not records; fields are not columns; tables are not
> files. You might also want to learn Standard SQL's AS for alias
> assignments, too. That will make your code portable and readable to
> the next guy to maintain it.
> I will ignore the remark about pulling out your hair :)
>|||>> The data type is INTEGER [return from T-SQL stored procedure] <<
No, that is a completion flag and it is no more a result data type than
a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
-- because of internal representation, but they are VERY different
domains. Think abstract, not current dialect implementation.
You might not remember the C programming language, but all statements
returned such flags (it was part of the DEC PDP-11 hardware that the
language was based on). If you want a scalar result value, you use a
function.|||Thank you so much! I put it in a function and now my sproc actually
saves!!! Just need to finish it up now. c",)
*** Sent via Developersdex http://www.examnotes.net ***|||> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
WRONG! WILL YOU PLEASE READ THE MANUAL AND ACTUALLY USE THE PRODUCT!!!!!
You can use RETURN to pass back any INTEGER, e.g. RETURN( 1 ) etc...
AGAIN: CHECK THE MANUAL UNDER STORED PROCEDURE!!!!

> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
Yes, I remember - I also remember PL/1, C++ and PASCAL.....
But, have you actually used these languages outside of a book / class room?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150925049.724707.252820@.r2g2000cwb.googlegroups.com...
> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
>|||>> - I also remember PL/1, C++ and PASCAL.. But, have you actually used thes
e languages outside of a book / class room? <<
PL/I at Coca Cola World HQ in Atlanta, Pascal at Southern Califrnia
Edison and I nver learned C++.