Please find the necessary SQL to create the table with sample data down below.In this simple table, I'm keeping project status logs i.e. what project, what happened and when.I want to create a select statement that will give me the ProjectId and the time stamps from "Contract Signed" to "First Meeting". So, I'd like my data to look like this:
Please notice that in my SELECT statement, I don't want to see anything else. For example, I don't want ProjectId: 555 included because it never made it to "First Meeting". I also don't want to see any information about "Press Release" for ProjectId: 123. I only want to see those projects that went from "Contract Signed" to "First Meeting".
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProjectStatus](
[LogId] [int] NOT NULL,
I have a database that has entries that I want sorted by date order. Each entry has an auto ID number allocated (primary key auto sequencing), which I want to change to reflect the sorting (so the first date has the first auto ID number and so on).I've gone into the database and sorted the entries as I want them. Then I've gone into Design View to delete and restablish the primary key autosequence. However, it is not keeping the date order in the database (ie entry ID 3140 date is 12/06/2015, but 3141 is 02/02/2012). How do I get it to maintain the order?
I need a simple tSQL code that will produce an array sequence values that has gap with 2 or more consecutive values, which can vary - so 2 or 3 or 4 between sequence of number. For example, I want it to return a series with a gap or space of 2- So it should return 1, 2, 5, 6, 9, 10.. or a gap of 3 - 1,2 ,6,7,11,12.. etc I know a single gap is quite simple using a recursive cte, but I haven't been able to find how to get this kind of result.
I am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events).I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC SET LKC.combo = lockCombo2 FROM [LockerPopulation] A JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
I'm new to sql server 2005. Previously I used Access. I've noticed that in sql server when a record is inserted into the database table, the data does not get entered sequentially. I looked at the id column and saw that the order numbers are mixed up. Is this something that is common in sql server, and how can I change this. I need the data to be ordered in the order that it was inserted into the database.
I have one Data flow, which trasfer data into two table (Parent & Child) .
My question is : Is there a way, i can load data first into parent and then child table. because child table getting load first after that parent table loading. (Execution should be Source Parent --> Destination Parent) First , (Source Child --> Destination Child) Second. In my case its executing reverse. So i have foreign key constraints at child table , its giving foreign contraints error while running ssis package
Can any one tell me, How to define my own sequence execution at the Data flow task (Source - Destination) ?
I am creating a staging database in which I am loading required tables from 2 different sources. I have 30 different tables to load from source 1 and 10 different tables from source 2. This is the way I am doing, in Control flow task I am using Sequence container and in that I included the data flow task, the data flow task has source OLD DB connection from where I select the table and then destination OLE DB connection where I load the data. So for 30 tables I have one Sequence container with 30 different data flow task and each data flow task has OLE DB source and OLD DB destination. I wanted to find out if this is the efficient way to do, or if there is any other way to do this. And for source 2 shall I put in another package or shall I use the same package with different sequence container and follow the same steps as for Source 1 tables. Please advice. Thanks,
I have a question in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
I would like to know different possible ways in appending extra values like new uniqueidentifiers, sequence numbers, random number. Can you please tell what type of data flow components helps us ?
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to 255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue.
INSERT into tmp_Test SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:Book1.xlsx', [Sheet1$])
I need to create SQL to convert multiple rows data to single row for given subscriber#. Below is the example. In below example , I've 4 family members with same subscriber # and each members have separate rows, I want to combine member data for same subscriber in 1 row, so there would be a 1 row for each subscriber.
select distinct bl_rec_ref ,freq_cd from hgmprty1 where(bl_rec_ref is not null and bl_rec_ref not like '%SCH%')
That produces the following output (part shown)
However what I need to show is only bl_rec_ref values where there is no corresponding freq_cd of QTLY.
Looking at the example data above Rows 45 and 46 have the same bl_rec_ref value with a MTHLY and a QTLY value in freq_cd so therefore I don't want this to appear in my output. Similarly with Rows 47 and 48 were the bl_rec_ref has both QTLY and MTHLY values so again should be excluded.
However looking at Rows 51 to 55 which has a bl_rec_ref only showing once for each value of MTHLY and no value for QTLY so these Rows are required in the output.
drop table #temp create table #temp (id int, dat datetime) insert into #temp values(1,'2015-03-01') insert into #temp values (2,'2015-02-01') insert into #temp values (2,'2015-01-01') insert into #temp values (3,'2015-01-01') insert into #temp values (3,'2015-03-01') insert into #temp values (3,'2014-12-01') insert into #temp values (2,'2014-12-01') insert into #temp values (1,'2014-12-01') select * from #temp
The result i am expecting is 2014-12-01. I need to get the max(dat) for data available for all ids.In the sample set, only for 2014-12-01, there is a row for id 1,2,3. I need to get that date as result.
I have a date into format YYYYMM. Data type is int. I need to make query where every time it will return me last 3 months, but without current one. For example I have data for months below
201510 201509 201508 201507
Query should return all records for 201509, 201508, 201507.
I was trying lot of solutions founded in internet like this one:
We are running the following querry which insert data row from an XML source
INSERT INTO End_ReelProcessValue (LineId ,ReelHist_id ,Variable_tagName ,ProcessMeasure )
[Code] ....
From that querry, I need to store in a variable the Value of a particular XML entry in order to store it in an other table.The XML parsing is based on the following lines :
What I need is that if the [Variable Tagname] issue from entry @Name is equal to the string "Quality", then I need to store its value to a variable.Then an other querry will use that variable to insert it to an other table.
Howto write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i don't want. everyday i need to search the data from yesterday whole day.
I found string from net how to split column data to row
SELECT A.JbIDFull, A.ProdID, A.OrderQty, A.OtherDetails, A.OrderDate, c.Item FROM (SELECT JbIDFull, DeptID, ProdID, OrderQty, OtherDetails, OrderDate, LamORSteachType, JBID, RMIDs, RMQty FROM tblJobCardforProduction WHERE JBID = '2' AND DeptID = '3') A CROSS APPLY dbo.SplitStringNEW(a.RMIDs,';') b CROSS APPLY dbo.SplitStringNEW(a.RMQty,'/') c
When I apply one cross apply it's working fine but when i apply for one more column is replacing three time one row, this data i want to split RMIDs and RMQty with same JbIDFull
