I have a database with 12 tables with pension details of employees for Jan-Dec 2008. I want to get the total pension for 12 months for each employee adding up amounts from all the 12 tables (jan through dec). How should I write the query? ...
Lets say you have the following entities in your database: You want to be able to add a note to all three of the other entity tables (User, Client and Project). How would you set this up? Add a tableName and a FKId to the Note table? Create a separate table for each associated Note group (ie., ClientNote, UserNote) Create a separat...
Is there a way to rewrite this query using the Django QuerySet object: SELECT b.created_on, SUM(a.vote) FROM votes a JOIN votes b ON a.created_on <= b.created_on WHERE a.object_id = 1 GROUP BY 1 Where votes is a table, object_id is an int that occurs multiple times (foreign key - although that doesn't matter here), and created_on whic...
I'm working on someone else's project that makes heavy use of tableAdapters. The site works but It regular adds an entry in the Event log : "ExecuteReader requires an open and available Connection. The connection's current state is connecting. " The site doesn't throw an exception though unless there is high traffic (5+ request...
My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03...
I didn't see any questions that asked this previously so here goes: Once you made (or had) the decision to start using LINQ to SQL how long did it take you to learn it's ins and outs? We're looking at using it at my organization but my superiors are concerned with the initial time cost (learning it). Hours would be the most useful of a...
Im trying to get the total amount of points a user has, as well as current month's points. When a user gets a point, it gets logged into the points table with a timestamp. Totals ignore the timestamp, while the current month's points looks for the points with the correct timestamp (from the first day of the month). SELECT user_id, user_...
If I have a table containing schedule information that implies particular dates, is there a SQL statement that can be written to convert that information into actual rows, using some sort of CROSS JOIN, perhaps? Consider a payment schedule table with these columns: StartDate - the date the schedule begins (1st payment is due on this d...
I can already use Excel (2007) to import data from SQL Server 2005. I add a data connection and I enter a custom SQL query that retrieves my desired data. Cool. But what I'd like to add is the ability to parameterize that query based on a value found in a known cell on the spreadsheet. My query would go from SELECT * FROM dbo.MyDataTa...
In every ASP.NET application I have written, I would make number of request to the database before outputting the information onto the web page. For example: var DataTable1 = GetDataTable("Select * From Customers"); var DataTable2 = GetDataTable("Select * From Products"); var DataTable3 = GetDataTable("Select * From Orders"); As far...
I'll hit refresh on my page 20 times and it will work, then a few minutes later it stops working for 5min. The tableadapter says that it has a count of 1 but when i access a property eg : tableadapter1[0].property1 it throws an exception: [StrongTypingException: The value for column 'blogpull_keywords' in table 'blogpull' is DBNull.] b...
Hello, I have a trigger in a table in SQL Server 2000, this is the trigger: ALTER Trigger [dbo].[Set_Asignado_State] ON [dbo].[Tables] FOR INSERT AS BEGIN DECLARE @area varchar(1) SELECT @area = Table_Area_ID FROM inserted IF (@area = 'L') BEGIN INSERT INTO Table_History SELECT (SELECT TOP 1 Table_Are...
I want to execute UPDATE command over a group of records, but it fails, because some records after the update would violate an table constraint. Is it possible to update only suitable records somehow? ...
You system allows new users to register and enter their wedding date. Being a purist, I wanted to store this as a date. After user interviews, it was clear that the reality differs from this approach. Users are often not sure of the day or the month, and wish to store partial dates, so "June 2009" or "sometime in 2012". Does anyone know...
We have 2 databases, say DB1 and DB2. DB1 contains all the stored procedures which access also data in DB2. DB1 uses synonyms to access the tables in DB2. (Using synonyms is a requirement in our situation) This works perfectly fine in all situations with SQL Server 2005 Developer Edition. However in the Express Edition, we get an excep...
I'd be grateful for any advice that anyone has regarding: How you you effectively store a gps (or any floating point number) in a varchar field that can be indexed. Background: We develop a content management system that can effectively store files of any type together with a collection of metadata. This file/metadata is stored as ...
I have two tables with one identical column name, but different data. I want to join the tables, but access both columns (row["price"], row["other_price"]): How can I rename/alias one of them in the select statement? (I do not want to rename them in the DB) ...
The .NET TimeZoneInfo class is great and I thought it would answer all my issues with recording data from multiple time zones in my SQL 2005 database. To convert a UTC datetime in the database to any other time zone i'd just get the time zone into a TimeZoneInfo class using TimeZoneInfo.FindSystemTimeZoneById() and then call the TimeZon...
I am trying to execute my php code, which calls two mysql queries via mysqli, and get the titular error. Here is the code I am using <?php $con = mysqli_connect("localhost", "user", "password", "db"); if (!$con) { echo "Can't connect to MySQL Server. Errorcode: %s\n". mysqli_connect_error(); exit; } $con->query("SET NAMES 'utf8...