summing different combinations of selections

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

summing different combinations of selections

Author

Topic

ishaame
Starting Member

12 Posts

Posted - 2008-05-07 : 11:43:46

I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='\' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"

**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)

Thanks in advance
I.Shaame

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 11:51:17

i think you require a CASE construct inside SUM() to get summation of column value for different conditions. post some sample data from table and your desired o/p if yoiu need more help on this.

ishaame
Starting Member

12 Posts

Posted - 2008-05-07 : 12:37:26

can't see how I can attach the data file I wonder whether pasting it here would do? can send it in seperate email if necessary.
Thanks

heri is:
ACCOUNT COSTCENTER DONOR ACTIVITY TTYPE TransactionDate AmountLCU
3410 P4 1273 P402RB U 31/01/2007 -2,895.00
3410 P4 1273 P402RB U 28/02/2007 -2,895.00
3410 P4 1273 P402RB U 31/03/2007 -2,895.00
3410 P4 1273 P402RB U 30/04/2007 -2,895.00
3410 P4 1273 P402RB U 31/05/2007 -2,895.00
3410 P4 1273 P402RB U 30/06/2007 -2,895.00
3410 P4 1273 P402RB U 31/07/2007 -2,895.00
3410 P4 1273 P402RB U 31/08/2007 -2,895.00
3410 P4 1273 P402RB U 30/09/2007 -2,895.00
3410 P4 1273 P402RB U 31/10/2007 -2,895.00
3410 P4 1273 P402RB U 30/11/2007 -2,895.00
3410 P4 1273 P402RB U 29/12/2007 -2,895.00
3410 P4 1108 P402RC U 31/01/2007 -2,125.00
3410 P4 1108 P402RC U 28/02/2007 -2,125.00
3410 P4 1108 P402RC U 31/03/2007 -2,125.00
3410 P4 1108 P402RC U 30/04/2007 -2,125.00
3410 P4 1108 P402RC U 31/05/2007 -2,125.00
3410 P4 1108 P402RC U 30/06/2007 -2,125.00
3410 P4 1108 P402RC U 31/07/2007 -2,125.00
3410 P4 1108 P402RC U 31/08/2007 -2,125.00
3410 P4 1108 P402RC U 30/09/2007 -2,125.00
3410 P4 1108 P402RC U 31/10/2007 -2,125.00
3410 P4 1108 P402RC U 30/11/2007 -2,125.00
3410 P4 1108 P402RC U 29/12/2007 -2,125.00
3410 P4 1108 P4053A U 31/01/2007 -4,250.00
3410 P4 1108 P4053A U 28/02/2007 -4,250.00
3410 P4 1108 P4053A U 31/03/2007 -4,250.00
3410 P4 1108 P4053A U 30/04/2007 -4,250.00
3410 P4 1108 P4053A U 31/05/2007 -4,250.00
3410 P4 1108 P4053A U 30/06/2007 -4,250.00
3410 P4 1108 P4053A U 31/07/2007 -4,250.00
3410 P4 1108 P4053A U 31/08/2007 -4,250.00
3410 P4 1108 P4053A U 30/09/2007 -4,250.00
3410 P4 1108 P4053A U 31/10/2007 -4,250.00
3410 P4 1108 P4053A U 30/11/2007 -4,250.00
3410 P4 1108 P4053A U 29/12/2007 -4,250.00
3410 P4 1009 YP403 U 31/01/2007 -1,475.00
3410 P4 1009 YP403 U 28/02/2007 -1,475.00
3410 P4 1009 YP403 U 31/03/2007 -1,475.00
3410 P4 1009 YP403 U 30/04/2007 -1,475.00
3410 P4 1009 YP403 U 31/05/2007 -1,475.00
3410 P4 1009 YP403 U 30/06/2007 -1,475.00
3410 P4 1009 YP403 U 31/07/2007 -1,475.00
3410 P4 1009 YP403 U 31/08/2007 -1,475.00
3410 P4 1009 YP403 U 30/09/2007 -1,475.00
3410 P4 1009 YP403 U 31/10/2007 -1,475.00
3410 P4 1009 YP403 U 30/11/2007 -1,475.00
3410 P4 1009 YP403 U 29/12/2007 -1,475.00
3410 P4 1009 YYP4 U 31/01/2007 -1,475.00
2610 P4 P4014A / 01/01/1900 -21,314.73
2610 P4 P4014B / 01/01/1900 -41,314.70
2610 P4 P4014C / 01/01/1900 -44,879.00
2610 P4 YP404 / 01/01/1900 -18,653.02
2610 P4 P4062C / 01/01/1900 -24,396.25
2610 P4 P4071B / 01/01/1900 -2,237.84
2610 P4 XXP4 / 01/01/1900 9,026.00
2610 P4 YP407 / 01/01/1900 -119,862.38
2610 P4 AFR008 / 01/01/1900 -2,730.11
2610 P4 ASA003 / 01/01/1900 -2,417.57
2610 P4 ASA025 / 01/01/1900 -13,532.39
2610 P4 ASA034 / 01/01/1900 -7,792.19
2610 P4 ASA037 / 01/01/1900 -1,919.61
2610 P4 BD7504 / 01/01/1900 -6,523.87
2610 P4 CAR000 / 01/01/1900 -452.65
2610 P4 CAR003 / 01/01/1900 -101.56
2610 P4 GD0004 / 01/01/1900 -339.57
2610 P4 ID0003 / 01/01/1900 -6,243.72
2610 P4 IN7411 / 01/01/1900 -5,424.39
2610 P4 LK8404 / 01/01/1900 -1,031.77
2610 P4 MEA004 / 01/01/1900 -30.12
2610 P4 MM0005 / 01/01/1900 -3,356.44
2610 P4 PAC002 / 01/01/1900 -24,426.13
2610 P4 PAC006 / 01/01/1900 -9,230.34
2610 P4 PAC014 / 01/01/1900 -10,000.00
2610 P4 WW0175 / 01/01/1900 -12,201.22
2610 P4 WW0308 / 01/01/1900 -8,327.51
2610 P4 WW0309 / 01/01/1900 -190,231.66
2610 P4 XXP4 / 01/01/1900 12,592.00
2610 P4 YP401 / 01/01/1900 -15,781.26
2610 P4 YP4013 / 01/01/1900 -73,427.20
2610 P4 YP402 / 01/01/1900 -23,664.77
2610 P4 YP402 / 01/01/1900 -65,925.90
2610 P4 YP403 / 01/01/1900 -17,167.18
3410 P4 1273 YP401 0 02/07/2007 -24,817.50
3410 P4 1273 YP405 0 02/07/2007 -16,545.00
3410 P4 1273 YP407 0 02/07/2007 -82,725.00
3410 P4 1273 ASA035 0 02/07/2007 -13,236.00
3410 P4 1273 MD0001 0 02/07/2007 -33,090.00
3410 P4 1273 BR7401 0 02/07/2007 -26,472.00
3410 P4 1273 AFR009 0 02/07/2007 -66,180.00
3410 P4 1273 ASA004 0 02/07/2007 -33,090.00
3410 P4 1108 AFR026 0 12/06/2007 -13,280.25
3410 P4 1108 AM9410 0 12/06/2007 -6,640.13
3410 P4 1108 CAR003 0 12/06/2007 -17,715.85
3410 P4 1108 LAM014 0 12/06/2007 -1,328.03
3410 P4 1108 LAM043 0 12/06/2007 -1,770.70
3410 P4 1108 LAM052 0 12/06/2007 -6,197.45
3410 P4 1108 LAM053 0 12/06/2007 -4,426.75
3410 P4 1108 LAM055 0 12/06/2007 -4,338.22
3410 P4 1108 AR0002 0 12/06/2007 -6,197.45
3410 P4 1108 BR7401 0 12/06/2007 -7,968.15
3410 P4 1108 BR7503 0 12/06/2007 -8,047.83
3410 P4 1108 PE0006 0 12/06/2007 -1,770.70
3410 P4 1108 SV8901 0 12/06/2007 -2,213.36
3410 P4 1108 EUR016 0 12/06/2007 -6,640.13
3410 P4 1044 YYP4 0 03/07/2007 -25,000.00
3410 P4 1290 YP407 0 03/07/2007 -100,000.00
3410 P4 1368 YP406 0 27/06/2007 -40,045.50
3410 P4 1368 YP407 0 27/06/2007 -22,247.50
3410 P4 2122 P4053A 0 19/06/2007 -5,789.25
3410 P4 1079 YYP4 0 22/06/2007 -8,185.41
3410 P4 1295 YP407 0 13/07/2007 -199,068.00
3410 P4 1295 YP401 0 16/07/2007 -331,400.00
3410 P4 1295 YP402 0 16/07/2007 -165,700.00
3410 P4 1295 YP403 0 16/07/2007 -165,700.00
3410 P4 1295 YP405 0 16/07/2007 -66,280.00
3410 P4 2096 YP406 0 16/07/2007 -66,280.00
3410 P4 2096 YP404 0 16/07/2007 -66,280.00
3410 P4 2096 YP405 0 16/07/2007 -66,280.00
3410 P4 1108 EUR012 0 16/07/2007 -32,551.20
3410 P4 1108 LAM015 0 16/07/2007 -27,126.00
3410 P4 1108 MEA006 0 16/07/2007 -28,482.30
3410 P4 1108 YP403 0 16/07/2007 -18,084.00
3410 P4 1108 YP405 0 16/07/2007 -9,042.00
3410 P4 1108 P4053A 0 16/07/2007 -13,563.00
3410 P4 1108 YP406 0 16/07/2007 -13,563.00
3410 P4 1108 YP407 0 16/07/2007 -18,084.00
3410 P4 1108 AFR026 0 16/07/2007 -6,781.50
3410 P4 1108 CAR003 0 16/07/2007 -9,046.52
3410 P4 1108 LAM014 0 16/07/2007 -678.15
3410 P4 1108 LAM043 0 16/07/2007 -904.20
3410 P4 1108 LAM052 0 16/07/2007 -3,164.70
3410 P4 1108 LAM053 0 16/07/2007 -2,260.50
3410 P4 1108 LAM055 0 16/07/2007 -2,215.29
3410 P4 1108 AR0002 0 16/07/2007 -3,164.70
3410 P4 1108 BR7401 0 16/07/2007 -4,068.90
3410 P4 1108 BR7503 0 16/07/2007 -4,109.59
3410 P4 1108 PE0006 0 16/07/2007 -904.20
3410 P4 1108 SV8901 0 16/07/2007 -1,130.25
3410 P4 1178 YYP4 0 11/07/2007 -206,750.00
3410 P4 4045 YP4011 0 17/07/2007 -29,833.20
3410 P4 4045 P4062A 0 17/07/2007 -21,546.20
3410 P4 4045 AFR008 0 17/07/2007 -36,462.80
3410 P4 4045 ASA037 0 17/07/2007 -31,490.60
3410 P4 4045 CAR000 0 17/07/2007 -11,601.80
3410 P4 4045 EUR016 0 17/07/2007 -8,287.00
3410 P4 4045 LAM014 0 17/07/2007 -21,546.20
3410 P4 4045 MEA004 0 17/07/2007 -14,916.60
3410 P4 4045 LAM015 0 17/07/2007 -16,574.00
3410 P4 4045 MEA006 0 17/07/2007 -8,287.00
3410 P4 1079 YYP4 0 22/06/2007 -8,185.41
3410 P4 1295 YP407 0 13/07/2007 -199,068.00
3410 P4 1295 YP401 0 16/07/2007 -331,400.00
3410 P4 1295 YP402 0 16/07/2007 -165,700.00
3410 P4 1295 YP403 0 16/07/2007 -165,700.00
3410 P4 1295 YP405 0 16/07/2007 -66,280.00
3410 P4 2096 YP406 0 16/07/2007 -66,280.00
3410 P4 2096 YP404 0 16/07/2007 -66,280.00
3410 P4 2096 YP405 0 16/07/2007 -66,280.00
3410 P4 1108 EUR012 0 16/07/2007 -32,551.20
3410 P4 1108 LAM015 0 16/07/2007 -27,126.00
3410 P4 1108 MEA006 0 16/07/2007 -28,482.30
3410 P4 1108 YP403 0 16/07/2007 -18,084.00
3410 P4 1108 YP405 0 16/07/2007 -9,042.00
3410 P4 1108 P4053A 0 16/07/2007 -13,563.00
3410 P4 1108 YP406 0 16/07/2007 -13,563.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 12:39:18

quote:
Originally posted by ishaame

can't see how I can attach the data file I wonder whether pasting it here would do? can send it in seperate email if necessary.
Thanks

heri is:
ACCOUNT COSTCENTER DONOR ACTIVITY TTYPE TransactionDate AmountLCU
3410 P4 1273 P402RB U 31/01/2007 -2,895.00
3410 P4 1273 P402RB U 28/02/2007 -2,895.00
3410 P4 1273 P402RB U 31/03/2007 -2,895.00
3410 P4 1273 P402RB U 30/04/2007 -2,895.00
3410 P4 1273 P402RB U 31/05/2007 -2,895.00
3410 P4 1273 P402RB U 30/06/2007 -2,895.00
3410 P4 1273 P402RB U 31/07/2007 -2,895.00
3410 P4 1273 P402RB U 31/08/2007 -2,895.00
3410 P4 1273 P402RB U 30/09/2007 -2,895.00
3410 P4 1273 P402RB U 31/10/2007 -2,895.00
3410 P4 1273 P402RB U 30/11/2007 -2,895.00
3410 P4 1273 P402RB U 29/12/2007 -2,895.00
3410 P4 1108 P402RC U 31/01/2007 -2,125.00
3410 P4 1108 P402RC U 28/02/2007 -2,125.00
3410 P4 1108 P402RC U 31/03/2007 -2,125.00
3410 P4 1108 P402RC U 30/04/2007 -2,125.00
3410 P4 1108 P402RC U 31/05/2007 -2,125.00
3410 P4 1108 P402RC U 30/06/2007 -2,125.00
3410 P4 1108 P402RC U 31/07/2007 -2,125.00
3410 P4 1108 P402RC U 31/08/2007 -2,125.00
3410 P4 1108 P402RC U 30/09/2007 -2,125.00
3410 P4 1108 P402RC U 31/10/2007 -2,125.00
3410 P4 1108 P402RC U 30/11/2007 -2,125.00
3410 P4 1108 P402RC U 29/12/2007 -2,125.00
3410 P4 1108 P4053A U 31/01/2007 -4,250.00
3410 P4 1108 P4053A U 28/02/2007 -4,250.00
3410 P4 1108 P4053A U 31/03/2007 -4,250.00
3410 P4 1108 P4053A U 30/04/2007 -4,250.00
3410 P4 1108 P4053A U 31/05/2007 -4,250.00
3410 P4 1108 P4053A U 30/06/2007 -4,250.00
3410 P4 1108 P4053A U 31/07/2007 -4,250.00
3410 P4 1108 P4053A U 31/08/2007 -4,250.00
3410 P4 1108 P4053A U 30/09/2007 -4,250.00
3410 P4 1108 P4053A U 31/10/2007 -4,250.00
3410 P4 1108 P4053A U 30/11/2007 -4,250.00
3410 P4 1108 P4053A U 29/12/2007 -4,250.00
3410 P4 1009 YP403 U 31/01/2007 -1,475.00
3410 P4 1009 YP403 U 28/02/2007 -1,475.00
3410 P4 1009 YP403 U 31/03/2007 -1,475.00
3410 P4 1009 YP403 U 30/04/2007 -1,475.00
3410 P4 1009 YP403 U 31/05/2007 -1,475.00
3410 P4 1009 YP403 U 30/06/2007 -1,475.00
3410 P4 1009 YP403 U 31/07/2007 -1,475.00
3410 P4 1009 YP403 U 31/08/2007 -1,475.00
3410 P4 1009 YP403 U 30/09/2007 -1,475.00
3410 P4 1009 YP403 U 31/10/2007 -1,475.00
3410 P4 1009 YP403 U 30/11/2007 -1,475.00
3410 P4 1009 YP403 U 29/12/2007 -1,475.00
3410 P4 1009 YYP4 U 31/01/2007 -1,475.00
2610 P4 P4014A / 01/01/1900 -21,314.73
2610 P4 P4014B / 01/01/1900 -41,314.70
2610 P4 P4014C / 01/01/1900 -44,879.00
2610 P4 YP404 / 01/01/1900 -18,653.02
2610 P4 P4062C / 01/01/1900 -24,396.25
2610 P4 P4071B / 01/01/1900 -2,237.84
2610 P4 XXP4 / 01/01/1900 9,026.00
2610 P4 YP407 / 01/01/1900 -119,862.38
2610 P4 AFR008 / 01/01/1900 -2,730.11
2610 P4 ASA003 / 01/01/1900 -2,417.57
2610 P4 ASA025 / 01/01/1900 -13,532.39
2610 P4 ASA034 / 01/01/1900 -7,792.19
2610 P4 ASA037 / 01/01/1900 -1,919.61
2610 P4 BD7504 / 01/01/1900 -6,523.87
2610 P4 CAR000 / 01/01/1900 -452.65
2610 P4 CAR003 / 01/01/1900 -101.56
2610 P4 GD0004 / 01/01/1900 -339.57
2610 P4 ID0003 / 01/01/1900 -6,243.72
2610 P4 IN7411 / 01/01/1900 -5,424.39
2610 P4 LK8404 / 01/01/1900 -1,031.77
2610 P4 MEA004 / 01/01/1900 -30.12
2610 P4 MM0005 / 01/01/1900 -3,356.44
2610 P4 PAC002 / 01/01/1900 -24,426.13
2610 P4 PAC006 / 01/01/1900 -9,230.34
2610 P4 PAC014 / 01/01/1900 -10,000.00
2610 P4 WW0175 / 01/01/1900 -12,201.22
2610 P4 WW0308 / 01/01/1900 -8,327.51
2610 P4 WW0309 / 01/01/1900 -190,231.66
2610 P4 XXP4 / 01/01/1900 12,592.00
2610 P4 YP401 / 01/01/1900 -15,781.26
2610 P4 YP4013 / 01/01/1900 -73,427.20
2610 P4 YP402 / 01/01/1900 -23,664.77
2610 P4 YP402 / 01/01/1900 -65,925.90
2610 P4 YP403 / 01/01/1900 -17,167.18
3410 P4 1273 YP401 0 02/07/2007 -24,817.50
3410 P4 1273 YP405 0 02/07/2007 -16,545.00
3410 P4 1273 YP407 0 02/07/2007 -82,725.00
3410 P4 1273 ASA035 0 02/07/2007 -13,236.00
3410 P4 1273 MD0001 0 02/07/2007 -33,090.00
3410 P4 1273 BR7401 0 02/07/2007 -26,472.00
3410 P4 1273 AFR009 0 02/07/2007 -66,180.00
3410 P4 1273 ASA004 0 02/07/2007 -33,090.00
3410 P4 1108 AFR026 0 12/06/2007 -13,280.25
3410 P4 1108 AM9410 0 12/06/2007 -6,640.13
3410 P4 1108 CAR003 0 12/06/2007 -17,715.85
3410 P4 1108 LAM014 0 12/06/2007 -1,328.03
3410 P4 1108 LAM043 0 12/06/2007 -1,770.70
3410 P4 1108 LAM052 0 12/06/2007 -6,197.45
3410 P4 1108 LAM053 0 12/06/2007 -4,426.75
3410 P4 1108 LAM055 0 12/06/2007 -4,338.22
3410 P4 1108 AR0002 0 12/06/2007 -6,197.45
3410 P4 1108 BR7401 0 12/06/2007 -7,968.15
3410 P4 1108 BR7503 0 12/06/2007 -8,047.83
3410 P4 1108 PE0006 0 12/06/2007 -1,770.70
3410 P4 1108 SV8901 0 12/06/2007 -2,213.36
3410 P4 1108 EUR016 0 12/06/2007 -6,640.13
3410 P4 1044 YYP4 0 03/07/2007 -25,000.00
3410 P4 1290 YP407 0 03/07/2007 -100,000.00
3410 P4 1368 YP406 0 27/06/2007 -40,045.50
3410 P4 1368 YP407 0 27/06/2007 -22,247.50
3410 P4 2122 P4053A 0 19/06/2007 -5,789.25
3410 P4 1079 YYP4 0 22/06/2007 -8,185.41
3410 P4 1295 YP407 0 13/07/2007 -199,068.00
3410 P4 1295 YP401 0 16/07/2007 -331,400.00
3410 P4 1295 YP402 0 16/07/2007 -165,700.00
3410 P4 1295 YP403 0 16/07/2007 -165,700.00
3410 P4 1295 YP405 0 16/07/2007 -66,280.00
3410 P4 2096 YP406 0 16/07/2007 -66,280.00
3410 P4 2096 YP404 0 16/07/2007 -66,280.00
3410 P4 2096 YP405 0 16/07/2007 -66,280.00
3410 P4 1108 EUR012 0 16/07/2007 -32,551.20
3410 P4 1108 LAM015 0 16/07/2007 -27,126.00
3410 P4 1108 MEA006 0 16/07/2007 -28,482.30
3410 P4 1108 YP403 0 16/07/2007 -18,084.00
3410 P4 1108 YP405 0 16/07/2007 -9,042.00
3410 P4 1108 P4053A 0 16/07/2007 -13,563.00
3410 P4 1108 YP406 0 16/07/2007 -13,563.00
3410 P4 1108 YP407 0 16/07/2007 -18,084.00
3410 P4 1108 AFR026 0 16/07/2007 -6,781.50
3410 P4 1108 CAR003 0 16/07/2007 -9,046.52
3410 P4 1108 LAM014 0 16/07/2007 -678.15
3410 P4 1108 LAM043 0 16/07/2007 -904.20
3410 P4 1108 LAM052 0 16/07/2007 -3,164.70
3410 P4 1108 LAM053 0 16/07/2007 -2,260.50
3410 P4 1108 LAM055 0 16/07/2007 -2,215.29
3410 P4 1108 AR0002 0 16/07/2007 -3,164.70
3410 P4 1108 BR7401 0 16/07/2007 -4,068.90
3410 P4 1108 BR7503 0 16/07/2007 -4,109.59
3410 P4 1108 PE0006 0 16/07/2007 -904.20
3410 P4 1108 SV8901 0 16/07/2007 -1,130.25
3410 P4 1178 YYP4 0 11/07/2007 -206,750.00
3410 P4 4045 YP4011 0 17/07/2007 -29,833.20
3410 P4 4045 P4062A 0 17/07/2007 -21,546.20
3410 P4 4045 AFR008 0 17/07/2007 -36,462.80
3410 P4 4045 ASA037 0 17/07/2007 -31,490.60
3410 P4 4045 CAR000 0 17/07/2007 -11,601.80
3410 P4 4045 EUR016 0 17/07/2007 -8,287.00
3410 P4 4045 LAM014 0 17/07/2007 -21,546.20
3410 P4 4045 MEA004 0 17/07/2007 -14,916.60
3410 P4 4045 LAM015 0 17/07/2007 -16,574.00
3410 P4 4045 MEA006 0 17/07/2007 -8,287.00
3410 P4 1079 YYP4 0 22/06/2007 -8,185.41
3410 P4 1295 YP407 0 13/07/2007 -199,068.00
3410 P4 1295 YP401 0 16/07/2007 -331,400.00
3410 P4 1295 YP402 0 16/07/2007 -165,700.00
3410 P4 1295 YP403 0 16/07/2007 -165,700.00
3410 P4 1295 YP405 0 16/07/2007 -66,280.00
3410 P4 2096 YP406 0 16/07/2007 -66,280.00
3410 P4 2096 YP404 0 16/07/2007 -66,280.00
3410 P4 2096 YP405 0 16/07/2007 -66,280.00
3410 P4 1108 EUR012 0 16/07/2007 -32,551.20
3410 P4 1108 LAM015 0 16/07/2007 -27,126.00
3410 P4 1108 MEA006 0 16/07/2007 -28,482.30
3410 P4 1108 YP403 0 16/07/2007 -18,084.00
3410 P4 1108 YP405 0 16/07/2007 -9,042.00
3410 P4 1108 P4053A 0 16/07/2007 -13,563.00
3410 P4 1108 YP406 0 16/07/2007 -13,563.00

Only a few were necessary.and wat should be your o/p?

ishaame
Starting Member

12 Posts

Posted - 2008-05-08 : 03:10:08

sorry, but I didn't (and still don't) understand what is o/p !
In fact your suggestion of using CASE solved my previous problem. Now I am working back to my original problem, which is linking this table to another. I will keep you informed of the outcome later in the day

Thanks again for the help
IShaame

ishaame
Starting Member

12 Posts

Posted - 2008-05-08 : 11:33:09

Here is my real problem which I have been struggling with unsuccessfully:
There are two tables: 1- TR_1 with budget and actual amounts recorded as AmountLCU (only in one local currency)
2- RE_Donor_Gifts with all contributions recorded in different currencies with equivalent of local currency. There are three common fields: donor,activity,costcenter
Fields not common: Currency code (only in table 2), Account (only in table 1)

The required report should: report by Cost Center, budget (all in local currency), actuals (from table TR_1 in local currency) and from table RE_Donor_Gift in and foreign currency, grouped by account,donor,activity,and currency. The third table is just to obtain descriptions of fields in TR_1.
Here is my sql which gives me wrong figures:
********************************************************
SELECT DISTINCT (CASE WHEN "TR_1"."TTYPE" = 'U' THEN "TR_1"."AmountLCU" ELSE 0 END) AS "Budget",(CASE WHEN "TR_1"."TTYPE" <> 'U' THEN "TR_1"."AmountLCU" ELSE 0 END) AS "Actual","TR_1"."ACCOUNT", "TR_1"."COSTCENTER", "TR_1"."DONOR", "TR_1"."ACTIVITY", "TR_1"."TTYPE", "TR_1"."AmountLCU", "TR_1"."TransactionDate", "GL_03_1"."Name", "GL_03_2"."CostCentre", "GL_03_2"."Description1", "GL_03_2"."Description2", "GL_03_1"."RecordType", "GL_03_1"."CostCentre", "GL_03_2"."RecordType", "RE_Donor_Gift"."SYMBOL", "RE_Donor_Gift"."DONOR", "RE_Donor_Gift"."DTE", "RE_Donor_Gift"."FOREIGN_AMOUNT", "RE_Donor_Gift"."ACT"
FROM (("scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1" INNER JOIN "scalaDB"."dbo"."A_GL0301_PREVIOUS" "GL_03_1" ON "TR_1"."ACTIVITY"="GL_03_1"."CostCentre") LEFT OUTER JOIN "scalaDB"."dbo"."A_GL0301_PREVIOUS" "GL_03_2" ON "TR_1"."DONOR"="GL_03_2"."CostCentre") LEFT OUTER JOIN "scalaDB"."dbo"."RE_Donor_Gift" "RE_Donor_Gift" ON (("TR_1"."DONOR"="RE_Donor_Gift"."DONOR") AND ("TR_1"."ACTIVITY"="RE_Donor_Gift"."ACT")) AND (YEAR("TR_1"."TransactionDate")=YEAR("RE_Donor_Gift"."DTE"))
WHERE NOT ("TR_1"."TTYPE"='\' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
*****************************************************************************************
The result is that I get is that values are repeated as many times and get wrong totals of course.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 11:48:59

quote:
Originally posted by ishaame

Here is my real problem which I have been struggling with unsuccessfully:
There are two tables: 1- TR_1 with budget and actual amounts recorded as AmountLCU (only in one local currency)
2- RE_Donor_Gifts with all contributions recorded in different currencies with equivalent of local currency. There are three common fields: donor,activity,costcenter
Fields not common: Currency code (only in table 2), Account (only in table 1)

The required report should: report by Cost Center, budget (all in local currency), actuals (from table TR_1 in local currency) and from table RE_Donor_Gift in and foreign currency, grouped by account,donor,activity,and currency. The third table is just to obtain descriptions of fields in TR_1.
Here is my sql which gives me wrong figures:
********************************************************
SELECT DISTINCT (CASE WHEN "TR_1"."TTYPE" = 'U' THEN "TR_1"."AmountLCU" ELSE 0 END) AS "Budget",(CASE WHEN "TR_1"."TTYPE" <> 'U' THEN "TR_1"."AmountLCU" ELSE 0 END) AS "Actual","TR_1"."ACCOUNT", "TR_1"."COSTCENTER", "TR_1"."DONOR", "TR_1"."ACTIVITY", "TR_1"."TTYPE", "TR_1"."AmountLCU", "TR_1"."TransactionDate", "GL_03_1"."Name", "GL_03_2"."CostCentre", "GL_03_2"."Description1", "GL_03_2"."Description2", "GL_03_1"."RecordType", "GL_03_1"."CostCentre", "GL_03_2"."RecordType", "RE_Donor_Gift"."SYMBOL", "RE_Donor_Gift"."DONOR", "RE_Donor_Gift"."DTE", "RE_Donor_Gift"."FOREIGN_AMOUNT", "RE_Donor_Gift"."ACT"
FROM (("scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1" INNER JOIN "scalaDB"."dbo"."A_GL0301_PREVIOUS" "GL_03_1" ON "TR_1"."ACTIVITY"="GL_03_1"."CostCentre") LEFT OUTER JOIN "scalaDB"."dbo"."A_GL0301_PREVIOUS" "GL_03_2" ON "TR_1"."DONOR"="GL_03_2"."CostCentre") LEFT OUTER JOIN "scalaDB"."dbo"."RE_Donor_Gift" "RE_Donor_Gift" ON (("TR_1"."DONOR"="RE_Donor_Gift"."DONOR") AND ("TR_1"."ACTIVITY"="RE_Donor_Gift"."ACT")) AND (YEAR("TR_1"."TransactionDate")=YEAR("RE_Donor_Gift"."DTE"))
WHERE NOT ("TR_1"."TTYPE"='\' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
*****************************************************************************************
The result is that I get is that values are repeated as many times and get wrong totals of course.

To be honest i've never seen such a messy SQL script. Why are you using "" in all places? It makes it really hard to read & understand the code. Can you please post what you are expecting as your result values from the below sample data

ACCOUNT COSTCENTER DONOR ACTIVITY TTYPE TransactionDate AmountLCU
3410 P4 1273 P402RB U 31/01/2007 -2,895.00
3410 P4 1273 P402RB U 28/02/2007 -2,895.00
3410 P4 1273 P402RB U 31/03/2007 -2,895.00
3410 P4 1273 P402RB U 30/04/2007 -2,895.00
3410 P4 1273 P402RB U 31/05/2007 -2,895.00
3410 P4 1273 P402RB U 30/06/2007 -2,895.00
3410 P4 1273 P402RB U 31/07/2007 -2,895.00
3410 P4 1273 P402RB U 31/08/2007 -2,895.00
3410 P4 1273 P402RB U 30/09/2007 -2,895.00
3410 P4 1273 P402RB U 31/10/2007 -2,895.00
3410 P4 1273 P402RB U 30/11/2007 -2,895.00
3410 P4 1273 P402RB U 29/12/2007 -2,895.00
3410 P4 1108 P402RC U 31/01/2007 -2,125.00
3410 P4 1108 P402RC U 28/02/2007 -2,125.00
3410 P4 1108 P402RC U 31/03/2007 -2,125.00
3410 P4 1108 P402RC U 30/04/2007 -2,125.00
3410 P4 1108 P402RC U 31/05/2007 -2,125.00
3410 P4 1108 P402RC U 30/06/2007 -2,125.00
3410 P4 1108 P402RC U 31/07/2007 -2,125.00
3410 P4 1108 P402RC U 31/08/2007 -2,125.00
3410 P4 1108 P402RC U 30/09/2007 -2,125.00
3410 P4 1108 P402RC U 31/10/2007 -2,125.00
3410 P4 1108 P402RC U 30/11/2007 -2,125.00
3410 P4 1108 P402RC U 29/12/2007 -2,125.00
3410 P4 1108 P4053A U 31/01/2007 -4,250.00
3410 P4 1108 P4053A U 28/02/2007 -4,250.00
3410 P4 1108 P4053A U 31/03/2007 -4,250.00
3410 P4 1108 P4053A U 30/04/2007 -4,250.00
3410 P4 1108 P4053A U 31/05/2007 -4,250.00
3410 P4 1108 P4053A U 30/06/2007 -4,250.00
3410 P4 1108 P4053A U 31/07/2007 -4,250.00
3410 P4 1108 P4053A U 31/08/2007 -4,250.00
3410 P4 1108 P4053A U 30/09/2007 -4,250.00
3410 P4 1108 P4053A U 31/10/2007 -4,250.00
3410 P4 1108 P4053A U 30/11/2007 -4,250.00
3410 P4 1108 P4053A U 29/12/2007 -4,250.00
3410 P4 1009 YP403 U 31/01/2007 -1,475.00
3410 P4 1009 YP403 U 28/02/2007 -1,475.00
3410 P4 1009 YP403 U 31/03/2007 -1,475.00
3410 P4 1009 YP403 U 30/04/2007 -1,475.00
3410 P4 1009 YP403 U 31/05/2007 -1,475.00
3410 P4 1009 YP403 U 30/06/2007 -1,475.00
3410 P4 1009 YP403 U 31/07/2007 -1,475.00
3410 P4 1009 YP403 U 31/08/2007 -1,475.00
3410 P4 1009 YP403 U 30/09/2007 -1,475.00
3410 P4 1009 YP403 U 31/10/2007 -1,475.00
3410 P4 1009 YP403 U 30/11/2007 -1,475.00
3410 P4 1009 YP403 U 29/12/2007 -1,475.00
3410 P4 1009 YYP4 U 31/01/2007 -1,475.00
2610 P4 P4014A / 01/01/1900 -21,314.73
2610 P4 P4014B / 01/01/1900 -41,314.70
2610 P4 P4014C / 01/01/1900 -44,879.00
2610 P4 YP404 / 01/01/1900 -18,653.02
2610 P4 P4062C / 01/01/1900 -24,396.25
2610 P4 P4071B / 01/01/1900 -2,237.84
2610 P4 XXP4 / 01/01/1900 9,026.00
2610 P4 YP407 / 01/01/1900 -119,862.38
2610 P4 AFR008 / 01/01/1900 -2,730.11
2610 P4 ASA003 / 01/01/1900 -2,417.57
2610 P4 ASA025 / 01/01/1900 -13,532.39
2610 P4 ASA034 / 01/01/1900 -7,792.19
2610 P4 ASA037 / 01/01/1900 -1,919.61
2610 P4 BD7504 / 01/01/1900 -6,523.87
2610 P4 CAR000 / 01/01/1900 -452.65
2610 P4 CAR003 / 01/01/1900 -101.56
2610 P4 GD0004 / 01/01/1900 -339.57
2610 P4 ID0003 / 01/01/1900 -6,243.72
2610 P4 IN7411 / 01/01/1900 -5,424.39
2610 P4 LK8404 / 01/01/1900 -1,031.77
2610 P4 MEA004 / 01/01/1900 -30.12
2610 P4 MM0005 / 01/01/1900 -3,356.44
2610 P4 PAC002 / 01/01/1900 -24,426.13
2610 P4 PAC006 / 01/01/1900 -9,230.34
2610 P4 PAC014 / 01/01/1900 -10,000.00
2610 P4 WW0175 / 01/01/1900 -12,201.22
2610 P4 WW0308 / 01/01/1900 -8,327.51
2610 P4 WW0309 / 01/01/1900 -190,231.66
2610 P4 XXP4 / 01/01/1900 12,592.00
2610 P4 YP401 / 01/01/1900 -15,781.26
2610 P4 YP4013 / 01/01/1900 -73,427.20
2610 P4 YP402 / 01/01/1900 -23,664.77
2610 P4 YP402 / 01/01/1900 -65,925.90
2610 P4 YP403 / 01/01/1900 -17,167.18
3410 P4 1273 YP401 0 02/07/2007 -24,817.50
3410 P4 1273 YP405 0 02/07/2007 -16,545.00
3410 P4 1273 YP407 0 02/07/2007 -82,725.00
3410 P4 1273 ASA035 0 02/07/2007 -13,236.00
3410 P4 1273 MD0001 0 02/07/2007 -33,090.00
3410 P4 1273 BR7401 0 02/07/2007 -26,472.00
3410 P4 1273 AFR009 0 02/07/2007 -66,180.00
3410 P4 1273 ASA004 0 02/07/2007 -33,090.00
3410 P4 1108 AFR026 0 12/06/2007 -13,280.25
3410 P4 1108 AM9410 0 12/06/2007 -6,640.13
3410 P4 1108 CAR003 0 12/06/2007 -17,715.85
3410 P4 1108 LAM014 0 12/06/2007 -1,328.03
3410 P4 1108 LAM043 0 12/06/2007 -1,770.70
3410 P4 1108 LAM052 0 12/06/2007 -6,197.45
3410 P4 1108 LAM053 0 12/06/2007 -4,426.75
3410 P4 1108 LAM055 0 12/06/2007 -4,338.22
3410 P4 1108 AR0002 0 12/06/2007 -6,197.45
3410 P4 1108 BR7401 0 12/06/2007 -7,968.15
3410 P4 1108 BR7503 0 12/06/2007 -8,047.83
3410 P4 1108 PE0006 0 12/06/2007 -1,770.70
3410 P4 1108 SV8901 0 12/06/2007 -2,213.36
3410 P4 1108 EUR016 0 12/06/2007 -6,640.13
3410 P4 1044 YYP4 0 03/07/2007 -25,000.00
3410 P4 1290 YP407 0 03/07/2007 -100,000.00
3410 P4 1368 YP406 0 27/06/2007 -40,045.50
3410 P4 1368 YP407 0 27/06/2007 -22,247.50
3410 P4 2122 P4053A 0 19/06/2007 -5,789.25
3410 P4 1079 YYP4 0 22/06/2007 -8,185.41
3410 P4 1295 YP407 0 13/07/2007 -199,068.00
3410 P4 1295 YP401 0 16/07/2007 -331,400.00
3410 P4 1295 YP402 0 16/07/2007 -165,700.00
3410 P4 1295 YP403 0 16/07/2007 -165,700.00
3410 P4 1295 YP405 0 16/07/2007 -66,280.00
3410 P4 2096 YP406 0 16/07/2007 -66,280.00
3410 P4 2096 YP404 0 16/07/2007 -66,280.00
3410 P4 2096 YP405 0 16/07/2007 -66,280.00
3410 P4 1108 EUR012 0 16/07/2007 -32,551.20
3410 P4 1108 LAM015 0 16/07/2007 -27,126.00
3410 P4 1108 MEA006 0 16/07/2007 -28,482.30
3410 P4 1108 YP403 0 16/07/2007 -18,084.00
3410 P4 1108 YP405 0 16/07/2007 -9,042.00
3410 P4 1108 P4053A 0 16/07/2007 -13,563.00
3410 P4 1108 YP406 0 16/07/2007 -13,563.00
3410 P4 1108 YP407 0 16/07/2007 -18,084.00
3410 P4 1108 AFR026 0 16/07/2007 -6,781.50
3410 P4 1108 CAR003 0 16/07/2007 -9,046.52
3410 P4 1108 LAM014 0 16/07/2007 -678.15
3410 P4 1108 LAM043 0 16/07/2007 -904.20
3410 P4 1108 LAM052 0 16/07/2007 -3,164.70
3410 P4 1108 LAM053 0 16/07/2007 -2,260.50
3410 P4 1108 LAM055 0 16/07/2007 -2,215.29
3410 P4 1108 AR0002 0 16/07/2007 -3,164.70
3410 P4 1108 BR7401 0 16/07/2007 -4,068.90
3410 P4 1108 BR7503 0 16/07/2007 -4,109.59
3410 P4 1108 PE0006 0 16/07/2007 -904.20
3410 P4 1108 SV8901 0 16/07/2007 -1,130.25
3410 P4 1178 YYP4 0 11/07/2007 -206,750.00
3410 P4 4045 YP4011 0 17/07/2007 -29,833.20
3410 P4 4045 P4062A 0 17/07/2007 -21,546.20
3410 P4 4045 AFR008 0 17/07/2007 -36,462.80
3410 P4 4045 ASA037 0 17/07/2007 -31,490.60
3410 P4 4045 CAR000 0 17/07/2007 -11,601.80
3410 P4 4045 EUR016 0 17/07/2007 -8,287.00
3410 P4 4045 LAM014 0 17/07/2007 -21,546.20
3410 P4 4045 MEA004 0 17/07/2007 -14,916.60
3410 P4 4045 LAM015 0 17/07/2007 -16,574.00
3410 P4 4045 MEA006 0 17/07/2007 -8,287.00
3410 P4 1079 YYP4 0 22/06/2007 -8,185.41
3410 P4 1295 YP407 0 13/07/2007 -199,068.00
3410 P4 1295 YP401 0 16/07/2007 -331,400.00
3410 P4 1295 YP402 0 16/07/2007 -165,700.00
3410 P4 1295 YP403 0 16/07/2007 -165,700.00
3410 P4 1295 YP405 0 16/07/2007 -66,280.00
3410 P4 2096 YP406 0 16/07/2007 -66,280.00
3410 P4 2096 YP404 0 16/07/2007 -66,280.00
3410 P4 2096 YP405 0 16/07/2007 -66,280.00
3410 P4 1108 EUR012 0 16/07/2007 -32,551.20
3410 P4 1108 LAM015 0 16/07/2007 -27,126.00
3410 P4 1108 MEA006 0 16/07/2007 -28,482.30
3410 P4 1108 YP403 0 16/07/2007 -18,084.00
3410 P4 1108 YP405 0 16/07/2007 -9,042.00
3410 P4 1108 P4053A 0 16/07/2007 -13,563.00
3410 P4 1108 YP406 0 16/07/2007 -13,563.00

ishaame
Starting Member

12 Posts

Posted - 2008-05-08 : 12:31:45

To be frank, I also find it very messy. But I am quite new in using SQL, and that is the format that was originally proposed to me by Crystal Reports following my the building of the query using it's query wizard. So I kept it.
The expected end result would look like this:
**********************************************************************
Cost Centre C1 2007

CHF Original Original CHF
Account Donor Activity Budget Curr Amount Actual
in CHF in CHF
Opening Balances
2610 C1017A 49,620.00 0.00 28,673.59
2610 XXC1 (127,740.00) 0.00 (55,275.00)
(78,120.00) (26,601.41)

Total Opening Balances (78,120.00) (26,601.41)
Programme Contributions
3410 4079 C1014A 0.00 CHF 50.00 50.00
3410 9093 C1014F 0.00 USD 100.00 124.19
3410 9105 C1014F 0.00 CHF 7.50 7.50
3410 1053 YYC1 0.00 GBP 40,000.00 95,834.00
3410 1108 YYC1 204,000.00 SEK 1,800,000.00 212,697.00
3410 1157 YYC1 0.00 CHF 44,101.24 44,101.24
3410 1178 YYC1 772,000.00 EUR 750,000.00 821,425.00
3410 1273 YYC1 123,520.00 EUR 80,000.00 130,871.50
3410 2093 YYC1 0.00 USD 15,000.00 11,890.50
3410 2096 YYC1 0.00 EUR 90,000.00 49,710.00
3410 2122 YYC1 53,300.00 CAD 50,000.00 57,457.49
1,152,820.00 1,424,168.42

Other Income
37 C1013A 0.00 0.00 1,498.82
37 C1014A 0.00 0.00 29,981.58
37 C1014C 0.00 0.00 23,063.92
37 C1014D 0.00 0.00 16,053.51
37 C1014E 0.00 0.00 7,688.82
37 C1014F 0.00 0.00 63,930.48
37 C1014G 0.00 0.00 111.53
37 C1014H 0.00 0.00 4,204.96
37 C1014N 0.00 0.00 21,600.01
37 C1014O 0.00 0.00 13,751.62
37 C1014P 0.00 0.00 22,601.94
37 C1014Q 0.00 0.00 22,320.78
37 YYC1 0.00 0.00 407.55
39 C1011B 0.00 0.00 720.55
39 C1014C 0.00 0.00 500.51
39 C1014D 0.00 0.00 289.21
39 C1014F 0.00 0.00 3,370.37
39 C1014G 0.00 0.00 53.70
39 C1014Q 0.00 0.00 1,000.00
39 YC101 0.00 0.00 154.70
39 YYC1 400,000.00 0.00 19,060.78
400,000.00 252,365.34

Undesignated Income
1,892,381.00 0.00 1,877,077.42
1,892,381.00 1,877,077.42

Total Income 3,445,201.00 3,553,611.18
Direct Costs
40 470,000.00 0.00 504,414.00
41 0.00 0.00 103,704.38
41A 10,000.00 0.00 21,291.62
41B 59,616.00 0.00 2,558.71
43 0.00 0.00 7,437.45
45 0.00 0.00 84,535.00
46 138,734.00 0.00 21,491.96
47 286,704.00 0.00 340,638.49
48 0.00 0.00 50.00
49 0.00 0.00 3,100.80
50/1 1,853,090.00 0.00 1,697,970.77
52-59 0.00 0.00 31,020.82
2,818,144.00 2,818,214.00

Allocated Costs
6800 701,676.00 0.00 716,034.22
701,676.00 716,034.22

Total Expenses 3,519,820.00 3,534,248.22
Net Transfers
73 0.00 0.00 14,372.00
74 25,000.00 0.00 (3,844.62)
25,000.00 10,527.38

Total Transfers 25,000.00 10,527.38
Closing Balance (127,739.00) 3,288.93

*******************************************************************

But what I get is
COSTCENTER
ACCOUNT ACTIVITY DONOR in CHF Original Original Actual
Budget Curr Amount
2610 C1014H 0.00 0.00
2610 C1017A -49,620.00 -28,673.59
2610 XXC1 127,740.00 55,275.00
78,120.00 26,601.41
1. Total Opening Balances 78,120.00 26,601.41
3410 C1014A 4079 0.00 CHF 50.00 -50.00
3410 C1014F 9093 0.00 USD 100.00 -124.19
3410 C1014F 9105 0.00 CHF 7.50 -7.50
3410 YYC1 0.00 0.00
3410 YYC1 1053 0.00 GBP 40,000.00 -95,834.00
3410 YYC1 1108 -816,000.00 SEK 20,400,000.00 -633,780.00
3410 YYC1 1157 0.00 CHF 88,202.48 -88,202.48
3410 YYC1 1178 -3,087,999.84 EUR 8,500,000.00 -2,458,700.00
3410 YYC1 1273 -247,039.92 EUR 1,120,000.00 -261,743.00
3410 YYC1 2093 0.00 USD 20,000.00 -17,710.50
3410 YYC1 2096 0.00 EUR 60,000.00 0.00
3410 YYC1 2122 -159,900.12 CAD 750,000.00 -172,372.47
-4,310,939.88 -3,728,524.14

37 C1013A 0.00 -1,498.82
37 C1014A 0.00 -29,465.57
37 C1014C 0.00 -22,845.85
37 C1014D 0.00 -15,807.00
37 C1014E 0.00 -7,688.82
37 C1014F 0.00 -63,912.48
37 C1014G 0.00 -111.53
37 C1014H 0.00 -4,161.43
37 C1014N 0.00 -21,600.01
37 C1014O 0.00 -13,710.95
37 C1014P 0.00 -22,403.55
37 C1014Q 0.00 -22,320.78
37 YYC1 0.00 -407.55
39 C1011B 0.00 -720.55
39 C1014C 0.00 -500.51
39 C1014D 0.00 -289.21
39 C1014F 0.00 -3,370.37
39 C1014G 0.00 -53.70
39 C1014Q 0.00 -1,000.00
39 YC101 0.00 -154.70
39 YYC1 -400,000.00 -19,060.78
-400,000.00 -251,084.16

YYC1 -1,892,381.00 -1,877,077.42
-1,877,077.42
2. Total Income -6,603,320.88 -5,856,685.72

40 YYC1 470,000.04 504,414.00
41 C1014F 0.00 103,704.38
41A C1017A 9,999.96 21,291.62
41B C1017A 59,616.04 2,558.71
43 ZZC1 0.00 7,437.45
45 C1014C 0.00 84,346.65
46 ZZC1 138,734.04 20,777.16
47 C1014A 286,704.00 340,809.59
48 C1013A 0.00 50.00
49 C1013A 0.00 3,078.10
50/1 C1011A 1,853,089.92 1,697,585.77
52-59 C1014A 0.00 31,020.82
2,818,144.00 2,817,074.25

6100 YC101 0.00 0.00
6800 ZZC1 701,676.00 649,969.67
701,676.00 649,969.67

3,519,820.00 3,467,043.92
73 C1017A 0.00 -14,372.00
74 YYC1 -25,000.04 3,844.62
-25,000.04 -10,527.38
4. Total Transfers -25,000.04 -10,527.38
-3,030,380.92 -2,373,567.77

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 12:56:21

quote:
Originally posted by ishaame

To be frank, I also find it very messy. But I am quite new in using SQL, and that is the format that was originally proposed to me by Crystal Reports following my the building of the query using it's query wizard. So I kept it.
The expected end result would look like this:
**********************************************************************
Cost Centre C1 2007

CHF Original Original CHF
Account Donor Activity Budget Curr Amount Actual
in CHF in CHF
Opening Balances
2610 C1017A 49,620.00 0.00 28,673.59
2610 XXC1 (127,740.00) 0.00 (55,275.00)
(78,120.00) (26,601.41)

Total Opening Balances (78,120.00) (26,601.41)
Programme Contributions
3410 4079 C1014A 0.00 CHF 50.00 50.00
3410 9093 C1014F 0.00 USD 100.00 124.19
3410 9105 C1014F 0.00 CHF 7.50 7.50
3410 1053 YYC1 0.00 GBP 40,000.00 95,834.00
3410 1108 YYC1 204,000.00 SEK 1,800,000.00 212,697.00
3410 1157 YYC1 0.00 CHF 44,101.24 44,101.24
3410 1178 YYC1 772,000.00 EUR 750,000.00 821,425.00
3410 1273 YYC1 123,520.00 EUR 80,000.00 130,871.50
3410 2093 YYC1 0.00 USD 15,000.00 11,890.50
3410 2096 YYC1 0.00 EUR 90,000.00 49,710.00
3410 2122 YYC1 53,300.00 CAD 50,000.00 57,457.49
1,152,820.00 1,424,168.42

Other Income
37 C1013A 0.00 0.00 1,498.82
37 C1014A 0.00 0.00 29,981.58
37 C1014C 0.00 0.00 23,063.92
37 C1014D 0.00 0.00 16,053.51
37 C1014E 0.00 0.00 7,688.82
37 C1014F 0.00 0.00 63,930.48
37 C1014G 0.00 0.00 111.53
37 C1014H 0.00 0.00 4,204.96
37 C1014N 0.00 0.00 21,600.01
37 C1014O 0.00 0.00 13,751.62
37 C1014P 0.00 0.00 22,601.94
37 C1014Q 0.00 0.00 22,320.78
37 YYC1 0.00 0.00 407.55
39 C1011B 0.00 0.00 720.55
39 C1014C 0.00 0.00 500.51
39 C1014D 0.00 0.00 289.21
39 C1014F 0.00 0.00 3,370.37
39 C1014G 0.00 0.00 53.70
39 C1014Q 0.00 0.00 1,000.00
39 YC101 0.00 0.00 154.70
39 YYC1 400,000.00 0.00 19,060.78
400,000.00 252,365.34

Undesignated Income
1,892,381.00 0.00 1,877,077.42
1,892,381.00 1,877,077.42

Total Income 3,445,201.00 3,553,611.18
Direct Costs
40 470,000.00 0.00 504,414.00
41 0.00 0.00 103,704.38
41A 10,000.00 0.00 21,291.62
41B 59,616.00 0.00 2,558.71
43 0.00 0.00 7,437.45
45 0.00 0.00 84,535.00
46 138,734.00 0.00 21,491.96
47 286,704.00 0.00 340,638.49
48 0.00 0.00 50.00
49 0.00 0.00 3,100.80
50/1 1,853,090.00 0.00 1,697,970.77
52-59 0.00 0.00 31,020.82
2,818,144.00 2,818,214.00

Allocated Costs
6800 701,676.00 0.00 716,034.22
701,676.00 716,034.22

Total Expenses 3,519,820.00 3,534,248.22
Net Transfers
73 0.00 0.00 14,372.00
74 25,000.00 0.00 (3,844.62)
25,000.00 10,527.38

Total Transfers 25,000.00 10,527.38
Closing Balance (127,739.00) 3,288.93

*******************************************************************

But what I get is
COSTCENTER
ACCOUNT ACTIVITY DONOR in CHF Original Original Actual
Budget Curr Amount
2610 C1014H 0.00 0.00
2610 C1017A -49,620.00 -28,673.59
2610 XXC1 127,740.00 55,275.00
78,120.00 26,601.41
1. Total Opening Balances 78,120.00 26,601.41
3410 C1014A 4079 0.00 CHF 50.00 -50.00
3410 C1014F 9093 0.00 USD 100.00 -124.19
3410 C1014F 9105 0.00 CHF 7.50 -7.50
3410 YYC1 0.00 0.00
3410 YYC1 1053 0.00 GBP 40,000.00 -95,834.00
3410 YYC1 1108 -816,000.00 SEK 20,400,000.00 -633,780.00
3410 YYC1 1157 0.00 CHF 88,202.48 -88,202.48
3410 YYC1 1178 -3,087,999.84 EUR 8,500,000.00 -2,458,700.00
3410 YYC1 1273 -247,039.92 EUR 1,120,000.00 -261,743.00
3410 YYC1 2093 0.00 USD 20,000.00 -17,710.50
3410 YYC1 2096 0.00 EUR 60,000.00 0.00
3410 YYC1 2122 -159,900.12 CAD 750,000.00 -172,372.47
-4,310,939.88 -3,728,524.14

37 C1013A 0.00 -1,498.82
37 C1014A 0.00 -29,465.57
37 C1014C 0.00 -22,845.85
37 C1014D 0.00 -15,807.00
37 C1014E 0.00 -7,688.82
37 C1014F 0.00 -63,912.48
37 C1014G 0.00 -111.53
37 C1014H 0.00 -4,161.43
37 C1014N 0.00 -21,600.01
37 C1014O 0.00 -13,710.95
37 C1014P 0.00 -22,403.55
37 C1014Q 0.00 -22,320.78
37 YYC1 0.00 -407.55
39 C1011B 0.00 -720.55
39 C1014C 0.00 -500.51
39 C1014D 0.00 -289.21
39 C1014F 0.00 -3,370.37
39 C1014G 0.00 -53.70
39 C1014Q 0.00 -1,000.00
39 YC101 0.00 -154.70
39 YYC1 -400,000.00 -19,060.78
-400,000.00 -251,084.16

YYC1 -1,892,381.00 -1,877,077.42
-1,877,077.42
2. Total Income -6,603,320.88 -5,856,685.72

40 YYC1 470,000.04 504,414.00
41 C1014F 0.00 103,704.38
41A C1017A 9,999.96 21,291.62
41B C1017A 59,616.04 2,558.71
43 ZZC1 0.00 7,437.45
45 C1014C 0.00 84,346.65
46 ZZC1 138,734.04 20,777.16
47 C1014A 286,704.00 340,809.59
48 C1013A 0.00 50.00
49 C1013A 0.00 3,078.10
50/1 C1011A 1,853,089.92 1,697,585.77
52-59 C1014A 0.00 31,020.82
2,818,144.00 2,817,074.25

6100 YC101 0.00 0.00
6800 ZZC1 701,676.00 649,969.67
701,676.00 649,969.67

3,519,820.00 3,467,043.92
73 C1017A 0.00 -14,372.00
74 YYC1 -25,000.04 3,844.62
-25,000.04 -10,527.38
4. Total Transfers -25,000.04 -10,527.38
-3,030,380.92 -2,373,567.77

Most of DONOR values given here does not exist among sample data provided before. Can you take a single category of records may be 5 or 10 and then explain what you result values expected out of them.

ishaame
Starting Member

12 Posts

Posted - 2008-05-13 : 06:06:28

Here is a sample data for the two tables. I have taken only two cost centres and three donors (1108,1178, and a null) and two from the second table, as in the second table we cannot a null donor. Not that in the table two "Foreign Amount is the actual amount paid in it original currency, Split Amount is it's equivalent in local (CHF) currency and is the amount recorded in the first table as amountLCU. The TTYPE in table 1 is the one which distinguishes Budget from actuals.
The output should be (by year and by Cost Centre):

Account¦ Donor¦ Budget(in CHF)¦ Curr(SYMBOL)¦ ForeignAmnt(Actual)¦ Actual(in CHF)¦

Table 1
******************************
ACCOUNT COSTCENTER DONOR ACTIVITY TTYPE TransactionDate AmountLCU
3410 A2 YA2 U 01/01/2007 -90,166.00
3410 A2 YA2 U 31/01/2007 -90,166.00
3410 A2 YA2 U 28/02/2007 -90,166.00
3410 A2 YA2 U 31/03/2007 -90,166.00
3410 A2 YA2 U 30/04/2007 -90,166.00
3410 A2 YA2 U 31/05/2007 -90,166.00
3410 A2 YA2 U 30/06/2007 -90,166.00
3410 A2 YA2 U 31/07/2007 -90,166.00
3410 A2 YA2 U 31/08/2007 -90,166.00
3410 A2 YA2 U 30/09/2007 -90,166.00
3410 A2 YA2 U 31/10/2007 -90,166.00
3410 A2 YA2 U 30/11/2007 -90,166.00
3410 A2 YA2 U 29/12/2007 -90,174.00
3410 A2 YA2 U 01/01/2007 45,083.00
3410 A2 YA2 U 01/01/2007 45,083.00
3410 A2 YA2 U 31/01/2007 45,083.00
3410 A2 YA2 U 28/02/2007 45,083.00
3410 A2 YA2 U 31/03/2007 45,083.00
3410 A2 YA2 U 30/04/2007 45,083.00
3410 A2 YA2 U 31/05/2007 45,083.00
3410 A2 YA2 U 30/06/2007 45,083.00
3410 A2 YA2 U 31/07/2007 45,083.00
3410 A2 YA2 U 31/08/2007 45,083.00
3410 A2 YA2 U 30/09/2007 45,083.00
3410 A2 YA2 U 31/10/2007 45,083.00
3410 A2 YA2 U 30/11/2007 45,083.00
3410 A2 YA2 U 29/12/2007 45,087.00
3410 C1 YYC1 U 31/01/2007 0.00
3410 C1 YYC1 U 28/02/2007 0.00
3410 C1 YYC1 U 31/03/2007 0.00
3410 C1 YYC1 U 30/04/2007 0.00
3410 C1 YYC1 U 31/05/2007 0.00
3410 C1 YYC1 U 30/06/2007 0.00
3410 C1 YYC1 U 31/07/2007 0.00
3410 C1 YYC1 U 31/08/2007 0.00
3410 C1 YYC1 U 30/09/2007 0.00
3410 C1 YYC1 U 31/10/2007 0.00
3410 C1 YYC1 U 30/11/2007 0.00
3410 C1 YYC1 U 29/12/2007 0.00
3410 C1 1108 YYC1 U 31/01/2007 -17,000.00
3410 C1 1108 YYC1 U 28/02/2007 -17,000.00
3410 C1 1108 YYC1 U 31/03/2007 -17,000.00
3410 C1 1108 YYC1 U 30/04/2007 -17,000.00
3410 C1 1108 YYC1 U 31/05/2007 -17,000.00
3410 C1 1108 YYC1 U 30/06/2007 -17,000.00
3410 C1 1108 YYC1 U 31/07/2007 -17,000.00
3410 C1 1108 YYC1 U 31/08/2007 -17,000.00
3410 C1 1108 YYC1 U 30/09/2007 -17,000.00
3410 C1 1108 YYC1 U 31/10/2007 -17,000.00
3410 C1 1108 YYC1 U 30/11/2007 -17,000.00
3410 C1 1108 YYC1 U 29/12/2007 -17,000.00
3410 C1 1178 YYC1 U 31/01/2007 -64,333.33
3410 C1 1178 YYC1 U 28/02/2007 -64,333.33
3410 C1 1178 YYC1 U 31/03/2007 -64,333.33
3410 C1 1178 YYC1 U 30/04/2007 -64,333.33
3410 C1 1178 YYC1 U 31/05/2007 -64,333.33
3410 C1 1178 YYC1 U 30/06/2007 -64,333.33
3410 C1 1178 YYC1 U 31/07/2007 -64,333.33
3410 C1 1178 YYC1 U 31/08/2007 -64,333.33
3410 C1 1178 YYC1 U 30/09/2007 -64,333.33
3410 C1 YYC1 U 31/01/2007 -17,000.00
3410 C1 YYC1 U 28/02/2007 -17,000.00
3410 C1 YYC1 U 31/03/2007 -17,000.00
3410 C1 YYC1 U 30/04/2007 -17,000.00
3410 C1 YYC1 U 31/05/2007 -17,000.00
3410 C1 YYC1 U 30/06/2007 -17,000.00
3410 C1 YYC1 U 31/07/2007 -17,000.00
3410 C1 YYC1 U 31/08/2007 -17,000.00
3410 C1 YYC1 U 30/09/2007 -17,000.00
3410 C1 YYC1 U 31/10/2007 -17,000.00
3410 C1 YYC1 U 30/11/2007 -17,000.00
3410 C1 YYC1 U 29/12/2007 -17,000.00
3410 C1 YYC1 U 31/01/2007 54,040.00
3410 C1 YYC1 U 28/02/2007 54,040.00
3410 C1 YYC1 U 31/03/2007 54,040.00
3410 C1 YYC1 U 30/04/2007 54,040.00
3410 C1 YYC1 U 31/05/2007 54,040.00
3410 C1 YYC1 U 30/06/2007 54,040.00
3410 C1 YYC1 U 31/07/2007 54,040.00
3410 C1 YYC1 U 31/08/2007 54,040.00
3410 C1 YYC1 U 30/09/2007 54,040.00
3410 C1 YYC1 U 31/10/2007 54,040.00
3410 C1 YYC1 U 30/11/2007 54,040.00
3410 C1 YYC1 U 29/12/2007 54,040.00
3410 C1 YYC1 U 31/01/2007 5,851.66
3410 C1 YYC1 U 28/02/2007 5,851.66
3410 C1 YYC1 U 31/03/2007 5,851.66
3410 C1 YYC1 U 30/04/2007 5,851.66
3410 C1 YYC1 U 31/05/2007 5,851.66
3410 C1 YYC1 U 30/06/2007 5,851.66
3410 C1 YYC1 U 31/07/2007 5,851.66
3410 C1 YYC1 U 31/08/2007 5,851.66
3410 C1 YYC1 U 30/09/2007 5,851.66
3410 C1 YYC1 U 31/10/2007 5,851.66
3410 C1 YYC1 U 30/11/2007 5,851.66
3410 C1 YYC1 U 29/12/2007 5,851.66
3410 C1 YYC1 U 31/01/2007 4,441.67
3410 C1 YYC1 U 28/02/2007 4,441.67
3410 C1 YYC1 U 31/03/2007 4,441.67
3410 C1 YYC1 U 30/04/2007 4,441.67
3410 C1 YYC1 U 31/05/2007 4,441.67
3410 C1 YYC1 U 30/06/2007 4,441.67
3410 C1 YYC1 U 31/07/2007 4,441.67
3410 C1 YYC1 U 31/08/2007 4,441.67
3410 C1 YYC1 U 30/09/2007 4,441.67
3410 C1 YYC1 U 31/10/2007 4,441.67
3410 C1 YYC1 U 30/11/2007 4,441.67
3410 C1 YYC1 U 29/12/2007 4,441.67
3410 C1 1273 YYC1 U 31/01/2007 -10,293.33
3410 C1 1273 YYC1 U 28/02/2007 -10,293.33
3410 C1 1273 YYC1 U 31/03/2007 -10,293.33
3410 C1 1273 YYC1 U 30/04/2007 -10,293.33
3410 C1 1273 YYC1 U 31/05/2007 -10,293.33
3410 C1 1273 YYC1 U 30/06/2007 -10,293.33
3410 C1 1273 YYC1 U 31/07/2007 -10,293.33
3410 C1 1273 YYC1 U 31/08/2007 -10,293.33
3410 C1 1273 YYC1 U 30/09/2007 -10,293.33
3410 C1 1273 YYC1 U 31/10/2007 -10,293.33
3410 C1 1273 YYC1 U 30/11/2007 -10,293.33
3410 C1 1273 YYC1 U 29/12/2007 -10,293.33
3410 C1 YYC1 U 31/01/2007 -47,333.33
3410 C1 YYC1 U 28/02/2007 -47,333.33
3410 C1 YYC1 U 31/03/2007 -47,333.33
3410 C1 YYC1 U 30/04/2007 -47,333.33
3410 C1 YYC1 U 31/05/2007 -47,333.33
3410 C1 YYC1 U 30/06/2007 -47,333.33
3410 C1 YYC1 U 31/07/2007 -47,333.33
3410 C1 YYC1 U 31/08/2007 -47,333.33
3410 C1 YYC1 U 30/09/2007 -47,333.33
3410 C1 YYC1 U 31/10/2007 -47,333.33
3410 C1 YYC1 U 30/11/2007 -47,333.33
3410 C1 YYC1 U 29/12/2007 -47,333.33
3410 C1 2122 YYC1 U 31/01/2007 -4,441.67
3410 C1 2122 YYC1 U 28/02/2007 -4,441.67
3410 C1 2122 YYC1 U 31/03/2007 -4,441.67
3410 C1 2122 YYC1 U 30/04/2007 -4,441.67
3410 C1 2122 YYC1 U 31/05/2007 -4,441.67
3410 C1 2122 YYC1 U 30/06/2007 -4,441.67
3410 C1 2122 YYC1 U 31/07/2007 -4,441.67
3410 C1 2122 YYC1 U 31/08/2007 -4,441.67
3410 C1 2122 YYC1 U 30/09/2007 -4,441.67
3410 C1 2122 YYC1 U 31/10/2007 -4,441.67
3410 C1 2122 YYC1 U 30/11/2007 -4,441.67
3410 C1 2122 YYC1 U 29/12/2007 -4,441.67
3410 C1 4079 C1014A 0 30/01/2007 -50.00
3410 C1 1178 YYC1 U 31/10/2007 -64,333.33
3410 C1 1178 YYC1 U 30/11/2007 -64,333.33
3410 C1 1178 YYC1 U 29/12/2007 -64,333.33
3410 C1 1178 YYC1 0 11/01/2007 -201,750.00
3410 A2 2122 YA2 0 15/03/2007 -1,554.23
3410 A2 9028 YA2 0 27/04/2007 -604.20
3410 A2 1047 YA2 0 04/05/2007 -4,206.50
3410 A2 9030 YA2 0 26/04/2007 -601.75
3410 C1 1108 YYC1 0 04/04/2007 -52,230.00
3410 A2 1108 YA2 0 04/04/2007 -8,705.00
3410 C1 1178 YYC1 0 10/04/2007 -204,750.00
3410 A2 9029 YA2 0 17/05/2007 -1,196.79
3410 A2 1296 YA2 0 18/04/2007 -2,068.46
3410 A2 9027 YA2 0 27/04/2007 -1,208.40
3410 A2 1108 YA2 0 04/04/2007 -1,221.90
3410 A2 9031 YA2 0 16/04/2007 -1,214.40
3410 C1 1108 YYC1 0 23/04/2007 -53,517.00
3410 A2 1108 YA2 0 23/04/2007 -8,919.50
3410 A2 4156 YA2 0 18/04/2007 -2,789.96
3410 A2 4232 YA2 0 27/04/2007 -5,101.86
3410 A2 1411 YA2 0 02/04/2007 -39,990.00
3410 A2 2114 YA2 0 16/05/2007 -21,457.00
3410 A2 9031 YA2 0 16/04/2007 607.20
3410 A2 9036 YA2 0 15/05/2007 -1,218.60
3410 A2 1516 YA2 0 17/05/2007 -558.67
3410 A2 9039 YA2 0 24/05/2007 -1,227.20
3410 A2 1344 YA2 0 25/05/2007 -614.15
3410 A2 9041 YA2 0 25/05/2007 -1,197.59
3410 A2 1102 YA2 0 01/06/2007 -1,213.64
3410 C1 2122 YYC1 0 09/05/2007 -18,342.50
3410 A2 1411 YA2 0 23/05/2007 -1,221.06
3410 A2 1043 YA2 0 29/05/2007 -24,498.00
3410 A2 1231 YA2 0 19/07/2007 -1,201.60
3410 C1 2096 YYC1 0 16/07/2007 -49,710.00
3410 A2 1108 YA2 0 16/07/2007 -9,042.00
3410 C1 1108 YYC1 4 16/07/2007 54,252.00
3410 C1 1178 YYC1 4 11/07/2007 206,750.00
3410 C1 1273 YYC1 0 02/07/2007 -24,817.50
3410 C1 1108 YYC1 0 16/07/2007 -54,252.00
3410 C1 1108 YYC1 0 16/07/2007 -54,252.00
3410 A2 1079 YA2 0 22/06/2007 -3,014.59
3410 C1 2096 YYC1 4 16/07/2007 49,710.00
3410 A2 1079 YA2 4 22/06/2007 3,014.59
3410 A2 1108 YA2 4 16/07/2007 9,042.00
3410 A2 9090 YA2 0 10/05/2007 -1,204.14
3410 C1 2096 YYC1 0 16/07/2007 -49,710.00
3410 A2 1108 YA2 0 16/07/2007 -9,042.00
3410 C1 1178 YYC1 0 11/07/2007 -206,750.00
3410 A2 1231 YA2 4 19/07/2007 1,201.60
3410 A2 1231 YA2 0 19/07/2007 -1,201.60
3410 C1 1178 YYC1 0 11/07/2007 -206,750.00
3410 A2 1079 YA2 0 22/06/2007 -3,014.59
3410 C1 9093 C1014F 0 18/06/2007 -124.19
3410 A2 1290 YA2 0 26/07/2007 -26,000.00
3410 C1 1157 YYC1 0 06/08/2007 -30,883.93
3410 A2 9096 YA2 0 14/08/2007 -1,160.00
3410 A2 1044 YA2 0 20/08/2007 -7,230.00
3410 C1 2093 YYC1 0 26/07/2007 -3,035.25
3410 A2 1290 YA2 0 30/07/2007 -1,207.00
3410 C1 2122 YYC1 0 21/08/2007 -18,977.50
3410 A2 2122 YA2 0 24/08/2007 -3,413.40
3410 A2 1411 YA2 0 02/08/2007 -20,654.00
3410 C1 1157 YYC1 0 10/09/2007 -13,217.31
3410 C1 1053 YYC1 0 14/09/2007 -95,834.00
3410 C1 2093 YYC1 0 22/10/2007 -5,820.00
3410 A2 1043 YA2 0 15/10/2007 -24,498.00
3410 C1 1178 YYC1 0 09/10/2007 -208,175.00
3410 C1 1273 YYC1 0 23/11/2007 -106,054.00
3410 A2 1273 YA2 0 23/11/2007 -32,632.00
3410 A2 1273 WW0355 0 23/11/2007 -26,105.60
3410 A2 1121 YA2 0 31/10/2007 -26,631.70
3410 C1 2122 YYC1 0 15/10/2007 -20,137.49
3410 A2 1157 YA2 0 21/11/2007 -36,652.55
3410 A2 1157 YA2 0 21/11/2007 -1,120.51
3410 A2 1411 YA2 0 03/12/2007 -20,413.00
3410 A2 1178 YA2 0 29/11/2007 -65,964.00
3410 C1 2093 YYC1 0 26/07/2007 -3,035.25
3410 C1 1108 YYC1 0 10/12/2007 -52,698.00
3410 A2 1108 YA2 0 10/12/2007 -8,783.00
3410 C1 9105 C1014F 0 15/11/2007 -7.50
3410 A2 1053 YA2 0 06/12/2007 -70,000.04
3410 A2 9123 YA2 0 02/10/2007 -1,159.66
3410 A2 9030 YA2 0 15/11/2007 -7,166.19
3410 A2 1273 YA2 0 23/11/2007 11,421.20
3410 A2 1273 WW0355 0 23/11/2007 -11,421.20
3410 A2 1108 WW0350 0 20/12/2007 -45,643.00
3410 A2 9907 YA2 U 31/01/2007 -45,083.00
3410 A2 9907 YA2 U 28/02/2007 -45,083.00
3410 A2 9907 YA2 U 31/03/2007 -45,083.00
3410 A2 9907 YA2 U 30/04/2007 -45,083.00
3410 A2 9907 YA2 U 31/05/2007 -45,083.00
3410 A2 9907 YA2 U 30/06/2007 -45,083.00
3410 A2 9907 YA2 U 31/07/2007 -45,083.00
3410 A2 9907 YA2 U 31/08/2007 -45,083.00
3410 A2 9907 YA2 U 30/09/2007 -45,083.00
3410 A2 9907 YA2 U 31/10/2007 -45,083.00
3410 A2 9907 YA2 U 30/11/2007 -45,083.00
3410 A2 9907 YA2 U 29/12/2007 -45,087.00
3410 A2 YA2 U 31/01/2007 45,083.00
3410 A2 YA2 U 28/02/2007 45,083.00
3410 A2 YA2 U 31/03/2007 45,083.00
3410 A2 YA2 U 30/04/2007 45,083.00
3410 A2 YA2 U 31/05/2007 45,083.00
3410 A2 YA2 U 30/06/2007 45,083.00
3410 A2 YA2 U 31/07/2007 45,083.00
3410 A2 YA2 U 31/08/2007 45,083.00
3410 A2 YA2 U 30/09/2007 45,083.00
3410 A2 2114 YA2 0 20/12/2007 -1,156.20
3410 A2 YA2 U 31/10/2007 45,083.00
3410 A2 YA2 U 30/11/2007 45,083.00
3410 A2 YA2 U 29/12/2007 45,087.00
3410 A2 1295 YA2 0 21/12/2007 -166,020.00
3410 A2 1392 YA2 0 30/12/2007 -14,433.90
3410 A2 YA2 0 30/09/2007 -1,173.90
3410 A2 YA2 0 30/09/2007 1,173.90
3410 A2 9147 YA2 0 30/09/2007 -1,173.90
*********************************************************************

Table 2
*************
CC DONOR SYMBOL DTE CURRENCY_AMOUNT CURRENCY_EXCHANGE_RATE FOREIGN_AMOUNT ACT SPLIT_AMOUNT
A2 1108 SEK 27/12/2006 491,000.00 0.17782 91,000.00 WW0350 16,181.62
A2 1108 SEK 16/07/2007 50,000.00 0.18084 50,000.00 YA2 9,042.00
C1 1108 SEK 10/12/2007 3,576,000.00 0.17566 300,000.00 YYC1 52,698.00
A2 1108 SEK 10/12/2007 50,000.00 0.17566 50,000.00 YA2 8,783.00
C1 1108 SEK 04/04/2007 2,837,500.00 0.17410 300,000.00 YYC1 52,230.00
C1 1108 SEK 23/04/2007 2,837,500.00 0.17839 300,000.00 YYC1 53,517.00
C1 1108 SEK 16/07/2007 3,537,500.00 0.18084 300,000.00 YYC1 54,252.00
A2 1108 SEK 20/12/2007 260,000.00 0.17555 260,000.00 WW0350 45,643.00
A2 1108 USD 26/03/2008 1,000.00 1.01125 1,000.00 YA2 1,011.25
A2 1108 SEK 26/03/2008 325,000.00 0.16750 200,000.00 YA2 33,500.00
A2 1108 SEK 26/03/2008 325,000.00 0.16750 125,000.00 WW0350 20,937.50
C1 1108 SEK 26/03/2008 6,175,000.00 0.16750 500,000.00 YYC1 83,750.00
A2 1108 USD 04/04/2007 1,000.00 1.22190 1,000.00 YA2 1,221.90
A2 1108 SEK 04/04/2007 50,000.00 0.17410 50,000.00 YA2 8,705.00
A2 1108 SEK 23/04/2007 50,000.00 0.17839 50,000.00 YA2 8,919.50
C1 1178 EUR 11/07/2007 785,000.00 1.65400 125,000.00 YYC1 206,750.00
C1 1178 EUR 09/10/2007 785,000.00 1.66540 125,000.00 YYC1 208,175.00
C1 1178 EUR 11/01/2007 785,000.00 1.61400 125,000.00 YYC1 201,750.00
C1 1178 EUR 16/01/2008 763,500.00 1.61270 125,000.00 YYC1 201,587.50
A2 1178 EUR 29/11/2007 40,000.00 1.64910 40,000.00 YA2 65,964.00
C1 1178 EUR 11/04/2008 763,500.00 1.58665 124,999.99 YYC1 198,331.24
C1 1178 EUR 10/04/2007 785,000.00 1.63800 125,000.00 YYC1 204,750.00
******************************************************
Thanks
ishaame

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources