I need to "group" two rows of data, but cannot seem to figure it out

Question

Post reply

I need to "group" two rows of data, but cannot seem to figure it out

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

randyetheridge SSCommitted Points: 1703 More actions · Answer 1

CREATE TABLE blacklineCIM.dbo.CIM_FACT (

[Year] nvarchar (4) not null ,

[Period] nvarchar (10) not null,

[Entity] nvarchar (10) not null,

[Account] nvarchar (10) not null,

[currency] nvarchar (10) not null,

[Amt] float not null

);

I am not sure how to do it, but in my tables, Year, period, entity, account and currency are ALL set to primary key status. could not figure out how to do that in the create function. I do not believe for your requirements that is an issue, but I did want to let you know.

randyetheridge SSCommitted Points: 1703 More actions · Answer 2

Insert INTO dbo.cim_Fact

(2018,January,ENT_1000,1588,USD Total,-888823191.97),

(2018,January,ENT_1000,1591,USD Total,0.01),

(2018,January,ENT_1000,1599,USD Total,888823191.96),

(2018,January,ENT_1000,1623,USD Total,0),

(2018,January,ENT_1000,783,USD Total,0),

(2018,January,ENT_1005,1554,USD Total,36299019.4),

(2018,January,ENT_1005,1581,USD Total,257505.26),

(2018,January,ENT_1005,1584,USD Total,165502103.58),

(2018,January,ENT_1005,1585,USD Total,17755523.43),

(2018,January,ENT_1005,1586,USD Total,-17755523.43),

(2018,January,ENT_1005,1593,USD Total,-12548880.08),

(2018,January,ENT_1005,1599,USD Total,-11639198.67),

(2018,January,ENT_1005,1622,USD Total,-1000000),

(2018,January,ENT_1005,178,USD Total,38577829),

(2018,January,ENT_1005,783,USD Total,138292720.49),

(2018,January,ENT_1007,1034,USD Total,-0.23),

(2018,January,ENT_1007,1035,USD Total,-1094.09),

(2018,January,ENT_1007,1529,USD Total,40153587.27),

(2018,January,ENT_1007,1581,USD Total,160000),

(2018,January,ENT_1007,1589,USD Total,71050691.48),

(2018,January,ENT_1007,1590,USD Total,27745583.46),

(2018,January,ENT_1007,1591,USD Total,-130446661.27),

(2018,January,ENT_1007,1593,USD Total,284584160),

(2018,January,ENT_1007,1599,USD Total,18780997.06),

(2018,January,ENT_1007,1612,USD Total,-3767296.62),

(2018,January,ENT_1007,1622,USD Total,-13579584.46),

(2018,January,ENT_1007,1623,USD Total,0),

(2018,January,ENT_1007,2484,USD Total,540839803.43),

(2018,January,ENT_1007,2486,USD Total,242281.02),

(2018,January,ENT_1007,2487,USD Total,-458943.48),

(2018,January,ENT_1007,2488,USD Total,-184788.56),

(2018,January,ENT_1007,2491,USD Total,-0.02),

(2018,January,ENT_1007,471,USD Total,28004721.55),

(2018,January,ENT_1007,481,USD Total,30986746.55),

(2018,January,ENT_1007,783,USD Total,838100759.99),

(2018,January,ENT_1010,1004,USD Total,23498452.79),

(2018,January,ENT_1010,1006,USD Total,1205416.25),

(2018,January,ENT_1010,1010,USD Total,2599846.02),

(2018,January,ENT_1010,1019,USD Total,2987810.05),

(2018,January,ENT_1010,1020,USD Total,1810637.62),

(2018,January,ENT_1010,1022,USD Total,-2388800.07),

(2018,January,ENT_1010,1026,USD Total,4661821.97),

(2018,January,ENT_1010,1027,USD Total,761786.12),

(2018,January,ENT_1010,1031,USD Total,99551),

(2018,January,ENT_1010,1032,USD Total,31849.86),

(2018,January,ENT_1010,1033,USD Total,-135207.65),

(2018,January,ENT_1010,1037,USD Total,3870804.55),

(2018,January,ENT_1010,1038,USD Total,257088.2),

(2018,January,ENT_1010,1039,USD Total,6587150),

(2018,January,ENT_1010,1040,USD Total,1066222.19),

(2018,January,ENT_1010,1043,USD Total,937463.63),

(2018,January,ENT_1010,1045,USD Total,103159.57),

(2018,January,ENT_1010,1046,USD Total,-9731),

(2018,January,ENT_1010,1050,USD Total,902377.91),

(2018,January,ENT_1010,1051,USD Total,41595.94),

(2018,January,ENT_1010,1053,USD Total,416456.53),

(2018,January,ENT_1010,1054,USD Total,-1634499.12),

(2018,January,ENT_1010,1055,USD Total,340638.54),

(2018,January,ENT_1010,1056,USD Total,-13786.37),

(2018,January,ENT_1010,1060,USD Total,2589599.83),

(2018,January,ENT_1010,1066,USD Total,42426.51),

(2018,January,ENT_1010,1067,USD Total,75067.2),

(2018,January,ENT_1012,1039,USD Total,1992500),

(2018,January,ENT_1012,1071,USD Total,45779.24),

(2018,January,ENT_1012,1087,USD Total,2604656.15),

(2018,January,ENT_1012,1109,USD Total,279016.86),

(2018,January,ENT_1012,1115,USD Total,-192704.6),

(2018,January,ENT_1012,1125,USD Total,1211.71),

(2018,January,ENT_1012,1127,USD Total,386.03),

(2018,January,ENT_1012,1136,USD Total,997.5),

(2018,January,ENT_1012,1137,USD Total,0.03),

(2018,January,ENT_1012,1140,USD Total,29.18),

(2018,January,ENT_1014,1588,USD Total,13121074.53),

(2018,January,ENT_1014,1598,USD Total,59580.75),

(2018,January,ENT_1014,1599,USD Total,2111557.61),

(2018,January,ENT_1014,2201,USD Total,-59580.75),

(2018,January,ENT_1014,471,USD Total,8867474.52),

(2018,January,ENT_1014,777,USD Total,982840.37),

(2018,January,ENT_1014,778,USD Total,5441898),

(2018,January,ENT_1016,1588,USD Total,83366671.71),

(2018,January,ENT_1016,1589,USD Total,35525345.74),

(2018,January,ENT_1016,1599,USD Total,10070933.69),

(2018,January,ENT_1016,1612,USD Total,2962651.71),

(2018,January,ENT_1016,1623,USD Total,0),

(2018,January,ENT_1016,2484,USD Total,-51599191.31),

(2018,January,ENT_1016,2487,USD Total,-4943890.48),

(2018,January,ENT_1016,481,USD Total,9375309.84),

(2018,January,ENT_1016,783,USD Total,84757830.9),

(2018,January,ENT_1018,1000,CAD Total,605602.3),

(2018,January,ENT_1018,1000,USD Total,492657.47),

(2018,January,ENT_1018,1002,CAD Total,2120.92),

(2018,January,ENT_1018,1002,USD Total,1725.37),

(2018,January,ENT_1018,1004,CAD Total,1715724.61),

(2018,January,ENT_1018,1004,USD Total,1395741.97),

(2018,January,ENT_1019,1599,USD Total,0),

(2018,January,ENT_1019,1623,USD Total,0),

(2018,January,ENT_1024,1588,USD Total,77306.96),

(2018,January,ENT_1024,1598,USD Total,-444.29),

(2018,January,ENT_1024,1599,USD Total,3094786.66),

(2018,January,ENT_1024,1612,USD Total,348033.55),

(2018,January,ENT_1024,1623,USD Total,0),

(2018,January,ENT_1024,2041,USD Total,444.29),

(2018,January,ENT_1024,2441,USD Total,-31044461.11),

(2018,January,ENT_1024,2484,USD Total,416492.27),

(2018,January,ENT_1024,471,USD Total,2465066.45),

(2018,January,ENT_1024,481,USD Total,34067183.1),

(2018,January,ENT_1024,783,USD Total,4493830.69),

(2018,January,ENT_1028,1591,USD Total,1234.9),

(2018,January,ENT_1028,1599,USD Total,-476400.68),

(2018,January,ENT_1028,1623,USD Total,0),

(2018,January,ENT_1028,481,USD Total,475165.78),

(2018,January,ENT_1500,1004,USD Total,2159023.38),

(2018,January,ENT_1500,1006,USD Total,42525),

(2018,January,ENT_1500,1040,USD Total,327613.42),

(2018,January,ENT_1500,1076,USD Total,59082.48),

(2018,January,ENT_1500,1080,USD Total,810374.8),

(2018,January,ENT_1500,1087,USD Total,7864006),

(2018,January,ENT_1500,1108,USD Total,3247503.67),

(2018,January,ENT_1500,1120,USD Total,-73613.78),

(2018,January,ENT_1500,1167,USD Total,36826.63),

(2018,January,ENT_1500,1228,USD Total,8961.47),

(2018,January,ENT_1500,14,USD Total,2507501.89),

(2018,January,ENT_1500,1551,USD Total,84444.93),

(2018,January,ENT_1510,1579,USD Total,22892),

(2018,January,ENT_1510,1598,USD Total,177722.06),

(2018,January,ENT_1510,1599,USD Total,7583967.07),

(2018,January,ENT_1510,1603,USD Total,-5805000),

(2018,January,ENT_1510,1668,USD Total,217070.02),

(2018,January,ENT_1510,1822,USD Total,38943.46),

(2018,January,ENT_1510,2106,USD Total,404.5),

(2018,January,ENT_1510,218,USD Total,404502.61),

(2018,January,ENT_1510,223,USD Total,-404.5),

(2018,January,ENT_1510,2484,USD Total,49211.15),

(2018,January,ENT_1510,28,USD Total,1902495.08),

(2018,January,ENT_1510,35,USD Total,170),

(2018,January,ENT_1510,793,USD Total,153950.87),

(2018,January,ENT_1510,822,USD Total,-44829.36),

(2018,January,ENT_1510,994,USD Total,168849.4),

(2018,January,ENT_3310,1006,MXN Total,35660.11),

(2018,January,ENT_3310,1006,USD Total,1914.95),

(2018,January,ENT_3310,106,MXN Total,1109968.12),

(2018,January,ENT_3310,106,USD Total,59605.29),

(2018,January,ENT_3310,109,MXN Total,1964637.92),

(2018,January,ENT_3310,109,USD Total,105501.06),

(2018,January,ENT_3310,1442,MXN Total,-2574190.72),

(2018,January,ENT_3310,1442,USD Total,-138234.04),

(2018,January,ENT_3310,1588,MXN Total,-23152803.52),

(2018,January,ENT_3310,1588,USD Total,-1785081.17),

(2018,January,ENT_3310,1599,MXN Total,-20083580.99),

(2018,January,ENT_3310,1599,USD Total,-1217204.4),

(2018,January,ENT_5010,1006,EUR Total,11770),

(2018,January,ENT_5010,1006,USD Total,14661.89),

(2018,January,ENT_5010,1009,EUR Total,42155.18),

(2018,January,ENT_5010,1009,USD Total,52512.71),

(2018,January,ENT_5010,1040,EUR Total,75.73),

(2018,January,ENT_5010,1040,USD Total,94.34),

(2018,January,ENT_5010,1043,EUR Total,3083.49),

(2018,January,ENT_5010,1043,USD Total,3841.1),

(2018,January,ENT_5010,1053,EUR Total,600),

(2018,January,ENT_5010,1053,USD Total,747.42),

(2018,January,ENT_5010,1074,EUR Total,270595.89),

(2018,January,ENT_5010,1074,USD Total,337081.3),

(2018,January,ENT_5010,1099,EUR Total,20000),

(2018,January,ENT_5010,1099,USD Total,24914),

(2018,January,ENT_5310,1009,EUR Total,2058.96),

(2018,January,ENT_5310,1009,USD Total,2564.85),

(2018,January,ENT_5310,1040,EUR Total,183.83),

(2018,January,ENT_5310,1040,USD Total,229),

(2018,January,ENT_5310,1043,EUR Total,578.19),

(2018,January,ENT_5310,1043,USD Total,720.25),

(2018,January,ENT_5310,1053,EUR Total,780),

(2018,January,ENT_5310,1053,USD Total,971.65),

(2018,January,ENT_5310,1087,EUR Total,85745.87),

(2018,January,ENT_5310,1087,USD Total,106813.63),

(2018,January,ENT_5310,1100,EUR Total,21995),

(2018,January,ENT_5310,1100,USD Total,27399.17),

(2018,January,ENT_5310,1115,EUR Total,229218.22),

(2018,January,ENT_5310,1115,USD Total,285537.14),

(2018,January,ENT_5312,1026,PLN Total,153740.19),

(2018,January,ENT_5312,1026,USD Total,46137.43),

(2018,January,ENT_5312,103,PLN Total,6884.58),

(2018,January,ENT_5312,103,USD Total,2066.06),

(2018,January,ENT_5312,1043,PLN Total,171724.43),

(2018,January,ENT_5312,1043,USD Total,51534.5),

(2018,January,ENT_5312,1115,PLN Total,-6749.96),

(2018,January,ENT_5312,1115,USD Total,-2025.66),

(2018,January,ENT_5312,1288,PLN Total,63393.64),

(2018,January,ENT_5312,1288,USD Total,19024.43),

(2018,January,ENT_5312,1550,PLN Total,2272976.96),

(2018,January,ENT_5312,1550,USD Total,682120.39),

(2018,January,ENT_7010,1100,SGD Total,15000),

(2018,January,ENT_7010,1100,USD Total,11434.5),

(2018,January,ENT_7010,1442,SGD Total,38167),

(2018,January,ENT_7010,1442,USD Total,29094.7),

(2018,January,ENT_7010,1588,SGD Total,-1406670.33),

(2018,January,ENT_7010,1588,USD Total,-1128149.62),

(2018,January,ENT_7010,1599,SGD Total,1347080.79),

(2018,January,ENT_7010,1599,USD Total,919160.18),

(2018,January,ENT_7010,214,SGD Total,-2263.05),

(2018,January,ENT_7010,214,USD Total,-1725.12),

(2018,January,ENT_7010,346,SGD Total,353.84),

(2018,January,ENT_7010,346,USD Total,269.73),

(2018,January,ENT_7010,481,SGD Total,4513.23),

(2018,January,ENT_7010,481,USD Total,3440.44),

(2018,January,ENT_7620,1004,RMB Total,31784.37),

(2018,January,ENT_7620,1004,USD Total,5018.75),

