PHPmaster
20th November 2011, 12:26 AM
Misal ane punya tabel gini gan:
kdtoko|bln |Pendapatan
012 | 01 |38217229145.00
012 | 01 |12115124316.00
012 | 02 |2216574585.00
012 | 02 |48881599.00
012 | 03 |48712503.00
012 | 03 |28233351.00
012 | 04 |307685373.00
012 | 04 |85943532.00
012 | 05 |1105089601.00
012 | 05 |104214690.00
012 | 06 |9178132630.00
012 | 06 |368590822.00
012 | 07 |12817354466.00
012 | 07 |162518569.00
012 | 08 |53564709.00
012 | 08 |388000.00
012 | 08 |1183793367.00
012 | 09 |2289719760.00
012 | 09 |8720148.00
012 | 10 |1208958929.00
012 | 10 |1517060966.00
012 | 11 |1538124757.00
012 | 11 |2350941785.00
012 | 12 |101844398.00
012 | 12 |6754661450.00
042 | 01 |16995026284.00
042 | 01 |1792224090.00
042 | 01 |1594145695.00
042 | 02 |1232478605.00
042 | 02 |70823423.00
042 | 02 |135496023.00
042 | 03 |937713998.00
042 | 03 |78549786.00
042 | 03 |39865006.00
042 | 04 |73404567.00
042 | 04 |94457048.00
042 | 04 |103365292.00
042 | 05 |717826589.00
042 | 05 |103826654.00
042 | 05 |349909831.00
042 | 06 |426072778.00
042 | 06 |83149149.00
042 | 06 |143322641.00
042 | 07 |1949942002.00
042 | 07 |86498952.00
042 | 07 |78206817.00
042 | 08 |286822495.00
042 | 08 |197191953.00
042 | 08 |30250403.00
042 | 09 |950272644.00
042 | 09 |224912104.00
042 | 09 |1987243491.00
042 | 10 |861985212.00
042 | 10 |84105269.00
042 | 10 |77449614.00
042 | 11 |453689367.00
042 | 11 |356033811.00
042 | 11 |708311831.00
042 | 12 |5797393.00
042 | 12 |302897881.00
042 | 12 |9803722.00
092 | 01 |3627427909.00
092 | 01 |225292243.00
092 | 02 |206820282.00
092 | 02 |49245348.00
092 | 03 |182502262.00
092 | 03 |70024181.00
092 | 04 |124719990.00
092 | 04 |74891864.00
092 | 05 |223143811.00
092 | 05 |102120502.00
092 | 06 |220368400.00
092 | 06 |13597915.00
092 | 07 |131079233.00
092 | 07 |56963620.00
092 | 08 |215356817.00
092 | 08 |55283620.00
092 | 09 |166999501.00
092 | 09 |53941720.00
092 | 10 |163082917.00
092 | 10 |56526537.00
092 | 11 |446021089.00
092 | 11 |479312791.00
092 | 12 |288903470.00
092 | 12 |68255209.00
ane mau bikin queri dari tabel itu gan, fieldnya:
KdToko | pendapatan s.d. bulan lalu | pendapatan blnini | pendapatan sdblnini
sintaksnya gimana ya gan? apa perlu bikin tabel bantu dulu? misal bulan ini bulan 12.
thanks.
numpang nyimpen sintaks, takut kelupaan
<div style="margin:20px; margin-top:5px">
Code:
select kdsatker, left(perksai, 2), abs(sum(case when jnsdok1 like '2%' then rphreal end)) as Pagu,
sum(case when periode < 12 and jnsdok1 like '3%' then rphreal end) as bulanlalu,
sum(case when periode = 12 and jnsdok1 like '3%' then rphreal end) as blnini,
sum(case when periode
kdtoko|bln |Pendapatan
012 | 01 |38217229145.00
012 | 01 |12115124316.00
012 | 02 |2216574585.00
012 | 02 |48881599.00
012 | 03 |48712503.00
012 | 03 |28233351.00
012 | 04 |307685373.00
012 | 04 |85943532.00
012 | 05 |1105089601.00
012 | 05 |104214690.00
012 | 06 |9178132630.00
012 | 06 |368590822.00
012 | 07 |12817354466.00
012 | 07 |162518569.00
012 | 08 |53564709.00
012 | 08 |388000.00
012 | 08 |1183793367.00
012 | 09 |2289719760.00
012 | 09 |8720148.00
012 | 10 |1208958929.00
012 | 10 |1517060966.00
012 | 11 |1538124757.00
012 | 11 |2350941785.00
012 | 12 |101844398.00
012 | 12 |6754661450.00
042 | 01 |16995026284.00
042 | 01 |1792224090.00
042 | 01 |1594145695.00
042 | 02 |1232478605.00
042 | 02 |70823423.00
042 | 02 |135496023.00
042 | 03 |937713998.00
042 | 03 |78549786.00
042 | 03 |39865006.00
042 | 04 |73404567.00
042 | 04 |94457048.00
042 | 04 |103365292.00
042 | 05 |717826589.00
042 | 05 |103826654.00
042 | 05 |349909831.00
042 | 06 |426072778.00
042 | 06 |83149149.00
042 | 06 |143322641.00
042 | 07 |1949942002.00
042 | 07 |86498952.00
042 | 07 |78206817.00
042 | 08 |286822495.00
042 | 08 |197191953.00
042 | 08 |30250403.00
042 | 09 |950272644.00
042 | 09 |224912104.00
042 | 09 |1987243491.00
042 | 10 |861985212.00
042 | 10 |84105269.00
042 | 10 |77449614.00
042 | 11 |453689367.00
042 | 11 |356033811.00
042 | 11 |708311831.00
042 | 12 |5797393.00
042 | 12 |302897881.00
042 | 12 |9803722.00
092 | 01 |3627427909.00
092 | 01 |225292243.00
092 | 02 |206820282.00
092 | 02 |49245348.00
092 | 03 |182502262.00
092 | 03 |70024181.00
092 | 04 |124719990.00
092 | 04 |74891864.00
092 | 05 |223143811.00
092 | 05 |102120502.00
092 | 06 |220368400.00
092 | 06 |13597915.00
092 | 07 |131079233.00
092 | 07 |56963620.00
092 | 08 |215356817.00
092 | 08 |55283620.00
092 | 09 |166999501.00
092 | 09 |53941720.00
092 | 10 |163082917.00
092 | 10 |56526537.00
092 | 11 |446021089.00
092 | 11 |479312791.00
092 | 12 |288903470.00
092 | 12 |68255209.00
ane mau bikin queri dari tabel itu gan, fieldnya:
KdToko | pendapatan s.d. bulan lalu | pendapatan blnini | pendapatan sdblnini
sintaksnya gimana ya gan? apa perlu bikin tabel bantu dulu? misal bulan ini bulan 12.
thanks.
numpang nyimpen sintaks, takut kelupaan
<div style="margin:20px; margin-top:5px">
Code:
select kdsatker, left(perksai, 2), abs(sum(case when jnsdok1 like '2%' then rphreal end)) as Pagu,
sum(case when periode < 12 and jnsdok1 like '3%' then rphreal end) as bulanlalu,
sum(case when periode = 12 and jnsdok1 like '3%' then rphreal end) as blnini,
sum(case when periode