I have a MS SQL table with a derived column, for date the records were imported, and need to delete records, based on the content of this column. What I need to do is delete all records from the table with a date of '2011-11-18'. Now this column is a datetime column, so it contains the time info after the date, i.e. 2011-11-18 09:29:38.000, but no matter what command I try for this:
-Delete from table where Date_Imported like '2011-11-18%' -Delete from table where Date_Imported like '2011-11-18' -Delete from table where Date_Imported = 2011-11-18
It comes back saying "0 rows affected", even though I know there are records with that date in the table.
I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1
We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID
My tables look like
Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int) Insert Into @tab1 values ('101','2015-07-01',0) Insert Into @tab1 values ('101','2015-07-02',1) Insert Into @tab1 values ('101','2015-07-03',1) Insert Into @tab1 values ('101','2015-07-01',1) Insert Into @tab1 values ('101','2015-06-30',1)
Insert Into @tab1 values ('102','2015-07-01',0) Insert Into @tab1 values ('102','2015-07-02',1) Insert Into @tab1 values ('102','2015-07-01',1)
select * from @tab1
1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only
2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column
3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag 1111296.54 3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id 100000001 200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag 1111296.54 3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id 100000001 (Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs) IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
I now would like to have a SQL query which gives me the number of unique object and reference combinations, like this:
a b c
d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
Using SQL 2000, how can you combine multiple records into 1?The source data is varchar(255), the destination will be text. I need helpwith the select statement.example tables:CREATE TABLE [NoteHeader] ([NoteID] [int],[CustomerID] [int] ,[Desc1] [varchar] (255),[Date] [datetime] ,)GOCREATE TABLE [NoteDetail] ([NoteId] [int],[SeqNum] [int] NOT NULL ,[Note1] [varchar] (255),[Note2] [varchar] (255),[Note3] [varchar] (255),[Note4] [varchar] (255),[Note5] [varchar] (255))GOSample script joining tables:SELECT *FROM NoteHeader INNER JOINNoteDetail ON NoteHeader.NoteID = NoteDetail.NoteIdSample results:NoteID CustomerID Desc1 DateNote1 Note2.....Note51111 987 Note Header Description 2007-07-15Notes detail record 1 field 1 Notes detail record 1 field2 .....1111 987 Note Header Description 2007-07-15Notes detail record 2 field 1 Notes detail record 2 field 2Desired results:NoteID CustomerID Desc1 DateCombinedNotes1111 987 Note Header Description 2007-07-15Notes detail record 1 field 1 +Notes detail record 1 field2 +Notes detail record 2 field 1 +Notes detail record 2 field 2 +through unlimited number of records up to 5fields eachThe NoteID field is the unique number. 1 record per NoteID in NoteHeader,NoteDetail can have unlimited number of same NoteID (usually not more than10)
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results. [wo_No] [prt_Mark] [dwg_Seq] [324037] [d400] [1S2] [324037] [d400] [A1] [324037] [d400] [1]
What I would like is to combine the dwg_Seq into one record where they have the same wo_No and prt_Mark. [324037] [d400] [1S2, A1, 1]
I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.
select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt, case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid, case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt FROM BI50_BankTransactions_AR_InvcDt_H where cus_no is not null group by cus_no, Inv_no
Could some one please help me with the following query.
I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results. wo_No | prt_Mark | dwg_Seq | 324037 | d400 | 1S2 | 324037 | d400 | A1 | 324037 | d400 | 1 | 219001 | 56 | 2B | 219001 | 56 | 2C | 219001 | 56 | 2C |
What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark. 324037 | d400 | 1S2,A1,1 | 219001 | 56 | 2B,2C |
I would also be happy with the following results get the Max unique dwg_Seq and count how many more unique dwg_Seq there are. 324037 | d400 | A1 + 2 | 219001 | 56 | 2B + 1 |
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
Result: combine tblShipmentHdr + attached tblShipmentDet records with tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005' AS SELECT SHPH.ProductID, SHPH.ReceivedDate, SHPH.ShipmentNo, SHPD.Vendor, SHPD.Quantity, QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ReceivedDate END, QShipQty = CASE WHEN SHPD.TransCd = 'F' THEN NULL WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ShippingQty END, PROD.ProductName, QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null THEN 0 ELSE MEND.OpeningAccrual END FROM dbo.tblShipmentHdr SHPH LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7) LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121) WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record, Table1 has the primary key id -- 1 2 3 4 Table2 has the follwing records id year subject ----------------- 1 2000 English 1 2002 French 2 2004 English 2 2005 English 2 2006 English 3 2007 French I want the result to be like this id 2000 2001 2002 2003 2004 2005 2006 2007 ----------------------------------------------------------- 1 English null French null null null null null 2 null null null null English English English null 3 null null null null null null null English
I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.
I have a table called Health that has a unique child record, key is childID.
I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.
So if the Concerns table has the following records for a child:
The final values I need to update the Health table are:
1, True, True, False.
And of course, my recordset of Concerns has records for many children.
O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.
I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.
So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?
Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?
Hope that makes sense, and thanks in advance for any help/suggestions.
I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.
My table looks like this: ID - DayNr - Transportation - TransOrder - Route 25 - 1 - Car - 1 - Text A 25 - 1 - Train - 1 - Text B 25 - 1 - Train - 2 - Text C 25 - 7 - Train - 1 - Text D 25 - 7 - Train - 2 - Text E
I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:
Column 1 - Column 2 25/1/Car - Text A 25/1/Train - TextB;TextC 25/7/Train - TextD;TextE
I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?
Thanks. RMG
P.S. ID is not my primary key and doesn't have to be unique
I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example: Multiple records with the same record ID:
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:
Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName
Create a derived column in the SSIS flow which does the same thing
Create the ProductShortName column as a computed column which uses substring on ProductName
Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated
Create a named calculation in the table in the Analysis Services project's data source view
Create a named query in the Analysis Services project's data source view
I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried? Which approach(es) do or would you use? Pros and cons?
Hi I want the sql query to display columns of different rows in a single row.The data base is given below. I want to make it in one row with all the details. Please help
1 | Rahul Bose | Male | #13, Bangalore, India, 686691 | Landphone-4452121252 1 | Rahul Bose | Male | #13, Bangalore, India, 686691 | Mobilephone2-558521252 1 | Rahul Bose | Male | #13, Bangalore, India, 686691 | Official_Email-TL@abc.com
The resulu should look like this
1 | Rahul Bose | Male #13, Bangalore, India, 686691 Landphone-4452121252 Mobilephone2-558521252 Official_Email-TL@abc.com
Hi Folks, Im new to SQL, and I am trying to do the following:
I have a table Documents with DocID, Path and FileName. A second table Keywords has KwdID, KeywordString A third table DocumentKeywords links the two with DocID,KwdID. Multiple keywords are linked to one document.
I want to create a SELECT query that makes a result table that contains Path, FileName and Keywords columns where the Keywords column contains entries like "Keyword1,Keyword2,Keyword3" ie. a comma delimited list of keyword strings which have been built from the keywords that associate with a specific document.
I found a nice sample here http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string which shows how to return just the comma delimited string itself:
DECLARE @List varchar(100)
SELECT @List = COALESCE(@List + ', ', '') + Keywords.KeywordString FROM DocumentKeywords WHERE KwdID = 1
SELECT @List
I cannot seem to integrate this into the query so that it calculates the string for each row on the fly. My suspicion is that the capability is there. Can somebody point me in the right direction?
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?
With Item as( Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as ( Select 1 as Parent, 2 as child union all Select 1 as Parent, 3 as child union all Select 1 as Parent, 4 as child union all Select 5 as Parent, 6 as child union all Select 5 as Parent, 7 as child )Expected Result:
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames 887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID, dbo.tblAttributes.AttributeName FROM dbo.tblProgramAttributes INNER JOIN dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%') ORDER BY dbo.tblProgramAttributes.ProgramID DESC
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
I need to generate weekly report for data usage for my c drive and d drive.the graph should be likefor a particuluar date, I want to display graph like this. even if I change secondary axis, my c: drive used and free space not coming in single column. it comes like c: drive used space, on top of it d: drive used space and in adjacent column c: drive free space, on top of it d: drive free space.I don't want to add two category one for date and another for driver names.
Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
