You have a surplus comma here: @answer nvarchar (100)=null, ) also checkisn’t a good column name as it is a reserved keyword, and the datetype doesn’t take a size parameter. This should work: CREATE PROCEDURE dbo.storMember ( @Check nchar (1), @UserName nvarchar (15), @Passowerd nvarchar (15)=null, @Name nvarchar (15)=null, @Phone nvarchar (15)=null, @email nvarchar (30)=null, … Read more
Here is a generic method regardless of the level of the hierarchy. SQL — DDL and sample data population, start DECLARE @tbl table ( idGeo INT IDENTITY PRIMARY KEY, GEO VARCHAR(64), PARENTID INT ); insert into @tbl (GEO, PARENTID) values ( ‘EMEA’, NULL), ( ‘France’, 1), ( ‘mIDCAPSfRANCE’, 2), ( ‘Germany’, 1), ( ‘France exl … Read more
The endings are different 7i18704 5i18704 4i18704 Following your comment I have updated and they GROUP as expected. What do you get when you try this? CREATE TABLE #Advertisements ( ID INT IDENTITY(1,1), Url VARCHAR(200) ) INSERT INTO #Advertisements VALUES (‘http://example.com’) INSERT INTO #Advertisements VALUES (‘http://example.com’) INSERT INTO #Advertisements VALUES (‘http://example.com’) SELECT COUNT(*) c, Url … Read more
There are some issue with your insert statement The column ZID is INT and you are passing 12 as string like ’12’ you can remove ‘ but it won’t give an error. The column CID is Varchar(2) and you are trying to insert 3 character string ‘ASD’ it will give a data truncation error. ie … Read more
I’m not sure what you’ve tried so far but based on your sample data, try this: SELECT UserID, SessionID, MIN(timestamp) StartTime, MAX(timestamp) EndTime, DATEDIFF(s,MIN(timestamp), MAX(timestamp)) SecondsDifference FROM Table GROUP BY UserID, SessionID This kind of data is usually tricky because you don’t have the luxury of a sessionid, or the sessionid doesn’t truly reflect what … Read more
You could calculate the 1st of the month using EOMONTH something like this SELECT DISTINCT ATB.AcountCountDesc,TB.LastFirstName,N.EMAIL,TB.AccountNumber,TB.OpenShareCount,TB.MemberOpenDate, TB.OpenMemberCount,TB.OpenShareBalance,SH.ShareType,FORMAT(SH.ShareOpenDate,’MM/dd/yyyy’) AS “ShareOpenDate”, SH.ShareCreatedByUser,SH.ShareCreatedByUserName,SH.ShareBranchName,SH.ShareBranch,cast(month(SH.ShareOpenDate) as varchar) + “https://stackoverflow.com/” + cast(year(SH.ShareOpenDate) as varchar)as ‘Open Period’, CONCAT(SH.ShareCreatedByUser,’-‘,SH.ShareCreatedByUserName) ‘Opened By’ FROM arcu.vwARCUOperationMemberTrialBalance as TB JOIN arcu.vwARCUOperationMemberAccountTrialBalance as ATB ON TB.MemberSuppID = ATB.MemberID and TB.ProcessDate = ATB.PDate and TB.MemberStatus = 0 — Account … Read more
In order to get the result that you want you will have to apply both the UNPIVOT and the PIVOT functions. The unpivot function will convert your Q1, Q2, Q3, and Q4 columns into rows. Once that is done, you will apply the pivot function. The unpivot code will be similar to this: select programid, … Read more
with SQL Server you can use those very usefull windowed functions LEAD and FIRST_VALUE : select * ,[duration(sec)] = DATEDIFF(SECOND ,ticketTime ,LEAD(ticketTime,1,ticketTime)over(partition by ticketNumber order by ticketTime) ) ,[cumulative duration(sec)] = DATEDIFF( SECOND , FIRST_VALUE(ticketTime)over(partition by ticketNumber order by ticketTime) , ticketTime) from (values (1,cast(‘20211101 10:00:01′ as datetime)) ,(1,’20211101 10:00:33′) ,(1,’20211101 10:01:59’) )T(ticketNumber,ticketTime) ticketNumber ticketTime … Read more
DECLARE @YEAR int = 2020; DECLARE @WEEKSTOADD int = 6; SET DATEFIRST 1; SELECT DATEADD(day, 1 – DATEPART(dw,DATEADD(week,@WEEKSTOADD,cast(cast(@YEAR as varchar(4)) + ‘0101’ as date))), DATEADD(week,@WEEKSTOADD,cast(cast(@YEAR as varchar(4)) + ‘0101’ as date))) 3 solved Get first day of week T-SQL
It looks like vb code if you want c# code then use following code cmd = new SqlCommand(“select name from wq where id='” + TextBox1.Text + “‘”, con); con.Open(); dr = cmd.ExecuteReader(); dr.Read(); TextBox2.Text = dr[0].ToString(); dr.Close(); con.Close(); 7 solved Trouble writing a select query [closed]
If I have two columns, how can I select all distinct values of columnA where columnB is never a specific value given that columnA is the same value? solved If I have two columns, how can I select all distinct values of columnA where columnB is never a specific value given that columnA is the … Read more
The method I have used here is a Tally Table to create extra rows. I JOIN onto the tally table where the number of hours / 24 (integer maths is useful) is greater than the tally number, and then can use that to calculate the hours. WITH YourTable AS( SELECT * FROM (VALUES(1,CONVERT(date,’2018/01/24′,111),4 ), (2,CONVERT(date,’2018/03/21′,111),40), … Read more
Using CASE Condition and Row_number we can achieve the above Output It’s Purely based on your sample Data DECLARE @Table1 TABLE (projName varchar(1), percentage int) ; INSERT INTO @Table1 (projName, percentage) VALUES (‘A’, 10), (‘A’, 25), (‘B’, 20), (‘B’, 30) ; Select CASE WHEN RN = 1 THEN projName ELSE NULL END projName, percentage from … Read more
Following should work .. SELECT decadeBegin, decadeEnd, NumOfMovies FROM dbo.TableOfDecades(); solved using of functions or views in sql
There are no Differences, Both cases Work as Same except that you are declaring them using 2 statements. But There are no performance effects 1 solved Difference between two sql statements