Friday, April 12, 2013

[T-SQL] Stuck on querying 3 tables where I'm looking for non-matches

[T-SQL] Stuck on querying 3 tables where I'm looking for non-matches

Stuck on querying 3 tables where I'm looking for non-matches

Posted: 11 Apr 2013 07:41 AM PDT

I have three tables: LitHold, LitHoldDetails and EmailTemplate. The definitions are as follows.[quote]CREATE TABLE [dbo].[LitHold]( [LitholdID] [int] IDENTITY(1,1) NOT NULL, [LitHoldStatusID] [tinyint] NOT NULL, [EmailReminderID] [tinyint] NULL, [ApprovedDate] [datetime] NULL, [TerminatedDate] [datetime] NULL, CONSTRAINT [PK_Lithold] PRIMARY KEY CLUSTERED ( [LitholdID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/quote][quote]CREATE TABLE [dbo].[LitHoldDetails]( [LitHoldDetailsID] [int] IDENTITY(1,1) NOT NULL, [LitholdID] [int] NOT NULL, [VersionID] [int] NOT NULL, [Description] [varchar](300) NULL, [ResAttorneyID] [varchar](10) NOT NULL, [Comments] [varchar](1000) NULL, [HoldStartDate] [datetime] NULL, [HoldEndDate] [datetime] NULL, [CreatedDate] [datetime] NOT NULL, [CreatedByLogin] [varchar](10) NULL, CONSTRAINT [PK_LitholdDetails] PRIMARY KEY CLUSTERED ( [LitHoldDetailsID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/quote][quote]CREATE TABLE [dbo].[EmailTemplate]( [TemplateID] [int] IDENTITY(1,1) NOT NULL, [LitHoldDetailsID] [int] NOT NULL, [From] [varchar](50) NULL, [To] [varchar](2000) NULL, [CC] [varchar](500) NULL, [BCC] [varchar](500) NULL, [Subject] [nvarchar](200) NULL, [MessageBody] [nvarchar](max) NULL, [SendDate] [datetime] NULL, [IsDefault] [bit] NOT NULL, CONSTRAINT [PK_EmailTemplate] PRIMARY KEY CLUSTERED ( [TemplateID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/quote]For each LitHold, there can be multiple LitHoldDetails. For each LitHoldDetail, there should be one EmailTemplate. I recently found that some LitHoldDetails do NOT have EmailTemplates. We're still working in development on this project, so this isn't a big deal. However, we want to get the EmailTemplate data into the database. The situation is that for each LitHold, there is at least one LitHoldDetail that has an EmailTemplate. I would like to duplicate this EmailTemplate data for all the LitHoldDetails that a) have the same LitHoldID and b) do not have an EmailTemplate. One of the approaches I've tried is:[quote]insert into EmailTemplate(LitHoldDetailsID, [From], [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault)(select (select LitHoldDetailsID from LitHoldDetails where LitholdID = d.LitholdID and LitHoldDetailsID <> e.LitHoldDetailsID), [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault from EmailTemplate e inner join LitHoldDetails d on e.LitHoldDetailsID = d.LitHoldDetailsID)[/quote]but this gets me the error message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." How can I accomplish this?

Obtaining earliest change for one colum?

Posted: 11 Apr 2013 03:07 AM PDT

Guys,I've been looking at this problem on and off for a while and I can't for the life of me find a solution. Basically we have an audit system where by every time someone changes one field in one table it appends the entire row into another table, a slight simplification but you get the idea.What I want to get is a historical record for one field over time - so say this was a list of individuals and it had their wealth in dollars, their job title, their address, their age etc. It may be that their wealth in dollars didn't change over the course of say 10 rows in the table but other variables did change, I'm trying to build up a picture of their wealth changing. Totally random example but I thought it might help!With this test data:[quote]SELECT * INTO #testFROM(SELECT 1 as Audit_ID, 123 as EntID, 100 as Field_A, 10 as Field_B, '01-Jan-12' as ActionDate UNIONSELECT 2 as Audit_ID, 123 as EntID, 110 as Field_A, 10 as Field_B, '15-Feb-12' as ActionDate UNIONSELECT 3 as Audit_ID, 123 as EntID, 150 as Field_A, 15 as Field_B, '20-Mar-12' as ActionDate UNIONSELECT 4 as Audit_ID, 123 as EntID, 100 as Field_A, 10 as Field_B, '12-Jun-12' as ActionDate UNIONSELECT 5 as Audit_ID, 445 as EntID, 50 as Field_A, 4 as Field_B, '02-Apr-12' as ActionDate UNIONSELECT 6 as Audit_ID, 445 as EntID, 60 as Field_A, 3 as Field_B, '15-May-12' as ActionDate UNIONSELECT 7 as Audit_ID, 445 as EntID, 30 as Field_A, 3 as Field_B, '18-Aug-12' as ActionDate UNIONSELECT 8 as Audit_ID, 552 as EntID, 500 as Field_A, 1 as Field_B, '10-Jan-12' as ActionDate UNIONSELECT 9 as Audit_ID, 552 as EntID, 450 as Field_A, 3 as Field_B, '15-Mar-12' as ActionDate UNIONSELECT 10 as Audit_ID, 552 as EntID, 320 as Field_A, 4 as Field_B, '29-Aug-12' as ActionDate)x[/quote]Say I was trying to track field B over time for Entity (EntID) 123, I'd want a results set like this:01-Jan-12 1020-Mar-12 1512-Jun-12 10I thought I could do some kind of ranking whereby I partitioned on the field I was interested on and ranked on date, and chose those with rank 1, however, this doesn't take account for the same value at different time points - e.g. using the wealth example perhaps in Jan 2010 I had $200, in Feb 2010 this went up to 350 but then it fell to $200 again in September 2010, with my ranking idea I'd just have Feb 2010 and 200, not September too.I thought about some form of 'double join' type thing along hte lines of Field B <> Field B, I've also got pratically no experience of them but for some reason I'm wondering if a CTE is called for?!Hoepfully I've explained this not too bad and my test data gives you kind of an idea! Thanks for reading :)

Begginer Sub Query problem

Posted: 11 Apr 2013 04:30 AM PDT

//EDIT: I've posted in the wrong board, I should have posted in the General Board, ut I don't know how to move this!I'm fairly green with SQL (am still learning on the job!) so please accept my apologies if this both a confused question as well as a very basic question.I have a table that I'll simplify to have justthe following columns: CompanyName (string), CompanyID (Guid), Heirarchy (int), ParentCompanyID (Guid)The ParentCompanyID I suppose is self-referential in that if the Company is a sub-division of a parent company it will have say an Heirarchy =1 and the CompanyID of the Parent Company held in the same Table.I have been trying to write a Select stmt to show the Parent CompanyName instead of the ParentCompanyID:Select CompanyName, Heirarchy, [substitute ParentCompanyID for Parent CompanyName]From CompanyOrder by CompanyNameIf I use a simple Where clause of Where ParentCompanyID = CompanyID I land up filtering down to only companies that have SubDivisions. This has occupied my entire afternoon and I have gotten nowhere so am turning to the forums in the hope I can be put of my misery!Hopefully someone could point me in the right direction, unfortunately I can't change the schema as it is form the company's CRM system.

Add prefix to all table name

Posted: 11 Apr 2013 07:56 AM PDT

Hi,I would like add same prefix to all table names.For Example:TableATableBResult Needed:aaa_TableAaaa_TableBI would appreciate some input.Thanks in advance !!!

Question about query hint nolock

Posted: 11 Apr 2013 11:00 PM PDT

Hi people,I have a question about nolock. I work in a company that there is a rule: all select query we must put nolock. If we don't put it the query execution becomes slowly. don't sqlserver get manage it transactions?Is there any configuration or parameter to avoid using nolock all time when I run a select statement?Best regards.

Format SQL (Pro)

Posted: 11 May 2012 04:07 AM PDT

I tried Format SQL (Pro) and I really liked it.Unfortunately I will probably not be able to get it funded.There are some Free SQL Formatting Tools.Has anyone used any of them and if so what is your preferred tool?

Extract Data From A String in SQL Server

Posted: 11 Apr 2013 04:16 PM PDT

Hi All,I have a String as in put, I need to Extract the Data in teh String.Delimiter for the String is "[" String: -1 * [SAL] + [COMM] - [BON]Please need help...

Multiple DMLS in one when clause of MERGE Statement

Posted: 11 Apr 2013 07:50 PM PDT

Hi All,I want to use 2 DMLS in When not Matched by target clause ie. first insert and then update.But I am getting problem while executing this code .Please provide a better way to do it.MY CODE :[size="4"][i]Merge Vacancy.TB_FTSearchData as A using vacancy.TB_Vacancy as B on A.FTSearchVacancyInternalID=B.VacancyInternalID and A.FTSearchContractID=B.VacancyContractID When not Matched by source then Delete When not Matched by target and B.VacancyFTSupdated=1 and B.VacancyStatusID=1 then INSERT ([FTSearchVacancyInternalID] ,[FTSearchVacancyTitle] ,[FTSearchVacancyCleanDescription] ,[FTSearchVacancyCategoryName] ,[FTSearchVacancyTagName] ,[FTSearchContractID] ,[FTSearchVacancyLCID] ,[CreatedDate] ,[ModifiedDate]) Values (VacancyInternalID, VacancyTitle, VacancyCleanDescription, dbo.fn_GetCSV(B.VacancyInternalID,1) , dbo.fn_GetCSV(B.VacancyInternalID,2) , VacancyContractID,(Select la.LCID from ReferenceData.TB_Language la where la.languageInternalID =B.vacancyLanguageID), GETDATE(), NULL ) UPDATE SET B.VacancyFTSupdated=3 WHERE VacancyFTSupdated=1 and VacancyStatusID=1 and VacancyInternalID in (Select FTSearchVacancyInternalID from Vacancy.TB_FTSearchData) when matched and B.VacancyFTSupdated=2 AND B.VacancyStatusID=1 then UPDATE SET A.FTSearchVacancyTitle=B.VacancyTitle ,A.FTSearchVacancyCleanDescription=VacancyCleanDescription ,A.FTSearchVacancyCategoryName=dbo.fn_GetCSV(B.VacancyInternalID,1) ,A.FTSearchVacancyTagName=dbo.fn_GetCSV(B.VacancyInternalID,2) ,[FTSearchContractID]=VacancyContractID ,[FTSearchVacancyLCID]=(Select la.LCID from ReferenceData.TB_Language la where la.languageInternalID =B.vacancyLanguageID) ,[ModifiedDate]=GETDATE() OUTPUT $action,Deleted.*,Inserted.* ; [/i][/size]

CTE - UNION ALL - GROUP BY

Posted: 11 Apr 2013 04:31 PM PDT

Today I got a mail from one of our developers. It is a little complicated to explain and I cannot post the DDL. So, let me post a piece of the code ('cleaned up') and explain what was going wrong.CTE definition 'cleaned up'[code="sql"];with t2 as ( select tb.product, r.category, p.date, sum(p.value) as value, ROW_NUMBER() over (partition by tb.product, r.category order by SUM(p.value)) as cnt FROM --tables )[/code][code="sql"]select t3.product, t3.category, SUM(t3.value) * -1 as Value from( select t2.product, t2.category, t2.Value * @Ratio1 as value from t2 where cnt = @Count1 union all select t2.product, t2.category, t2.Value * @Ratio2 as value from t2 where cnt = @Count2) as t3--where t3.category = 'ABC'group by t3.product, t3.categoryorder by t3.product[/code]Here t2 is a CTE which does an aggregation (SUM) and a row_number() generation. When the developer ran this query, he was getting incorrect output for column Value. When he added the filter by category, he got the correct output.My analysis:I removed the GROUP BY and ran the query, got the correct values for the column value (say Val1 and Val2). When I added the GROUP BY, the output was wrong, i.e. SUM(t3.value) was not showing Val1 + Val2.I got a hunch that this had something to do with UNION ALL on CTE, so I modified the query as below to eliminate the UNION ALL op.[code="sql"]select t3.product, t3.category, SUM(t3.value) * -1 as Value from( select t2.product, t2.category, CASE WHEN cnt = @Count1 THEN t2.value * @Ratio1 WHEN cnt = @Count2 THEN t2.value * @Ratio2 END as value from t2 where cnt = @Count1 OR cnt = @Count2 ) as t3--where t3.category = 'ABC'group by t3.product, t3.categoryorder by t3.product[/code]This worked just fine, gave the expected output. What I would like to know is what could have caused a wrong aggregation when the query had a UNION ALL. I know the CTE gets evaluated twice but the CTE is deterministic. Any help here would be really great. I will post the CTE definition below.

Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt

Posted: 11 Apr 2013 09:56 AM PDT

Can someone help me understand why this is happening:If I put the following in a where clause: CASE WHEN PATINDEX('%[^0-9 ]%',h.oe_po_no) = 0 THEN Cast(RTrim(LTrim(h.oe_po_no)) as BigInt) ELSE 0 END = 0I get the following error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.I have seen the same situation when using IsNumeric instead of PATINDEX. Why doesn't T-SQL truly recognize numeric strings and allow them to be converted? This is so frustrating.Thank you.Tammy

Needed Urgent help please with a TSQL code for report

Posted: 11 Apr 2013 08:36 AM PDT

Dear friends,i have a dead line to do the following requirement for 2 fields-1) Age (Working Days) "a) Don't display this if the job posting is in Closed statusb) If the job posting is in Halted status, only display this data if there is no corresponding work order.Calculate as the number of working days from the Job Posting Create Date until the day prior to the Report Run Date, inclusive.For example, if the Job Posting Create Date is March 18, 2013 and the Report Run Date is April 8, the age would be 15 days. Working days should exclude weekends."Age Group (Working Days) "a) Don't display this if the job posting is in Closed statusb) If the job posting is in Halted status, only display this data if there is no corresponding work order.2) Based on the Age (Working Days), display one of these selections: 0 - 2930 - 5960 - 8990 - 119120 +In the example display ""0 - 29""."for the 1st field I have the code -(datediff(dd,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP]),convert(int,[WorkForce_JobPosting].[Report Run Date_BYR])+1))- (datediff(WK,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP]),convert(int,[WorkForce_JobPosting].[Report Run Date_BYR]))*2)- (CASE when DATENAME(DW,convert(int,[WorkForce_JobPosting].[Report Run Date_BYR])) = 'Sunday' then 1 else 0 END)- (CASE when DATENAME(DW,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP])) = 'Saturday' then 1 else 0 END)but not sure how to implement it conditionally and need help with the second field to display range based on the output of this field.thanksDhananjay

CTE - Temp table - CROSS APPLY

Posted: 03 Apr 2013 07:52 PM PDT

I had some free time at work and was trying out some silly queries when I came across this.CTE referenced in a subquery does not yield any result. I didn't expect this but I can understand why it can happen. Why doesn't the parser throw an error in that case?Here are the queries:[code="sql"]--Subquery with CTE - was surprised by the output;WITH cteProcDependenciesAS(SELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]FROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAME)SELECT pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]FROM cteProcDependencies pdGROUP BY pd.ROUTINE_NAME--Subquery With Temp tableIF OBJECT_ID('tempdb..#cteProcDependencies') IS NOT NULLDROP TABLE #cteProcDependenciesGOSELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name] INTO #cteProcDependenciesFROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAMESELECT pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM #cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]FROM #cteProcDependencies pdGROUP BY pd.ROUTINE_NAME--With cross apply, as my teammate suggested;WITH cteProcDependenciesAS(SELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]FROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAME)SELECT pd.ROUTINE_NAME, tab.c [enc_ROUTINE_NAME]FROM cteProcDependencies pdCROSS APPLY (SELECT enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME) [Tab] (c)[/code]

How To Validate in Procedure

Posted: 22 Jan 2013 04:06 PM PST

I Created a one Procedure ..its given Below...[code="sql"]create procedure [dbo].[USP_Reliance_Master_Upload] as begin declare @dt datetime , @Mnth varchar(10) BEGIN TRY SELECT @dt = convert(varchar(10),convert(datetime,DCPI_Date),101) from Ram_Reliance_Master END TRY BEGIN CATCH RETURN-1 -- The Date Field is In Correct Formate so Please Check Your Data END CATCH BEGIN TRY SELECT @Mnth = Mnth from Ram_Reliance_Master where isnull(Mnth,'')!='' END TRY BEGIN CATCH RETURN-1 END CATCH -- The Date Field Should Not Be Null insert into dbo.Reliance_Master (Mnth,DCPI_Date,GRP_OUTS_RIL,Interest_Cost,OverDue_Earned,Qty,EPI,insertDt,Year) select Mnth,convert(datetime,DCPI_Date),GRP_OUTS_RIL,Interest_Cost,OverDue_Earned,Qty,EPI,cast(getdate() as DATE),DATEPART(YY,DCPI_Date) from Ram_Reliance_Master end [/code]The Existing Ram_Reliance_Master Table Data is Given Below,[code="sql"]Mnth DCPI_Date GRP_OUTS_RIL Interest_Cost OverDue_Earned Qty EPI Year ---------- ---------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------- DEC 2012-12-12 NULL NULL NULL 5 2925 2012 DEC 2012-12-21 NULL NULL NULL 0.002 0.91 2012 DEC 2012-12-26 NULL NULL NULL 0.5 NULL 2012 Null 13-02-2012 NULL NULL NULL 5 NULL 2012 DEC 2012-12-12 NULL NULL NULL 5 2925 2012 [/code][b]Requirement is...[u][/u][/b]I Build that Stored Procedure in SSIS Package with Job scheduler...In that Procedure i want to check the Ram_Reliance_Master table Value1)Mnth Column Should be Not Null 2)The DCPI_Date Field Should be 101 Format ie, (MMDDYYYY)Otherwise that Procedure will not Execute ... it should be terminate ...should not commit any Rows...How To Validate in Procedure ?I dont know well the Above Query is working fine or not..or any other idea Please Share and Support Me...Thanks & RegardsSaravanan.D

sql trigger problem

Posted: 26 Jan 2013 01:38 PM PST

I am trying to write my first trigger on a sql server 2008 r2 database.I keep getting a procedure error the 'INSERT INTO [dbo].[RPT_Trans_Audit] (Package_ID, Received_Date, Download_Date)' statement listed below. I do not know what is wrong. Can you tell me what I need to change?USE DEV2GOCREATE TRIGGER [dbo].[RPT_Trans_Audit] ON [RPT_Trans] AFTER INSERT, UPDATE, DELETEASBEGINSET NOCOUNT ON;BEGIN TRY INSERT INTO [dbo].[RPT_Trans_Audit] (Package_ID, Received_Date, Download_Date) SELECT Package_ID, Received_Date, Download_Date FROM INSERTEDEND TRY GO
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog