MS SQL need to insert calculated record
Our Problem:
We have a sql tabel on our server were we need to calculate ('l'+'h'/2)='m' and add to the existing table those m values. (See: "Our Stored Proceedure")
L= Low value
H= High value
M= Mid value
In the table there are besides 'l' and 'h' as well 'u' and 'c' records
With the stored proceedure we couldn't loop through all these records in the table.
Please help us with that one.
Our Tabel Design:
EvalDate, smalldatetime, Unchecked
IndexName, varchar(150), Unchecked
IndexLH, char(1), Unchecked
PriceFixing, float, Unchecked
PriceCurrency, varchar(3), Unchecked
Unit, varchar(10), Unchecked
Our Stored Proceedure:
/****** Object: StoredProcedure [dbo].[proc_Import_IndexFi
USE [QuoteBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_Import_IndexFi
as
insert into dbo.IndexFixings_BAK (evaldate, indexname, indexlh, pricefixing, pricecurrency, unit)
select h.Evaldate, h.indexname, 'M', (l.pricefixing + h.pricefixing) / 2, h.pricecurrency, h.unit from
dbo.IndexFixings_BAK L
inner join dbo.IndexFixings_BAK H on l.indexname = h.indexname and l.evaldate = h.evaldate
where l.indexlh = 'L' and h.indexlh = 'H'
/****** ******/
We have a sql tabel on our server were we need to calculate ('l'+'h'/2)='m' and add to the existing table those m values. (See: "Our Stored Proceedure")
L= Low value
H= High value
M= Mid value
In the table there are besides 'l' and 'h' as well 'u' and 'c' records
With the stored proceedure we couldn't loop through all these records in the table.
Please help us with that one.
Our Tabel Design:
EvalDate, smalldatetime, Unchecked
IndexName, varchar(150), Unchecked
IndexLH, char(1), Unchecked
PriceFixing, float, Unchecked
PriceCurrency, varchar(3), Unchecked
Unit, varchar(10), Unchecked
Our Stored Proceedure:
/****** Object: StoredProcedure [dbo].[proc_Import_IndexFi
USE [QuoteBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_Import_IndexFi
as
insert into dbo.IndexFixings_BAK (evaldate, indexname, indexlh, pricefixing, pricecurrency, unit)
select h.Evaldate, h.indexname, 'M', (l.pricefixing + h.pricefixing) / 2, h.pricecurrency, h.unit from
dbo.IndexFixings_BAK L
inner join dbo.IndexFixings_BAK H on l.indexname = h.indexname and l.evaldate = h.evaldate
where l.indexlh = 'L' and h.indexlh = 'H'
/****** ******/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmm, looks like you have some pricecurrency values that are NULL. You will need to substitute the desired value for them in the query:
insert into dbo.IndexFixings_BAK (evaldate, indexname, indexlh, pricefixing, pricecurrency, unit)
select evaldate, indexname, 'M',
sum(case when indexlh in ('H', 'L') then pricefixing else 0 end) / 2,
max(case when indexlh = 'H' then COALESCE(pricecurrency, '?') end), --??
max(case when indexlh = 'H' then unit end) --??
from dbo.IndexFixings_BAK
--where evaldate >= dateadd(day, datediff(day, 0, getdate()), 0)
group by evaldate, indexname
insert into dbo.IndexFixings_BAK (evaldate, indexname, indexlh, pricefixing, pricecurrency, unit)
select evaldate, indexname, 'M',
sum(case when indexlh in ('H', 'L') then pricefixing else 0 end) / 2,
max(case when indexlh = 'H' then COALESCE(pricecurrency, '?') end), --??
max(case when indexlh = 'H' then unit end) --??
from dbo.IndexFixings_BAK
--where evaldate >= dateadd(day, datediff(day, 0, getdate()), 0)
group by evaldate, indexname
ASKER
The result of that query is:
---
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'PriceCurrency', table 'QuoteBase.dbo.IndexFixing
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
---
The only difference of L and H are in IndexLH and Pricefixings, the other rows with different values then L and H needs to be skipped.
@tring
It's not a query but a stored proceedure. What I need is a query which goes through all those records in IndexFixings_BAK and uses this proceedure. I tried it but it didn't do anything.