SQL Server 2012 :: Finding Breaks In Key Values?
Jun 12, 2014
The following is sample data I am dealing with.
SELECT * INTO TEMP
FROM
(SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X
I need to extract the date that the value in CODE column changes to another code for each value of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.
View 3 Replies
ADVERTISEMENT
Aug 26, 2014
I'm trying to come up with a query for this data:
CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
[Code] ....
Here are the expected results:
OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010
[Code] ....
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 1
ELSE 0
END AS isFirstVisit,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 0
ELSE 1
END AS isRepeatVisit
FROM#Visits
View 3 Replies
View Related
Dec 13, 2007
I can't figure out how to get my line chart to break when there isn't a value. For example, I have a trend line over 4 time periods. The 3rd time period is missing a value. Instead of the line ending at the 2nd period and picking up again at the 4th time period, it's connecting the line 2nd to the 4th period. I'd like it to break and for there to be no line appearing in the 3rd period. I bet that's as clear as mud, but let me know if you have any questions.
Thanks!
sash
View 1 Replies
View Related
Dec 27, 2013
I need to do some clean up activities in my databases... So i intended to drop unwanted tables and views.
for that I need to find, whether the table being used by any other objects ?
How could I achieve it?
View 2 Replies
View Related
Feb 21, 2014
I need to be able to identify breaks in a sequence so I can evaluate the data more correctly. In the sample I have given I need to be able to identify the break in sequence at 69397576, ideally I would set that as a D. My query also needs to recognize that the 3 sequences following 69397576 are sequential and would belong to that set. so the out come would look like this.
id file_name page_follow
693975631555557564_22222221114014810D
693975641555557564_22222221114014810F
693975651555557564_22222221114014810F
693975661555557564_22222221114014810F
693975671555557564_22222221114014810F
693975681555557564_22222221114014810F
693975691555557564_22222221114014810F
693975761555557564_22222221114014810D
693975771555557564_22222221114014810F
693975781555557564_22222221114014810F
693975791555557564_22222221114014810F
here is some test data.
create table test1 (id INT
, [file_name] VARCHAR(100)
, page_follow CHAR(1));
go
[code]....
View 9 Replies
View Related
Mar 3, 2014
How do you find stale stored procedures ?
In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.
"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.
Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.
So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?
And could this be done in SS2K8 ?
View 5 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Feb 18, 2014
I am trying to build a select statement that will
1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.
I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.
Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.
Below you will find a temp table to create and try in your environment.
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int
[Code] ....
Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.
View 3 Replies
View Related
Oct 22, 2014
know a way to find all stored procedures that use declared or temp tables, i.e
Declare @temptable TABLE as....
Create table #temptable
View 8 Replies
View Related
Mar 11, 2015
I've got a fairly large hierarchy table and I'm trying to put together a query to find the lowest level descendants of the hierarchy. I think there must be some way to use the "Breadth-first" approach that's stated in the MSDN technet sites about SQL Server HierarchyID but i'm not sure how to write the necessary T-SQL to traverse that. I know I can get all the descendants of a parent node like this
SELECT *
FROM AdventureWorks2012.HumanResources.Employee
WHERE OrganizationNode.IsDescendantOf(@ParentNode) = 1
However, this query returns all levels for that parent's branch. If I just wanted list of employees that were at the lowest level of the branch(es) for this parent node, how would I do this?
View 1 Replies
View Related
Dec 17, 2013
We have set up SQL server 2012 alwaysOn availability group on windows 2012. It runs and fails over successfully. Recently, a VMWare creates a snapshort of a primary and it breaks a cluster. We saw errors in a cluster log.
In VMware setting, The quiesce option is turned off for these VMs. Also, we configured cluster setting :Windowssystem32> (get-cluster).SamesubnetThreshold = 10 ( Relaxed )
Cluster node 'Test1' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or
File share witness resource 'File Share Witness' failed to arbitrate for the file share 'TestTEstQuorum'. ensure that file share 'TestTestQuorum' exists and is accessible by the cluster.In alwaysOn error log, A connection timeout has occurred on a previously established connection to availability replica 'Test1' with id [CAD40D99-E333-457E-9993-BBE977D2CDA2]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
View 9 Replies
View Related
Feb 21, 2014
I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
View 2 Replies
View Related
Nov 3, 2014
There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.
How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.
SELECT
[groom_first_name]
,[groom_last_name]
,[bride_first_name]
,[bride_last_name]
[code]....
View 5 Replies
View Related
Mar 22, 2006
Hi
I am new to SQL. My scenario is I get a string from the user I need to match this string with value present in 2 cloumns. If the string is present in either of the column then i should select that particular record from the table.
Now my question is if i Have to match with one cloumn i can used charindex function is there any function with which i can match the string with data present in more than one cloumn.
Thanks Much
View 2 Replies
View Related
Apr 10, 2007
I want to write a query to bring out any records in a table
where any of its fields contain a null value. I could do it in this way:
select *
from
customers
where name is
null
or role
is null
or email
is null
u003c/p>u003cp styleu003d"margin:0cm 0cm 0pt">u003cfont faceu003d"Times New Roman" sizeu003d"3">u003cfont coloru003d"#000000">However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance.u003cspan>Â u003c/span>I was hoping there was a simple way to do the same thingu003cspan> ie find any rows with a blank fieldu003c/span>?u003cspan>u003cfont faceu003d"Arial" sizeu003d"2">Â u003c/font>u003c/span>u003c/font>u003c/font>u003c/p>u003c/span>u003c/font>u003c/div>",0]
);
D(["ce"]);
//-->
However I want to perform a similar function for a
number of tables and the method above would mean more sql and maintenance. I was hoping there was a simple way to
do the same thing ie find any rows with a blank
field?
View 4 Replies
View Related
May 24, 2008
Hello,
I have a web form with a check box list with 5 values. Each of them is an int value, and the user can select multiple values from the check box list. For example:
Status:
1) Single
2) Married
3) In Relationship
4) Divorced
5) Any
These values correspond to a column called "RelationshipStatus" which holds an int value of NULL or 0-5.
So the value passed to my SQL query would be any combination of 1, 2, 3, 4, or 5 (Which denote any of the above). It could look like this:
@Status = '132'
Now my question is how would I do a select statement that finds any row with one of those value (Any row with 1, or 3, or 2)? If there is a '5' in the varible then the select statement should return any row regardless of the value.
Table Name: "USER_TABLE"
Column Name: "Relationship_Status"
Value Type: INT
Thank you experts!
View 1 Replies
View Related
Oct 2, 2015
I have a query I am currently attempting to optimise. Â The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. Â For example:
SELECT Column1
,Column2
,DateColumn
FROM Table1 T
INNER JOIN
(
SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt
FROM Table1
GROUP BY Column1 + Column2
) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
View 19 Replies
View Related
Jul 17, 2007
Hi Everyone,
This is the table I have:
Sections Class1 Class2 Class3 Class4
--------------------------------------------------------------------
A 30 20 30 40
B 40 15 10 10
C 20 5 10 5
Total 90 40 50 55
There are around 6 more classes which I haven't mentioned. The resultant table that I need would be something like this.
Sections Class1 Class4 Others
-------------------------------------------------
A 30 40 50
B 40 10 25
C 20 5 15
Total 90 55 90
What I want is the top two maximum values to appear and everything else to be clubbed into Others and correspondingly their numbers to be summed up i.e. Dynamically depending on the total only those classes with maximum 'Total' value to appear.
View 9 Replies
View Related
Mar 29, 2013
how to find all possible combinations of values, for example:
My table includes:
Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);
I need the output to be like this:
apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry
I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.
View 5 Replies
View Related
May 23, 2007
Hi all...I've got a group of tables and I wanted to first see which tablescontained a fields containing the "Date/Time" data type. And I wassuccessful in doing so...here's the query:(select a.name tablename, b.name colnamefrom sysobjects a, syscolumns bwhere a.name = object_name(b.id)AND B.TYPE = 61 AND A.XTYPE = 'U')Now...my only issue is find which query I can run (On each table)which returns to me ONLY the values containing the date/time stampwith milli seconds (Ex: 01/01/2007 10:10:50:987)I require this since we are planning on shifting those tables to theOracle platform, and we've had issues doing this while loading theextracted information, since it was not taking the values containingthe "milliseconds".Is there a way to do this? Or is it better to simply remove themilliseconds all togeather? I wouldn't mind doing so provided that itdoesn't includes changes to the actual data type.
View 1 Replies
View Related
Mar 13, 2008
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b
task_c
task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus:
queue_num task_num team
queue_1 task_a red
task_b red
task_c red
task_d red
No bonus for any team here
queue_num task_num team
queue_1 task_a red
task_b blue
task_c red
task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
View 4 Replies
View Related
Jan 28, 2008
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
View 4 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Jun 8, 2005
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns.
Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values.
Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values.
I would appreciate If anybody gives me hint about achieving desired result
View 2 Replies
View Related
Apr 15, 2015
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB]
GO
/****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 2 Replies
View Related
Jul 23, 2005
Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name
View 2 Replies
View Related
Oct 28, 2014
I need to generate a report from a table using the values from a filed to determine what to add up.
create table fun_test(
packtype nvarchar(50),
price money)
insert into fun_test
values ('Single',''),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single',''),('Deposit',''),('Deposit','')
[code]....
order by PackTypeWhat I need is if the PackType is Deposit and need to multiply the number of records by 35, If the PackType is Monthly I need to multiply the records by 25 and if the PackType is Single I need to sum the values in the price column.
View 4 Replies
View Related
Mar 17, 2014
I try to do all insert-actions in one SP. But it doesn't work. I couldn't insert any values into the DB. Is this possible or the wrong way?
use env
go
create proc [SP$insert](
@p1 nvarchar(100),
@p2 nvarchar(100),
@id int output,
@debug bit = 0
[Code] ....
View 9 Replies
View Related
Jun 17, 2015
While running SQL Server Profiler I reached some Lock:Escalations. When I searched for Statements having same SPID as Lock:Escalation event I realized that one of delete statements causes this. Is there any way to find out why lock escalation in such place occurres?
Statement is like:
delete from BOOK_IN_LIBRARY where libraryId in (,,,,); <-20 elements ids
CREATE TABLE BOOK_IN_LIBRARY(
[libraryId] [bigint] NOT NULL,
[bookId] [bigint] NULL,
[otherData] [bigint]NULL,
[otherData2] [int] NULL,
[Code] ....
View 0 Replies
View Related
Nov 21, 2013
I have the following code and trouble reading values of Bank Accounts. If i remove the line it says "xmlns="http://applications.apch1.com/webservice/schema/" then i my query is working. But i cant remove this becasue that is what i will get response from a web service. All the records are stored in the database with this line included.
DECLARE @MyXML XML
SET @MyXML = '<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<CustomerID> A8339 </CustomerID>
<BankAccounts>
[Code] .....
View 2 Replies
View Related
Jan 18, 2014
I have an existing function and need to alter function to give result of the values multipiled until its parent is reached.need two seperate functions for city and amt columns..need to also display the parent-description
--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]
[code]...
View 8 Replies
View Related
Jan 31, 2014
I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.
Here's some test data and code which shows the principle:
CREATE TABLE #t
(
ref INT ,
id INT ,
who VARCHAR(10) ,
checkin DATE
[Code] ....
The result set is:
ref 1 who1 2 who2 3 who3 4 who4
123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol
456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL
Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?
View 4 Replies
View Related
Feb 10, 2014
I have two tables, a dates table and a values table. They are joined on the date column.The date table has a range, say from today as far as 20 days from now, incrementing by 1 day each row.The values table may have a row for a day, and may not. If the day has a value I want to display that value.If the day does not have a value in the values table I want to display the last known value.
I think this can be done with windowing functions in a set based manner but have not been able to work it out. I have done it procedurally but im not happy with that at all, and really want to see if this is possible in a set based manner.Below is some simplified code to allow testing with sample data.
create table DimDate
(
DateCol date
)
create table TotalsData
(
DateCol date
[code]....
View 6 Replies
View Related
