sql-server

[Solved] Getting error “Incorrect syntax near ‘,'”

by

You are appending mainmenu.tbxSelected whereas I suspect that your intention was to append mainmenu.tbxSelected.Text The former would probably result in a fully qualified typename in your WHERE clause, which would contain commas. As an aside, you should be aware that constructing SQL in this way leaves you potentially open to SQL Injection attacks. If that’s … Read more

[Solved] SQL Server Char/VarChar DateTime Error [closed]

by

This should look more like this: DateTime now = DateTime.Now; DateTime after1month = DateTime.Now.AddMonths(1); SqlCommand cmd = new SqlCommand(“SELECT * FROM TABLE WHERE THEDATE BETWEEN @now AND @after1month”, connection); cmd.Parameters.Add(new SqlParameter(“@now”, System.Data.SqlDbType.DateTime).Value = now); cmd.Parameters.Add(new SqlParameter(“@after1month”, System.Data.SqlDbType.DateTime).Value = after1month); Sometimes you can do it directly on SQL Server side using query: SELECT * FROM TABLE … Read more

[Solved] Group by – Each Date summary value [closed]

by

You were close. You need to move the CASE…END inside the SUM() and drop TRS.DATE from the GROUP BY and ORDER BY. SELECT TRS.ITEM, SUM(CASE WHEN CAST(TRS.DATE AS DATE)=’2022-01-01′ THEN TRS.QTY END) D1Q, SUM(CASE WHEN CAST(TRS.DATE AS DATE)=’2022-01-02′ THEN TRS.QTY END) D2Q, SUM(CASE WHEN CAST(TRS.DATE AS DATE)=’2022-01-03′ THEN TRS.QTY END) D3Q, SUM(CASE WHEN CAST(TRS.DATE AS … Read more

[Solved] How can I write this stored procedure with EF in C# [closed]

by

SO is not a website when you throw everything here and expected people finish the job for you. Anyway, to give you some hint, I give you a straight suggestion: Select CostGroupId From CostGroups Where CostGroupType = 1 –> Stored these in A collection, like an array: var costGroupsIdArr = ctx.CostGroup.Where(x=>x.CostGroupType == 1).Select(x.CostGroupId).toArray(); Then from … Read more

[Solved] SQL, SUM + CASE [closed]

by

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax. Then, you cannot nest aggregation functions, so this should be sufficient: SELECT o.ID_DOSSIER, SUM(CASE WHEN ID_TYPE IN (‘0’, ‘1’) THEN TTL * -1 WHEN ID_TYPE IN (‘2’, ‘3’) THEN TTL END) FROM ope o JOIN actor a ON o.ID_ACTION = a.ID_ACTION GROUP … Read more

[Solved] Cummulative SUM based on columns

by

No need to make thinks more complicated than they need to be… IF OBJECT_ID(‘tempdb..#TestData’, ‘U’) IS NOT NULL DROP TABLE #TestData; CREATE TABLE #TestData ( ID INT NOT NULL, [Year] INT NOT NULL ); INSERT #TestData (ID, Year) VALUES (1, 2010), (1, 2010), (2, 2010), (2, 2011), (3, 2012), (4, 2013), (5, 2014), (5, 2014), … Read more