InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 15:06:34 GMT20http://www.sqlservercentral.com/Forums/Topic1454605-392-1.aspxHiI’ve a CustomerActivity table that records all the activities of the consumers.I've create a simplyfied version of the table with the activities of just two members as below:USE [Sample]GOANSI_PADDING ONGO-- Create a ConsumerActivityTableCREATE TABLE [dbo].[ConsumerActivity]( [ConsumerID] [varchar](10) NOT NULL, [ActivityDate] [datetime] NULL, [Status] [varchar](10) NULL, [Category] [varchar](10) NULL, [PointsEarned] [int] NULL, [PointsUsed] [int] NULL, [Description] [varchar](100) NULL) ON [PRIMARY] GO-- Insert some sample data into this activity tableGOINSERT INTO dbo.ConsumerActivity (ConsumerID, ActivityDate, [Status], Category, PointsEarned, PointsUsed,[Description])SELECT '101', '2013-05-10 00:00:00.000','ACTIVE','PREMIUM',NULL,'500','Upgrage to PREMIUM class'UNION ALLSELECT '101', '2013-05-07 00:00:00.000','ACTIVE','GENERAL',NULL,'-200','Points used'UNION ALLSELECT '101', '2013-02-10 00:00:00.000','ACTIVE','GENERAL','200',NULL,'General purchase'UNION ALLSELECT '101', '2012-10-12 00:00:00.000','ACTIVE','GENERAL','200','100','First time purchase with bonus points'UNION ALLSELECT '102', '2013-05-10 00:00:00.000','ACTIVE','GENERAL','600',NULL,'Account Reactivated'UNION ALLSELECT '102','2013-01-10 00:00:00.000','INACTIVE','GENERAL','600','-600','Account DEACTIVATED and the remaining points are adjusted'UNION ALLSELECT '102','2012-10-20 00:00:00.000','ACTIVE','GENERAL','300',NULL,'General Purchase'UNION ALLSELECT '102','2012-11-20 00:00:00.000','ACTIVE','GENERAL',NULL,'-200','Points used'UNION ALLSELECT '102','2012-09-20 00:00:00.000','ACTIVE','GENERAL','400','100','New purchase with bonus points'GO-- SELECT * from this table to see how the data looks currentlyGOSELECT * FROM dbo.ConsumerActivityORDER BY ConsumerID, ActivityDate DESCGONow, I need to write a query that gives me the “TOTAL COUNT” of ACTIVE customers and their “TOTAL Remaining Points (Rewards + Bonus points)”, for each DISTINCT group of “Category” as of @ReportDate(parameter). All these should be based on the latest STATUS and latest CATEGORY on a specified @ReportDate parameter.Here are the two scenarios of how I want to see the result from the given sample:• If @ReportDate = ‘05/15/2013’ Category TotalActiveConsumerCount TotalRemainingPointsPremium 1 800General 1 600Here, for all the consumers whose accounts has been REACTIVATED, we only use the points that they’ve gathered after they becomes ACTIVE again. The latest CATEGORY of consumerID 101 on the specified date is PREMIMUM.• If @ReportDate = ‘04/15/2013’ConsumerType TotalActiveConsumerCount TotalRemainingPointsPremium 0 0General 1 500Here, the latest Category of consumerID 101 on the specified date is GENERAL. The latest status of ConsumerID 102 on the specified date is INACTIVE and thus we don’t need to count this consumer at all.Thanks for your help in advanceMon, 20 May 2013 09:53:18 GMTsql1411http://www.sqlservercentral.com/Forums/Topic1456107-392-1.aspxI have data that looks like the following:Sales_2012 Plan fiscal_week_2013 Sales_2013 LY PN232.2935700 286.8642 2013-01-05 263.1448400 0.132811553931518638 -0.0826852665251378237.2016700 286.8642 2013-01-12 258.2310900 0.088656289814485707 -0.0998144463016142232.0180700 286.8642 2013-01-19 264.8221900 0.141386056698083903 -0.0768380765586006238.2087000 286.8642 2013-01-26 266.3660400 0.118204498828128443 -0.0714562634427699253.8050500 297.0714 2013-02-02 267.9389200 0.055687899039045913 -0.0980658655463566But I need the results to look like:Sales 1/5 1/12 1/19 1/26 2/2 2/9 2/16 2/232012 213 216 225 241 246 247 241 265Plan 230 233 243 260 266 267 260 2862013 217 222 226 243 250 251 261 271% to LY 101.9% 102.8% 100.4% 100.8% 101.6% 101.6% 108.3% 102.3%% to PN 94.3% 95.2% 93.0% 93.4% 94.1% 94.1% 100.3% 94.7%How can I get my data to be look like this. I need a combination of UnPivot and Pivot - but my dates vary and I don't want an IN statement with hardcoded dates. I need dynamic but not sure how to build this. Please help.Thu, 23 May 2013 10:55:44 GMTJOHNSON4INFOhttp://www.sqlservercentral.com/Forums/Topic1455982-392-1.aspxHi Guys,I'm designing a report in SSRS,I need to pull that from DB from previous Friday till the end of the current Friday.this is what I have on WHERE clause dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())Please assistThe whole Query:select distinct Top 6 Count(c.ref_num) as Logged, p.sym AS [Category] -- into #TempCat from call_req c, ca_contact cn, ca_contact cn2, ca_contact cn3, cr_stat st, view_group v, prob_ctg p WITH (NOLOCK), act_log al where c.type = 'I' and c.assignee *= cn.contact_uuid and c.customer = cn2.contact_uuid and al.analyst = cn3.contact_uuid and c.status = st.code and c.group_id *= v.contact_uuid and c.persid = al.call_req_id and c.category = p.persid and dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate()) Group by p.sym order by count(c.ref_num) descThu, 23 May 2013 06:50:35 GMTGOODShttp://www.sqlservercentral.com/Forums/Topic1455981-392-1.aspxDECLARE @EmpID nvarchar(max) = ' 'DECLARE @sql nvarchar(max)SELECT @EmpID = @EmpID + '['+Convert(nvarchar(max),EmpID) +']'FROM (SELECT DISTINCT EmpID FROM emp.teamassignments) AS StaffListSET @EmpID = STUFF(@EmpID,1,1,'')PRINT @EmpIDSET @sql = 'SELECT * FROM emp.teamassignments ' +'PIVOT TEAM FOR EmpID IN ''['+@EmpID+']'' AS P'EXECUTE sp_executesql @sqlI am trying to pivot at get a listing of my EmpID's going down and their respective teams across.Some belong to 1 team others may belong to 5 or more.I have this: EmpID TEAM1 Stripes1 Orange1 Green2 Blue3 Sales3 ExecI would like this: EmpID TEAM_1 TEAM_2 TEAM_31 Stripes Orange Green2 Blue None None3 Sales Exec NoneThu, 23 May 2013 06:50:02 GMTccmrethttp://www.sqlservercentral.com/Forums/Topic1455473-392-1.aspxHi all,I have a problem with a trigger and I hope some expert can help me on this ...This is the trigger[code="other"]ALTER TRIGGER [dbo].[updt_order_date] ON [dbo].[FUND_MANAGEMENT_DETAILS]after insert AS BEGIN set nocount on declare @dt datetime select @dt = getdate() update fmd set order_date = (case f.pricing_frequency when 2 then -- weekly (select min(c.dt) from calendar cc, calendar c where 1 = 1 and cc.dt = @dt and c.dw_lissia = f.PRICING_DAY and c.id_fisa_bsn_day >= cc.id_fisa_bsn_day + f.FWD_PRICE_REPORT_DAYS ) else '' end) from FUND_MANAGEMENT_DETAILS fmd inner join inserted i on i.FMD_ID = fmd.FMD_ID inner join FUNDS f on f.FDS_ID = fmd.FUNDEND [/code]This code doesn't work! The case "when 2" is never executed although it should be for some rows.However, if I rem out all reference to calendar cc, which gives following code, it works; meaning the results are wrong but the "case 2 " gets executed.[code="other"]ALTER TRIGGER [dbo].[updt_order_date] ON [dbo].[FUND_MANAGEMENT_DETAILS]after insert AS BEGIN update fmd set order_date = (case f.pricing_frequency when 2 then -- weekly (select min(c.dt) from --calendar cc, calendar c where 1 = 1 --and cc.dt = @dt and c.dw_lissia = f.PRICING_DAY and c.id_fisa_bsn_day >= 98 + f.FWD_PRICE_REPORT_DAYS ) else '' end) from FUND_MANAGEMENT_DETAILS fmd inner join inserted i on i.FMD_ID = fmd.FMD_ID inner join FUNDS f on f.FDS_ID = fmd.FUNDEND [/code]I tried many scenarii since yesterday, but none of them succeeds and to be honest I run against the wall now.I'd be grateful if anybody with a good knowledge about triggers could help me.ThanksrvEDIT: to paste missing codeWed, 22 May 2013 06:53:42 GMTrot-717018http://www.sqlservercentral.com/Forums/Topic1455809-392-1.aspxHI,I have a table, SampleNums, containing a couple of int columns that I need to concatenate into one column. When the columns are stored as varchar datatype my below ddl works, but when I recreate the SampleNums table with num1 and num2 as int columns, the concatenation doesn't work.[code="plain"]create table SampleNums (SomeID int, num1 varchar(4) null, num2 varchar(4) null)insert into SampleNumsvalues(1, 858, 621),(2, 597, 425),(3, 683, 840),(4, NULL, 333),(5, 444, NULL);select * from SampleNums--drop table SampleNums;select SomeID, NewNum = case when cast(num1 as varchar(10)) is null then NUM2 when cast(num2 as varchar(10)) is null then NUM1else cast(num1 as varchar(10)) + '.' + cast(num2 as varchar(4)) endfrom SampleNums---DESIRED OUTPUTselect '1', '858.621' UNION ALLselect '2','597.425' UNION ALLselect '3', '683.840' UNION ALLselect '4', '333' UNION ALLselect '5', '444' [/code]but with everything the same, except that Num1 and Num2 have int datatypes (use drop ddl and run create sequence again with datatype int) I get this error:--with datatype as intMsg 245, Level 16, State 1, Line 2Conversion failed when converting the varchar value '858.621' to data type int.Also, if I recreate and change datatypes to numeric, I get the following output. It is strange that line 1 and 3 have increased in value by 1:[code="plain"]--with datatype as numericselect '1', '859' UNION ALLselect '2','597' UNION ALLselect '3', '684' UNION ALLselect '4', '333' UNION ALLselect '5', '444' [/code]I would like conditional statements to work regardless of int/numeric/varchar datatype. Why, when I am handling for the string concatenation using CAST is it breaking? How to fix? Thanks!!!!Wed, 22 May 2013 23:42:08 GMTKoldCoffeehttp://www.sqlservercentral.com/Forums/Topic1455823-392-1.aspxHai Friends , I m creating one web application in that input fileds are departuredate,from_place,To_place,travel mode.My condition for insertion when i choose to enter departuredate(input date) is always greater than already appeared date on database date.create table journey(departuredate datetime,from_place varchar(50),to_place varchar(50),travel mode nvarchar(50))insert into journey values ('20-05-2013','cdsfs','dhf','Train')insert into journey values ('21-05-2013','cds','dh','Car')insert into journey values ('22-05-2013','cfs','df','Bus')My procedurecode:create procedure jack(@departuredate datetime,@from_place varchar(50),@to_place varchar(50),@travelmode nvarchar(10))as begin if exists('select departuredate from journey where departuredate<@departuredate')print 'Must choose greater date of departuredate'endelsebegininsert into journey (departuredate,from_place,to_place,travel mode) values(@depaturedate,@from_place,@to_place ,@travelmode,)endend/these query shows exceuted successfully,but it was not woking any body suggest me Thu, 23 May 2013 00:15:16 GMTraghuldraghttp://www.sqlservercentral.com/Forums/Topic1455571-392-1.aspxI altered a sql script the other day to handle for a column containing NULLs. Basically, in the case of a NULL I wanted the value 'Other' to take its' place. When I gave the condition = NULL, the NULLs remained. When I gave the condition IS NULL then the NULLS were replaced with the value 'Other' as was desirable.So far I have only come up with the following link when googling: http://www.sqlservercentral.com/articles/T-SQL/understandingthedifferencebetweenisnull/871/ and it refers to setting variables to NULL and memory allocation.Can someone explain difference between IS NULL and = NULL when using conditional statements?Wed, 22 May 2013 09:12:54 GMTKoldCoffeehttp://www.sqlservercentral.com/Forums/Topic1455894-392-1.aspxMorning folks,I'm a bit stumped here. The issue I've got is syntax related I'm sure.Essentially I want to run the results of an Stored Proc into a variableThe error that I'm getting is:Incorrect syntax near the keyword 'EXEC'[code]DECLARE @Cols as nvarchar(max)SET @Cols = 'SELECT @ColsOut = EXEC DB..sp_1 ''DB..Tmp_LkUp_Tbl'' , NULL , B, ''MOP'',''TTL_Cnt'' 'EXEC sp_Executesql @Cols, N'@ColsOut varchar(max) Out', @cols OUTPUTPRINT @Cols[/code]Any help would be greatly appreciated as this is starting to frustrate me somewhatThanksThu, 23 May 2013 02:54:15 GMTniquem1974http://www.sqlservercentral.com/Forums/Topic1455892-392-1.aspxcreate table dbo.#abc([name] varchar(100),id int)insert into dbo.#abc([name],id)select 'Animal',100unionselect 'beer',100unionselect 'Tree',200unionselect 'Olive',200I want to have resultset asanimal.beertree.Olive.Thu, 23 May 2013 02:52:52 GMTscottichrosaviakosmoshttp://www.sqlservercentral.com/Forums/Topic1455623-392-1.aspxI'm trying to join 3 tables. I can do either one of these lines individually but not both together. Any ideas what I'm missing? The 3 tables are examination, preliminary_exam and pathology. from ((examination as b inner join examination as pre on b.related_exam_id = pre.id) left join preliminary_exam on pre.procedure_id = preliminary_exam.id)((examination as b2 inner join examination as pat on b2.related_exam_id = pat.id) left join pathology on pat.procedure_id = pathology.id)Thanks very much.Wed, 22 May 2013 11:22:53 GMTDenise McMillanhttp://www.sqlservercentral.com/Forums/Topic1454770-392-1.aspxThanks in advance if you can offer any assistance.I have a table of ranked cities-#CitiesI have a table called #CityGroups with a RankStart and RankEnd field. Example, GroupA should contain the top 3 ranked cities.I have a table #CityGroupmembers which I periodically need to refresh as the data in table #Cities is updated.This would be the classic MERGE scenario but table #CityGroupmembers is accessed through a linked server, so I need to generate an insert and a delete statement based on existence checks, that is I can not delete all members of a group and then repopulate in its entirety.I would like to include ties if there are ties in table #Cities, not shown in my sample data.I guess the rub (at least for me) is how do I extract the rankings from table #CityGroups and use them in my insert and delete statements.I would strongly prefer to not use dynamic SQL. [code]CREATE TABLE #Cities( City varchar (20), CityRank int)INSERT INTO #CitiesSELECT 'Paris',1 UNIONSELECT 'Chicago',2 UNIONSELECT 'Seattle',3 UNIONSELECT 'Hong Kong',4 UNIONSELECT 'New York',5 UNIONSELECT 'Brasilia',6 UNIONSELECT 'Tangier',7 UNIONSELECT 'Berlin',8 UNIONSELECT 'Tokyo',9 UNIONSELECT 'Mexico City',10 CREATE TABLE #CityGroups( CityGroup varchar(10), CityDescription varchar(15), RankStart int, RankEnd int)INSERT INTO #CityGroups SELECT 'GroupA','Top 3 Cities',1,3 UNIONSELECT 'GroupB','Top 10 Cities',1,10 UNIONSELECT 'GroupC','Middle Eight',2,9 CREATE TABLE #CityGroupmembers( CityGroup varchar(10), City varchar (20))--The inserts I would be trying to generate--These are obviously not getting the rankings from the #CityGroups table in any dynamic fashionINSERT INTO #CityGroupmembersSELECT TOP 3 'GroupA', City FROM #Cities ORDER BY CityRank INSERT INTO #CityGroupmembersSELECT TOP 10 'GroupB', City FROM #Cities ORDER BY CityRankINSERT INTO #CityGroupmembersSELECT 'GroupC', CityFROM #CitiesWHERE CityRank BETWEEN 2 AND 9 SELECT * FROM #Cities ORDER BY CityRankSELECT * FROM #CityGroupsSELECT * FROM #CityGroupmembersDROP TABLE #CitiesDROP TABLE #CityGroupsDROP TABLE #CityGroupmembers--Updated list for testing/*INSERT INTO #CitiesSELECT 'Montreal',1 UNIONSELECT 'Chicago',2 UNIONSELECT 'Mexico City',3 UNIONSELECT 'San Diego',4 UNIONSELECT 'New York',5 UNIONSELECT 'Brasilia',6 UNIONSELECT 'Paris',7 UNIONSELECT 'Berlin',8 UNIONSELECT 'Tokyo',9 UNIONSELECT 'Hong Kong',10 */[/code]Mon, 20 May 2013 16:19:29 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1455678-392-1.aspxI need to figure out how to do a group by on this query by number of days between start and end date. Can someone point me in the right directions. Thanks!select plantfilenumber,(select min(cc.lasteventdate) from tbltitlevault_history c inner join tbltitlevault cc on c.tvid = cc.tvid where c.tvid = a.tvid and c.eventid = '1') as startDate,(select max(dd.lasteventdate) from tbltitlevault_history d inner join tbltitlevault dd on d.tvid = dd.tvid where d.tvid = a.tvid and d.eventid = '6') as endDatefrom tbltitlevault a inner join tbltitlevault_history b on a.tvid = b.tvidWed, 22 May 2013 13:57:23 GMTtgomezhttp://www.sqlservercentral.com/Forums/Topic1454731-392-1.aspxI have a scenario here I have been wrestling over off and on for weeks.We have to tables, one lists Classes, and one lists Entry Codes for certain classes.[code="sql"]DECLARE @Class TABLE (ClassID char(8), CourseID varchar(10), ClassCapacity int)INSERT INTO @Class (ClassID, CourseID, ClassCapacity)SELECT '3520B014','ENGL&101',25UNION ALLSELECT '3525B014','ENGL&101',25UNION ALLSELECT '3530B014','ENGL&101',25DECLARE @EntryCode TABLE (EntryCodeID varchar(9), CourseID varchar(10))INSERT INTO @EntryCode (EntryCodeID, CourseID)SELECT '10515B014','ENGL&101'UNION ALLSELECT '10594B014','ENGL&101'UNION ALLSELECT '10625B014','ENGL&101'UNION ALLSELECT '10727B014','ENGL&101'UNION ALLSELECT '10736B014','ENGL&101'UNION ALLSELECT '10790B014','ENGL&101'UNION ALLSELECT '11092B014','ENGL&101'UNION ALLSELECT '11228B014','ENGL&101'UNION ALLSELECT '11627B014','ENGL&101'UNION ALLSELECT '11826B014','ENGL&101'UNION ALLSELECT '12562B014','ENGL&101'UNION ALLSELECT '12767B014','ENGL&101'UNION ALLSELECT '12777B014','ENGL&101'UNION ALLSELECT '12936B014','ENGL&101'UNION ALLSELECT '13229B014','ENGL&101'UNION ALLSELECT '13451B014','ENGL&101'UNION ALLSELECT '13478B014','ENGL&101'UNION ALLSELECT '13778B014','ENGL&101'UNION ALLSELECT '13907B014','ENGL&101'UNION ALLSELECT '13977B014','ENGL&101'UNION ALLSELECT '14004B014','ENGL&101'UNION ALLSELECT '14010B014','ENGL&101'UNION ALLSELECT '14134B014','ENGL&101'UNION ALLSELECT '14261B014','ENGL&101'UNION ALLSELECT '14273B014','ENGL&101'UNION ALLSELECT '14311B014','ENGL&101'UNION ALLSELECT '14463B014','ENGL&101'UNION ALLSELECT '15060B014','ENGL&101'UNION ALLSELECT '15741B014','ENGL&101'UNION ALLSELECT '15787B014','ENGL&101'UNION ALLSELECT '15892B014','ENGL&101'UNION ALLSELECT '16148B014','ENGL&101'UNION ALLSELECT '16374B014','ENGL&101'UNION ALLSELECT '16673B014','ENGL&101'UNION ALLSELECT '17067B014','ENGL&101'UNION ALLSELECT '17162B014','ENGL&101'UNION ALLSELECT '17201B014','ENGL&101'UNION ALLSELECT '17273B014','ENGL&101'UNION ALLSELECT '17396B014','ENGL&101'UNION ALLSELECT '17624B014','ENGL&101'UNION ALLSELECT '17659B014','ENGL&101'UNION ALLSELECT '17723B014','ENGL&101'UNION ALLSELECT '17894B014','ENGL&101'UNION ALLSELECT '18158B014','ENGL&101'UNION ALLSELECT '18291B014','ENGL&101'UNION ALLSELECT '18308B014','ENGL&101'UNION ALLSELECT '18387B014','ENGL&101'UNION ALLSELECT '18917B014','ENGL&101'UNION ALLSELECT '19046B014','ENGL&101'UNION ALLSELECT '20037B014','ENGL&101'UNION ALLSELECT '20188B014','ENGL&101'UNION ALLSELECT '20204B014','ENGL&101'UNION ALLSELECT '20297B014','ENGL&101'UNION ALLSELECT '20446B014','ENGL&101'UNION ALLSELECT '20526B014','ENGL&101'UNION ALLSELECT '20621B014','ENGL&101'UNION ALLSELECT '20651B014','ENGL&101'UNION ALLSELECT '20963B014','ENGL&101'UNION ALLSELECT '21088B014','ENGL&101'UNION ALLSELECT '21415B014','ENGL&101'UNION ALLSELECT '21517B014','ENGL&101'UNION ALLSELECT '21969B014','ENGL&101'UNION ALLSELECT '21983B014','ENGL&101'UNION ALLSELECT '22154B014','ENGL&101'UNION ALLSELECT '22514B014','ENGL&101'UNION ALLSELECT '22535B014','ENGL&101'UNION ALLSELECT '22552B014','ENGL&101'UNION ALLSELECT '22558B014','ENGL&101'UNION ALLSELECT '22598B014','ENGL&101'UNION ALLSELECT '23037B014','ENGL&101'UNION ALLSELECT '23397B014','ENGL&101'UNION ALLSELECT '23531B014','ENGL&101'UNION ALLSELECT '23567B014','ENGL&101'UNION ALLSELECT '24236B014','ENGL&101'UNION ALLSELECT '24382B014','ENGL&101'UNION ALLSELECT '24617B014','ENGL&101'UNION ALLSELECT '24711B014','ENGL&101'UNION ALLSELECT '24853B014','ENGL&101'UNION ALLSELECT '24963B014','ENGL&101'UNION ALLSELECT '25151B014','ENGL&101'UNION ALLSELECT '25169B014','ENGL&101'UNION ALLSELECT '25939B014','ENGL&101'UNION ALLSELECT '26015B014','ENGL&101'UNION ALLSELECT '26056B014','ENGL&101'UNION ALLSELECT '26147B014','ENGL&101'UNION ALLSELECT '26273B014','ENGL&101'UNION ALLSELECT '26560B014','ENGL&101'UNION ALLSELECT '26891B014','ENGL&101'UNION ALLSELECT '27035B014','ENGL&101'UNION ALLSELECT '27129B014','ENGL&101'UNION ALLSELECT '27448B014','ENGL&101'UNION ALLSELECT '27464B014','ENGL&101'UNION ALLSELECT '28025B014','ENGL&101'UNION ALLSELECT '28068B014','ENGL&101'UNION ALLSELECT '28101B014','ENGL&101'UNION ALLSELECT '28136B014','ENGL&101'UNION ALLSELECT '28873B014','ENGL&101'UNION ALLSELECT '28895B014','ENGL&101'UNION ALLSELECT '28993B014','ENGL&101'UNION ALLSELECT '29228B014','ENGL&101'UNION ALLSELECT '29558B014','ENGL&101'UNION ALLSELECT '29680B014','ENGL&101'UNION ALLSELECT '29711B014','ENGL&101'UNION ALLSELECT '29715B014','ENGL&101'UNION ALLSELECT '29840B014','ENGL&101'[/code]CourseID is the join key between these two tables. EntryCodeID is the code itself.What we would like to do is select ClassCapacity + 10 entry codes to each ClassID. The EntryCodeIDs must be unique, must not overlap into other ClassIDs.Is there a way accomplish this without a cursor? I’m thinking a CTE might work, but I have never been able to completely wrap my head around their syntax.Suggestions? Running on SQL 2008 R2.Thank you in advance!MikeMon, 20 May 2013 14:24:24 GMTpetrimdhttp://www.sqlservercentral.com/Forums/Topic1455273-392-1.aspxCreate Table Test1( ID INT, VALUE VARCHAR(20), MAXVAL VARCHAR(20), VALUETYPE INT, OutCome int) --select * from Test1 INSERT INTO Test1VALUES ('1','FirstValue','140/90','1','4')INSERT INTO Test1VALUES ('2','SecondValue','140/90','1','4')INSERT INTO Test1VALUES ('3','ThirdValue',null,'4','3')INSERT INTO Test1VALUES ('4','FourthValue',null,'4','3') Create Table Test2( ID INT, MAXVAL VARCHAR(20), MINVAL VARCHAR(20), Type int) INSERT INTO Test2VALUES ('1','139','89','1')INSERT INTO Test2VALUES ('2','141','95','1')INSERT INTO Test2VALUES ('3','141','95','4') Select * from test1 t1 Inner Join Test2 t2 ON T1.ID = T2.ID WHERE ((SUBSTRING(isnull(t1.MaxVal,''),1,CHARINDEX('/',ISNULL(T1.MAXVAL,''))-1))> T2.MAXVAL AND LTRIM(RIGHT(ISNULL(t1.MaxVal,''), CHARINDEX('/', ISNULL(t1.MaxVal,'') + '/')-2) ) > T2.MINVAL) or (T1.VALUETYPE <= T2.Type) Here is the error that I am getting“Invalid length parameter passed to the LEFT or SUBSTRING function”Please Advice.Thanks in advance.Tue, 21 May 2013 19:07:21 GMTrocky_498http://www.sqlservercentral.com/Forums/Topic1455541-392-1.aspxIm running the following in a batch filesqlcmd -Q "exec dbo.MySproc" -S "MyServer" -d "MyDatabase"and Im getting the following error:Named Pipes Provider: Could not open a connection to SQL Server [121]Any idea what Im doing wrong?Wed, 22 May 2013 08:18:22 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic1454767-392-1.aspxI have a table with data that looks as below...[img]http://www.sqlservercentral.com/Forums/Attachment13718.aspx[/img]Each CaseID has multiple records in the table. Each of these rows is expected to have same data in all columns except "CreatedDate". However, in certain cases the StudentID may be different from the other row(s) of the same CaseID. I need to report data for all such cases. Additionally, the output needs to be arranged in the order of caseID with highest created date. i.e., in the result set all records belonging to the CaseID which was modified most recently should appear at the top.Here is the sample of how the desired result set should look like...[img]http://www.sqlservercentral.com/Forums/Attachment13719.aspx[/img]Following is the code snippet to create sample data[code="sql"]DECLARE @mySampleTable TABLE ( CaseID INT, StudentID VARCHAR (20), StudentSocial VARCHAR (11), StudentName VARCHAR (50), CreatedDate DateTime2 )INSERT @mySampleTable (CaseID, StudentID, StudentSocial, StudentName, CreatedDate) VALUES (1001, 'FTS-2134', '111-22-3333', 'Roger Federer', '5/12/2013'), (1001, 'FTS-2314', '111-22-3333', 'Roger Federer', '5/08/2013'), (1002, 'PTS-5769', '222-33-4444', 'Maria Sharapova', '5/14/2013'), (1002, 'PTS-5769', '222-33-4444', 'Maria Sharapova', '5/2/2013'), (1003, 'FTS-8764', '333-44-5555', 'Serena Williams', '5/17/2013'), (1003, 'FTS-8764', '333-44-5555', 'Serena Williams', '5/06/2013'), (1003, 'FTS-7864', '333-44-5555', 'Serena Williams', '4/30/2013'), (1004, 'FTS-9132', '444-55-6666', 'Novak Djokovic', '5/10/2013'), (1004, 'FTS-9312', '444-55-6666', 'Novak Djokovic', '4/27/2013'), (1005, 'PTS-4722', '555-66-7777', 'Novak Djokovic', '5/19/2013'), (1005, 'PTS-4722', '555-66-7777', 'Novak Djokovic', '5/12/2013')select * from @mySampleTable[/code]For now, this is what I did...[code]SELECT DISTINCT a.CaseID, a.StudentID, b.StudentID, a.StudentSocial, a.StudentName, a.CreatedDateFROM @mySampleTable aINNER JOIN @mySampleTable b ON a.CaseID = b.CaseID AND a.StudentSocial = b.StudentSocial AND a.StudentID <> b.StudentID[/code]It doesn't do the sorting, but seems to do the rest. I am not sure if this is the best way to achieve what I am looking for though. My actual production tables have fairly large amount of data. I would appreciate your help and inputs with this.Thanks in advance...- RexMon, 20 May 2013 15:44:35 GMTRexHelioshttp://www.sqlservercentral.com/Forums/Topic1455179-392-1.aspxHello,I want to know if anyone knows of an easy way to do a SELECT INTO from a table with an identity column, but I do not want the Identity column to be marked as an identity column in the new table. I do want the data from the identity column, but I don't want to have the identity property.The best I can come up with is using the union operator:[code="sql"]with t1 as (select * from table union select * from table)select * into newtable from t1[/code]Tue, 21 May 2013 13:06:29 GMTGabriel Phttp://www.sqlservercentral.com/Forums/Topic1455463-392-1.aspxHello EveryoneI am working on a query that I know can be written to be more efficient.I have a column that is of datetime data type.[code="sql"]DECLARE @Dates TABLE( CreateDate datetime)INSERT INTO @Dates(CreateDate)SELECT '2013-03-12 08:44:12.420' UNION ALL -- this oneSELECT '2013-03-12 08:38:46.103' UNION ALL -- this oneSELECT '2013-03-12 07:37:17.693' UNION ALLSELECT '2013-03-12 07:56:50.697' UNION ALLSELECT '2013-03-12 08:43:20.623' UNION ALL -- this oneSELECT '2013-03-12 08:44:12.297' UNION ALL -- this oneSELECT '2013-03-12 10:06:57.353' UNION ALLSELECT '2013-03-12 09:10:54.390' UNION ALLSELECT '2013-03-12 10:03:58.770' UNION ALLSELECT '2013-03-12 07:56:59.817'[/code] I would like to query all the rows for a specific day, '2013-03-12', and only the for the hour of 0800If I were to select the count, I should have only 4 rows returnedThanks in advance for your assistance, suggestions and commentsAndrew SQLDBAWed, 22 May 2013 06:43:28 GMTAndrewSQLDBAhttp://www.sqlservercentral.com/Forums/Topic1455336-392-1.aspxHi Friends...I Having Table For The Following Structure..( Sample Data )[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 ('DS_1','Saran','Purchase','A',15,11,4,NULL), ('DS_1','Saran','Measurement','B',18,15,3,NULL), ('DS_1','Saran','Cutting','C',12,15,-3,NULL), ('DS_1','Saran','Stitching','D',20,27,-7,NULL), ('DS_1','Saran','Ironing','E',14,19,-5,NULL), ('DS_1','Saran','Packing','F',15,13,2,NULL), ('DS_1','Saran','Checking','G',18,12,6,NULL), ('DS_1','Saran','Delivery','H',22,33,-11,NULL), ('DS_2','Arun','Purchase','A',12,13,-1,NULL), ('DS_2','Arun','Measurement','B',15,19,-4,NULL), ('DS_2','Arun','Cutting','C',22,29,-7,NULL), ('DS_2','Arun','Stitching','D',41,43,-2,NULL), ('DS_2','Arun','Ironing','E',27,26,-1,NULL), ('DS_2','Arun','Packing','F',18,13,5,NULL), ('DS_2','Arun','Checking','G',11,14,-3,NULL), ('DS_2','Arun','Delivery','H',17,24,-7,NULL), ('DS_3','Kumar','Purchase','A',41,43,-2,NULL), ('DS_3','Kumar','Measurement','B',23,26,-3,NULL), ('DS_3','Kumar','Cutting','C',22,29,-7,NULL), ('DS_3','Kumar','Stitching','D',41,43,-2,NULL), ('DS_3','Kumar','Ironing','E',12,13,-1,NULL), ('DS_3','Kumar','Packing','F',14,19,-5,NULL), ('DS_3','Kumar','Checking','G',12,14,-2,NULL), ('DS_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------------------------- -------------------------------------------------- ------------------------------ --------- ----------- ----------- ----------- --------------DS_1 Saran Purchase A 15 11 4 4DS_1 Saran Measurement B 18 15 3 7DS_1 Saran Cutting C 12 15 -3 4DS_1 Saran Stitching D 20 27 -7 -3DS_1 Saran Ironing E 14 19 -5 -8DS_1 Saran Packing F 15 13 2 -6DS_1 Saran Checking G 18 12 6 0DS_1 Saran Delivery H 22 33 -11 -11DS_2 Arun Purchase A 12 13 -1 -1DS_2 Arun Measurement B 15 19 -4 -5DS_2 Arun Cutting C 22 29 -7 -12DS_2 Arun Stitching D 41 43 -2 -14DS_2 Arun Ironing E 27 26 -1 -15DS_2 Arun Packing F 18 13 5 -10DS_2 Arun Checking G 11 14 -3 -13DS_2 Arun Delivery H 17 24 -7 -20DS_3 Kumar Purchase A 41 43 -2 -2DS_3 Kumar Measurement B 23 26 -3 -5DS_3 Kumar Cutting C 22 29 -7 -12DS_3 Kumar Stitching D 41 43 -2 -14DS_3 Kumar Ironing E 12 13 -1 -15DS_3 Kumar Packing F 14 19 -5 -20DS_3 Kumar Checking G 12 14 -2 -22DS_3 Kumar Delivery H 18 13 5 -17[/code]My Requirement :1) I want to be Fill the TotalDelayDays Column..2)The Cumulative Sum Based on the DelayedDays..3) The Cumulative Sum Should be Calculated on EmpID once it will finished for particular EmpIDthen Cumulative Sum should start from Initial for second EmpID ...I Got The Result For the Following Method...[code="sql"]; WITH BASE AS ( SELECT EmpId, EmpName, ProcessName, GroupCode, PlannedDays, ActualDays, DelayedDays, rnk = DENSE_RANK() OVER (PARTITION BY EmpID ORDER BY GroupCode) FROM EmpProcessDetail) SELECT EmpId, EmpName, ProcessName, GroupCode, PlannedDays, ActualDays, DelayedDays, x.TotalDelayDaysFROMBASE bCROSS APPLY (SELECT TotalDelayDays = SUM(DelayedDays) FROM BASE WHERE EmpID = b.EmpID AND rnk <= b.rnk) x[/code]But That Method Takes Over time To Produce The Result for 40000 Records takes around 25 minutes.I want to avoid the Performance Issue..So i Need Another Method to calculate The Cumulative Sum...(for 40000 Records)Thanks & Regards..Saravanan.DWed, 22 May 2013 01:27:41 GMTsarwaanmcahttp://www.sqlservercentral.com/Forums/Topic1454730-392-1.aspxHi, I m trying to get a datetime function for the start day of current week (Sunday). I m using the below function select DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)However, i need to start from 7/5/4 AM every sunday depends on the value of the variable supplied.like : declare @minute int set @minute = -420 select DATEADD(wk,DATEDIFF(wk,0, dateadd(mi,-1*@minute,GETDATE())),-1)I m expecting it to return : 2013-05-19 07:00:00.000but it returns : 2013-05-19 00:00:00.000 Can someone help me on this?THank youMon, 20 May 2013 14:22:14 GMTssismaddyhttp://www.sqlservercentral.com/Forums/Topic1454498-392-1.aspxhi alli want to create table for fiscal year in sql server 2012.the fields are :1-ID2-StartDate3-EndDatebut i have some problems with ID column ,i want to set it for the first year(2013)Id int identity(2013,1)when i insert the first records in 2013, ID for that record is 20131, and for the second one 20132, and ...next year it must be set to id int identity (2014,1)how can i do that?changing values of identity column ?Mon, 20 May 2013 06:10:55 GMTsoftwareeng2010http://www.sqlservercentral.com/Forums/Topic1454686-392-1.aspxhi all,i want to convert database diagram to script in sql server 2012,how?someone told me to check Definition field in sysdiagrams, but couldn't find anythinghope u can help meMon, 20 May 2013 12:39:21 GMTsoftwareeng2010http://www.sqlservercentral.com/Forums/Topic1454618-392-1.aspxOk,This is driving me crazy.The security configuration for Linked Server on the local one is as follow:[b]On Local server login to remote server login mappings:[/b]UserA to UserA[b]For a login not defined in the list above, connections will[/b]"Be made using this security context" .... and remote login is "sa"The Linked Server works fine for regular queries, but a non client database is being moved out to the 2nd server. Now a local store procedure that points to the database that it's being moved cannot be altered, not even by me. When I run the following, using my own DBA account ...[code="sql"]CREATE PROCEDURE ABC AS set nocount on; SELECT TOP 1 [col2] FROM [RemoteServer].[dbName].[dbo].[table] where col1 = something GO[/code]I get this error:[code="plain"]The OLE DB provider "SQLNCLI" for linked server "xxxxxx" does not contain the table "MyTable here". The table either does not exist or the current user does not have permissions on that table.[/code]If I open a query window and connect to local server using "UserA", I can run the query inside and see the remote table and data set. However, I can't locally alter or create the store procedure with that remote query. I know it's because the LinkedServer credentials, but how can I solve this without adding extra logins or myself to the LinkedServer properties? I want to minimize the security risk of connecting or running queries across servers using elevated privileges. "UserA" has limited privileges on the remote server. And "sa" on remote server is not actually "sa". It has limited privileges as well. It uses same name, but that's because it is encoded on the app, so I tricked the system that way.Any hints?Mon, 20 May 2013 10:23:10 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1454619-392-1.aspxHi all i have a table in which results are stored every 30 minutes.[code="plain"]tblResultsTestName sample_time valueTest1 5/17/2013 12:00 657Test2 5/17/2013 12:30 634Test3 5/17/2013 13:00 576Test4 5/17/2013 13:30 641[/code]I would like to sum results for the sample_time of 1 hour so for 12PM-1PM value = 1867I'm using this query[code="plain"]SELECT sample_time AS sample_time_start , sample_time AS sample_time_end , SUM(VALUE) FROM tblResults WHERE GROUP BY DATEPART(HOUR, SAMPLE_TIME)[/code]but i need to see TestName and sample_time column also like this[code="plain"]tblResultsTestName sample_time_start sample_time_end valueTest1 5/17/2013 12:00 5/17/2013 01:00 1867[/code]any help appreciated.Mon, 20 May 2013 10:23:12 GMTAlexSQLForumshttp://www.sqlservercentral.com/Forums/Topic1453645-392-1.aspxHello Pros,I manage 100 + SQL servers and wanted to find a way to backup all databases to multiple .bak files (striping) regardless of the DB size. Below is the closes one I got on google. can anyone shed some light\share script that can accomplish this. Thanks in advance for your help.DECLARE @sql VARCHAR(max);SELECT @sql = COALESCE(@sql,'')+ 'BACKUP DATABASE '+db.name+' '+CHAR(13)+ 'TO DISK = ''c:\'+db.name+'_1.bak'','+CHAR(13)+ 'DISK = ''c:\'+db.name+'_2.bak'','+CHAR(13)+ 'DISK = ''c:\'+db.name+'_3.bak'','+CHAR(13)+ 'DISK = ''c:\'+db.name+'_5.bak''with INIT, compression'+CHAR(13)+CHAR(13)FROM sys.databases dbWHERE name NOT IN ('tempdb','pubs','AdventureWorks','AdventureWorksDW')exec(@sql)---PRINT(@sql)Thu, 16 May 2013 10:01:44 GMTpehlebhiayathahttp://www.sqlservercentral.com/Forums/Topic1454609-392-1.aspxHi,I need -13599.99 as 13600162157.36 as 1621577415781.64 AS 7415782How can I achive that ?thanks,Mon, 20 May 2013 10:03:33 GMTPSBhttp://www.sqlservercentral.com/Forums/Topic1454560-392-1.aspxI have two tables [b]1) Product with columns[/b][code="sql"]ProductId Name1 ABC2 DEF [/code][b]2) Sales[/b][code="sql"]TransactionId ProductId ReqTime ResTime1 1 05/20/2013 13:22 05/20/2013 13:232 1 05/20/2013 13:22 05/20/2013 13:233 1 05/20/2013 13:22 05/20/2013 13:23[/code]I want to get an output like this :[code="other"]ProductId TotalSales1 32 0[/code]I am currently doing a query like this :[code="sql"][code="other"]select Distinct(ProductId), Count(TransactionId) from Product LEFT OUTER JOIN Transaction ON Product.ProductId=Transaction.ProductIdwhere ReqTime > @BeginTime and ResTime <@EndTimeGroupby Product.ProductId;[/code][/code][code="sql"]I get an output like this :ProductId TotalSales1 3[/code]Mon, 20 May 2013 08:31:44 GMTSqlServerNinjahttp://www.sqlservercentral.com/Forums/Topic1454015-392-1.aspxHi,I don't spend enough time SQL coding so I am wondering of someone can help me with my statement please, as I am struggling a little.I'm getting the following error from the statement below.[i][b]Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'select'.[/b][/i]If you run the select on its own it works fine. When you add the Insert on is when it starts to fail.Thanks in advanceTaggsInsert into SQL_ENVIRONMENT.dbo.DatabaseSizing (instancename,databasename,db_datafile,db_logfile,db_datasize,dateofsize)values select convert(nvarchar(50),SERVERPROPERTY ('Servername')), convert(nvarchar(50),sys.databases.name), (SELECT (SUM(sys.master_files.[size])*8/1024) FROM sys.master_files where sys.databases.database_id = sys.master_files.database_id and type=0), (SELECT (SUM(sys.master_files.[size])*8/1024) FROM sys.master_files where sys.databases.database_id = sys.master_files.database_id and type=1), -- (convert(float,fileproperty(sysfiles.name,'SpaceUsed'))) * (8192.0/1048576) MB_Used, Null, getdate() FROM sys.databases left join sysfiles on sys.databases.name = sysfiles.name WHERE sys.databases.database_id>4 ORDER BY sys.databases.nameFri, 17 May 2013 07:50:45 GMTTaggshttp://www.sqlservercentral.com/Forums/Topic1454423-392-1.aspxAll,I want to display -0 when i run the below queryselect 0.0*-1Mon, 20 May 2013 01:50:56 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic1454226-392-1.aspxHi,all. I am beginner of learning sql cursor. Can someone help me to edit the following sql statement. I would like to update the table1 column 'total'. It records anyone leave on working date from table2.Leo---------Declare @LeaveId intDeclare @LeaveType nvarchar(30)Declare @Note nvarchar(30)Declare AskForLeaveCursor CURSOR FORselect AskForLeaveId from tempAttendanceForLeaveOpen AskForLeaveCursorFetch Next from AskForLeaveCursor into @LeaveIdwhile(@@FETCH_STATUS = 0) Begin select @LeaveType = LeaveType, @Note = Note from Table2 where AskForLeaveId=@AskForLeaveId if (@Note = '22x') Begin Update Table1 set total = total + 1 End endTable 1:total EmpID Name Date Start Time End Time0 A00030 John 2013/03/06 07:59:19 11:59:25 0 A00030 John 2013/03/11 07:58:40 10:36:35 0 A00048 May 2013/03/31 07:50:20 11:59:10 Table2:ID Name Date LeaveType Note Start Time596184 john 2013/03/06 Sick Leave 22x 2013-04-04 18:00:00.000 End Time2013-04-04 20:00:00.000 596185 john 2013/03/11 Sick Leave 22x 2013-04-04 18:00:00.000 2013-04-04 20:00:00.000 after update:total EmpID Name Date Start Time End Time1 A00030 John 2013/03/06 07:59:19 11:59:25 1 A00030 John 2013/03/11 07:58:40 10:36:35 0 A00048 May 2013/03/31 07:50:20 11:59:10Fri, 17 May 2013 23:01:04 GMTleostruthttp://www.sqlservercentral.com/Forums/Topic1453478-392-1.aspxHi all Experts,I am trying this since a long time. My intellisense is not working with a specified [b]Database[/b]. Intellisense i working for all other Database except one. I tried all the things that were told in this article http://blog.sqlauthority.com/2009/03/31/sql-server-2008-intellisense-does-not-work-enable-intellisense/ .Does any body know that how to enable the intellisense at Database Level.Thu, 16 May 2013 06:44:27 GMTShadab Shahhttp://www.sqlservercentral.com/Forums/Topic1454172-392-1.aspxI have the following:DECLARE @StartInsertedDate DATETIMESET @StartInsertedDate = CAST(CONVERT(VARCHAR(10), DATEADD(MONTH, -18,GETDATE()) - 1, 101) AS DATETIME) --DATEADD(DAY, -1, DATEDIFF(DAY, 0, GETDATE()))SELECT @StartInsertedDateAND it returns 2011-11-16 00:00:00.000I want it to return the 1st day of the month (2011-11-01 00:00:00.000 is what I want) and I can not get it to for the life of me.Any help will be greatly appreciated.Fri, 17 May 2013 12:43:57 GMTGBeezyhttp://www.sqlservercentral.com/Forums/Topic1453870-392-1.aspxHi !!!I have a table like team listTBL_Teamlist:userid superid101a AA1102b AA1201c AA2Hear i need to asign to the perticular user is having a rights like 1.READ 2. write 3. read & write in front end application:this is like providing a security if anybody having the idea on this pls help me ie appriciated :-):Fri, 17 May 2013 01:10:03 GMTmahi123http://www.sqlservercentral.com/Forums/Topic1453920-392-1.aspxMorningI am attempting to send a query result via DB mail and where as it is working fine, I want to order the file by a particular column. When I add the order by clause in I get the below errorMsg 22050, Level 16, State 1, Line 0Error formatting query, probably invalid parametersMsg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504Query execution failed: Msg 207, Level 16, State 1, Server PROMETHEUS, Line 33Invalid column name 'trantype'.Msg 104, Level 16, State 1, Server PROMETHEUS, Line 33ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.My Query is as follows:EXEC msdb.dbo.sp_send_dbmail@recipients=N'',--@copy_recipients=N'',@subject='',@body='', @profile_name ='standard',@query ='SET NOCOUNT ON; select ''[Polsdate]'' ,''[Affinity]'' ,''[Title]'' ,''[Forename]'' ,''[Surname]'' ,''[Address1]'' ,''[Address2]'' ,''[Address3]'' ,''[Address4]'' ,''[Postcode]'' ,''[DOB]'' ,''[Cover]'' ,''[Reg]'' ,''[Cover Description]'' ,''[trantype]'' union all select top 100 cast([Polsdate] as varchar) ,cast([Affinity] as varchar) ,cast([Title] as varchar) ,cast([Forename] as varchar) ,cast([Surname] as varchar) ,cast([Address1] as varchar) ,cast([Address2] as varchar) ,cast([Address3] as varchar) ,cast([Address4] as varchar) ,cast([Postcode] as varchar) ,cast([DOB] as varchar) ,cast([Cover] as varchar) ,cast([Reg] as varchar) ,cast([Cover Description] as varchar) ,cast([Trantype] as varchar) from db where insertdate = CONVERT(VARCHAR(10), GETDATE(), 105) [b]order by [trantype][/b]' , @attach_query_result_as_file = 1,@query_attachment_filename ='TEST.CSV',@query_result_header=0,@query_result_separator=' ',@query_result_no_padding=1Fri, 17 May 2013 04:10:41 GMTkirk.hughes 13236http://www.sqlservercentral.com/Forums/Topic1279826-392-1.aspxDoes anyone have a basic example of the use of the OUTPUT CLAUSE in a MERGE Statement?I found examples with INSERT & DELETE nut no UPDATE?Sat, 07 Apr 2012 08:10:17 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1453527-392-1.aspxIs there a way to query two different servers at the same time if they aren't linked?Thu, 16 May 2013 07:33:58 GMTccmrethttp://www.sqlservercentral.com/Forums/Topic1453485-392-1.aspxI have three fields I am interested in:Employee_ID,Employee_Title,Employee_EntryI want to be able to select these three fields but only the record with the most recent Employee_Entry date. In some cases an Employee may have moved job titles and have several entries against the Employee_ID.I have started with something like this:SELECT DISTINCTEmployee_ID,Employee_Title,Employee_EntryFROM emp.employeesI have tried using Max(Employee_Entry) but this returns all records for those who have multiple titles. I just want the current most recent Employee_title.Thu, 16 May 2013 06:50:39 GMTccmrethttp://www.sqlservercentral.com/Forums/Topic1453304-392-1.aspxDear friends,I need to do a urgent requirement-I'm facing issues with rounding the duration to a whole number. The new requirement is: Calculate as (Job Posting End Date - Job Posting Start Date)/30. Round to the nearest two decimals. OR Calculate as (Work Order End Date - Work Order Start Date)/30. Round to the nearest two decimals. So per this updated requirement:- if the calculation came to 20.42, it should display as 20.42- if the calculation came to 20.4789, it should display as 20.48- if the calculation came to 20.4739, is should display as 20.47- if the calculation came to 20.1234, it should display as 20.12BUT my code is- CAST(floor(datediff(d, [WorkForce_JobPosting].[Job Posting Start Date_JP], [WorkForce_JobPosting].[Job Posting End Date_JP]) / 30) AS INT)and is not working it's giving me the above results.Need help please.ThanksDhananjayWed, 15 May 2013 20:31:32 GMTdhananjay.nagarkarhttp://www.sqlservercentral.com/Forums/Topic1451530-392-1.aspxI have found a bug in my duplicate macthing application and I believe it's with the way I group my records at the end of the matching process.Consider the following example (details are not real!)[img]http://i.imgur.com/zZy2PqS.jpg[/img]52941 matches 8388 and 15799 because mobile number and name is the same80833 matches 8388 and 15799 because name and address matchso this is same as saying 80833 matches 52941, 8388 AND 15799So based on the second result set, I would like to perform a grouping of the outcome to look like this:[img]http://i.imgur.com/NibYbnP.jpg[/img]NOTE, I picked the highest value ID as my Base or Group ID.Any suggestions?Fri, 10 May 2013 05:34:02 GMTAbu Dina