Dear group,
I need to create a stored procedure to return some data. The trick is the
data needs to be AGGREGATED with a SUM of commissions against each line for
each broker...
I have a table (TRADE) with the following data:
trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
1, 1, 1, 150, 13/06/2006
2, 2, 1, 100, 13/06/2006
3, 1, 1, 75, 14/06/2006
4, 1, 2, 165, 14/06/2006
5, 3, 1, 33.50, 14/06/2006
I want to display (for a DataTable to be used in a Crystal Report) a grid
where the headers will be:
Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id =
2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
(where tradeType_id = 2), Sum Monthly Total.
So that the query, when run on (14/06/2006), will look like:
1, 75, 165, 240, 225, 165, 190
2, null, null, null, 100, null, 100
3, 33.50, null, 33.50, 33.50, null, 33.50
The concept here is that I have a table which contains trades that a broker
has made. Each trade has a commission_amount column and a datestamp. I need
to be able to produce a report which has daily totals for different trade
types, but where the data is AGGREGATED by broker_id. All the SQL I've been
coming up with has been a total mess.
Can anyone assist with the above problem?
Many thanks!
MikeLiddle,
I think the trickiest thing here is the grouping. Is the monthly
total the running total since the first of the month, or just the sum
or trade type 2 records for a broker on a given day?
You can pretty easily group by date, broker id, and trade type.
SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
sumcom
FROM TRADE
GROUP BY broker_id, tradetype_id, datestamp
but the most straightforward way to get it into the format you want is
to do two subqueries and join them back together. But, what that looks
like will depend on whether you're looking for a running total or not.
Ion
Liddle Feesh wrote:
> Dear group,
> I need to create a stored procedure to return some data. The trick is the
> data needs to be AGGREGATED with a SUM of commissions against each line fo
r
> each broker...
> I have a table (TRADE) with the following data:
> trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
> 1, 1, 1, 150, 13/06/2006
> 2, 2, 1, 100, 13/06/2006
> 3, 1, 1, 75, 14/06/2006
> 4, 1, 2, 165, 14/06/2006
> 5, 3, 1, 33.50, 14/06/2006
> I want to display (for a DataTable to be used in a Crystal Report) a grid
> where the headers will be:
> Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id
=
> 2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
> (where tradeType_id = 2), Sum Monthly Total.
> So that the query, when run on (14/06/2006), will look like:
> 1, 75, 165, 240, 225, 165, 190
> 2, null, null, null, 100, null, 100
> 3, 33.50, null, 33.50, 33.50, null, 33.50
> The concept here is that I have a table which contains trades that a broke
r
> has made. Each trade has a commission_amount column and a datestamp. I nee
d
> to be able to produce a report which has daily totals for different trade
> types, but where the data is AGGREGATED by broker_id. All the SQL I've bee
n
> coming up with has been a total mess.
> Can anyone assist with the above problem?
> Many thanks!
> Mike|||<ionFreeman@.gmail.com> wrote in message
news:1150320207.374658.305960@.f6g2000cwb.googlegroups.com...
> Liddle,
> I think the trickiest thing here is the grouping. Is the monthly
> total the running total since the first of the month, or just the sum
> or trade type 2 records for a broker on a given day?
There are only four aggregated calculated fields, those are the daily totals
for trade_Type 1 and 2 and the monthly totals for trade_Type 1 and 2. The
sum is just an addition of those two fields and can be calculated easily by
hand.
> You can pretty easily group by date, broker id, and trade type.
> SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
> sumcom
> FROM TRADE
> GROUP BY broker_id, tradetype_id, datestamp
> but the most straightforward way to get it into the format you want is
> to do two subqueries and join them back together. But, what that looks
> like will depend on whether you're looking for a running total or not.
Cheers, Jon - that's close, but not quite right. I'm looking for a distinct
group, so that if there are only 2 broker_id's, there will be only two rows
and the SUM() data will be aggregated on those rows.
Thanks!sql
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Friday, March 30, 2012
help with SQL procedure conversion
Hi Guys, I need some in SQL conversion from Oracle to SQL Server...Here is the procedure in T-SQL..When I run the below SQL in SQL Server, it is going in infinite loop. When I click stop, I am getting the error ......
"Invalid length parameter passed to the substring function."
at the following line
SELECT @.RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@.UserRoles_in, 1, CHARINDEX(',', @.UserRoles_in) - 1))
----
DECLARE @.objid_in INT
DECLARE @.objclass_in INT
DECLARE @.userid_in INT
DECLARE @.userRoles_in VARCHAR(3000)
DECLARE @.RoleID_in INT
DECLARE @.cnt INT
DECLARE csr CURSOR FOR
SELECT * FROM objectACL
OPEN csr
WHILE (0 = 0)
BEGIN --(
fetch NEXT FROM csr INTO @.objid_in, @.objclass_in, @.userid_in, @.userRoles_in
IF (@.@.FETCH_STATUS = -1)
BREAK
SELECT @.UserRoles_in = SUBSTRING(@.UserRoles_in, 2, LEN(@.UserRoles_in))
WHILE (0 = 0)
BEGIN --(
SELECT @.RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@.UserRoles_in, 1, CHARINDEX(',', @.UserRoles_in) - 1))
SELECT @.cnt = COUNT(*) FROM nodetable WHERE objtype = 21 AND id = @.RoleId_in
IF ( @.cnt = 0 )
BEGIN
INSERT INTO error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@.RoleID_in AS VARCHAR) )
END
SELECT @.UserRoles_in = SUBSTRING(@.UserRoles_in, LEN(@.RoleID_in) + 2, LEN(@.UserRoles_in))
IF ( @.UserRoles_in is null )
BEGIN
BREAK
END
END --)
END --)
close csr
DEALLOCATE csr
GO
------
Corresponding procedure in Oracle
-----
declare
cursor csr is select * from objectACL;
objid_in number;
objclass_in number;
userid_in number;
userRoles_in varchar2(3000);
RoleID_in number;
cnt number;
begin
open csr;
loop
fetch csr into objid_in, objclass_in, userid_in, userRoles_in;
exit when csr%notfound;
UserRoles_in := substr(UserRoles_in, 2);
loop
RoleID_in := to_number(substr(UserRoles_in, 1, instr(UserRoles_in, ',')-1));
select count(1) into cnt from nodetable where objtype=21 and id=RoleId_in;
if (cnt =0) then
insert into error_report values ('ObjectACL', '0', 'UserRoles refering to Non-existing Role : '||RoleId_in);
end if;
UserRoles_in := substr(userRoles_in, length(RoleId_in)+2);
if (userRoles_in is null) then
exit;
end if;
end loop;
end loop;
close csr;
end;
/
------Dear Lord, PSQL is a sucky language. That is pretty near unreadable.
Do yourself a favor and don't even try to convert this into TSQL directly. Oracle developers love cursors, but set-based operations are almost always easier to debug and run faster. I'd better dollars to doughnut holes you don't even need a cursor for this.
Post your table layout and a description of what you are trying to do.
"Invalid length parameter passed to the substring function."
at the following line
SELECT @.RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@.UserRoles_in, 1, CHARINDEX(',', @.UserRoles_in) - 1))
----
DECLARE @.objid_in INT
DECLARE @.objclass_in INT
DECLARE @.userid_in INT
DECLARE @.userRoles_in VARCHAR(3000)
DECLARE @.RoleID_in INT
DECLARE @.cnt INT
DECLARE csr CURSOR FOR
SELECT * FROM objectACL
OPEN csr
WHILE (0 = 0)
BEGIN --(
fetch NEXT FROM csr INTO @.objid_in, @.objclass_in, @.userid_in, @.userRoles_in
IF (@.@.FETCH_STATUS = -1)
BREAK
SELECT @.UserRoles_in = SUBSTRING(@.UserRoles_in, 2, LEN(@.UserRoles_in))
WHILE (0 = 0)
BEGIN --(
SELECT @.RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@.UserRoles_in, 1, CHARINDEX(',', @.UserRoles_in) - 1))
SELECT @.cnt = COUNT(*) FROM nodetable WHERE objtype = 21 AND id = @.RoleId_in
IF ( @.cnt = 0 )
BEGIN
INSERT INTO error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@.RoleID_in AS VARCHAR) )
END
SELECT @.UserRoles_in = SUBSTRING(@.UserRoles_in, LEN(@.RoleID_in) + 2, LEN(@.UserRoles_in))
IF ( @.UserRoles_in is null )
BEGIN
BREAK
END
END --)
END --)
close csr
DEALLOCATE csr
GO
------
Corresponding procedure in Oracle
-----
declare
cursor csr is select * from objectACL;
objid_in number;
objclass_in number;
userid_in number;
userRoles_in varchar2(3000);
RoleID_in number;
cnt number;
begin
open csr;
loop
fetch csr into objid_in, objclass_in, userid_in, userRoles_in;
exit when csr%notfound;
UserRoles_in := substr(UserRoles_in, 2);
loop
RoleID_in := to_number(substr(UserRoles_in, 1, instr(UserRoles_in, ',')-1));
select count(1) into cnt from nodetable where objtype=21 and id=RoleId_in;
if (cnt =0) then
insert into error_report values ('ObjectACL', '0', 'UserRoles refering to Non-existing Role : '||RoleId_in);
end if;
UserRoles_in := substr(userRoles_in, length(RoleId_in)+2);
if (userRoles_in is null) then
exit;
end if;
end loop;
end loop;
close csr;
end;
/
------Dear Lord, PSQL is a sucky language. That is pretty near unreadable.
Do yourself a favor and don't even try to convert this into TSQL directly. Oracle developers love cursors, but set-based operations are almost always easier to debug and run faster. I'd better dollars to doughnut holes you don't even need a cursor for this.
Post your table layout and a description of what you are trying to do.
Wednesday, March 28, 2012
Help with SQL 2000 Debugger
I installed the SQL 2000 trial as a local server on a standalone XP Pro system. When use the debugger, it executes the stored procedure without allowing me to step into the code.
Any help would be greatly appreciated.
Check in the help topic in SQL Server Books Online titled: Troubleshooting the Transact-SQL Debugger
You can experience the problems you are having when the DCOM settings aren't configured correctly.
-Sue
|||Sue, thanks for responding. Yes I've gone thru the DCOM settings and made sure EVERYONE was there and set to allow, SYSTEM is also there and set to allow. The identy for sqldbreg is set at interactive like described in the Troubleshooting the Transact-SQL Debugger section of the books online. You should know that I'm not a windows op sys expert. I think it has something to do with the Windows Services Account is the Local System Account. All the Services Account stuff is well beyond my knowledge or experience and I don't know if since my system is XP Pro wether I can even setup a Domain User Account?
Any more help would be appreciated.
|||Sue, I just figured it out. I had to change the MSSQLSERVER properties logon to logon using a User Account versus the Local System.|||Can you please tell me where do I go to set MSSQLServer properties logon? Should I be a SysAdmin/DBA and have access to the server box.
Thank you.
|||In SQL Server 2000, use Enterprise Manager to change the service accounts. Right click on the server and select properties. Then go to the Security tab.
In SQL Server 2005, you would use Configuration Manager to change the service accounts.
-Sue
Help with SQL 2000 Debugger
I installed the SQL 2000 trial as a local server on a standalone XP Pro system. When use the debugger, it executes the stored procedure without allowing me to step into the code.
Any help would be greatly appreciated.
Check in the help topic in SQL Server Books Online titled: Troubleshooting the Transact-SQL Debugger
You can experience the problems you are having when the DCOM settings aren't configured correctly.
-Sue
|||Sue, thanks for responding. Yes I've gone thru the DCOM settings and made sure EVERYONE was there and set to allow, SYSTEM is also there and set to allow. The identy for sqldbreg is set at interactive like described in the Troubleshooting the Transact-SQL Debugger section of the books online. You should know that I'm not a windows op sys expert. I think it has something to do with the Windows Services Account is the Local System Account. All the Services Account stuff is well beyond my knowledge or experience and I don't know if since my system is XP Pro wether I can even setup a Domain User Account?
Any more help would be appreciated.
|||Sue, I just figured it out. I had to change the MSSQLSERVER properties logon to logon using a User Account versus the Local System.|||Can you please tell me where do I go to set MSSQLServer properties logon? Should I be a SysAdmin/DBA and have access to the server box.
Thank you.
|||In SQL Server 2000, use Enterprise Manager to change the service accounts. Right click on the server and select properties. Then go to the Security tab.
In SQL Server 2005, you would use Configuration Manager to change the service accounts.
-Sue
sqlHelp with sproc with one parameter that can contain multiple values
I am trying to get the following procedure to work and I am getting hung up
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>
Monday, March 26, 2012
HELP with SP_configure
I am running the following command:
exec sp_configure 'allow updates', '0'
I get:
Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.
What's causeing this?
Thanks!
Kenyou need to be associated (at least) with serveradmin role to be able to perform this operation. check with your sql server dba on that.
exec sp_configure 'allow updates', '0'
I get:
Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.
What's causeing this?
Thanks!
Kenyou need to be associated (at least) with serveradmin role to be able to perform this operation. check with your sql server dba on that.
Help with SP or Function
I was trying to write this MDX but I am getting errors so maybe I should
create a stored procedure against my CUBE.
I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.
can someone provide an example?
My cube provides the total revenue for any date. I thought I could take the
avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg
'
avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg
'
and get a value but I am getting an error message.What error message? Where?
ML
http://milambda.blogspot.com/
create a stored procedure against my CUBE.
I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.
can someone provide an example?
My cube provides the total revenue for any date. I thought I could take the
avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg
'
avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg
'
and get a value but I am getting an error message.What error message? Where?
ML
http://milambda.blogspot.com/
Help with SP
I have a webform that I need to insert data into a db. I would like to have a stored procedure handle the insert for me.
Here are the data fields from the form:
<td><asp:textbox id="f_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="l_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="emp_num" runat="server"></asp:textbox></td>
<td><asp:textbox id="email" runat="server"></asp:textbox></td>
<td><asp:dropdownlist id=dd1 runat="server" DataMember="Line_Item" DataSource="<%# dsLineItem1 %>" DataTextField="LI_ID" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd2 runat="server" DataMember="Component" DataSource="<%# dsComponent1 %>" DataTextField="Component" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd3 runat="server" DataMember="Activity" DataSource="<%# dsActivity1 %>" DataTextField="Abbrev" DataValueField="ID"></asp:dropdownlist></td>
Needed Solution:
I need to create a stored procedure that will insert the data from the above fields into the following database:
ProfileDB
Table columns:
id
f_name
l_name
emp_num
email
line_item
component
activity
I am sure that there are some rules for this I am unaware of (such as field names must match table column names etc.).
Thank you for your help.
BTW,
My version of SQL is 2000.
Thank you again.
Sincerely,
TimI have constucted a stored procedure that is at the least error free:
If you have any comments or suggestions regarding this sp I would greatly appreciate your input since I am new to SQL.
CREATE PROCEDURE dbo.InsertProfile
(
@.F_Name [varchar] (100),
@.L_Name [varchar] (100),
@.Emp_Num [numeric] (9),
@.Email [varchar] (250),
@.Line_Item [varchar] (250),
@.Component [varchar] (250),
@.Activity [varchar] (250)
)
AS
Insert into [dbo.InsertProfile]
(
[F_Name],
[L_Name],
[Emp_Num],
[Email],
[Line_Item],
[Component],
[Activity]
)
Values
(@.F_Name, @.L_Name, @.Emp_Num, @.Email, @.Line_Item, @.Component, @.Activity)
GO
Thank you.|||OK
You might want to check for errors
DECLARE @.Error, @.Rowcount
...sql statement
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.Rowcount
Then interogate thos values...if @.Error is other than 0, then you have a problem...
And if @.Rowcount doesn't = 1 the that's a problem as well (don't know how that would ever happen, but it's a check)
Here are the data fields from the form:
<td><asp:textbox id="f_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="l_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="emp_num" runat="server"></asp:textbox></td>
<td><asp:textbox id="email" runat="server"></asp:textbox></td>
<td><asp:dropdownlist id=dd1 runat="server" DataMember="Line_Item" DataSource="<%# dsLineItem1 %>" DataTextField="LI_ID" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd2 runat="server" DataMember="Component" DataSource="<%# dsComponent1 %>" DataTextField="Component" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd3 runat="server" DataMember="Activity" DataSource="<%# dsActivity1 %>" DataTextField="Abbrev" DataValueField="ID"></asp:dropdownlist></td>
Needed Solution:
I need to create a stored procedure that will insert the data from the above fields into the following database:
ProfileDB
Table columns:
id
f_name
l_name
emp_num
line_item
component
activity
I am sure that there are some rules for this I am unaware of (such as field names must match table column names etc.).
Thank you for your help.
BTW,
My version of SQL is 2000.
Thank you again.
Sincerely,
TimI have constucted a stored procedure that is at the least error free:
If you have any comments or suggestions regarding this sp I would greatly appreciate your input since I am new to SQL.
CREATE PROCEDURE dbo.InsertProfile
(
@.F_Name [varchar] (100),
@.L_Name [varchar] (100),
@.Emp_Num [numeric] (9),
@.Email [varchar] (250),
@.Line_Item [varchar] (250),
@.Component [varchar] (250),
@.Activity [varchar] (250)
)
AS
Insert into [dbo.InsertProfile]
(
[F_Name],
[L_Name],
[Emp_Num],
[Email],
[Line_Item],
[Component],
[Activity]
)
Values
(@.F_Name, @.L_Name, @.Emp_Num, @.Email, @.Line_Item, @.Component, @.Activity)
GO
Thank you.|||OK
You might want to check for errors
DECLARE @.Error, @.Rowcount
...sql statement
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.Rowcount
Then interogate thos values...if @.Error is other than 0, then you have a problem...
And if @.Rowcount doesn't = 1 the that's a problem as well (don't know how that would ever happen, but it's a check)
Help with some SQL pls
I have this SP:
CREATE PROCEDURE GetFarmDetails
(@.FarmID varchar(200))
AS
SELECT Name, Address1,Address2, County,Postcode,TelNo,FaxNo,MobileNo,Contact,CustNo,StatusID,RepID FROM Customers
WHERE CustomerID = @.CustomerID
RETURN
GO
But I want the SQL to find the StatusName and RepName from the Status and Rep table according to the StatusID and RepID returned above. Could this be added to the above SQL and I get the StatusName and RepName from the one SP?
Thanks for any help.
Simon
yes do you have any PK-FK relation with the other two tables youmentioned? you need to provide us the data structure too for someone tohelp you out..|||
Yes there are Pk-FK relationships between Rep and Status tables and Customers.
Thanks
Simon
|||Sorry, the SP should read:CREATE PROCEDURE GetFarmDetails
(@.CustomerIDvarchar(200))
AS
SELECT Name, Address1,Address2, County,Postcode,TelNo,FaxNo,MobileNo,Contact,CustNo,StatusID,RepID FROM Customers
WHERE CustomerID = @.CustomerID
RETURN
GO
Simon
|||
Hello,
Here is the Solution.
SELECT Status.StatusName , Rep.RepName FROM
Customer C
LEFT OUTER JOIN Status
ON StatusID = C.StatusID
LEFT OUTER JOIN Rep
ON RepID = C.RepID
WHERE
CustomerID = @.CustomerID
Here you go little one,
Hope it will work out well
Regards
KeanXSoul
|||Just the job, will post the final SP later for others.Simon
Help with slow query please...
I've inherited a piece of SQL that takes an age to run (it's dynamically
created, not in a stored procedure)
---
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA =
tableA.fieldA
INNER JOIN tableC
ON tableC.fieldC =
tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
AND
ISNULL(tableC.fieldF, 0) = 0
ORDER BY
tableA.fieldB
---
I've checked the database when this runs and it shows object locks on tableB
and tableC (at the page and table level).
I changed the SQL so that each table name in the FROM clause has the 'WITH
(NOLOCK)' hint on, but this led to no obvious improvement, and the object
locks remained the same.
So, I looked at the execution plan. Nothing horrific...one nested loop
(55%) and one table spool/lazy spool (32%).
So, I ran an index analysis.
This suggested that I create the following index:
CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
[fieldA] ASC )
However, when I check tableB, it already has the following indexes:
1 - clustered index on fieldC
2 - non-clustered index on fieldA and fieldC
Okay, the for the existing non-clustered index, the left and right columns
have been swapped, and I know that the left column is the all important on,
but I'm not sure what to do here...
I could either:
a - re-jig the non-clustered index to swap the columns around, but I've no
idea whether this will improve things and presumably it may break it for
other queries?
b - remove the index altogether and replace it with a single column index
for fieldA (as there's already a single columned clustered index on fieldC).
Suggestions please....
Thanks
GriffTry,
SELECT DISTINCT
tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN
tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN
tableC
ON tableC.fieldC = tableB.fieldC
and tableC.fieldD IN ('para1', 'para2')
AND tableC.fieldE = 'para3'
AND (tableC.fieldF = 0 or tableC.fieldF is null)
ORDER BY
tableA.fieldB;
Post tables definition including primary keys, DRI constraints and indexes
for a better analysis.
AMB
"Griff" wrote:
> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Griff wrote:
> ISNULL(tableC.fieldF, 0) = 0
This is a problem right here. Never apply a function against a table column
in the WHERE clause: it forces a table scan. Unfortunately in this case
(where you want either 0 or Null), it's going to be tough to create a
sargable criterion. You can try
(tableC.fieldF = 0 OR tableC.fieldF IS NULL)
but I suspect this will be non-sargable as well (due to the OR condition)
You can also try a subquery containing a union:
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN (
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF IS NULL
UNION ALL
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF = 0) C
ON C.fieldC =tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
ORDER BY
tableA.fieldB
Of course, the DISTINCT keyword isn't helping performance. Is there any way
to eliminate that?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Tried your suggestion, but it actually took a bit longer...(3 minutes cf 2.5
minutes)
However, now got it down to sub 1 second by re-ordering the inner joins!
I think that this is due to the fact that first time I was getting a small
amount of data in tableA and inner joining it with a huge amount of data in
tableB (and a fairly huge amount of data in tableC).
I changed the order so that it now gets the big table first and inner joins
it with the smaller table. I'm not sure WHY it's faster as it's got to be
doing the same net amount of work, but the query plan is significantly
different.
Now, the only thing left to do is to change the ASP code that generates the
dynamic ASP code.
Thanks everyone...
Griff|||Have you consider running DBCC DBREINDEX and/or DBCC SHOWCONTIG against the
largest tables + DBCC INDEXDEFRAG?
"Griff" wrote:
> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Hi Bob
Thanks for the response. As you can see from one of my responses in this
thread, I've actually solved the problem.
Latest incarnation of the SQL simply changed the table from which the first
field is retrieved from:
> SELECT
> DISTINCT tableA.fieldA
To:
> SELECT
> DISTINCT tableB.fieldA
And the query gone from ~3 mins to sub 1 second. Amazing!
Not sure how I would get rid of the DISTINCT clause...it does exactly what
I need it to do.
Anyhow, I've learnt two things from your post [1 - not using a function; 2 -
the word "sargable" (which I had to look up)] so a day definitely not
wasted.
Thx
Griff|||Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in
> this thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the
> first field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does
> exactly what I need it to do.
>
There may be another way to accomplish the same task. Without details about
your database structure, it is impossible to make any suggestions.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Griff,
You did not post DDL (another abbreviation to look up :-)), so we cannot
tell what the keys of the different tables are, and what the real
indexes are.
The fact that changing tableA.fieldA to tableB.fieldA increases the
performance with orders of magnitude indicates that you might not have
properly defined the keys (or unique indexes).
Also, in this case you should pay particular attention to the indexes of
tableC, especially if it contains many rows. Since you dit not post DDL,
I cannot give a real suggestion here.
Gert-Jan
Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in this
> thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the firs
t
> field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does exactly wha
t
> I need it to do.
> Anyhow, I've learnt two things from your post [1 - not using a function; 2
-
> the word "sargable" (which I had to look up)] so a day definitely not
> wasted.
> Thx
> Griffsql
created, not in a stored procedure)
---
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA =
tableA.fieldA
INNER JOIN tableC
ON tableC.fieldC =
tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
AND
ISNULL(tableC.fieldF, 0) = 0
ORDER BY
tableA.fieldB
---
I've checked the database when this runs and it shows object locks on tableB
and tableC (at the page and table level).
I changed the SQL so that each table name in the FROM clause has the 'WITH
(NOLOCK)' hint on, but this led to no obvious improvement, and the object
locks remained the same.
So, I looked at the execution plan. Nothing horrific...one nested loop
(55%) and one table spool/lazy spool (32%).
So, I ran an index analysis.
This suggested that I create the following index:
CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
[fieldA] ASC )
However, when I check tableB, it already has the following indexes:
1 - clustered index on fieldC
2 - non-clustered index on fieldA and fieldC
Okay, the for the existing non-clustered index, the left and right columns
have been swapped, and I know that the left column is the all important on,
but I'm not sure what to do here...
I could either:
a - re-jig the non-clustered index to swap the columns around, but I've no
idea whether this will improve things and presumably it may break it for
other queries?
b - remove the index altogether and replace it with a single column index
for fieldA (as there's already a single columned clustered index on fieldC).
Suggestions please....
Thanks
GriffTry,
SELECT DISTINCT
tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN
tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN
tableC
ON tableC.fieldC = tableB.fieldC
and tableC.fieldD IN ('para1', 'para2')
AND tableC.fieldE = 'para3'
AND (tableC.fieldF = 0 or tableC.fieldF is null)
ORDER BY
tableA.fieldB;
Post tables definition including primary keys, DRI constraints and indexes
for a better analysis.
AMB
"Griff" wrote:
> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Griff wrote:
> ISNULL(tableC.fieldF, 0) = 0
This is a problem right here. Never apply a function against a table column
in the WHERE clause: it forces a table scan. Unfortunately in this case
(where you want either 0 or Null), it's going to be tough to create a
sargable criterion. You can try
(tableC.fieldF = 0 OR tableC.fieldF IS NULL)
but I suspect this will be non-sargable as well (due to the OR condition)
You can also try a subquery containing a union:
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN (
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF IS NULL
UNION ALL
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF = 0) C
ON C.fieldC =tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
ORDER BY
tableA.fieldB
Of course, the DISTINCT keyword isn't helping performance. Is there any way
to eliminate that?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Tried your suggestion, but it actually took a bit longer...(3 minutes cf 2.5
minutes)
However, now got it down to sub 1 second by re-ordering the inner joins!
I think that this is due to the fact that first time I was getting a small
amount of data in tableA and inner joining it with a huge amount of data in
tableB (and a fairly huge amount of data in tableC).
I changed the order so that it now gets the big table first and inner joins
it with the smaller table. I'm not sure WHY it's faster as it's got to be
doing the same net amount of work, but the query plan is significantly
different.
Now, the only thing left to do is to change the ASP code that generates the
dynamic ASP code.
Thanks everyone...
Griff|||Have you consider running DBCC DBREINDEX and/or DBCC SHOWCONTIG against the
largest tables + DBCC INDEXDEFRAG?
"Griff" wrote:
> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Hi Bob
Thanks for the response. As you can see from one of my responses in this
thread, I've actually solved the problem.
Latest incarnation of the SQL simply changed the table from which the first
field is retrieved from:
> SELECT
> DISTINCT tableA.fieldA
To:
> SELECT
> DISTINCT tableB.fieldA
And the query gone from ~3 mins to sub 1 second. Amazing!
Not sure how I would get rid of the DISTINCT clause...it does exactly what
I need it to do.
Anyhow, I've learnt two things from your post [1 - not using a function; 2 -
the word "sargable" (which I had to look up)] so a day definitely not
wasted.
Thx
Griff|||Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in
> this thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the
> first field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does
> exactly what I need it to do.
>
There may be another way to accomplish the same task. Without details about
your database structure, it is impossible to make any suggestions.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Griff,
You did not post DDL (another abbreviation to look up :-)), so we cannot
tell what the keys of the different tables are, and what the real
indexes are.
The fact that changing tableA.fieldA to tableB.fieldA increases the
performance with orders of magnitude indicates that you might not have
properly defined the keys (or unique indexes).
Also, in this case you should pay particular attention to the indexes of
tableC, especially if it contains many rows. Since you dit not post DDL,
I cannot give a real suggestion here.
Gert-Jan
Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in this
> thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the firs
t
> field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does exactly wha
t
> I need it to do.
> Anyhow, I've learnt two things from your post [1 - not using a function; 2
-
> the word "sargable" (which I had to look up)] so a day definitely not
> wasted.
> Thx
> Griffsql
Help with simple insert, how to use primary key?
Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.
i want an unique identifier for each row.
heres my stored procedure:
CREATE PROCEDURE composeMessage
-- Add the parameters for the stored procedure here
@.username varchar(24),
@.sender varchar(24),
@.date dateTime,
@.subject varchar(255),
@.message varchar(2500)
AS
BEGIN
insert into Messages(
"Username",
"Sender",
"Date",
"Subject",
"Message"
)
values (
@.username,
@.sender,
@.date,
@.subject,
@.message
)
END
GO
heres my sqlcreate table:
USE [Messenger]
GO
/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Messages](
[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[ID] [int] NOT NULL,
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
As primary keycan't be null, what do i put for primary key for my insert to work?
hope you understand what i mean?
Am i right that i have to set the table designer/identity column to my primary key?
It generates an unique incresing number, so doi i use that?
|||If you want to have an increasing value, you will have to switch on the identity property on your column.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
sqlWednesday, March 21, 2012
Help with Recursive Stored Procedure
Hello,
I am having problem writing a recursive stored procedure for a Content Management System and am hoping that someone can give me a hand with it. I have posted (below) a script to recreate a table and data to test it with, and I've included my sample stored procedure that isn't working as expected. Any help would really be appreciated, I'm stuck.
I have a table which represents a hierarchical view of data. In this case, it is an example of classified ads. The hierarchy looks something like this:
............
Classifieds
--Animals
----Dogs
-----Golden Retrievers
-----Poodles
--Automobiles
............
Each of these above I call a NODE. Each NODE has a Parent Node, and since a NODE can have multiple Parent Nodes, each NODE also has a PriorParentID (which is its parent's parent node). (still with me here?) Each NODE supports many different Types of Content. What I need to do is walk the hierarchy from a specific NODE, up through each of it's parents to the top-most node. Throughout the recursion up the tree, I need to collect a distinct list of Content Types that the parents support.
So, suppose that the "Classifieds" node supports "ContentTypeID" of 3. Every ascendent node below it inherits that "ContentTypeID". So if I am looking at "Poodles", I want to walk the tree up through its parents, collecting the "ContentTypeID" all the way up, I'd end up with a "3" for a ContentTypeID for Poodles because one of it's parents in the hierarchy supports ContentTypeID "3".
And if the "Dogs" node supports a "ContentTypeID" of 1 and "Animals" supports a "ContentTypeID" of 1 and 2, then I need to get back 1, 2 and 3 in a resultset...because as you walk up the hierarchy from Poodles to Classifieds, you encounter a "1" and a "2" and a "3" for ContentTypeID's.
Here's a script to set up the data:
---------------
CREATE TABLE [tmpNodalHierarchy] (
[ChildNodeID] [int] NULL ,
[ParentNodeID] [int] NULL ,
[PriorParentNodeID] [int] NULL ,
[NodeLabel] nvarchar(50) NULL,
[ContentTypeID] [int] NULL)
GO
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(1, 0, -1, 'Classifieds', 3)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(2, 1, 0, 'Animals', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(2, 1, 0, 'Animals', 2)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(3, 1, 0, 'Automobiles', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(4, 2, 1, 'Dogs', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(5, 4, 2, 'Golden Retrievers', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(5, 4, 2, 'Golden Retrievers', 2)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(6, 4, 2, 'Poodles', 1)
INSERT INTO tmpNodalHierarchy
(ChildNodeID, ParentNodeID, PriorParentNodeID, NodeLabel, ContentTypeID)
VALUES
(7, 4, 2, 'Chows', NULL)
---------------
CREATE PROC dbo.tmpGetRecursiveContentTypes
(
@.ParentNodeID int,
@.PriorParentNodeID int,
@.ContentTypeID int = 0,
@.InContentTypes nvarchar(500) = '',
@.OutContentTypes nvarchar(500) = null OUTPUT
)
AS
IF NOT @.PriorParentNodeID IS NULL
BEGIN
SET NOCOUNT ON
DECLARE @.ContentTypes nvarchar(500)
SELECT @.ContentTypes = @.InContentTypes
DECLARE @.curContentTypeID int, @.tmpPriorParentNodeID int, @.curNodeLabel nvarchar(100), @.curChildNodeID int, @.curParentNodeID int, @.curPriorParentNodeID int
WHILE 0=0
BEGIN
SELECT TOP 1 @.curContentTypeID = ContentTypeID, @.curParentNodeID = ParentNodeID, @.curPriorParentNodeID = PriorParentNodeID FROM dbo.tmpNodalHierarchy WHERE ChildNodeID=@.ParentNodeID and ParentNodeID = @.PriorParentNodeID and (ContentTypeID > @.ContentTypeID) ORDER BY ContentTypeID ASC
IF @.@.ROWCOUNT = 0
BEGIN
SELECT @.curContentTypeID = ContentTypeID, @.curParentNodeID = ParentNodeID, @.curPriorParentNodeID = PriorParentNodeID FROM dbo.tmpNodalHierarchy WHERE ChildNodeID=@.ParentNodeID and ParentNodeID = @.PriorParentNodeID and (ContentTypeID = @.ContentTypeID) ORDER BY ContentTypeID ASC
SET @.ContentTypeID = @.curContentTypeID
BREAK
END
SET @.ContentTypeID = @.curContentTypeID
IF NOT @.curContentTypeID IS NULL
SELECT @.ContentTypes = @.ContentTypes + ',' + CAST(@.curContentTypeID as nvarchar(10))
END
EXEC dbo.tmpGetRecursiveContentTypes @.curParentNodeID,@.curPriorParentNodeID,@.ContentTypeID,@.ContentTypes
END
SET @.OutContentTypes = @.ContentTypes
IF @.@.NESTLEVEL=1
SELECT @.ContentTypes
GO
----------------
--To execute the sproc as if we wanted
--to get the ContentTypeID's for "Poodles"
--
--
EXEC tmpGetRecursiveContentTypes 4, 2
--
----------------
I have been concatenating the results, but ideally I want the resultset to look like:
ContentTypeIDs
------
1
2
3
Thanks for any help you can give.
DanHave you ever thought to use XML to present these data?|||Search booksonline for 'Expanding hierarchies', pretty good example|||I thought of using XML, but I can't use it...it needs to return a recordset because it needs to easily adapt to other databases other than SQL Server.
Dan|||Thanks for the link Dutch. It inspired an idea that worked and resulted in an even simpler solution. Thanks a lot.
Dan|||Big, I'm not going to write your stored proc for you, but I can point you in a direction. The basic problem you have is getting all parents of a leaf node. if you have all the parents, a simple select will give you all the ContentTypeID's right? Ok, so let's forget about the ContentTypeIDs and focus on the real problem - getting that list of parents given a certain node.
First: get rid of the PriorParentID - and whack yourself on the head with a blunt object 10 times repeating 'I can get the prior parent Id be looking at the parent's ParentNodeID'
Then: Please normalise your tables! One node = Many ContentTypeIDs, so:
Table1:
ChildNodeID, ParentNodeID, NodeLabel
Table2:
ChildNodeID, ContentTypeID
So you'll have this in the tables:
Table1:
1, null, Classifieds
2, 1, Animals
3, 1, Automobiles
4, 2, Dogs
5, 4, Golden...
6, 4, Poodles
7, 4, Cows
Table2:
1, 3
2, 1
2, 2
3, 1
4, 1
5, 1
5, 2
6, 1
Now all you do is this:
1 Start at leaf node
2 Add the ContentTypeIDs for the node to your list - don't add ones in the list already
3 Get the parentID
4 if the parentID is null, finish
5 Else get the node where id = parentID
6 go to 2
Recursive, which is bad for performance. So: how do we do this without recursion? Textbook answer is by using a bridge table. What is a bridge table? It's a table that lists all parent-child relationships. Example - this is a condensed version of your table1: (ChildId, ParentId)
1, null
2, 1
3, 1
4, 2
5, 4
6, 4
7, 4
This is the brige table for it: (ChildID, ParentID)
2, 1
3, 1
4, 1
5, 1
6, 1
7, 1
4, 2
5, 2
6, 2
7, 2
5, 4
6, 4
7, 4
As you can see, with one select from the bridge table I can get all parents of a node. E.g. in your example, you looked at poodles, node 6, and you wanted all parents:
select ParentID
from Bridge
where ChildId = 6
and you get all the parent nodes: 1, 2, 4 (classifieds, animals and dogs) and now with one simple join, and perhaps a distinct, you can get all the ContentTypeIds
select distinct Table2.ContentTypeID
from Bridge
join Table2
on Bridge.ChildID = 6
and Bridge.ParentID = Table2.ID
How do you build / maintain the bridge table? Triggers on Table1.
Phew... Enough for now.
Monday, March 19, 2012
Help with query plez
Hello all,
I'm trying to write a stored procedure that will prompt the user for a
date range and produce a report based on that date range and 4 incident
types. I only need count how may times these incidents happen within
the data range. The query looks like this
SELECT incident, @.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
@.Enter_Ending_Date) AND (incident = 'Customer Accident')
GROUP BY incident
This works fine, but I need to get incident = customer accident,
Customer Illness, Employee Accident and Employee Illness in my incident
table. When ever I try to add Incident = Customer Illness to this
query, I get no results. Any assistance will be greatly appreciated.First of all, how are you prompting the user for values thru Stored
procedures????
Your question is pretty hard to answer as you havent provided any info
about your table or Data...!
Do you have records that qualifies Incident = 'Customer Illness'...!
It would help people to help you if you post some DDL/DML codes to
understand where the problem is...|||sorry about the vagness,
The table I'm trying to query looks something like this
ID(int) Incident(nvarchar) DateOccured (datetime)
1200 Customer Illness 1/1/2003
1201 Customer Illness 1/2/2003
1202 Customer Accident 1/2/2003
1203 Customer Accident 1/3/2003
1204 Employee Accident 1/5/2003
1205 Employee Illness 1/6/2003
The stored procedure prompts the user for a beginning
(@.Enter_Beginning_Date) and ending date(@.Enter_Ending_Date) which uses
the DateOccured column for the specified date range. I want the query
to count the number of occurences that happen within the specified date
range. I want to create a report that looks loke this
Incident Number of Occurences
Customer Illness 2
Customer Accident 2
Employee Accident 1
Employee Illness 1
When ever I query for one incident (Customer Accident), the query will
work fine, but when I insert another statement [(incident = Customer
Illness) AND (incident = Customer Accident)] I get no results, when I
know there are at least 3 incidents of Customer Illness. Does this help?|||Based on the data you gave..
declare @.Enter_Ending_Date datetime
declare @.Enter_Beginning_Date datetime
set @.Enter_Ending_Date='1/2/2003'
set @.Enter_Beginning_Date='1/2/2003'
SELECT incident,
@.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date],
COUNT(*) AS Occurances
FROM Incident
WHERE (DateOccured BETWEEN @.Enter_Beginning_Date AND
@.Enter_Ending_Date) AND
(incident = 'Customer Illness') or (incident = 'Customer Accident')
--(incident = 'Customer Accident')
GROUP BY incident
/*******************************
RESULTSET
Customer Accident2003-01-02 00:00:00.0002003-01-02 00:00:00.0002
Customer Illness2003-01-02 00:00:00.0002003-01-02 00:00:00.0001|||On 26 Jan 2005 12:09:50 -0800, ndn_24_7 wrote:
(snip)
>The stored procedure prompts the user for a beginning
>(@.Enter_Beginning_Date) and ending date(@.Enter_Ending_Date) which uses
>the DateOccured column for the specified date range.
Hi ndn_27_7,
I presume you meant to write that the front-end app prompts the user for
these dates and passes them to SQL Server when calling the stored
procedure, as there is no way that a stored procedure will ever prompt for
input.
>When ever I query for one incident (Customer Accident), the query will
>work fine, but when I insert another statement [(incident = Customer
>Illness) AND (incident = Customer Accident)] I get no results, when I
>know there are at least 3 incidents of Customer Illness. Does this help?
The WHERE clause is checked against each row individually. No single row
will ever have both 'Customer Illness' and 'Customer Accident' in the same
column (incident).
SELECT incident, @.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE DateOccured BETWEEN @.Enter_Beginning_Date
AND @.Enter_Ending_Date
AND ( incident = 'Customer Accident'
OR incident = 'Customer Illness')
GROUP BY incident
or
SELECT incident, @.Enter_Beginning_Date AS [Beginning Date],
@.Enter_Ending_Date AS [Ending Date], COUNT(*) AS Occurances
FROM dbo.Incident
WHERE DateOccured BETWEEN @.Enter_Beginning_Date
AND @.Enter_Ending_Date
AND incident IN ('Customer Accident', 'Customer Illness')
GROUP BY incident
(Note that IN is just a short form for writing a bunch of OR's)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||It worked very good, THank you for all your assistance
Monday, March 12, 2012
Help with query
Hi,
I have a stored procedure (posted below) that returns a club ranking list
with fatsest to slowest time for a swim club based on
Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based on
the @.Rowcount variable passed in.
I would like to expand this query to return a the (single) fastest time per
strokeID held in the BBMD_Strokes table. I understand that I could use a
CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the same
query , but have read that CURSORS should be avoided due to poor
performance. Is there a prefered option to solve this ?
Niclas
CREATE procedure dbo.BBMD_GetEventRecord
@.StrokeID int,
@.DistanceID int,
@.CourseID int,
@.GenderID int,
@.AgeID int,
@.RowCount int
AS
Set ROWCOUNT @.Rowcount
SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', ' +
U.firstname as Swimmer,
R.Result,G.GalaName, G.StartDate,X.DOB
FROM BBMD_Results R
JOIN BBMD_Events E ON R.Eventid=E.EventID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
JOIN Users U ON R.UserID=U.UserID
JOIN BBMD_ExtUser X ON R.USERID=X.UserID
JOIN (SELECT R.UserID,MIN(R.Result) as RES
FROM BBMD_Results R
JOIN BBMD_Events E ON R.EventID=E.EventID
JOIN BBMD_ExtUser X ON R.UserID=X.UserID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
WHERE
E.StrokeID=@.StrokeID AND
E.Distanceid=@.DistanceID AND
E.Genderid=@.GenderID AND
E.Courseid=@.CourseID AND
R.Resulttypeid=1 AND
DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
< 100 * MONTH(X.DOB) + DAY(X.DOB)
THEN 1 ELSE 0 END
BETWEEN (SELECT YearMin From BBMD_YearGroups
WHERE YearGroupID= @.Age)
AND
(SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
Group By R.UserID) AS MinR ON minR.Res=R.result
AND minR.UserID=R.UserID
GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
S.StrokeName,D.DistanceName, G.StartDate,X.DOB
ORDER BY RESULT
GOPlease send the table DDL and sample data as INSERT statements, and what the
expected output looks like. Without that information, we are guessing and
the quality of help is sub-optimal..
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:%23roRJf5lGHA.1488@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a stored procedure (posted below) that returns a club ranking list
> with fatsest to slowest time for a swim club based on
> Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based
> on the @.Rowcount variable passed in.
> I would like to expand this query to return a the (single) fastest time
> per strokeID held in the BBMD_Strokes table. I understand that I could use
> a CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the
> same query , but have read that CURSORS should be avoided due to poor
> performance. Is there a prefered option to solve this ?
> Niclas
> CREATE procedure dbo.BBMD_GetEventRecord
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
> AS
> Set ROWCOUNT @.Rowcount
> SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', '
> + U.firstname as Swimmer,
> R.Result,G.GalaName, G.StartDate,X.DOB
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.Eventid=E.EventID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
> JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
> JOIN Users U ON R.UserID=U.UserID
> JOIN BBMD_ExtUser X ON R.USERID=X.UserID
> JOIN (SELECT R.UserID,MIN(R.Result) as RES
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.EventID=E.EventID
> JOIN BBMD_ExtUser X ON R.UserID=X.UserID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> WHERE
> E.StrokeID=@.StrokeID AND
> E.Distanceid=@.DistanceID AND
> E.Genderid=@.GenderID AND
> E.Courseid=@.CourseID AND
> R.Resulttypeid=1 AND
> DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
> WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
> < 100 * MONTH(X.DOB) + DAY(X.DOB)
> THEN 1 ELSE 0 END
> BETWEEN (SELECT YearMin From BBMD_YearGroups
> WHERE YearGroupID= @.Age)
> AND
> (SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
> Group By R.UserID) AS MinR ON minR.Res=R.result
> AND minR.UserID=R.UserID
> GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
> S.StrokeName,D.DistanceName, G.StartDate,X.DOB
> ORDER BY RESULT
> GO
>|||
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
Why is everything in your world an identifier? Explain what an
"age_id" is? Likewise, what is a gender_id? Gee, everyone else uses
an ISO gender_code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
I have a stored procedure (posted below) that returns a club ranking list
with fatsest to slowest time for a swim club based on
Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based on
the @.Rowcount variable passed in.
I would like to expand this query to return a the (single) fastest time per
strokeID held in the BBMD_Strokes table. I understand that I could use a
CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the same
query , but have read that CURSORS should be avoided due to poor
performance. Is there a prefered option to solve this ?
Niclas
CREATE procedure dbo.BBMD_GetEventRecord
@.StrokeID int,
@.DistanceID int,
@.CourseID int,
@.GenderID int,
@.AgeID int,
@.RowCount int
AS
Set ROWCOUNT @.Rowcount
SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', ' +
U.firstname as Swimmer,
R.Result,G.GalaName, G.StartDate,X.DOB
FROM BBMD_Results R
JOIN BBMD_Events E ON R.Eventid=E.EventID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
JOIN Users U ON R.UserID=U.UserID
JOIN BBMD_ExtUser X ON R.USERID=X.UserID
JOIN (SELECT R.UserID,MIN(R.Result) as RES
FROM BBMD_Results R
JOIN BBMD_Events E ON R.EventID=E.EventID
JOIN BBMD_ExtUser X ON R.UserID=X.UserID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
WHERE
E.StrokeID=@.StrokeID AND
E.Distanceid=@.DistanceID AND
E.Genderid=@.GenderID AND
E.Courseid=@.CourseID AND
R.Resulttypeid=1 AND
DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
< 100 * MONTH(X.DOB) + DAY(X.DOB)
THEN 1 ELSE 0 END
BETWEEN (SELECT YearMin From BBMD_YearGroups
WHERE YearGroupID= @.Age)
AND
(SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
Group By R.UserID) AS MinR ON minR.Res=R.result
AND minR.UserID=R.UserID
GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
S.StrokeName,D.DistanceName, G.StartDate,X.DOB
ORDER BY RESULT
GOPlease send the table DDL and sample data as INSERT statements, and what the
expected output looks like. Without that information, we are guessing and
the quality of help is sub-optimal..
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:%23roRJf5lGHA.1488@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a stored procedure (posted below) that returns a club ranking list
> with fatsest to slowest time for a swim club based on
> Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based
> on the @.Rowcount variable passed in.
> I would like to expand this query to return a the (single) fastest time
> per strokeID held in the BBMD_Strokes table. I understand that I could use
> a CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the
> same query , but have read that CURSORS should be avoided due to poor
> performance. Is there a prefered option to solve this ?
> Niclas
> CREATE procedure dbo.BBMD_GetEventRecord
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
> AS
> Set ROWCOUNT @.Rowcount
> SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', '
> + U.firstname as Swimmer,
> R.Result,G.GalaName, G.StartDate,X.DOB
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.Eventid=E.EventID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
> JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
> JOIN Users U ON R.UserID=U.UserID
> JOIN BBMD_ExtUser X ON R.USERID=X.UserID
> JOIN (SELECT R.UserID,MIN(R.Result) as RES
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.EventID=E.EventID
> JOIN BBMD_ExtUser X ON R.UserID=X.UserID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> WHERE
> E.StrokeID=@.StrokeID AND
> E.Distanceid=@.DistanceID AND
> E.Genderid=@.GenderID AND
> E.Courseid=@.CourseID AND
> R.Resulttypeid=1 AND
> DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
> WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
> < 100 * MONTH(X.DOB) + DAY(X.DOB)
> THEN 1 ELSE 0 END
> BETWEEN (SELECT YearMin From BBMD_YearGroups
> WHERE YearGroupID= @.Age)
> AND
> (SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
> Group By R.UserID) AS MinR ON minR.Res=R.result
> AND minR.UserID=R.UserID
> GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
> S.StrokeName,D.DistanceName, G.StartDate,X.DOB
> ORDER BY RESULT
> GO
>|||
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
Why is everything in your world an identifier? Explain what an
"age_id" is? Likewise, what is a gender_id? Gee, everyone else uses
an ISO gender_code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
help with query
Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
================================================== ==
attempt at storedprocedure
================================================== ==
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
================================================== ==
table structure being used
================================================== ==
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
I think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
================================================== ==
attempt at storedprocedure
================================================== ==
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
================================================== ==
table structure being used
================================================== ==
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
I think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
help with query
Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
==================================================== attempt at storedprocedure
==================================================== SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
==================================================== table structure being used
==================================================== CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOI think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
==================================================== attempt at storedprocedure
==================================================== SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
==================================================== table structure being used
==================================================== CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOI think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
help with query
Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
================================================== ==
attempt at storedprocedure
================================================== ==
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
================================================== ==
table structure being used
================================================== ==
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOI think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
Friday, March 9, 2012
help with query
Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
========================================
============
attempt at storedprocedure
========================================
============
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
========================================
============
table structure being used
========================================
============
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOHere are a couple of possible solutions:
Select C.ID, C.Name, F.ID, F.Title
, (Select Count(*) From Topic As T1 Where T1.F_Id = F.Id) As TopicCount
, (Select Count(*)
From Post As P2
Join Topic As T2
On P2.T_Id = T2.ID
Where T2.F_Id = F.Id) As PostCount
, (Select Max(P3.[DateEntered]
From Post As P3
Join Topic As T3
On P3.T_Id = T3.ID
Where T3.F_Id = F.Id) As LastPostDate
From Category C
Join Forum F
On F.cat_ID = C.ID
Note that you do not need the join to Posts in the main query here. That avo
ids
your duplication problem.
Another, cleaner solution would be:
Select C.ID, C.Name, F.ID, F.Title
, Stats.PostCount
, Stats.TopicCount
, Stats.LastPostDate
From Category C
Join Forum F
On F.cat_ID = C.ID
Join (
Select F_Id
, Count(*) As PostCount
, Count(Distinct(T1.Id) As TopicCount
, Max(P1.DateEntered) As LastPostDate
From Post As P1
Join Topic As T1
On P1.T_Id = T3.ID
Group By F_Id
) As Stats
On F.ID = Stats.F_Id
HTH
Thomas|||I think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
========================================
============
attempt at storedprocedure
========================================
============
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
========================================
============
table structure being used
========================================
============
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOHere are a couple of possible solutions:
Select C.ID, C.Name, F.ID, F.Title
, (Select Count(*) From Topic As T1 Where T1.F_Id = F.Id) As TopicCount
, (Select Count(*)
From Post As P2
Join Topic As T2
On P2.T_Id = T2.ID
Where T2.F_Id = F.Id) As PostCount
, (Select Max(P3.[DateEntered]
From Post As P3
Join Topic As T3
On P3.T_Id = T3.ID
Where T3.F_Id = F.Id) As LastPostDate
From Category C
Join Forum F
On F.cat_ID = C.ID
Note that you do not need the join to Posts in the main query here. That avo
ids
your duplication problem.
Another, cleaner solution would be:
Select C.ID, C.Name, F.ID, F.Title
, Stats.PostCount
, Stats.TopicCount
, Stats.LastPostDate
From Category C
Join Forum F
On F.cat_ID = C.ID
Join (
Select F_Id
, Count(*) As PostCount
, Count(Distinct(T1.Id) As TopicCount
, Max(P1.DateEntered) As LastPostDate
From Post As P1
Join Topic As T1
On P1.T_Id = T3.ID
Group By F_Id
) As Stats
On F.ID = Stats.F_Id
HTH
Thomas|||I think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
help with query
Using the included stored procedure I am getting the following result.
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
========================================
============
attempt at storedprocedure
========================================
============
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPost
Date
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
========================================
============
table structure being used
========================================
============
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOI think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
However I need this to return the DISTINCT forum.ID from the tables while
maintaining the correct post count, topic count and last post date.
Columns
c_ID t_ID f_ID c_Name f_Titlle TopicCount PostCount LastPostDate
Results
2 1 55 Equipment Lures 4 1 2005-04-20 15:27:41.047
2 2 55 Equipment Lures 4 0 NULL
2 3 55 Equipment Lures 4 1 2005-04-20 15:27:41.093
2 4 55 Equipment Lures 4 0 NULL
2 5 57 Equipment Boats & Motors 4 2 2005-04-20 15:27:41.077
2 6 57 Equipment Boats & Motors 4 0 NULL
2 7 57 Equipment Boats & Motors 4 0 NULL
2 8 57 Equipment Boats & Motors 4 0 NULL
1 9 52 Fishing Bass Fishing 2 1 2005-04-20 15:27:41.077
1 10 52 Fishing Bass Fishing 2 0 NULL
1 11 53 Fishing Crappie Fishing 1 0 NULL
1 12 54 Fishing Cat Fishing 1 0 NULL
========================================
============
attempt at storedprocedure
========================================
============
SELECT C.ID AS c_ID, T.ID as t_ID, f.ID AS f_ID,
C.Name AS c_Name,
F.Title AS f_Title,
(SELECT COUNT(ID) FROM Topic WHERE [f_ID]=F.ID) AS TopicCount,
(SELECT COUNT(ID) FROM Post WHERE [t_ID]=T.ID) AS PostCount,
(SELECT MAX([Date Entered]) FROM Post WHERE [t_ID]=T.ID) AS LastPost
Date
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON t.f_ID = F.ID
========================================
============
table structure being used
========================================
============
CREATE TABLE [dbo].[Post] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[t_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL ,
[r_ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Category] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topic] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[Date Entered] [datetime] NOT NULL ,
[f_id] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Forum] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[cat_ID] [numeric](18, 0) NOT NULL ,
[Date Entered] [datetime] NOT NULL
) ON [PRIMARY]
GOI think that this is what you need:
SELECT C.ID AS c_ID, f.ID AS f_ID, C.Name AS c_Name, F.Title AS
f_Title,
COUNT(DISTINCT t.ID) as TopicCount,
COUNT(*) AS PostCount,
MAX(P.[Date Entered]) AS LastPostDate
FROM Category C
INNER JOIN Forum F ON F.cat_ID = C.ID
INNER JOIN Topic T ON T.f_ID = F.ID
INNER JOIN Post P ON P.t_ID = T.ID
GROUP BY C.ID, f.ID, C.Name, F.Title
It would have been useful if you also posted sample data (as INSERT
statements) and expected results; see: http://www.aspfaq.com/5006
Razvan
help with proc
i wrote a procedure which calculates how many minutes a person is late to th
e
office on a particular day
what is the best method for converting it to calculate the minutes late for
the
whole monthHi Raghu
You can sum up all the minutes and show it as a monthly late.
just see if this helps you:
SELECT
NAME, SUM( DATEDIFF(mi, timearrived, officetime ) ),
datepart(month,timearrived)
FROM ATTENDANCE
GROUP BY NAME, datepart(month,timearrived)
please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"raghu veer" wrote:
> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||Raghu
use datediff(m.date1,date2) in query
date1 or date1 is stored using getdate() default so that the time is also
stored in databse
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:
> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||I mean
use datediff('mi'.date1,date2) in query
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Raghu
> use datediff(m.date1,date2) in query
> date1 or date1 is stored using getdate() default so that the time is also
> stored in databse
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "raghu veer" wrote:
>
e
office on a particular day
what is the best method for converting it to calculate the minutes late for
the
whole monthHi Raghu
You can sum up all the minutes and show it as a monthly late.
just see if this helps you:
SELECT
NAME, SUM( DATEDIFF(mi, timearrived, officetime ) ),
datepart(month,timearrived)
FROM ATTENDANCE
GROUP BY NAME, datepart(month,timearrived)
please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"raghu veer" wrote:
> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||Raghu
use datediff(m.date1,date2) in query
date1 or date1 is stored using getdate() default so that the time is also
stored in databse
--
Regards
R.D
--Knowledge gets doubled when shared
"raghu veer" wrote:
> i wrote a procedure which calculates how many minutes a person is late to
the
> office on a particular day
> what is the best method for converting it to calculate the minutes late fo
r
> the
> whole month|||I mean
use datediff('mi'.date1,date2) in query
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Raghu
> use datediff(m.date1,date2) in query
> date1 or date1 is stored using getdate() default so that the time is also
> stored in databse
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "raghu veer" wrote:
>
Subscribe to:
Posts (Atom)