Retriew number of sunday,s for a given month in sql server

hello i am student and developing project having the fallowing problem

1: i want to calculate the number of sunday,s in a given month.

OR between two given dates like 01/02/06 and 28/02/06 .

thanks

Hi,

this should solve the problem, input is the day of the week you want to get.

CREATE FUNCTION NumberOfWeekdays
(
@.StartDate DATETIME,
@.EndDate DATETIME,
@.Weekday TINYINT
)
RETURNS INT
BEGIN
DECLARE @.dt DATETIME
DECLARE @.NumberofDays INT
SET @.NumberofDays = 0

SET @.dt = @.StartDate
WHILE @.dt < @.EndDate
BEGIN
SET @.NumberofDays = @.NumberofDays + (CASE WHEN DATEPART(dw,@.dt) = @.Weekday THEN 1 ELSE 0 END)
SET @.dt = @.dt+1
END
RETURN @.NumberofDays
END

HTH; Jens Suessmeyer.

Retrieving RowCount from a dataset

Hi,
I need to calculate a percentage of population based on the number of rows
returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
there is no way for SRS to receive multiple result sets in a single data set.
I could execute the query twice, once for the count and second with the
count included as a column. I hate to run the query twice. What I could use
is a rowcount from the dataset in SRS, but I don't this property exists.
Does anyone have any ideas?
Thank you,
BobBob,
As you are using a SProc to return the recordset, just add the @.@.rowcount to
the end of the SELECT statement and it will be returned with each row. This
will allow a single call only, and the amount of 'extra' data returned (2
bytes per row maybe?) will be mnore efficient than recalling the SProc a
second time.
Tony
"Bob" wrote:
> Hi,
> I need to calculate a percentage of population based on the number of rows
> returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> there is no way for SRS to receive multiple result sets in a single data set.
> I could execute the query twice, once for the count and second with the
> count included as a column. I hate to run the query twice. What I could use
> is a rowcount from the dataset in SRS, but I don't this property exists.
> Does anyone have any ideas?
> Thank you,
> Bob|||Hi Tony,
I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
only valid after the select has completed. I was hoping that a rowcount
property would be available within SRS.
Thank you for the suggestion.
Bob
"Logicalman" wrote:
> Bob,
> As you are using a SProc to return the recordset, just add the @.@.rowcount to
> the end of the SELECT statement and it will be returned with each row. This
> will allow a single call only, and the amount of 'extra' data returned (2
> bytes per row maybe?) will be mnore efficient than recalling the SProc a
> second time.
> Tony
> "Bob" wrote:
> > Hi,
> >
> > I need to calculate a percentage of population based on the number of rows
> > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > there is no way for SRS to receive multiple result sets in a single data set.
> > I could execute the query twice, once for the count and second with the
> > count included as a column. I hate to run the query twice. What I could use
> > is a rowcount from the dataset in SRS, but I don't this property exists.
> > Does anyone have any ideas?
> >
> > Thank you,
> > Bob|||Bob,
Have you tried the CountRows function? (Syntax should be:
=CountRows("yourDataset")) Just a suggestion. I have never used this
technique.
Ernie
Bob wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||I figured out how to do this.
In the table HEADER section, which is the entire scope, I placed a
CountDistinct(fld!name.value) for the unique item in the dataset. Then I
made the column invisible. Lastly, I referenced this value within the table
via ReportItems!textboxname.value. This gave me the count I was looking for.
"Bob" wrote:
> Hi Tony,
> I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> only valid after the select has completed. I was hoping that a rowcount
> property would be available within SRS.
> Thank you for the suggestion.
> Bob
> "Logicalman" wrote:
> > Bob,
> >
> > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > the end of the SELECT statement and it will be returned with each row. This
> > will allow a single call only, and the amount of 'extra' data returned (2
> > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > second time.
> >
> > Tony
> >
> > "Bob" wrote:
> >
> > > Hi,
> > >
> > > I need to calculate a percentage of population based on the number of rows
> > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > there is no way for SRS to receive multiple result sets in a single data set.
> > > I could execute the query twice, once for the count and second with the
> > > count included as a column. I hate to run the query twice. What I could use
> > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > Does anyone have any ideas?
> > >
> > > Thank you,
> > > Bob|||Bob,
Excellent result. We are all still on the learning curve, I'll have to
remember that one.
Regarding using the @.@.RowCount feature, you could also have used a simple
variable in the sproc and set it to Select Count(*) FROm tblename and then
passed that as an extra column in the final select statement.
Tony
"Ernie Gutierrez" wrote:
> Bob,
> Have you tried the CountRows function? (Syntax should be:
> =CountRows("yourDataset")) Just a suggestion. I have never used this
> technique.
> Ernie
> Bob wrote:
> > Hi Tony,
> >
> > I have already tried that idea. For each row, @.@.ROWCOUNT returns 1. Its
> > only valid after the select has completed. I was hoping that a rowcount
> > property would be available within SRS.
> >
> > Thank you for the suggestion.
> >
> > Bob
> >
> > "Logicalman" wrote:
> >
> > > Bob,
> > >
> > > As you are using a SProc to return the recordset, just add the @.@.rowcount to
> > > the end of the SELECT statement and it will be returned with each row. This
> > > will allow a single call only, and the amount of 'extra' data returned (2
> > > bytes per row maybe?) will be mnore efficient than recalling the SProc a
> > > second time.
> > >
> > > Tony
> > >
> > > "Bob" wrote:
> > >
> > > > Hi,
> > > >
> > > > I need to calculate a percentage of population based on the number of rows
> > > > returned on a dataset. Within a stored procedure, I could use @.@.ROWCOUNT but
> > > > there is no way for SRS to receive multiple result sets in a single data set.
> > > > I could execute the query twice, once for the count and second with the
> > > > count included as a column. I hate to run the query twice. What I could use
> > > > is a rowcount from the dataset in SRS, but I don't this property exists.
> > > > Does anyone have any ideas?
> > > >
> > > > Thank you,
> > > > Bob
>

Retrieve length of image or BLOB

I use the data type image to store BLOB's on the database. Now I would
like to Query the length of the BLOB with T-SQL to calculate the used
disk-space for this item.
Has some one a good idea?Check out the DATALENGTH function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroups.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>