July 31, 2020 at 2:26 pm
the TOP 1 doesn't works and this is the reason why I'm here. If there's a query to do this, and you want help me, good.....the rest is my problem. How to adjust the name, the position, the data and other.
Thanks
TOP(1) does work but you need to use it inside a CROSS APPLY.
This has been said to you multiple times. What don't you understand about it?
July 31, 2020 at 2:35 pm
;with
details_summary_cte(Manage_GUID, details_row_count) as (
select Manage_GUID,
count(*) /* ... other aggregate functions */
from dbo.Details
group by Manage_GUID),
payments_summary_cte(Manage_GUID, payments_row_count) as (
select Manage_GUID,
count(*) /* ... other aggregate functions */
from dbo.Payments
group by Manage_GUID)
select
m.ID, m.[Type], m.Customer, m.[Date],
dsc.details_row_count,
psc.payments_row_count
from
dbo.Manage m
join
details_summary_cte dsc ON m.[GUID] = dsc.Manage_GUID
join
payments_summary_cte psc on m.[GUID] = psc.Manage_GUID;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 31, 2020 at 5:52 pm
This is an absolute guess - since you have not provided any sample data to work with...
Select Top 1 With Ties
m.ID
, m.Type
, m.Customer
, m.Date
, d.Order
, d.Order_Date
, p.Amount
, p.PayDate
, p.PayState
From dbo.Manage m
Inner Join dbo.Details d On d.GUID = d.Manage_GUID
Inner Join dbo.Payments p On p.Manage_GUID = d.GUID
Order By
row_number() over(Partition By d.GUID Order By p.PayDate desc);
I am assuming you would want the latest payment date per GUID - but if not you can adjust the partition in the row_number (or even rank/dense_rank if needed).
With that said - if you want accurate help for a problem you need to define the problem in a way that is understandable. The best way to do that in a forum is to provide sample data (create a temp table - provide insert statements for sample data) and a query that utilizes the sample data up to where you are having an issue. By providing this...you allow those of us who volunteer our time (in many cases - very valuable time) to help you resolve your issue.
For future reference - please refer to the link in my signature to review how to post questions to get better answers.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 1, 2020 at 7:11 pm
Let me step back, why doesn't top 1 work? This will get you a row from Manage and a matching from both Details and Payments. If you determine that, you might be able to solve this, or ask a better question.
A number of people have asked you for some DDL or linked to a explanation. We are looking for something like this.
DROP TABLE Manage
DROP TABLE Detail
DROP TABLE Payment
go
CREATE TABLE Manage
( ManageID UNIQUEIDENTIFIER
, ManageName VARCHAR(10));
GO
CREATE TABLE Detail
( DetailID INT
, ManageID UNIQUEIDENTIFIER
, DetailName VARCHAR(10));
GO
CREATE TABLE Payment
( PaymentID INT
, ManageID UNIQUEIDENTIFIER
, PaymentDate DATE
, PaymentAmount NUMERIC(10, 4));
GO
DECLARE @n UNIQUEIDENTIFIER = NEWID();
INSERT Manage SELECT @n, 'Steve';
INSERT Detail VALUES (1, @n, 'First'), (2, @n, 'Second');
INSERT Payment VALUES (1, @n, '2020-01-01', 100);
SELECT @n = NEWID();
INSERT Manage SELECT @n, 'Andy';
INSERT Detail VALUES (3, @n, 'First'), (4, @n, 'Third');
INSERT Payment VALUES (2, @n, '2020-01-01', 100), (3, @n
, '2020-02-01', 200);
Tables and data. Then, include your view and explain what is wrong in the result.
Your writing is not explaining the issue.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply