July 25, 2017 at 8:55 am
Ok so what I am wanting to do is fill in rows with information that is missing from a file. I read a file insert it into a table but there is data missing from the file format. I've added an excel file so it's easier to understand what I am trying to accomplish. The highlighted cells are of the missing data I need to fill in. I have the data in a sql table and I am trying to insert it into a final table where the other files are being read are being inserted. This is the only file that is completely different format. I just need to know what is the best way to find the last row and insert the "Ship to Customer" number and the "sh Long Description", I have tried
WITH etl as
(
SELECT [ID]
,[Ship to Customer]
,Row_Number() OVER
(PARTITION BY REPLACE([Catch Weight],'N','Y') ORDER BY ID ASC) AS Rank
FROM [WH_Stores].[dbo].[TONYS]
where [Ship to Customer] <> ''
)
select * from etl
order by rank asc
but doesn't seem to work as I thought it would, any suggestions or ideas would be greatly appreciated.
July 25, 2017 at 9:21 am
can you post create/insert exmaple data scripts for the table [WH_Stores].[dbo].[TONYS]?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 25, 2017 at 9:31 am
CREATE TABLE [dbo].[TONYS_v2](
[customerfileid] [int] NULL,
[ID] [int] NULL,
[Ship to Customer] [nvarchar](10) NULL,
[sh Long Description] [nvarchar](40) NULL,
[Item Number] [varchar](25) NULL,
[Brand Code] [nvarchar](20) NULL,
[Long Description] [nvarchar](50) NULL,
[Catch Weight] [nvarchar](3) NULL,
[Item Pack] [int] NULL,
[Item Size] [nvarchar](10) NULL,
[QTY1] [int] NULL,
[QTY2] [int] NULL,
[WEIGHT1] [float] NULL,
[WEIGHT2] [float] NULL,
[Field13] [float] NULL,
[Field14] [float] NULL,
[ERP_CUSTOMER_NUMBER] [varchar](30) NULL
) ON [PRIMARY]
INSERT INTO TONYS_v2 From SELECT [ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14]
FROM [WH_Stores].[dbo].[TONYS_20170417]
July 25, 2017 at 9:42 am
cbrammer1219 - Tuesday, July 25, 2017 9:31 AM
CREATE TABLE [dbo].[TONYS_v2](
[customerfileid] [int] NULL,
[ID] [int] NULL,
[Ship to Customer] [nvarchar](10) NULL,
[sh Long Description] [nvarchar](40) NULL,
[Item Number] [varchar](25) NULL,
[Brand Code] [nvarchar](20) NULL,
[Long Description] [nvarchar](50) NULL,
[Catch Weight] [nvarchar](3) NULL,
[Item Pack] [int] NULL,
[Item Size] [nvarchar](10) NULL,
[QTY1] [int] NULL,
[QTY2] [int] NULL,
[WEIGHT1] [float] NULL,
[WEIGHT2] [float] NULL,
[Field13] [float] NULL,
[Field14] [float] NULL,
[ERP_CUSTOMER_NUMBER] [varchar](30) NULL
) ON [PRIMARY]INSERT INTO TONYS_v2 From SELECT [ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14]
FROM [WH_Stores].[dbo].[TONYS_20170417]
Thanks for table script.....now we need some data please
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 25, 2017 at 9:45 am
That is what is in the file attached. Or are you meaning write the script to insert it into the table?
July 25, 2017 at 9:53 am
cbrammer1219 - Tuesday, July 25, 2017 9:45 AMThat is what is in the file attached. Or are you meaning write the script to insert it into the table?
yes please....tis std practice.....not many will open an excel file from untrusted source
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 25, 2017 at 10:04 am
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (1,'17000215','TIMES SUPER #2 KAHALA','41172','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (2,'','','41220','ORGANIC','**JIT Chicken, Org Drum / Thigh Combo','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (3,'','','36720','ORGANIC','**JIT Chicken, Bnls Sknls Breast','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (4,'','','40329','ORGANIC','**JIT Chicken, Bnls Sknls Thighs','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (5,'','','40326','ORGANIC','**JIT Whole Wing Organic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (6,'','','41171','ORGANIC','41173','Chicken, Organic Thighs','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (7,'','','41173','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (7,'','','40692','ORGANIC','Sausage, Organic Sweet Apple','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
July 25, 2017 at 10:20 am
cbrammer1219 - Tuesday, July 25, 2017 10:04 AMINSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (1,'17000215','TIMES SUPER #2 KAHALA','41172','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (2,'','','41220','ORGANIC','**JIT Chicken, Org Drum / Thigh Combo','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (3,'','','36720','ORGANIC','**JIT Chicken, Bnls Sknls Breast','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (4,'','','40329','ORGANIC','**JIT Chicken, Bnls Sknls Thighs','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (5,'','','40326','ORGANIC','**JIT Whole Wing Organic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (6,'','','41171','ORGANIC','41173','Chicken, Organic Thighs','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (7,'','','41173','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID]
,[Ship to Customer]
,[sh Long Description]
,[Item Number]
,[Brand Code]
,[Long Description]
,[Catch Weight]
,[Item Pack]
,[Item Size]
,[QTY1]
,[QTY2]
,[WEIGHT1]
,[WEIGHT2]
,[Field13]
,[Field14])
VALUES (7,'','','40692','ORGANIC','Sausage, Organic Sweet Apple','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
Hello....did you test this before posting?
You need some more work...one issue is that this will throw "The number of columns for each row in a table value constructor must be the same.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 25, 2017 at 10:58 am
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ( [ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (1,'17000215','TIMES SUPER #2 KAHALA','41172','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (2,'','','41163','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (3,'','','41162','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (4,'','','41164','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (5,'','','41182','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (6,'','','41170','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (7,'','','41111','ORGANIC','Chicken, Boneless Skinless BreastOrganic','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (8,'','','40692','ORGANIC','Sausage, Organic Sweet Apple','N','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (9,'17000815','TIMES SUPER #8-BERETANIA','40692','ORGANIC','Sausage, Organic Sweet Apple','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (10,'','','41692','ORGANIC','Sausage, Organic Sweet Apple','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (11,'','','41693','ORGANIC','Sausage, Organic Sweet Apple','N','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (12,'','','41688','ORGANIC','Sausage, Organic Sweet Apple','Y','12','1#','3','4','42.49','53.62','245.45','307.10');
INSERT INTO [WH_Stores].[dbo].[TONYS_20170417] ([ID],[Ship to Customer],[sh Long Description],[Item Number],[Brand Code],[Long Description],[Catch Weight],[Item Pack],[Item Size],[QTY1],[QTY2],[WEIGHT1],[WEIGHT2],[Field13],[Field14])
VALUES (13,'','','41
July 25, 2017 at 11:01 am
here's some sample code ...I think it does what you are asking
reference http://sqlmag.com/t-sql/last-non-null-puzzle
CREATE TABLE #yourtable(
ID INT NOT NULL
,Shipto INT
,Description VARCHAR(25)
,ItemN INTEGER NOT NULL
);
INSERT INTO #yourtable(ID,Shipto,Description,ItemN) VALUES
(1,17000215,'TIMES SUPER #2 KAHALA',41172),(2,NULL,NULL,41220),(3,NULL,NULL,36720),(4,NULL,NULL,40329),(5,NULL,NULL,40326),(6,NULL,NULL,41171)
,(7,NULL,NULL,41173),(8,NULL,NULL,40692),(9,NULL,NULL,36723),(10,NULL,NULL,40718),(11,NULL,NULL,41169),(12,NULL,NULL,40320),(13,NULL,NULL,41168)
,(15,17000815,'TIMES SUPER #8-BERETANIA',41220),(16,NULL,NULL,36720),(17,NULL,NULL,40326),(18,NULL,NULL,41173),(19,NULL,NULL,41212)
,(20,NULL,NULL,40320),(21,NULL,NULL,41168)
,(23,17001015,'TIMES SUPER#10-KOOLAU',41172),(24,NULL,NULL,36716),(25,NULL,NULL,36720),(26,NULL,NULL,40329),(27,NULL,NULL,41173);
--one solution ref http://sqlmag.com/t-sql/last-non-null-puzzle
SELECT ID,
Shipto,
Description,
ItemN,
CAST(SUBSTRING(MAX(CAST(id AS BINARY(4)) + CAST(shipto AS BINARY(4))) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING), 5, 4) AS INT) AS shipto_new,
CAST(SUBSTRING(MAX(CAST(id AS BINARY(4))+CAST(NULLIF(Description, '') AS VARBINARY(25))) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING), 5, 25) AS VARCHAR(25)) AS description_new
FROM #yourtable
ORDER BY ID;
DROP TABLE #yourtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 25, 2017 at 12:24 pm
So do I need to turn all blank fields to NULLS? Because it seems to work other than my fields have blanks and you inserted NULLS into the blank fields. Will it not work if I select all of the fields, because I do need all fields in the table, to write an ETL into the final table. Which is like this.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SALES_DATA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ERP_CUSTOMER_NUMBER] [varchar](30) NULL,
[WH_ID] [varchar](50) NULL,
[NAME] [varchar](100) NULL,
[WH_PART_CODE] [varchar](50) NULL,
[WH_STORE_ID] [varchar](50) NULL,
[STORE_PART_CODE] [varchar](50) NULL,
[SC_STORE_ID] [varchar](100) NULL,
[SC_PART_CODE] [varchar](75) NULL,
[CASES] [decimal](10, 4) NULL,
[WEIGHT] [decimal](10, 4) NULL,
[FILE_ID] [int] NULL,
[SC_START_DATE] [date] NULL,
[SC_START_DATE_KEY] [int] NULL,
[WH_START_DATE] [date] NULL,
[WH_START_DATE_KEY] [int] NULL,
[PERIOD ID] [int] NULL,
[STORE DELIVERY DATE] [date] NULL,
[STORE DELIVERY DATE_KEY] [int] NULL,
[ERP_CUSTOMER_NAME] [varchar](75) NULL,
[ACCT_COMPANY_NAME] [varchar](75) NULL,
[PART_CODE_DESCRIPTION] [varchar](50) NULL,
[ERP_WEEK] [smallint] NULL
) ON [PRIMARY]
July 25, 2017 at 12:27 pm
cbrammer1219 - Tuesday, July 25, 2017 12:24 PMSo do I need to turn all blank fields to NULLS? Because it seems to work other than my fields have blanks and you inserted NULLS into the blank fields. Will it not work if I select all of the fields, because I do need all fields in the table, to write an ETL into the final table. Which is like this.SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SALES_DATA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ERP_CUSTOMER_NUMBER] [varchar](30) NULL,
[WH_ID] [varchar](50) NULL,
[NAME] [varchar](100) NULL,
[WH_PART_CODE] [varchar](50) NULL,
[WH_STORE_ID] [varchar](50) NULL,
[STORE_PART_CODE] [varchar](50) NULL,
[SC_STORE_ID] [varchar](100) NULL,
[SC_PART_CODE] [varchar](75) NULL,
[CASES] [decimal](10, 4) NULL,
[WEIGHT] [decimal](10, 4) NULL,
[FILE_ID] [int] NULL,
[SC_START_DATE] [date] NULL,
[SC_START_DATE_KEY] [int] NULL,
[WH_START_DATE] [date] NULL,
[WH_START_DATE_KEY] [int] NULL,
[PERIOD ID] [int] NULL,
[STORE DELIVERY DATE] [date] NULL,
[STORE DELIVERY DATE_KEY] [int] NULL,
[ERP_CUSTOMER_NAME] [varchar](75) NULL,
[ACCT_COMPANY_NAME] [varchar](75) NULL,
[PART_CODE_DESCRIPTION] [varchar](50) NULL,
[ERP_WEEK] [smallint] NULL
) ON [PRIMARY]
data insert script please
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 25, 2017 at 12:36 pm
varchars as empty strings
CREATE TABLE #yourtable(
ID INT NOT NULL
,Shipto varchar(25)
,Description VARCHAR(25)
,ItemN INTEGER NOT NULL
);
INSERT INTO #yourtable(ID,Shipto,Description,ItemN) VALUES
(1,'17000215','TIMES SUPER #2 KAHALA',41172),(2,'','',41220),(3,'','',36720),(4,'','',40329),(5,'','',40326),(6,'','',41171)
,(7,'','',41173),(8,'','',40692),(9,'','',36723),(10,'','',40718),(11,'','',41169),(12,'','',40320),(13,'','',41168)
,(15,'17000815','TIMES SUPER #8-BERETANIA',41220),(16,'','',36720),(17,'','',40326),(18,'','',41173),(19,'','',41212)
,(20,'','',40320),(21,'','',41168)
,(23,'17001015','TIMES SUPER#10-KOOLAU',41172),(24,'','',36716),(25,'','',36720),(26,'','',40329),(27,'','',41173);
--one solution ref http://sqlmag.com/t-sql/last-non-null-puzzle
SELECT ID,
Shipto,
Description,
ItemN,
CAST(SUBSTRING(MAX(CAST(id AS BINARY(4)) + CAST(NULLIF(shipto, '') AS VARBINARY(25))) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING), 5, 25) AS VARCHAR(25)) AS shipto_new,
CAST(SUBSTRING(MAX(CAST(id AS BINARY(4)) + CAST(NULLIF(Description, '') AS VARBINARY(25))) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING), 5, 25) AS VARCHAR(25)) AS description_new
FROM #yourtable
ORDER BY ID;
DROP TABLE #yourtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 25, 2017 at 12:39 pm
I don't have the script for inserting into the final table yet, that is why I need this data to be like SELECT * FROM [TONYS_20170417], with those 2 fields filled in with the [Ship to Customer] and [sh Long Description] then I can create the ETL, I hope this makes sense.
July 25, 2017 at 1:20 pm
cbrammer1219 - Tuesday, July 25, 2017 12:39 PMI don't have the script for inserting into the final table yet, that is why I need this data to be like SELECT * FROM [TONYS_20170417], with those 2 fields filled in with the [Ship to Customer] and [sh Long Description] then I can create the ETL, I hope this makes sense.
I am sorry, but I am failing to understand why you cannot create your ETL script based on the code samples I provided.
please provide a set up script (with correct table names (no need for db schemas) and data insert script.....for input and output tables.
...and providing I have time this evening, I will post back.
Please read this article for a comprehensive and tried nd trusted method of providing sufficient detail in one single post to enable you to help us....thank you
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply