[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS SQL need to insert calculated record

Posted on 2011-10-31
5
Medium Priority
?
231 Views
Last Modified: 2012-05-12
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_IndexFixings_Create_M] ******/
USE [QuoteBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[proc_Import_IndexFixings_Create_M]
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'
/******  ******/
0
Comment
Question by:HelpdeskJBC
  • 3
5 Comments
 
LVL 1

Accepted Solution

by:
tring_tring earned 1200 total points
ID: 37057373
The query you posted should work. Please post how is the desired output different from query output.

And you don't need to loop through as the query will give all the possible combinations. Though I am not aware of the business importance but I think check on priceCurrency and unit should be included in the query.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 37057383
Not 100% sure of the data source and requirements, but maybe something like this:
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 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

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 37057393
CORRECTION: Left out WHERE clause:

where indexlh in ('H', 'L')
0
 

Author Comment

by:HelpdeskJBC
ID: 37068786
@Scott:

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.IndexFixings_BAK'; column does not allow nulls. INSERT fails.
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.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37076279
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question