.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Sql query to find continuos sequence of numbers

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server
 

 

Hi,

I have a table of structure below.

create table table_1

(id bigint identity(1,1),

stringnum varchar(20)

)

 

insert into table_1 values('2130')

insert into table_1 values('2131')

insert into table_1 values('21320')

insert into table_1 values('21321')

insert into table_1 values('21322')

insert into table_1 values('21323')

insert into table_1 values('21324')

insert into table_1 values('21325')

insert into table_1 values('21326')

insert into table_1 values('21327')

insert into table_1 values('21328')

insert into table_1 values('21329')

insert into table_1 values('2133')

insert into table_1 values('2134')

insert into table_1 values('2135')

insert into table_1 values('2136')

insert into table_1 values('2137')

insert into table_1 values('2138')

insert into table_1 values('2139')

 

 

 

I am trying to compress these stringnums i.e for example

if a complete range exists say 21320 21321,21322...21329 (i.e if the number is ranging from 0-9) then the last digit of the number can be eliminated so that the result is

2132 ( which is equivalent to 21320-21329 )

 

this elimination of la


View Complete Post


More Related Resource Links

Find SQL Query when report is executed

  
Hi All, I am using SSRS 2008 R2 verison of reporting services. I have a report and the data source for this report is Oracle 10i, it takes around 2 mins to execute the report. I want to find out the query, which is sent by the reporting services to the Oracle. Is there a way to find it out. Please help me with this. Thanks Vineeshvineesh1701

Please help me to find out distinct value from linq to sql query given.

  
profileuserDataContext db = new profileuserDataContext(); var Q = (from p in db.edufinals join RM in db.edufinals on p.UserName equals RM.UserName where p.name.StartsWith(srcval) select new { p.name, RM.ProfielID, RM.ProfielPic, RM.city }).OrderBy(x => x.name).Distinct().Take(10);//, p.Key.ProfielID, p.Key.ProfielPic, p.Key.city, p.Key.country foreach (var pg in Q.Distinct()) { Response.Write(pg.name); ..................................... .................................... } how to refine this query to get distinct "name" column? Please help me to find out exact value.

