I have a table in production with 12 columns for each month. I need to create a SP where I pass in a productID, Customer and Month parameter and retrieve the sum of that month. currently my logic is if month = 1 then select sum(JAN) from table where productID = @id and customer = @cust if month = 2 then select SUM(FEB) from table ...
Currently have a database formatted as follows: id (unique id) url: http://domain.com/page.html Urls are both from the same domain and from different domains. This also needs to be accomplished in one query so that I can use things like limits when displaying the data to the end user. Data 1, http://domain.com/about.html 2, h...
I have two tables in an MS SQL Server 2005 database, parent and child, where the parent may be related to many child records. [Child.parent_id] is related to [parent.id]. The child table also has column [foo] I need to bring back all records in the parent table where [child.foo] matches on each of one to many parameters. For example I wo...
I have an sql server table with a timestamp column. Is there a way to force the timestamp column to change without an actual update of the record? The reason I am asking is because I want the record's timestamp to change when a record is inserted/updated/deleted in a child table. Timestamp may not be the best way to go about doing this...
I was researching for a CMS to use and ran into a review on vBulletin 4.0; using about 200 queries on one page load. I was then worried. Further research brought me to other sites to see how much queries they are using and I found that some forum software such as Invision Power Board and PHPBB are using queries as low as 6 or 8. Curre...
Background: I have a MS SQL application that reads data from our Oracle billing database once an hour, looking for new payments. It does this by storing a timestamp based on the CRT_DTTM of the most recent timestamp found each time it runs. e.g. SELECT * FROM V_TRANS WHERE TRANS_CLS = 'P' AND CRT_DTTM > TO_DATE('2010-01-25 12:59:44...
How do I call stored procedures in bulk? I would like to do something like a bulk copy. All that the stored procedure does is 8 selects for unique constraint and 8 inserts. With no returning value. ...
Hi All, I am using WinHost.com to host my site. The SQL Database/membership system works perfectly on my local computer, but when uploaded to the server it doesn't work. I've followed all steps correctly. And I have contacted support for my service but it's been over 2weeks and no reply. I keep getting this error when i try to login or...
Given this table: How can I get the datediff in days between each status_date for each group of ID_Number? In other words I need to find the number of elapsed days for each status that the ID_Number has been given. Some things to know: All ID_Number will have a received_date which should be the earliest date for each ID_Number (bu...
I am using sqlconnection. I want to create a view and select from the view set. Like I have shown below I have created a view called vwtopic.. from this I need to select distinct values of a column. I cannot put in one easy statment because i need distinct values of topic column only and need to order by another column datetime.. So I...
Postgres has a useful function called GREATEST (which returns the largest value of those passed to it, documented here: http://www.postgresql.org/docs/8.4/interactive/functions-conditional.html), is there any equivalent in SQLite? As a note, I only need it to work with 2 arguments. ...
Say I have a Post model. When I delete last post 'Post 24', I want the next post to take id of Post 24 and not Post 25. I want to show id in views and I don't want missing numbers. How do I do that? Thanks for your help. ...
I know that there is Sql Server Reporting Services uses caching. But, I would like to now if it caches the report before clicking the View Report button i.e. does it already cache the data for the report by merely changing the values of the dropdown Report Parameters. Here is the scenario: I call stored procedures to get the labels a...
I have a table like this: Field1 | Field2 | Field3 1 | 1 | 22 1 | 2 | 10 2 | 5 | 40 2 | 2 | 55 I want to group by Field1, and then take the values from the rest of the row with the minimal Field2, eg: Field1 | Field2 | Field3 1 | 1 | 22 2 | 2 | 55 Note that this is not the...
Hi, Given a From Date, To Date and a Fiscal Year system, I want to get all the split-up duration within the given From & To Date based on the Fiscal Year system. Explained below with examples: Example 1: Fiscal Year system: Apr to Mar From Date: Jan-05-2008 To Date: May-15-2008 Based on Fiscal Year system, duration should be splitte...
When you want to insert default values into a table, some databases allow this syntax: INSERT INTO table DEFAULT VALUES; ASE does not support this. Using: INSERT INTO table (col2, col3) VALUES (DEFAULT, DEFAULT) and skipping the identity column works for columns with constant default values, but not for computed columns including ti...
I found SubSonic, but I'd rather roll my own with a few buddies. I've never done something like this before, but if you tell me technically how I should proceed, I can figure it out. I want to have a sort of visual designer. Ask the user what tables there are going to be, the relations, what fields, which are primary keys, etc. Then g...
I am about to embark on a PostgreSQL project for a client. They want to develop a huge professional database with many complex joins, so after consideration I have chosen to go with PostgreSQL over MySQL. An important consideration is how to effectively interface to the database with scripts. Currently, the client uses about a million s...
I need to perform a group by, but only on rows that meet a condition, otherwise return all rows that do not meet the condition. For example, in the following table, I want to group only rows that have '1' in the "active" field, and return all rows that do not. TABLE (id, label, active): 1, A, 1 2, A, 1 3, B, 0 4, B, 0 Would return: ...
Latelly I've seen a lot of PHP/MySQL questions that enclose SQL values within {}, for instance: SELECT * FROM table WHERE field LIKE '{$value}'; What's up with that? Is it even valid? Why do so many people use this weird (at least for me) syntax? ...