1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
| ------------------
-- Espace Disque
------------------
use HORUSREPORTOLAP
go
SELECT
a.FILEID,
CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
a.name as [DATABASENAME],
a.FILENAME as [FILENAME]
FROM
dbo.sysfiles a
use HorusReportOlapRelationnel
go
SELECT
a.FILEID,
CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
a.name as [DATABASENAME],
a.FILENAME as [FILENAME]
FROM
dbo.sysfiles a
------------------
-- Select *
------------------
select FTOPERATION.* from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION
select
--ft.*,
db.CODE, dt.JOUR, ds.CODEISSUER, dp.CODE, dtr.CODE, dtr.[TYPE],
ft.NPV, ft.VALUE, ft.DELTA
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp ON dp.IDPRODUCT = ft.IDPRODUCT
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr ON dtr.IDTRADE = ft.IDTRADE
------------------
-- Group by Simple
------------------
select FTOPERATION.CODETRADE from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION
group by FTOPERATION.CODETRADE
having COUNT(*) > 100
select
--ft.*,
--db.CODE, dt.JOUR, ds.CODEISSUER, dp.CODE, dtr.CODE, dtr.[TYPE],
--ft.NPV, ft.VALUE, ft.DELTA
dtr.CODE
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp ON dp.IDPRODUCT = ft.IDPRODUCT
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr ON dtr.IDTRADE = ft.IDTRADE
group by dtr.CODE
having COUNT(*) > 100
------------------
-- Group by 1
------------------
select
ft.TYPETRADE, ft.CODETRADE, ft.CODEISSUER, ft.CODEPRODUCT
, ft.[DATE], ft.CODEBOOK
from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION ft
--where ft.CODETRADE = 'UL'
group by ft.TYPETRADE, ft.CODETRADE, ft.CODEISSUER, ft.CODEPRODUCT
, ft.[DATE], ft.CODEBOOK
--having COUNT(*) > 100
select
dtr.[TYPE], dtr.CODE, ds.CODEISSUER, dp.CODE, dt.IDDATE, dp.CODE
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp ON dp.IDPRODUCT = ft.IDPRODUCT
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr ON dtr.IDTRADE = ft.IDTRADE
--where dtr.CODE = 'UL'
group by dtr.[TYPE], dtr.CODE, ds.CODEISSUER, dp.CODE, dt.IDDATE, dp.CODE
--having COUNT(*) > 100
------------------
-- Group by 2
------------------
select ft.TYPETRADE, ft.CODETRADE
, SUM(VALUE) as value, SUM(npv) as npv
from HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION ft
where
ft.CODEPRODUCT = 'FG'
AND ft.CODEISSUER = 'UGYHHGFHJDF'
group by ft.TYPETRADE, ft.CODETRADE, ft.CODEISSUER, ft.CODEPRODUCT
, ft.[DATE], ft.CODEBOOK
select
--ft.*
--db.CODE, dt.JOUR, ds.CODEISSUER, dp.CODE, dtr.CODE, dtr.[TYPE],
--ft.NPV, ft.VALUE, ft.DELTA
dtr.[TYPE], dtr.CODE
, SUM(VALUE) as value, SUM(npv) as npv
from HORUSREPORTOLAP.dbo.FTOPERATION ft
JOIN HORUSREPORTOLAP.dbo.DIMTRADE dtr ON dtr.IDTRADE = ft.IDTRADE
JOIN HORUSREPORTOLAP.dbo.DIMBOOK db ON db.IDBOOK = ft.IDBOOK
JOIN HORUSREPORTOLAP.dbo.DIMDATE dt ON dt.IDDATE = ft.IDDATE
JOIN HORUSREPORTOLAP.dbo.DIMISSUER ds ON ds.IDISSUER = ft.IDISSUER
JOIN HORUSREPORTOLAP.dbo.DIMPRODUCT dp ON dp.IDPRODUCT = ft.IDPRODUCT
where
dp.CODE = 'FG'
AND ds.CODEISSUER = 'UGYHHGFHJDF'
group by dtr.[TYPE], dtr.CODE, ds.CODEISSUER, dp.CODE, dt.IDDATE, dp.CODE
------------------
-- UPDATE
------------------
UPDATE HORUSREPORTOLAPRELATIONNEL.dbo.FTOPERATION
SET
CODEISSUER = 'UGYHHGFHJDF'
WHERE
CODETRADE = 'UL'
UPDATE HORUSREPORTOLAP.dbo.FTOPERATION
SET HORUSREPORTOLAP.dbo.FTOPERATION.IDISSUER = ds.IDISSUER
FROM
HORUSREPORTOLAP.dbo.DIMTRADE dt
, HORUSREPORTOLAP.dbo.DIMISSUER ds
WHERE
dt.CODE = 'UL'
and ds.CODEISSUER = 'UGYHHGFHJDF'
AND dt.IDTRADE = HORUSREPORTOLAP.dbo.FTOPERATION.IDTRADE |
Partager