Newsgroup: it.comp.appl.access
Titolo: report "moving", come costruirlo in SQL?

>>>>  
http://www.bb2002.it
 :)  <<<<<
........... [ al lavoro ] ...........

 
 
fino a prova contraria questo funziona :)
 
dichiarato @Month l'ultimo mese da visualizzare (colonna M12)
PCN sta per Previous, Current, Next
DM è il mese del documento
 
select 
  ShortName
, isnull(sum(case when ( PCN = 'P' and DM > @Month -11) or ( PCN = 'C' and 
DM <= @Month -11 ) then QtyDelivered end), 0) as M01
, isnull(sum(case when ( PCN = 'P' and DM > @Month -10) or ( PCN = 'C' and 
DM <= @Month -10 ) then QtyDelivered end), 0) as M02
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 9) or ( PCN = 'C' and 
DM <= @Month - 9 ) then QtyDelivered end), 0) as M03
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 8) or ( PCN = 'C' and 
DM <= @Month - 8 ) then QtyDelivered end), 0) as M04
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 7) or ( PCN = 'C' and 
DM <= @Month - 7 ) then QtyDelivered end), 0) as M05
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 6) or ( PCN = 'C' and 
DM <= @Month - 6 ) then QtyDelivered end), 0) as M06
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 5) or ( PCN = 'C' and 
DM <= @Month - 5 ) then QtyDelivered end), 0) as M07
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 4) or ( PCN = 'C' and 
DM <= @Month - 4 ) then QtyDelivered end), 0) as M08
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 3) or ( PCN = 'C' and 
DM <= @Month - 3 ) then QtyDelivered end), 0) as M09
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 2) or ( PCN = 'C' and 
DM <= @Month - 2 ) then QtyDelivered end), 0) as M10
, isnull(sum(case when ( PCN = 'P' and DM > @Month - 1) or ( PCN = 'C' and 
DM <= @Month - 1 ) then QtyDelivered end), 0) as M11
, isnull(sum(case when ( PCN = 'P' and DM > @Month    ) or ( PCN = 'C' and 
DM <= @Month     ) then QtyDelivered end), 0) as M12
 
from #tmp
 
group by ShortName
Order by ShortName
 
ora si tratta di inserire quel poco codice necessario per calcolare il 
valore della transazione


 
 
LOL
ho trovato la prova contraria, devo gestire meglio il mese, perň l'idea 
alla base č valida :)


 
 
ultima versione, ancora non ho trovato la prova contraria :)
 
quindi niente codice VBA da convertire ma solo una finestra temporale da 
gestire dopo aver opportunamente "adattato" il mese del documento, che non 
va tre volte da 1 a 12 ma una sola volta da 1 a 36 nell'arco dei tre anni, 
dove il terzo anno non č in realtŕ utilizzato ma potrebbe venire utile in 
futuro per altre cose che ho in testa
 
select 
  ShortName
, isnull(sum(case when ( DM >= @Month -22) and ( DM <= @Month -11 ) then 
ValDelivered end), 0) as M01
, isnull(sum(case when ( DM >= @Month -21) and ( DM <= @Month -10 ) then 
ValDelivered end), 0) as M02
, isnull(sum(case when ( DM >= @Month -20) and ( DM <= @Month - 9 ) then 
ValDelivered end), 0) as M03
, isnull(sum(case when ( DM >= @Month -19) and ( DM <= @Month - 8 ) then 
ValDelivered end), 0) as M04
, isnull(sum(case when ( DM >= @Month -18) and ( DM <= @Month - 7 ) then 
ValDelivered end), 0) as M05
, isnull(sum(case when ( DM >= @Month -17) and ( DM <= @Month - 6 ) then 
ValDelivered end), 0) as M06
, isnull(sum(case when ( DM >= @Month -16) and ( DM <= @Month - 5 ) then 
ValDelivered end), 0) as M07
, isnull(sum(case when ( DM >= @Month -15) and ( DM <= @Month - 4 ) then 
ValDelivered end), 0) as M08
, isnull(sum(case when ( DM >= @Month -14) and ( DM <= @Month - 3 ) then 
ValDelivered end), 0) as M09
, isnull(sum(case when ( DM >= @Month -13) and ( DM <= @Month - 2 ) then 
ValDelivered end), 0) as M10
, isnull(sum(case when ( DM >= @Month -12) and ( DM <= @Month - 1 ) then 
ValDelivered end), 0) as M11
, isnull(sum(case when ( DM >= @Month -11) and ( DM <= @Month     ) then 
ValDelivered end), 0) as M12
 
from #tmp
 
group by ShortName
Order by ShortName
 
 
