Get items with 0 Counts after GroupBy in SQL
Building on the second half of Gordon's answer, you also need to add the condition of your WHERE clause to the join between Days and VendorBillUVBLog. with days as ( select cast(getdate() as date) as dd union all select cast(getdate() - 1 as date) as dd union all select cast(getdate()...
Improving work with SQL DataTime
You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....
MS SQL JDBC connection with MS SQL Server 2012
What finally worked for me (after hours of searching on all kinds of blogs): Installed MS SQL 2012 with a default instance. This removed the 2008 version. Redid all of the above. Plus, added an outbound rule for port 1433. Found the part about outbound rule after much searching. Everybody...
Combine multiple rows using SUM that share a same column value but has different other column values
You can apply the min() aggregate function to the date to limit the number of rows returned to one per account: SELECT Account, SUM(Charges) AS TotCharges, SUM(Charges2) AS TotCharges2, MIN(Date) AS Date FROM TABLE GROUP BY Account ORDER BY Account Sample SQL Fiddle...
Conversion failed when converting date and/or time from character string in sql server
Too many zeros in the millisecond part. This works fine DECLARE @Value nVarchar(MAX)='2016-03-01 00:00:00.000' SELECT CONVERT(DATETIME,@Value,101) You can try using LEFT like this DECLARE @Value nVarchar(MAX)='2016-03-01 00:00:00.000000' SELECT CONVERT(DATETIME,LEFT(@Value,23),101) ...
Order by clause in update query in sql server [on hold]
you need to use TOP: Update tblTempChek Set TmpCheckIn='15:50:03' Where TempID in ( Select TOP 1 TempID From tblTempChek Where Convert(date, TmpDate)='2015-06-23' AND UserID='1' Order By TempID Desc ) ...
Update where column has highest value
That should give you the latest start_date to every entry which has the same value in element like the defined value in update statement. So you need to change it only at one place. UPDATE [table] t SET t.end_date = NULL WHERE t.element = 1 AND t.start_date = (select max(sub.start_date)...
giving username sql server 2012 management studio
Did you mean the SA? If so... you can add your own administrators (ideally with windows authentication) and add them to the role sysadmin. After that you can give SA a very strong password and deactivate it afterwards. The strong password is recommended, as even if the sa is accidentally...
TSQL update value with subquery
sql-server,tsql,sql-update,compare
Could you try this: MERGE TableA AS [Target] USING TableB AS [Source] ON [Target].[ID] = [Source].[ID] AND [Target].[Name ] = [Source].[Name] WHEN NOT MATCHED BY TARGET THEN UPDATE SET NameMod = 1; It is using the MERGE clause. If you do not like the clause, you can use CTE like...
How do I do this monthly calculation taking into account the correct number of months in SQL?
Is this what you're looking for? DATEDIFF(mm, @InputPeriodStart, @InputPeriodEnd)) If you are trying to do something a bit weirder like adjust for the days in the month your "periodstart" is in - then you are getting into some weird territory but it is still possible. Just drop a comment to...
ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables
If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...
How do I check whether the input date's DAY is the last day of the given month in SQL?
An easy way that works in almost any version of SQL Server is: select (case when month(@date) <> month(dateadd(day, 1, @date)) then 'true' else 'false' end) That is, add a day and see if you are in the same month. This works for leap years, and Dec 31 and so...
Newbie to linking SQL tables
Better to add ID in you Calender table so your schema should be something like this Note : in EmployeeDate table you dont need to add FirstName or LastName because you have EmployeeID which references to Employee table where you can easy get those fields. And your query will be...
SQL Server Stored procedure declaring variable in different way
sql-server,tsql,stored-procedures
@UPA nvarchar(30) = NULL is the procedure argument/parameter (in this case, an optional parameter though since it's being declared like that having NULL as the default value. So while calling procedure if you don't supply @UPA, it will assume NULL) that you need to supply while calling the procedure like...
How to Retrieve value from a UDT
It's quite simple. A table type just can be used inside it's database. You try to access it like that: [test].[dbo].[tvf_id] Instead try this: [dbo].[tvf_id] As your error already stated, you exceed the maximum of allowed prefixes. 1 is allowed (dbo) and you use two (test + dbo). For further...
Can someone explain to me how this statement is an exclude?
I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...
Multiple column values into a single column as comma separated value
You can use ISNULL like this ISNULL(',' + CommentA, '') and write your query like this. SELECT CommentId, STUFF( ISNULL(',' + CommentA, '') + ISNULL(',' + CommentB, '') + ISNULL(',' + CommentC, '') + ISNULL(',' + CommentD, '') + ISNULL(',' + CommentE, ''),1,1,'') as [CommentDetails] FROM CommentsTable WHERE ...... //Some...
Take thousand value in SQL
SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...
Setting time limit in SQL Query
Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....
Latest two datetime in SQL Server
With row_number function: with cte as(select *, row_number() over(partition by event_id order by event_timestamp desc) rn from events) select * from cte where rn <= 2 ...
Foreign key in C#
You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...
Purging Database - Count purged/not-purged tables
mysql,sql,sql-server,database,stored-procedures
The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...
Microsoft SQL Insert into subset of table columns fails [on hold]
Check for constraints or triggers that would attempt to insert a value too large for a given column. This can happen when over time schema changes occur, and constraints or triggers escaped the scope of impact review. In this case (varchar(3)) column status had a default constraint that was attempting...
Convert row to columns SQL dynamically [duplicate]
sql,sql-server,sql-server-2008,tsql
First you will need to unpivot your table and after that pivot again. This is the key idea: select * from TableName unpivot(v for xyz in([Atest],[Btest],[Ctest]))u pivot(max(v) for yyyymmdd in([20150525],[20150526],[20150527]))p Fiddle http://sqlfiddle.com/#!3/a675c/1 As for dynamic sql, you can find many example of how you can use STUFF function to concatenate...
Improving SQL Queries
I understand your problem. You have some column-rule. Which is globally used in all app. For example in my case there was 'status' column, and there were some logical meaning called 'important', which worked if column had one of the certain values in the set. So, everywhere, to check if...
TSQL - Error in stored procedure due to conversion failure
sql-server,sql-server-2008,tsql
I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...
Update SQL Server table using XML data
You have 2 errors in your xml: First is nonmatching root tags. Second, more important, you are quering nodes('/Aprroval/Aprrove'), but inner tag is Approve not Aprrove. Fiddle http://sqlfiddle.com/#!3/66b08/3...
SQL Server 2012 & Polybase - 'Hadoop Connectivity' configuration option missing
sql-server,hadoop,sql-server-2012
Are you sure Polybase is installed and enabled? You should have installed it during the SQL Server installation process and enable the according services....
Cannot Browse in sql to backup bak file
Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...
Syntax Error, Operator Expected
Try: lemmas:- odbc_query('my_db', 'SELECT * ,case \ when ActualCost<EstimatedCost then \'true\' \ else \'false\' \ end as Value \ from Work_Order ' ). ...
Not getting the correct SUM values in SQL Server 2012 when using a PIVOT
sql-server,sql-server-2012,pivot,distinct,aggregate-functions
The following query should give you what you want: SELECT Store, TotalSold, [John] AS WastedByJohn, [Jim] AS WastedByJim, [Alex] AS WastedByAlex FROM (SELECT Store, Employee, Waste, SUM(Sold) OVER (PARTITION BY Store) AS TotalSold FROM #Foo) src PIVOT (SUM(Waste) FOR Employee IN ([John], [Jim], [Alex]) ) PVT To understand why you...
Optimize the execution of select
sql-server,indexing,query-optimization,clustered-index,non-clustered-index
There's few things to consider when creating indexes, for example in this case: How many rows have Dane5 > 199850 and how many rows there are in total? Are there a lot of updates to the columns in the index -> slowness to updates. Are there going to be a...
How can I use IF statement in sql stored-procedure
sql,sql-server,stored-procedures
You can combine AND and OR conditions to get the result you desire. You can use below query: SELECT * FROM tablename AS CMS_ENCOUNTER WHERE (@PATIENT_STATUS = 'all') OR (@PATIENT_STATUS = 'out' AND CMS_ENCOUNTER.ENCOUNTER_END_TIME IS NOT NULL) OR (@PATIENT_STATUS <> 'out' AND CMS_ENCOUNTER.ENCOUNTER_END_TIME IS NULL) In this case first you...
How to select next row after select in SQL Server?
The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...
converting varchar to Int SQL SERVER
The error is simply occurring as your destination column can only have one data type. The first part of your CASE statement is effictively setting the column type to expect an integer, so when you hit the ELSE section and try to insert Not Same, you're getting the error. Sample:...
INSERT INTO fails due to incorrect conversion T-SQL
The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...
Dynamic Query Generation is not working with SQL
ALFKI is being treated as if it is a column name. Your generated sql will look like this: select * from Customers where CustomerID = ALFKI What you want is your code to look like this: select * from Customers where CustomerID = 'ALFKI' To achieve this, change your where...
Create a new column in my SQL View - Column must be two other columns combined
sql,sql-server,tsql,concatenation
You simply need to add another column to your select: CREATE VIEW KFF_Rep_Comm AS SELECT vw_KLX_RepCommTrx.AccName AS AccountName ,vw_KLX_RepCommTrx.Account AS AccountNum ,vw_KLX_RepCommTrx.RepID ,vw_KLX_RepCommTrx.RepName ,vw_KLX_RepCommTrx.CommPerc AS CommPercent ,vw_KLX_RepCommTrx.Credit ,vw_KLX_RepCommTrx.Debit ,vw_KLX_RepCommTrx.MUPercent AS MarkUpPercent ,vw_KLX_RepCommTrx.ProfitPercent AS GrossProfitPercent ,vw_KLX_RepCommTrx.SgdTrxAmt AS InvValue ,vw_KLX_RepCommTrx.SgdCost AS Cost ,vw_KLX_RepCommTrx.SgdProfit AS GrossProfit...
Retrieve data from one table and insert into another table
INSERT INTO tbl2 ( Name ,parentId ) SELECT DISTINCT manager ,0 FROM tbl1 WHERE manager NOT IN ( SELECT employee FROM tbl1 ) INSERT INTO tbl2 SELECT DISTINCT employee ,0 FROM tbl1 UPDATE tbl2 SET parentid = parent.id FROM tbl2 INNER JOIN tbl1 ON tbl2.Name = tbl1.employee INNER JOIN tbl2...
How to select from a table with two columns but one of them is null
Maybe try something like : select * from t_items where (code ='#itemcode#' and #itemcode# is not null) OR (definition like '%#definition#%' and #definition# is not null) ...
DELETE … OUTPUT COUNT(DELETED.*)
Just run your query and get the modified rows DELETE FROM datacache WHERE [email protected] SELECT @@ROWCOUNT ...
How do I convert this tSQL statement to LINQ using group by in a sub query
I think there is opportunity to rewrite your query, but for information purposes I rewrote your sql into linq verbatim. If you explain what you are trying to achieve we can provide alternative sql / linq var eqnums = new[] { "M0435", "Z0843" }; var testdate = "2008-06-01"; var query...
SQL Server / C# : Filter for System.Date - results only entries at 00:00:00
c#,asp.net,sql-server,date,gridview-sorting
What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...
Join SQL query Results and Get-ChildItem Results
sql-server,sql-server-2008,powershell
OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...
copy table and drop it
sql,sql-server,sql-server-2008,tsql,stored-procedures
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl2')) BEGIN -- tbl2 exists, so just copy rows INSERT INTO tbl2 SELECT * FROM tbl1; END ELSE BEGIN -- tbl2 doesn't exist, so create new table tbl2 and copy rows SELECT * INTO tbl2 FROM tbl1; DROP tbl1; END This...
Retrieving an XML node value with TSQL?
Here is the solution: DECLARE @xml xml SELECT @xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <SOAP-ENV:Body> <webregdataResponse> <result>0</result> <regData /> <errorFlag>99</errorFlag> <errorResult>Not Processed</errorResult> </webregdataResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>' declare @table table...
SQL Server Query Performance with Timestamp and variable
sql-server,performance,timestamp
My guess is that you also have an index on MachineName - or that SQL is deciding that since it needs to group by MachineName, that would be a better way to access the records. Updating statistics as suggested by AngularRat is a good start - but SQL often maintains...
Get Parent and grand parents of a particular child
sql-server,sql-server-2005,sql-cte
Like my comment, your columns were incorrect in join. Use getallparent p on p.ParentID = c.ChildID. with getallparent as ( select ChildID,ParentID from ExampleTable where ChildID = 5 union all select C.ChildID,C.ParentID from ExampleTable c inner join getallparent p on p.ParentID = c.ChildID ) select * from getallparent; Fiddle...
How to find all customers who didn't place an order?
SELECT c.CustomerId ,Name FROM dbo.Customers c LEFT JOIN dbo.Orders o ON o.CustomerId = c.CustomerId WHERE OrderId IS NULL ...
No column name was specified for column 1 of 'tbl'
Since you are using your subquery in JOIN, your column needs to have a name: SELECT rollno, classid, t_class.classname FROM t_class LEFT JOIN(SELECT Count(classname) as CountOfClasses, classname FROM t_class GROUP BY groupname HAVING Count(classname) > 1)tbl ON tbl.classname = t_class.classname But since you are not using this aggregate field in...
Default the year based on month value
SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...
SQL Multiple LIKE Statements
sql,sql-server,tsql,variables,like
WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...
SQL Server: checkident: “[S00014][2560] Parameter 3 is incorrect for this DBCC statement.”
https://msdn.microsoft.com/en-us/library/ms187745.aspx Turns out the ID field was a smallint which can only go to 32767. It was overflowing....
left join table, find both null and match value
Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...
how can I update a list of records in a table with the new value depending on the old value
you can use case statements in an update statement as so: update acc1152 set [columnName] = case [columnName] when '7007' then '4007' when '7008' then '4008' -- etc when '4512' then '4012' else [columnName] end Or a better way (IMO) would be to use a temp/variable table to keep track...
SQL Server - notify 1 month from time (SQL Agent)
Maybe you can create a simple HTML based on the items you need to return and then send the email Declare @html Varchar(max) SET @html = CAST(( SELECT ColName1 AS 'td','',ColName2 AS 'td','', ColName3 AS 'td' ,'' FROM Parts WHERE Convert(date,ExpirationDate) = DATEADD(month, +1, getdate()) //Convert to date since not...
pivot not working in SSIS
That statement is equivalent to: select listid, max(case when [order] = 1 then val end) as [1] from gmt_listsvals group by listid ...
Return 0 in sum when no values to sum - sql server
sql,sql-server,sql-server-2008-r2,sum
You need a table of all the statuses. If you don't already have a table, you can do this in the query itself: SELECT ClientDeliveryStatus, COUNT(t.ClientDeliveryStatus) AS Total FROM (SELECT 'Past Due' as cds UNION ALL SELECT 'Due Tomorrow' UNION ALL SELECT 'Due Today' UNION ALL SELECT 'Due Beyond' )...
More precision needed from SQL Server Money data type
sql,sql-server,asp-classic,sqldatatypes,money
you should define the type as decimal(25,10) which could hold the federal deficit up to an accuracy of 10 digits. (25 digits with ten after the decimal place).
Group Count in T/SQL
sql,sql-server,sql-server-2012
You can try qith cross apply: SELECT ..., ca.NoUniqueGRPed FROM #TempTab t1 CROSS APPLY(SELECT COUNT(DISTINCT GRP) AS NoUniqueGRPed FROM #TempTab t2 WHERE t1.Value = t2.Value)ca ...
The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument
sql,sql-server,sql-server-2008
You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...
ASP.NET EF6 - 2 Tables & 1 Link Table
c#,sql-server,asp.net-mvc,entity-framework
If you are looking to add an employee and their contact info on the same form, then you should use a View Model. Your View Model will be an amalgamation of the properties you need on both the Employee and Contact into one class. You will then implement your Employee...
How to insert excel formula to cell in Report Builder 3.0
sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
Excel Formulas support as ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services). No Formula Support in Excel In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel,...
Executing dynamically created SQL Query and storing the Query results as a temporary table
You have two solutions for this: As a first solution you can simply use an INSERT EXEC. This will work if you have a specified result set. This could be used if your procedure just returns one result set with a fixed result design. Simply create your temporary table with...
Problems With FOR XML AUTO
sql,asp.net,sql-server,subquery,sqlxml
Change XML PATH('') to XML PATH('tag')
Error connecting to MSSQL using PHP
php,sql-server,pdo,odbc,sqlsrv
Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....
How to incorporate wildcards with place holder variables in Java using SQL
In order to make this work, the wild card cannot be applied to the place holder variable. Instead it needs to be added around searchParameters.get("formation"). See example below. if (!Strings.isNullOrEmpty(searchParameters.get("formation"))) { nativeQueryFromAndWhereClause.append(" AND formation LIKE :formation "); parameters.put("formation", "%" + searchParameters.get("formation") + "%"); }"); ...
SQL Server: Issues with data type
Type of expression in SUM determines return type. Try the following: SELECT pa.[type], (SUM(CAST(pa.Actions_Logged as BIGINT ))/SUM(CAST(pi.Impressions_Served as BIGINT ))) AS ActionRates from Performance_Actions pa INNER JOIN Performance_Impressions pi ON pa.Alternative = Pi.Alternative GROUP BY pa.[type]; ...
Fastest way to add a grouping column which divides the result per 4 rows
sql,sql-server,tsql,sql-server-2012
Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...
Select @field From table as parameter
asp.net,sql-server,parameter-passing
If doing it from codebehind works then you can do something like sdsOrderErrors.SelectCommand = string.Format("SELECT {0} AS fld FROM [a_table]", colName); (OR) Have a stored procedure to accept a parameter and perform a dynamic query to achieve the same like create procedure usp_testSelect(@colname varchar(30)) as begin declare @sql varchar(200); set...
Implement reference key in SQL Server
It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...
One identifier for set of values
sql,sql-server,sql-server-2008,sql-server-2008-r2
You can solve this for example using the DENSE_RANK() function. See the example below: CREATE TABLE #yourTable(col1 int, col2 int) INSERT INTO #yourTable(col1,col2) VALUES(null, 72),(null, 72),(null, 72),(null, 33),(null, 33),(null, 12),(null, 12),(null, 55),(null, 72) SELECT * FROM #yourTable -- Your part: UPDATE yt SET col1 = numbering.number FROM #yourTable yt INNER...
Select count Columns group by No
this will work. you have to provide separate case statement to each condition SQLFIDDLE for the same SQLFIDDLE SELECT EMP_NO, sum(CASE WHEN Emp_Shift = 'AL' THEN 1 ELSE 0 END) AS COUNT_AL, sum(CASE WHEN Emp_Shift = 'S' THEN 1 ELSE 0 END) AS COUNT_S, sum(CASE WHEN Emp_Shift = 'H' THEN...
SQL Subquery column equals operation
Try this query select u.ID, u.Name, case when p.value>0 then 'True' else '' end as IsPermissionEnabled from Users u left join permission p on p.UserID = u.ID and p.key='CanEdit' ...
SQL Customized search with special characters
sql,sql-server,sql-server-2008
Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...
Looping distinct values from one table through another without a join
sql,sql-server,tsql,while-loop
So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...
Combine two subqueries to main query in SQL
The query below lists each resort and its type along with the number of accommodations and the lowest cost per night. select r.name, t.resort_type as type, count(a.accommodations_id) as accommodations, min(cost_per_night) as lowestcost from resort r inner join resort_type t on t.resort_type_id = r.resort_type_id left join accommodations a on a.resort_id =...
how to make xml values comma seperated using XPath, XQuery in Sql Server
SQL Server does not implement the xPath function string-join, so you would need to adopt a two step process, the first would be to extract all the terms to rows using nodes(); SELECT n.value('.', 'VARCHAR(100)') AS parsedString FROM #temp AS t CROSS APPLY t.Response.nodes('/error/description2') r (n); Which gives you your...
How to use OFFSET and Fetch without Order by in SQL Server
sql-server,sql-server-2012,sql-order-by,fetch,offset
By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...
Recursive Lag Column Calculation in SQL
Edit In hindsight, this problem is a running partitioned maximum over Column1 * 2. It can be done as simply as SELECT Id, Column1, Model, Product, MAX(Column1 * 2) OVER (Partition BY Model, Product Order BY ID ASC) AS Column2 FROM Table1; Fiddle Original Answer Here's a way to do...
Get unique row by single column where duplicates exist
SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...
LINQ to Entities Retrieving related Entities or T-SQL
you can try selecting from Vendors and using Any() for ShowId. Vendor[] vendorsInShow = (from v in db_.Vendor .Include("Products.Pics") .Where(m => m.Booths.Any(a => a.ShowID == showId) && m.Products.Count > 0) select v).AsNoTracking().ToArray(); ...
Title search in SQL With replacement of noice words [on hold]
sql,sql-server,sql-server-2008
I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...
Sort a LINQ with another LINQ in MVC
sql-server,linq,entity-framework,controller
Try this: var materialnumb = (from r in db.MaterialNumber where r.MaterialNumber == 80254842 select r.MaterialNumber).FirstOrDefault(); var query = from r in db.SQLViewFinalTable where r.MaterialNumber == materialnumb select r But I can not get whay are you filtering by 80254842 and selecting the same value? You can do directly: var query...
Sql injected code is inserted to my database . How to remove it
You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...
How do you configure SQL Session State in ASP.NET 5.0 (vNext) MVC 6? [closed]
sql-server,configuration,session-state,asp.net-5,asp.net-mvc-6
Support for SQL Server Session State is currently in development. So your best options for now are using In-Memory session(which has the issue which you mentioned about state not persisted for multiple servers behind load balancer kind of scenarios). However you could try using Redis cache(ASP.NET 5 Session implementation is...
Get work time in minutes based on shift schedule
sql,sql-server,sql-server-2008,ms-access,user-defined-functions
Whenever I have to calculate a field and use the results in a second field, I use a common table expression to make the first calculation. Given this query, it could look like this: with cte_preprocess as ( SELECT tbl_ShiftSched.Shift, tbl_ShiftSched.SortInd, [ShiftStart]+ case when @dteFTm>[shiftstart] then DateAdd(day,1,@dteShf) else @dteShf end...
How to Implement Dependent Dropdownlist in MVC4 Razor and using SQL server also
sql-server,asp.net-mvc-4,razor
Note : As per your requirement you need to show country name when user selects the state then why you need dropdownlist for country ?? it is better to use a label for that. For you requirement first you have to maintain a table which stores country and it's state...
Connecting to database using Windows Athentication
sql-server,vb.net,authentication,connection-string
You need to add Integrated Security=SSPI and remove username and password from the connection string. Dim ConnectionString As String = "Data Source=Server;Initial Catalog=m2mdata02;Integrated Security=SSPI;" ...
SQL Store queried data to one table (either temporary or not)
You can use INSERT followed by your SELECT if your table exists or you can use SELECT INTO in order to create the new table. see INSERT INTO tempTable SELECT c.[pf_id] ,a.[RequestDate] ,c.[pf_carrierUsed] ,b.[PiecePrice] * b.[PartQuantity] AS totalPrice ,c.[pf_type] ,c.[pf_resSupplier] ,c.[pf_resCustomer] ,c.[pf_trailerNum] ,b.[PartDesc] ,c.[pf_chargeBack] ,c.[pf_chargetoPlant] FROM [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a JOIN [CNCTC-WEB01].[NOP_PR].[dbo].[Parts]...
Using SqlQuery in Entity Framework
I was oblivious to the situation at first! Something like this should resolve your issue: var Parameter = new List<SqlParameter>(); Parameter.Add(new SqlParameter("@p1", 1)); Parameter.Add(new SqlParameter("@p2", 2)); Parameter.Add(new SqlParameter("@p3", 3)); context.Database.SqlQuery<myEntityType>("exec sp_Stored_Procedure @ParamOne = @p1, @ParamTwo = @p2, @ParamThree = @p3", Parameter.ToArray()).ToList<myEntityType>(); ...
SQL Recursive Query - Sum Rows with Lesser Rank
If i am understanding this correctly I am thinking you want this. Also this provides sample code, if the first assumptions are wrong please comment. create table #apples (product varchar(20) ,orders varchar(20) ,qty int ) insert into #apples values ('apple','john',1) ,('apple','john',1) ,('apple','john',1) ,('apple','john',1) ,('apple','john',1) ,('apple','josh',1) ,('apple','josh',1) ,('apple','jacob',1) ,('apple','jennifer',1) Go With...
Merging two tables into new table by ID and date
You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some...
want to generate coupon code 5 digit number [duplicate]
Records in a relational database tables are unordered by nature. therefor, you can simply create a table that has all the values between @First and @Last (0 and 9999 in your case), and then use a random order by when selecting from that table. you can also use a simple...
Concatenated string as parameter argument value does not give output in stored procedure
As Mark Sinkinson wrote in his comment, the IN() operator expects a comma separated list of arguments, but you have supplied it with a single argument that contains a comma separated list of values. Change this part of the stored procedure: and (recv_email in (@recvemail ) ) to this: and...
What is the execute command to use here
sql,sql-server,sql-server-2008,tsql
Have a look at this and tell me if this works for you - I've added a try catch to check if the command is timing out. Just few tips: I would always use a try catch because it'll tell you exactly what you need to know and why your...
Adding values of different sizes in SQL
The basic idea is to get the size by CASE like Size / CASE Unit WHEN 'KB' THEN 1000 * 1000 WHEN 'MB' THEN 1000 WHEN 'GB' THEN 1 WHEN 'TB' THEN 0.001 END Example for SQL Server: DECLARE @Table table ( Size decimal(18,3), Unit char(2) ) INSERT @Table VALUES...
SQL Server - Substitute for OR when JOINing on non-indexed field
sql-server,join,query-performance
You can try using LIKE WITH AgentRecordings AS ( SELECT a.name, r.recordingId AS rawrecordingid, r.filename, r.recordtime, CONCAT( 'C:\Recordings\' + a.name + '\' + CONVERT(NVARCHAR(4), DATEPART(yyyy, recordtime)) + '\' + CONVERT(NVARCHAR(2), DATEPART(m, recordtime)) + '\' + FILENAME, 'C:\' + SUBSTRING(a.name, 0, CHARINDEX(' ', a.name, 0)) + '-Recordings\' + CONVERT(NVARCHAR(4), DATEPART(yyyy, recordtime))...