Tag Info

New answers tagged

1

Here is a working example. There is a very common function that gets used in SQL databases called fnSplit. It's not native, but I have seen it added to databases many times. It's a table-valued function that takes a string and returns a table (splitting the string on some delimiter). The code for the function is below: CREATE FUNCTION [dbo].[fnSplit]( ...


3

I use charIndex and patIndex to resolve it. CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0 AND LEN(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1))>=3 THEN RIGHT(SUBSTRING(Descr,1,CharIndex(' Stage', Descr)-1),3) ELSE NULL END AS NewNo, CASE WHEN PatIndex('%, [0-9][0-9]% Stage%',Descr) > 0 AND ...


0

The question is really unclear. It's not obvious the database structure that you are working with. Your problem looks as though it would be solved with a self-join but am only guessing. Here's one possible (much simplified to show general principles) solution. In sqlite as I don't have SQL Server. CREATE TABLE `timesheet` ( `user_id` INTEGER PRIMARY ...


3

OLEDB via ADO has always had the issue where DONE_IN_PROC messages (rows affected) confuse code that is not expecting them. A workaround without app code changes is to specify SET NOCOUNT ON as the first statement in the batch: SET NOCOUNT ON; DECLARE @s varchar(1000), @ver int; SET @ver = 10; SELECT @s = case WHEN @ver > 9 THEN 'BACKUP DATABASE [DB] TO ...


0

I have assumed the ID field that would be used to join the two tables. Let me know what field you would use to join these tables and I will update the answer. You should also specify the correct alias in your @columns variable. I also assumed the UserRegistration table schema to be dbo. SET @sqlText = N' SELECT ...


0