(2018,January,ENT_7620,1043,RMB Total,151250),

(2018,January,ENT_7620,1043,USD Total,23882.38),

(2018,January,ENT_7620,1080,RMB Total,50689.75),

(2018,January,ENT_7620,1080,USD Total,8003.91),

(2018,January,ENT_7620,1087,RMB Total,182521.22),

(2018,January,ENT_7620,1087,USD Total,28820.1),

(2018,January,ENT_7620,1096,RMB Total,3500),

(2018,January,ENT_7620,1096,USD Total,552.65),

(2018,January,ENT_7620,1099,RMB Total,154717),

(2018,January,ENT_7620,1099,USD Total,24429.81),

(2018,January,ENT_7620,1100,RMB Total,172200),

(2018,January,ENT_7620,1100,USD Total,27190.38),

(2018,January,ENT_7620,1228,RMB Total,7275301.21),

(2018,January,ENT_7620,1228,USD Total,1148770.06),

(2018,January,ENT_7620,1442,RMB Total,702445.35),

(2018,January,ENT_7620,1442,USD Total,110916.12),

(2018,January,ENT_7620,1588,RMB Total,-34617.83),

(2018,January,ENT_7620,1588,USD Total,-5625.38),

(2018,January,ENT_7620,1591,RMB Total,11938178.08),

(2018,January,ENT_7620,1591,USD Total,1788339.08),

(2018,January,ENT_7620,1599,RMB Total,11998828.79),

(2018,January,ENT_7620,1599,USD Total,1738398.13)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Closer, but you've missed out the VALUES keyword and haven't put string values in quotes. It's a good idea to test code before posting it on a forum, if you have the opportunity.

Incidentally, if you have any control over the structure of the database, I'd advise you to use decimal instead of float for sums of money.

John

randyetheridge SSCommitted Points: 1703 More actions · Answer 4

randyetheridge

SSCommitted

Points: 1703

April 17, 2018 at 9:16 am

#1987176

ok ' quote correct ?

randyetheridge SSCommitted Points: 1703 More actions · Answer 5

CREATE TABLE blacklineCIM.dbo.CIM_FACT (

[Year] nvarchar (4) not null ,

[Period] nvarchar (10) not null,

[Entity] nvarchar (10) not null,

[Account] nvarchar (10) not null,

[currency] nvarchar (10) not null,

[Amt] Decimal (18, 2) not null

);

thanks updated

below86 SSChampion Points: 11532 More actions · Answer 6

CREATE TABLE #CIM_FACT ( YearYYYY nvarchar (4) not null , Period nvarchar (10) not null, Entity nvarchar (10) not null, Account nvarchar (10) not null, currency nvarchar (10) not null, Amt Decimal (18, 2) not null

);

Insert INTO #cim_Fact VALUES ('2018','January','ENT_1000','1588','USD Total',-888823191.97), ('2018','January','ENT_1000','1591','USD Total',0.01), ('2018','January','ENT_1000','1599','USD Total',888823191.96), ('2018','January','ENT_1000','1623','USD Total',0), ('2018','January','ENT_1000','783','USD Total',0), ('2018','January','ENT_1005','1554','USD Total',36299019.4), ('2018','January','ENT_1005','1581','USD Total',257505.26), ('2018','January','ENT_1005','1584','USD Total',165502103.58), ('2018','January','ENT_1005','1585','USD Total',17755523.43), ('2018','January','ENT_1005','1586','USD Total',-17755523.43), ('2018','January','ENT_1005','1593','USD Total',-12548880.08), ('2018','January','ENT_1005','1599','USD Total',-11639198.67), ('2018','January','ENT_1005','1622','USD Total',-1000000), ('2018','January','ENT_1005','178','USD Total',38577829), ('2018','January','ENT_1005','783','USD Total',138292720.49), ('2018','January','ENT_1007','1034','USD Total',-0.23), ('2018','January','ENT_1007','1035','USD Total',-1094.09), ('2018','January','ENT_1007','1529','USD Total',40153587.27), ('2018','January','ENT_1007','1581','USD Total',160000), ('2018','January','ENT_1007','1589','USD Total',71050691.48), ('2018','January','ENT_1007','1590','USD Total',27745583.46), ('2018','January','ENT_1007','1591','USD Total',-130446661.27), ('2018','January','ENT_1007','1593','USD Total',284584160), ('2018','January','ENT_1007','1599','USD Total',18780997.06), ('2018','January','ENT_1007','1612','USD Total',-3767296.62), ('2018','January','ENT_1007','1622','USD Total',-13579584.46), ('2018','January','ENT_1007','1623','USD Total',0), ('2018','January','ENT_1007','2484','USD Total',540839803.43), ('2018','January','ENT_1007','2486','USD Total',242281.02), ('2018','January','ENT_1007','2487','USD Total',-458943.48), ('2018','January','ENT_1007','2488','USD Total',-184788.56), ('2018','January','ENT_1007','2491','USD Total',-0.02), ('2018','January','ENT_1007','471','USD Total',28004721.55), ('2018','January','ENT_1007','481','USD Total',30986746.55), ('2018','January','ENT_1007','783','USD Total',838100759.99), ('2018','January','ENT_1010','1004','USD Total',23498452.79), ('2018','January','ENT_1010','1006','USD Total',1205416.25), ('2018','January','ENT_1010','1010','USD Total',2599846.02), ('2018','January','ENT_1010','1019','USD Total',2987810.05), ('2018','January','ENT_1010','1020','USD Total',1810637.62), ('2018','January','ENT_1010','1022','USD Total',-2388800.07), ('2018','January','ENT_1010','1026','USD Total',4661821.97), ('2018','January','ENT_1010','1027','USD Total',761786.12), ('2018','January','ENT_1010','1031','USD Total',99551), ('2018','January','ENT_1010','1032','USD Total',31849.86), ('2018','January','ENT_1010','1033','USD Total',-135207.65), ('2018','January','ENT_1010','1037','USD Total',3870804.55), ('2018','January','ENT_1010','1038','USD Total',257088.2), ('2018','January','ENT_1010','1039','USD Total',6587150), ('2018','January','ENT_1010','1040','USD Total',1066222.19), ('2018','January','ENT_1010','1043','USD Total',937463.63), ('2018','January','ENT_1010','1045','USD Total',103159.57), ('2018','January','ENT_1010','1046','USD Total',-9731), ('2018','January','ENT_1010','1050','USD Total',902377.91), ('2018','January','ENT_1010','1051','USD Total',41595.94), ('2018','January','ENT_1010','1053','USD Total',416456.53), ('2018','January','ENT_1010','1054','USD Total',-1634499.12), ('2018','January','ENT_1010','1055','USD Total',340638.54), ('2018','January','ENT_1010','1056','USD Total',-13786.37), ('2018','January','ENT_1010','1060','USD Total',2589599.83), ('2018','January','ENT_1010','1066','USD Total',42426.51), ('2018','January','ENT_1010','1067','USD Total',75067.2), ('2018','January','ENT_1012','1039','USD Total',1992500), ('2018','January','ENT_1012','1071','USD Total',45779.24), ('2018','January','ENT_1012','1087','USD Total',2604656.15), ('2018','January','ENT_1012','1109','USD Total',279016.86), ('2018','January','ENT_1012','1115','USD Total',-192704.6), ('2018','January','ENT_1012','1125','USD Total',1211.71), ('2018','January','ENT_1012','1127','USD Total',386.03), ('2018','January','ENT_1012','1136','USD Total',997.5), ('2018','January','ENT_1012','1137','USD Total',0.03), ('2018','January','ENT_1012','1140','USD Total',29.18), ('2018','January','ENT_1014','1588','USD Total',13121074.53), ('2018','January','ENT_1014','1598','USD Total',59580.75), ('2018','January','ENT_1014','1599','USD Total',2111557.61), ('2018','January','ENT_1014','2201','USD Total',-59580.75), ('2018','January','ENT_1014','471','USD Total',8867474.52), ('2018','January','ENT_1014','777','USD Total',982840.37), ('2018','January','ENT_1014','778','USD Total',5441898), ('2018','January','ENT_1016','1588','USD Total',83366671.71), ('2018','January','ENT_1016','1589','USD Total',35525345.74), ('2018','January','ENT_1016','1599','USD Total',10070933.69), ('2018','January','ENT_1016','1612','USD Total',2962651.71), ('2018','January','ENT_1016','1623','USD Total',0), ('2018','January','ENT_1016','2484','USD Total',-51599191.31), ('2018','January','ENT_1016','2487','USD Total',-4943890.48), ('2018','January','ENT_1016','481','USD Total',9375309.84), ('2018','January','ENT_1016','783','USD Total',84757830.9), ('2018','January','ENT_1018','1000','CAD Total',605602.3), ('2018','January','ENT_1018','1000','USD Total',492657.47), ('2018','January','ENT_1018','1002','CAD Total',2120.92), ('2018','January','ENT_1018','1002','USD Total',1725.37), ('2018','January','ENT_1018','1004','CAD Total',1715724.61), ('2018','January','ENT_1018','1004','USD Total',1395741.97), ('2018','January','ENT_1019','1599','USD Total',0), ('2018','January','ENT_1019','1623','USD Total',0), ('2018','January','ENT_1024','1588','USD Total',77306.96), ('2018','January','ENT_1024','1598','USD Total',-444.29), ('2018','January','ENT_1024','1599','USD Total',3094786.66), ('2018','January','ENT_1024','1612','USD Total',348033.55), ('2018','January','ENT_1024','1623','USD Total',0), ('2018','January','ENT_1024','2041','USD Total',444.29), ('2018','January','ENT_1024','2441','USD Total',-31044461.11), ('2018','January','ENT_1024','2484','USD Total',416492.27), ('2018','January','ENT_1024','471','USD Total',2465066.45), ('2018','January','ENT_1024','481','USD Total',34067183.1), ('2018','January','ENT_1024','783','USD Total',4493830.69), ('2018','January','ENT_1028','1591','USD Total',1234.9), ('2018','January','ENT_1028','1599','USD Total',-476400.68), ('2018','January','ENT_1028','1623','USD Total',0), ('2018','January','ENT_1028','481','USD Total',475165.78), ('2018','January','ENT_1500','1004','USD Total',2159023.38), ('2018','January','ENT_1500','1006','USD Total',42525), ('2018','January','ENT_1500','1040','USD Total',327613.42), ('2018','January','ENT_1500','1076','USD Total',59082.48), ('2018','January','ENT_1500','1080','USD Total',810374.8), ('2018','January','ENT_1500','1087','USD Total',7864006), ('2018','January','ENT_1500','1108','USD Total',3247503.67), ('2018','January','ENT_1500','1120','USD Total',-73613.78), ('2018','January','ENT_1500','1167','USD Total',36826.63), ('2018','January','ENT_1500','1228','USD Total',8961.47), ('2018','January','ENT_1500','14','USD Total',2507501.89), ('2018','January','ENT_1500','1551','USD Total',84444.93), ('2018','January','ENT_1510','1579','USD Total',22892), ('2018','January','ENT_1510','1598','USD Total',177722.06), ('2018','January','ENT_1510','1599','USD Total',7583967.07), ('2018','January','ENT_1510','1603','USD Total',-5805000), ('2018','January','ENT_1510','1668','USD Total',217070.02), ('2018','January','ENT_1510','1822','USD Total',38943.46), ('2018','January','ENT_1510','2106','USD Total',404.5), ('2018','January','ENT_1510','218','USD Total',404502.61), ('2018','January','ENT_1510','223','USD Total',-404.5), ('2018','January','ENT_1510','2484','USD Total',49211.15), ('2018','January','ENT_1510','28','USD Total',1902495.08), ('2018','January','ENT_1510','35','USD Total',170), ('2018','January','ENT_1510','793','USD Total',153950.87), ('2018','January','ENT_1510','822','USD Total',-44829.36), ('2018','January','ENT_1510','994','USD Total',168849.4), ('2018','January','ENT_3310','1006','MXN Total',35660.11), ('2018','January','ENT_3310','1006','USD Total',1914.95), ('2018','January','ENT_3310','106','MXN Total',1109968.12), ('2018','January','ENT_3310','106','USD Total',59605.29), ('2018','January','ENT_3310','109','MXN Total',1964637.92), ('2018','January','ENT_3310','109','USD Total',105501.06), ('2018','January','ENT_3310','1442','MXN Total',-2574190.72), ('2018','January','ENT_3310','1442','USD Total',-138234.04), ('2018','January','ENT_3310','1588','MXN Total',-23152803.52), ('2018','January','ENT_3310','1588','USD Total',-1785081.17), ('2018','January','ENT_3310','1599','MXN Total',-20083580.99), ('2018','January','ENT_3310','1599','USD Total',-1217204.4), ('2018','January','ENT_5010','1006','EUR Total',11770), ('2018','January','ENT_5010','1006','USD Total',14661.89), ('2018','January','ENT_5010','1009','EUR Total',42155.18), ('2018','January','ENT_5010','1009','USD Total',52512.71), ('2018','January','ENT_5010','1040','EUR Total',75.73), ('2018','January','ENT_5010','1040','USD Total',94.34), ('2018','January','ENT_5010','1043','EUR Total',3083.49), ('2018','January','ENT_5010','1043','USD Total',3841.1), ('2018','January','ENT_5010','1053','EUR Total',600), ('2018','January','ENT_5010','1053','USD Total',747.42), ('2018','January','ENT_5010','1074','EUR Total',270595.89), ('2018','January','ENT_5010','1074','USD Total',337081.3), ('2018','January','ENT_5010','1099','EUR Total',20000), ('2018','January','ENT_5010','1099','USD Total',24914), ('2018','January','ENT_5310','1009','EUR Total',2058.96), ('2018','January','ENT_5310','1009','USD Total',2564.85), ('2018','January','ENT_5310','1040','EUR Total',183.83), ('2018','January','ENT_5310','1040','USD Total',229), ('2018','January','ENT_5310','1043','EUR Total',578.19), ('2018','January','ENT_5310','1043','USD Total',720.25), ('2018','January','ENT_5310','1053','EUR Total',780), ('2018','January','ENT_5310','1053','USD Total',971.65), ('2018','January','ENT_5310','1087','EUR Total',85745.87), ('2018','January','ENT_5310','1087','USD Total',106813.63), ('2018','January','ENT_5310','1100','EUR Total',21995), ('2018','January','ENT_5310','1100','USD Total',27399.17), ('2018','January','ENT_5310','1115','EUR Total',229218.22), ('2018','January','ENT_5310','1115','USD Total',285537.14), ('2018','January','ENT_5312','1026','PLN Total',153740.19), ('2018','January','ENT_5312','1026','USD Total',46137.43), ('2018','January','ENT_5312','103','PLN Total',6884.58), ('2018','January','ENT_5312','103','USD Total',2066.06), ('2018','January','ENT_5312','1043','PLN Total',171724.43), ('2018','January','ENT_5312','1043','USD Total',51534.5), ('2018','January','ENT_5312','1115','PLN Total',-6749.96), ('2018','January','ENT_5312','1115','USD Total',-2025.66), ('2018','January','ENT_5312','1288','PLN Total',63393.64), ('2018','January','ENT_5312','1288','USD Total',19024.43), ('2018','January','ENT_5312','1550','PLN Total',2272976.96), ('2018','January','ENT_5312','1550','USD Total',682120.39), ('2018','January','ENT_7010','1100','SGD Total',15000), ('2018','January','ENT_7010','1100','USD Total',11434.5), ('2018','January','ENT_7010','1442','SGD Total',38167), ('2018','January','ENT_7010','1442','USD Total',29094.7), ('2018','January','ENT_7010','1588','SGD Total',-1406670.33), ('2018','January','ENT_7010','1588','USD Total',-1128149.62), ('2018','January','ENT_7010','1599','SGD Total',1347080.79), ('2018','January','ENT_7010','1599','USD Total',919160.18), ('2018','January','ENT_7010','214','SGD Total',-2263.05), ('2018','January','ENT_7010','214','USD Total',-1725.12), ('2018','January','ENT_7010','346','SGD Total',353.84), ('2018','January','ENT_7010','346','USD Total',269.73), ('2018','January','ENT_7010','481','SGD Total',4513.23), ('2018','January','ENT_7010','481','USD Total',3440.44), ('2018','January','ENT_7620','1004','RMB Total',31784.37), ('2018','January','ENT_7620','1004','USD Total',5018.75), ('2018','January','ENT_7620','1043','RMB Total',151250), ('2018','January','ENT_7620','1043','USD Total',23882.38), ('2018','January','ENT_7620','1080','RMB Total',50689.75), ('2018','January','ENT_7620','1080','USD Total',8003.91), ('2018','January','ENT_7620','1087','RMB Total',182521.22), ('2018','January','ENT_7620','1087','USD Total',28820.1), ('2018','January','ENT_7620','1096','RMB Total',3500), ('2018','January','ENT_7620','1096','USD Total',552.65), ('2018','January','ENT_7620','1099','RMB Total',154717), ('2018','January','ENT_7620','1099','USD Total',24429.81), ('2018','January','ENT_7620','1100','RMB Total',172200), ('2018','January','ENT_7620','1100','USD Total',27190.38), ('2018','January','ENT_7620','1228','RMB Total',7275301.21), ('2018','January','ENT_7620','1228','USD Total',1148770.06), ('2018','January','ENT_7620','1442','RMB Total',702445.35), ('2018','January','ENT_7620','1442','USD Total',110916.12), ('2018','January','ENT_7620','1588','RMB Total',-34617.83), ('2018','January','ENT_7620','1588','USD Total',-5625.38), ('2018','January','ENT_7620','1591','RMB Total',11938178.08), ('2018','January','ENT_7620','1591','USD Total',1788339.08), ('2018','January','ENT_7620','1599','RMB Total',11998828.79), ('2018','January','ENT_7620','1599','USD Total',1738398.13) ;

