One MS SQL Query needed

Fullført Lagt ut 5 år siden Betales ved levering
Fullført Betales ved levering

I have MS SQL table which I filter by MyAction, Type1, Type2, Type3, MyXml and I group it by RecordDate and need to get aggregated values (as specified bellow) together with first price (from first date in sequence) and last price (from last date in sequence). Table specification and my current query is bellow. My query does not return ok FirstPrice nad LastPrice - it returns all the same values for all days (they should be different). I know why - because these subqueries are written without grouping by days.

Need you to create new query and make it fastest as possible (I use MS SQL 2016 SP1) and this table has a lot of records.

CREATE TABLE MyTable

(

Id int IDENTITY(1,1) NOT NULL,

RecordDate datetime NOT NULL,

MyAction varchar(20) NOT NULL,

Price decimal(18, 4) NOT NULL,

Volume decimal(8, 2) NOT NULL,

Type1 varchar(20) NOT NULL,

Type2 varchar(20) NOT NULL,

Type3 varchar(20) NOT NULL,

MyXml xml NOT NULL

PRIMARY KEY CLUSTERED

(

Id ASC

)

)

SELECT CONVERT(date, RecordDate) as GroupedDate,

MIN(Price) as LowPrice,

MAX(Price) as HighPrice,

SUM(Price * Volume) / SUM(Volume) as AveragePrice,

SUM(Volume) as Volume,

(SELECT TOP 1 Price FROM MyTable WHERE MyAction <> 'Remove' AND CAST(MyXml as nvarchar(max)) LIKE '%SeqSpan="Single"%' AND Type1 = 10001126 AND Type2 = 10000104 AND Type3 = 175 ORDER BY RecordDate ASC) AS FirstPrice,

(SELECT TOP 1 Price FROM MyTable WHERE MyAction <> 'Remove' AND CAST(MyXml as nvarchar(max)) LIKE '%SeqSpan="Single"%' AND Type1 = 10001126 AND Type2 = 10000104 AND Type3 = 175 ORDER BY RecordDate DESC) AS LastPrice

FROM MyTable

WHERE MyAction <> 'Remove' AND

CAST(MyXml as nvarchar(max)) LIKE '%SeqSpan="Single"%' AND

Type1 = 10001126 AND

Type2 = 10000104 AND

Type3 = 175

GROUP BY CONVERT(date, RecordDate)

ORDER BY GroupedDate

Microsoft SQL Server MySQL SQL T-SQL (Transact Structures Query Language)

Prosjekt-ID: #17650559

Om prosjektet

10 bud Eksternt prosjekt Aktiv 5 år siden

Tildelt til:

znawazch

Low bid bc of very strong knowledge of SQL Queries, Views, Sps, Triggers, UDFs, Static and Dynamic Queries, Can complete shortly

€22 EUR på 1 dag
(3 omtaler)
1.7

10 frilansere byr i gjennomsnitt €20 for denne jobben

feninsoftech

Hi, I have 9 years experience in development and deployment that includes SQL Server, SSRS, SSIS and .NET. Involved in SQL Server activities like Installing, Migrations, Table Partitioning, Configuring maintenanc Mer

€29 EUR på 1 dag
(54 Omtaler)
6.7
ygenchik

Dear Employer, This is not a bid that I want to get - just a couple of observations based on your design. Since your are striving for speed, and I do not really know what is going on with your application, but he Mer

€8 EUR på 1 dag
(5 Omtaler)
4.9
gracefulwork

Hi I am Mysql expert lets start. Thank you. _

€25 EUR på 1 dag
(30 Omtaler)
4.6
saudagarljadhav

I have more than 3 years of exerience as sql devloper and mostly working sales report for us based comany. I am dealing with these kind of queries for daily basis. It will take less than 1Hr for me to solve this quer Mer

€15 EUR på 1 dag
(0 Omtaler)
0.0