Sub-Query SQL server

Question

Sub-Query SQL server

Navigation

#1 by (2 votes)

0

I'm new in these parts.

I'm doing an SQL query, which started as a girl and ended up making my life more complicated.

I have something like this:
Two tables.
One has data on costs, date and ID, the other has date and exchange rate / Currency.

Within the main selection, I have to take the cost and divide it by the exchange rate, with the date where the registration was made.

SELECT Nro_id, fecha, 
costo $, 
(costo/ Select top 1(cambio) from tabla2 LEFT JOIN 
tabla1.fecha=tabla2.fecha) 'Costo U$'
FROM tabla1

The problem I have is that the subquery brings me the first type of change and not the date.

Could you give me a hand? I leave the structure of the two tables.

table 1:

CREATE TABLE [dbo].[OT_Control](
    [ID_Nro] [int] IDENTITY(1,1) NOT NULL,
    [OT_Nro] [int] NOT NULL,
    [Created_Date] [date] NULL,
    [Completed_Date] [date] NULL, (Fecha para comparar)
    [Partial_Date] [date] NULL,
    [Employee] [varchar](200) NULL,
    [Contractor] [varchar](200) NULL,
    [Position] [varchar](100) NULL,
    [Company] [varchar](120) NULL,
    [Equipment] [varchar](200) NOT NULL,
    [Planned_Hours] [float] NOT NULL,
    [Actual_Hours] [float] NOT NULL,
    [Cost] [float] NULL, (Lo que deberia dividir por el cambio de tabla2)
    [Details] [varchar](200) NOT NULL,
    [Clasificación_OT] [varchar](100) NOT NULL,
    [Nro_Obra] [int] NULL,
    [Tipo_Servicio] [varchar](100) NULL,
    [Nro_RQ] [varchar](100) NULL,
    [Asset_Number] [varchar](50) NOT NULL,
    [Alias] [varchar](50) NULL,
    [Nro_IR] [int] NULL,
    [Item_IR] [int] NULL,
    [Active] [bit] NOT NULL,
    [Fech_created] [datetime] NULL,
    [Fech_update] [datetime] NULL,
    [user_edit] [varchar](20) NULL,
    [MoID] [int] NULL,
    [Clasificacion_Tarea] [varchar](50) NULL,
 CONSTRAINT [PK_OT_Control] PRIMARY KEY CLUSTERED 
(
    [ID_Nro] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Table 2:

CREATE TABLE [dbo].[exchangeRateHistory](
    [exId] [int] IDENTITY(1,1) NOT NULL,
    [exMoneyId] [int] NOT NULL,
    [exRate] [money] NULL,
    [exRateSales] [money] NULL,
    [exMoneyOriginalId] [int] NOT NULL,
    [exDate] [int] NOT NULL,
    [exDateLoad] [int] NULL,
    [exHour] [varchar](10) NULL,
    [exUser] [varchar](50) NULL,
 CONSTRAINT [PK_exchangeRateHistory] PRIMARY KEY CLUSTERED 
(
    [exId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

sql sql-server

asked by Nico Difeo 15.03.2018 в 18:28

source

1 answer

java.util.Listandroid.widget.ImageView "is not supported error android Records with Arraylist java

score 2 · Accepted Answer

The idea is .. but not entirely ..

To begin, let's write the correct query:

SELECT 
    Nro_id, 
    fecha, 
    costo AS '$', 
    (costo / 
        (Select cambio from tabla2 WHERE tabla1.fecha=tabla2.fecha)) AS 'Costo U$'
FROM 
    tabla1

And now .. let's see why ... the idea you had, you had to bring a record of the table2 .. but, your way of bringing it was ambiguous.

In general to do these things, I recommend always doing the query by pieces. You did not even know I was returning this:

Select top 1(cambio) from tabla2 LEFT JOIN 
tabla1.fecha=tabla2.fecha

Because if you had known, you would know that what it does, is to mix the two tables together by date, and then return the first record. What is anyone who has found.

Then what you really wanted was:

Select cambio from tabla2 WHERE tabla1.fecha=tabla2.fecha

always taking into account, that if it were a test, you would have matched table2.date on any date;)