INSERT INTO timecrunch.dbo.intervals (IntervalID, Duration) SELECT ixInterval, DATEDIFF(hour, dtStart, dtEnd) FROM fogbugz.dbo.TimeInterval WHERE dtEnd is not NULL IntervalID is an int, Duration is a float I am trying to put all the hourly durations into the Duration column... I assume that even if it is less than an hour it will use...
I have a table that has 4 sets of 25 columns in a BIT concept. Actually field is smallint but it is either 0 or 1 in it's data. Here is my code that is an attempt to get the total for the first group of 25 cols. Declare @rows int , @ID uniqueidentifier , @LocTotal bigint select @rows = ( select count(*) from #t1 ) while @rows > 0 ...
I've built an open source ASP.NET web application and now I'm moving towards release. I'm keen to offer an easy install for my users but I'm having real problems coming up with a solution. The app needs to have a site setup in IIS and a SQL Database installed. What is the easiest deployment process for my users you can think of? ...
I would like to take a simple query on a list of members that are indexed by a number and group them into 'buckets' of equal size. So the base query is: select my_members.member_index from my_members where my_members.active=1; Say I get 1000 member index numbers back, now I want to split them into 10 equally sized groups by a max and...
Hi, Is it possible to reorder rows in SQL database? For example; how can I swap the order of 2nd row and 3rd row's values? The order of the row is important to me since i need to display the value according to the order. ...
How would you store a time or time range in SQL? It won't be a datetime because it will just be let's say 4:30PM (not, January 3rd, 4:30pm). Those would be weekly, or daily meetings. The type of queries that I need are of course be for display, but also later will include complex queries such as avoiding conflicts in schedule. I'd rather...
I have an Orders table (simplified) OrderId, SalesPersonId, SaleAmount, CurrencyId, ... I am attempting to create a report on this table, I'm hoping for something like: SalesPersonId TotalCAD TotalUSD 1 12,345.00 6,789.00 2 7,890.00 1,234.00 I'd prefer not to do a self join (perhaps I'm optimiz...
[ edit ] For the record, here's the problem portion of the query, which is now working: SELECT m.groupNum, t.ea, ( t.ea - ( t.ea * m.margin )) AS estCost, (( t.ea - ( t.ea * m.margin )) * t.quantity ) AS salesSub, ((( t.ea - ( t.ea * m.margin )) * t.quantity ) / ( SELECT SUM(( t2.ea - ( t2.ea * m.margin )) * t2.quantity ...
I have a number of indexes on some tables, they are all similar and I want to know if the Clustered Index is on the correct column. Here are the stats from the two most active indexes: Nonclustered I3_Identity (bigint) rows: 193,781 pages: 3821 MB: 29.85 user seeks: 463,355 user_scans: 784 user_lookups: 0 updates: 256,516 Clustered Pr...
I have a stored procedure.. Proc: spMyStoredProcedure Takes a Param: @List varchar(255) The @List would be a comma separated list of values i.e... @List = '1,2,3' (clarity.. a single value of 1 would mean all records with col1 = true) I have a table with these columns: ID int, col1 bit, col2 bit, col3 bit, col4 bit. ID | col1 | ...
I'm using ASP.net and an SQL database. I have a blog like system where a number of comments are made against a post and I want to display the number of those comments next to the post. To get that number I could either hold it in the post record and add/subtrack when a comment is added or deleted or I could use the SQL to calculate the ...
I have been given a spec that requires the ISO 8601 date format, does any one no the conversion codes or a way of getting these 2 examples: ISO 8601 Extended Date 2000-01-14T13:42Z ISO 8601 Basic Date 20090123T105321Z ...
I am performing a standard update in nhibernate to a single property. However on commit of the transaction the sql update seems to set all fields I have mapped on the table even though they have not changed. Surely this can't be normal behaviour in Nhibernate? Am I doing something wrong? Thanks using (var session = sessionFactory.OpenSe...
I have a table of housing listings. I would like to keep a maximum of 10 listings per city. (Most cities have less than 10 listings). When I do this query: select city, count(city) as cityCount from tREaltyTrac group by city SQL returns: Acampo 1 Acton 1 Adelanto 20 Agua Dulce 1 Aguanga 1 Akron 19 Albany 12 Albion 3 Al...
I'm trying to figure out decimal data type of a column in the SQL server. I need to be able to store values like 15.5, 26.9, 24.7, 9.8, etc I assigned decimal(18, 0) to the column data type but this not allowing me to store these values. What is the right way to do this? Thank you ...
Hi, Is it possible to create a Database which has 1 column (but not the column of primary key) to be auto-increment? So that when I insert value to the database, i don't need to fill in the value myself, and DB will fill in that value for that column for me (and increment every time I do a new insert)? Thank you. ...
I am trying to update a column in my table which was last inserted. I tried creating this stored procedure: CREATE PROCEDURE [dbo].[msp_AssociateEvent] ( @EventId int ) AS UPDATE tblFoodMenus set EventID = @EventId Where FoodMenuID = IDENT_CURRENT(tblFoodMenus) but it gives me this error: Invalid column name tblFoodMenus. Am ...
I'm trying to work out a pretty complex query in SQL Server 2008. I'd like some input from SQL experts here. Imagine I had a Payments table with these fields: PaymentID int, CustomerID int, PaymentDate datetime, Amount decimal So essentially, it is a table of payments made by a customer on specific dates. An important thing to n...
I am installing SQL Server 2005 Standard Edition. But when the installation has finished installing, tools like Management Studio, Business Intelligence and Analysis Services are displayed. ...
I want to get the top 5 of selling price of each month in each year. So I put in the code like this def query(request): from django.db import connection cursor = connection.cursor() cursor.execute("SELECT product_style_id ,sum(price) As total_p ,sum(cast( amount as int )) AS total_a FROM jewelry_productorder group by pr...