SQL Server 2012 :: Get Only Records Which Row-number Is 0 Or 1 In Module 20?

Mar 5, 2015

With this query i get only the records i need, but i would like to output in this way

1 - 20
21 - 30
31 - 40

of course in the real environment the ID are not consecutive, this is just one example of data.

declare @temp table (ID int)
declare @i int = 1
while(@i<1000) begin
insert into @temp values (@i)
set @i=@i+1
end
select ID from (
select ID, row_number() over (order by ID)
as rn
from @temp
) q where (rn % 20=0) OR (rn % 20=1)

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Delete Large Number Of Records?

Sep 8, 2015

I have the following scenario:

SQL database on SQL 2012

Large Production table 15 Million record

The table has 3 years of data

New monthly data is being added every month.

A New Monthly data is being loaded, checked and finally approved after 6 or 7 iteration before approval.Because of this iteration the monthly data set is being added then deleted then added then deleted few times.Because the table is big this process takes time, any thoughts on how to make the delete insert process faster.Keep in mind I cannot do much because it is a production table and is being access by other users to do other analysis.

Delete is done based on trx_date which is a year/month combo, like 201508.

The table has monthly sales by customer aggregated.

The table structure is:

CREATE TABLE [dbo].[Sales](
[batch_key] [int] NOT NULL,
[Company_key] [int] NOT NULL,
[customer_key] [char](22) NOT NULL,
[Trx_Date] [int] NOT NULL,
[account] [nvarchar](35) NOT NULL,

[code].....

View 9 Replies View Related

SQL 2012 :: Query To Understand Names Of All Available Tables / Number Of Records

Aug 31, 2014

SQL query to understand the names of all the available tables , number of records in these tables and size of these tables?

View 4 Replies View Related

Set Execute As On Module Without Redefining Module?

Aug 8, 2007

Is it possible to change the EXECUTE AS principal on a module (such as a stored procedure or function) without having to redefine the entire module in an ALTER statement? I didn't find anything in BOL about it in the ALTER PROCEDURE or ALTER FUNCTION articles. If there is a system sproc that does it, I couldn't find it. Changing system tables is not an option (assuming that is an option) for me.

View 1 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

SQL Server 2014 :: Getting Unique Number For Groups Of Records?

Aug 6, 2014

I'm trying to do the following and haven't been able to figure it out.

Say there's a table with these records:

Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g

I want to generate a number that represents the groups of columns like this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3

So that each grouping gets its own identifier. I've tried this:

SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable

But I get this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2

View 9 Replies View Related

SQL Server 2008 :: Append Records With Different Number Of Columns?

Feb 16, 2015

I have a table of Customers & their data in about 20 Columns.

I have another table that has potential Customers with 3 Columns.

I want to append the records from Table 2 onto Table 1 to the Columns with the same names.

I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.

There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.

Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?

View 2 Replies View Related

SQL Server 2008 :: Divide Total Rate By Number Of Records

Jun 22, 2015

my table includes detailed records with total Rate repeated in each record:

CREATE TABLE Table1
(
Providerid varchar (6) NOT NULL,
Providername char (30) NOT NULL,
Clientid varchar (15) NOT NULL,

[code]....

View 1 Replies View Related

SQL Server 2014 :: Group Up Records Randomly Into N Number Of Batches

Jul 6, 2015

I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.

Say for example, following is the list of records I have in my table which I want to group into 5 batches

A123
A124
A124
A123
A127

After Ntile I will get the below,

Desired output is, Need output like Ntile but all same id should reside in single batch

Even if I n=5, maximum possibility of batches are 3 only.

View 2 Replies View Related

How To Number Records / Duplicates Receiving Same Number

Feb 19, 2013

I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.

View 8 Replies View Related

SQL Server 2005 Full Text Performance With Large Number Of Records

Dec 12, 2007

Hi
We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM.
 
I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ?
 
If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results
 
Thanks in advance

View 1 Replies View Related

SQL Server 2014 :: Count Duplicate Records Based Upon Account Number?

Jul 16, 2014

I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable.i need to check whether count > 0 or not in stored procedure.I have used below query.It is not working.

SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END

View 9 Replies View Related

SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?

Oct 7, 2015

I have a challenge and I'm not sure the best route to go. Consider the following dataset.

I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.

CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012

What I want to know is the average number of months between the purchases. And do this for each customer.

View 6 Replies View Related

SQL Server 2012 :: Row Number Over Partition

Dec 5, 2013

I am having problems getting my last revision number out when i am trying to use inner joins along with the row_number over partition

I am using 2 tables, tbl_acyear_lookup & tbl_targets

tbl_acyear_lookup columns = (pk)- academic_year_id, academic_year
looks like this:
1, 2010/2011
2, 2011/2012
3, 2012/2013

tbl_targets columns = targetID, Academic_Year_ID,Course_Mode,UK_ENROL, INT_ENROL, Notes, Revision_Number

I have one stored proc that uses the Row_number over partition that looks like this:

Select TargetID, Academic_Year_id, Course_Mode, UK_Enrol, Int_Enrol, Notes, Revision_Number from
(SELECT ROW_NUMBER() OVER (partition by [Academic_Year_id] order by [Revision_Number] DESC) as [RevNum],TargetID, Academic_Year_id, Course_Mode, Target_Year, UK_Enrol, Int_Enrol, Notes, Revision_Number
FROM tbl_targets where course_mode=@course_mode) RV where (RV.RevNum=1)

Now the next store proc needs to use the above but i need to add the Academic_year from the tbl_acyear_lookup table also add filter the target_year ='year 1'

View 2 Replies View Related

Integrating Hardware Security Module (HSM) For Cryptographic Key Storage With SQL Server

Aug 21, 2007



Hi,



I have come to know from the SQL Server documentation that the Hardware Security Modules (HSM) can be used for Cryptographic Key storage within SQL Server to increase the level of protection of data at rest. We provide a HSM Solution which protects cryptographic keys and performs cryptographic operations onboard. The aforesaid fact about SQL Server and HSMs motivates us to test our HSM product with the SQL Server. Our HSM solution provides SDK for applications/servers to communicate with the HSM hardware. The SDK basically consists of two libraries:

-MS CAPI Interface (CSP Library)

-PKCS#11 Interface (cryptoki Library)



I have found this forum best for such kind of discussion. So could you guys on the list let me know:

- How the CSP or PKCS#11 library can be integrated with the SQL Server for HSM box to protect the cryptographic keys?

- Would I need to write a new Interface/Wrapper for this integration, If yes please help?



Thanks in advance.

Harsh.

View 4 Replies View Related

SQL Server Enterprise 2005 Cluster Installation. Error: The Specified Module Could Not Be Found.

May 29, 2007

Hi Everyone,



I have problem when installing SQL 2005 Enterprise to my Windows 2003 R2 cluster. I install the SQL instance on the first node in my cluster, it analyses the cluster and comes up with no errors or warnings, i answer all the installation questions and proceed with the installation. When installing the SQL engine I see the status changing as the instance is started and then stopped again etc, finally the status changes to "removing backup files" when it get an error that reports:



"The setup has encountered an unexpected error while Completing Commit. The error is: The specified module could not be found."



Unfortunately there isnt any more information to go on. Any help would be appreciated.

View 2 Replies View Related

SQL Server 2012 :: Create Row Number For A Consecutive Action

Nov 26, 2013

How to create a row number for a consecutive action. Example: I have a listing of people who have either completed a goal or not. I need to count by person the number of consecutively missed goals.

My sql table is this:
PersonId, GoalDate, GoalStatus (holds completed or missed)

My first thought was to use the rownumber function however that doesn’t work because someone could complete a goal, miss a goal, then complete one and when they complete a goal after a missed goal the count has to start over.

View 9 Replies View Related

SQL Server 2012 :: How To Find Continues Number Ranges

Dec 19, 2013

Consider the following data

create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')

[code]....

if status = 0 then unused status = 1 then used

i need to find 10 continues unused numbers

This is the sample data. In live I'm having plenty of data like that . So quite complex.

note : In real world I need to find the 1000 continues number ranges.

View 8 Replies View Related

SQL Server 2012 :: Return Number With 2 Leading Zeroes

May 5, 2015

In a t-sql 2012 select statement, I have a query that looks like the following:

SELECT CAST(ROUND(SUM([ABSCNT]), 1) AS NUMERIC(24,1)) from table1. The field called [ABSCNT] is declared as a double. I would like to know how to return a number like 009.99 from the query. I would basically like to have the following:

1. 2 leading zeroes (basically I want 3 numbers displayed before the decimal point)

2. the number before the decimal point to always display even if the value is 0, and

3. and 2 digits after the decimal point.

View 3 Replies View Related

SQL Server 2012 :: Calculating Number Of Hours In Usage

Jul 23, 2015

I am trying to calculate the number of hours a device has been used and I cant find how. I need a query that calculated and does an average of the number of hrs used in a week.

View 5 Replies View Related

SQL Server 2012 :: How To Choose Max Of Each Groups Formed By Row Number

Sep 15, 2015

I have a below table as:

IF OBJECT_ID('tempdb..#Test') IS NOt NULL
DROP TABLe #Test
--===== Create the test table with
create table #Test([Year] float,
Age Int,
)
INSERT INTO #Test
([Year], Age)

[Code]...

I queried below to get additional column

Select *,row_number() over(partition by [Year] order by Age) as RN from #Test as

YearAgeRN
2014301
2014312
2014323
2015251
2015262
2015273
2015284
2015295

i want one more addtional column (Desired Output) with max of RN in each group as below"

YearAgeRNDesired output
20152515
20152625
20152735
20152845
20152955
20143013
20143123
20143233

View 7 Replies View Related

Page Number &&amp; Records Number

Jun 20, 2006

1. how to show page number & total page number in report body?

2. how to show total records number?

View 25 Replies View Related

SQL Server 2012 :: Case When Statement - N Number Of Years Without Modifications

Feb 4, 2014

Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.

Startdate =
CASE WHEN
Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013'
Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012'
Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011'
Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010'
Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009'
Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008'
END

View 5 Replies View Related

SQL Server 2012 :: Adding A Number To A String To Create Series

Sep 3, 2014

add a number to the end of an ID to create a series.For example, I have an EventID that may have many sub events. If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:

312061
312062
312063
312064

How can I check what the EventID is, then concatenate a sequence number by the EventID?

View 9 Replies View Related

SQL Server 2012 :: Adapting Number Of Columns For SSRS Chart

Oct 10, 2014

For my SSRS 2012 report I've created a dataset to extract the TOP 20 amounts.

I've create a stacked column chart to put these amounts as a values.

But in the chart I can see only ten amounts and not all 20 ones.

20 is a fixed number.

How can I adjust the number of columns in the chart in order to show all 20 amounts?

View 0 Replies View Related

SQL Server 2012 :: Maximum Number Of Global Temporary Tables?

Dec 9, 2014

What is the maximum no.of global temporary tables can create in sql server

View 4 Replies View Related

SQL Server 2012 :: Limitation Of Number Of Objects In TempDB Database?

Dec 9, 2014

how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?

View 2 Replies View Related

SQL Server 2012 :: Query To Get Count Of All Fields Named LX Where X Is A Number

Jan 29, 2015

For example in a table with this fields "field1, L1,L3,L100" field2 the count is 3

it would be better to match a number into the like but i thinks it cannot be done in the like so i've to add another condition to ensure all the text after L is a number.

is this the best way to do it?

Select count(*) from Information_Schema.Columns Where Table_Name = @Table
AND column_name like 'L%' and ISNUMERIC(SUBSTRING(column_name,2, len(column_name)-1))=1

View 6 Replies View Related

SQL Server 2012 :: Number Of Months Between Two Dates In YYYYMM Format

Jun 15, 2015

I am looking to calculate no of months between two dates which are in YYYYMM format.

Like no of months between 201505 and 201305

View 7 Replies View Related

SQL Server 2012 :: 2 Records At A Time?

Nov 15, 2013

I am developing a form for a mortgage company. There can be any number of borrowers on a given loan, and the business has asked that this form return only 2 borrowers at a time for a loan. For example, if there are 3 borrowers for a loan, they want the first copy of the form to print the first 2 borrowers and then another copy of the form to print the 3rd. No matter how many copies are printed, they want the borrower information to be labeled as 'Borrower1' xyz and 'Borrower2' xyz. Also, there will be a LOT more fields returned on the real form, so the sample information below is very simplified test data.

Sample Data:
CREATE TABLE #t (LoanID VARCHAR(5), BorrowerName VARCHAR(20), BorrowerOrder INT);
GO
INSERT INTO #t VALUES
('::E', 'John Smith', 0)
, ('::E', 'Jane Smith', 1)
, ('::E', 'Rob Jackson', 2)
, ('AF_CF', 'Sloan Burton', 1)

[code]...

I don't want that 2nd record to return. This result is what makes me think of gaps and islands, but I don't know if the 2nd record is really an island since it's (1) not stored this way...it's returning this way because of the query and (2) it's not sequential data..I tried restricting this by putting this into a CTE and then returning only the odd numbered records like I have below. This runs pretty quickly when dealing with one loan. But...I am concerned that the CTE will be slow when we run batches of loans.

Attempt with CTE:
--With CTE
;WITH cte AS
(SELECT
Borrower1 = BorrowerName
, Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder)
, RowNumber = ROW_NUMBER() OVER(ORDER BY BorrowerOrder)

[code]...

Is there a better, cleaner way to do this? Or is the CTE the best way to go?

View 8 Replies View Related

SQL Server 2012 :: Manage Concurrency When Users Need To Create Invoice Number

May 31, 2014

I have a db to manage the creation of invoice number designed for a web application.

My problem is how to manage the concurrency when the users need to create an invoice number.

View 9 Replies View Related

SQL Server 2012 :: Populate Number Table Variation With Inline Tally?

Dec 27, 2014

I have to create a table like this across a bunch of servers. I'm thinking that I'm overlooking something with needing two additional CTEs, but maybe not. I have it at 17 seconds, which isn't much faster than a while loop solution that's currently in place.

DECLARE @START DATETIME,
@msg NVARCHAR(MAX) = N''
USE tempdb
SELECT @START = GETDATE()
CREATE TABLE dbo.EulerSource ( [SID] INT, Euler BIGINT )

[Code] ....

View 9 Replies View Related

SQL Server 2012 :: Find Number Of Times Character Appears In A String

Apr 11, 2015

In my staging table I am having data like below

ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯

Now I want to find the Number of times a '¯'character appears in a string. I should get output 14

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved