Thursday, September 12, 2013

[T-SQL] Exclude rows

[T-SQL] Exclude rows

Exclude rows

Posted: 12 Sep 2013 01:00 AM PDT

Hey,Say I have a table with the following rows;Case | Hours | Typexxx | 3 | 1xxx | 3 | 2xxx | 4 | 1xxx | 1 | 1and I do a sum of the hours, I get 11. However, a Type of 1 is a billed item and 2 is a credit item. If something has been credited, the billed item row should be excluded, resulting in a total of 5.I'm shtoock!

Add Table to Different FileGroup

Posted: 12 Sep 2013 12:05 AM PDT

Hello EveryoneI would like to create a new filegroup and add, for now, only one table, in the new filegroup for archiving purposes. I have not had but very little experience in doing this task.I have created the new file and filegroup in the database. The name of the FileGroup is MessageArchive. I would like to create a new table named MessageArchive in the New Filegroup named MessageArchive by using[code="sql"]SELECT * INTO MessageArchiveFROM Messages[/code]But I am missing something on how to define the filegroup that I want to use, which is MessageArchive.After all the data has been copied into the new table in the MessageArchive filegroup, how does one query from that table?Thank you in advance for your assistance, comments and suggestions.Andrew SQLDBA

Query Help

Posted: 11 Sep 2013 09:06 AM PDT

Hi I want to update Flag column in second table based on the Adder names.If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.If the Application has more the one AIX and Adder names are diferent then the flag would be null.APpName OS Adder App1 ||| Windows|||NullApp1 ||| Linux |||UDBApp1 ||| AIX |||UDBApp1 ||| Linux |||SqlApp2 ||| AIX ||| UDBApp2 ||| Windows||| UDBApp2 ||| Linux ||| UDBApp2 ||| AIX ||| UDBOUTPUT SHOULD BE LOOK LIKE BELOWAPpName OS Adder FlagApp1||| Windows|||Null|||null App1||| Linux |||UDB |||null App1||| AIX |||UDB |||null App1||| Linux |||Sql |||null App2|||AIX ||| UDB|||TRUEApp2|||Windows||| UDB|||TRUEApp2|||Linux ||| UDB|||TRUEApp2|||AIX ||| UDB|||TRUELet me know fi you need addiitional information.ThanksAswin

Reset Identity Column only on 1 Times when Month have changed

Posted: 11 Sep 2013 06:15 PM PDT

Hi,I want to reset an Identity Column 1 Time in Month (by T-SQL -not job).How can I make sure that this action is performed only 1 Times every Month.Like this...if datepart(month,getdate()) != datepart(month,dateFromTableValue) Truncate Table DBCC CHECKIDENT('dbo.MyTable', RESEED, 1); Regards Nicole;-)

The working of PIVOT

Posted: 11 Sep 2013 08:12 PM PDT

Can you please explain how PIVOT is used in this query? i.e. logic behind the working of PIVOT in the query.SELECT * FROM ( SELECT IsNull(St.alternateStudyCode, St.studyCode) AS StudyCode, St.clientStudyReference as SponsorStudyCode, Cl.clientName AS Sponsor, SAFV.txtValue, SAF.extractName FROM Activities AS Ac INNER JOIN Studies AS St ON (St.studyIncId = Ac.studyIncId AND St.studySqlId = Ac.studySqlId) AND St.isDeleted = 0x0 AND St.studyStartDate < DateAdd(Day,1,@ToDate) -- study initiation date <= the end date AND (St.studyEndDate is null or St.studyEndDate >= @FromDate) -- study completion date >= the start date LEFT JOIN StudiesClients as StCl ON StCl.StudySqlId = St.studySqlId AND StCl.studyIncId = St.StudyIncId AND StCl.isDeleted = 0x0 AND StCl.sponsorRanking = 1 LEFT JOIN Clients AS Cl ON Cl.clientSqlId = StCl.clientSqlId AND Cl.clientIncId = StCl.clientIncId AND StCl.isDeleted = 0x0 LEFT JOIN StudiesCategories as StCa ON StCa.studyCategorySqlId = St.studyCategorySqlId AND StCa.studyCategoryIncId = St.studyCategoryIncId AND StCa.isDeleted = 0x0 LEFT JOIN StudiesStatuses AS StSt ON StSt.studyStatusSqlId = St.studyStatusSqlId AND StSt.studyStatusIncId = St.studyStatusIncId AND StSt.isDeleted = 0x0 LEFT JOIN StudiesAdditionalFieldsValues As SAFV LEFT JOIN StudiesAdditionalFields AS SAF ON SAF.studyAdditionalFieldSqlId = SAFV.studyAdditionalFieldSqlId AND SAF.studyAdditionalFieldIncId = SAFV.studyAdditionalFieldIncId AND SAF.isDeleted = 0x0 --Study link to Site LEFT JOIN CboValues AS StCbo ON SAFV.cboRecordSqlId = StCbo.cboValueSqlId AND SAFV.cboRecordIncId = StCbo.cboValueIncId AND StCbo.isDeleted = 0x0 ON SAFV.studySqlId = St.studySqlId AND SAFV.studyIncId = St.studyIncId AND SAFV.isDeleted = 0x0 AND SAF.extractName = 'GLPSite' LEFT JOIN Operators AS Op ON Op.operatorSqlId = Ac.todoBySqlId AND Op.operatorIncId = Ac.todoByIncId AND Op.isDeleted = 0x0 INNER JOIN TypesOfActivities AS TypOA WITH(NOLOCK) ON TypOA.typeOfActivitySqlId=Ac.typeOfActivitySqlId AND TypOA.typeOfActivityIncId=Ac.typeOfActivityIncId AND TypOA.isDeleted=0x0 AND TypOA.typeOfActivityCode = 'EAS-1' WHERE Ac.isDeleted = 0x0 AND ( (@siteSqlId = StCbo.cboValueSqlId AND @siteIncId = StCbo.cboValueIncId) OR (@siteSqlId = AcCbo.cboValueSqlId AND @siteIncId = AcCbo.cboValueIncId) )) As SourceTablePIVOT ( Min(TxtValue) FOR extractName In ( [TestItem], [TestSystem], [Crop], [Organism], [QASystem] ) ) As PivotTable

Only functions and some extended stored procedures can be executed from within a function.

Posted: 11 Sep 2013 07:25 AM PDT

Hi,I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.When I run it in my SQL2008R2 environment I get the error:Only functions and some extended stored procedures can be executed from within a function.The SQL2008R2 server is new. What can I look for?Here's the code for the function:[code="sql"]BEGIN DECLARE @v_Key CHAR(12) EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT RETURN @v_KeyEND[/code]

SELECT TOP ????

Posted: 11 Sep 2013 04:46 AM PDT

Hello EveryoneI am a bit curious after doing some accidental testing. I was going thru someone elses code, and noticed that on a few Select queries, they were using SELECT TOP 50000 etc, etc....But there are only something like 60 rows in that table. I removed the TOP clause and the query ran much faster and with a lot less CPU cycles. Is there truly something less efficient about using the TOP clause, with some really high number, when there are not nearly that many rows in the table? That is almost like using SELECT TOP 100% without a WHERE clause.Does removing the TOP clause and the large number, really increase the performance when there are not nearly that many rows in that particular table?Thanks in advance for any and all suggestions, comments and adviceAndrew SQLDBA

Trapping error from sp in another db

Posted: 11 Sep 2013 05:58 AM PDT

SQL Server 2008 R2Two computers: MachineOne and MachineTwoTwo instances of SQL server,: MachineOne.ServerOne and MachineTwo.ServerTwoTwo databases:MachineOne.ServerOne.DBOne, and Machine2.Server2.DBTwo.On [MachineTwo].[ServerTwo].[DBTwo] I write an sp that calls an sp from [MachineOne].[ServerOne].[DBOne][code]EXEC [MachineOne].[ServerOne].[DBOne].[dbo].[MySP][/code]On ServerOne [MySP] does a bunch of validation and uses RAISEERROR to break when something fails. The code there that raises the flag looks like[code] SET @i_ReturnMessage = dbo.udf_SystemMessage(100000,1)RAISERROR (@i_ReturnMessage, 16, 1)RETURN @@ERROR[/code]When everything is right as expected it works fine. I want to trap an error in MySP, though, so on ServerTwo I call[code]BEGIN TRY EXEC @ErrorReturn = [MachineOne].[ServerOne].[DBOne].[dbo].[MySP]END TRYBEGIN CATCH @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLocation = 'DoExistingEmployeeNewPasses', @ErrorDescription = @ErrorReturn+' - Adding Address Profile for '+@WFirstName+' '+@wLastName; EXEC [dbo].[LogError] @ErrorMessage, @ErrorSeverity, @ErrorLocation, @ErrorDescription ;END CATCH[/code]Where [LogError] is an sp on ServerTwo that should write the error to a log file.When I run it from SSMS connected to ServerTwo I get an error message as expected, but my TRY clause never seems to get triggered.How can I collect the error information on ServerTwo for logging? [MySP] on ServerOne is vendor code, so I can't do anything there to change how the flag is raised.

DATEPART WITH YEAR AND MONTH

Posted: 11 Sep 2013 06:54 AM PDT

Hi all how do I query datepart to include month also when passing a variable from my app. My attempt below WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, Docket_Date) AND (DATEPART(m, @date1) = DATEPART(m, Docket_Date)))

Trapping error from sp in another db

Posted: 11 Sep 2013 05:46 AM PDT

SQL Server 2008 R2Two computers: MachineOne and MachineTwoTwo instances of SQL server,: MachineOne.ServerOne and MachineTwo.ServerTwoTwo databases:MachineOne.ServerOne.DBOne, and Machine2.Server2.DBTwo.On [MachineTwo].[ServerTwo].[DBTwo] I write an sp that calls an sp from [MachineOne].[ServerOne].[DBOne]EXEC [MachineOne].[ServerOne].[DBOne].[dbo].[MySP][MySP] does a bunch of validation and uses RAISEERROR to break when something fails.When everything is right as expected it works fine. I want to trap an error in MySP, though, so on ServerOne I callBEGIN TRY EXEC [MachineOne].[ServerOne].[DBOne].[dbo].[MySP]END TRYBEGIN CATCH

SP_SEND_DB_MAIL Alignment Issue

Posted: 11 Sep 2013 04:06 AM PDT

HiI have a script, which when ran inserts values from multiple tables into another table, then executes 'sp_send_dbmail'. I use the '@query' parameter in 'sp_send_dbmail' to select from the new table, so that the data that has been amalgamated into the new table is shown in the output when the email is sent. All this works fine.The issue I am having is to do with how the data is displayed in the email received, the columns and the corresponding data do not align correctly, it's readable but obviously doesn't look great.For example, The corresponding data for Column A will not align correctly with Column A's heading, Column B's data will not align with Column B's heading etc.Hope this is clear, can anyone suggest a resolution? It's driving me crazy!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog