It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.
SQL query to display as PIVOT
Select
faa.AssetID, faa.AssetNumber, faa.Status, faa.Dept, faa.Class, faa.Description, faa.PropertyNumber,faa.Acq
case When fayr.ValueID = 'COST' THEN fayr.Amount else 0 end 'Cost',
case when fayr.ValueID = 'ACC DEPR' THEN fayr.Amount else 0 end 'Accum Depr',
case when fayr.ValueID = 'BOOK' THEN fayr.Amount else 0 end 'Book value'
FROM [Livendb].[dbo].[FaAssets]
join [Livendb].[dbo].[FaAssetAm
on faa.AssetID = fayr.AssetID and faa.SourceID = fayr.SourceID
where faa.[Status] = 'ACTIVE' and faa.AssetID = '6' and fayr.FiscalYearID = '2018' and fayr.ValueID in ('BOOK','ACC DEPR','COST')
I am getting multiple line, would like to combine 3 lines in to one so the Last 3 columns (cost, Accum Depr and Book Value) are all shown into on line, here is what I have right now:
AssetID AssetNumber Status Dept Class Description PropertyNumber AcquiredDateTime Cost Accum Depr Book value
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 0.00 0.00 83722355.32
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 0.00 9087644.68 0.00
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 92810000.00 0.00 0.00
faa.AssetID, faa.AssetNumber, faa.Status, faa.Dept, faa.Class, faa.Description, faa.PropertyNumber,faa.Acq
case When fayr.ValueID = 'COST' THEN fayr.Amount else 0 end 'Cost',
case when fayr.ValueID = 'ACC DEPR' THEN fayr.Amount else 0 end 'Accum Depr',
case when fayr.ValueID = 'BOOK' THEN fayr.Amount else 0 end 'Book value'
FROM [Livendb].[dbo].[FaAssets]
join [Livendb].[dbo].[FaAssetAm
on faa.AssetID = fayr.AssetID and faa.SourceID = fayr.SourceID
where faa.[Status] = 'ACTIVE' and faa.AssetID = '6' and fayr.FiscalYearID = '2018' and fayr.ValueID in ('BOOK','ACC DEPR','COST')
I am getting multiple line, would like to combine 3 lines in to one so the Last 3 columns (cost, Accum Depr and Book Value) are all shown into on line, here is what I have right now:
AssetID AssetNumber Status Dept Class Description PropertyNumber AcquiredDateTime Cost Accum Depr Book value
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 0.00 0.00 83722355.32
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 0.00 9087644.68 0.00
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 92810000.00 0.00 0.00
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
We can do like below-
;WITH faa AS
(
Select
faa.AssetID, faa.AssetNumber, faa.Status, faa.Dept, faa.Class, faa.Description, faa.PropertyNumber,faa.AcquiredDateTime,
case When fayr.ValueID = 'COST' THEN fayr.Amount else 0 end 'Cost',
case when fayr.ValueID = 'ACC DEPR' THEN fayr.Amount else 0 end 'Accum Depr',
case when fayr.ValueID = 'BOOK' THEN fayr.Amount else 0 end 'Book value'
FROM [Livendb].[dbo].[FaAssets] faa with (nolock)
join [Livendb].[dbo].[FaAssetAmountsByYear] fayr with (nolock)
on faa.AssetID = fayr.AssetID and faa.SourceID = fayr.SourceID
where faa.[Status] = 'ACTIVE' and faa.AssetID = '6' and fayr.FiscalYearID = '2018' and fayr.ValueID in ('BOOK','ACC DEPR','COST')
)
SELECT
faa.AssetID, faa.AssetNumber, faa.Status, faa.Dept, faa.Class, faa.Description, faa.PropertyNumber,faa.AcquiredDateTime
,SUM(COST) COST,MAX([Accum Depr])[Accum Depr],MAX([Book value]) [Book value]
FROM faa
GROUP BY
faa.AssetID, faa.AssetNumber, faa.Status, faa.Dept, faa.Class, faa.Description, faa.PropertyNumber,faa.AcquiredDateTime
Premium Content
You need an Expert Office subscription to comment.Start Free Trial