Thursday, May 2, 2013

[T-SQL] Update Column after Alter Table ADD column

[T-SQL] Update Column after Alter Table ADD column

Update Column after Alter Table ADD column

Posted: 01 May 2013 05:05 PM PDT

hi,I just came across an error, the cause of which is unknown to me.why would a TSQL :-----------------------------------------------------------------------IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Schema1' AND TABLE_NAME = 'Table1' AND COLUMN_NAME = 'Column1')BEGIN ALTER TABLE Schema1.Table1 ADD Column1 int NULL UPDATE Schema1.Table1SET Column1 = -1 ALTER TABLE Schema1.Table1 ALTER COLUMN Column1 int NOT NULLEND------------------------------------------------throw an error:Msg 207, Level 16, State 1, Line 5Invalid column name 'Column1'.while running the update command.

sql count

Posted: 01 May 2013 04:50 PM PDT

I have a table like the following:Name Time1 Time2 Time3leo 06:03 0615 08:13John 06:17 10:03 11:05Can anyone tell me how to use sql to count the time between 06:00 and 06:30 and make it like the following:Name Time1 Time2 Time3 Totalleo 06:03 0615 08:13 2John 06:17 10:03 11:05 1thanks in advanced

update record from another record in same table

Posted: 01 May 2013 01:54 AM PDT

Hi, I'm looking for the best way to update pkid 1 = pkid 2update testTableset a.fname = b.fnamefrom(select * from testTable where pkid = 1) as ajoin(select * from testTable where pkid = 2) as b

Converting particular set of Rows as Columns

Posted: 01 May 2013 02:34 PM PDT

Converting one row as one Column is possible using Pivot. But is there any way to convert particular set of rows to one column?For example, below is the data tableCustomer---------------Product-------------QtyJain --------------- Mango -------------10Charlie --------------- Orange--------------5Rocky --------------- Mango---------------6Rocky ---------------- Orange---------------3Expected Result setCustomer---------------Mango---------------Orange---------------Mango and OrangeJain---------------------10-------------------NULL ---------------------NULL Charlie------------------NULL-------------------5-----------------------NULLRocky -------------------NULL-----------------NULL---------------------- 9 Appreciate any help* ---- are used for identation purpose onlyThank You :-)

Replacing a string when the value is found in a table

Posted: 01 May 2013 04:29 PM PDT

Hi guys, I'm trying to create a function where I can pass a string value that will be used for searching a table column and see if there is match, then replace that string partially when found like below:Passed string: "John Doe University"The column values from the table:SELECT LastName FROM ThisTable--------------------------------LastName---------CruzPatarDoeQuiJongHalalSo if the search is succesful, I'll replace the string with empty string, then the value will be returned as:John UniversityHope it makes sense?Thanks

Making Query SQL 2000 compatable

Posted: 01 May 2013 07:33 AM PDT

I have a query i wrote for 2008 to get the top 3 salesPeople per territory by their total sales. Its not too difficult in 2005/2008 with CTE's and Partitioning however i cannot get this to work in SQL 2000 where there are no CTE's.Id appreciate any help or nudges in the right direction:Query is a mockup of the actual query, columns, tables and data is all fictional:[code="sql"]with ST AS( select ROW_NUMBER() over(partition by TerritoryID order by sum(totalDue) desc) as rowID, TerritoryID, salesPersonID, sum(TotalDue) as salesTotal from [Sales].[SalesOrderHeader] where SalesPersonID is not null group by TerritoryID, SalesPersonID)select territoryID, salesPersonID, SalesTotalfrom STwhere RowID <=3order by territoryID[/code]Thanks!

Hot to Get Cumulative Total..

Posted: 01 May 2013 03:10 AM PDT

Hi Friends...I Having Table For The Following Structure..[code="sql"]Create Table EmpProcessDetail ( EmpId varchar(25), EmpName varchar(50),ProcessName varchar(30),GroupCode varchar(5),PlannedDays int,ActualDays int, DelayedDays int,TotalDelayDays int) insert into EmpProcessDetail values ('AKM_1','Saran','Purchase','A',15,11,4,NULL), ('AKM_1','Saran','Measurement','B',18,15,3,NULL), ('AKM_1','Saran','Cutting','C',12,15,-3,NULL), ('AKM_1','Saran','Stitching','D',20,27,-7,NULL), ('AKM_1','Saran','Ironing','E',14,19,-5,NULL), ('AKM_1','Saran','Packing','F',15,13,2,NULL), ('AKM_1','Saran','Checking','G',18,12,6,NULL), ('AKM_1','Saran','Delivery','H',22,33,-11,NULL), ('AKM_2','Arun','Purchase','A',12,13,-1,NULL), ('AKM_2','Arun','Measurement','B',15,19,-4,NULL), ('AKM_2','Arun','Cutting','C',22,29,-7,NULL), ('AKM_2','Arun','Stitching','D',41,43,-2,NULL), ('AKM_2','Arun','Ironing','E',27,26,-1,NULL), ('AKM_2','Arun','Packing','F',18,13,5,NULL), ('AKM_2','Arun','Checking','G',11,14,-3,NULL), ('AKM_2','Arun','Delivery','H',17,24,-7,NULL), ('AKM_3','Kumar','Purchase','A',41,43,-2,NULL), ('AKM_3','Kumar','Measurement','B',23,26,-3,NULL), ('AKM_3','Kumar','Cutting','C',22,29,-7,NULL), ('AKM_3','Kumar','Stitching','D',41,43,-2,NULL), ('AKM_3','Kumar','Ironing','E',12,13,-1,NULL), ('AKM_3','Kumar','Packing','F',14,19,-5,NULL), ('AKM_3','Kumar','Checking','G',12,14,-2,NULL), ('AKM_3','Kumar','Delivery','H',18,13,5,NULL) select * from EmpProcessDetail[/code]Below I have mentioned The my Required Table Data [code="sql"]EmpId EmpName ProcessName GroupCode PlannedDays ActualDays DelayedDays TotalDelayDays------------------------- -------------------------------------------------- ------------------------------ --------- ----------- ----------- ----------- --------------AKM_1 Saran Purchase A 15 11 4 4AKM_1 Saran Measurement B 18 15 3 7AKM_1 Saran Cutting C 12 15 -3 4AKM_1 Saran Stitching D 20 27 -7 -3AKM_1 Saran Ironing E 14 19 -5 -8AKM_1 Saran Packing F 15 13 2 -6AKM_1 Saran Checking G 18 12 6 0AKM_1 Saran Delivery H 22 33 -11 -11AKM_2 Arun Purchase A 12 13 -1 -1AKM_2 Arun Measurement B 15 19 -4 -5AKM_2 Arun Cutting C 22 29 -7 -12AKM_2 Arun Stitching D 41 43 -2 -14AKM_2 Arun Ironing E 27 26 -1 -15AKM_2 Arun Packing F 18 13 5 -10AKM_2 Arun Checking G 11 14 -3 -3AKM_2 Arun Delivery H 17 24 -7 -10AKM_3 Kumar Purchase A 41 43 -2 -2AKM_3 Kumar Measurement B 23 26 -3 -5AKM_3 Kumar Cutting C 22 29 -7 -12AKM_3 Kumar Stitching D 41 43 -2 -14AKM_3 Kumar Ironing E 12 13 -1 -15AKM_3 Kumar Packing F 14 19 -5 -20AKM_3 Kumar Checking G 12 14 -2 -22AKM_3 Kumar Delivery H 18 13 5 -17[/code][u]My Requirement :[/u]1) I want to be Fill the TotalDelayDays Column..2)The Cumulative Sum Based on the DelayedDays..2) The Cumulative Sum Should be Calculated on EmpID once it will finished for particular EmpID then Cumulative Sum should start from Initial for second EmpID ...Thanks & Regards,Saravanan.D

Trying to improve the vIew performance

Posted: 01 May 2013 05:33 AM PDT

