Advertisement
Guest User

Untitled

a guest
May 28th, 2018
67
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.71 KB | None | 0 0
  1.  
  2.  
  3.  
  4. ---=====================================================================================
  5. --==
  6. --== Memoria resumen facturado 2017
  7. --==
  8. --======================================================================================
  9.  
  10.  
  11. drop view vistaFacturado2017
  12.  
  13. create view vistaFacturado2017 as
  14. select '201701' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18012017 group by Farmacia,organismo,entrega union all
  15. select '201702' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18022017 group by Farmacia,organismo,entrega union all
  16. select '201703' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18032017 group by Farmacia,organismo,entrega union all
  17. select '201704' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18042017 group by Farmacia,organismo,entrega union all
  18. select '201705' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18052017 group by Farmacia,organismo,entrega union all
  19. select '201706' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18062017 group by Farmacia,organismo,entrega union all
  20. select '201707' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18072017 group by Farmacia,organismo,entrega union all
  21. select '201708' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18082017 group by Farmacia,organismo,entrega union all
  22. select '201709' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18092017 group by Farmacia,organismo,entrega union all
  23. select '201710' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18102017 group by Farmacia,organismo,entrega union all
  24. select '201711' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18112017 group by Farmacia,organismo,entrega union all
  25. select '201712' as Mes,Farmacia,case when ORGANISMO='01' and Entrega=0 then 'RElectronica' else 'RPapel' end as TipoRECETA,Organismo,COUNT(*) as Recetas , SUM(unidades) as Unidades , SUM(precio)as Precio,SUM(aportacion) as Aportacion , SUM(descuento) as RD82010 from RECETA18122017 group by Farmacia,organismo,entrega
  26.  
  27.  
  28. drop table ResumenFacturado2017
  29.  
  30. select * into ResumenFacturado2017 from vistaFacturado2017
  31.  
  32. select * from ResumenFacturado2017 order by Mes,farmacia
  33.  
  34.  
  35. drop view VistFarmaIndustria2017
  36.  
  37. create view VistFarmaIndustria2017 as
  38. select '201701' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18012017 where Situacion like 'ADHERIDO' group by Farmacia union all
  39. select '201702' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18022017 where Situacion like 'ADHERIDO' group by Farmacia union all
  40. select '201703' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18032017 where Situacion like 'ADHERIDO' group by Farmacia union all
  41. select '201704' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18042017 where Situacion like 'ADHERIDO' group by Farmacia union all
  42. select '201705' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18052017 where Situacion like 'ADHERIDO' group by Farmacia union all
  43. select '201706' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18062017 where Situacion like 'ADHERIDO' group by Farmacia union all
  44. select '201707' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18072017 where Situacion like 'ADHERIDO' group by Farmacia union all
  45. select '201708' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18082017 where Situacion like 'ADHERIDO' group by Farmacia union all
  46. select '201709' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18092017 where Situacion like 'ADHERIDO' group by Farmacia union all
  47. select '201710' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18102017 where Situacion like 'ADHERIDO' group by Farmacia union all
  48. select '201711' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18112017 where Situacion like 'ADHERIDO' group by Farmacia union all
  49. select '201712' as Mes,farmacia,SUM([DescuentoPVL7.5]+[DescuentoPVL15]+[DescuentoPVL4.0]) as DescuentoPVL from Rdl82010Desglose18122017 where Situacion like 'ADHERIDO' group by Farmacia
  50.  
  51.  
  52. select * into ResumenFarmaindustria2017 from VistFarmaIndustria2017
  53.  
  54. select * from ResumenFarmaindustria2017
  55.  
  56. drop view vistaRD119320112017
  57.  
  58. create view vistaRD119320112017 as
  59. select '201701' as mes,* from recetaRD1193201118012017 union all
  60. select '201702' as mes,* from recetaRD1193201118022017 union all
  61. select '201703' as mes,* from recetaRD1193201118032017 union all
  62. select '201704' as mes,* from recetaRD1193201118042017 union all
  63. select '201705' as mes,* from recetaRD1193201118052017 union all
  64. select '201706' as mes,* from recetaRD1193201118062017 union all
  65. select '201707' as mes,* from recetaRD1193201118072017 union all
  66. select '201708' as mes,* from recetaRD1193201118082017 union all
  67. select '201709' as mes,* from recetaRD1193201118092017 union all
  68. select '201710' as mes,* from recetaRD1193201118102017 union all
  69. select '201711' as mes,* from recetaRD1193201118112017 union all
  70. select '201712' as mes,* from recetaRD1193201118122017
  71.  
  72.  
  73. select * into ResumenRD119320112017 from vistaRD119320112017
  74.  
  75.  
  76. select * from sysobjects where xtype like 'U' and name like 'Notifica%'
  77.  
  78.  
  79. select * from Notificados012017
  80.  
  81.  
  82. create view vistaNotificados2017 as
  83. select '201701' as mes, * from Notificados012017 union all
  84. select '201702' as mes, * from Notificados022017 union all
  85. select '201703' as mes, * from Notificados032017 union all
  86. select '201704' as mes, * from Notificados042017 union all
  87. select '201705' as mes, * from Notificados052017 union all
  88. select '201706' as mes, * from Notificados062017 union all
  89. select '201707' as mes, * from Notificados072017 union all
  90. select '201708' as mes, * from Notificados082017 union all
  91. select '201709' as mes, * from Notificados092017 union all
  92. select '201710' as mes, * from Notificados102017 union all
  93. select '201711' as mes, * from Notificados112017 union all
  94. select '201712' as mes, * from Notificados122017
  95.  
  96.  
  97.  
  98. drop table ResumenNotificados2017
  99. select * into resumenNotificados2017 from vistaNotificados2017
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement