Hi,
I'm making a rating system that save the results in the database.
I have 1 table with 3 columns:
1) name
2) points
3) numberofvotes
now I want to select the 5 names with the best results. In other words the 5 best results when you do (points/numberofvotes)
how can I say this in SQL? I hope someone can help me!
thnx!
Greetings everyone, I'm posting this thread with the hope that someone will notice it and might offer me a helping hand regarding one of my problems.I have the database named "DBEXAMPLE" with the table MEMB_INFO that contains two important columns that are named cur_points and points where cur_points column contains the total available points that a member can use/spend on a game and the points column stores the total of the points that a member used so far.So as i plan to wipe all data of the database, i need to keep the member login,password and total points that each member purchased.So i will somehow need to update the cur_points column with the total of current cur_points+points columns and then wipe points column.I've personally asked a friend regarding this and he said that this should be something complicated and it might require php also.Really appreciate if someone could help me regarding this.small schema:Database: DBEXAMPLETable: MEMB_INFOColumn cur_points -> total available points that a member can spendColumn points -> total points that the member has already spentColumn memb___id -> member login aka account idTODO -> update cur_points column with the total of cur_points+points columns for each member(buyer)With best regards.
View 5 Replies View RelatedI know how to create mount point in windows 20003 cluster, I am not sure how
to set it up with SQL 2005 running on the cluster.
Does sql need to be dependant on any of the disks? I have tried looking for
a guide, but cannot find.
current setup active active cluster running. I need to add san space which
will hold the databases. The san will be carved up into drive letters. each
drive letter will hold 3 mount points.
ie.
node 1
J:-2 mount point
k:2 mount point
l:2 mount point
node 2-
r:-2 mount point
s:2 mount point
t:2 mount point
each node would be able to own the disk if the other node failed over.
any help is appreciate. I have tried books online etc.. cannot find a good
step by step.;
thanks
HI All,
I use MSSQL as my database and ASP.NET as my front application. I want to display Price value S$23.68.
The dayatype I used is smallmoney, but it display: S$23.6800. HOw do I control the number of decimals point in the column of MSSQL??
Thanks a lot
Suigion
Dear experts,
Hi, I got a table name Employee, with the following fields
EmployeeID
Name,
ManagerID
ManagerID2,
ManagerID3,
ManagerID4 ..
ManagerID, ManagerID2, ManagerID3, ManagerID4 are all pointing to EmployeeID in the same table.
Now I want to select the all employee records where the manager NAME is like '%Raymond%'. (i.e. any of the four managers name is like '%Raymond%'.
How do I construct the select query? I tried this, but it does not work if there are more than 1 manager named 'Raymond'
DECLARE @Name varchar(50)
SET @Name = '%Raymond%'
DECLARE @EmployeeNumber nvarchar(50)
Select
@EmployeeNumber = EmployeeID
from
Employee
where
[Name] like @Name
SELECT
'HumanResources.Employee' as TableName,
E.[Name] as EmployeeName ,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID),'') as Manager1,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID2),'') as Manager2,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID3),'') as AppraisingManager,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID4),'') as ModeratingManager,
E.CurrentFlag as Active
FROM
HumanResources.Employee E
WHERE
(E.[ManagerID] = @EmployeeNumber)
or (E.[ManagerID2] = @EmployeeNumber)
or (E.[AppraisingManagerID] = @EmployeeNumber)
or (E.[ModeratingManagerID] = @EmployeeNumber))
Any help is very much appreciated.
I understand mount points help scalability in easier maintenance.
By scalability do we mean more than 26 drive letters or it means adding more space to the same mount point letter on with more ease .
Can I add more space to a mountpoint if required later on by adding hard disks .
Also if one can give some pointers to good file group configuration guidelines / storage align partitions , it will be very much helpful
Further I my server CPU has 4 cores , will having 4 filegroups help me in improving system performance.
If SAN has 2 controllers , is it preferred to run data file partition on one controller and log file partition on another.
Thanks in advance.
I have a column in a table with a data type of float.
When I update it to store 7.50, it changes it to 7.5
Is there any way to force it to store 2 decimal points?
Hello,
We have a requirement to be able to monitor mount points. The xp_fixeddrives does not support mount point monitoring. Is there another way to do it. Does microsoft working on updating the xp_fixeddrives. Let me know if anyone has any ideas how to monitor mount points.
Thanks
I have a user defined function, I want to determine the distance between the 2 points. I have it working but i'm having a problem getting to print.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Code Snippetcreate function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
returns float
as
begin
declare @DegToRad as float
declare @Ans as float
declare @Miles as float
set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0
if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0
begin
return ( @Miles )
end
set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)
set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)
set @Miles = CEILING(@Miles)
return ( @Miles )
end
DECLARE @RC float
EXEC Distance '39.943762', '-78.122265', '32.334709', '-96.633546'
PRINT @RC /* in miles */
Previously I have posted about my problem with transaction logs. Threw my research I have found the truncate log at check point. I have also been lead to think that a check point could be a full back up. I have set this option to true and then performed a full back up. Afterward I have shrank the database to find the transaction log to still be over 43 gigs. Where have I gone astray?
Regards
SeTi_Ni
Hi all,
I'm trying to debug storedprocedures in query analyzer from workstation, login with server adminuser.Debugger is not stoping at break points.
any help regarding this will be greatly appreciated.
thanks.
I was messing around with backup strategies today at work while contemplating my other db issues.
In doing so, I've made a list of backups that don't truly exist anymore (I deleted the 'backup device' (bleh.bak).
Here's an image, stolen from technet's articles.
http://www.microsoft.com/library/media/1033/technet/images/prodtechnol/sql/2000/maintain/ppc1106_big.gif
See that list of restore points? Mine is full of restore points that have absolutely no meaning in them for a DB I made to test out (testDB).
Is there a way to clean those out?
DECLARE @EffLevels TABLE (ChangePoint int, Value Int)
INSERT@EffLevels
SELECT'1000', '767' UNION ALL--Changed
SELECT'1000', '675' UNION ALL
SELECT'1001', '600' UNION ALL--Changed
SELECT'1001', '545' UNION ALL
SELECT'1001', '765' UNION ALL
SELECT'1000', '673' UNION ALL--Changed
SELECT'1002', '343' UNION ALL--Changed
SELECT'1002', '413' UNION ALL
SELECT'1002', '334' UNION ALL
SELECT'1001', '823'--Changed
-- My Result should be
-- ChangePointPrevChangePointValue
-- 1000Null767
-- 1001 1000 675
-- 1000 1001 765
-- 1002 1000 343
-- 1001 1002 823
Any suggestion ?
Hi,
The default number of decimal points for 'money' data type is 4. Can I change it as 6?
Eg 120.123456
Thanks!
Hi allI have a large data set of points situated in 3d space. I have a simpleprimary key and an x, y and z value.What I would like is an efficient method for finding the group ofpoints within a threshold.So far I have tested the following however it is very slow.---------------select *from locations a full outer join locations bon a.ID < b.ID and a.X-b.X<2 and a.Y-b.Y<2 and a.Z-b.Z<2where a.ID is not null and b.ID is not null---------------If anyone knows of a more efficient method to arrive at this results itwould be most appreciated.Thanks in advanceBevan
View 8 Replies View RelatedThis sounds so simple but yet don't know why it doesn't work.
i've got two decimal numbers in columns
closingUnits = 25093.53640
closingAmt = 59110.33
i use a derived column transformation to generate a string column which is the division of those two above.
mystrfield = closingAmt /closingUnits = 2.355599
what i want is cast it to just 4 decimal points when i run the expression below it cast it to 4 decimal points but it doesn't do any rounding.
the value i get is 2.3555 when i should get 2.3556
(ISNULL(closingAmt ) || closingUnits == 0) ? "0.0000" : ((closingAmt / closingUnits) < 1 && (closingAmt / closingUnits) > -1 ? "0" : "") + (DT_WSTR,15)(DT_DECIMAL,4)(closingAmt / closingUnits)
Hi all,
I am seeking your expertise to create SQL codes (SQL server 2005) that can help me to answer the problem below.
I have two tables (points and station), presented in form of SQL codes below. I€™d like to find the 6 closest panels for each of the station. As can be seen in the result table below, the 6 closest panel names are arranged from the first closest (P1) to the sixth closest (P6). Similar procedure also applies for the distance column arrangement. This distance column (D1 €“ D6) is the distance of panels P1 €“ P6 to the station. The distance between two points (with x-y coordinates) can be calculated using a simple Cartesian formula:
Distance = ( (X1 €“ X2)2 + (Y1 - Y2)2 ) 0.5 . As the sample, distance between station €˜A€™ and panel €˜P19-04W€™ is = ((737606.383 - 737599.964)2 + (9548850.844 - 9548856.856)2) 0.5 = 8.79.
The expected result of the work is presented in the table below:
Panel
distance
Station
P1
P2
P3
P4
P5
P6
D1
D2
D3
D4
D5
D6
A
P19-04W
P19-06E
P19-05E
P19-05W
P19-03W
P19-07E
8.79
12.00
13.54
19.02
21.00
27.43
B
P19-07W
P19-09E
P19-08E
P19-06W
P19-08W
P19-07E
9.50
11.58
12.92
21.15
24.85
29.11
C
P19-11E
P19-10W
P19-12E
P19-09W
P19-10E
P19-11W
8.45
11.42
16.06
16.38
23.15
25.30
Table 1:
create table 1 (
Panels varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into 1 values('P19-03E','737640.722','9548882.875')
insert into 1 values('P19-04E','737630.166','9548868.3')
insert into 1 values('P19-05E','737619.611','9548853.726')
insert into 1 values('P19-06E','737609.054','9548839.15')
insert into 1 values('P19-07E','737598.495','9548824.571')
insert into 1 values('P19-08E','737587.941','9548809.998')
insert into 1 values('P19-09E','737577.386','9548795.425')
insert into 1 values('P19-10E','737563.359','9548776.163')
insert into 1 values('P19-11E','737552.795','9548761.578')
insert into 1 values('P19-12E','737542.256','9548746.919')
insert into 1 values('P19-13E','737531.701','9548732.345')
insert into 1 values('P19-14E','737521.146','9548717.772')
insert into 1 values('P19-03W','737610.519','9548871.43')
insert into 1 values('P19-04W','737599.964','9548856.856')
insert into 1 values('P19-05W','737589.404','9548842.275')
insert into 1 values('P19-06W','737578.849','9548827.702')
insert into 1 values('P19-07W','737568.294','9548813.128')
insert into 1 values('P19-08W','737554.274','9548793.77')
insert into 1 values('P19-09W','737543.718','9548779.195')
insert into 1 values('P19-10W','737533.157','9548764.614')
insert into 1 values('P19-11W','737522.603','9548750.041')
set nocount off
go
Table 2:
create table 2 (
Station varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into 2 values('A','737606.383','9548850.844')
insert into 2 values('B','737575.41','9548806.838')
insert into 2 values('C','737544.437','9548762.832')
set nocount off
go
Thanks alot in advance!
HI:
Is there any algorithim, which can help me write a script or SQL that will select all the points within a Closed Polygon.
This polygon and point data have latitude and longitude information
Help will be appreciated.
Thanks
Namita
Can anyone point me to a book or give me a cookbook recipe for creating a proxy table in Sybase 12.5 that points to a MS SQL Server 2005 table?
I have searched the forum, Sybooks, and BOL and it appears that it can be done.
I have a table which hold records that contain all the lat/long points. I want to select all records that are within a polygon. Server 2008...
View 6 Replies View RelatedThe total quantity(sum(woitem.qtytarget) as total) result set is showing more than 6 numbers after the decimal point (example:442.2565485). How can I limit it to 2. (example:442.25)
select wo.num, sysuser.username,sum(woitem.qtytarget) as total
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
ORDER BY CAST( REPLACE( wo.num, ':', '.') as decimal(12, 3))
The parent table has the column OrderID which is the primary key. There is a unique non-clustered index in the same table with the same column and some included ones. A child table references the parent table on the OrderID column. Unfortunatelly, in the sys.foreign_keys table the index that has been used to acomplish the referential integrity is the unique non-clustered one instead of the primary key (which is the clustered index as well).
This is causing issues in some maintenance tasks where we need to disable all the indexes of the table apart from the clustered index. Disabling these indexes leeds to disabling the not properly configured foreign key as well. Maybe dropping the index create the constraint again and then recreate the index is a workarround but I wish to have something more elegant and future proof.
How do I limit the decimal points in a computed column. SSE 2005 seems to override the precision and scale I set in either GUI or by creating the table by query.
Thanks in advance for helping.
Scott
Hello,I am using SQL Server 2000 with SP4. I have a database with two fullbackups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30PM. Is there a possible way to do a point in time restore to 4:30 PM,that is between two full backups?When I try to use the transactional log backup that is taken at 5:30, Ican never specify a time before 5:00 PM. Is the transaction logtruncated at each full backup? If so, even if you take transactionallog backup every ten minutes, and full backups every once in a while,there will be some point in time which cannot be recovered to, namelythe time between a transactional log backup and a full backup. Take alog backup at 4:50, and full backup at 5:00 and you can never recoverto 4:55, can you?Any insight on the topic will be appreciated,Regards,M. Baris Caglar
View 3 Replies View RelatedI have Team suite VS2005 sp1 installed and I can't debug my SSIS packages. If I put any break points in the Script code when I try to debug I get the message.
"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."
I click on close and it runs the package but doesn't stop at any break point. If I take out all break points it will run without this error. A couple of times I have managed to put in a single break point early in the package within a different script component which causes it to break... but at a completely different point within a completely different script component. It's so weird. Any help would be appreciated.
Hoots.
I have discovered an instance where the Y-AXIS displays a scale incorrectly. The scale values that are displayed do not match the data points shown on the chart itself. I am not using any Minimum or Maximum values in the properties y-axis tab. This does not occur on all charts and seems to be random.
Has anyone encountered this before? Is this a bug in SSRS 2005? Does anyone know of a fix? All sugestions are welcome, thankx!
Hello,
To implement the new SQL 2005, I plan to make the environment easy to manage. The environment should be simple to document and be automated via scripts. Therefore I plan to use mount points as described below.
On a typical SQL server with multiple drives like C, D, E, F, G, H. Where each drive will have various folders to hold SQL code, data files, transaction log files, tempdb files, snapshot files, and other types of files. This typical environment is not pretty and is hard to write scripts for.
So I plan to standardize on one standard directory structure via volume mount point. On all new SQL 2005 servers, we should see drive E as the one and only SQL Server directory. Other drives will be mounted to drive E as shown.
E:
SQLSERVER local folder -sql code for each db instance
SQLSHARED local folder -sql shared tools for all db instances
SQLTLOG1 Drive H -db transaction log
SQLSNAP1 Drive F -db snapshot files
SQLTEMPDB1 Drive H -tempdb main data file
SQLWORK Drive D - DBA work area
SQLDATA1 Drive G -db data files
SQLDATA2 Future Drive -if SQLDATA1 is too large for any direct attached drive, or to get more I/O throughput.
With this implementation, I can easily write scripts to manage the environment. Also if any mounted volume is out of space, we can swap the based drive without doing any change to database configuration. We can also switch from direct attached drive to SAN in the future.
Do you think mount point is safe to use with SQL 2005? I know it is supported.
Do you have a standard directory structure for your environment? How do you do it?
Thanks,
KTMD
On our project we export SSRS reports to Excel. Report Definition Language (RDL) enables you to use several measurement units (for example, inches, pixels, centimeters, and points) to specify position and size values. However, Excel uses only points. Therefore, the SSRS Excel rendering extension converts the height and width of the table, the heights of the rows, and the widths of the columns to points.
This process may include rounding some values. In this situation, the table height or width and the sum of the row heights or column widths are different. To compensate for the difference, the SSRS Excel rendering extension inserts a small row or column to the workbook.
To prevent that ‘hidden’ row or column, we configure the table height and width to a value specified in "pt" (points) rather than "cm". 1 cm is about 28 points. Is it possible to configure SSRS to use points by default while designing a report?
I am trying to build a voting system. Website visitors will rate a pictures from 0-10.
This value should update the amount of points already stored is SQL.
I think the way to do it is passing the value of a field to a varible, performing the operation and updating the database, but I don't know how to capture a value in Sql and pass it to variable...
Does anybody there could post a piece of code or point to a link where I could find this information?
thanks
Does sql server 2012 mount points support FileStream feature?
View 1 Replies View RelatedThis is a general question about data modeling. I'm more curious than anything else.
There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?
-Young K
I'm trying to produce a chart that has both actual data values from a database and matching "line of best fit" plots on the one chart. I have 4 data series, 2 of the actual data values and 2 that represent the values for the "line of best fit". What I want to do is:
Plot the actual values just as data points (joining them with lines in meaningless) and
Plot the "line of best fit" values as a line.
When I edit the data series on the scatter chart, I can see the "Plot data as line" option but it is "greyed" out.
Have I missed something really simple here or is this not possible. I'm using RS2005
Many thanks
Simon
Hi ALL,
I am getting 2 errors for the report which has graph.
1.The chart €˜Chart_ID0EDABDA€™ contains a different number of DataPoints per ChartSeries element than the number of StaticCategory elements. If the chart contains no StaticCategory, it must contain one DataPoint per ChartSeries.
2.[rsWrongNumberOfChartDataPoints] The chart €˜Chart_ID0EDABDA€™ has a ChartData element that contains a collection of 6 DataPoint element(s), which is different from the number of expected datapoints (1). The expected number of datapoints in the definition is (Max(number of static categories, 1) * Max (number of static series, 1)).
I am converting crystal reports to SSRS.Can anybody give me some info about Data points requirments and how to get rid of these errors.I am getting these 2 error almost all the SSRS reports having Graphs.
Thanks .