September 12, 2018 at 6:13 am
Hello
I have the below table which has the records of the payments, I want to select the kids whom last "EndDate" payments are lower than the current date
use the sql query to create the table then copy and past the below text in the table using MS Sql Server Management studio
CREATE TABLE [dbo].[Payments](
[Bill_ID] [int] IDENTITY(1,1) NOT NULL,
[KidID] [int] NOT NULL,
[PeriodFrom] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[PaidBy] [nvarchar](50) NULL,
[MonthlyFees] [int] NULL,
[PaidMoney] [int] NULL,
[BusFees] [int] NULL,
[Uniform] [int] NULL,
[Books] [int] NULL,
[Total] [int] NOT NULL,
[Remains] [int] NOT NULL,
[Comments] [nvarchar](200) NULL,
[IssuedBy] [int] NOT NULL,
[IssueDate] [datetime2](7) NOT NULL,
[EditedBy] [nvarchar](50) NULL,
[EditTime] [datetime2](7) NULL,
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[Bill_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
11 22 2017-11-22 2017-12-25 Mahmoud 1000 520 20 0 0 0 500 qwe 2 2017-06-22 14:03:36.9930000 11 2017-11-22 15:20:07.4400000
12 21 2017-11-22 2017-12-22 9999 1000 0 0 0 9999 8999 nnn 11 2017-11-22 16:02:12.9630000 11 2018-04-25 23:47:21.2270000
13 23 2018-03-18 2018-04-18 Mahmoud 200 350 50 50 50 350 0 2 2018-03-18 13:21:24.9300000 NULL NULL
14 10 2018-03-28 2018-04-28 200 0 50 0 0 250 250 c 11 2018-03-28 07:04:53.1430000 2 2018-04-26 00:10:19.5130000
15 18 2018-03-28 2018-04-28 Father 200 0 0 0 0 200 200 2 2018-03-28 07:06:39.4030000 NULL NULL
16 21 2018-03-28 2018-04-28 100 120 0 20 0 120 0 xxx 11 2018-03-28 07:27:54.3430000 2 2018-04-25 23:22:27.2230000
17 20 2018-03-28 2018-04-28 Father 100 250 50 80 50 280 30 2 2018-03-28 07:57:02.7100000 11 2018-03-28 07:59:43.7670000
18 26 2018-03-28 2018-04-28 200 320 50 50 20 320 0 11 2018-03-28 08:12:49.0070000 NULL NULL
19 21 2018-03-30 2018-04-30 Mother 100 150 0 30 20 150 0 حححححح 2 2018-03-30 11:42:52.5970000 2 2018-03-30 11:43:51.1270000
20 18 2018-04-25 2018-05-25 200 200 0 0 0 200 0 2 2018-04-25 23:57:40.7270000 NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
September 12, 2018 at 8:31 am
How about this:
SELECT KidID, MAX(EndDate) as MaxEndDate
FROM [dbo].[Payments]
WHERE EndDate < GETDATE()
GROUP BY KidID;
September 12, 2018 at 8:43 am
Mando_eg - Wednesday, September 12, 2018 6:13 AMHello
I have the below table which has the records of the payments, I want to select the kids whom last "EndDate" payments are lower than the current dateuse the sql query to create the table then copy and past the below text in the table using MS Sql Server Management studio
CREATE TABLE [dbo].[Payments](
[Bill_ID] [int] IDENTITY(1,1) NOT NULL,
[KidID] [int] NOT NULL,
[PeriodFrom] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[PaidBy] [nvarchar](50) NULL,
[MonthlyFees] [int] NULL,
[PaidMoney] [int] NULL,
[BusFees] [int] NULL,
[Uniform] [int] NULL,
[Books] [int] NULL,
[Total] [int] NOT NULL,
[Remains] [int] NOT NULL,
[Comments] [nvarchar](200) NULL,
[IssuedBy] [int] NOT NULL,
[IssueDate] [datetime2](7) NOT NULL,
[EditedBy] [nvarchar](50) NULL,
[EditTime] [datetime2](7) NULL,
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[Bill_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]11 22 2017-11-22 2017-12-25 Mahmoud 1000 520 20 0 0 0 500 qwe 2 2017-06-22 14:03:36.9930000 11 2017-11-22 15:20:07.4400000
12 21 2017-11-22 2017-12-22 9999 1000 0 0 0 9999 8999 nnn 11 2017-11-22 16:02:12.9630000 11 2018-04-25 23:47:21.2270000
13 23 2018-03-18 2018-04-18 Mahmoud 200 350 50 50 50 350 0 2 2018-03-18 13:21:24.9300000 NULL NULL
14 10 2018-03-28 2018-04-28 200 0 50 0 0 250 250 c 11 2018-03-28 07:04:53.1430000 2 2018-04-26 00:10:19.5130000
15 18 2018-03-28 2018-04-28 Father 200 0 0 0 0 200 200 2 2018-03-28 07:06:39.4030000 NULL NULL
16 21 2018-03-28 2018-04-28 100 120 0 20 0 120 0 xxx 11 2018-03-28 07:27:54.3430000 2 2018-04-25 23:22:27.2230000
17 20 2018-03-28 2018-04-28 Father 100 250 50 80 50 280 30 2 2018-03-28 07:57:02.7100000 11 2018-03-28 07:59:43.7670000
18 26 2018-03-28 2018-04-28 200 320 50 50 20 320 0 11 2018-03-28 08:12:49.0070000 NULL NULL
19 21 2018-03-30 2018-04-30 Mother 100 150 0 30 20 150 0 ØØØØØØ 2 2018-03-30 11:42:52.5970000 2 2018-03-30 11:43:51.1270000
20 18 2018-04-25 2018-05-25 200 200 0 0 0 200 0 2 2018-04-25 23:57:40.7270000 NULL NULL
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
Please take the time to setup your sample data as an insert statement using the TVC format instead of relying on us doing it for you. You did provide the DDL for the table, which was very good.
September 12, 2018 at 9:55 am
laurie-789651 - Wednesday, September 12, 2018 8:31 AMHow about this:
SELECT KidID, MAX(EndDate) as MaxEndDate
FROM [dbo].[Payments]
WHERE EndDate < GETDATE()
GROUP BY KidID;
That will evaluate the condition before finding the max value. I think that he wants to find the max value and then evaluate the condition.
SELECT KidID, MAX(EndDate) as MaxEndDate
FROM [dbo].[Payments]
GROUP BY KidID;
HAVING MAX(EndDate) < GETDATE()
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 12, 2018 at 2:32 pm
Thanks drew.allen,
Thats exactly what I want
September 12, 2018 at 2:37 pm
drew.allen - Wednesday, September 12, 2018 9:55 AMlaurie-789651 - Wednesday, September 12, 2018 8:31 AMHow about this:
SELECT KidID, MAX(EndDate) as MaxEndDate
FROM [dbo].[Payments]
WHERE EndDate < GETDATE()
GROUP BY KidID;That will evaluate the condition before finding the max value. I think that he wants to find the max value and then evaluate the condition.
SELECT KidID, MAX(EndDate) as MaxEndDate
FROM [dbo].[Payments]
GROUP BY KidID;
HAVING MAX(EndDate) < GETDATE()
Goddamn it Drew, why were you using the brain when I needed it? I've done this several times lately:
SELECT KidID, EndDate as MaxEndDate
FROM [dbo].[Payments] outerPayments
WHERE EndDate = (
SELECT MAX(EndDate)
FROM [dbo].[Payments] innerPayments
WHERE innerPayments.KidID = outerPayments.KidID
AND innerPayments.EndDate < GETDATE()
);
.....now I have to go track it down....
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply