Getting the right grouping

I have the following view statement
SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
NULL THEN
'0' ELSE Orig_miles END AS total_miles
FROM dbo.Engine INNER JOIN
dbo.tblEventInfo ON dbo.Engine.ID =
dbo.tblEventInfo.ID LEFT OUTER JOIN
dbo.orig_miles ON dbo.Engine.EngineNumber =
dbo.orig_miles.EngineNumber
GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
dbo.tblEventInfo.EventDate
HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
(dbo.tblEventInfo.EventDate <
CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
The problem I am having is that when there are more than two records
in the data set that meet the having criteria it spits out two
records. I am looking for it to spit out a single record that is a
sum of all the records available. If I take away the second part of
the having statement it works just fine except without the date
filter(which i need). Any help is greatly appreciated.Plamen responded in .programming.
"pyrahna" <pltaylor3@.gmail.com> wrote in message
news:1180014589.891961.116170@.g4g2000hsf.googlegroups.com...
> I have the following view statement
> SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
> NULL THEN
> '0' ELSE Orig_miles END AS total_miles
> FROM dbo.Engine INNER JOIN
> dbo.tblEventInfo ON dbo.Engine.ID =
> dbo.tblEventInfo.ID LEFT OUTER JOIN
> dbo.orig_miles ON dbo.Engine.EngineNumber =
> dbo.orig_miles.EngineNumber
> GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
> dbo.tblEventInfo.EventDate
> HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
> (dbo.tblEventInfo.EventDate <
> CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
> The problem I am having is that when there are more than two records
> in the data set that meet the having criteria it spits out two
> records. I am looking for it to spit out a single record that is a
> sum of all the records available. If I take away the second part of
> the having statement it works just fine except without the date
> filter(which i need). Any help is greatly appreciated.
>

Getting the right grouping

I have the following view statement
SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
NULL THEN
'0' ELSE Orig_miles END AS total_miles
FROM dbo.Engine INNER JOIN
dbo.tblEventInfo ON dbo.Engine.ID = dbo.tblEventInfo.ID LEFT OUTER JOIN
dbo.orig_miles ON dbo.Engine.EngineNumber = dbo.orig_miles.EngineNumber
GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
dbo.tblEventInfo.EventDate
HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
(dbo.tblEventInfo.EventDate <
CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
The problem I am having is that when there are more than two records
in the data set that meet the having criteria it spits out two
records. I am looking for it to spit out a single record that is a
sum of all the records available. If I take away the second part of
the having statement it works just fine except without the date
filter(which i need). Any help is greatly appreciated.Plamen responded in .programming.
"pyrahna" <pltaylor3@.gmail.com> wrote in message
news:1180014589.891961.116170@.g4g2000hsf.googlegroups.com...
> I have the following view statement
> SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
> NULL THEN
> '0' ELSE Orig_miles END AS total_miles
> FROM dbo.Engine INNER JOIN
> dbo.tblEventInfo ON dbo.Engine.ID => dbo.tblEventInfo.ID LEFT OUTER JOIN
> dbo.orig_miles ON dbo.Engine.EngineNumber => dbo.orig_miles.EngineNumber
> GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
> dbo.tblEventInfo.EventDate
> HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
> (dbo.tblEventInfo.EventDate <
> CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
> The problem I am having is that when there are more than two records
> in the data set that meet the having criteria it spits out two
> records. I am looking for it to spit out a single record that is a
> sum of all the records available. If I take away the second part of
> the having statement it works just fine except without the date
> filter(which i need). Any help is greatly appreciated.
>

Getting the right grouping

I have the following view statement
SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
NULL THEN
'0' ELSE Orig_miles END AS total_miles
FROM dbo.Engine INNER JOIN
dbo.tblEventInfo ON dbo.Engine.ID =
dbo.tblEventInfo.ID LEFT OUTER JOIN
dbo.orig_miles ON dbo.Engine.EngineNumber =
dbo.orig_miles.EngineNumber
GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
dbo.tblEventInfo.EventDate
HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
(dbo.tblEventInfo.EventDate <
CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
The problem I am having is that when there are more than two records
in the data set that meet the having criteria it spits out two
records. I am looking for it to spit out a single record that is a
sum of all the records available. If I take away the second part of
the having statement it works just fine except without the date
filter(which i need). Any help is greatly appreciated.
Plamen responded in .programming.
"pyrahna" <pltaylor3@.gmail.com> wrote in message
news:1180014589.891961.116170@.g4g2000hsf.googlegro ups.com...
> I have the following view statement
> SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
> NULL THEN
> '0' ELSE Orig_miles END AS total_miles
> FROM dbo.Engine INNER JOIN
> dbo.tblEventInfo ON dbo.Engine.ID =
> dbo.tblEventInfo.ID LEFT OUTER JOIN
> dbo.orig_miles ON dbo.Engine.EngineNumber =
> dbo.orig_miles.EngineNumber
> GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
> dbo.tblEventInfo.EventDate
> HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
> (dbo.tblEventInfo.EventDate <
> CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
> The problem I am having is that when there are more than two records
> in the data set that meet the having criteria it spits out two
> records. I am looking for it to spit out a single record that is a
> sum of all the records available. If I take away the second part of
> the having statement it works just fine except without the date
> filter(which i need). Any help is greatly appreciated.
>

Getting the return value

Hello everyone -

Please advise on why the output value is an empty

declare
@.DynamicSQL3 NVARCHAR(4000

SET @.DynamicSQL3 = 'SELECT Security_Level_ID ' +
'FROM ' + @.DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '

exec sp_executesql @.DynamicSQL3, N'@.i int output', @.AdministratorSecurityID output

I run the query and see the grid being populated with the proper
security_level_id in the query analyzer,

but when i execute a print @.AdministratorSecurityID
there is a space character in the results window

please advise on what i am doing wrong

thanks
tonydeclare @.DynamicSQL3 NVARCHAR(4000

SET @.DynamicSQL3 = 'SELECT @.i = Security_Level_ID ' +
'FROM ' + @.DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '

exec sp_executesql @.DynamicSQL3, N'@.i int output'
, @.AdministratorSecurityID output
-PatP|||that was the issue - many thanks!!!sql

Getting the return from a stored procedure from within a stored

Im trying to get stp1 to get the return value from stp2. stp2 returns
a int. Here is the code that I used in stp1
delcare @.return as in
set @.return = (dbo.stp2 (PARAM1, PARAM2))
I have tried different way to execute this but I get a error:
"Msg 4121, Level 16, State 1, Procedure stp1, Line 58 Cannot find
either column "dbo" or the user-defined function or aggregate
"dbo.stp2", or the name is ambiguous."
Is there a way to get the return value of a stored procedure from
within a stored procedure?Try :
declare @.return int
EXEC stp2 @.ValuePassed, @.return OUTPUT
PRINT @.return
Also , you need to make sure stp2 , is set up appropriately
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:6d0de7c3-e56c-4fba-8886-8cf70a60d646@.i72g2000hsd.googlegroups.com...
> Im trying to get stp1 to get the return value from stp2. stp2 returns
> a int. Here is the code that I used in stp1
> delcare @.return as in
> set @.return = (dbo.stp2 (PARAM1, PARAM2))
> I have tried different way to execute this but I get a error:
> "Msg 4121, Level 16, State 1, Procedure stp1, Line 58 Cannot find
> either column "dbo" or the user-defined function or aggregate
> "dbo.stp2", or the name is ambiguous."
> Is there a way to get the return value of a stored procedure from
> within a stored procedure?|||declare @.return int
exec @.return =3D dbo.stp2 @.PARAM1, @.PARAM2
select @.return
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Jan 24, 9:44=A0am, TheVillageCodingIdiot
<whosyodaddy1...@.hotmail.com> wrote:
> Im trying to get stp1 to get the return value from stp2. stp2 returns
> a int. Here is the code that I used in stp1
> delcare @.return as in
> set @.return =3D (dbo.stp2 (PARAM1, PARAM2))
> I have tried different way to execute this but I get a error:
> "Msg 4121, Level 16, State 1, Procedure stp1, Line 58 Cannot find
> either column "dbo" or the user-defined function or aggregate
> "dbo.stp2", or the name is ambiguous."
> Is there a way to get the return value of a stored procedure from
> within a stored procedure?

Getting the results for month and year by a single SQL query

Hi,

I've to get the results from a single table for month and year-to-date by a single query. I've two columns monthstartdate and monthenddate. If it is only for month I can assign the values and execute the query. Vice versa for year. But If I want to get results for month and year at once like below:

Reportingdate # of Views per month # of view per year

Please suggest me on writing this query.I am confused. Please give me data sample and desired result.

Thank you.|||Current Result:

PageName Month-To-Date # of views
SqlHelp.aspx 15

Expected Result: Along with the above result it should fetch result for year to date( Jan 1st 2007 to current date)

PageName Month-#-views Year-to-Date # Views
SqlHelp.aspx 5 300

Current query is like as below:
SELECT TOP(10)
PageName,
SUM([Number of Views])[Number of Views],
FROM Pages (NOLOCK)
WHERE MonthStartDt >= @.StartDt
AND MonthEndDt < DATEADD(d, 1, @.EndDt)
GROUP BY
PageName
ORDER BY SUM([Number of Views]) DESC

How do I write query to get the results for month as well as year in a single query, please suggest me.|||I need table structure like what columns are in a table and what parameters you pass to the query to get a result.
Give me something like this:
Table name [Pages]

DATA
PageName , Number of Views, MonthStartDt, MonthEndDt
Page 1 1000 01/01/2006 31/03/2006
Page 1 500 01/05/2006 31/07/2006
Page 2 7000 01/01/2006 31/03/2006

Parameters @.StartDtMonth = ?
Parameters @.EndDtMonth = ?
Parameters @.StartDtYear = ?
Parameters @.EndDtYear = ?

What result do you expect to get
PageName, Count per Month, Count per Year.

Getting The report server cannot open a connection to the report server database error

Hi

I went to my comp ->rt cick->manage-IIS->default website->rt click->properties->directory security->annoymouse access…EDIT->under Default domain it was blank, and i added a domain there.

And i was getting error opening up the reports from localhost.

I changed everything to same as it was previosly, and I am getting the error:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) (rsRPCError) Get Online Help

Cannot open database "ReportServer$SQLExpress" requested by the login. The login failed. Login failed for user 'NGES\Computer name$'.

Hi,

Maybe you should change the databse connection credential. You can do it on the SSRS Configuration manager on the Database Setup page.

change the credential type from Service Credential to Windows or SQL server credential and type a user name and password with the appropiate permnission in the SSRS Report Server and ReportServerTemp database. that user must be a member of the RSExecRole role in the database.

Another option, to cange the SSRS service credential to a windows account and add the same permission (also if you do it through the SQL Server Config manager, it assigns the appropiate permisson to that user.

regards,

Janos