One MS SQL Query needed
€8-30 EUR
K zaplacení v momentě doručení
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
Identifikační číslo projektu: #17650559
O projektu
Uděleno uživateli:
Low bid bc of very strong knowledge of SQL Queries, Views, Sps, Triggers, UDFs, Static and Dynamic Queries, Can complete shortly
10 Freelnceři na tento projekt zveřejňují nabídky v průměru €20
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 Další
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 Další