Based on the below table and inputs: Id RelatedId -------------- 1 1 1 2 1 3 2 2 2 3 2 4 3 5 Inputs are @input_1 = 2 and @input_2 = 3 (input count can vary) I want to select only those Ids from the above table that have both these input in their corresponding RelatedIds. So, based on the give inputs, output will be...
I'm just getting my head around insert statements today after getting sick of cheating with Dreamweaver's methods to do this for so long now (please don't laugh). One thing I'm trying to figure out is how to get the ID value of a newly inserted record so I can redirect the user to that page if successful. I have seen some examples whic...
Hi. I could need some help with a T-SQL query. I want to count fields, that have a special value(e.g. >1). Assuming i have a table Like IGrp | Item | Value1 | Value2 ############################# A | I11 | 0.52 | 1.18 A | I12 | 1.30 | 0.54 A | I21 | 0.49 | 2.37 B | I22 | 2.16 | 1.12 B | I31 | 1.50 | 0.28 ...
Hi. I have a table called Periods that looks like this PeriodID | PeriodYear | PeriodQuarter 7 | 2009 | 1 8 | 2009 | 2 9 | 2009 | 3 10 | 2009 | 4 11 | 2010 | 1 12 | 2010 | 2 Each row in the table represents 1 of the 4 quarters of the year (like 3-monthly school terms). E.g. The first row represents Period 1 of 2009 (i.e. the dat...
I ran into this a bit ago and was wondering why the "Begin" & "End" is need to result in correct values. the if statement(s) is a singleton and does not require the "Begin" & "End" where multiple statements in the if would require it and if omitted would generate an execution error when trying to create/alter the procedure. Any ideas...
What I want to do is something like this DECLARE @operator nvarchar; SET @operator = 'AND' SELECT * FROM MyTable WHERE first_column = "1" @operator second_columnt = "2" is there a way to implement a logic like that one ? ...
Is there a TSQL script that will allow me to see the contents of a constraint. I found a question regarding Oracle but I need a TSQL script. http://stackoverflow.com/questions/2686353/how-to-see-contents-of-check-constraint-on-oracle I am aware of sys.check_constraints, however, the 'definition' comes back null for all objects. Selec...
I have a user defined function (e.g. myUDF(a,b)) that returns an integer. I am trying to ensure this function will be called only once and its results can be used as a condition in the WHERE clause: SELECT col1, col2, col3, myUDF(col1,col2) AS X From myTable WHERE x>0 SQL Server tries to detect x as column, but it's really an...
Hi. I'm having some difficulty in understanding the following WHERE clause in a T-SQL (SQL Server 2000/2005) query: update #tempTable SET Total_Avg=isnull(TerminationReason,'terminated'), Individual_Subscriptions=null, Business_Subscriptions=null, Other_subscriptions=null, -- snip. 10 more fields set to NULL. PMI...
Right now I'm storing a number of records in SQL Server with a DATETIME column that stores the current timestamp using GETUTCDATE(). This ensures that we're always storing the exact date without having to worry about questions like "well is this 2:00 my time or 2:00 your time?" Using UTC ensures that we know exactly when it happened rega...
I have a sticky SQL issue and I'm not the best with SQL... I have a table that has a varchar(40) column that has data in the following two formats: nn-nnn1nnn00000 nn-nnn-nnn-0000 The second data type is outdated; however because they are outdated they need to be viewed first in order. It was recommended to me to substring the last 5...
This snippet: select Datename(hh,DATEADD(HH, -5, [time])) + ':' + Datename(mi,[time]).... will produce: 11:4 But i need the leading '0' in front of the '4'. ...
What I mean by deterministic is that the query will always return exactly the same result set. Is there a way to do this? ...
I've seen mention of the Oracle WITH clause a few times around here for aliasing a subquery like this: WITH myData AS (SELECT id, text FROM SomeTable) SELECT myData from SomeOtherTable Does any version of SQL Server support this? If not, is there a particular reason they don't? Performance? Potential for incorrect usage? ...
How to get time part from Sql Server 2005 datetime in HH:mm tt format e.g. 11:25 AM 14:36 PM ...
Hello all, I have a web based program which chooses some records from a database using a checkboxlist. (my check box list sends parameters to a stored procedure and I use the result of stored procedure). Here how my check box list looks like Overflow [] (sends param1) Open Records [] (sends param2) Records with 4...
Hi, If I have two tables, say: Clients ( ClientID int primary key, ClientName varchar(50) not null ) and Addresses ( AddressID int primary key, AddressLine1 varchar(50), etc.. ClientID int not null, IsPrimaryAddress bit not null ) with a unique constraint across (ClientID, IsPrimaryAddress), how can up date those tab...
Hi, I'm using a generic system for reporting which takes data from a database view (SQL Server 2005). In this view I had to merge data from one-to-many relations in one row and used the solution described by priyanka.sarkar in this thread: Combine multiple results in a subquery into a single comma-separated value. The solution uses SQLX...
I want to place this working code within a SQL Statement, OR do I need to perform a UDF. The result set is a one line concatenation, and I want it to be place in every one of the overall result set lines. ---- MAIN QUERY SELECT H.CONNECTION_ID, H.SEQUENTIAL_NO, H.INVOICE_NUMBER, H.INVOICE_DATE, H.LAST_INVOICE_NUMBER, ...
Hello, I have a question addressed to sql guru. There are two tables with almost identical structure. Based on parameter passed into the stored procedure I need to collect data from one or another table. How to do that in a best way? Please do not suggest to combine those tables into single one - that is not appropriate. I did it ...