Posted: 05 Jul 2013 12:06 AM PDT

Hi All,I have two tables as described below:CREATE TABLE [dbo].[Batch]( [BatchID] [int] IDENTITY(1,1) NOT NULL, [BatchName] [nvarchar](50) NULL, [CourseID] [int] NULL, [DateFrom] [datetime] NULL, [DateTo] [datetime] NULL, CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED ( [BatchID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Batch] ONINSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (1, N'B1', 1, CAST(0x0000A1F200000000 AS DateTime), CAST(0x0000A1FC00000000 AS DateTime))INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (2, N'B2', 2, CAST(0x0000A1F300000000 AS DateTime), CAST(0x0000A1FD00000000 AS DateTime))INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (3, N'B3', 3, CAST(0x0000A1F400000000 AS DateTime), CAST(0x0000A1FE00000000 AS DateTime))INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (4, N'B4', 4, CAST(0x0000A1F500000000 AS DateTime), CAST(0x0000A1FF00000000 AS DateTime))INSERT [dbo].[Batch] ([BatchID], [BatchName], [CourseID], [DateFrom], [DateTo]) VALUES (5, N'B5', 5, CAST(0x0000A1F600000000 AS DateTime), CAST(0x0000A20000000000 AS DateTime))SET IDENTITY_INSERT [dbo].[Batch] OFF------------CREATE TABLE [dbo].[Course_Module]( [CourseModuleID] [int] IDENTITY(1,1) NOT NULL, [CourseID] [int] NOT NULL, [ModuleID] [int] NOT NULL, CONSTRAINT [PK_Course_Module] PRIMARY KEY CLUSTERED ( [CourseModuleID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Course_Module] ONINSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (1, 1, 1)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (2, 1, 2)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (3, 1, 3)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (4, 1, 4)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (5, 2, 2)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (6, 2, 5)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (7, 2, 6)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (8, 2, 7)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (9, 3, 1)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (10, 3, 4)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (11, 3, 5)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (12, 4, 2)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (13, 4, 3)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (14, 5, 6)INSERT [dbo].[Course_Module] ([CourseModuleID], [CourseID], [ModuleID]) VALUES (15, 5, 7)SET IDENTITY_INSERT [dbo].[Course_Module] OFFHere I want a TSQL query which return those batchesWhen I provide a CourseID then I should get all those batches which explicitly contains all the MODULEID attached with the provided CourseID.For Example:I have provided the CourseID = 4, then it should return CourseID 1 and 4 as both of them contains the ModuleID 2,3 but it should not return 1,2,3,4 as CourseID = 2 do not contain the ModuleID = 3 and CourseID = 3 do not contain the ModuleID = 2I have written the Code :Select distinct B.* from Batch Binner join course_Module CM on B.CourseID = CM.CourseIDinner join Course_Module CMM on CM.ModuleID = CMM.ModuleIDwhere CMM.CourseID = 4The Answer I am getting is :BatchName CourseIDB1 1B2 2B4 4Answer should be :BatchName CourseIDB1 1B4 4

Posted: 04 Jul 2013 07:49 AM PDT

Coming from MySQL background, one query design I learned early on was 'strawberry query', which got its odd name from the MySQL newsgroup. It is a very useful pattern for solving the problem of answering questions like "who's the best performing salesperson of month?" or similar questions.The solution basically involves doing a "triangular join" and filtering for NULLs. Using salesperson example:[code="sql"]SELECT s.SalesPerson, s.SalesMonth, s.SalesAmount, s.CustomerFROM Sales AS sLEFT JOIN Sales AS m ON s.SalesMonth = m.SalesMonth AND s.SalesAmount < m.SalesAmountWHERE m.SalesID IS NULL;[/code]Note that the query is free to include other fields from the same row because there is no GROUP BY; the grouping is implicitly done via the self-join. We are guaranteed to get only one possible row each month for a given sales which also is the greatest amount. There is no any other row that's greater than the greatest amount of given month so m.SalesID must be NULL.This also works for getting the minimum; just reverse the inequality operator on the join criteria. Also, there is no TOP 1 ... ORDER BY which can be problematic when you need to get multiple results (e.g. you want to see all 12 months at once.)Now, that worked well with MySQL. However, I'm also aware that T-SQL language has some features that doesn't exist in the MySQL dialect and also whether there might be a better way of doing it in T-SQL. I don't exactly trust myself to interpret the best execution plans so I'd be very interested in hearing from others whether this can be outperformed by alternatives such as using ROW_NUMBER() or whatever other approaches. Thanks!

Server level trigger

Posted: 04 Jul 2013 07:55 AM PDT

Hi,I wanted to a create server level trigger which prevents change of recovery modelI got some resources from internet and here is the code. There are couple of problems with this code. 1. When I issue ALTER DATABASE COMMAND , it says Mail queued and because of the below ROLLBACK statement it is getting rollbacked and I dont receive any kind of email. 2. Other thing is that, I am able to see the RAISERROR() msg in management studio, but the ALTER DATABASE statement somehow getting auto-commit which allows the recovery model to be changed. Can anybody help me in acheiving this task. what necessary changes/logic should i need to incorporate in the below code to make the mail functionality as well prevent ALTER DATABASE statement getting executed.Other alternative is Policy Based Management but I wanted to implement this using trigger.Thanks in Advance. Use master go create database testdbgoALTER DATABASE testdb SET RECOVERY FULL;go create trigger [usp_Restrict_RecoveryModel_Changes]ON ALL SERVERFOR ALTER_DATABASE, DROP_DATABASEASBEGIN DECLARE @data xml DECLARE @trigger_name sysname, @LoginName sysname, @UserName sysname, @dbname sysname SET @data = EVENTDATA() DECLARE @Str nvarchar(max) SELECT @Str=cast(@data as nvarchar(max)); /* <EVENT_INSTANCE> <EventType>ALTER_DATABASE</EventType> <PostTime>2007-01-12T20:05:27.527</PostTime> <SPID>65</SPID> <ServerName>NAME</ServerName> <LoginName>sa</LoginName> <DatabaseName>TestSecurity</DatabaseName> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>ALTER DATABASE [TestSecurity] SET RECOVERY SIMPLE WITH NO_WAIT </CommandText> </TSQLCommand> </EVENT_INSTANCE> */ -- send email to self before you will raise error EXEC msdb.dbo.sp_send_dbmail @recipients='test@gmail.com', @subject = '!!!****Attempt to alter database***!!!', @body = @Str, @body_format = 'HTML', @profile_name ='Test profile' RAISERROR ('ALTER DATABASE DISABLED!',10, 1) ROLLBACKEND GO---trying to change recovery model to 'SIMPLE', we need to prevent this happening!!!!ALTER DATABASE testdb SET RECOVERY FULL;go Thanks in Advance.

Sql samples, sql tips

Friday, July 5, 2013

[T-SQL] Require help to retrieve data from a table

[T-SQL] Require help to retrieve data from a table

No comments:

Post a Comment

Search This Blog