Depending what the colum is for your userID (i've declared it as TeamMemberUserID which you are using later) Simply Join the timesheet table to the User Registration table on the TeamMemberUserID and select the user.name field End result looks something like: SET @sqlText = N'SELECT user.name as [Name], isnull(SUM(isnull(['+REPLACE(@columns,',','],0)), ...


4

First, create a temporary table (not a table variable) to hold the results: SET NOCOUNT ON; GO CREATE TABLE #results ( db SYSNAME, [schema] SYSNAME, [table] SYSNAME, [column] SYSNAME, ColumnValue NVARCHAR(3640) ); GO Now, the following gets a little convoluted, because this is like peeling an onion. We need to build dynamic SQL commands that ...


1

To avoid the extra DateAdd (may be difficult if time zone is a variable), use: TODATETIMEOFFSET(@datetimeoffset, @timeZone)


3

OK, replacing the previous answer with this one. USE OF ms_foreach stored procedures is not recommended. This is a little more code but it's more reliable. You can limit the databases you search through, it checks to make sure the transaction table exists, and still provides the database name in the results. Note - this will provide a separate result set ...


0

This solution uses two CTEs to identify your open and close records and then joins the two datasets together. Enjoy --Build the test tables (using BIT instead of OPEN / CLOSE IF OBJECT_ID('JobHistory','U') IS NULL BEGIN CREATE TABLE JobHistory ( jobOrderId INT , dateAdded DATETIME , ...


5

Given the sample data, here is my first try: SELECT [Route], [Sequence] = ROW_NUMBER() OVER (PARTITION BY [Route] ORDER BY [Planned Time]), [Planned Time], [Business Date] = MIN(CONVERT(DATE, [Planned Time])) OVER (PARTITION BY [Route]) FROM dbo.table_name ORDER BY [Route], [Sequence]; I am sure there are edge cases you haven't ...


0

If you are OK with using a slightly different approach (still T-SQL) go to this link and scroll down to the answer by Neeraj Prasad Sharma where he creates the stored procedure named INS. Within that procedure you can add a where clause of something like table where id <> 1. Here is some example code. First create your table and test records. CREATE ...


0

Providing that you'll always close a job before opening another under the same ID (although personally you should never have two seperate jobs running under the same ID without another unique identifier to distinguish (You've opened 4909 and 4910 twice)) if you join the table on itself distinguishing them between Open and Closed you can get it all in one ...


1

Is this approach correct? Yes. It meets all the objectives stated in the question. A comment in the procedures to explain the strategy and note the related procedure name might be helpful for future maintenance by others. Is there a better way? Not to my mind, no. Taking a single lock is an extremely fast operation, and results in very clear ...


1

This scenario seems very similar to the following Question: Strategies for "checking out" records for processing In my answer there, I advocated a model similar to what you have here, but with the notion of including sp_applock as a fail-safe only if the initial concept wasn't bullet-proof. The main difference in the "check out" process was that ...


0

create table #jobhistory (JobOrderID int, dateAdded datetime, Status varchar(10)) go insert into #jobHistory (JobOrderID, dateAdded, Status) select 4909, '2015-08-26 18:15:07.527', 'OPEN' union all select 4909, '2015-08-28 13:35:38.997', 'CLOSE' union all select 4909, '2015-08-31 12:16:29.787', 'OPEN' union all select 4910, '2015-08-27 12:16:42.72 ', 'OPEN' ...


1

You can't on a host with only 1 node. Your query on sys.dm_os_memory_clerks shows you only have 1 physical node (0). The other node (64) is a logical node for Dedicated Admin Access (DAC). In older versions of SQL Server that were NUMA supported instead of NUMA aware/optimized, it is possible to tank performance when running on large NUMA hosts. Consider ...


1

SELECT [ID Code], statusCode FROM dbo.Table AS t WHERE NOT EXISTS ( SELECT 1 FROM dbo.Table WHERE [ID Code] = t.[ID Code] AND statusCode = 'A' ) GROUP BY [ID Code], statusCode;


1

The only way I can think of doing this is to slightly hack the process. It would involve the following steps: Copy the Distribution agent job and rename it to indicate it is run manually. You'll also need to change/disable the schedule and remove the "-Continuous" flag from the exec command of this new job. Create a job that does the following: Step 1: ...


3

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1 Like this: WITH multi_design_perfs AS (SELECT b.perf_id FROM perf_ticket_type b GROUP BY b.perf_id HAVING COUNT(DISTINCT b.design) > 1 ) SELECT m.perf_id, STUFF((select ', ' + CAST(b.design AS varchar(10)) FROM perf_ticket_type b WHERE b.perf_id = m.perf_id ORDER ...


2

For these kind of reports I prefer to use a table of numbers or calendar table. Table of numbers is simply a table that has one column with integer numbers from 1 to some large enough number, say, 100,000. I personally use table with 100K numbers. Aaron Bertrand wrote a good article explaining how to generate such table. Calendar table is simply a table that ...


2

Move the condition from the WHERE clause to a conditional count: SELECT COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END), CD_Carrier, DATEPART(DAY, DT_Arriving) as [Date] FROM TB_AGIL WHERE DT_Arriving >= @date AND DT_Arriving < DATEADD(MONTH,+1,@date) GROUP BY CD_Carrier, DATEPART(DAY, DT_Arriving) ORDER ...


1

If the dates aren't in your resultset then you need to get them from somewhere right? You could create a join with a derived table full of dates.


2

The list of events that can be used for DDL Triggers can be found on the following MSDN page: DDL Event Groups. If you look through that list, you will notice that they do not offer a level of granularity below the base CREATE / ALTER / DROP {ObjectType} ... So trapping ALTER_TABLE will get all ALTER TABLE... statements. Once a DDL Trigger is fired, you ...


2

One method is with a common table expression like the example below. Note that the preceding statement must be terminated with a semicolon. WITH cte AS ( SELECT a.database_name , a.collection_time , ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET), ...


2

You should create a derived table or a CTE , to be able to access the ROW_NUMBER() SELECT a.database_name , a.collection_time , a.row_ID FROM ( SELECT a.database_name , a.collection_time , ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET), ...


1

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task: https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/ The Author created the following Script using the LAG Window function: SELECT DISTINCT A.[database_name] , AVG( ...


1

I might be misunderstanding the question, but assuming that I do understand it, it seems that you could fairly easily take the where clause from the first where digitalfiles.path in ( select * from Staple ) And make it a part of the second query. There are a number of ways you could do this (CTE, derived table, etc...) but it ...


0

You should use the original column name in your WHERE clause. woitempart is just an alias you gave to part.num in the select statement. As you can see in this great article by Pinal Dave, you don't have access to a SELECT alias in the WHERE clause because the alias doesn't exist yet since the WHERE clause is processed before the SELECT clause. This is ...


1

Put apostrophes around the string, and use the proper column name: Where part.num = 'A00089'


6

The following assumptions have been made (some of them possibly repeating parts of your description): SF/FLEX is always followed either by a number immediately or by a space character and then a number. There is always one space character before SF/FLEX, unless the item is at the beginning of the string. There is always a comma or a space after the number ...


2

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets. CREATE TABLE dbo.Hardware -- hw? ( [Event_Num] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(100) NOT NULL, [Install_Date] DATE NULL, -- Install after pulling? ...


0

Under ordinary circumstances, filter conditions can be specified either in WHERE or JOIN clauses. I tend to place filters under WHERE unless OUTER JOIN precedence could be affected (see below) or if the filter is very specific to that table (e.g. TYPE=12 to specify a specific subset of rows in the table). On the other hand both ON and WHERE clauses can be ...


1

I agree with NReiligh but if you still want to extract values use combination of CHARINDEX and SUBSTRING functions of SQL SERVER SELECT [PEvent] ,[PDate] , [PDescription] ,CASE ------------------------------------------For SF Values WHEN [PDescription] LIKE '%SF%' THEN CASE substring([PDescription], ...


0

I would go for a 2 step solution. I use simple SQL. You can use the right names and fields. First I would update the existing rows: update tab1 set tab1.field = tab1.field + tmp1.field from table1 tab1 inner join temp1 tmp1 on tab1.key=tmp1.key; Secondly I would insert all non-existing rows: insert into table1 (key, field) select key, field from temp1 ...


1

I think this is a situation where you just need a cross apply. If you are not familiar with it, look at this link. With your data I created a table and inserted the data with this scrip CREATE TABLE school ( schoolcode VARCHAR(20) ,SchoolYear INT ,Semester INT ,Term INT ,TermEnd DATETIME2 ); INSERT INTO dbo.school ( ...


2

Define the outer join as a separate, non-recursive, CTE: WITH FactorColumns AS ( SELECT a.FactorTitle, a.FactorCode, a.parentId, c.FactorColumnCode, c.FactorColumnTitle, c.FactorColumnValue, c.isFactorValue, c.FieldType FROM FactorSetup a LEFT JOIN FactorColumnSetup c ON ...


2

Another option, at least just to have it stated, is to use BCP Format Files. Format Files allow for specify column properties, including the delimiter. Delimiters are typically just whatever character, but you can add double-quotes into the appropriate field delimiters to produce text-qualified values. You can do a simple test by having BCP generate a ...


2

I realize this has been answered before on Stack Overflow. It got me going in the right direction. I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering. For anyone in the future who has a similar issue: My ...


2

There should be no real performance problems related to using the system objects to build your T-SQL. Consider also that building your dynamic T-SQL is probably a much lighter load on your server than actually running the T-SQL statements that you generate. There are also INFORMATION_SCHEMA views, but these only partially cover the system objects. I ...


5

Now that I better understand that you are trying to extract data, here is a new answer. This just simply extracts data from a table that I created in tempdb. use tempdb -- drop table mytable create table mytable (id int, customer_name varchar(55), cityname varchar(55), statename varchar(55)) insert into mytable values (1, 'a', 'a city', 'a state') ...


0

Sorry I don't have enough rep on this site to post a comment @Edward Dortland: The link is from NancySon and I would also recommend posting the context of the article or the answer in case things get broken and lost: Maintenance Plans Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename ...


2

I was wondering whether you want to group by well_name for all different pull date or not. Right now, a well with X dates gets X rows. If you want the X date/rows to become 1, I will update the queries. Query 1 uses left join Query 2 uses pivot SQL Fiddle (Left Join): ;with data as( Select Pull_Date, Well_Name, Part, PartPN, Part_SN , id = ...


2

This should work. I took the original CTE, partitioned by part and date and numbered those. This gave every part on each date it's own number starting with 1. Then in the case statement, adding WHERE Number = N allows it so that each part is unique. Finally, the column names were modified to have their respective numbers. Since this is unwieldy, ...


3

Once again, thank you to Vladimir for providing the formula! The correct way to accomplish what I need is this: DECLARE @step_size INT = 7, @max_iteration_range INT = 40, @max_iterations INT = 2 UPDATE #mytable SET iteration = ((id*@step_size)/@max_iteration_range) % (@max_iterations+1), step = ...


0

@PaulBarbin's answer is great if you know that you will always need to output three columns of data. If you need to dynamically output a different number of columns based on the data being selected then you will have to adapt his solution using dynamic sql. Effectively you are trying to get SqlServer to output data contrary to what its natural inclination ...


3

You could try Pivot: I think you'll need to order on some key that ties these records together (how do you know that the starttime of "TESR" is related to the EndTime of "TESR". That probably needs to be in the ORDER BY clause below. SELECT Title ,[1] ,[2] ,[3] FROM ( SELECT Title, Value, ...


6

It seems you don't really need the "frequency" for the sorting, only the "maximum frequency" per customer. You can use the COUNT(*) OVER (PARTITION BY customerID): SELECT customerID, eventID, ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY eventID) AS frequency FROM customerEvent ORDER BY COUNT(*) OVER (PARTITION BY customerID) ...


2

I think SSIS is really what you want to use here. To just do a very basic import of Excel into SQL Server this is a great link to use - Simple Talk 10 Simple Steps From there, you will then need to use a combination of Conditional Splits as well as MultiCast in order to move the data to different fields/locations depending on your various rules. ...


0

To me 30 queries does not sound like to much work in order to obtain the level of performance increase you will see having SQL Server do the work instead of Access. Especially if the data is going to expand to more users and is a more mission critical type thing. Something you might consider is running your Access database through the SQL Server Migration ...



Top 50 recent answers are included