MDX Query - Find only new item that purchased in the last day or during the last week

  
Hi to all I whould like to write a MDX query to get new items that purchased in the last day and not purchased in the last 7 days or 30 days  , Also to find new items that purchased in the last 7 days and not purchased in the last 30 days , this is what i done until now CREATE   SET CURRENTCUBE.[FindNewWordIn_1_Day]   AS Filter   (nonempty([Keyword].[Keyword Name].members, ([Measures].[Count keyword],   NONEMPTY (Tail( Filter( [Time].[Date].MEMBERS ,   Not IsEmpty ([Measures].[Count keyword]) ), 1) ))) ,([Measures].[Count keyword], [Time].[Date].&[2010-09-10] )=0);   The value of [Time].[Date].&[2010-09-10] should be change to period that i want to check , ( 1 day, 7 day , 30 day)   the other one is lateset day with value NONEMPTY (Tail( Filter( [Time].[Date].MEMBERS , Not IsEmpty ([Measures].[Count keyword]) ), 1) with for you help ,   ofer  

VWD 2010 and Linq to Sql: can't find a query model for...

  

Hello!

I have added a linq to sql layer in my MVC 2 application.

This is the code I am using:


using (ProductPortalDataContext ctx = new ProductPortalDataContext())
            {
                var Title = from aps in ProductPortal.Models.AppSettingsView
                            where aps.LanguageID = Lang
                            select aps.WebSiteTitle;
            }


It says to be unable to find a quey model for AppSettings view and then says "where not found".

Tried googling, nothing came up. Tried searching here(first two pages) and nothing that seemed relevant popped up.

But the best of all, created a new ASP.NET applicatio, imported the model using Linq to SQL and it compiled!

No idea what I am doing wrong... hints?

(some time later...)

Ok, I tried adding a button and a label to the asp.net application j

Add sequence numbers to selected records

  
How can I add a unique ascendant number to each row that has been selected by a SELECT statement? The ROW_NUMBER() is not suitable for me because it has a obligatory ORDER BY, I don't want to use order by!

MJ_Developer.

SPView query to find if the current user belongs to a Group

  

HI All,

I´m trying to write a query for a list view that should return all items if the current user belong to a group. If the user doesn´t belongs to this group, no items should be returned.

So far, I could find many equal examples about using the CAML Membership element in a comparison with an AssignetTo field. But not making a comparison between the current user and a specific Sharepoint Group.

Any suggestions, please?

Thanks



How to find the sequence of backups from msdb.dbo.backupset

  

I am a bit confused  with the first_lsn, last_lsn, differential_base_lsn in the msdb.dbo.backupset.I have  a sql server 2005 database in full recovery mode. My backup jobs are – nightly full backup, dfifferential backup every six hrs and log backups every hr. In addition to that I do do adhoc backups with copy_only.  Usually full bcakup takes 15 minutes, differential backup 5 minutes and  log backup under one minute.

Because they are scheduled to run on top of the hour, it is quite possible all three jobs are triggered at the same time. ( Full backups do not block log backups, Not sure if same is tru with diff backups and log backup). In such a situation I want to see a complete sequence of backups what should be the ordering criteria?

 I am using below query to get the backup sequence. Should I order based on backup_start_date, F.family_sequence

How to find the sequence of backups from msdb.dbo.backupset

  

I am a bit confused  with the first_lsn, last_lsn, differential_base_lsn in the msdb.dbo.backupset.I have  a sql server 2005 database in full recovery mode. My backup jobs are – nightly full backup, dfifferential backup every six hrs and log backups every hr. In addition to that I do do adhoc backups with copy_only.  Usually full bcakup takes 15 minutes, differential backup 5 minutes and  log backup under one minute.

Because they are scheduled to run on top of the hour, it is quite possible all three jobs are triggered at the same time. ( Full backups do not block log backups, Not sure if same is tru with diff backups and log backup). In such a situation I want to see a complete sequence of backups what should be the ordering criteria?

 I am using below query to get the backup sequence. Should I order based on backup_start_date, F.family_sequence

find situation about a query that it is executing now

  
Hi all. I am a c# programmer. I have a update query that it need 2 minutes for executing (for each reason). I don't want discuss about its performance. My question is: How can I find situation about a query that it is executing now and it has not executed completely yet. for example How can find my c# code how many records are affected by my query every 10 seconds while that query is executing? Is sql profiler says that to us?

How do find what query is being executed on specific session ID?

  

Hello,

I have a SP which writes to a table all processes which are being executed when application decides to stop waiting for results of the query. It works very well but problem is that it does not dump all SPIDs but only some of them which are being executing for a long time. Common pattern that a lot of SPIDs will be blocked by single SPID which will not be part of the output which is being generated. How do I dump all SPIDs and what they are doing along with SP below?

 

SELECT 
 t1.resource_type,
 'database'=db_name(resource_database_id),
 'blk object' = t1.resource_associated_entity_id,
 t1.request_mode,
 t1.request_session_id,
 t2.blocking_session_id,
	 t2.wait_duration_ms,

 (SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
 		(CASE WHEN t3.statement_end_offset = -1 
 			THEN LEN(CONVERT(nvarchar(

query to find the 5th highest salary from an employee table..help plz...

  

 Thanks in advance



Xpath query to find an Node who's attribute contains a forward slash - Going crazy

  

If  I have a .XML file that contains the following example:

<Socks Style ="Business/Business Casual/Casual"

</Socks

When I try to use the Xpath expression below to find the element above by searching for the value contained in the Style Attribute, the expression returns null:

//Socks [@Style='Business/Business Casual/Casual'] 

I downloaded xPath virtualizer and I noticed that the expression above is adding the namespace alias when it finds the embedded forward slash contained in the value of the Style Attribute (which is of course not what I want to happen).  This is driving me up a tree!!  Isn't there a way to escape the forward slash?  I know it is the slash that is the problem because for testing purposes I substituted dashes (-) in one of the nodes' Style Attribute values and then changed the xpath expression to match and it returned the exact node.

My goal is to do some replacements on all elements with the Style attribute based on each contents but I can't even try that because of this roadblock.

Please help anyone

-Rick

 


How to find records based on CAPITAL and small letters in SQL Query?

  

Hi,

I stumbled upon this scenario at my work and it is really something I was not expecting. I know that in general SQL itself is not a CASE SENSITIVE language ... so it will not make any difference if i write select *, or SELECT * or SeLEct * ...

But recently I came across a situation where I am required to find results from a table where more than one record exists with just a difference of Captial and Small letter word. Let me explain my problem in detail,

I work for a government client, in one of their table they have list of all state statues and out of them some have exactly same string except in that string some letters are capitalized, e.g.  46b-12 (A) and 46b-12 (a) ... now my question is how can i write a query which can find only record that I search, e.g if I search select desc from statues where code='46b-12 (a)' ... it should find only one record not two.


Query to find the domain and username

  

Hi experts,

 Any one pls tel  me the query to find the current working domain name with username in sql server.

Thanks

Abdul2010


WMI Query to find the other Sp sites in the network

  

Is there a WMI query to find the Sharepoint sites in the network?

 


Cannot find execute any query, stored procedure not found even if it is there

  

Hey guys,

I am getting frustrated with this problem, I dont know what i did, but now I cannot execute any stored procedured when I could last time.

When I use my asp.net application to run the query, it finds the stored procedure but when I execute it is sql management studio it says it cannot find the stored procedure even though it is there.

I tried to execute other procedures and the samething happens. Even when I try a simple query it says it cannot find the table

I could execute the query if i placed Use [databasename] in front, but even with this, I cannot execute stored procedures.

does any1 know how to fix this?


Ju Lian


Need Query to find out the latest record insert in database

  

Here is my table contain the following fields

   ID    UID    OID   Date  

   1      20       1    2010-11-03 12:22:44.217

   2      30       1     2010-11-03 12:01:09.090  

   3      20      2     2010-11-03 12:22:44.217

   4      40      2   2010-11-03 12:01:09.090

i need the result like

UID   OID    Date

 30     1        2010-11-03 12:01:09.090  

 40     2         2010-11-03 12:01:09.090  


Please help me

20 2 2010-11-03 12:22:44.217
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend