I am using a poker application that imports all played hands into a PostgreSQL database. I'm trying to write some queries for that database so I can make good use of the data. I figure PostgreSQL and SQL server probably have similar language. And I know these forums are great. So I'm posting here for help.
This is a working code snippet that retreives three properties of a player:
Code Snippet
SELECT sum(totalhands) AS HANDS, sum(vpiphands) AS VPIPHANDS, sum(pfrhands) AS PFRHANDS
FROM compiledplayerresults WHERE compiledplayerresults_id in (
SELECT compiledplayerresults_id FROM compiledresults WHERE play
SELECT player_id FROM players WHERE playername='PLAYERNAME'));
This is all I can do with my skills right now. However I very much want to learn to query for more things:
- I'd like to be able to make a query for several players at the same time
- I'd like to be able to get the aggregate properties for all players who are not PLAYERNAME1 or PLAYERNAME2
- I'd like to be able to get the aggregate vpiphands property from all players whose totalhandsis > 100 and < 1000.
I hope you can help me with that. If you can, that will make me very happy and grateful! Thanks!
I am using this stored procedure in sql. I have 6 tables. One is called employees. This is what I need to be able to do. A user enters a new employee into a winform, picks a role, division, manager, technicalskill set and applications from the drop down lists and hits save. The employee table should be the only one updated and has these columns only.( firstname, lastname, dvisionid, managerid, roleid,techskillsid, and appID). At the moment what is happening is its saving the firstname, lastname correctly, but the rest of the ID columns are null. It is updating the other tables with the string entered but what I need is the emplyee table to update with the corresponding ids. Is this alot more complicated then i thought? If I try to replace the role with roleid etc, it will just tell me I can't convert string to int which is understandable. How do I do this?
CREATE PROCEDURE sp_InsertEmployee @Firstname nvarchar(50), @Lastname nvarchar(50), @Role nvarchar(50), @Manager nvarchar(50), @Division nvarchar(50) AS BEGIN SET NOCOUNT ON;
INSERT INTO EMPLOYEES (FIRSTNAME, LASTNAME) VALUES (@FIRSTNAME, @LASTNAME) INSERT INTO [ROLE] ([ROLE]) VALUES (@ROLE) INSERT INTO MANAGER (MANAGER) VALUES (@MANAGER) INSERT INTO DIVISION(DIVISION) VALUES (@DIVISION) END GO
My C# code is like this:
SqlCommand sqlC = new SqlCommand("sp_InsertEmployee", myConnection);
Hi, I need help with updating a record. When I run the following code underneath the update button, the record does not update and I get no error message. Anyone have any ideas?
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString Dim Description As String = DescriptionTB.Text Dim PlaceName As String = PlaceNameTB.Text Dim Location As String = LocationTB.SelectedValue Dim PropertyType As String = LocationTB.SelectedValue Dim Price As Integer = PriceTB.Text Dim con As New SqlConnection(ListingConnection)
Dim sql As String = "Update Listings SET Description = ?, PlaceName = ?, Location = ?, PropertyType = ?, Price = ? WHERE ListingID = ?" Dim cmd As New SqlCommand(Sql, con)
Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus. Suppose I have five records
I know the method where you select each and every table and manually create foreign key relationships to other tables in the database visually or by sql. Is there any way in SQL server 2005, to create these relationships using wizard, where it checks for the same column names in different tables and creates the relationships. If yes, please let me know how to do it in SQL server 2005.
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
Can someone please help me....I have created a DNN module that works on the test site but when I upload the module zip to a new site I get an error on creating my stored proceedure as follows:
Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'. Line 51: Incorrect syntax near '@Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC
EndJob End Sql execution: 01.00.00.SqlDataProvider file
The SP looks like this:
/* ------------------------------------------------------------------------------------- / ListTAS_Journal / ------------------------------------------------------------------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = ''
SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
This SP works on the test site. Any help would be creatly apreciated
Hi I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system, then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that was read, else send the data down the unmacthed output which will be the same as the input.
So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows load in memory.
Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer, as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.
Hi all,Sorry for HTML, there is a lot of code & comments I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :( Please help me figure out what stops it mid way? I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*): Here is my stored procedure:CREATE PROCEDURE csp_AuthorAccountInfo@CandidateID int,AS DECLARE @ScriptsNo int, @ManuscriptID int SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @CandidateID/* this is where it stops all the time :(Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/SELECT c.*, l.LocationID, @ManuscriptID=m.ManuscriptID, l.State, cn.Country FROM Candidates c INNER JOINManuscripts m ONc.CandidateID = m.CandidateID INNER JOINLocations l ON c.LocationID = l.LocationID INNER JOINcn ON l.CountryCode = cn.CountryCodeWHERE c.CandidateID = @CandidateID/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.CommentsFROM Manuscripts m INNER JOINManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOINManuscriptStageNames msn ON ms.StageNameID = msn.StageNameIDWHERE m.ManuscriptID = @ManuscriptIDORDER BY ms.DatePosted DESCGO
Details : Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message. "The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM. If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab. Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
I need to create a stored procedure that will have about 10-15 queries and take 3 parameters. the variables will be: @lastmonth, @curryear and @id @lastmonth should inherit Session variable intlastmonth @curryear should inherit Session variable intCurrYear @id should inherit Session id One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "' AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id") The rest of the queries will be similar and use all 3 variables as well. How can I go about this and how will queries be seperated.
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id, dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note into #temp_group_sessions FROM dbo.group_session_client, dbo.group_session WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services LEFT OUTER JOIN #temp_group_sessions on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id ;
-- Drop temporary tables
DROP TABLE #temp_group_sessions; DROP TABLE #temp_services;
I would like to create a database for keeping track of payroll data for employees where the supervisors (job coaches) on our workshop floor can use a Pocket PC device to record the hourly employee data on the fly. Then at the end of the day, the supervisor can place the device in a cradle of some sort and synch the newly entered data into the main database.
I'm guessing that SQL Server Compact edition would be perfect for this type of task? Is that correct? Can someone give me recommendations on how to go about setting this up? What should I use as the main database? SQL Server? Access? Any advice is appreciated!
Hi, I need to use a top and a join in the same sql. To get 10 top refnr from orders_refnr. That works fine to I use this: SQL = "SELECT TOP 10 refnr, antal = COUNT(refnr) FROM orders_refnr INNER JOIN produkter ON (orders_refnr.refnr = produkter.referensnummer) GROUP BY refnr ORDER BY antal DESC" But I need to be able to get information from more fields than the field refnr. How can I specify more fields? I need to get other fields from produkter. Please helt I´m really stucked.
I have gotten some criticism from coworkers regarding this test and just wanted to see what you guys think. I realize the wording could use improvement and any criticism towards making it easier to understand is much appreciated.FWIW - I had to solve this problem on the job so I feel it is a real-world test that helps me understand how people think and if they try to find alternate solutions.Thanks!~~~~~~~~~~~~~~~~~~~~Given a table that has over 100,000 records…SUBSIDIARY=========PARENT_IDINTCHILD_IDINTULTIMATE_PARENT_IDINTCLEANUP_INDBIT…where each PARENT_ID can have multiple CHILD_ID values, but the PARENT_ID should not equal the CHILD_ID. After an initial data load, the ULTIMATE_PARENT_ID and CLEANUP_IND columns contain NULL values (see page 2 for sample data).ULTIMATE_PARENT_ID is defined as the topmost parent in the chain for the particular CHILD_ID record, so if the chain was only 2-level’s deep the ULTIMATE_PARENT_ID is the CHILD_ID’s PARENT_ID’s PARENT_ID.Please write an answer for all three questions below:A)Which of the following queries should you run first?B)Write an optimized query to identify the ULTIMATE_PARENT_ID for each CHILD_ID and set its value into the ULTIMATE_PARENT_ID column.C)Write a query to identify ALL of the circular references and mark each record that is a circular reference by updating the CLEANUP_IND column to 1.~~~~~~~~~ Page 2 ~~~~~~~~~ Sample Data, remember though this table has over 100,000 records and the parent-child chain can go n-levels deep – where n is not known.PARENT_IDCHILD_IDULTIMATE_PARENT_IDCLEANUP_IND1024512NULLNULL362300NULLNULL887541NULLNULL10221024NULLNULL546887NULLNULL5122305NULLNULL112967NULLNULL697123NULLNULL901452NULLNULL2300666NULLNULL334445NULLNULL512903NULLNULL884554NULLNULL313313NULLNULL554884NULLNULL112119NULLNULL967555NULLNULL2305333NULLNULL33336NULLNULL541546NULLNULL10301020NULLNULL112999NULLNULL
hi, I have NT server which has drive c: 500 MB and drive d has 44 GB.
I know that the person who set up this server did not give enough space to the c drive, here is the problem. I am running sql server 7.0 which has 30 GB of data in the d drive. I need to reconfigure the NT hard drive so I can allocate 2 GB for C drive and 42 GB for D drive.
What is the best, safe method to accomplish this task.
After experiencing a hard drive failure i have reinstaled MSSQL7 on one drive and have a database which I need to recover on separate physical drive. How can I go about doing this?
Hi, I have ran 1. xp_fixeddrives and got the result drive MB free ----- ----------- C 1708 D 16311 2. I ran Backup Wizard in EM and able to see only above drives
3. But if ran backup in EM able to see more than 10 Drives(like C,D,H,I,J,M,N and etc). Why I can able to see those difference?. How do I find out exactly how many drives are there in this server without directly going to that server?. I appreciated your valuable answere. Thanks, Ravi
Hi, I'm looking for a way to check the free space left on the hard drives and then if needed send an alert to notify when we need to free up some space. I played around with the performance monitor and realized I could do it that way but I think you would have to leave the performance monitor running all the time and I'm not sure if I want to do that. I also read about the xp_fixeddrives proc that displays how much free space is available but then I don't know what to do from there? Does anyone have any recommendations for the best way to do this.
Right, I have this database that I need to sort, I'll give you an example:
ID Name Value 2312 Sega 200 5678 Blizzard 215 3412 Bullfrog 210 6798 Nintendo 195
Now, what I need to do is to sort it, perform calculations on and I need the list to be sorted with a predefined post as the top result, say like this one time:
ID Name Value 3412 Bullfrog 200 2312 Sega 210 5678 Alizzard 215 6798 Nintendo 195
as you can see sorting it alphabetically would lead to
5678 Alizzard 215 3412 Bullfrog 210 6798 Nintendo 195 2312 Sega 200
(or the other way around if you play with asc/desc) by id would be
2312 Sega 200 3412 Bullfrog 210 5678 Alizzard 215 6798 Nintendo 195
There aren't any top or bottom values sort of speak for the posts I want to be on top, so...how to sort this like this?
3412 Bullfrog 200 2312 Sega 210 5678 Alizzard 215 6798 Nintendo 195
the order after the top one is irrelevant.
Now...I know I could sort this by doing something like
"Select * FROM blablabla WHERE Name = 'Bullfrog'"
and then doing "Select * FROM blablabla" and then just bypassing that post in asp/php code or whatever, but that would be a pain for me to do as I have to perform some massive calculations and the code would be alot larger then needed be
Its a brain teaser allright...can you help me out?
I was wondering if anyone played around with changing the allocation unit size when formatting the hard drive the SQL server is running on. I would think that setting it higher to account for the larger size of the database files would help, but I'm not sure.
I have 2 harddisk in my computer and I have SQL 2005 Express on 1 of them (let's say C:), however, my C: is going to be full soon! Once it is full, is it possible to create a table on my other harddisk which the server can recognise?
Hello, I am experimenting with indexes and hope people can shed lighton some of my problems.I am using SLQ 2000 on Win 2000 Server. Using the following query fordiscussion;--------------------------------SELECT TOP 1000000E.EUN_Numeric, -- Primary KeyE.EUN_CODE, -- VarCharE.[timestamp] --,--E.Model -- Computed column (substring of EUN_CODE)FROM dbo.Z1_EUNCHK E--WHERE E.[timestamp]DATEADD ( wk , -48, getdate() ) AND-- E.[timestamp]< DATEADD ( wk , -4, getdate() )ORDER BY E.[timestamp] DESC-----------------------------------Problem 1) If I set up a single Index on the TimeStamp (plus the PK onEUN_Numeric) then there is not improvement in performance.It is only when I set up an Index on the Timestamp,EUN_Numeric,EUN_Codethen I get a good improvement. This is also thecase with the "where" clause added. I am using query analyser. Theimprovement is 14 secs to 3 secs (mainly with the removal of the sortprocess)Why?My expectation is that if my query uses [timestamp] column then surelyan index only on this is adequate.Problem 2) Introducing the simple computed column into the query takesthe time to 15 secs (with Sort processes involved).Why does revert back to sorting process when previous the index wasused ?Regards JC......
I have a 75 GB hard drive and a 300 GB. I want to mirror the 75 to the 300 and use the extra space as data storage. Is this possible if I partition the 300 and then mirror the hard drives.
How can I do this with vbscript, or C# ? - Copy backup files down from a network share, into the data directory of my local sql 2005 instance - perform a restore using the files copied from above - Execute a dts package
More info: Our databases are scripted and exist on the typical development, and testing enviroments. So as I get ready to start a new application, I want my local sql instance to be updated based on structure changes as well as data. So I have to apply the changes from the scripted sources and pull over the data. I would naturally like to automate this.
It was shipped with a 76 gig drive setup in RAID 1 (2 disk) and a 400 gig drive setup in RAID 5 (4 disk).
I would like to determine what is the best way to setup up the partitions. What size and what should be placed on each.
Like the C: Drive...Should I just put Windows on there and nothing else? Do I stand to gain something from not using part of that 76 gigs as a D: drive for my apps?
Is it possible to force parameters into the reports so enabling me to force a user id value into every report that is picked up from the list. The user ID is a system value and I don't want end users having any knowledge of it?
Hi!I'm desperating here!!! Two questions:1º Is line 13 realy selecting all the records with the username samurai (in this case)?!2º How do I fill the Boolean SeExiste var with a value from the record?1 Dim UserIDParameters As New Parameter 2 3 UserIDParameters.Name = "ProdUserID" 4 5 UserIDParameters.DefaultValue = "samurai" 6 7 Dim LoginSource As New SqlDataSource() 8 9 LoginSource.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString1").ToString() 10 11 LoginSource.SelectCommandType = SqlDataSourceCommandType.Text 12 13 LoginSource.SelectCommand = "SELECT FROM aspnet_Users (FirstTime) VALUES (@UserIDParameters) " 14 15 Dim SeExiste As Boolean 16 17 SeExiste = LoginSource.SelectParameters("FirstTime").DefaultValue I'm a newbye and despite this simple thing that in normal ASP is very easy to do!!! Please help me!Thanks in advance!