SELECT L.Entity, L.Account, R.Local_Currency_ID, L.Report_Currency_ID, L.Amt AS USAmt, R.Amt FROM (SELECT U.Entity, U.Account, U.Currency AS Report_Currency_ID, U.Amt FROM #CIM_FACT AS U WHERE Currency = 'USD Total') AS L LEFT JOIN (SELECT C.Entity, C.Account, C.Currency AS Local_Currency_ID, C.Amt FROM #CIM_FACT AS C WHERE Currency <> 'USD Total') AS R ON L.Entity = R.Entity AND L.Account = R.Account ;
This gets the following results:

There are a lot of Entity and Accounts for 'USD' that do not have a match for other currency. Is this what you are expecting?

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

randyetheridge SSCommitted Points: 1703 More actions · Answer 7

I must say, for some reason the insert command never seems to work for me. hence why I just use MS Access to load data. I have tried numerous combinations of ' ' around the strings and I always get an error. for some reason I believe this should work but it does not.

Insert INTO dbo.cim_Fact

VALUES(2018,’January’,’ENT_1000’,1588,’USD Total’,-888823191.97),

(2018,’January’,’ENT_1000’,1591,’USD Total’,0.01),

(2018,’January’,’ENT_1000’,1599,’USD Total’,888823191.96),

(2018,’January’,’ENT_1000’,1623,’USD Total’,0),

(2018,’January’,’ENT_1000’,783’,’USD Total’,0),

(2018,’January’,’ENT_1005’,1554,’USD Total’,36299019.4),

(2018,’January’,’ENT_1005’,1581,’USD Total’,257505.26),

(2018,’January’,’ENT_1005’,1584,’USD Total’,165502103.58),

(2018,’January’,’ENT_1005’,1585,’USD Total’,17755523.43),

(2018,’January’,’ENT_1005’,1586,’USD Total’,-17755523.43),

(2018,’January’,’ENT_1005’,1593,’USD Total’,-12548880.08),

(2018,’January’,’ENT_1005’,1599,’USD Total’,-11639198.67),

(2018,’January’,’ENT_1005’,1622,’USD Total’,-1000000),

(2018,’January’,’ENT_1005’,178,’USD Total’,38577829),

(2018,’January’,’ENT_1005’,783,’USD Total’,138292720.49),

(2018,’January’,’ENT_1007’’,1034,’USD Total’,-0.23),

(2018,’January’,’ENT_1007’’,1035,’USD Total’,-1094.09),

(2018,’January’,’ENT_1007’’,1529,’USD Total’,40153587.27),

(2018,’January’,’ENT_1007’’,1581,’USD Total’,160000),

(2018,’January’,’ENT_1007’’,1589,’USD Total’,71050691.48),

(2018,’January’,’ENT_1007’’,1590,’USD Total’,27745583.46),

(2018,’January’,’ENT_1007’’,1591,’USD Total’,-130446661.27),

(2018,’January’,’ENT_1007’’,1593,’USD Total’,284584160),

(2018,’January’,’ENT_1007’,1599,’USD Total’,18780997.06),

(2018,’January’,’ENT_1007’,1612,’USD Total’,-3767296.62),

(2018,’January’,’ENT_1007’,1622,’USD Total’,-13579584.46),

(2018,’January’,’ENT_1007’,1623,’USD Total’,0),

(2018,’January’,’ENT_1007’,2484,’USD Total’,540839803.43),

(2018,’January’,’ENT_1007’,2486,’USD Total’,242281.02),

(2018,’January’,’ENT_1007’,2487,’USD Total’,-458943.48),

(2018,’January’,’ENT_1007’,2488,’USD Total’,-184788.56),

(2018,’January’,’ENT_1007’,2491,’USD Total’,-0.02),

(2018,’January’,’ENT_1007’,471,’USD Total’,28004721.55),

(2018,’January’,’ENT_1007’,481,’USD Total’,30986746.55),

(2018,’January’,’ENT_1007’,783,’USD Total’,838100759.99),

(2018,’January’,’ENT_1010’,1004,’USD Total’,23498452.79),

(2018,’January’,’ENT_1010’,1006,’USD Total’,1205416.25),

(2018,’January’,’ENT_1010’,1010,’USD Total’,2599846.02),

(2018,’January’,’ENT_1010’,1019,’USD Total’,2987810.05),

(2018,’January’,’ENT_1010’,1020,’USD Total’,1810637.62),

(2018,’January’,’ENT_1010’,1022,’USD Total’,-2388800.07),

(2018,’January’,’ENT_1010’,1026,’USD Total’,4661821.97),

(2018,’January’,’ENT_1010’,1027,’USD Total’,761786.12),

(2018,’January’,’ENT_1010’,1031,’USD Total’,99551),

(2018,’January’,’ENT_1010’,1032,’USD Total’,31849.86),

(2018,’January’,’ENT_1010’,1033,’USD Total’,-135207.65),

(2018,’January’,’ENT_1010’,1037,’USD Total’,3870804.55),

(2018,’January’,’ENT_1010’,1038,’USD Total’,257088.2),

(2018,’January’,’ENT_1010’,1039,’USD Total’,6587150),

(2018,’January’,’ENT_1010’,1040,’USD Total’,1066222.19),

(2018,’January’,’ENT_1010’,1043,’USD Total’,937463.63),

(2018,’January’,’ENT_1010’,1045,’USD Total’,103159.57),

(2018,’January’,’ENT_1010’,1046,’USD Total’,-9731),

(2018,’January’,’ENT_1010’,1050,’USD Total’,902377.91),

(2018,’January’,’ENT_1010’,1051,’USD Total’,41595.94),

(2018,’January’,’ENT_1010’,1053,’USD Total’,416456.53),

(2018,’January’,’ENT_1010’,1054,’USD Total’,-1634499.12),

(2018,’January’,’ENT_1010’,1055,’USD Total’,340638.54),

(2018,’January’,’ENT_1010’,1056,’USD Total’,-13786.37),

(2018,’January’,’ENT_1010’,1060,’USD Total’,2589599.83),

(2018,’January’,’ENT_1010’,1066,’USD Total’,42426.51),

(2018,’January’,’ENT_1010’,1067,’USD Total’,75067.2),

(2018,’January’,’ENT_1012’,1039,’USD Total’,1992500),

(2018,’January’,’ENT_1012’,1071,’USD Total’,45779.24),

(2018,’January’,’ENT_1012’,1087,’USD Total’,2604656.15),

(2018,’January’,’ENT_1012’,1109,’USD Total’,279016.86),

(2018,’January’,’ENT_1012’,1115,’USD Total’,-192704.6),

(2018,’January’,’ENT_1012’,1125,’USD Total’,1211.71),

(2018,’January’,’ENT_1012’,1127,’USD Total’,386.03),

(2018,’January’,’ENT_1012’,1136,’USD Total’,997.5),

(2018,’January’,’ENT_1012’,1137,’USD Total’,0.03),

(2018,’January’,’ENT_1012’,1140,’USD Total’,29.18),

(2018,’January’,’ENT_1014’,1588,’USD Total’,13121074.53),

(2018,’January’,’ENT_1014’,1598,’USD Total’,59580.75),

(2018,’January’,’ENT_1014’,1599,’USD Total’,2111557.61),

(2018,’January’,’ENT_1014’,2201,’USD Total’,-59580.75),

(2018,’January’,’ENT_1014’,471,’USD Total’,8867474.52),

(2018,’January’,’ENT_1014’,777,’USD Total’,982840.37),

(2018,’January’,’ENT_1014’,778,’USD Total’,5441898),

(2018,’January’,’ENT_1016’,1588,’USD Total’,83366671.71),

(2018,’January’,’ENT_1016’,1589,’USD Total’,35525345.74),

(2018,’January’,’ENT_1016’,1599,’USD Total’,10070933.69),

(2018,’January’,’ENT_1016’,1612,’USD Total’,2962651.71),

(2018,’January’,’ENT_1016’,1623,’USD Total’,0),

(2018,’January’,’ENT_1016’,2484,’USD Total’,-51599191.31),

(2018,’January’,’ENT_1016’,2487,’USD Total’,-4943890.48),

(2018,’January’,’ENT_1016’,481,’USD Total’,9375309.84),

(2018,’January’,’ENT_1016’,783,’USD Total’,84757830.9),

(2018,’January’,’ENT_1018’,1000,CAD Total,605602.3),

(2018,’January’,’ENT_1018’,1000,’USD Total’,492657.47),

(2018,’January’,’ENT_1018’,1002,CAD Total,2120.92),

(2018,’January’,’ENT_1018’,1002,’USD Total’,1725.37),

(2018,’January’,’ENT_1018’,1004,CAD Total,1715724.61),

(2018,’January’,’ENT_1018’,1004,’USD Total’,1395741.97),

(2018,’January’,’ENT_1019’,1599,’USD Total’,0),

(2018,’January’,’ENT_1019’,1623,’USD Total’,0),

(2018,’January’,’ENT_1024’,1588,’USD Total’,77306.96),

(2018,’January’,’ENT_1024’,1598,’USD Total’,-444.29),

(2018,’January’,’ENT_1024’,1599,’USD Total’,3094786.66),

(2018,’January’,’ENT_1024’,1612,’USD Total’,348033.55),

(2018,’January’,’ENT_1024’,1623,’USD Total’,0),

(2018,’January’,’ENT_1024’,2041,’USD Total’,444.29),

(2018,’January’,’ENT_1024’,2441,’USD Total’,-31044461.11),

(2018,’January’,’ENT_1024’,2484,’USD Total’,416492.27),

(2018,’January’,’ENT_1024’,471,’USD Total’,2465066.45),

(2018,’January’,’ENT_1024’,481,’USD Total’,34067183.1),

(2018,’January’,’ENT_1024’,783,’USD Total’,4493830.69),

(2018,’January’,’ENT_1028’,1591,’USD Total’,1234.9),

(2018,’January’,’ENT_1028’,1599,’USD Total’,-476400.68),

(2018,’January’,’ENT_1028’,1623,’USD Total’,0),

(2018,’January’,’ENT_1028’,481,’USD Total’,475165.78),

(2018,’January’,’ENT_1500’,1004,’USD Total’,2159023.38),

(2018,’January’,’ENT_1500’,1006,’USD Total’,42525),

(2018,’January’,’ENT_1500’,1040,’USD Total’,327613.42),

(2018,’January’,’ENT_1500’,1076,’USD Total’,59082.48),

(2018,’January’,’ENT_1500’,1080,’USD Total’,810374.8),

(2018,’January’,’ENT_1500’,1087,’USD Total’,7864006),

(2018,’January’,’ENT_1500’,1108,’USD Total’,3247503.67),

(2018,’January’,’ENT_1500’,1120,’USD Total’,-73613.78),

(2018,’January’,’ENT_1500’,1167,’USD Total’,36826.63),

(2018,’January’,’ENT_1500’,1228,’USD Total’,8961.47),

(2018,’January’,’ENT_1500’,14,’USD Total’,2507501.89),

(2018,’January’,’ENT_1500’,1551,’USD Total’,84444.93),

(2018,’January’,’ENT_1510,1579,’USD Total’,22892),

(2018,’January’,’ENT_1510,1598,’USD Total’,177722.06),

(2018,’January’,’ENT_1510,1599,’USD Total’,7583967.07),

(2018,’January’,’ENT_1510,1603,’USD Total’,-5805000),

(2018,’January’,’ENT_1510,1668,’USD Total’,217070.02),

(2018,’January’,’ENT_1510,1822,’USD Total’,38943.46),

(2018,’January’,’ENT_1510,2106,’USD Total’,404.5),

(2018,’January’,’ENT_1510,218,’USD Total’,404502.61),

(2018,’January’,’ENT_1510,223,’USD Total’,-404.5),

(2018,’January’,’ENT_1510,2484,’USD Total’,49211.15),

(2018,’January’,’ENT_1510,28,’USD Total’,1902495.08),

(2018,’January’,’ENT_1510,35,’USD Total’,170),

(2018,’January’,’ENT_1510,793,’USD Total’,153950.87),

(2018,’January’,’ENT_1510,822,’USD Total’,-44829.36),

(2018,’January’,’ENT_1510,994,’USD Total’,168849.4),

(2018,’January’,’ENT_3310’,1006,MXN Total,35660.11),

(2018,’January’,’ENT_3310’,1006,’USD Total’,1914.95),

(2018,’January’,’ENT_3310’,106,MXN Total,1109968.12),

(2018,’January’,’ENT_3310’,106,’USD Total’,59605.29),

(2018,’January’,’ENT_3310’,109,MXN Total,1964637.92),

(2018,’January’,’ENT_3310’,109,’USD Total’,105501.06),

(2018,’January’,’ENT_3310’,1442,MXN Total,-2574190.72),

(2018,’January’,’ENT_3310’,1442,’USD Total’,-138234.04),

(2018,’January’,’ENT_3310’,1588,MXN Total,-23152803.52),

(2018,’January’,’ENT_3310’,1588,’USD Total’,-1785081.17),

(2018,’January’,’ENT_3310’,1599,MXN Total,-20083580.99),

(2018,’January’,’ENT_3310’,1599,’USD Total’,-1217204.4),

(2018,’January’,’ENT_5010’,1006,EUR Total,11770),

(2018,’January’,’ENT_5010‘,1006,’USD Total’,14661.89),

(2018,’January’,’ENT_5010’,1009,EUR Total,42155.18),

(2018,’January’,’ENT_5010’,1009,’USD Total’,52512.71),

(2018,’January’,’ENT_5010’,1040,EUR Total,75.73),

(2018,’January’,’ENT_5010’,1040,’USD Total’,94.34),

(2018,’January’,’ENT_5010’,1043,EUR Total,3083.49),

(2018,’January’,’ENT_5010’,1043,’USD Total’,3841.1),

(2018,’January’,’ENT_5010’,1053,EUR Total,600),

(2018,’January’,’ENT_5010’,1053,’USD Total’,747.42),

(2018,’January’,’ENT_5010’,1074,EUR Total,270595.89),

(2018,’January’,’ENT_5010’,1074,’USD Total’,337081.3),

(2018,’January’,’ENT_5010’,1099,EUR Total,20000),

(2018,’January’,’ENT_5010’,1099,’USD Total’,24914),

(2018,’January’,’ENT_5310’,1009,EUR Total,2058.96),

(2018,’January’,’ENT_5310’,1009,’USD Total’,2564.85),

(2018,’January’,’ENT_5310’,1040,EUR Total,183.83),

(2018,’January’,’ENT_5310’,1040,’USD Total’,229),

(2018,’January’,’ENT_5310’,1043,EUR Total,578.19),

(2018,’January’,’ENT_5310’,1043,’USD Total’,720.25),

(2018,’January’,’ENT_5310’,1053,EUR Total,780),

(2018,’January’,’ENT_5310’,1053,’USD Total’,971.65),

(2018,’January’,’ENT_5310’,1087,EUR Total,85745.87),

(2018,’January’,’ENT_5310’,1087,’USD Total’’,106813.63),

(2018,’January’,’ENT_5310’,1100,EUR Total,21995),

(2018,’January’,’ENT_5310’,1100,’USD Total’,27399.17),

(2018,’January’,’ENT_5310’,1115,EUR Total,229218.22),

(2018,’January’,’ENT_5310’,1115,’USD Total’,285537.14),

(2018,’January’,’ENT_5312’,1026,PLN Total,153740.19),

(2018,’January’,’ENT_5312’,1026,’USD Total’,46137.43),

(2018,’January’,’ENT_5312’,103,PLN Total,6884.58),

(2018,’January’,’ENT_5312’,103,’USD Total’,2066.06),

(2018,’January’,’ENT_5312’,1043,PLN Total,171724.43),

(2018,’January’,’ENT_5312’,1043,’USD Total’,51534.5),

(2018,’January’,’ENT_5312’,1115,PLN Total,-6749.96),

(2018,’January’,’ENT_5312’,1115,’USD Total’,-2025.66),

(2018,’January’,’ENT_5312’,1288,PLN Total,63393.64),

(2018,’January’,’ENT_5312’,1288,’USD Total’,19024.43),

(2018,’January’,’ENT_5312’,1550,PLN Total,2272976.96),

(2018,’January’,’ENT_5312’,1550,’USD Total’,682120.39),

(2018,’January’,’ENT_7010’,1100,SGD Total,15000),

(2018,’January’,’ENT_7010’,1100,’USD Total’,11434.5),

(2018,’January’,’ENT_7010’,1442,SGD Total,38167),

(2018,’January’,’ENT_7010’,1442,’USD Total’,29094.7),

(2018,’January’,’ENT_7010’,1588,SGD Total,-1406670.33),

(2018,’January’,’ENT_7010’,1588,’USD Total’,-1128149.62),

(2018,’January’,’ENT_7010’,1599,SGD Total,1347080.79),

(2018,’January’,’ENT_7010’,1599,’USD Total’,919160.18),

(2018,’January’,’ENT_7010’,214,SGD Total,-2263.05),

(2018,’January’,’ENT_7010’,214,’USD Total’,-1725.12),

(2018,’January’,’ENT_7010’,346,SGD Total,353.84),

(2018,’January’,’ENT_7010’,346,’USD Total’,269.73),

(2018,’January’,’ENT_7010’,481,SGD Total,4513.23),

(2018,’January’,’ENT_7010’,481,’USD Total’,3440.44),

(2018,’January’,’ENT_7620’,1004,RMB Total,31784.37),

(2018,’January’,’ENT_7620’,1004,’USD Total’,5018.75),

(2018,’January’,’ENT_7620’,1043,RMB Total,151250),

(2018,’January’,’ENT_7620’,1043,’USD Total’,23882.38),

(2018,’January’,’ENT_7620’,1080,RMB Total,50689.75),

(2018,’January’,’ENT_7620’,1080,’USD Total’,8003.91),

(2018,’January’,’ENT_7620’,1087,RMB Total,182521.22),

(2018,’January’,’ENT_7620’,1087,’USD Total’,28820.1),

(2018,’January’,’ENT_7620’,1096,RMB Total,3500),

(2018,’January’,’ENT_7620’,1096,’USD Total’,552.65),

(2018,’January’,’ENT_7620’,1099,RMB Total,154717),

(2018,’January’,’ENT_7620’,1099,’USD Total’,24429.81),

(2018,’January’,’ENT_7620’,1100,RMB Total,172200),

(2018,’January’,’ENT_7620’,1100,’USD Total’,27190.38),

(2018,’January’,’ENT_7620’,1228,RMB Total,7275301.21),

(2018,’January’,’ENT_7620’,1228,’USD Total’,1148770.06),

(2018,’January’,’ENT_7620’,1442,RMB Total,702445.35),

(2018,’January’,’ENT_7620’,1442,’USD Total’,110916.12),

(2018,’January’,’ENT_7620’,1588,RMB Total,-34617.83),

(2018,’January’,’ENT_7620’,1588,’USD Total’,-5625.38),

(2018,’January’,’ENT_7620’,1591,RMB Total,11938178.08),

(2018,’January’,’ENT_7620’,1591,’USD Total’,1788339.08),

(2018,’January’,’ENT_7620’,1599,RMB Total,11998828.79),

(2018,’January’,’ENT_7620’,1599,’USD Total’,1738398.13)

randyetheridge SSCommitted Points: 1703 More actions · Answer 8

ok thanks for your answer. And your answer is my issue
if the entity ONLY has USD, then the USD amount must be in BOTH columns
if the entity has USD AND XXX then USD in column USAmt and XXX in Amt

I got the same answer that you got back in my original post. What I cannot get done is for the USD ONLY entities to show the AMT in both columns. that is my issue.

sorry about posting my INSERT command, I forgot to refresh the page so I had not seen your post.

below86 SSChampion Points: 11532 More actions · Answer 9

Try this:
SELECT L.Entity, L.Account, COALESCE(R.Local_Currency_ID, L.Report_Currency_ID) AS Local_Currency_ID, L.Report_Currency_ID, L.Amt AS USAmt, COALESCE(R.Amt, L.Amt) AS Amt FROM (SELECT U.Entity, U.Account, U.Currency AS Report_Currency_ID, U.Amt FROM #CIM_FACT AS U WHERE Currency = 'USD Total') AS L LEFT JOIN (SELECT C.Entity, C.Account, C.Currency AS Local_Currency_ID, C.Amt FROM #CIM_FACT AS C WHERE Currency <> 'USD Total') AS R ON L.Entity = R.Entity AND L.Account = R.Account ;

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

This has got to be close. It looks right, although my result set has 180 rows whereas yours has 184.

WITH USandLocal AS ( SELECT Year , Period , Entity , Account , MAX(CASE WHEN Currency = 'USD Total' THEN Amt ELSE 0 END) AS USDAmount , MAX(CASE WHEN Currency <> 'USD Total' THEN Amt ELSE 0 END) AS LocalAmount FROM #cim_Fact GROUP BY Year , Period , Entity , Account ) SELECT Year , Period , Entity , Account , USDAmount , CASE WHEN LocalAmount = 0 THEN USDAmount ELSE LocalAmount END AS LocalAmount FROM USandLocal;

John

randyetheridge SSCommitted Points: 1703 More actions · Answer 11

so awesome, it worked, and now I see why I could not figure it out. A DB Admin showed me select statements inside the From statement and it just did not click at the time
now that you have shown me this and I understand the data better, maybe I can figure out how this works

I cannot thank you enough for your patience with me. this was more over my head that I had originally thought. thanks again

below86 SSChampion Points: 11532 More actions · Answer 12

John Mitchell-245523 - Tuesday, April 17, 2018 12:16 PM
This has got to be close. It looks right, although my result set has 180 rows whereas yours has 184.
WITH USandLocal AS ( SELECT Year , Period , Entity , Account , MAX(CASE WHEN Currency = 'USD Total' THEN Amt ELSE 0 END) AS USDAmount , MAX(CASE WHEN Currency <> 'USD Total' THEN Amt ELSE 0 END) AS LocalAmount FROM #cim_Fact GROUP BY Year , Period , Entity , Account ) SELECT Year , Period , Entity , Account , USDAmount , CASE WHEN LocalAmount = 0 THEN USDAmount ELSE LocalAmount END AS LocalAmount FROM USandLocal;
John

I got 180 rows in my SQL on both versions. You are not including the Currency ID in yours. You can't tell what currency the 'Local Amount' represents.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

below86 - Tuesday, April 17, 2018 12:29 PM
You are not including the Currency ID in yours. You can't tell what currency the 'Local Amount' represents.

Indeed you can't. But that's how it was in the expected results spreadsheet - I did think that a little strange.

John

lpowell_NC Grasshopper Points: 23 More actions · Answer 14

Try this. I created a smaller version of your table and cut down on the data.

CREATE TABLE dbo.CIMBalances (
[Entity] nvarchar (10) not null,
[Account] nvarchar (10) not null,
[CurrencyID] nvarchar (10) not null,
[Amt] decimal (18,2) not null
);

Insert INTO dbo.CIMBalances (Entity, Account, CurrencyID, Amt)
VALUES
('ENT_1000', '1588', 'USD', 1100.00),
('ENT_1000', '1588', 'CAD', 2100.00),
('ENT_1000', '1588', 'MXN', 3100.00),
('ENT_1000', '1588', 'PLN', 4100.00),
('ENT_1000', '1588', 'SGD', 5100.00),
('ENT_1000', '1588', 'USD', 1.00),
('ENT_1000', '1588', 'CAD', 1.00),
('ENT_1000', '1588', 'MXN', 1.00),
('ENT_1000', '1588', 'PLN', 1.00),
('ENT_1000', '1588', 'SGD', 1.00),
('ENT_1000', 'XYZ', 'USD', 100.00),
('ENT_1000', 'XYZ', 'CAD', 200.00),
('ENT_1000', 'XYZ', 'MXN', 300.00),
('ENT_1000', 'XYZ', 'PLN', 400.00),
('ENT_1000', 'XYZ', 'USD', 1.00),
('ENT_1000', 'XYZ', 'CAD', 1.00),
('ENT_1000', 'XYZ', 'MXN', 1.00),
('ENT_1000', 'XYZ', 'PLN', 1.00)

-- using non-dynamic sql -- hard-coding Currency values (USD, CAD,MXN, PLN, SGD)
SELECT *
FROM (SELECT CurrencyID as Currency, entity, account, Amt FROM dbo.CIMBalances) AS source
PIVOT (SUM(Amt) FOR Currency in (CAD,MXN, PLN, SGD, USD))AS pvt
ORDER BY entity, account

-- using dynamic sql
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';

SELECT @columns += N', p.' + QUOTENAME(CurrencyID)
FROM (Select CurrencyID
from dbo.CIMBalances
Group by CurrencyID
) AS Curr;

SET @sql =
N'SELECT * FROM (SELECT CurrencyID as Currency, entity, account, Amt FROM dbo.CIMBalances) AS j
PIVOT (SUM(Amt) FOR Currency IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';
--PRINT @sql;
EXEC sp_executesql @sql;

randyetheridge SSCommitted Points: 1703 More actions · Answer 15

ok yes, the expected output does not have the local currency identified. the reason for this is, this information is going to a third party application that accounts use to create monthly account reconciliations. the application has a master table and the entity local currency is identified in this master table. So when this data is loaded and combined with the static master table data the accountants now know, the entity, the account, the USD balance, and the local Balance and local currently because the local currency is identified from this master table.

this data is being used as a feed to a third party application. We had to meet their specifications. I thought no local currency was weird as well, but it is what it is.