How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.
In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...
I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...
My problem is one of my query is returing party_codes . now i also want a column which returns serial numbers along with it . the serial numbers are not stored anywhere. they should be autogenerated. My query is combining two different databases and its using union in it so i can not use count in it . Is there any other way i can acheive . for eg Now my query output is party_code ---------- R06048 R06600 R06791 (3 row(s) affected) I want it like party_code serial number ---------- ------------- R06048 1 R06600 2 R06791 3 (3 row(s) affected) The serial number column should be auto generated in the select statement it self Is there any system rowid i can use . Please suggest
I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)
Hello --We have annual values for several 'MeasName':Capital expenditure incrementGrowth rateSubscribersThe table has these fields:YearMeasNameMeasValueWe want the result of the crosstab to look like:MeasName 2005 2006 2007 2008 2009-----------------------------------------------------------------CapexIncrement 33 33 41 41 41GrowthRate 0 .1 .1 .1 .1Subscribers 42000 46000 50000 55000 60000The code below results in:CapexIncremt 33 0 0 0 0CapexIncremt 0 33 0 0 0CapexIncremt 0 0 41 0 0CapexIncremt 0 0 0 41 0CapexIncremt 0 0 0 0 41GrowthRate 0 0.1 0 0 0GrowthRate 0 0 0.1 0 0GrowthRate 0 0 0 0.1 0GrowthRate 0 0 0 0 0.1Subscribers 42000 0 0 0 0Subscribers 0 46000 0 0 0Subscribers 0 0 50000 0 0Subscribers 0 0 0 55000 0Subscribers 0 0 0 0 60000SELECT MeasName,SUM(CASE Yr WHEN 2005 THEN MeasValue ELSE 0 END) AS '2005',SUM(CASE Yr WHEN 2006 THEN MeasValue ELSE 0 END) AS '2006',SUM(CASE Yr WHEN 2007 THEN MeasValue ELSE 0 END) AS '2007',SUM(CASE Yr WHEN 2008 THEN MeasValue ELSE 0 END) AS '2008',SUM(CASE Yr WHEN 2009 THEN MeasValue ELSE 0 END) AS '2009'FROM MetricsTimeGROUP BY Yr, MeasNameCan anyone tell me how to change the code to result in the layout wewant?Thanks for any help.Larry Mehl
I have a group in my report. This group is showing only the Top 50 results. Inside of group row is a total column. I would like to display the total of the totals in the table footer. Something like this:
This is my data:
Creature Kind Number
Frog Amphibian 3
Cat Feline 10
Lizard Amphibian 20
Cow Mammal 8
Group is on Kind. Limited to the Top 50 Sum(Number). So I want the report to look like this:
Amphibian 23
Feline 10
Mammal 8
Total Creatures 41
I can't get the correct Total Creatures because the data is limited to the Top 50 Sum(Number). So right now Total Creatures is adding up to be every creature in the database. I just want the Total Top 50 Sum(Number) Creatures.
Client ID Client Name Date Score 1 Smith 12/31/2014 25 1 Smith 10/15/2014 45 2 John 08/11/2014 55 2 John 06/18/2014 15 3 Rose 04/15/2014 12 4 Mike 07/23/2014 28 5 Mary 01/5/2014 56 6 Lisa 08/1/2014 54 6 Lisa 05/10/2014 34
Now I want to use Row Number function or any way where I can get the result as below
Client ID Client Name Date Score RowNo 1 Smith 12/31/2014 25 1 1 Smith 10/15/2014 45 2 2 John 08/11/2014 55 1 2 John 06/18/2014 15 2 3 Rose 04/15/2014 12 1 4 Mike 07/23/2014 28 1 5 Mary 01/5/2014 56 1 6 Lisa 08/1/2014 54 1 6 Lisa 05/10/2014 34 2
I have a table with first name, last name, SSN(social security number)and other columns.I want to assign group number according to this business logic.1. Records with equal SSN and (similar first name or last name) belongto the same group.John Smith 1234Smith John 1234S John 1234J Smith 1234John Smith and Smith John falls in the same group Number as long asthey have similar SSN.This is because I have a record of equal SSN but the first name andlast name is switched because of people who make error inserting lastname as first name and vice versa. John Smith and Smith John will haveequal group Name if they have equal SSN.2. There are records with equal SSN but different first name and lastname. These belong to different group numbers.Equal SSN doesn't guarantee equal group number, at least one of thefirst name or last name should be the same. John Smith and Dan Brownwith equal SSN=1234 shouldn't fall in the same group number.Sample data:Id Fname lname SSN grpNum1 John Smith 1234 12 Smith John 1234 13 S John 1234 14 J Smith 1234 15 J S 1234 16 Dan Brown 1234 27 John Smith 1111 3I have tried this code for 65,000 rows. It took 20 minute. I have torun it for 21 million row data. I now that this is not an efficientcode.INSERT into temp_FnLnSSN_grpSELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,(SELECT 1 + count(*)FROM distFLS AS c2WHERE c2.ssn < c1.ssnor (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =substring(c1.fname,1,1) or substring(c2.lname,1,1) =substring(c1.lname,1,1)or substring(c2.fname,1,1) =substring(c1.lname,1,1) or substring(c2.lname,1,1) =substring(c1.fname,1,1)))) AS group_numberFROM distFLS AS c1JOIN tu_people_data AS c3ON (c1.ssn = c3.ssn andc1.fname = c3.fname andc1.lname= c3.lname)dist FLS is distinct First Name, last Name and SSN table from thepeople table.I have posted part of this question, schema one week ago. Please referthis thread.http://groups.google.com/group/comp...6eb380b5f2e6de6
I have a sql statement and one of the arguments I want to pass is a comma delimited set of numbers. It keeps getting turned into a string. How do I keep that from happening. Here is kind of what it looks likeSelect FirstNamefrom Userwhere NameID in (5,6,7)or Select FirstNamefrom Userwhere NameID in (@NameIDList)There is no error code just nothing returns. If I take out the @ANameIDList and put the values I want, it returns the correct results.Thanks,Bryan PS the link to the original thread it here http://forums.asp.net/1046154/ShowPost.aspx
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners num1, num2, num3, num4, num5, num6 33 52 47 23 17 28 ... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
I'm trying to calculate some leagues for a website i run usuing some quite complicated querys. As the leagues are calculated with such somplicated criteria, i've had to build the leagues inot a temporary table.
What i need to be able to do is update the temp table with the results of a group by query. This gives me an error and the only way i have found to achieve this is to insert the results of a group by into another temp table, and then use that to perfom the update.
Is there something i'm missing, or is this the only way to achieve my goal ?
Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOfAs i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)Thanks in advance!
Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region) ProductID is the primary key.
I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.
I tried the follwoing, but get incorrect results.
select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price
However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.
select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price
I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?
I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.
Current Query:
select t.Name 'Task', d.FirstName + d.LastName 'User' from [dbo].[Tasks_TemplateAssignTo] a join Task_Template t on a.template_id = t.ID join Doctor d on d.id = a.provider_id
Results from query above:
TaskUser Call CustomerJohn Smith Call CustomerBetty White Call CustomerTammy Johnson Order suppliesGreg Bullard Order suppliesJosephine Gonzalez
Expected Results:
TaskUser Call CustomerJohn Smith, Betty White, Tammy Johnson Order SuppliesGreg Bullard, Jospehine Gonzalez
I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
I really hope that someone can help me or at least point me in the right direction. I am selecting a set of data and using the date values across the X axis. However the needs exists to group these by week, but these weeks are not the normal weeks, for - they exist as follows the month starts on the first Monday of a month, for example December 2007 starts on Monday the 3rd and the week ends on the 6th of December a so on till the fact that the last week of the month December 2007 starts on Monday the 31st and ends on January the 6th is there any way that I can create a group that could group the datetime values together in this way,
This is not best achieved in SSRS where should I be creating these groups. Any help would really be appreciated.
Below is the code. It gives 2 errors. 1 @StartDate must be defined (it is a report parameter) 2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field) Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5)) DECLARE @sowners nvarchar(5) DECLARE @mydate datetime DECLARE @sstatus nvarchar(10) DECLARE @sjtype nvarchar(5) DECLARE @chardate nvarchar(7) DECLARE @Jmonth Int DECLARE @Jyear Int DECLARE @Jcharyear nvarchar(4) DECLARE @Jcharmonth Nvarchar(2) DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype FROM Requirements WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate)) OPEN JobCur FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype WHILE @@FETCH_STATUS = 0 BEGIN SET @Jyear = (Year(@mydate)) SET @Jmonth = (Month(@mydate)) SET @Jcharyear = CAST(@Jyear,4) SET @Jcharmonth = CAST(@Jmonth,2) IF Len(Jcharmonth) = 1 BEGIN SET Jcharmonth = '0' + Jcharmonth END IF @Jmonth >= 10 BEGIN SET @chardate = @Jyear + '/' + @Jmonth END IF @Jmonth < 10 BEGIN SET @chardate = @Jyear + '/0' + @Jmonth END INSERT INTO @t (Owner, cdate, status, jtype) VALUES (@sowners,@chardate,@sstatus,@sjtype) FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype END CLOSE jobcur DEALLOCATE jobcur BEGIN SELECT * FROM @t END
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent). At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results. Hints? Mahalos in advance ... :) KevInKauai
Please give me the correct function name, otherwise please ignore in sending reply. I had used all the functions like identity, ident and so on. I need while selecting a querry, i need to generate serial numbers. I dont have identity column in my table. But, i need to generate serial numbers..
In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..
Select Number (*) from x
The output will be
Number(*) 1 2 3 4 5 6 and so on..
I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..
I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)