I have which im looking to optimize ...I have tried whatever i could think like Creating indexes, schema binding the view to create an index on the view ([dbo].v_adnetwork..... View in the code), included columns, but to no success. Could any one suggest anything which could help meBelow is how the view looksselect client_name, client_id, web_id,CASE when active_hierarchy.trending_id is null then 1 -- web_id expired else CASE when mapping_is_historical=1 then 2 else 0 end end is_historicalfrom ( select distinct client_name,client_id,web_id, CASE when dbo.CurrentEffectiveDate(-1) between EFFECTIVE_DATE and EXPIRATION_DATE then 0 else 1 end mapping_is_historical from ( select category_id as webster_category_id, trending_id as web_id, map.EFFECTIVE_DATE, map.EXPIRATION_DATE from object_category_map map (nolock), level l (nolock) where category_index between 50000 and 60000 and (object_id=level_id and level_type='SYN' or object_id=level_id and level_type='CUS' and level_level=1 or object_id=level_id and level_type='ALT' and right(level_name,3)<>'_AR' ) ) entity_client_map, ( select category_id as webster_category_id, CATEGORY_NAME as client_name, CATEGORY_DESCRIPTION as client_id from category nolock ) client_defintion where client_defintion.webster_category_id=entity_client_map.webster_category_id and client_defintion.webster_category_id=entity_client_map.webster_category_id ) all_client_web_mapping_active_and_expired left outer join ( select trending_id, case when hierarchy_id is null then syn.level_type else other_type.level_type end as level_type from level syn (nolock) left outer join ( select dc_hierarchy_id as hierarchy_id,'WGG' as level_type from [dbo].v_distributed_content (nolock) union select adn_hierarchy_id as hierarchy_id,'ADN' as level_type from [dbo].v_adnetwork (nolock) ) other_type on level_group_id=hierarchy_id where dbo.CurrentEffectiveDate(-1) between syn.EFFECTIVE_DATE and syn.EXPIRATION_DATE ) active_hierarchyon all_client_web_mapping_active_and_expired.web_id = active_hierarchy.trending_idThanks

Combine date from two different date ranges in one dataset

Posted: 01 May 2013 04:26 AM PDT

Hello all,I have the following dataset that I am using:select HourOfTheDay ,sum(NumberOfOrdersInBatch) as NumberOfOrders ,AVG(TimeToProcess) as AverageTimefrom info.batchstatsTEST with (nolock)where TimeReceived between GETDATE()-1 and GETDATE()group by HourOfTheDayWhat I want to do is add the AverageTime from a different date range, say between GETDATE()-8 and GETDATE()-7. So what I am looking for is a new column in the dataset called AverageTime2 that shows me the previous week. How can I get this in the same dataset?Thanks!

problem with join T-Sql

Posted: 11 Feb 2013 06:40 AM PST

