June 16, 2011 at 1:04 pm
Hi
Can someone help me write a query to increment each row number by 10 include 5 characters(000 at the beginning) . For example, i would want my results to look exactly like this in the Document Item No
00010
00020
00030
00040
00050
00060
00070
00080
00090
00100
00110
etc
--Create Table Product
CREATE TABLE Product(
Vendor [varchar] (10) null,
Shipto [varchar] (10)null,
Weeks [varchar] (10)null,
Product[varchar] (10)null,
Amount [int] null
)
-- Drop Table Product
Drop table Product
--Insert Data into Product Table
INSERT INTO dbo.Product
VALUES('454638','CA','1','Hard Drive',100)
INSERT INTO dbo.Product
VALUES('738372','SP','2','PC',64)
INSERT INTO dbo.Product
VALUES('4536373','EN','1','Laptop',30)
INSERT INTO dbo.Product
VALUES('6473839','GU','1','Cables',89)
INSERT INTO dbo.Product
VALUES('3635272','AF','4','Wires',20)
INSERT INTO dbo.Product
VALUES('0393837','JP','6','NAS',5)
INSERT INTO dbo.Product
VALUES('3637822','N','8','Router',19)
INSERT INTO dbo.Product
VALUES('8373632','USA','1','Printer',36)
INSERT INTO dbo.Product
VALUES('7636363','WA','10','Software',45)
INSERT INTO dbo.Product
VALUES('3636373','SC','9','WMware',4)
-- My Attempt so far
SELECT 'Pl01_'+ RTRIM(Vendor) + '_' + RTRIM(Shipto) + '_'+RTRIM(CONVERT(char(20),GETDATE(),112))+'_'+CONVERT(CHAR(8),SYSDATETIME(),114)AS[Document No]
,ROW_NUMBER()over(Order by P.Vendor)+10 AS [Document Item No]
,P.Vendor
,P.Shipto
,P.Weeks
,P.Product
,P.Amount
FROM dbo.Product P
WHERE P.Weeks = 1
Can someone help me adjust my code to achieve this result
Many Thanks
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 16, 2011 at 1:13 pm
Tx for the script.. took only 30 seconds to do!
,RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(10), ROW_NUMBER()over(Order by P.Vendor)+10) + '0', 5) AS [Document Item No]
June 16, 2011 at 1:26 pm
Many Thanks for your reply Ninja's_RGR'us
I need it to start from 00010, 00020 00030 etc could you show me how that's done
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 16, 2011 at 1:30 pm
Not to worry I've sorted it out
[Code]
RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(10), ROW_NUMBER()over(Order by P.Vendor)+0) + '0', 5) AS [Document Item No]
[/Code]
Thanks a lot for your help
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 17, 2011 at 8:43 am
Hi
I would like to make slight chances to the code above i still want it to increment by 10 but to be unique according to the
Vendor and Shipto in the Document No column
So i would like the results to look like this
[Code]
Document No DocumentItemNo Vendor Shipto Weeks Product Amount
PL01_4536373_EN_20110617_15:28:48000104536373EN1 Laptop 30
PL01_4536373_EN_20110617_15:28:48000204536373EN1 Laptop 30
PL01_454638_CA_20110617_15:28:4800010454638CA1 Hard Drive 100
PL01_454638_CA_20110617_15:28:4800020454638CA1 Hard Drive 100
PL01_454638_CA_20110617_15:28:4800030454638CA1 Beer 100
PL01_6473839_GU_20110617_15:28:48000106473839GU1 Cables 89
PL01_6473839_GU_20110617_15:28:48000206473839GU1 Cables 89
PL01_6473839_GU_20110617_15:28:48000306473839GU1 Cables 89
PL01_8373632_USA_20110617_15:28:48000108373632USA1 Printer 36
PL01_8373632_USA_20110617_15:28:48000208373632USA1 Printer 36
PL01_8373632_USA_20110617_15:28:48000308373632USA1 Printer 36
[/Code]
So in the document no column when its all the EN would start from 00010 in the results above there is only two so it would be 00010, 00020 then restarts again according to the Ship to and vendor
[Code]
--Insert extra data into Product table
INSERT INTO dbo.Product
VALUES('454638','CA','1','Hard Drive',100)
INSERT INTO dbo.Product
VALUES('738372','SP','2','PC',64)
INSERT INTO dbo.Product
VALUES('4536373','EN','1','Laptop',30)
INSERT INTO dbo.Product
VALUES('6473839','GU','1','Cables',89)
INSERT INTO dbo.Product
VALUES('3635272','AF','4','Wires',20)
INSERT INTO dbo.Product
VALUES('0393837','JP','6','NAS',5)
INSERT INTO dbo.Product
VALUES('3637822','N','8','Router',19)
INSERT INTO dbo.Product
VALUES('8373632','USA','1','Printer',36)
INSERT INTO dbo.Product
VALUES('7636363','WA','10','Software',45)
INSERT INTO dbo.Product
VALUES('3636373','SC','9','WMware',4)
INSERT INTO dbo.Product
VALUES('0663635','CA','11','Store',4)
INSERT INTO dbo.Product
VALUES('9367363','CA','23','Network',4)
INSERT INTO dbo.Product
VALUES('3636373','CA','33','Dell',4)
INSERT INTO dbo.Product
VALUES('2636373','CA','9','HP',4)
INSERT INTO dbo.Product
VALUES('1663635','GU','11','Fan',4)
INSERT INTO dbo.Product
VALUES('6367363','GU','93','NAS',4)
INSERT INTO dbo.Product
VALUES('7636373','GU','56','Light',4)
INSERT INTO dbo.Product
VALUES('8636373','GU','100','Keyboard',4)
INSERT INTO dbo.Product
VALUES('963635','USA','15','Mat',4)
INSERT INTO dbo.Product
VALUES('9847363','USA','88','Mouse',4)
INSERT INTO dbo.Product
VALUES('1467382','USA','99','Cable',4)
[/code]
--[My attempt]
SELECT 'PL01_'+ RTRIM(Vendor) + '_' + RTRIM(Shipto) + '_'+RTRIM(CONVERT(char(20),GETDATE(),112))+'_'+CONVERT(CHAR(8),SYSDATETIME(),114)AS[Document No]
,RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(10), ROW_NUMBER()over(Order by Vendor)+0) + '0', 5)AS[DocumentItemNo]
,P.Vendor
,P.Shipto
,P.Weeks
,P.Product
,P.Amount
FROM dbo.Product P
WHERE P.Weeks = 1
Can you help me achieve this
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 17, 2011 at 8:47 am
Steve
You just need to use a PARTITION BY clause in your ROW_NUMBER function.
John
June 20, 2011 at 1:58 am
Hey John Mitchell I took your advice and included PARTITION BY which has worked perfectly, thanks for the advice
Sample
SELECT 'PL01_'+ RTRIM(Vendor) + '_' + RTRIM(Shipto) + '_'+RTRIM(CONVERT(char(20),GETDATE(),112))+'_'+CONVERT(CHAR(8),SYSDATETIME(),114)AS[Document No]
,Ltrim((RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(10), ROW_NUMBER()over(PARTITION BY ShipTo ORDER BY Vendor)+0) + '0', 5)))AS[DocumentItemNo]
,P.Vendor
,P.Shipto
,P.Weeks
,P.Product
,P.Amount
FROM dbo.Product P
WHERE P.Weeks = 1
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply