I think you need to use a case statement for the value. Below is just off the top of my head, so you may need to use full join if that doesn’t work. select t1.date, case when t3.IDCLIENT is not NULL then t3.value else t2.value end as Value from Table1 t1 inner join Table2 t2 … Read more
Try like this protected void loginButton_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(); con.ConnectionString = “Data Source=.\\SQLEXPRESS;Initial Catalog=University;Integrated Security=True;Pooling=False”; Int32 verify; string query1 = “Select count(*) from Login where ID='” + idBox.Text + “‘ and Password='” + passwordBox.Text + “‘ “; SqlCommand cmd1 = new SqlCommand(query1, con); con.Open(); verify = Convert.ToInt32(cmd1.ExecuteScalar()); con.Close(); if … Read more
Try this: DECLARE @SUM VARCHAR(50)=’33.90′; SELECT CAST(CAST(LEFT(@SUM,CHARINDEX(‘.’,@SUM,0)-1) AS INT)+ CAST(SUBSTRING(@SUM,CHARINDEX(‘.’,@SUM,0)+1,LEN(@SUM)) AS INT)/60 AS VARCHAR(10))+’.’+ CAST(CAST(SUBSTRING(@SUM,CHARINDEX(‘.’,@SUM,0)+1,LEN(@SUM)) AS INT)%60 AS VARCHAR(10)) result: 34.30 1 solved How to convert the Varchar of sum to time using sql server 2008 [closed]
Same as any other… Based on PEMDAS Parenthesis Exponents Multiply and Divide at same level, left to right Add and Subtract at same level left to right. (D-E) is done first (B) is done next, but is left as-is as no other direct relation A * B C * 100 then (result of C * … Read more
You seem to want aggregation: select disport, actualagent, . . . , country, sum(pallats), sum(gross) from t group by disport, actualagent, . . ., country; You need to list all the columns where the . . . is. 1 solved Select distinct record from table and perform Sum of column (Pallats, Gross) of a duplicate … Read more
You can use apply: select a.*, b.* from a cross apply (select top (1) b.* from b where b.code = a.code and b.time < getdate() order by b.time desc ) b; This assumes that time is really a datetime. If you just want to compare times, then use convert(time, getdate()). 2 solved Left join combining … Read more
SELECT ID,SUM(CASE WHEN Level=1 AND Grade IN (‘A’,’B’,’C’,’D’) THEN 1 WHEN Level=2 AND Grade IN (‘A’,’B’,’C’) THEN 1 ELSE 0 END AS GradeCount FROM YourTable GROUP BY ID; 0 solved Select criteria using a case expression in where clause based on other columns [closed]
Here is another option Example Select Robot_Type from (Select distinct Robot_Type From YourTable ) A Order By try_convert(int,left(replace(Robot_Type,’IRB’,”),patindex(‘%[a-z,/]%’,replace(Robot_Type,’IRB’,”)+’a’)-1)) ,Robot_Type Returns Robot_Type NULL IRB 120 IRB140 IRB 360 IRB 910SC —<< Disconnect from your desired results IRB 1200 IRB 1410 IRB 1520 IRB1600 IRB1600/1660 IRB1600ID IRB1600ID/1660ID IRB1660ID IRB 2400 IRB 2600 IRB 4400 IRB 4600 IRB … Read more
create procedure my_procedure (@col_alias varchar(100)) as declare @my_stmt nvarchar(max) = N’select 1+1 as ‘ + @col_alias exec sp_executesql @stmt = @my_stmt exec my_procedure @col_alias=”[This is a dynamix col alias]” This is a dynamix col alias ————————— 2 15 solved Generate column Alias name dynamically in MS SQL Server [closed]
Try this: DECLARE @dt VARCHAR(8) = ‘201501’ SELECT LEFT(CONVERT(VARCHAR(8), DATEADD(m, -1, @dt + ’01’), 112), 6) Output: 201412 Using DATEADD you can calculate the previous month. This function conveniently accepts a string as an argument. CONVERT is then used to convert the result back to yyyymmdd format. 1 solved Get previous month in SQL when … Read more
Use a tally table to generate N number of dates DECLARE @TillDate DATE = ‘20151031’ DECLARE @NoOfMonthFromStartDate INT = 5 ;WITH Tally(N) AS( SELECT TOP(@NoOfMonthFromStartDate) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) FROM sys.columns ) SELECT DATEADD(MONTH, -(N-1), DATEADD(MONTH, DATEDIFF(MONTH, 0, @TillDate), 0)) FROM Tally If @TillDate is always the end of a month: ;WITH Tally(N) AS( SELECT … Read more
Here is my explanation for this script following creates a sample database table containing an object (name) and its dependends on an other field (DependsOnCSV) seperated by comma CREATE TABLE tbl (Name VARCHAR(100),DependsOnCSV VARCHAR(100)) Following code populates above table with sample data. This is a new syntax for many developers. If you are working with … Read more
You are missing the connection parameter to sqlsrv_query. While you’re at it, you might as well use bind variables instead of string substitution to help guard against SQL-Injection attacks. $serverName = “serverName\instancename”; $connectionInfo = array( “Database”=>”dbName”, “UID”=>”username”, “PWD”=>”password” ); $conn = sqlsrv_connect( $serverName, $connectionInfo); $params = array($username, $password); $stmt = sqlsrv_query ($conn, ‘select * from … Read more
When you say you accept 9 numbers and want to change it to be 10 numbers, I’m assuming that you mean that the number is 9 digits in length, and you want it to be 10 digits. The maximum size of a 32-bit integer is ~2.1 billion. You could use a long instead, which is … Read more
Simply take out the MAX() function and add this column in your group by clause , happy days…… SELECT [b_BookNum] ,[b_CHMAT] ,COUNT(*) iCount FROM MPA.dbo.SCHM_Books GROUP BY [b_BookNum] ,[b_CHMAT] ORDER BY [b_BookNum] EDIT I am surprised that you have all the information you need to solve the problem and yet you are so incompetent to … Read more