i have customer table and it have 1 foreign key from other table in it then i have service table that customerID(foreign key) have relation with it.i want to show all custmer with last call,sum of payed service,last payed service,Liability of customer(acourding to getKeraye(bit) field),number of service of customer from service table.i write this query but it have wrong info from service and duplicate info.i give all table creation script with insert test data [code="sql"]/****** all table creation script with insert test data ******//****** Object: Table [dbo].[CustomerColor] Script Date: 02/12/2013 11:34:53 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[CustomerColor]( [CustomerColorID] [tinyint] IDENTITY(1,1) NOT NULL, [CustomerSubject] [nvarchar](50) NOT NULL, [Color] [nvarchar](50) NOT NULL, CONSTRAINT [PK_CustomerColor] PRIMARY KEY CLUSTERED ( [CustomerColorID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [dbo].[Customer] Script Date: 02/12/2013 11:34:53 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(10,1) NOT NULL, [EshterakTypeID] [tinyint] NOT NULL, [MahaleID] [int] NULL, [KnowID] [smallint] NULL, [CustomerColorID] [tinyint] NULL, [FullName] [nvarchar](50) NOT NULL, [Bestankary] [int] NOT NULL, [Etebar] [int] NOT NULL, [Address] [nvarchar](250) NOT NULL, [Telephone1] [varchar](11) NOT NULL, [Telephone2] [varchar](11) NULL, [Telephone3] [varchar](11) NULL, [Mobile] [varchar](11) NULL, [CustomerSabtDate] [datetime] NULL, [Describtion] [nvarchar](500) NULL, [GetService] [bit] NOT NULL, [PardakhtType] [bit] NOT NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [CustomerID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Customer] UNIQUE NONCLUSTERED ( [FullName] ASC, [Telephone1] 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 ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[Customer] ONINSERT [dbo].[Customer] ([CustomerID], [EshterakTypeID], [MahaleID], [KnowID], [CustomerColorID], [FullName], [Bestankary], [Etebar], [Address], [Telephone1], [Telephone2], [Telephone3], [Mobile], [CustomerSabtDate], [Describtion], [GetService], [PardakhtType]) VALUES (12, 1, 2, 3, NULL, N'اکبری', 1200, 0, N'شیراز 1', N'8343513', N'', N'', N'09362928936', CAST(0x0000A13600000000 AS DateTime), N'توضیحات1 ', 1, 1)INSERT [dbo].[Customer] ([CustomerID], [EshterakTypeID], [MahaleID], [KnowID], [CustomerColorID], [FullName], [Bestankary], [Etebar], [Address], [Telephone1], [Telephone2], [Telephone3], [Mobile], [CustomerSabtDate], [Describtion], [GetService], [PardakhtType]) VALUES (13, 1, 2, 3, NULL, N'توکلی', 0, 0, N'شیراز', N'8353313', N'', N'', N'09362928936', CAST(0x0000A13600000000 AS DateTime), N'توضیحات', 1, 1)INSERT [dbo].[Customer] ([CustomerID], [EshterakTypeID], [MahaleID], [KnowID], [CustomerColorID], [FullName], [Bestankary], [Etebar], [Address], [Telephone1], [Telephone2], [Telephone3], [Mobile], [CustomerSabtDate], [Describtion], [GetService], [PardakhtType]) VALUES (15, 1, 2, 3, NULL, N'رحمانی', 0, 0, N'شیراز', N'8343313', N'', N'', N'09362928936', CAST(0x0000A13400000000 AS DateTime), N'توضیحات', 1, 1)SET IDENTITY_INSERT [dbo].[Customer] OFF/****** Object: Table [dbo].[Service] Script Date: 02/12/2013 11:34:53 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Service]( [ServiceID] [bigint] IDENTITY(1,1) NOT NULL, [CustomerID] [int] NOT NULL, [DriverID] [int] NOT NULL, [khadamatID] [smallint] NULL, [EndUserName] [nvarchar](20) NOT NULL, [FactorEditDelID] [smallint] NULL, [Mabda] [nvarchar](250) NULL, [Maghsad] [nvarchar](250) NOT NULL, [SendTime] [datetime] NULL, [ReturnTime] [datetime] NULL, [Describtion] [nvarchar](500) NULL, [SabtDate] [datetime] NULL, [GetPorsant] [bit] NOT NULL, [DakhelShahr] [bit] NULL, [Gheymat] [int] NULL, [GetKeraye] [bit] NOT NULL, [Editor] [nvarchar](20) NULL, [EditDate] [datetime] NULL, CONSTRAINT [PK_Service] PRIMARY KEY CLUSTERED ( [ServiceID] 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].[Service] ONINSERT [dbo].[Service] ([ServiceID], [CustomerID], [DriverID], [khadamatID], [EndUserName], [FactorEditDelID], [Mabda], [Maghsad], [SendTime], [ReturnTime], [Describtion], [SabtDate], [GetPorsant], [DakhelShahr], [Gheymat], [GetKeraye], [Editor], [EditDate]) VALUES (4, 12, 12, 2, N'SA', 1, N'شیراز', N'شیراز2', CAST(0x0000A1600046DCA0 AS DateTime), CAST(0x0000A16000BDC9F0 AS DateTime), N'', CAST(0x0000A15B011CAD84 AS DateTime), 1, 1, 120000, 1, N'SA', CAST(0x0000A16100F09B0D AS DateTime))INSERT [dbo].[Service] ([ServiceID], [CustomerID], [DriverID], [khadamatID], [EndUserName], [FactorEditDelID], [Mabda], [Maghsad], [SendTime], [ReturnTime], [Describtion], [SabtDate], [GetPorsant], [DakhelShahr], [Gheymat], [GetKeraye], [Editor], [EditDate]) VALUES (5, 15, 13, NULL, N'SA', 1, N'شیراز', N'گلستان', CAST(0x0000A15C00AF3B60 AS DateTime), CAST(0x0000A16100B54640 AS DateTime), N'dsfsd', CAST(0x0000A15B011D6CD6 AS DateTime), 1, 1, 45000, 1, N'SA', CAST(0x0000A16100F17462 AS DateTime))SET IDENTITY_INSERT [dbo].[Service] OFF/****** Object: ForeignKey [FK_Service_Customer] Script Date: 02/12/2013 11:34:53 ******/ALTER TABLE [dbo].[Service] WITH CHECK ADD CONSTRAINT [FK_Service_Customer] FOREIGN KEY([CustomerID])REFERENCES [dbo].[Customer] ([CustomerID])GOALTER TABLE [dbo].[Service] CHECK CONSTRAINT [FK_Service_Customer]GO/****** Object: ForeignKey [FK_Customer_CustomerColor] Script Date: 02/12/2013 11:34:53 ******/ALTER TABLE [dbo].[Customer] WITH CHECK ADD CONSTRAINT [FK_Customer_CustomerColor] FOREIGN KEY([CustomerColorID])REFERENCES [dbo].[CustomerColor] ([CustomerColorID])GOALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_CustomerColor]GO/****** query ******/select distinct customer.*,CustomerColor.Color,CustomerColor.CustomerSubject,b.AkharinKeraye,b.Karkard,b.Lcallfrom customerleft outer join CustomerColor on Customer.CustomerColorID=CustomerColor.CustomerColorID,(select [Service].CustomerID,max([Service].SabtDate) as Lcall,SUM(Service.Gheymat) as Karkard,(select s.Gheymat from [Service] as s where s.SabtDate=max([Service].SabtDate)) as AkharinKerayefrom [Service] group by [Service].CustomerID )border by Customer.CustomerID,Customer.FullName [/code]

Comparing figures based on relative date

Posted: 10 Feb 2013 11:15 PM PST

Hi AllI'm having trouble working out a solution to the following issue:I have a table containing a date and a metric I wish to add another field which is based on the existing metric but shows the figure as at 12 months ago side by side with my current figure.I want to store it on the table so I need code that can dynamically allocate the 12 months previous figure to the relative column. I have started with a cursor (it's been a while!) but have hit a brick wall, in theory or in my head it seems simple but I'm not sure if I'm over complicating the task or i've simply looked at it too long and am being thick.The cursor below returns the corrent figure I need but I then require to add it to the correct line(this is where I'm struggling). Any advice appreciated [code="other"]CREATE TABLE #mytable ( P_KEY INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, LAST_DAY_OF_MONTH DATETIME, Value int , PreviousYearValue INT )INSERT INTO #mytableVALUES('20130131',20,NULL)INSERT INTO #mytableVALUES('20120131',5,NULL)INSERT INTO #mytableVALUES('20130331',40,NULL)INSERT INTO #mytableVALUES('20120331',2,NULL)DECLARE @DATE DATETIME DECLARE @TWLEVE_MONTHS_PREVIOUS DATETIME DECLARE @MEASURE INTDECLARE @P_KEY INTDECLARE db_cursor CURSOR FOR SELECT P_KEY,LAST_DAY_OF_MONTH FROM #mytableOPEN db_cursor FETCH NEXT FROM db_cursor INTO @P_KEY,@DATE WHILE @@FETCH_STATUS = 0 BEGIN SET @TWLEVE_MONTHS_PREVIOUS = dateadd(month, datediff(month, -1, @DATE) - 12, -1) FETCH NEXT FROM db_cursor INTO @P_KEY,@DATE SELECT P_KEY,Value FROM #mytable WHERE LAST_DAY_OF_MONTH =@TWLEVE_MONTHS_PREVIOUS END CLOSE db_cursor DEALLOCATE db_cursor [/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog