Monday, August 12, 2013

[SQL Server 2008 issues] How to update Flag based on Maximum Amount

[SQL Server 2008 issues] How to update Flag based on Maximum Amount

How to update Flag based on Maximum Amount

Posted: 07 Aug 2013 10:00 PM PDT

Create Table #Temp(Lnno Varchar(15),Custcode varchar(10),Flag varchar(10),Amount Int,Amount_Flag varchar(1))Insert Into #TempValues ('1','A1','Cust',1000,''),('1','A2','CoAp',500,''),('1','A3','CoAp',100,''),('1','A4','CoAp',2000,''),('2','B1','Cust',1000,''),('2','B2','CoAp',1000,''),('2','B3','CoAp',1000,''),('2','B4','CoAp',1000,''),('3','C1','Cust',0,''),('3','C2','CoAp',1000,''),('3','C3','CoAp',1000,''),('3','C4','CoAp',5000,'')Select * from #Temp/*Hi,I have this data where it has Lnno,Custcode,Amount and Flag.My requirement is,I has to Update Amount_Flag as Y,to maximum Amount for that Lnno.1)Now in case Lnno = 1,there is one Cust and three CoAp ,So Amount_Flag should be updated as Y to Custcode 'A4',since it has maximum amount.2)In Case Lnno = 2,Amount is same for all,so in this case Amount_Flag should be Updated to Flag = 'Cust',that is the priority should be given to Cust.3)In Case Lnno = 3,Cust has amount 0,but two CoAp has Amount same that is 1000,so Amount_Flag should be updated to any one of the record.Please Help me.Thanks in Advance!!*/

SQl 2008 full RAM Utilizing

Posted: 11 Aug 2013 05:18 AM PDT

Hello ,Using Sql 2008 R2 , have 16 Gb RAM . Did Sql will use whole memory ;if no another services using that memory .how we can control the memory use of SQL ?ThanksSatheesh

keep result in table valued data type .

Posted: 11 Aug 2013 06:37 PM PDT

Hi,i want to keep result in table valued data type the result which is returned by function please tell me how to do it.

Does Transactional Replication have any affect on normal Transaction Log Backups?

Posted: 11 Aug 2013 12:33 PM PDT

Pretty basic question, but haven't touched replication in a long time. Does Transactional Replication have any affect on normal Transaction Log Backups and your recoverability in using them?

Primary server down

Posted: 11 Aug 2013 04:56 PM PDT

How can we make synchronise of data when the primary server down in log shipping and we have no witness server ?

Starting SSIS jobs from BMC Control-M

Posted: 11 Aug 2013 05:05 PM PDT

Our outsourcing partner is trying to setup a way to start SSIS jobs from BMC Control-M. There seams to be a problem doing that because the SSIS job returns control before the SSIS job has finnished. Is there a way to work around that problem?Control-M is a central scheduler, its installed on the SQL Server batch server so Connection between Control-M and the server is no problem.

distribution agent fails

Posted: 11 Aug 2013 05:05 PM PDT

Hi , i did not configure replication as of now , in one of the server configured transactional replication.Distribution agent fails on the primary server what is the impact of the data when distribution agent fails?

select only some rows and then the rest

Posted: 11 Aug 2013 03:51 PM PDT

Hi Professionals.I am stuck on a way around a query and wonder if it is possible.I want to always select the first 3 rows in the exact order from my table and then the rest of the rows in now particular order. The table is created dynmaically so it will not always be the same columns in the first 3.I have tried[code]select softwaremanufacturer,productname,productversion, * from newtable[/code]but it shows those columns listed and then those columns listed again plus the rest like so[code]Microsoft Corporation Office Access 2003 11.x Microsoft Corporation Office Access 2003 11.0.SP3 (jp) 22/04/2005 30/04/2008 11.xMicrosoft Corporation Office Access 2003 11.x Microsoft Corporation Office Access 2003 NULL 22/04/2005 30/04/2008 11.x[/code]is there a way around this so it selects just the softwaremanufacturer,productname,productversion oncehope this makes sensethanks in advance

Is overpunch amenable to cross apply?

Posted: 11 Aug 2013 01:18 PM PDT

I have a file that needs to have its contents converted to money from overpunch characters. I started on an approach, but my conscience is nagging me that it is a dumb approach. My instinct is that this needs to be a function, but then I'd have to write that over 40x, so that doesn't sound to bright either.Another idea is to use an overpunch table, but joining on the end of the substring also didn't sound like a good idea to me either. the gist of the problem is like so;select NDCNumber,cast( case when right(ingredientcost,1)='{' then substring(ingredientcost, 1,len(ingredientcost)-1)+'0' when right(ingredientcost,1)='A' then substring(ingredientcost, 1,len(ingredientcost)-1)+'1' when right(ingredientcost,1)='B' then substring(ingredientcost, 1,len(ingredientcost)-1)+'2' when right(ingredientcost,1)='C' then substring(ingredientcost, 1,len(ingredientcost)-1)+'3'when right(ingredientcost,1)='D' then substring(ingredientcost, 1,len(ingredientcost)-1)+'4'when right(ingredientcost,1)='E' then substring(ingredientcost, 1,len(ingredientcost)-1)+'5'when right(ingredientcost,1)='F' then substring(ingredientcost, 1,len(ingredientcost)-1)+'6'when right(ingredientcost,1)='G' then substring(ingredientcost, 1,len(ingredientcost)-1)+'7'when right(ingredientcost,1)='H' then substring(ingredientcost, 1,len(ingredientcost)-1)+'8'when right(ingredientcost,1)='I' then substring(ingredientcost, 1,len(ingredientcost)-1)+'9'when right(ingredientcost,1)='J' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='K' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='L' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='M' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='N' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='O' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='P' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='Q' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='R' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'when right(ingredientcost,1)='}' then '-'+ substring(ingredientcost, 1,len(ingredientcost)-1)+'1'end as money)/100 IngredientCost...into #ESIStage From ESILandedso, the column named ingredientcost should be the parameter to the function, so it can be @ColumnName, but then, what kind of function? returns table? I don't think it should return a table because I need to work on selected columns. As above, I should be able to join to it, but how to do that for only 40 out of 200 columns? Can you suggest how can I organize the work to get the overpunches in over forty columns to be updated to numbers to fix each affected column?iow, I think I have disqualified select dbo.fnFixOverpunch(IngredientCost)IngredientCostdbo.fnFixOverpunch(AWP)AWPdbo.fnFixOverpunch(DiscountAmount)DiscountAmountinto #ESIStage from ESILandedandselect * from ESILanded a join overpunch b onsubstring(....all the columns that need to be fixed??)= b.overpunchCharacter is there a way to use cross apply to reference the columns that need to be fixed as well as the other columns that need to be brought along with them (there are over 200 columns in the import, the overpunch columns are different money attributes ), or do I need to split the work somehow so I can use cross apply on the money columns and plain old select into for the other columns, then join them up again (this idea also means managing six columns that determine uniqueness, NDCNumber, DateFilled, Refill, Member, Pharmacy & Prescriber)my gut tells me writing the same thing a zillion times is a nonstarter, but my head cannot feature how to do this with cross apply, which I think is what it really needs.Hope this is clear, and the design alternatives understandable. thanks in advance for any guidance you can provide

Need Rank function to filter the records

Posted: 11 Aug 2013 03:04 PM PDT

Hi [code]CREATE TABLE #Temp( Lvl int ,Level1_pk_id int ,Level2_pk_id int ,Level3_pk_id int )INSERT INTO #TempSELECT 1,11,null,null UNION ALLSELECT 2,11,22,null UNION ALLSELECT 2,11,23,null UNION ALLSELECT 3,11,22,33 UNION ALLSELECT 3,11,22,34 UNION ALLSELECT 1,12,null,nullHere , Need to select the last records for each level1,level2,level3 comibation.Out should be as below select * from #TempLvl Level1_pk_id Level2_pk_id Level3_pk_id2 11 23 NULL3 11 22 333 11 22 341 12 NULL NULL[/code]Logic: Level1_pk_id =11 has 2 Level2_pk_ids 22 and 23. 22 has value for Level3_pk_id. so result for 11 and 22 combination is (11 22 33) and (11 22 34) will be the final result.11 and 23 does not have level3 so 11 and 23 should be come in result12 also does not have any child levels , so 12 should be in result set.1,11,null,null -- records should not come in final result set as it has child levels 2,11,22,null -- aslo should not come in final result set as it has child levels Thanks!

Saving / Retrieving Queries / Views

Posted: 11 Aug 2013 04:03 AM PDT

Just starting out, i can run queries, then i'm prompted to save them, but I don't know how to retrieve them. Also, after running a query, how can I save that 'extrtacted table' to work with it later, thxf

Job finished with errors

Posted: 11 Aug 2013 12:54 AM PDT

Hi all,I have a very strange case (at least for me). Job which was running smoothly for a long time (like 2 years) about 30-40 min now takes hours or even days if ever succeeded.First the errors was on Linked servers ( there is a query which selects from 2 remote sources). I run this query manually without any problem.But now I am getting this error:DBCC execution completed. If DBCC printed error messages, contact your system administrator.Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line 105Could not locate statistics '...' in the system catalogs.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Each time it complains on different columns. Those columns belong to remote tables and not even in a select list.I run update statistics on all of remote tables and it didn't help at all. I am still getting the same error. I do not understand where DBCC execution comes from. SP doesn't have it, I checked. Is it some setting can be changed to avoid it?Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog