May 10, 2011 at 10:11 am
Hello I have two tables that I need to compile the data into one new table. The first table is called "Table A" and the second one is called "Table B". Here are the create statements for both:
CREATE TABLE [dbo].[Table A](
[Dist] [varchar](20) NULL,
[Date] [datetime] NULL,
[Bucket] [varchar](50) NULL,
[Volume] [float] NULL,
[Minutes] [float] NULL,
[EQE] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table B](
[Compile_Date] [datetime] NOT NULL,
[Date_Compile_Type] [nvarchar](10) NOT NULL,
[Service_Type_en] [nvarchar](50) NOT NULL,
[WorkCenter_en] [nvarchar](15) NOT NULL,
[Region_en] [nvarchar](10) NOT NULL,
[Status_en] [nvarchar](10) NOT NULL,
[Volume] [int] NULL,
[TaskTime] [int] NULL,
[LastUpdate] [datetime] NOT NULL
) ON [PRIMARY]
Attached are sample data for both tables.
The new table will have the same columns as Table A, the column named "Bucket" will contain all the results from Service_Type_en column from Table B. Since the Bucket "wipro" is shown differently in both tables, I need to compile it into one column while including the "types of wipro" from Table B, which are shown in the Service_Type_en column, i.e. Data Wipro, Megaroute Wipro, Miscellaneous Wipro, etc.
What I also need to do is show the splits, i.e. the percentage of wipro buckets, that is, what % is Data, what % is Megaroute, etc.
I worked out the percentages by totalling up all the Wipros in Table B of a specific year i.e. 2009 or 2008, then dividing them into each Service_Type_en or Bucket, so that I had 10 % in Data, 40 % in Megaroute and the remaining 50% in Miscellaneous.
How do I go about scripting these percentages when I need to show these results in the new table? Do I include it in the Select statement or do I make a Case or Cast statement?
Please forgive me if this sounds complicated, I can provide more info if needed.
Thanks.
May 10, 2011 at 1:10 pm
Based on your sample data, what would be your expected result and what have you tried so far?
May 11, 2011 at 8:15 am
What I want to do to compile the data, is to import the results of Table A, up until a cutoff point of December 4th, 2008. This is where the newer results from Table B start. I need to do two inserts, in order to transfer the data from both tables. My query is something like this:
---For Table A Data Transfer----
Insert Into Table C
Select Dist,Date,
CASE
When Bucket = 'Data' Then 'Data'When Bucket = 'LPAG' Then 'LPAG'
When Bucket = 'Megaroute' Then 'Megaroute'When Bucket = 'Megastream' Then 'Megastream'
When Bucket = 'WIPRO' Then 'Wipro'When Bucket = 'VoiceVoix' Then 'Voice'
When Bucket = 'Other' Then 'Miscellaneous'
When Bucket IN ('LocalChange','LocalNew', 'LocalOut') Then 'LocalComp'
End as Bucket,
Volume, Minutes, EQE
From Table A
Where convert(varchar,Date,120) between convert(varchar,Getdate()-1950,120)
and convert(varchar,Getdate()-880,120)
Order By date
----For Table B Data Transfer-------
Select (Region_en + 'OMD') as Dist,Compile_Date as Date , Service_Type_en as Bucket,
convert(float,Volume) as Volume,convert(float,TaskTime) as Minutes, (convert(float,TaskTime)/390) as EQE
From Table B
Order by Compile_Date
When I did a calculation from Table B for 2009, I found that there were 928 results for 'Wipro' under the WorkCenter_en column.
I then broke it down to 3 types of Wipro:
377 were Data Wipro or 40.63 %
404 were Megaroute Wipro or 43.53 %
147 were Miscelllaneous Wipro or 15.84 %
I want the results to reflect these percentages when I do the Insert Into of Table A into the new table (Table C)
As you can see, these percentages are already shown in Table B, because of the extra column (Service_Type_en) which explains what type of service the Wipro Workcenter is.
I hope that this explains this better.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply