Count Item Number

Hello;

Which is the easy way to do this:

I have 2 tables:Work_Order_Header and Work_Order_Detail.

I need to generate the Item Number from 1 to "qty of items" when I make the JOIN on WorkOrderNumber.

Example:

WorkOrderNumber WorkOrderItem WorkOrderAmt

122 1 10.00

122 2 15.25

122 3 24.37

How I generate the WorkOrderItem using a select with a JOIN on the 2 tables?

if you are working on SQL Server 2005, you can use the following (samples tables from Northwind database, as you provided no DDL and sample data)

SELECT ROW_NUMBER() OVER (PARTITION BY O.OrderId order by O.OrderDate),O.*,OD.* FROm

Orders O

INNER JOIN [Order details] OD

ON O.orderid = od.OrderID

WHERE O.orderid <=10251

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you Jens, but I am working with SQL 2000 I forgot to mentioned.|||A sample you be something like:

SELECT (SELECT COUNT(*) FROM [Order details] OD2 WHERE OD2.orderID = O.OrderID AND OD2.ProductID >=OD.ProductID) AS Number ,O.*,OD.* FROm

Orders O

INNER JOIN [Order details] OD

ON O.orderid = od.OrderID

WHERE O.orderid <=10251

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Did that solve your problem ?sql

Count Frequencies of row using sql query

I'm new to t-sql, but i would like to know how to generate a frequency of a row, let say i would like to have a query to select the number of times the result of 'Good' shows up, and number of times the result of 'Perfect' shows up in the data provided. i've got like thousands of rows with hundreds of different result groups, in the data sample below i just use 3(Good, Perfect, and Bad) of them. can anyone give me a suggestion on how to do this? thanks.

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

SELECTCOUNT(Result)[ResultBad]FROM #Count where result ='Bad'

groupby result

DROPTABLE #Count

ResultBad

2

Check for "group by" clause in BOL.

select Result, Count(*) as cnt

from #Count

group by Result

order by Result

go

AMB

|||

Code Snippet

select ResultBad= sum(case Result when 'Bad' then 1 else 0 end) ,
ResultGood= sum(case Result when 'Good' then 1 else 0 end) ,
ResultPerfect= sum(case Result when 'Perfect' then 1 else 0 end)
from #count

You can also use PIVOT if you are using Sql Server 2005...

http://msdn2.microsoft.com/en-us/library/ms177410(SQL.90).aspx

|||

You have already discovered or learned how to find dupes.

Are you asking how to calculate a probability?

Such as:

Code Snippet

CREATETABLE #Count(Result VARCHAR(60))

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Bad')

INSERTINTO #Count(Result)VALUES('Perfect')

INSERTINTO #Count(Result)VALUES('Good')

DECLARE @.frequency asint

DECLARE @.Count asint

DECLARE @.Probability asint

SET @.Frequency =(SELECTCOUNT(Result)[ResultBad] FROM #Count where result ='Perfect'

groupby result)

SET @.Count =(SELECTCOUNT(*)FROM #Count)

SET @.Probability = @.Frequency * 100 / @.Count

SELECTCAST(@.Probability as varchar(5))+'% Chance'

DROPTABLE #Count

Adamus

|||Thanks y'all, i should have known that i only need to modify my select statement. Sorry for confusion, but thanks!!

could not find stored procedure 'dbo.sp_MSdropfkreferencingarticle'

Hi, I'm trying to generate a snapshot of a database (running on SQL 2005 SP1) prior to starting transactional replication to an SQL 7.0 server. After generating the snapshot, the agent fails with the error message 'could not find stored procedure 'dbo.sp_MSdropfkreferencingarticle' and suggests lookin at error number 2812 (which offers no help).

I was convinced this worked prior to installing SP1, but that testing was done some time ago....

Andy, this is a bug we need to fix (I am guessing you are using "truncate" as the pre-creation command). To workaround for now, you can:

1) Use drop as the pre-creation command

2) Create a dummy procedure "sp_MSdropfkreferencingarticle" at your 70 subscriber

-Raymond

|||Thanks Raymond, creating the dummy stored procedure worked OK without changing the pre-creation command from truncate to drop.

Cost of using Analysis Service

We have purchased SQL Server 2005, and we are planning to use the Reporting Services and Analysis Services to generate reports, KPIs and perform some OLAP analysis.

My question is that apart from the server licence of SQL Server, is there any other licence cost for the solution deployment, like developer licence, client access licence, etc.?

Thanks,

Chris

No, if these are all part of your SQL Server license. So if you have approriate licensing for your SQL Server you are also licensed to use SSRS and SSAS. But note that the SQL Licensing is per server, so if you want to put SSRS or SSAS on different hardware, they will need their own licenses at that point.|||

Darren Gosbell wrote:

No, if these are all part of your SQL Server license. So if you have approriate licensing for your SQL Server you are also licensed to use SSRS and SSAS. But note that the SQL Licensing is per server, so if you want to put SSRS or SSAS on different hardware, they will need their own licenses at that point.

Do you mean the end-users do not need any additional software to view the reports and OLAP cubes?

How? Please advise.

Thanks,

Chris