November 17, 2016 at 1:43 pm
I was recently assigned to work on a client's spreadsheet data which consists 4 sheets for each quarter's data, each sheet's structure is similar but differential by some key value in them, please see the screenshot:
Other than the 6 lines header, you can see there are some standard columns for each sheet like:
ProjectWork Number
ProjectWork Name
for each project, there are different by:
Employee Number
Resource Type
Location
Resource Role
Shore Type
Preferred Vendor
for each sheet, they contain the above columns with different content, plus, each week in the respective quarter, so the first sheet (Q1 2016)'s columns are:
ProjectWork Number
ProjectWork Name
Employee Number
Resource Type
Location
Resource Role
Shore Type
Preferred Vendor
Nov 01, 2015 (yes, no kidding)
Nov 08, 2015
.....
Jan 31, 2016
and the second sheet(Q2 2016)'s columns are
ProjectWork Number
ProjectWork Name
Employee Number
Resource Type
Location
Resource Role
Shore Type
Preferred Vendor
Jan 31, 2016
Feb 07, 2016
Feb 14, 2016
....
Apr 24, 2016
The third sheet(Q3 2016):
ProjectWork Number
ProjectWork Name
Employee Number
Resource Type
Location
Resource Role
Shore Type
Preferred Vendor
May 01, 2016
May 08, 2016
May 15, 2016
....
Jul 31, 2016
the fourth sheet(Q4 2016):
ProjectWork Number
ProjectWork Name
Employee Number
Resource Type
Location
Resource Role
Shore Type
Preferred Vendor
Jul 31, 2016
Aug 07, 2016
Aug 14, 2016
....
Oct 30, 2016
Yes you might notice there are overlapped week for Q1 and Q2 on Jan 31, 2016, they are NOT duplicated or redundant, and there is no overlap between Q2 and Q3, but there are overlap for Q3 and Q4
I wonder who has experience on processing data like this, I guess this format is actually considered as common by financial person so I really need to find a practical way to import them.
Any help is greatly appreciated.
Thanks in advance.
BTW: it is not realistic to ask client to use the format we want.
November 17, 2016 at 2:02 pm
I am thinking the final table be designed like this:
key columns:
ProjectWork Number
ProjectWork Name
non-key columns:
Employee Number
Resource Type
Location
Resource Role
Shore Type
Preferred Vendor
Value columns:
QuarterNo
WeekNo
WeekValue
Does that make sense? is there a better design? Thanks for your input.
November 17, 2016 at 2:41 pm
Now I have imported 4 original sheets into 4 tables, with structure as below (Q1 as an example):
CREATE TABLE [dbo].[Q1 2016](
[ProjectWork Number] [nvarchar](255) NULL,
[ProjectWork Name] [nvarchar](255) NULL,
[WBS07 Lvl 1] [nvarchar](255) NULL,
[WBS08 Lvl 2] [nvarchar](255) NULL,
[Resource Name] [nvarchar](255) NULL,
[Employee Number] [nvarchar](255) NULL,
[Resource Type] [nvarchar](255) NULL,
[Location] [nvarchar](255) NULL,
[Resource Role] [nvarchar](255) NULL,
[Shore Type] [nvarchar](255) NULL,
[Preferred Vendor] [nvarchar](255) NULL,
[Nov 01, 2015] [nvarchar](255) NULL,
[Nov 08, 2015] [nvarchar](255) NULL,
[Nov 15, 2015] [float] NULL,
[Nov 22, 2015] [nvarchar](255) NULL,
[Nov 29, 2015] [float] NULL,
[Dec 06, 2015] [float] NULL,
[Dec 13, 2015] [nvarchar](255) NULL,
[Dec 20, 2015] [float] NULL,
[Dec 27, 2015] [float] NULL,
[Jan 03, 2016] [float] NULL,
[Jan 10, 2016] [float] NULL,
[Jan 17, 2016] [nvarchar](255) NULL,
[Jan 24, 2016] [nvarchar](255) NULL,
[Jan 31, 2016] [nvarchar](255) NULL
) ON [PRIMARY]
GO
And I have created the final table with the structure I described above:
CREATE TABLE [dbo].[Fiscal2016](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProjectWork Number] [nvarchar](255) NULL,
[ProjectWork Name] [nvarchar](255) NULL,
[WBS07 Lvl 1] [nvarchar](255) NULL,
[WBS08 Lvl 2] [nvarchar](255) NULL,
[Resource Name] [nvarchar](255) NULL,
[Employee Number] [nvarchar](255) NULL,
[Resource Type] [nvarchar](255) NULL,
[Location] [nvarchar](255) NULL,
[Resource Role] [nvarchar](255) NULL,
[Shore Type] [nvarchar](255) NULL,
[Preferred Vendor] [nvarchar](255) NULL,
[Quarter] [int] NULL,
[WeekNo] [nvarchar](11) NULL,
[WeekValue] [float] NULL
) ON [PRIMARY]
GO
Now how do I insert every record from Q1-Q4 into the final table?
I think I've converted this question to how to transpose columns?
November 17, 2016 at 3:04 pm
OK, this is what I am going to do: I will create a table with:
CREATE TABLE [dbo].[WeekTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[QuaterNo] [varchar](7) NOT NULL,
[WeekNo] [varchar](11) NOT NULL
) ON [PRIMARY]
And I am to populate it with value of:
'Q1 2016',[Nov 01, 2015]
'Q1 2016',[Nov 08, 2015]
'Q1 2016',[Nov 15, 2015]
'Q1 2016',[Nov 22, 2015]
'Q1 2016',[Nov 29, 2015]
'Q1 2016',[Dec 06, 2015]
'Q1 2016',[Dec 13, 2015]
'Q1 2016',[Dec 20, 2015]
'Q1 2016',[Dec 27, 2015]
'Q1 2016',[Jan 03, 2016]
'Q1 2016',[Jan 10, 2016]
'Q1 2016',[Jan 17, 2016]
'Q1 2016',[Jan 24, 2016]
'Q1 2016',[Jan 31, 2016]
and the rest
I am to use a cursor to create a dynamic sql to execute the insert to the final table.
What a tedious procedure!!!
It would be greatly appreciated if you can share your better idea.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply