sql-server

[Solved] Database schema with categories, subcategories and products [closed]

by

I would recommend structuring your category table like below: Category ———- CategoryId ParentCategoryId –nullable CategoryName I’m not sure what you want to store in products so it’s hard for me to tell you how to design that, but at a minimum you should have a CategoryId column in there. I would leave it to your … Read more

[Solved] Custom row number in SQL

by

If you have a column in that table, that defines an order, you could get the section numbers with subqueries fetching the count of rows with lower ordered rows with the respective level. Assuming the column defining the order is id something looking like this could be what you want. SELECT col1, convert(varchar(max), (SELECT count(*) … Read more

[Solved] Connect to client’s SQL Server [closed]

by

It’s better to store sql server connection string in your configuration file (if you are on .net framework then it’s App.Config for desktop apps and Web.Config for web apps) which you can update independently from your compiled app. And then you will be able to connect any sql server you have access to. 0 solved … Read more

[Solved] SQL Server Converting Rows to Columns

by

You can get the output you desire with grouping and some CASE statements inside SUM aggregate functions: SELECT dbo.TableB.TrackingID, dbo.TableA.ParcelCode, dbo.TableC.CustID, SUM(CASE WHEN dbo.TableB.FinanceType=”Invoice” THEN dbo.TableA.TotalAmount ELSE 0 END) AS TotalAmount, SUM(CASE WHEN dbo.TableB.FinanceType=”Invoice” AND TransType=”Card” THEN dbo.TableA.TotalAmount ELSE 0 END) AS CardInvoice, SUM(CASE WHEN dbo.TableB.FinanceType=”Invoice” AND TransType=”Cash” THEN dbo.TableA.TotalAmount ELSE 0 END) AS CashInvoice, … Read more

[Solved] How to Join 4 tables in SQL

by

Ok, now that I think I better understand the question you need the following fields: Fornecedor.nomeFornecedor, idEncomenda, Produto.nomeProduto and Produto_encomenda.quantidade. So, let’s see if this works: SELECT f.nomeFornecedor, e.idEncomenda, p.nomeProduto, pe.quantidade FROM Fornecedor as f INNER JOIN Encomenda AS e ON f.idFornecedor = e.idFornededor INNER JOIN Produto_Encomenda as pe ON e.idEncomenda = pe.idEncomenda INNER JOIN … Read more

[Solved] Exception DATABASE1.MDF’ cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported

by

Exception DATABASE1.MDF’ cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported solved Exception DATABASE1.MDF’ cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported

[Solved] Procedure in SQL Server query

by

You better specify all columns after your table #tempbe_budget like following: INSERT INTO #tempbe_budget( budgetno, Finyear, Deptid, ….. ) Add collation adjustment to your JOINS: FROM budget bud LEFT OUTER JOIN womaster wo ON wo.bcno1516 COLLATE SQL_Latin1_General_CP1_CI_AS = bud.newno COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN department dept ON dept.deptid COLLATE SQL_Latin1_General_CP1_CI_AS = bud.deptid COLLATE SQL_Latin1_General_CP1_CI_AS LEFT … Read more

[Solved] sql search in multi columns [closed]

by

Put parens around your or-ed conditions, and change the double quotes to single quotes for the year (as suggested by jarlh): select from products WHERE year=”2016″ and ( name like ‘%”& name &”%’ or size like ‘%”& Size &”%’ ) Oh and in case this is VB.NET and you got name and Size from a … Read more

[Solved] SQL server SELECT CASE large script [closed]

by

Try This , Create another table like this CREATE TABLE [dbo].[Table_1]( [Col2] [nvarchar](50) NULL, [CaseVal] [nchar](10) NULL ) ON [PRIMARY] Insert all the Distinct data what you have. Then write a sql like below SELECT b.Col1, b.Col2, a.CaseVal TargetAliasColumnName FROM Table_1 a inner join [dbo].[Table1] b on a.col2=b.Col2 1 solved SQL server SELECT CASE large … Read more

[Solved] Order by two columns with usage of CASE WHEN

by

CASE is an expression that returns a single expression/value. You need to write one CASE statement per column: ORDER BY CASE WHEN @cOrderBy = ‘USER_FNM_USER_LNM ASC’ THEN USER_LNM END ASC, CASE WHEN @cOrderBy = ‘USER_FNM_USER_LNM DESC’ THEN USER_LNM END DESC, CASE WHEN @cOrderBy IS NULL THEN USER_KEY END ASC, CASE WHEN @cOrderBy = ‘USER_FNM_USER_LNM ASC’ … Read more

[Solved] Write an INNER JOIN in LINQ

by

I think it is easier to translate SQL using query comprehension syntax instead of lambda syntax. General rules: Translate inner queries into separate query variables Translate SQL phrases in LINQ phrase order Use table aliases as range variables, or if none, create range variables from table names abbreviations Translate IN to Contains Translate SQL functions … Read more