Since you are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL: DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(‘PeriodId’+cast(periodid as varchar(10))) from Periods FOR XML PATH(”), TYPE ).value(‘.’, … Read more
One more method based on JSON. It will work starting from SQL Server 2016 onwards. SQL Server 2016 — DDL and sample data population, start DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(250)); INSERT Into @tbl (tokens) VALUES (‘250 Cambridge ST 305′), (’30 ACKERS AVE 2’); — DDL and sample data population, end … Read more
Try this DECLARE @temp TABLE(col1 varchar(20),col2 int, col3 varchar(20)) insert into @temp values (‘data1′, 123 , ’12/03/2009’),(‘data1′, 124 , ’15/09/2009’), (‘data2 ‘,333 ,’02/09/2010’),(‘data2 ‘,323 , ’02/11/2010’), (‘data2 ‘,673 , ’02/09/2014’),(‘data2′,444 , ’05/01/2010’) SELECT (CASE rno WHEN 1 THEN col1 ELSE ” END )AS col1, col2, col3 FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY … Read more
Move the SAS syntax to the SAS side of the query. You also had an extra comma in the middle of the FROM clause. Note if you get in the habit of putting the commas (or any other continuation characters) at the beginning of the line instead of the end they will be easier for … Read more
Try following query: UPDATE TableB SET [1] = ISNULL(z.[1],TableB.[1]), [2] = ISNULL(z.[2],TableB.[2]), [3] = ISNULL(z.[3],TableB.[3]), [4] = ISNULL(z.[4],TableB.[4]), [5] = ISNULL(z.[5],TableB.[5]), [6] = ISNULL(z.[6],TableB.[6]), [7] = ISNULL(z.[7],TableB.[7]) FROM ( SELECT [1],[2],[3],[4],[5],[6],[7] FROM (SELECT Id, Value FROM TableA)AS p PIVOT (MAX(Value) FOR Id IN([1],[2],[3],[4],[5],[6],[7]))AS pvt )z EDIT In order to have dynamic pivot use following query: … Read more
You can use datepart if you have quarters specified as 1,2,3 and 4 as: declare @date date = getdate() select case when datepart(MM, @date) IN (4,5,6) then ‘Q1’ when datepart(MM, @date) IN (7,8,9) then ‘Q2’ when datepart(MM, @date) IN (10,11,12) then ‘Q3’ when datepart(MM, @date) IN (1,2,3) then ‘Q4’ end as Quater solved get quarter … Read more
A) Use String_Split or something similar to create a table for each word. LG 55 Vacuum theater home #table_of_words (text varchar(32)) text LG 55 Vacuum Theater Home B) join the created table with the main table using an expression like SELECT DISTINCT Description FROM main_table Main JOIN #table_of_words WD ON Main.Description Like ‘%’+WD.text+’%’ If you … Read more
As a general disclaimer, you should usually avoid storing CSV data in your tables because it is the epitomy of denormalization. Instead, store each CSV value on a separate row. To add to this problem, SQL Server has no built in function like MySQL’s FIND_IN_SET() which can conveniently search for a string inside a CSV … Read more
You can use APPLY : SELECT t.ID, MIN(t.startdate) AS StartDate, MAX(t.Enddate) AS EndDate FROM table t OUTER APPLY ( SELECT TOP (1) t1.Enddate FROM table t1 WHERE t1.id = t.id AND t1.startdate >= t.startdate AND t1.enddate IS NOT NULL ORDER BY t1.startdate ) t1 GROUP BY t.ID, t1.Enddate; solved SELECT Record that have start date … Read more
SELECT COALESCE(s.state, c.state) AS state , COALESCE(s.city, c.city) AS city , COALESCE(s.Suppliers, 0) AS Suppliers , COALESCE(c.Consumers, 0) AS Consumers FROM ( SELECT Tb_Supplier.State , Tb_Supplier.City , COUNT(Tb_Supplier.Name) AS Suppliers FROM Tb_Supplier GROUP BY Tb_Supplier.City , Tb_Supplier.State ) AS s FULL OUTER JOIN ( SELECT Tb_Consumer.State , Tb_Consumer.City , COUNT(Tb_Consumer.Name) AS Consumers FROM Tb_Consumer GROUP … Read more
select empid, sum ( datediff ( MINUTE, case when timesheet.timein < @timeframe_start then @timeframe_start else timesheet.timein end, case when timesheet.timeout > @timeframe_end then @timeframe_end else timesheet.timeout end ) ) as total_duration from ( select timein.empid, timein.swipe_time as timein, timeout.swipe_time as timeout from tbltest timein left join tblTest timeout on timein.empid = timeout.empid and timeout.eventtype=”ex” and … Read more
You can left join to a derived table containing the “numbers” (well, strings with a possible numeric representation actually). SELECT d.clientnumber FROM (VALUES (‘00602’), (‘00897’), (‘00940′)) AS n (n) LEFT JOIN dataentry AS de ON de.clientnumber = n.n; That’ll give you as many NULLs as “numbers” in the list that aren’t present in the table … Read more
I think it’s a terrible idea exposing your database to the internet. Much better solution would be to write a Web API that is exposed to the internet which both the web and desktop apps use to perform their tasks by interacting with the API. 1 solved One Common database of sqlserevr for web And … Read more
Assume that you table contains information about Company, Charge, Commission and you want to get total per each company and also per each of its column. As you didn’t provide any table schemas or your data sample, I created a table, populated with data from your question. I’d suggest the next query: create table test … Read more
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