July 11, 2018 at 4:08 pm
--Desired output (Only those rows with the latest date):
1657 Nov 10 2009 12:00AM 150.00
1773 Jun 7 2010 12:00AM 125.00
1817 Jun 2 2008 12:00AM 109.00
1825 Feb 14 2006 12:00AM 99.00
1828 Jul 15 2009 12:00AM 135.00
I tried Select ProfileID, Max(Date), Paid, even added a Distinct to the ProfileID, but keep getting duplicate ProfileID's.
TIA
July 11, 2018 at 4:20 pm
JaybeeSQL - Wednesday, July 11, 2018 4:08 PMHi all,
I'm trying to get the latest date for every distinct value in a column:
CREATE TABLE [dbo].[Ledger](
[ID] [int] NOT NULL,
[ProfileId] [int] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL) ON [PRIMARY]GO
--Sample Data
--Select Top 8 ProfileId, Date, Paid
--From Ledger As L
1657 Nov 10 2009 12:00AM 150.00
1657 May 8 2009 12:00AM 150.00
1773 Jun 7 2010 12:00AM 125.00
1773 Apr 19 2006 12:00AM 99.00
1817 Jun 2 2008 12:00AM 109.00
1825 Feb 14 2006 12:00AM 99.00
1828 Dec 3 2008 12:00AM 135.00
1828 Jul 15 2009 12:00AM 135.00--Desired output (Only those rows with the latest date):
1657 Nov 10 2009 12:00AM 150.00
1773 Jun 7 2010 12:00AM 125.00
1817 Jun 2 2008 12:00AM 109.00
1825 Feb 14 2006 12:00AM 99.00
1828 Jul 15 2009 12:00AM 135.00
I tried Select ProfileID, Max(Date), Paid, even added a Distinct to the ProfileID, but keep getting duplicate ProfileID's.TIA
Try this:
WITH Base AS (
SELECT
[l].[ProfileId]
, [l].[Date]
, [l].[Paid]
, [rn] = ROW_NUMBER() OVER (PARTITION BY [l].[ProfileId] ORDER BY [l].[Date] DESC)
FROM
[dbo].[Ledger] AS [l]
)
SELECT
.[ProfileId]
, .[Date]
, .[Paid]
FROM
[base] AS
WHERE
.[rn] = 1;
July 11, 2018 at 5:03 pm
Thanks bud, that code works stand-alone, now I'm trying to do a couple of joins to filter;
Inner Join personalprofiles PP
ON
L.ProfileId = PP.ID
Inner Join CurrentStudentMembership As CSM
on
CSM.[First name] = PP.[Fname]
And CSM.[Last name] = PP.[Lname]
Where [l].[Paid] > 0
But wherever I put them, I keep getting
Msg 4104, Level 16, State 1, Line 1160
The multi-part identifier "CSM.First name" could not be bound.
Msg 4104, Level 16, State 1, Line 1161
The multi-part identifier "PP.Fname" could not be bound.
Msg 4104, Level 16, State 1, Line 1162
The multi-part identifier "CSM.Last name" could not be bound.
Msg 4104, Level 16, State 1, Line 1163
The multi-part identifier "PP.Lname" could not be bound.
July 11, 2018 at 5:22 pm
JaybeeSQL - Wednesday, July 11, 2018 5:03 PMThanks bud, that code works stand-alone, now I'm trying to do a couple of joins to filter;Inner Join personalprofiles PP
ON
L.ProfileId = PP.ID
Inner Join CurrentStudentMembership As CSM
on
CSM.[First name] = PP.[Fname]
And CSM.[Last name] = PP.[Lname]
Where [l].[Paid] > 0But wherever I put them, I keep getting
Msg 4104, Level 16, State 1, Line 1160
The multi-part identifier "CSM.First name" could not be bound.
Msg 4104, Level 16, State 1, Line 1161
The multi-part identifier "PP.Fname" could not be bound.
Msg 4104, Level 16, State 1, Line 1162
The multi-part identifier "CSM.Last name" could not be bound.
Msg 4104, Level 16, State 1, Line 1163
The multi-part identifier "PP.Lname" could not be bound.
Can't help without the entire query and the DDL for the tables.
July 11, 2018 at 5:24 pm
It's all good I cracked the joins, just had to repeat the joins twice as such...
WITH Base AS (
SELECT
CSM.[First name]
,PP.[Fname]
,CSM.[Last name]
,PP.[Lname]
,[l].[ProfileId]
, [l].[Date]
, [l].[Paid]
, [rn] = ROW_NUMBER() OVER (PARTITION BY [l].[ProfileId] ORDER BY [l].[Date] DESC)
FROM
[dbo].[Ledger] AS [l]
Inner Join personalprofiles PP
ON
L.ProfileId = PP.ID
Inner Join CurrentStudentMembership As CSM
on
CSM.[First name] = PP.[Fname]
And CSM.[Last name] = PP.[Lname]
Where [l].[Paid] > 0
)
SELECT
CSM.[First name]
,PP.[Fname]
,CSM.[Last name]
,PP.[Lname]
, .[ProfileId]
, .[Date]
, .[Paid]
FROM
[base] AS
Inner Join Ledger L
on .[ProfileId] = L.[ProfileId]
Inner Join personalprofiles PP
ON
L.ProfileId = PP.ID
Inner Join CurrentStudentMembership As CSM
on
CSM.[First name] = PP.[Fname]
And CSM.[Last name] = PP.[Lname]
Where [l].[Paid] > 0
and .[rn] = 1;
July 11, 2018 at 5:50 pm
JaybeeSQL - Wednesday, July 11, 2018 5:24 PMIt's all good I cracked the joins, just had to repeat the joins twice as such...WITH Base AS (
SELECT
CSM.[First name]
,PP.[Fname]
,CSM.[Last name]
,PP.[Lname]
,[l].[ProfileId]
, [l].[Date]
, [l].[Paid]
, [rn] = ROW_NUMBER() OVER (PARTITION BY [l].[ProfileId] ORDER BY [l].[Date] DESC)
FROM
[dbo].[Ledger] AS [l]
Inner Join personalprofiles PP
ON
L.ProfileId = PP.ID
Inner Join CurrentStudentMembership As CSM
on
CSM.[First name] = PP.[Fname]
And CSM.[Last name] = PP.[Lname]
Where [l].[Paid] > 0
)
SELECT
CSM.[First name]
,PP.[Fname]
,CSM.[Last name]
,PP.[Lname]
, .[ProfileId]
, .[Date]
, .[Paid]
FROM
[base] AS
Inner Join Ledger L
on .[ProfileId] = L.[ProfileId]
Inner Join personalprofiles PP
ON
L.ProfileId = PP.ID
Inner Join CurrentStudentMembership As CSM
on
CSM.[First name] = PP.[Fname]
And CSM.[Last name] = PP.[Lname]
Where [l].[Paid] > 0
and .[rn] = 1;
Try this instead:
WITH [Base] AS
(
SELECT
[l].[ProfileId]
, [l].[Date]
, [l].[Paid]
, [rn] = ROW_NUMBER() OVER (PARTITION BY
[l].[ProfileId]
ORDER BY
[l].[Date] DESC
)
FROM
[dbo].[Ledger] AS [l]
WHERE
[l].[Paid] > 0
)
SELECT
[CSM].[First name]
, [PP].[Fname]
, [CSM].[Last name]
, [PP].[Lname]
, .[ProfileId]
, .[Date]
, .[Paid]
FROM
[Base] AS
INNER JOIN [personalprofiles] [PP]
ON .[ProfileId] = [PP].[ID]
INNER JOIN [CurrentStudentMembership] AS [CSM]
ON [CSM].[First name] = [PP].[Fname]
AND [CSM].[Last name] = [PP].[Lname]
WHERE
.[rn] = 1;
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply