- -   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 
 
 - -   A u t h o r : 	 	 R^
 
 - -   C r e a t e   d a t e :   2 0 2 1 - 0 2 - 2 6 
 
 - -   = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 
 
 C R E A T E                 P R O C E D U R E   [ d b o ] . [ USOo`_ e'f~_ g] 
 
 @ USAm4lS  i n t , 
 
 @ US{|W  i n t 
 
   A S 
 
 
 
 i f   @ US{|W=   3 
 
 b e g i n 
 
 s e l e c t   ~NAm4lS, Ǒ-N  a s   ire
Ty, VSbWS, USMO, Ǒ-pe  a s   e'pe, i s n u l l ( 0R'pe, 0 )   a s   [bpe, /}^X[,  gNO^X[, ireAm4lS, Ǒ-{t_ Ǒ-TTf~_ hQ_ gƉV. Am4lS, 1   a s   /f&TǑ-, I S N U L L ( agpe, 0 )   a s   agpe  f r o m   Ǒ-{t_ Ǒ-TTf~_ hQ_ gƉV  l e f t   j o i n   
 
 ( s e l e c t   c o u n t ( * )   a s   agpe, Am4lS  f r o m   e'US_ e'N_ O9eU_  w h e r e   /f&TǑ-  =   1   g r o u p   b y   Am4lS)   a s   a   o n   Ǒ-{t_ Ǒ-TTf~_ hQ_ gƉV. Am4lS=   a . Am4lS
 
 w h e r e   USAm4lS  =   @ USAm4lS
 
 u n i o n   a l l 
 
 S E L E C T           P D M _ ~N
Ty_ ƉV. ~NAm4lS, d b o . ^X[{t_ ire;NeN_ W,g. 
Ty  ,   d b o . ^X[{t_ ire;NeN_ W,g. NS,   d b o . USMO
Ty_ W@xh. USMO,   d b o . P D M _ Nf~h_ W,gN. Npeϑ  A S   e'pe,   
 
                                             I S N U L L ( d b o . fuN{t]z_ N]zR. [bpeϑ,   0 )   A S   [bpe,   d b o . ^X[{t_ Y-N/}^X[. /}^X[,   d b o . ^X[{t_ Y-N/}^X[.  gNO^X[,   
 
                                             d b o . P D M _ Nf~h_ W,gN. ireAm4lS, d b o . P D M _ Nf~h_ W,gN. W,gNAm4lS, 0   a s   /f&TǑ-, I S N U L L ( agpe, 0 )   a s   agpe
 
 F R O M                   d b o . P D M _ ~N
Ty_ ƉV  I N N E R   J O I N 
 
                                             d b o . P D M _ Nf~h_ W,gN  O N   d b o . P D M _ ~N
Ty_ ƉV. ~NAm4lS  =   d b o . P D M _ Nf~h_ W,gN. ~NAm4lS  l e f t   j o i n 
 
                                             ( s e l e c t   c o u n t ( * )   a s   agpe, Am4lS  f r o m   e'US_ e'N_ O9eU_  w h e r e   /f&TǑ-  =   0   g r o u p   b y   Am4lS)   a s   a   o n   P D M _ Nf~h_ W,gN. W,gNAm4lS=   a . Am4lS  I N N E R   J O I N 
 
                                             d b o . ^X[{t_ ire;NeN_ W,g  O N   d b o . P D M _ Nf~h_ W,gN. ireAm4lS  =   d b o . ^X[{t_ ire;NeN_ W,g. ireAm4lS  I N N E R   J O I N 
 
                                             d b o . USMO
Ty_ W@xh  O N   d b o . ^X[{t_ ire;NeN_ W,g. USMOAm4lS  =   d b o . USMO
Ty_ W@xh. USMOAm4lS  L E F T   O U T E R   J O I N 
 
                                             d b o . ^X[{t_ Y-N/}^X[  O N   d b o . ^X[{t_ ire;NeN_ W,g. ireAm4lS  =   d b o . ^X[{t_ Y-N/}^X[. ireAm4lS  L E F T   O U T E R   J O I N 
 
                                             d b o . fuN{t]z_ N]zR  O N   d b o . P D M _ Nf~h_ W,gN. W,gNAm4lS  =   d b o . fuN{t]z_ N]zR. W,gNAm4lS
 
                                               w h e r e   USAm4lS  =   @ USAm4lS  
 
 e n d 
 
 e l s e 
 
 b e g i n 
 
 s e l e c t   * , 0   a s   (u^X[pe  f r o m   P D M _ ~N
Ty_ ƉV
 
 w h e r e   USAm4lS  =   @ USAm4lS
 
 e n d 
 
 