SQL Server 2014 :: How To Partition And Add Sequence / Rownumber Using CTE
Jul 6, 2015
I have the following table struction, lets call it table A.
bookidstartdate endate
2001 2000-01-01 2000-01-05
3001 2001-01-01 2001-01-02
4001 2002-01-01 2002-01-04
and i want the end result to be look like this in table B.
bookidstartdate endate bookidrowdate bookidlogseqrowsequence
2001 2000-01-012000-01-05 2000_01_01 2001_0 0
2001 2000-01-012000-01-05 2000_02_01 2001_1 1
2001 2000-01-012000-01-05 2000_03_01 2001_2 2
2001 2000-01-012000-01-05 2000_04_01 2001_3 4
3001 2002-02-01 2003-02-02 2000_01_01 3001_0 0
3001 2002-02-01 2003-02-02 2000_02_01 3001_1 1
4001 2002-01-01 2002-01-04 2002-01-01 4001_0 0
4001 2002-01-01 2002-01-04 2002-02-01 4001_1 1
4001 2002-01-01 2002-01-04 2002-02-01 4001_2 2
The script below works but i have a break when datediff (days,startdate, endate) reaches 0. For every bookidm i want to iterate till the datediff is zero then move on to next bookid and do the same thing.
declare @orders table
(
bookid int,
startdate date,
endate date,
rowsequence int
[Code] .....
View 9 Replies
ADVERTISEMENT
Jun 4, 2015
I'm looking to partition a big table into years.
I have a big table with data going back to 2001.
I need to partition this into file groups each for the last 5 years, and the rest into its own file group
I was going to convert the inherent date time field into the format int(yyyymmdd) and partition on that field.
Is this an acceptable way to achieve this? Is there a better way to partition on date? [not time]
View 4 Replies
View Related
Nov 28, 2014
I work for a 24/7 shop. We currently have a table that is partition on monthly. I have to created a script that will add a new file group, add the new file to the group, and alter the the partition scheme and function. However, I need for this process to not cause a lock on the table. Typically I get the locking and issues when I am run the split command. Is there a way to prevent this from happening?
View 4 Replies
View Related
Apr 15, 2015
I have a heavy database , More than 100 GB only for six month .every Query on it takes me along time and I dont have enough space to add more indexes.by a way I decided to do partitioning. I create a partition function , on date filed and all Data records per month was appointed to a separate file.And is partitioning only for Future data entry?
View 9 Replies
View Related
Jun 21, 2015
So, I have some questions about best practice in SQL Server.
1.) I have PK like this (company TINYINT, store TINYINT, action TINYINT, invoice INT, sn SMALLINT). I know JOINS will work faster with surrogate key but I have only couple of JOINS on that table. I use members of PK in WHERE clause mainly, alone and combined for reporting purpose. Is it always better to have surrogate key because they don't have any meaning and context of data laying in current PK.
2.) In my PK from above I have two candidates for using Sequence object. Invoice start with 1 for every (company,store,action) combination. Sn start with 1 for every (company,store,action,invoice) combination. I would like to know can I implement Sequence object here knowing that Sequence don't support PARTITION BY in OVER clause. From what I red it cannot be done via Sequence but I have to ask.Here is data sample for this PK
company store action invoice sn
----------- ----------- ----------- ----------- -----------
1 1 1 2017 1
1 1 1 2018 1
1 1 1 2019 1
1 1 1 2019 2
1 1 1 2019 3
1 1 2 1 1
1 1 2 2 1
1 1 2 2 2
1 1 2 2 3
1 1 2 3 1
1 1 2 3 2
1 1 2 3 3
1 1 2 3 4
1 1 2 3 5
View 7 Replies
View Related
Oct 16, 2014
I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths. eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
Table BIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR--> 1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN10|10|4|10
Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
View 2 Replies
View Related
Jun 29, 2015
I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.
how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.
what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?
what is 64 KB cluster has 128 sectors?
tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB
View 3 Replies
View Related
May 11, 2007
Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
View 1 Replies
View Related
Aug 26, 2007
I am trying to write a stored procedure to be used for custompaging and I get error with the below SP. "Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."
What am I doing wrong?
CREATE PROCEDURE dbo.GetDealerSP
(@startRowIndex int,@maximumRows int)AsSELECT installersemaid,dealerid,[name],address1,address2,city,[state],zip,phone,faxFrom(SELECT installersemaid,dealerid,[name],address1,address2,city,[state],zip,phone,fax, ROW_NUMBER() OVER(ORDER BY [name] DESC)AS RowbankFROM dealerenrollment)as DealerWithRowNumbersWHERE Rowbank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)Go
View 2 Replies
View Related
Apr 26, 2007
I understand that if using this function with "nothing" between parentheses, then the running row total never resets.
However, I am hoping to have the row counter reset when a group value changes. I tried putting both the field and the defined group name in the parentheses both with and without quotes, but I get an error.
What is the correct syntax for accomplishing this?
View 1 Replies
View Related
May 14, 2008
Hi,
I have TWO columns i.e TIME and ACTION
i want to display RowNumber for that.
Like this...
TIME ACTION
12:40:01 BUY
12:40:31 BUY
12:40:51 BUY
12:41:01 SELL
12:41:21 SELL
12:41:31 SELL
12:41:41 SELL
12:41:51 BUY
12:42:01 BUY
12:42:29 SELL
12:42:31 SELL
12:42:41 SELL
12:42:51 BUY
It should display like this...
TIME ACTION RowNo
12:40:01 BUY 1
12:40:31 BUY 2
12:40:51 BUY 3
12:41:01 SELL 1
12:41:21 SELL 2
12:41:31 SELL 3
12:41:41 SELL 4
12:41:51 BUY 1
12:42:01 BUY 2
12:42:29 SELL 1
12:42:31 SELL 2
12:42:41 SELL 3
12:42:51 BUY 1
View 10 Replies
View Related
Apr 11, 2008
ID voucher amt
1 989 11.00
1 9876 234.00
2 677 678.99
3 789 3837
3 888 3733.77
3 890 66.43
I am trying to a rownumer using ROWNUMBER() OVER () function group by ID and order by voucher
The ourput should look like
ID voucher amt Linenum
1 989 11.00 00001
1 9876 234.00 00002
2 677 678.99 00001
3 789 3837 00001
3 888 3733.77 00002
3 890 66.43 00003
How can I do this? can you show me some example?
View 4 Replies
View Related
May 17, 2006
Hi All,
I have following table structure,
----------------------------------------------------------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------------------------------------------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230
I want a sql query which select all the record with a serial number eg:
---------------------------------------------------------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------------------------------------------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230
View 5 Replies
View Related
Nov 29, 2007
Hi All
We have a requirement of creating a report which has one data set
And the same data set is being used accross different tables in the report. The tables differ by the type of grouping applied on them.
My situation is
1. The table has two groups applied
a) table level group is on DIVISION
b) detail grouping is done based on two columns:
SYSTEM and NAME
2. When i use the expression rownumber("DIVISION") or rownumber("SYSTEM_NAME"), it does not return a proper sequence
I need the row numbers to be generated based on the grouping on (SYSTEM and NAME) for each DIVISON
Can someone please explain how this can be done?
View 3 Replies
View Related
Apr 22, 2008
I got problem with using custom paging in sql 2005
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[searchperson_view_general]
@Search nvarchar(2000)
,@OrderBy nvarchar (2000)
,@PageSize int
,@PageIndex int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
--Default order by to first column
IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
SET @OrderBy = 'p.[person_id]'
END
-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT distinct'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex '
SET @SQL = @SQL + ', p.[person_id]'
SET @SQL = @SQL + ', p.[userType_id]'
SET @SQL = @SQL + ', p.[fullName]'
SET @SQL = @SQL + ', p.[gender_nm]'
SET @SQL = @SQL + ', p.[dateOfBirth] '
SET @SQL = @SQL + ', p.[positionTitle]'
SET @SQL = @SQL + ' FROM dbo.[person_view] p '
IF LEN(@Search) > 0
BEGIN
SET @SQL = @SQL + @Search
END
SET @SQL = @SQL + ' ) SELECT distinct'
SET @SQL = @SQL + ' p.person_id'
SET @SQL = @SQL + ', p.userType_id'
SET @SQL = @SQL + ', p.fullName'
SET @SQL = @SQL + ', p.gender_nm'
SET @SQL = @SQL + ', (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth] '
SET @SQL = @SQL + ', p.positionTitle'
SET @SQL = @SQL + ' FROM PageIndex p '
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
exec sp_executesql @SQL
I checked my store procedure with parameters
exec [hr2b_searchperson_view_general_load]
'LEFT OUTER JOIN qualification
ON p.person_id = qualification.person_id
WHERE qualification.institutionName like N''%ABC%'''
,' p.person_id asc ', 25 , 1
This is a actual query show :
WITH PageIndex AS
( SELECT distinct TOP 49 ROW_NUMBER() OVER
(ORDER BY p.person_id asc )
as RowIndex
, p.[person_id]
, p.[userType_id]
, p.[fullName]
, p.[gender_nm]
, p.[dateOfBirth]
, p.[positionTitle]
FROM person_view p
LEFT OUTER JOIN qualification
ON p.person_id = qualification.person_id
WHERE qualification.institutionName like N'%ABC%' )
SELECT distinct
p.person_id
, p.userType_id
, p.fullName
, p.gender_nm
, (year(getdate()) - year(p.[dateOfBirth])) as [dateOfBirth]
, p.positionTitle
FROM PageIndex p
WHERE RowIndex > 25 AND RowIndex <= 49 ORDER BY p.person_id asc
If I used this query without using DISTINCT it will return extactly number of records which I expected but It is duplicated.
Then I tried to use DISTINCT in this query number of records return is less than 25 records . Because it was duplicated records when I used LEFT OUTER JOIN.But my query will be able to use more LEFT OUTER JOIN than this query. Please help me get exactly 25 records?
This is my tables
person_view(person_id, fullname, userType_id, gender_nm, dateOfBirth, positionTitle)
Qualification(qualification_id, qualification_nm,institutionName, person_id)
Thanks in advance.
View 3 Replies
View Related
Nov 1, 2006
HI ...
I have a detailed report ..with summary lines and detailed lines (drill down).
I have a column with a function "RowNumber(Nothing)" which is supposed to just count the rows
when I put this in the summary row for the column I need in the Design Layout section and run the report, I get numbers on the summary lines which include the number of rows in the level below (detailed rows).
I just want to number the summary rows sequentially without taking into consideration, the number of detailed rows.
How do I modify RowNumber(Nothing) to exclude counting the detailed rows....???
Any help will be much appreciated...thanks
View 9 Replies
View Related
Aug 28, 2007
Hi,
I am using SQL Server 2000 SP3 Version 8.00.7.60. Can I create instances of SQL server 2000 even if it's already installed?
Thanks for your help in advance!
View 8 Replies
View Related
Mar 27, 2007
Most examples for SQL Server 2005 involve a sales table that you split based on date, i.e. sales records prior to 2000 go to this partition, and the ones after that go to another one. Nice and simple.
Say I have a sales table:
id Amount Date
1 10 1/1/1999
2 9.99 1/1/2007
Now then, I put all the records prior to 2000 in it's own partition.
So when I do something like this: SELECT * FROM Sales WHERE DATE = 1/1/1999
the SQL server will know which partition to look at. Very nice.
Now then, if I do this: SELECT * FROM Sales WHERE id = 1
How will the SQL server know which partition to look at?
Thank You!
View 5 Replies
View Related
Apr 25, 2001
I want to show a row number for each row returned by a SELECT starting at 1 and incrementing by 1 for each row.
Anyone got any ideas?
View 2 Replies
View Related
Mar 27, 2008
Hi,
I create a matrix table with wizard. I want to write rownumber() as a new column near my column.
But when =Rownumber(nothing) it returns the value of the record in the dataset. If the data in the 56.row then
=Rownumber(nothing) =56
But in the preview of table it is in 1.row.
What can I do?
View 9 Replies
View Related
Apr 20, 2015
I have a requirement to find the rowmumbers based on a group. I know there is Rownumber function in sql. How can I implement the same in DAX. PFA screenshot. Rownumber is the calculated field i want based on the id and name column group.
View 8 Replies
View Related
Jan 21, 1999
Hi!
I'm installing a new SQL Server machine. During NT Server
installation our NT support guy converted the only 2GB FAT
C: partition to NTFS. So as of right now all my 4 8GB drives are
NTFS. I think it would be better to keep this C: partition in FAT
because, as of my knowledge, having FAT boot partition can help
to boot the machine in case of NT crash.
Is there anything that I'm really losing by this conversion to NTFS or I
should not be worried so much about it? Does it put my SQL Server
databases, database .dat files or NT Server in more danger situation
in case of any crash?
Or it's giving me some advantages?
Thanks
Ninel
View 2 Replies
View Related
May 15, 2007
hello,
We are writting an erp for group,and want to use partition of sql server 2005 for large data,our environment is window 2003 server,8g memory.my one solution will create many physical files(about 500-600) to fit 500-600 partitions,how about performance? or I will use another arithmetic in order to reduce partitions?
View 4 Replies
View Related
Jul 5, 2000
Not nice but a work around: is there a way to get the amount of fields or rows from a table or query within MS SQL, to pick out the lastfield-4 for example ?
SELECT LASTFIELD-4
FROM CUSTOMERS
Thanks
View 1 Replies
View Related
Mar 17, 2014
I will try my best to explain this, We have a shopping cart on our website, the person that was developing this has now left the company and I've been given the job to finish it off.
When I load all the items that the user has entered in to his/her cart I return the Item ID and the RowNumber (ROW_NUMBER() OVER (Order by Id) AS RowNumber)
I'm trying to delete the item from the table using the following query
DELETE FROM [dbo].[Cart.Items] WHERE UniqueID = UniqueID and ItemID = @ItemID and @RowNumber IN (
SELECT ROW_NUMBER() OVER (Order by Id) AS RowNumber)
Now the reason we are using the RowNumber is because the user can add the same Item as many times as they like so for example you buy 3 different mobile phones, and you want three screen protectors, they will click screen protector 3 times which will add 3 records in to the db with the same id. so the row number is used to find the correct one.
But the above delete is not working.
View 1 Replies
View Related
Apr 26, 2007
I have a report with details grouping on table. What i need to do is put row number only on Parent row and skip the child row. When i use RowNumber("GroupName") of course it gives me a current RowNumber. Is there a way to count only parents?
View 3 Replies
View Related
Nov 8, 2006
Hi there
I have a sales report that is pulling up data and displaying the detailed lines and the aggregate/summary lines grouped by a single field.
THe report say has 10 summary/aggregate lines and each summary lines have a maybe 20 more lines to it.
I want to NUMBER the SUMMARY lines only. In a new column on the summary line cell, if I type, =RowNumber(Nothing), I get a count of all its sub-lines displayed. How can I limit the scope of numbering to just the summary lines and make sure it does not include the sub lines involved ?
View 7 Replies
View Related
Jun 5, 2007
hi all,
i am working on a small "Biztalk" engine, by creating dynamic ssis packages that change according to the client source file definition.
in order to create a row-number to each row in my input file, i am tryng to add the Konesans's Row Number component to the dynamic package by using SSIS API, but i get a lot of errors. the component is not created as a"rowNumber" component, but as a General Managed Component, though i use the ComponentClassID as the classID in the RowNumber component.
has anybody try to do this ?
is there any way to get the row number other then this way?
any ideas?
thank you!!! for all your help until now!!
View 1 Replies
View Related
Jun 8, 2015
I have got question because time is running but I still don't know how to do it.
So I have 1 group with 3 rows and I would like to put for each row not only group individual number like 1,2,3,4,5 ... etc until end of report. Generally using fuction RowNumber I got 1,2,3 and then 1,2,3 again.
View 9 Replies
View Related
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
Aug 4, 2007
I have created the T1 table which will accept US records and T2 table for any other country.
CREATE TABLE T1(I INT unique ,name varchar(10) CHECK(name = 'US') PRIMARY KEY(I,name))
CREATE TABLE T2(I INT unique ,name varchar(10) ,CHECK(name NOT IN ('US')) PRIMARY KEY(I,name))
Then Created the partitioned View using below script
CREATE VIEW V AS
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
I am able to insert the US records using view
insert into V values (1,'us')
Problem:
I am not able to insert the non US records like UK and JN...
It is thorwing the exception like partition column not find.
eg : insert into V values (1,'uk')
PLease help me to insert the non US records into the View
View 5 Replies
View Related
Mar 14, 2008
I have to import data from a flatfile into our datawarehouse. The supplier of the flat file isn't able to give me a delta, he just gives me a flat files with all the changes on a certain table.
So for instance when a row in a table is updated 3 times, I get 3 rows in my flatfile with the 3 updates and I only need the last one.
The determine wich record is updated I need to combine 6 columns to be sure if I'm looking at the right row in the source database.
Is their a way to accomplish this?
I'm pretty new to those advanced SSIS things and I tried to look for a way to do it with conditional splits etc...
But honestly, I don't have a clue where to start.
Any help is appreciated.
Regards
Zekske
View 1 Replies
View Related
Apr 18, 2000
I want to move the SQL Server 7.0 root directory from C:MSSQL7 to D:MSSQL7 (same host). I see the startup parameters under server properties could be changed to point to the new location, but Master has the paths to all the databases recorded in sysdatabases table, so at least that would have to change, and sysdevices table points to files on C:. And maybe the NT registry points to some files there too. Anyway, is there some established method to move it? If that isn't practical, at least I would want to move TEMPDB ( perhaps by using the SP_DETACH and SP_ATTACH method ). My main database is already on D: so I do not have to move it. Any informed advice would be appreciated. Thanks.
View 1 Replies
View Related
