DBCC CHECKIDENT ('PRY_EQUIPOS', RESEED, 7)
----------------------------------------------ToValuesString
usario no PMO
rtypaldos
gasnatural
-- actualizar cache Transact
teclado CTRL+Mays+R
\\[Link]\Volume_1
credenciales:
aplicaciones
app2013
SET NOCOUNT ON;
SET NOCOUNT OF;
-- COUNT DE TRANSACCIONES
SELECT @@trancounterror
ROLLBACK TRANSACTION
if not exists (select name from [Link] where name = 'PRY_DIRNIVELII_MIG_140
429')
----------------------------------------------------------------------------------------------------------and (@IdMacrocapitulo is null or [Link] = @IdMacrocapitulo)
----------------------------------------------------------------------------------------------------------convert(MONEY, [Link]) AS [Coste Final Ao en curso]
----------------------------------------------------------------------------------------------------------UTLS DE PRUEBAS EN GPTO
----------------------------------------------------------------------------------------------------------exec UTL_Get_Propuestas 9525,979
exec UTL_Get_Partidas 2014,'es', 52991
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT ERROR_MESSAGE(),@@TRANCOUNT
SET IDENTITY_INSERT MST_Mensajes_Error ON
insert into MST_Mensajes_Error(idError,Error) values (60021, 'Se ha producido un
error al versionar presupuestos.');
SET IDENTITY_INSERT MST_Mensajes_Error OFF
-----------------------------------------------------------------------------------------------PROCEDIMIENTOS
-------------------------------------------------------------------------------------------------- =============================================
-- Author: Fernando Jimenez
-- Create date: /09/2014
-- Description:
-------------------------------------------------------------MARCA
FECHA
DESCRIPCION
-- =============================================
CREATE PROCEDURE [dbo].[p_Guardar_Arbol_Presupuestario]
@pIdPartida
int output,
-- Es de salida porque hay que devolverlo a NET
@pAnyo
int output ,
-- Es de
salida porque hay que devolverlo a NET
@pCodISOPais
nvarchar(3),
@pIdUsuarioAuditoria
nvarchar(max),
@pChkError
bit output,
--rastreo error
@pCodError
nvarchar(20)output
--rastre
o error. Codigo para mensaje en fichero de recursos .net
AS
BEGIN
SET NOCOUNT ON;
SET @pChkError = 0
BEGIN TRANSACTION
BEGIN TRY
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @pChkError = 1
SET @pCodError = 70001
ROLLBACK TRANSACTION
RETURN --si ha habido un error, no continuamos el proceso
END CATCH
END
------------------------------------------------------------------------------------------------CURSOR ESTATICO
------------------------------------------------------------------------------------------------Declare @IdProyectoCurr int
Declare @IdProyectoPadre int
Declare @IdTipoEntidad int
DECLARE Entidades SCROLL CURSOR
FOR
Select idProyecto,isnull(Idproyectopadre,''),idTipoEntid
ad
From Fn_GetHijos_Planificacion(2246,5,default)
OPEN Entidades
FETCH Entidades
INTO @IdProyectoCurr
,@IdProyectoPadre
,@IdTipoEntidad
WHILE (@@FETCH_STATUS = 0)
BEGIN
print cast(@IdProyectoCurr as varchar)
FETCH NEXT FROM Entidades
INTO @IdProyectoCurr
,@IdProyectoPadre
,@IdTipoEntidad
END
CLOSE Entidades
DEALLOCATE Entidades
-- cierre de cursor
IF (cursor_status('global', N'@c_cliente') <> -3)
DEALLOCATE @c_cliente
-- CERRAR CURSOR
DECLARE @iCursor INT
SELECT @iCursor = count(*) from master..syscursors where cursor_name =
'cCursor'
IF @iCursor > 0
BEGIN
CLOSE cCursor
DEALLOCATE cCursor
End
-------------------------------------------------------------------------------------------------ESTRUCTURA BASICA DE PROCEDIMIENTO
-------------------------------------------------------------------------------------------------- =============================================
-- Author:
Fernando Jimnez Garcia
-- Create date: 24/07/2014
-- Description:
-- Parametros :
-- Modificacion:Marca Fecha
Descripcion
------- ---------- -------------------------------------------------------FJG001 30/06/2014 Mantis 13123
-- =============================================
CREATE PROCEDURE [dbo].[PRY_DesplazarFechas]
@p_IdProyecto int,
@p_NumDias int
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--Capturar y devolver la excepcin
DECLARE @ErrorMessage NVARCHAR(4000) = (
(SELECT ERROR_PROCEDURE()) +' - ' + (SELECT ERRO
R_MESSAGE())
);
DECLARE @ErrorSeverity INT = (
SELECT ERROR_SEVERITY()
);
DECLARE @ErrorState INT = (
SELECT ERROR_STATE()
);
RAISERROR (
@ErrorMessage
,
-- Message text.
@ErrorSeverity
,
-- Severity.
@ErrorState -- State.
)
END CATCH
END
-------------------------------------------------------------------------------------------------ESTRUCTURA DE FUNCION ESCALAR
-------------------------------------------------------------------------------------------------- =============================================
-- Author: Fernando Jimenez
-- Create date: //2014
-- Description: Funcin que devuelve
-- =============================================
CREATE FUNCTION [dbo].[Fn_] (@ INT)
RETURNS nvarchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @vDev nvarchar(max)
SELECT @vDev = DescEstado
FROM PRY_EstadosProyecto
WHERE codEstado = @p_IdAprobacion
-- Return the result of the function
RETURN @vDev
END
-------------------------------------------------------------------------------------------------ESTRUCTURA DE FUNCION DE TABLA
-------------------------------------------------------------------------------------------------- =============================================
-- Author: Fernando Jimenez
-- Create date: //2014
-- Description: Funcin que devuelve
-- =============================================
CREATE FUNCTION [dbo].[Fn_] (@IdProyecto INT)
RETURNS @CosteEsfuerzo TABLE (
CosteTotal
EsfuerzoTotal
INT,
FechaInicio
FechaFin
MejorPrevisionInicio
DATE,
MejorPrevisionFin
DATE
)
DECIMAL(19, 8),
DATE ,
DATE,
AS
BEGIN
INSERT INTO @CosteEsfuerzo
SELECT DISTINCT
CASE WHEN ([Link] = 1) THEN ISNULL((select SUM(ISNULL(p
[Link],0)) FROM PRY_COSTES_INICIALES pci WHERE [Link] = [Link]
Proyecto), 0)
ELSE ISNULL((select MAX(CosteFinal) from PRY_ResumenCosteEsfuerz
o where IdProyecto = [Link]),0) END as ImporteTotal,
CASE WHEN ([Link] = 1) THEN ISNULL((select SUM(ISNULL(p
ci.Esfuerzo_Inicial,0)) FROM PRY_COSTES_INICIALES pci WHERE [Link] = ci.
IdProyecto), 0)
ELSE ISNULL((select MAX(EsfuerzoInicial) from PRY_ResumenCosteEs
fuerzo where IdProyecto = [Link]),0) END as EsfuerzoTotal,
convert(nvarchar(12) , [Link], 103),
convert(nvarchar(12) , [Link], 103),
convert(nvarchar(12) , [Link], 103),
convert(nvarchar(12) , [Link], 103)
FROM PRY_COSTES_INICIALES ci
INNER JOIN PRY_PROYECTOS p ON [Link] = [Link]
WHERE [Link] = @IdProyecto
RETURN
End
-------------------------------------------------------------------------------------------------bk de tabla
------------------------------------------------------------------------------------------------exec dbo.PRY_CrearBkTabla 'PRY_Programa',0
IF NOT EXISTS (SELECT * FROM [Link] WHERE object_id = OBJECT_ID(N'[PRY_DIRG
EN_140123]') AND type in (N'U'))
Begin
select * INTO PRY_DIRGEN_140123 FROM PRY_DIRGEN
if @@ROWCOUNT = 0
Print'Error al crear el backUp de la tabla PRY_DIRGEN'
else
Print' BackUp de la tabla PRY_DIRGEN correcto.(PRY_DIRGE
N_140123)'
End
Else
Print' Ya existe un backUp de la tabla PRY_DIRGEN.(PRY_DIRGEN_14
0123)'
-------------------------------------------------------------------------------------------------CASE
------------------------------------------------------------------------------------------------DECLARE @TestVal int
SET @TestVal = 3
SELECT CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END
-------------------------------------------------------------------------------------------------case en where
------------------------------------------------------------------------------------------------SELECT @IdRecursoAsigManual = [Link]
FROM PRY_PLAN_FACTURACION pf
,PRY_TAREAS t
,PRY_TAREAS_EQUIPOS_RECURSOS ter
,PRY_RECURSOS r
WHERE pf.IdPF_proyecto = @IdPF_proyecto
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] =
CASE
WHEN @iRecurso > 3
THEN [Link]
ELSE [Link]
END
---- otro
select @hayPartidas = 0
from @tabla t1
where [Link] = 3113
and [Link] = 2014
and not exists (select '1'
from Pry_Partidas_Proyec
tos
where IdProyecto = [Link]
Proyecto
and Anio = [Link]
ura
and IdPais = [Link]
factura)
and 0 < case dbo.Fn_GetTipoEntidad(3113)
when 5 then [Link]
else 1 end
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------comandos sql server :
[Link]
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------WITH ESTRUCTURA_TMP(
Proyecto,
Entidad
) AS
(
SELECT distinct IdProyecto,IdTipoEntidad
FROM PRY_TAREAS
where IdProyecto = 38
)
SELECT Proyecto, Entidad
FROM ESTRUCTURA_TMP
GO
------------------------------------------------------------------------------------CURSOR DINAMICO
------------------------------------------------------------------------------------DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
@ssql Nvarchar(500)
@tabla Nvarchar(500)
@Prod Nvarchar(5)
@Mes Nvarchar(2)
@Ao Nvarchar(4)
@vOutputDate
DATETIME
@pInputDate
DATETIME
Set @Ao = substring (db_name(),4,4)
print @Ao
select @tabla = 'ME_2012_02.INCENTIVOS.ME_REA_SATISFACCION'
SELECT @ssql = 'DECLARE my_cur CURSOR FOR SELECT distinct COD_AG
RUP_PROD, MAX(MES) FROM ' + @tabla +
' GROUP BY COD_AGRUP_PROD'
EXEC sp_executesql @ssql
Open my_cur
Fetch Next From my_cur Into @Prod ,@Mes
WHILE @@FETCH_STATUS = 0
BEGIN
print @Prod + ' - ' + @Mes
set @pInputDate = '01/'+ @MES +'/'+ @Ao +''
SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMA
L(12, 5))) (DAY(@pInputDate) - 1
) AS DATETIME)
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutpu
tDate))
print 'ultimo dia del mes '+ @MES +': ' + cast(@vOutputD
ate as varchar)
print 'ultimo dia del mes '+ @MES +': ' + convert(varcha
r(10),@vOutputDate ,103)
FETCH NEXT FROM my_cur INTO @Prod ,@Mes
END
CLOSE my_cur
DEALLOCATE my_cur
------------------------------------------------------------------------------------IF EXISTS
-------------------------------------------------------------------------------------
SELECT @sSql=' IF EXISTS (select * from '+ @Bd +'..sysobjects
WHERE XTYPE=''U'' AND NAME ='''+ @Seg + '_VAL_LIQUIDACI
ON'' )
drop table '+ @TabChequeos +''
PRINT @sSql
EXEC (@sSql)
SELECT @sSql='
CREATE TABLE '+ @TabChequeos +'(
[ID] FLOAT ,
[ESTADO] [varchar] (15) NOT NULL ,
[DESCRIPCION] [varchar] (500) NOT NULL ,
[TOTAL] FLOAT ,
[NUM_REG] FLOAT ,
[FECHA] [DATETIME]
) ON [PRIMARY] '
PRINT @sSql
EXEC (@sSql)
--Con clave primaria
ALTER TABLE [INCENTIVOS].[AU_Datos_Incentivos] WITH NOCHECK A
DD
CONSTRAINT [PK_AU_Datos_Incentivos] PRIMARY KEY NONCLUSTERED
(
[SEGMENTO],
[CATEGORIA],
[MATRICULA]
)ON [PRIMARY]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NUMERICOS
Set @ssql = 'Select @Count = Count(*) From Incentivos.ME_Log_Cargas Wher
e Carga = ''Productos'' And Fec_Carga ='+ @psEnv
Exec sp_executesql @ssql, N'@count int output', @Count output;
-- CADENAS
Set @ssql = 'select @TabPagos5 = NAME from '+ @bd +'..sysobjects WHERE X
TYPE=''U'' AND RIGHT(NAME,13)=''PAGOS_TOTALES'' '
print @ssql
Exec sp_executesql @ssql, N'@TabPagos5 Nvarchar(50) output', @TabPagos5
output;
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------DECLARE
DECLARE
DECLARE
DECLARE
DECLARE
@ssql NVARCHAR(500)
@CATEGORIA NVARCHAR(2)
@MATRICULA NVARCHAR(7)
@SUMA_PESOS FLOAT
@tab VARCHAR(100)
SET @tab ='GE_2012_02.INCENTIVOS.GE_PP_DETALLE '
Set @ssql = 'Select @CATEGORIA = CATEGORIA , @MATRICULA = MATRICULA , @
SUMA_PESOS = SUM(PESO)
From ' + @tab +' WHERE MATRICULA =''T093329'' GROUP BY
CATEGORIA,MATRICULA '
PRINT @ssql
Exec sp_executesql @ssql, N'@CATEGORIA NVARCHAR (2)output,
@MATRICULA NVARCHAR (7)
output ,
@SUMA_PESOS FLOAT OUTPUT
,
@tab NVARCHAR (50) ', @
CATEGORIA output,@MATRICULA OUTPUT,@SUMA_PESOS OUTPUT,@tab ;
PRINT @CATEGORIA
PRINT @MATRICULA
PRINT CAST(@SUMA_PESOS AS VARCHAR)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Update GE_2012_02.INCENTIVOS.GE_PP_DETALLE
set SUMA_PESOS=(Select SUM(PESO)
from
GE_2012_02.INCENTIVOS.GE_PP_DETALLE h
where
[Link]=GE_2012_02.INCENTIVOS.GE_PP_
[Link] and
[Link]=GE_2012_02.INCENTIVOS.GE_PP_
[Link] )
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------comparacion de consultas : EXCEPT e INTERSECT
Devuelven valores distintos al comparar los resultados de dos consultas.
EXCEPT devuelve los valores distintos de la consulta izquierda que no se
encuentran en la consulta derecha.
INTERSECT devuelve los valores distintos devueltos por las consultas sit
uadas a los lados izquierdo y derecho del operando INTERSECT.
Las reglas bsicas para combinar los conjuntos de resultados de do
s consultas que utilizan EXCEPT o INTERSECT son las siguientes:
-------------------------------------------------------------------------------------------------EXISTS
------------------------------------------------------------------------------------------------IF not Exists(Select * from PRY_ACTUALIZACION_FACTURACION_FLAGS where IdProyect
o = 2288)
BEGIN
INSERT INTO PRY_ACTUALIZACION_FACTURACION_FLAGS
(IdProyecto)
values
(2288)
END
-- Existe un registro
if exists (select '1'
from PRY_TAREAS
where IdProyecto = @IdProyecto
and IdTarea = @idtarea
and IdTipologia_Tarea = 1)
SELECT idTarea, idPais, '' as '@idrecurso_insertado', [Link],
'' as '@Descripcion', 0 Horas, 0 Coste, 0 HorasReal,
0 CosteReal, 0 HorasPendientes,
1 RepartoEsfuerzo, 1 RepartoInversion, GETDATE(), NULL,
0 CosteIniLocal, 0 CosteReaLocal
FROM PRY_TAREAS t,
PRY_COSTES_INICIALES ci
where [Link]= 576
and [Link]= [Link]
and [Link]
= (select YEAR([Link])
from PRY_PROYECTOS p
where IdProyecto = [Link])
and Not Exists(select [Link]
from PRY_TAREAS_EQUIPOS_RECURSOS
where idTarea
= [Link]
and idPais
= [Link]
and IdProyecto
SELECT [Link], [Link]
FROM [Link] AS a
WHERE EXISTS
(SELECT *
FROM [Link] AS b
WHERE [Link] = [Link]
AND [Link] = 'Johnson');
= 576)
-------------------------------------------------------------------------------------------------FORMATEO FECHAS
------------------------------------------------------------------------------------------------DECLARE @fFinal datetime = '17/04/2013'
convert(varchar , @fFinal, 103) -> 17/04/2013
convert(varchar , @fFinal, 112) -> 20130417
-------------------------------------------------------------------------------------------------1FUNCIONES ESCALARES.
------------------------------------------------------------------------------------------------Create function IVA (@cantidad money)
Returns money
As
Begin
Declare @resultado money
Set @resultado = @cantidad
End
0.12
Consumir:
Select productos,precio,[Link](precio) as Iva
From productos
-------------------------------------------------------------------------------------------------- 2-FUNCION VALORES DE VARIAS INSTRUCCIONES
------------------------------------------------------------------------------------------------Create Function ListadoPais(@pais varchar(100))
Returns @clientes table
(
customerId varchar(5),
companyname varchar(50),
contactname varchar(100)
country varchar(100)
)
as
begin
insert @clientes select customerId, companyname, contactname, c
ountry
from customer where country = @pais
return
end
consumir:
select from [Link]( Germany )
-------------------------------------------------------------------------------------------------- 3
FUNCIONES DE VALORES DE TABLA EN LINEA
------------------------------------------------------------------------------------------------Create Function ListadoPais2(@pais varchar(100))
Returns table
As
return
(
select customerId, companyname, contactname, country
from customer where country = @pais)
consumir:
select
from dbo.listadopais2( Germany )
-------------------------------------------------------------------------------------------------- TRIGGER
------------------------------------------------------------------------------------------------CREATE TRIGGER NOMBRE_TRIGER
ON NOMBRE_TABLA
AFTER (INSERT,UPDATE,DELETE)
AS
BEGIN
CUERPO DEL TRIGGER
END
-------------------------------------------------------------------------------------------------not exists
------------------------------------------------------------------------------------------------SELECT IdDirNivelII
FROM PRY_DIRNIVELII
WHERE NOT EXISTS
(SELECT *
FROM PRY_PROYECTOS
WHERE IdDirNivelII = PRY_DIRNIVELII.IdDirNivelII
and End_date is null)
-------------------------------------------------------------------------------------------------OPENROWSET con ficheros excel
------------------------------------------------------------------------------------------------INSERT INTO MyTable
SELECT * FROM OPENROWSET('[Link].12.0',
'Excel 12.0;Database=D:\[Link]', [NombreHoja$])
Dependiendo el tipo de archivo (extensin)
File Type (extension)
Extended Pr
operties
-------------------------------------------------------------------------------------------Excel 97-2003 Workbook (.xls)
"Excel 8.0"
Excel 2007-2010 Workbook (.xlsx)
"Excel 12.0 Xml"
Excel 2007-2010 Macro-enabled workbook (.xlsm)
"Excel 12.0 Mac
ro"
Excel 2007-2010 Non-XML binary workbook (.xlsb)
"Excel 12.0"
-------------------------------------------------------------------------------------------------FOREIGN KEY
------------------------------------------------------------------------------------------------alter table dbo.OOEE_ORDENES_ENTREGA
add constraint FK_OOEE_ORDENES_ENTREGA_OOEE_OOEE_VALIDACIONES
foreign key (CodValidacion)
references dbo.OOEE_VALIDACIONES (CodValidacion)
-------------------------------------------------------------------------------------------------CREATE CON PRYMARY Y FOREIGN KEY
------------------------------------------------------------------------------------------------CREATE TABLE [dbo].[OOEE_LOG_CARGAS](
[idCarga] [int] NOT NULL,
[NomFichero] [nvarchar](100) NOT NULL,
[Fecha] [datetime] NOT NULL,
[RegEnviados] [int] NULL,
[RegVinculados] [int] NULL,
[RegNoVinculados] [int] NULL,
[Estado] [nvarchar](100) NULL,
[Observaciones] [nvarchar](250) NULL,
[CodDocCompras] [nvarchar](10) NOT NULL,
[CodPosicion] [int] NOT NULL,
PRIMARY KEY ([CodDocCompras],[CodPosicion],[NomFichero],[idCarga]),
FOREIGN KEY ([CodDocCompras],[CodPosicion],[NomFichero],[idCarga]) REFER
ENCES OOEE_FICHEROS_ENTREGA_HIST([CodDocCompras],[CodPosicion],[NomFichero],[idC
arga]),
FOREIGN KEY ([CodDocCompras],[CodPosicion],[NomFichero],[idCarga]) REFER
ENCES OOEE_ORDENES_ENTREGA ([CodDocCompras],[CodPosicion],[NomFichero],[idCarga]
)
)
-------------------------------------------------------------------------------------------------Crear tabla, con estructura de otra
------------------------------------------------------------------------------------------------declare @TArea Table (IdArea int)
if @IdArea is null
set @sqlArea = 'Select IdArea from Areas Where PPT = 1'
else
set @sqlArea = 'Select IdArea from Areas Where IdArea = ' + CONVERT(var
char(10), @IdArea)
insert into @TArea
execute sp_executesql @sqlArea
--Insert en la Tabla ICP_PROYECTOS
declare @TPryProyectos IcpProyectos
insert into @TPryProyectos
exec dbo.PPT_Informe_GetProyectosFromPRY @IdComite
-------------------------------------------------------------------------------------------------Consulta de CECO
------------------------------------------------------------------------------------------------select [Link],[Link],cc.*
FROM PRY_PROYECTOS pp
INNER JOIN CECO cc ON [Link] = [Link]
and [Link] = [Link]
-------------------------------------------------------------------------------------------------Extraer el area de un proyecto
-----------------------------------------------------------------------------------------------select [Link],[Link],[Link] ,[Link] as Areas_Chtto--, cc.
*
FROM PRY_PROYECTOS pp
INNER JOIN CECO cc ON [Link] = [Link]
inner join Areas ar on [Link] = [Link]
where [Link] = 38
and [Link]= 1540
-- and [Link] = [Link]
-------------------------------------------------------------------------------------------------tabla temporal
-----------------------------------------------------------------------------------------------create table #Tabla_HorasPlanif (
Equipo Int,
Anio int ,
Resultado numeric(18,2)
)
insert into #Tabla_HorasPlanif exec PRY_GetHorasGruposAnios @idproyecto, @idPai
s
-------------------------------------------------------------------------------------------------variable de tipo tabla
------------------------------------------------------------------------------------------------- no es transaccional. buena para pocos registros.
DECLARE @TablaTemporal TABLE (
ID int
)
-------------------------------------------------------------------------------------------------SCRIPT CON MANEJADOR DE ERRORES
-----------------------------------------------------------------------------------------------BEGIN TRY
BEGIN TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
-------------------------------------------------------------------------------------------------COMITES por mes (el maximo)
-----------------------------------------------------------------------------------------------Declare @IdAreaComite int
Declare @IdMacrocapitulo int
Declare @Tipo int = 1
select max(IdComite)comite ,left(convert(varchar , fecha, 112),6)
from ICP_COMITE t
where [Link] = isnull(@Tipo, [Link])
and (@IdAreaComite is null or [Link] = @IdAreaComite )
and (@IdMacrocapitulo is null or [Link] = @IdMacrocapitulo)
And YEAR([Link]) = YEAR(GETDATE())
group by left(convert(varchar , fecha, 112),6)
-------------------------------------------------------------------------------------------------APROBADO Y COMPROMETIDO DE UN PROYECTO
-----------------------------------------------------------------------------------------------,dbo.Fn_CambioImporteAnioMes(ISNULL(dbo.Fn_CalculaImporteAprobadoPry(IdProyecto,
NULL, @Pais), 0), ISNULL(@Moneda, Moneda), Moneda, GETDATE())
AS ImporteAprobado
,dbo.Fn_CambioImporteAnioMes(ISNULL(dbo.Fn_CalculaImporteComprometidoPry(IdProye
cto, NULL, @Pais), 0), ISNULL(@Moneda, Moneda), Moneda, GETDATE())
AS ImporteComprometido
-------------------------------------------------------------------------------------------------CREAR SECUENCIAL DINAMICO EN TRANSACT (Rownum de PL)
-----------------------------------------------------------------------------------------------''R'' + CAST(ROW_NUMBER() OVER (ORDER BY IdProyRiesgo) AS NVARCHAR) AS NumRie
sgoPry,
-------------------------------------------------------------------------------------------------NO DINAMICA PERO CON PARAMETRO
-----------------------------------------------------------------------------------------------and ((@AllHitos = 0 and ([Link] = 1 or [Link] = 0 OR [Link] = 0))
OR @AllHitos = 1) /*FJG002*/
-------------------------------------------------------------------------------------------------LINQ
-----------------------------------------------------------------------------------------------from p in PRY_PROYECTOS
select p
-------------------------------------------------------------------------------------------------LISTAR TABLAS Y COLUMNAS
-----------------------------------------------------------------------------------------------SELECT [Link]
,[Link]
FROM [Link] SO
INNER JOIN [Link] SC ON SO.OBJECT_ID = SC.OBJECT_ID
WHERE [Link] = 'U'
ORDER BY [Link]
,[Link]
-----------------------------------------------------------------------------------------------PIVOT
-------------------------------------------------------------------------------------------------------- Pivot table with one row and five columns --ok
SELECT
edt
,idPartida
,PartidaPresupuestaria
,ImportePresupuestoActual
,[1] PORCENTAJE_ENERO
,[2] FEBRERO
,[3] MARZO
,[4] ABRIL
,[5] MAYO
,[6] JUNIO
,[7] JULIO
,[8] AGOSTO
,[9] SEPTIEMBRE
,[10] OCTUBRE
,[11] NOVIEMBRE
,[12] DICIEMBRE
FROM (
SELECT
[Link]
,[Link]
,[Link]
,[Link]
,[Link]
--,[Link] 'Mes2'
,[Link]
,[Link]
,[Link]
FROM PSP_Desglose_x_Meses m inner join
dbo.fn_Get_Rama_Hijos_Psp_Importes(2014,'ES',52968) f on
[Link] = [Link] And [Link] = [Link]
inner join PSP_Importes_Actuales ac on [Link] = f.
idPartida And [Link] = [Link]
--WHERE [Link] = 52956
) AS SourceTable
PIVOT(AVG(porcentajeRepartoMes) FOR mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],
[10],[11],[12])) AS PivotTable
-------------------------------------------------------------- deshabilitar restriccion
alter table PSP_Partidas_Presupuestarias
nocheck constraint FK_Partidas_Padres;
-- Para habilitar una restriccin deshabilitada
alter table PSP_Partidas_Presupuestarias
check constraint FK_Partidas_Padres;
-------------------------------------------------------------
