Either of these will work. The second is better, but the first will show you how you can go about this when grouping by isn’t so straight-forward (or achievable). SELECT ISNULL(SUM(CASE WHEN OS_NAME = ‘Linux’ THEN 1 ELSE 0 END), 0) AS [Linux Servers], ISNULL(SUM(CASE WHEN OS_NAME = ‘Windows’ THEN 1 ELSE 0 END), 0) … Read more
Your fact table should contain foreign keys to the dimensions. I see only primary keys 3 solved What is wrong with this DB Diagram? [closed]
as noted it is a bit vague, but you are also JOINING the tables upside down, as I cannot see the [race ID] being listed in the [runners] table, that would mean you have runners duplicated and the results would not correlate to the runners as there is no relation. I might also be way … Read more
There’s no such thing as “sort by” in SQL. ORDER BY lets you establish the sort order of your result set. GROUP BY means something entirely different. You should read about that. solved difference between order by and sort by
The relational model specifies that the tuples of a relation have no specific order. In other words, rows in a database table have no specific order. Therefor, when you are creating anything that can be references as a table (i.e views, derived tables etc’) you can’t specify an order by clause unless it’s needed for … Read more
The error message is pretty clear: The type ‘Edm.String’ of member ‘SEJ_STARDATE’ in type ‘HotelSearch.APP_SEJOUR’ is not compatible with ‘SqlServer.date Change edm.String to a recognizable datetime format (MM/DD/YYYY) by manipulating its contents or using DateTime.Parse solved “Member Mapping specified is not valid” error when saving to a database with Entity Framework
I see from your post edit last time, value from field datetime like ’20/Mar/2013:03:17:44′. Format from value of datetime can’t convert by a simple way. Try this query: SELECT BASESCORE, [DATEDIFF]= Datediff(second, Min(CONVERT(DATETIME, LEFT(Rtrim(Ltrim(DATETIME)), 11) + ‘ ‘ + RIGHT(Rtrim(Ltrim(DATETIME) ), 8))), Max ( CONVERT(DATETIME, LEFT(Rtrim(Ltrim(DATETIME)), 11 ) + ‘ ‘ + RIGHT(Rtrim(Ltrim(DATETIME)), 8)))) FROM … Read more
use a recursive cte that starts with elements whose frompoint matches no topoint in a self join so as to identify the starting points. then eleminate the intermediate results by joining again and use only those data whose topoint match no frompoint in another self join. with cte as ( select r.name, r.frompoint, r.topoint from … Read more
Maybe something like this: Test data: CREATE TABLE #PhoneBook(ID INT,Name VARCHAR(100)) INSERT INTO #PhoneBook VALUES(1,’Reza’),(2,’Ali’) CREATE TABLE #DynamicField(ID INT,Caption VARCHAR(100)) INSERT INTO #DynamicField VALUES(1,’Job’),(2,’Level’) CREATE TABLE #PhoneBook_DynamicField_Rel(ID INT,PhoneBookID INT, DynamicFieldID INT,Value VARCHAR(100)) INSERT INTO #PhoneBook_DynamicField_Rel VALUES(1,1,1,’Emp’),(2,1,2,’1′),(3,2,1,’SomeJob’) Getting the colums DECLARE @cols VARCHAR(MAX) SELECT @cols=STUFF ( ( SELECT ‘,’ +QUOTENAME(tbl.Caption) FROM #DynamicField AS tbl FOR XML … Read more
I would try with this : SELECT [CaseID], STUFF( (SELECT CONCAT(‘; ‘, A.[AssignedPathologist]) FROM CTE1 A WHERE A.[CaseID] = B.[CaseID] FOR XML PATH(”) ),1, 1, ” ) As [AssignedPathologist] FROM (SELECT DISTINCT CaseID CTE1 B) B; For newer versions you can use string_agg() : SELECT CASEID, STRING_AGG(AssignedPathologist, ‘; ‘) AS AssignedPathologist FROM CTE1 C1 GROUP … Read more
Given an input XML, you can use this simple XQuery statement to “shred” the XML into relational rows and columns – just do a simple INSERT INTO ….. and you’re done. No messy cursor nor OPENXML stuff needed… DECLARE @input XML = ‘<Salaray> <TransactionSalary EmpSL=”2″ Basic=”9860″ Grad_pay=”4100.00″ DA=”6282.00″ HRA=”2094″ MA=”300.00″ Ptax=”150.00″ pf=”2000″ Itax=”0.00″ LIC=”0.00″ Month_Of=”14/Dec/2012″ … Read more
Use YEAR and MONTH functions … WHERE YEAR(SaleDate) = 2015 AND MONTH(SaleDate) = 12 Don’t use BETWEEN because you must to express the dates as literals, and that will be a problem considering that your SaleDate column could come with the time part in the future… 1 solved SQL Server: How to do WHERE statement … Read more
Your last query SELECT 0, department, department_name, ”, ‘NP’ as acc_code, ‘NET PROFIT/(LOSS)’ as acc_name,1, SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*yr_balance ELSE yr_balance END)), SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*ly_balance ELSE ly_balance END)), SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*mn_balance ELSE mn_balance END)), SUM((CASE WHEN acc_code=”CS” OR acc_code=”EX” THEN -1*lm_balance ELSE lm_balance END)), … Read more
Finally found the answer: [dbo].[USP_GetProjectPhase] @PurchaseOrder INT AS SELECT -1 AS ‘ProjectPhaseID’ ,’No Filter’ AS ‘Phase’ UNION SELECT pp.ProjectPhaseID ,pp.Phase FROM ProjectPhase pp WHERE @PurchaseOrder = pp.PurchaseOrderId In my query I changed the WHERE clause to: WHERE (reg.ProjectPhaseId IN (SELECT Value FROM fnLocal_CmnParseList(@Phase,’,’)) OR @Phase=”-1″) 1 solved NULL values in multivalue parameter
You can take advantage of SQL Server’s flexibility to recognize various date formats. If you have the right regional settings, this should just work: cast(mycol as date) This gives you back a value of date datetype that corresponds to the input string; Demo on DB Fiddle: select cast(‘Jan 18 2019 12:00AM’ as date) | (No … Read more