[T-SQL] How can I get the dates I need? |
- How can I get the dates I need?
- Simple derived table with multiple rows
- Stubborn FK doesn't want to play nicely during insert statement
- IS NULL in a Case Statement
| How can I get the dates I need? Posted: 10 May 2013 09:23 AM PDT date1 >= 01/01/2013 --@STARTDATEdate1 <= 02/28/2013 --@ENDDATEcolumns:quote# date1 date2 1 01/01/2013 12/21/2012 <----This date is within -30 days of date1 2 02/17/2013 01/25/2013 <----This date is within -30 days of date1 3 02/08/2013 11/03/2012 <----This date is not within -30 days of date1 I do not want to return this row. I only want to return a row if the date2 column is within -30 days of date1 |
| Simple derived table with multiple rows Posted: 10 May 2013 02:24 AM PDT I'm having trouble creating a simple derived table. It is so simple to create a derived table with multiple columns with:[code="sql"]SELECT 1,2,3 [/code]Feels like it shouldn't be much more difficult to create a derived table with one column and multiple tuples/rows.Is there a way to get following result without creating a temp table or specifying the whole DDL:derived table123Thanks |
| Stubborn FK doesn't want to play nicely during insert statement Posted: 10 May 2013 08:18 AM PDT Stupid Troublesome Error Code:[code="other"]The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK600". The conflict occurred in database "HTC", table "dbo.COMMENTS", column 'GUID'. [/code]FK Setup:The FK600 is set up as:FK Base Table COMMENTSFK Columns RELATED_GUIDPrimary/Unique Key Base Table COMMENTSPrimary/Unique Key Columns GUIDSQL[code="sql"]INSERT INTO [dbo].[COMMENTS] ( [GUID] , [TYPE_CODE] , [SUB_TYPE] , [CUSTOMER] , [TEXT] , [RELATED_GUID] , [CREATE_DATE] , [CREATE_USER] ) SELECT tc.[GUID] , tc.[TYPE_CODE] , tc.[SUB_TYPE] , tc.[CUSTOMER] , tc.[TEXT] , tc.[RELATED_GUID] , tc.[CREATE_DATE] , tc.[CREATE_USER] FROM [dbo].[TEMP_COMMENT] AS tc[/code]TEMP_COMMENT is setup identical to COMMENT with the same data types. As each comment is replaced in the application with an updated version, the newly insert record in via the application creates a new GUID and references the old RELATED_GUID so I can see a trail. Note A, Guid = 1, RELATED_GUID = 1, replaced by Note B with new GUID 2 and RELATED_GUID = 1I cannot insert a RELATED_GUID with a NULL, so I build the TEMP_COMMENT - GUID with REPLACE( NEWID(), '-', '') and run an update statement to SET RELATED_GUID = GUID. I have tried order by, cast the text as VARCHAR(MAX) --current data type is TEXT --, kicking the computer, dumping water on the keyboard, and yelling at it. Nothing works. Any ideas? |
| Posted: 10 May 2013 02:33 AM PDT This is probably very simple but I can't get it to work.If trying to write this[b]Case Oppo_stage When 'Bus_Written' Then (oppo_closed is null or Oppo_Closed >= GETDATE() -7)[/b]but I'm being told there is incorrect syntax near the keyword 'is'Can anyone help?many thanks. |
| 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