You should write as: — STEP2: Insert records: INSERT INTO @Travels SELECT CountryId,VisitorId,0 FROM — 0 = false for IsVisited ( — STEP1: first create a combination for all visitor Id and country Id — and get all the combinations which are not there in existing Travels table SELECT C.CountryId,V.VisitorId FROM @Country C CROSS JOIN … Read more
If your names are stored in another table then you can do the following: insert into test (id, name, address, phonenumber) select id, name, ‘123 test dr’, ‘12345678’ from names select * from test See SQL Fiddle with Demo Based on your edit, you will do the following: declare @recNum int = 0 while @recNum … Read more
Picture paints a thousand words No! Don’t post pictures of data or code! Your question is not at all clear, even with the picture. And – looking at my attempt to answer – you see, that I had to type in a test scenario. This was your job actually… For the next question please provide … Read more
SELECT MYTABLE1.*, MYTABLE2.* FROM MYTABLE1 LEFT OUTER JOIN MYTABLE2 ON same WHERE XXX The ON does not have to relate the two tables This is more like a filtered cross join 1 solved Convert multiple sub-queries to on join [closed]
Jain, it looks like you are trying to adapt the code in the answer here to your own situation. What Andomar did there was to select the information from a subquery which contained only a single value, since he didn’t have access to the original poster’s source and tables. Doing it that way allows everyone … Read more
With a precision of two decimal places, determine the average number of guns for all battleships (including the ones in the Outcomes table) solved With a precision of two decimal places, determine the average number of guns for all battleships (including the ones in the Outcomes table)
Collect your error messages, if you get none return the result of your query, else return or raise the collected errormessages. Declare @a Table (a int,b int,c int) insert into @a Values(1,2,3),(4,4,4) Declare @va int=2 Declare @vb int=2 Declare @vc int=2 Declare @error Varchar(100)=” if not exists(select * from @a where a=@va) Select @Error=@Error + … Read more
all what i had to do is add this: ‘ into ##myTempTable DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) –Get distinct values of the PIVOT Column SELECT @ColumnName = ISNULL(@ColumnName + ‘,’,”) + QUOTENAME([month]) FROM (SELECT DISTINCT [Month] FROM MyTable) AS [Month] order by [month] –Prepare the PIVOT query using the dynamic SET @DynamicPivotQuery … Read more
surecModel = Surecler.Select(s=> new { s.SurecId , s.Damga}) .Distinct() // .Join( db.WEB_SURECROL.DefaultIfEmpty(), SURECDAMGALAR => SURECDAMGALAR.SurecId, SURECROLLER => SURECROLLER.SurecID, (SURECDAMGALAR, SURECROLLER) => new { SURECDAMGALAR, SURECROLLER }) // .GroupJoin(Surecler.DefaultIfEmpty(), surecTanim => new { surecTanim.SURECDAMGALAR.Damga,surecTanim.SURECROLLER.RolId }, SUREC_LIST => new { SUREC_LIST.Damga,SUREC_LIST.RolId }, (surecTanim,SUREC_LIST) => new {surecTanim,SUREC_LIST } ) // .Select(x=> new { x.surecTanim.SURECROLLER.WEB_SURECTANIM.SurecAdi, x.surecTanim.SURECROLLER.WEB_SURECTANIM.SurecTip, x.SUREC_LIST.FirstOrDefault().Damga, x.SUREC_LIST.FirstOrDefault().OnayDurumu, x.surecTanim.SURECROLLER.WEB_ROL.RolAdi, … Read more
Your current syntax problem is with the @count = @count + 1; which needs to be set @count = @count + 1. But… There is no need for a loop. You can simply do one big insert directly, like: insert into your_table (fk_col, other_col1, other_col2) select pk_col, ‘something’, ‘something else’ from your_other_table If you need … Read more
select ID, — returns a sequence without gaps for consecutive years first_value(year) over (partition by ID order by year desc) – year +1 as x, — returns a sequence without gaps row_number() over (partition by ID order by year desc) as rn from Temp e.g. for ID=1: 1 2016 1 1 1 2015 2 2 … Read more
You can do it using XQuery SELECT [TO] = t.XmlColumn.value(‘(ParameterValues/ParameterValue[Name/text() = “TO”]/Value/text())[1]’, ‘varchar(100)’) FROM YourTable t / is a child node navigation. [] is a predicate test on a particular node. So this looks for ParameterValues/ParameterValue which has a Name child with text TO and returns the Value child’s text. Note the use of text() … Read more
This is a classic Relational Division With Remainder question. You just need to frame it right: You want all Tasks… … whose TaskTags divide the set of all UserTags for a given User There can be a remainder of UserTags but not a remainder of TaskTags so the former is the dividend, the latter is … Read more
EDIT From SQL Server 2012 and later you might use the FORMAT function if you want to force a date into a formatted text: https://msdn.microsoft.com/en-us/library/hh213505.aspx With earlier versions you might use something like this: DECLARE @d DATETIME=GETDATE(); DECLARE @TargetFormat VARCHAR(100)=’DD/MM/YYYY’; SELECT CONVERT(VARCHAR(100),@d, CASE @TargetFormat WHEN ‘MM/DD/YYYY’ THEN 101 WHEN ‘DD/MM/YYYY’ THEN 103 –add all formats … Read more
SELECT c.ClientID, c.LastName, c.FirstName, c.MiddleName, CASE WHEN cudf.UserDefinedFieldFormatULink = ’93fb3820-38aa-4655-8aad-a8dce8aede’ THEN cudf.UDF_ReportValue AS ‘DA Status’ WHEN cudf.UserDefinedFieldFormatULink = ‘2144a742-08c5-4c96-b9e4-d6f1f56c76’ THEN cudf.UDF_ReportValue AS ‘FHAP Status’ WHEN cudf.UserDefinedFieldFormatULink = ‘c3d29be9-af58-4241-a02d-9ae9b43ffa’ THEN cudf.UDF_ReportValue AS ‘HCRA Status’ END INTO #Temp FROM Client_Program cp INNER JOIN client c ON c.ulink = cp.clientulink INNER JOIN code_program p ON p.ulink = cp.programulink … Read more