May 19, 2017 at 2:11 am
Hi,
I am trying to summarize some rows and do a revese output
Here is my table data
CREATE TABLE mycompanytable(
Companyname NVARCHAR(50) NOT NULL
,item_name NVARCHAR(50) NOT NULL
,Stock_last_month VARCHAR(5) NOT NULL
,Bought_in_current_time_frame_In_Kilos VARCHAR(6) NOT NULL
,Sold_in_current_time_frame_in_KILOS VARCHAR(5) NOT NULL
,Stock_this_month VARCHAR(6) NOT NULL
,Month INTEGER NOT NULL
,Year INTEGER NOT NULL
,Dataname_key NVARCHAR(5) NOT NULL
,Parent_Group_ID INTEGER NOT NULL
,Group_ID INTEGER NOT NULL
);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 1','6,00','1,20','5,70','3,00',4,2017,N'Key 1',1,22);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 1','0,00','0,80','1,20','0,00',4,2017,N'Key 1',1,21);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 1','6,00','1,20','54,60','3,00',4,2017,N'Key 1',1,22);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 1','0,00','0,80','20,40','0,00',4,2017,N'Key 1',1,21);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 2','0,00','0,50','22,75','0,00',4,2017,N'Key 1',2,40);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 2','0,00','0,50','1,75','0,00',4,2017,N'Key 1',2,40);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 3','0,00','0,25','19,25','0,00',4,2017,N'Key 1',8,41);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'Item 3','0,00','0,25','1,00','0,00',4,2017,N'Key 1',8,41);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'item 4','0,00','52,46','50,16','0,00',4,2017,N'Key 1',9,42);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 1',N'item 4','0,00','52,46','10,56','0,00',4,2017,N'Key 1',9,42);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 1','55,00','3,00','5,70','357,00',4,2017,N'key 2',1,22);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 1','23,00','1,20','4,00','301,00',4,2017,N'key 2',1,21);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 1','55,00','3,00','48,00','357,00',4,2017,N'key 2',1,22);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 1','23,00','1,20','21,20','301,00',4,2017,N'key 2',1,21);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 2','6,00','0,50','19,75','4,00',4,2017,N'key 2',2,40);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 2','6,00','0,50','4,00','4,00',4,2017,N'key 2',2,40);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 3','0,00','1,25','8,75','0,00',4,2017,N'key 2',8,41);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'Item 3','0,00','1,25','2,25','0,00',4,2017,N'key 2',8,41);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'item 4','0,00','41,90','93,06','0,00',4,2017,N'key 2',9,42);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Dataname_key,Parent_Group_ID,Group_ID) VALUES (N'Company 2',N'item 4','0,00','41,90','5,94','0,00',4,2017,N'key 2',9,42);
This is the current output
Table output 1
+-------------+-----------+------------------+---------------------------------------+-------------------------------------+------------------+-------+------+--------------+-----------------+----------+
| Companyname | item name | Stock last month | Bought in current time frame In Kilos | Sold in current time frame in KILOS | Stock this month | Month | Year | Dataname key | Parent Group ID | Group ID |
+-------------+-----------+------------------+---------------------------------------+-------------------------------------+------------------+-------+------+--------------+-----------------+----------+
| Company 1 | Item 1 | 6,00 | 1,20 | 5,70 | 3,00 | 4 | 2017 | Key 1 | 1 | 22 |
| Company 1 | Item 1 | 0,00 | 0,80 | 1,20 | 0,00 | 4 | 2017 | Key 1 | 1 | 21 |
| Company 1 | Item 1 | 6,00 | 1,20 | 54,60 | 3,00 | 4 | 2017 | Key 1 | 1 | 22 |
| Company 1 | Item 1 | 0,00 | 0,80 | 20,40 | 0,00 | 4 | 2017 | Key 1 | 1 | 21 |
| Company 1 | Item 2 | 0,00 | 0,50 | 22,75 | 0,00 | 4 | 2017 | Key 1 | 2 | 40 |
| Company 1 | Item 2 | 0,00 | 0,50 | 1,75 | 0,00 | 4 | 2017 | Key 1 | 2 | 40 |
| Company 1 | Item 3 | 0,00 | 0,25 | 19,25 | 0,00 | 4 | 2017 | Key 1 | 8 | 41 |
| Company 1 | Item 3 | 0,00 | 0,25 | 1,00 | 0,00 | 4 | 2017 | Key 1 | 8 | 41 |
| Company 1 | item 4 | 0,00 | 52,46 | 50,16 | 0,00 | 4 | 2017 | Key 1 | 9 | 42 |
| Company 1 | item 4 | 0,00 | 52,46 | 10,56 | 0,00 | 4 | 2017 | Key 1 | 9 | 42 |
| Company 2 | Item 1 | 55,00 | 3,00 | 5,70 | 357,00| 4 | 2017 | key 2 | 1 | 22 |
| Company 2 | Item 1 | 23,00 | 1,20 | 4,00 | 301,00| 4 | 2017 | key 2 | 1 | 21 |
| Company 2 | Item 1 | 55,00 | 3,00 | 48,00 | 357,00| 4 | 2017 | key 2 | 1 | 22 |
| Company 2 | Item 1 | 23,00 | 1,20 | 21,20 | 301,00| 4 | 2017 | key 2 | 1 | 21 |
| Company 2 | Item 2 | 6,00 | 0,50 | 19,75 | 4,00 | 4 | 2017 | key 2 | 2 | 40 |
| Company 2 | Item 2 | 6,00 | 0,50 | 4,00 | 4,00 | 4 | 2017 | key 2 | 2 | 40 |
| Company 2 | Item 3 | 0,00 | 1,25 | 8,75 | 0,00 | 4 | 2017 | key 2 | 8 | 41 |
| Company 2 | Item 3 | 0,00 | 1,25 | 2,25 | 0,00 | 4 | 2017 | key 2 | 8 | 41 |
| Company 2 | item 4 | 0,00 | 41,90 | 93,06 | 0,00 | 4 | 2017 | key 2 | 9 | 42 |
| Company 2 | item 4 | 0,00 | 41,90 | 5,94 | 0,00 | 4 | 2017 | key 2 | 9 | 42 |
+-------------+-----------+------------------+---------------------------------------+-------------------------------------+------------------+-------+------+--------------+-----------------+----------+
If i can get this output instead, it should be easier to do calculations and to get the final output shown below.
Table output 2
+-------------+-----------+------------------+---------------------------------------+-------------------------------------+------------------+-------+------+--------------+-----------------+----------+
| Companyname | item name | Stock last month | Bought in current time frame In Kilos | Sold in current time frame in KILOS | Stock this month | Month | Year | Dataname key | Parent Group ID | Group ID |
+-------------+-----------+------------------+---------------------------------------+-------------------------------------+------------------+-------+------+--------------+-----------------+----------+
| Company 1 | Item 1 | 6,00 | 2,00 | 81,90 | 3,00 | 4 | 2017 | Key 1 | 1 | 22 |
| Company 1 | Item 2 | 0,00 | 0,50 | 24,50 | 0,00 | 4 | 2017 | Key 1 | 2 | 40 |
| Company 1 | Item 3 | 0,00 | 0,25 | 20,25 | 0,00 | 4 | 2017 | Key 1 | 8 | 41 |
| Company 1 | item 4 | 0,00 | 52,46 | 60,72 | 0,00 | 4 | 2017 | Key 1 | 9 | 42 |
| Company 2 | Item 1 | 55,00 | 4,20 | 78,90 | 658,00| 4 | 2017 | key 2 | 1 | 22 |
| Company 2 | Item 2 | 6,00 | 0,50 | 23,75 | 4,00 | 4 | 2017 | key 2 | 2 | 40 |
| Company 2 | Item 3 | 0,00 | 1,25 | 11,00 | 0,00 | 4 | 2017 | key 2 | 8 | 41 |
| Company 2 | item 4 | 0,00 | 41,90 | 99,00 | 0,00 | 4 | 2017 | key 2 | 9 | 42 |
+-------------+-----------+------------------+---------------------------------------+-------------------------------------+------------------+-------+------+--------------+-----------------+----------+
Explanation:
Company 1 Item 1
'Stock last month': Should be a sum of row 1+2, where 'Group ID' 21,22 hasnt been summed up before. So the same values in row 3 and 4 are duplicates and should be ignored.
The same summation goes for: 'Bought in current time frame In Kilos' and 'Stock this month'.
'Sold in current time frame in KILOS' shall be a sum of the values for all 4 rows with same 'Parent Group ID' and same 'Dataname key'.
Table output 3
After output in Table output 2 is achived, i would like to do a calculation, like this for the procentage fields below.
CONVERT(decimal(18,1),
(
(SUM(CONVERT(decimal(18,1),Replace([Sold_in_current_time_frame_in_KILOS],',','.'))) + SUM(CONVERT(decimal(18,1),Replace([Stock_this_month],',','.'))))
-
(SUM(CONVERT(decimal(18,1),Replace([Stock_last_month],',','.')))+(SUM(CONVERT(decimal(18,1),Replace([Bought_in_current_time_frame_In_Kilos],',','.'))))
)
/
(SUM(CONVERT(decimal(18,1),Replace([Sold_in_current_time_frame_in_KILOS],',','.'))))
)
)
as procentage
+-----------+--------+--------+--------+--------+
| Company | Item 1 | Item 2 | Item 3 | Item 4 |
+-----------+--------+--------+--------+--------+
| Company 1 | 0,93% | 0,97% | 0,98% | 0,13% |
| Company 2 | 8,56% | 0,89% | 0,87% | 0,58% |
+-----------+--------+--------+--------+--------+
Can anyone help on this?
May 19, 2017 at 3:11 am
I don't really understand your requirement, but in trying to get to grips with it, I noticed that you have a denormalised design and the wrong data types. Parent_Group_ID should be stored in a separate lookup table and not repeated in every row of this table. The same goes for Dataname_key, since it appears to correlate to Companyname. You'd be best off storing Month and Year in a single date column. Worst of all, though, we need to sum columns such as Stock_last_month, but they are varchar and therefore not summable.
John
May 19, 2017 at 3:37 am
Hi,
Thank you for responding
The original data consists of 6 tables.
The data from these are packed into the test tabel above., for easier understanding (i hope).
Dataname_key are the actual key to each company.
Date and year are split up, due to the way the application proivides these when filtering on dates.
Fields like Stock_last_month can be converted before any calculations, like CONVERT(decimal(18,2), Replace(Stock_last_month,',','.'))
May 19, 2017 at 3:56 am
peter larsen-490879 - Friday, May 19, 2017 3:37 AMHi,Thank you for responding
The original data consists of 6 tables.
The data from these are packed into the test tabel above., for easier understanding (i hope).
Dataname_key are the actual key to each company.
Date and year are split up, due to the way the application proivides these when filtering on dates.
OK, so in real life you have six tables, but you've just joined them into one view for simplicity - is that right? In that case, why not get rid of columns that aren't relevant to this exercise, such as Parent_Group_ID and companyname, to make it easier to work with?
Fields like Stock_last_month can be converted before any calculations, like
CONVERT(decimal(18,2), Replace(Stock_last_month,',','.'))
Yes, I know. But you're expecting whoever helps you with this code to write that conversion every time. Worse, it'll be a performance nightmare. Conversions and string manipulations are inefficient, and you won't be able to take advantage of any indexes on the columns.
John
May 19, 2017 at 4:25 am
HI,
Parent_Group_ID will be used group fields from 'Sold in current time frame in KILOS' and companyname are the name to display in the final output.
I tried to change the datatype to decimal for '
| Stock last month | Bought in current time frame In Kilos | Sold in current time frame in KILOS | Stock this month |
', when generating the insert statements using this tool: http://www.convertcsv.com/csv-to-sql.htm, but it keeps saying this:
There are fewer columns in the INSERT statement than values specified in the VALUES clause.
It only worked when i chose nvarchar.
Hope this is ok.
May 19, 2017 at 4:46 am
peter larsen-490879 - Friday, May 19, 2017 4:25 AMHI,Parent_Group_ID will be used group fields from 'Sold in current time frame in KILOS' and companyname are the name to display in the final output.
I tried to change the datatype to decimal for '
Stock last month Bought in current time frame In Kilos Sold in current time frame in KILOS Stock this month ', when generating the insert statements using this tool: http://www.convertcsv.com/csv-to-sql.htm, but it keeps saying this:
There are fewer columns in the INSERT statement than values specified in the VALUES clause.
It only worked when i chose nvarchar.
Hope this is ok.
in your original tables, what is the SQL datatype for example "Stock_last_month"....is it really varchar?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 19, 2017 at 4:46 am
peter larsen-490879 - Friday, May 19, 2017 4:25 AMParent_Group_ID will be used group fields from 'Sold in current time frame in KILOS'
So you're saying that if you had two groups both with the same Parent_Group_ID, you'd want to group by the parent and not by the group itself? If so, get rid of Group_ID from the table. If not, get rid of Parent_Group_ID.
companyname are the name to display in the final output.
You have a one-to-one relationship between that and Key_ID. You don't need to include both, and it will complicate your code. GROUP BY (Key_ID) is easier to read, write and understand than GROUP BY (companyname, Key_ID). Since this is a simplification of your problem, you can always put the other stuff back in once we've helped you write something that works.
I tried to change the datatype to decimal for '
Stock last month Bought in current time frame In Kilos Sold in current time frame in KILOS Stock this month ', when generating the insert statements using this tool: http://www.convertcsv.com/csv-to-sql.htm
I'm guessing that's because you have "," as a delimiter and also as a decimal separator. Did you make it varchar in the first place to get round that issue?
John
May 19, 2017 at 7:30 am
Hi all,
I not sure what to do now.
Do you want me to create a different set of insert statements without Group_ID and convert decimal values to decimal datatype?
May 19, 2017 at 7:37 am
Yes please - and preferably also without one of Companyname and Dataname_key.
John
May 19, 2017 at 8:28 am
Hi,
Here is the changed Insert statements
CREATE TABLE mycompanytable(
Companyname NVARCHAR(50) NOT NULL
,item_name NVARCHAR(50) NOT NULL
,Stock_last_month NUMERIC(6,2) NOT NULL
,Bought_in_current_time_frame_In_Kilos NUMERIC(6,2) NOT NULL
,Sold_in_current_time_frame_in_KILOS NUMERIC(6,2) NOT NULL
,Stock_this_month NUMERIC(6,2) NOT NULL
,Month INTEGER NOT NULL
,Year INTEGER NOT NULL
,Parent_Group_ID INTEGER NOT NULL
);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 1',6.00,1.20,5.70,3.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 1',0.00,0.80,1.20,0.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 1',6.00,1.20,54.60,3.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 1',0.00,0.80,20.40,0.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 2',0.00,0.50,22.75,0.00,4,2017,2);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 2',0.00,0.50,1.75,0.00,4,2017,2);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 3',0.00,0.25,19.25,0.00,4,2017,8);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'Item 3',0.00,0.25,1.00,0.00,4,2017,8);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'item 4',0.00,52.46,50.16,0.00,4,2017,9);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 1',N'item 4',0.00,52.46,10.56,0.00,4,2017,9);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 1',55.00,3.00,5.70,357.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 1',23.00,1.20,4.00,301.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 1',55.00,3.00,48.00,357.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 1',23.00,1.20,21.20,301.00,4,2017,1);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 2',6.00,0.50,19.75,4.00,4,2017,2);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 2',6.00,0.50,4.00,4.00,4,2017,2);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 3',0.00,1.25,8.75,0.00,4,2017,8);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'Item 3',0.00,1.25,2.25,0.00,4,2017,8);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'item 4',0.00,41.90,93.06,0.00,4,2017,9);
INSERT INTO mycompanytable(Companyname,item_name,Stock_last_month,Bought_in_current_time_frame_In_Kilos,Sold_in_current_time_frame_in_KILOS,Stock_this_month,Month,Year,Parent_Group_ID) VALUES (N'Company 2',N'item 4',0.00,41.90,5.94,0.00,4,2017,9);
May 19, 2017 at 9:01 am
This almost works, but please will you check your logic for Company 2, Item 1 - it should be 55 + 23 = 78, shouldn't it?
SELECT
Companyname
, item_name
, Parent_Group_ID
, SUM(DISTINCT Stock_last_month) AS StockLastMonth
, SUM(DISTINCT Bought_in_current_time_frame_In_Kilos) AS BoughtinCurrentTimeFrameinKilos
, SUM(DISTINCT Sold_in_current_time_frame_in_KILOS) AS SoldinCurrentTimeFrameinKilos
, SUM(DISTINCT Stock_this_month) AS StockThisMonth
FROM mycompanytable
GROUP BY
Companyname
, item_name
, Parent_Group_ID
John
May 23, 2017 at 1:20 am
Hi,
I finally succed in getting the correct data which needs to be reversed.
My data
CREATE TABLE mycompanytable2(
CompanyName NVARCHAR(50) NOT NULL PRIMARY KEY
,ItemName NVARCHAR(50) NOT NULL
,Percent NUMERIC(7,5) NOT NULL
);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 1',0.93895);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 2',0.97959);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 3',0.98765);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 1',N'Item 4',0.00000);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 1',0.98251);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 2',0.98496);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 3',0.96667);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 2',N'Item 4',0.00000);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 1',0.99181);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 2',0.98000);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 3',0.99160);
INSERT INTO mycompanytable2(CompanyName,ItemName,Percent) VALUES (N'Company 3',N'Item 4',0.00000);
Cuurent ouput
+-------------+----------+---------+
| CompanyName | ItemName | Percent |
+-------------+----------+---------+
| Company 1 | Item 1 | 0.93895 |
| Company 1 | Item 2 | 0.97959 |
| Company 1 | Item 3 | 0.98765 |
| Company 1 | Item 4 | 0.00000 |
| Company 2 | Item 1 | 0.98251 |
| Company 2 | Item 2 | 0.98496 |
| Company 2 | Item 3 | 0.96667 |
| Company 2 | Item 4 | 0.00000 |
| Company 3 | Item 1 | 0.99181 |
| Company 3 | Item 2 | 0.98000 |
| Company 3 | Item 3 | 0.99160 |
| Company 3 | Item 4 | 0.00000 |
+-------------+----------+---------+
needs to be like this:
+-----------+--------+--------+--------+--------+
| Company | Item 1 | Item 2 | Item 3 | Item 4 |
+-----------+--------+--------+--------+--------+
| Company 1 | 0,93% | 0,97% | 0,98% | 0,13% |
| Company 2 | 8,56% | 0,89% | 0,87% | 0,58% |
+-----------+--------+--------+--------+--------+
There can be more Companies and more items.
Can you help on this last part?
The comversion of the percent is not needed.
May 23, 2017 at 1:49 am
Please read about crosstabs and pivots - that will help you to get the results you're looking for from your sample data. If you have an unspecified number of items, then you'll need a dynamic pivot. That's a more advanced technique for you to read about.
By the way, your CREATE TABLE statement doesn't work because Percent isn't a valid column name. And your INSERT statements violate the primary key constraint. Why does Company 3 not appear in your expected results?
John
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply