Monday, May 13, 2013

[SQL Server 2008 issues] How to arrange Employee manager Hierarchy tree in sql server .

[SQL Server 2008 issues] How to arrange Employee manager Hierarchy tree in sql server .

How to arrange Employee manager Hierarchy tree in sql server .

Posted: 12 May 2013 05:26 PM PDT

create table #Sample_emp(Ename varchar(50),EmployeeId int,ManagerId int)insert into #Sample_emp values ('Faisal Husain',11,NULl)insert into #Sample_emp values ('Deepak Zambre',12,11)insert into #Sample_emp values ('Milind joshi',13,12)insert into #Sample_emp values ('Mitesh Oswal',14,13)insert into #Sample_emp values ('Saket verma',15,13)insert into #Sample_emp values ('Sagar gadwe',16,13)insert into #Sample_emp values ('Vinayak Kulkarni',17,13)select * from #Sample_empWITH Asurion_mgr AS (SELECT e1.EmployeeId AS [employeeID] , e1.EmployeeId AS [NewEMPID] , e1.managerId AS [managerId] , 0 AS [level] , CAST(e1.Ename AS VARCHAR(MAX)) AS [Manager Hierarchy]FROM #Sample_emp e1 UNION ALL SELECT Am.[employeeId] , e2.EmployeeId AS [EmployeeId] , e2.managerID AS [managerId] , Am.[level] + 1 AS [level] , CAST(Am.[Manager Hierarchy] + ' - ' + e2.[Ename] AS VARCHAR(MAX)) AS [Manager Hierarchy2]FROM Asurion_mgr Am JOIN #Sample_emp e2 ON e2.EmployeeId = Am.[managerId]WHERE e2.EmployeeId <> Am.[employeeID] )SELECT [employeeID],[Manager Hierarchy] FROM Asurion_mgr where managerId is null

Sending Multiple Independent Messages to Multiple Users

Posted: 12 May 2013 01:28 AM PDT

I have confusion regarding how to [b]send multiple messages to multiple users at the same time[u][/u][/b] at periodic intervals(weekly basis or monthly basis)i.e each user will get different message... please suggest any solution

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager

Posted: 14 Dec 2012 02:27 AM PST

How to install an oracle linked server on SQL Server 2008 r2 failover cluster managerI'm reading some articles and applying them but with no result.First of all where can I get the ODBC driver free and the install, as I got the driver but does include the odbc_install.exeThanks for your help, this is driving me crazy.Thanks againAPA

Weird grouping problem thats driving my mad!

Posted: 12 May 2013 05:30 AM PDT

Can somone help me with the following grouping problem?I have a process which produced a table like the below:[code="sql"]create table foo(master_id int, duplicate_id int)insert into fooselect 43157, 31574 union allselect 43157, 35731 union allselect 51477, 35731 union allselect 65842, 31574 union allselect 65842, 43157 union allselect 77822, 35731 union allselect 77822, 43157 union allselect 77822, 49202 union allselect 79673, 31574 union allselect 79673, 43157 union allselect 79673, 65842[/code]I would like to change the result to the below:[code="sql"]create table foo_result (master_id int, duplicate_id int)insert into foo_resultselect 79673, 77822 union allselect 79673, 35731 union allselect 79673, 43157 union allselect 79673, 49202 union allselect 79673, 31574 union allselect 79673, 65842 union allselect 79673, 51477[/code]Any suggestions please?

Hash warnings

Posted: 12 May 2013 08:21 AM PDT

Hi - Can anbody advise me on the following:I am using red gates sql monitor and every night during the nightly etl loads I am getting the hash warnings alert triggered - it is set to fire when the value reaches 50. I have read that maybe adding indexes, or adding more memory may help. How do i find out what job is causing this and/or what queries are running when this happens ?I'm presumiing i will have to set profiler to run all night long from say 12am to 7 am using filters. I just want to capture the relevant info without adding extra overhead to the server, so can anybody give some tips on the best way to approach this ?Thanks:-)

Find the second comma

Posted: 11 May 2013 07:39 PM PDT

Dear,I have a String='A,B,C'.I want to find A, B, C individually using substing function. There may be 3 select statements. But I can't find the 2nd Comma(',') from that string.Please help me.

Update Values on one table based on another table - TRIGGER ???

Posted: 12 May 2013 05:17 AM PDT

Hi,I'm already sorry (especially if Sean is answering again) ... I am quite new to this whole posting stuff, but I am trying my best ...I got two tables ... 'Customer' and 'Rooms' ... a single Customer with a UNIQUE CU_ID is staying in a room (with a UNIQUE ROOM_ID, unfortunately for me, sometimes there are 2 or three CU_ID staying in the same Room ... they usually check in at the same day though, so the CheckInDate should be the same ... When they check in and the RoomNo is entered on the 'Customer'-Table I would like the Bit-Field 'Occupied' in the 'Rooms'-Table to be set to "TRUE". That part I got done with a Trigger (see below) ...The trick is when they are checking out ... If a User manually marks the "Occupied"-(Bit)Field for that Room-ID as "FALSE", then I would like to set the DepartDate on the Customer-Table for ANY Customer staying in that room at the MOMENT to Getdate(). Here's my tables, Trigger, and some test data:[code]CREATE TABLE [dbo].[Rooms]( [Room_ID] [int] IDENTITY(1,1) NOT NULL, [Room_No] [nvarchar](50) NULL, [Occupied] [bit] NULL, [CheckInDate] [int] NULL,CONSTRAINT [PK_Rooms] PRIMARY KEY CLUSTERED ( [Room_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[Customer]( [CU_ID] [int] IDENTITY(5000,1) NOT NULL, [CheckInDate] [datetime] NULL, [RoomNo] [int] NOT NULL, [Nights_Booked] [int] NULL, [DepartDate] [datetime] NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [CU_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [FK_Customer_Rooms] FOREIGN KEY([RoomNo])REFERENCES [dbo].[Rooms] ([Room_ID])GOALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_Rooms]GO-- 2 Tables created including PK/FK Relationship[/code]Here's my Trigger for the first step ... updating the bit column Occupied to True when Room_ID is used for new Check-In:[code]Create TRIGGER [dbo].[Occupied] ON [dbo].[Customer]FOR INSERTNOT FOR REPLICATIONASBEGIN IF TRIGGER_NESTLEVEL() > 1 RETURN UPDATE Rooms SET [Occupied] = 'True' FROM Rooms r JOIN Customer cu ON cu.[RoomNo] = r.[Room_ID] Join INSERTED INS ON cu.[RoomNo] = INS.[RoomNo]END[/code]I enter some test data into both of them ...[code]SET IDENTITY_INSERT Rooms ONINSERT INTO Rooms(Room_ID, Room_No, Occupied) SELECT 1, 'A14', 0 UNION ALLSELECT 2, 'B2', 0 UNION ALLSELECT 3, 'C3', 0 UNION ALLSELECT 4, 'D8', 0 UNION ALLSELECT 5, 'K9', 0 SET IDENTITY_INSERT Rooms OFFGOSET IDENTITY_INSERT Customer ONINSERT INTO Customer(CU_ID, CheckInDate, RoomNo, Nights_Booked, DepartDate) SELECT 5000, '2013-05-10', 1, 4, NULL UNION ALLSELECT 5001, '2013-05-10', 1, 4, NULL UNION ALLSELECT 5002, '2013-05-10', 2, 2, NULL UNION ALLSELECT 5003, '2013-05-10', 3, 3, NULL UNION ALLSELECT 5004, '2013-05-11', 4, 4, NULL UNION ALLSELECT 5005, '2013-05-11', 4, 4, NULL UNION ALLSELECT 5006, '2013-05-11', 4, 4, NULLSET IDENTITY_INSERT Customer OFF-- Test Data entered in rows on 'Rooms' and 'Customer'-Tables[/code]The Trigger works fine and it updates all the Records with the same Room_ID (RoomNo respectively on Customer Table). I tried to solve my problem with other Triggers. And I get SQL Server to enter the Depart-Date based on the Check-In-Date of the specific Customer, if I pass that one on to the Room-Table. Unfortunately it only updates the Data with the 1st Entry made for that specific Room_ID on the Rooms-Table ... and it seems awkwardly much passing back and forth between the two tables. I guess I need a Stored Procedure/Function to actually accomplish that:- On Insert of Customer Record pass NEWEST CheckInDate and Insert into Room-Table Field CheckInDate- When Rooms.Occupied is marked as 'False', set the Check-Out-Date for all CU_ID with Customer.RoomNo = Rooms.Room_ID AND Customer.CheckInDate = Rooms.CheckInDate to GETDATE() ...I struggle with the first part - how to pass the CheckInDate on Insert and if a value is existing update it with the newer date ...No idea, again ... I'm all new :)Thanks for any help in advance !!!P.S.: Sean ... still working on that Constraint ;)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog