[T-SQL] return results where value exists in another table |
- return results where value exists in another table
- Problem with update syntax
- Finding gaps within date ranges
- use results of view in where statement
- Extract string between to characters
| return results where value exists in another table Posted: 13 Sep 2013 10:54 PM PDT Hi, I would like to get results from the two tables below where the ITM$Con_Note value is in both tables.I would also like to calculate the WeightDif field which is the difference between the two weights.Also in reality these tables are identical tables in separate identical databases on two PC's using SQL Express 2008 R2Thanks,DavidITM$Con_Note ITM$Machine ITM$Date_Time ITM$Weight ITM$Machine ITM$Date_Time ITM$Weight WeightDifABC456 ADL01 2013-09-14 20:52:39.087 1.200 ADL02 2013-09-14 20:52:39.090 1.500 0.31234567890 ADL01 2013-09-14 20:52:39.087 35.6 ADL02 2013-09-14 20:52:39.090 35.75 0.25drop table #item1drop table #itemCREATE TABLE #Item( [ITM$Con_Note] [varchar](50) NULL, [ITM$Machine] [varchar](10) NULL, [ITM$Date_Time] [datetime] NULL, [ITM$Weight] [numeric](18, 3) NULL, )Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC123','ADL01',getdate(),10.5)Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC456','ADL01',getdate(),1.2)Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC789','ADL01',getdate(),4.5)Insert into #Item(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('1234567890','ADL01',getdate(),35.6)CREATE TABLE #Item1( [ITM$Con_Note] [varchar](50) NULL, [ITM$Machine] [varchar](10) NULL, [ITM$Date_Time] [datetime] NULL, [ITM$Weight] [numeric](18, 3) NULL, )Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC1231','ADL02',getdate(),10.75)Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC456','ADL02',getdate(),1.5)Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('ABC7890','ADL02',getdate(),4.55)Insert into #Item1(ITM$Con_Note,[ITM$Machine],[ITM$Date_Time],[ITM$Weight]) values('1234567890','ADL02',getdate(),35.75)select * from #Item AS oneLEFT OUTER JOIN #Item1 AS twoON one.ITM$Con_Note = two.ITM$Con_NoteWHERE one.ITM$Con_Note = two.ITM$Con_Note Topic Next Topic |
| Posted: 13 Sep 2013 07:36 AM PDT Hi all - I am preparing create tables with test data, but I thought this might be so simple and generic that it might not require them. I'm probably missing somthing simple here.After a table load I'm trying to set one column's data using the first char of another column. This is what I tried, but it seems to be updating the entire column to the same value - [code="other"]UPDATE DIM.DocControlProfile SET DocType = SUBSTRING(STG.CurrDocNumber,1,1)FROM STG.Staging AS STGINNER JOIN DIM.DocControlProfile AS DCON STG.DocContProfileID = DC.DocControlID[/code]Any generic syntax advice is appreciated but if needed I will finish the create table and add data script.Thanks in advance. |
| Finding gaps within date ranges Posted: 13 Sep 2013 02:00 AM PDT Hi, I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset. Example dataset SQL below:[code="sql"]CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME) INSERT INTO #test SELECT 1, '20130428', '20130523' UNION SELECT 1, '20130526', '20130823' UNION SELECT 1, '20130728', '20130728' UNION SELECT 1, '20130826', '20130830' UNION SELECT 2, '20130501', '20130515' UNION SELECT 2, '20130525', '20130830' select * from #test [/code]I would expect a dataset to be returned consisting of: 1, 24/05/2013, 25/05/20131, 24/08/2013, 25/08/20132, 16/05/2013, 24/05/2013Does anyone have a good way of doing this? I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns... Thanks! |
| use results of view in where statement Posted: 13 Sep 2013 07:28 AM PDT I have a sql server 2008 stored procedure where I want to use the results of a view in a where statement. I want to check to see if the results from the view is not null.The sql looks like the following currently:select top 5 from cust_table where view value is not null.Would you show me the t-sql that I would need to use to see if the results from the view is null? |
| Extract string between to characters Posted: 13 Sep 2013 03:09 AM PDT I've a string in following format:<1|458|test|q><2|789|test2|s><5|567|test3|p>First I've to extract the strings between < and >like: 1|458|test|q 2|789|test2|s 5|567|test3|pafter that I've to split the the pipes: 1 458 test q 2789 test2 s 5 567 test3 pThe occurrences of <> is not set to a specific number. I want to present the result in one row in different columns.I've tried with different functions (charindex, instring, substring) but no success the way I need the data.Any suggestions? Thanks in advance! |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment