t-sqlcursorcommon-table-expressionlagannuity
Efficient alternate to the cursor in TSQL 2012 to get last value for payment schedule calculation
I am trying to derive an annuity payment plan. I know there are some solution available online but as I need to make changes to monthly payment amounts, I can't use those solutions and due to security policies I can't install new packages as well. Right now I am struggling to calculate the monthly opening balance as it depends on the capital calculated in the last row. Is there a more efficient way to do this other then the cursor. I have like 30 million rows right now.
Data I have:
This is a very simple example, there can be different rates, different monthly payments and dates missing. So I can't use the available solution online.
End result I want:
where:
- Interest = Opening Balance x Rate Charge
- Capital = Monthly repayment - Interest
- Opening Balance = Last Opening Balance - Last Capital
I have tried to use LAG but it does not work as LAG is a deterministic function.
Here is the script for the data with account.
CREATE TABLE [temp_da_test].[example_3](
[Account] [nvarchar](255) NULL,
[Month] [date] NULL,
[Rate Charge] [float] NULL,
[Opening Balance] [money] NULL,
[Monthly Repayment] [money] NULL,
[Interest] [money] NULL,
[Capital] [money] NULL
) ON [PRIMARY]
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-06-30' AS Date), 0.014419, 2000.0000, 0.0000, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'A', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 35.8800, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-06-30' AS Date), 0.014419, 4000.0000, 0.0000, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
INSERT [temp_da_test].[example_3] ([Account], [Month], [Rate Charge], [Opening Balance], [Monthly Repayment], [Interest], [Capital]) VALUES (N'B', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 71.7600, NULL, NULL)
GO
Solution
Because your interest rate is not consistent and you cannot therefore use an interest rate calculation, you will not get any more efficient than going row by row.
Whilst not the greatest performance-wise, you will see a more efficient execution utilising a Recursive Common Table Expression (CTE), that joins to itself and checks the one row within the derived table with the next one incoming, which in your case is the next month you need to calculate the values for.
Because this row already exists in full within the CTE, you can reference the calculated values as you would in a cursor:
declare @t table(Account nvarchar(5),[Month] date null,[Rate Charge] float null,[Opening Balance] money null,[Monthly Repayment] money null);
insert into @t (Account, [Month], [Rate Charge], [Opening Balance], [Monthly Repayment]) values
(N'A', CAST(N'2005-06-30' AS Date), 0.014419, 2000.0000, 0.0000),(N'A', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 35.8800),(N'A', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 35.8800),(N'A', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 35.8800),(N'A', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 35.8800)
,(N'B', CAST(N'2005-06-30' AS Date), 0.014419, 4000.0000, 0.0000),(N'B', CAST(N'2005-07-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-08-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-09-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2005-10-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2005-11-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2005-12-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-01-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-02-28' AS Date), 0.013458, NULL, 71.7600),(N'B', CAST(N'2006-03-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-04-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-05-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-06-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-07-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-08-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-09-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-10-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2006-11-30' AS Date), 0.014419, NULL, 71.7600),(N'B', CAST(N'2006-12-31' AS Date), 0.0149, NULL, 71.7600),(N'B', CAST(N'2007-01-31' AS Date), 0.0149, NULL, 71.7600)
;
with d as
(
select Account
,[Month]
,[Rate Charge]
,[Opening Balance]
,[Monthly Repayment]
,row_number() over (order by Account,[Month]) as rn
from @t
)
,c as
(
-- Start with just the first row from your source data:
select top 1 Account
,[Month]
,[Rate Charge]
,cast([Opening Balance] as decimal(12,2)) as [Opening Balance]
,[Monthly Repayment]
,[Opening Balance] * [Rate Charge] as Interest
,[Monthly Repayment] - ([Opening Balance] * [Rate Charge]) as Capital
,rn
from d
order by [Month]
union all -- Then add on the next row until the end of the dataset is reached:
select d.Account
,d.[Month]
,d.[Rate Charge]
,case when c.Account <> d.Account -- When we hit a new account, restart the calculations:
then cast(d.[Opening Balance] as decimal(12,2))
else cast(c.[Opening Balance] - c.Capital as decimal(12,2))
end as [Opening Balance]
,d.[Monthly Repayment]
,case when c.Account <> d.Account
then d.[Opening Balance] * d.[Rate Charge]
else (c.[Opening Balance] - c.Capital) * d.[Rate Charge]
end as Interest
,case when c.Account <> d.Account
then d.[Monthly Repayment] - (d.[Opening Balance] * d.[Rate Charge])
else d.[Monthly Repayment] - ((c.[Opening Balance] - c.Capital) * d.[Rate Charge])
end as Capital
,d.rn
from c
join d
on c.rn = d.rn-1
)
select Account
,[Month]
,[Rate Charge]
,[Opening Balance]
,[Monthly Repayment]
,Interest
,Capital
from c
order by rn
option (maxrecursion 0) -- No matter how big the dataset is.
Output:
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
| Account | Month | Rate Charge | Opening Balance | Monthly Repayment | Interest | Capital |
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
| A | 2005-06-30 | 0.014419 | 2000.00 | 0.00 | 28.838 | -28.838 |
| A | 2005-07-31 | 0.0149 | 2028.84 | 35.88 | 30.2296862 | 5.6503138 |
| A | 2005-08-31 | 0.0149 | 2023.19 | 35.88 | 30.14552632438 | 5.73447367562001 |
| A | 2005-09-30 | 0.014419 | 2017.46 | 35.88 | 29.0896912340712 | 6.79030876592877 |
| A | 2005-10-31 | 0.0149 | 2010.67 | 35.88 | 29.9589783993877 | 5.92102160061234 |
| A | 2005-11-30 | 0.014419 | 2004.75 | 35.88 | 28.9064755195408 | 6.97352448045923 |
| A | 2005-12-31 | 0.0149 | 1997.78 | 35.88 | 29.7668694852412 | 6.11313051475884 |
| A | 2006-01-31 | 0.0149 | 1991.67 | 35.88 | 29.6758363553301 | 6.20416364466991 |
| A | 2006-02-28 | 0.013458 | 1985.47 | 35.88 | 26.72039922567 | 9.15960077432997 |
| A | 2006-03-31 | 0.0149 | 1976.31 | 35.88 | 29.4470249484625 | 6.43297505153752 |
| A | 2006-04-30 | 0.014419 | 1969.88 | 35.88 | 28.4036568227319 | 7.47634317726812 |
| A | 2006-05-31 | 0.0149 | 1962.40 | 35.88 | 29.2398144866587 | 6.64018551334129 |
| A | 2006-06-30 | 0.014419 | 1955.76 | 35.88 | 28.2001007650831 | 7.67989923491687 |
| A | 2006-07-31 | 0.0149 | 1948.08 | 35.88 | 29.0263935013997 | 6.85360649860026 |
| A | 2006-08-31 | 0.0149 | 1941.23 | 35.88 | 28.9242732631709 | 6.95572673682915 |
| A | 2006-09-30 | 0.014419 | 1934.27 | 35.88 | 27.8903007461817 | 7.98969925381834 |
| A | 2006-10-31 | 0.0149 | 1926.28 | 35.88 | 28.7015764811181 | 7.1784235188819 |
| A | 2006-11-30 | 0.014419 | 1919.10 | 35.88 | 27.6715256312812 | 8.20847436871876 |
| A | 2006-12-31 | 0.0149 | 1910.89 | 35.88 | 28.4722837319061 | 7.40771626809391 |
| A | 2007-01-31 | 0.0149 | 1903.48 | 35.88 | 28.3618860276054 | 7.5181139723946 |
| B | 2005-06-30 | 0.014419 | 4000.00 | 0.00 | 57.676 | -57.676 |
| B | 2005-07-31 | 0.0149 | 4057.68 | 71.76 | 60.4593724 | 11.3006276 |
| B | 2005-08-31 | 0.0149 | 4046.38 | 71.76 | 60.29105264876 | 11.46894735124 |
| B | 2005-09-30 | 0.014419 | 4034.91 | 71.76 | 58.1793824681425 | 13.5806175318575 |
| B | 2005-10-31 | 0.0149 | 4021.33 | 71.76 | 59.9178077987753 | 11.8421922012247 |
| B | 2005-11-30 | 0.014419 | 4009.49 | 71.76 | 57.8128047006505 | 13.9471952993495 |
| B | 2005-12-31 | 0.0149 | 3995.54 | 71.76 | 59.5335877900397 | 12.2264122099603 |
| B | 2006-01-31 | 0.0149 | 3983.31 | 71.76 | 59.3513724580716 | 12.4086275419284 |
| B | 2006-02-28 | 0.013458 | 3970.90 | 71.76 | 53.4403906705407 | 18.3196093294593 |
| B | 2006-03-31 | 0.0149 | 3952.58 | 71.76 | 58.8934478209911 | 12.8665521790089 |
| B | 2006-04-30 | 0.014419 | 3939.71 | 71.76 | 56.8067282041309 | 14.9532717958691 |
| B | 2006-05-31 | 0.0149 | 3924.76 | 71.76 | 58.4788752502416 | 13.2811247497585 |
| B | 2006-06-30 | 0.014419 | 3911.48 | 71.76 | 56.3996139022332 | 15.3603860977668 |
| B | 2006-07-31 | 0.0149 | 3896.12 | 71.76 | 58.0521822471433 | 13.7078177528567 |
| B | 2006-08-31 | 0.0149 | 3882.41 | 71.76 | 57.8479415154824 | 13.9120584845176 |
| B | 2006-09-30 | 0.014419 | 3868.50 | 71.76 | 55.7798718187117 | 15.9801281812883 |
| B | 2006-10-31 | 0.0149 | 3852.52 | 71.76 | 57.4025460900988 | 14.3574539099012 |
| B | 2006-11-30 | 0.014419 | 3838.16 | 71.76 | 55.3424657520731 | 16.4175342479269 |
| B | 2006-12-31 | 0.0149 | 3821.74 | 71.76 | 56.9439627397059 | 14.8160372602941 |
| B | 2007-01-31 | 0.0149 | 3806.92 | 71.76 | 56.7231670448216 | 15.0368329551784 |
+---------+------------+-------------+-----------------+-------------------+------------------+------------------+
- Replacing RAISERROR with THROW
- Paging with TSQL, MVC3 and SubSonic?
- Problem with SQL Query Tracking
- How can I get TSQL from easy MS Access SQL with little to no handiwork?
- Each GROUP BY expression must contain at least one column that is not an outer reference
- How do I perform a GROUP BY on an aliased column in SQL Server?
- SQL Server pivoting on data without an aggregate
- SQL Server PRINT SELECT (Print a select query result)?
- "Dynamic" tables in SQL?
- SQL Merge SOURCE order and OUTPUT order
- Output of update statement does not show correct row number
- Date and time range filter in SQL
- Replace duplicate spaces with a single space in T-SQL
- Best approach to remove time part of datetime in SQL Server
- Varchar datetime with EDT in it convert into datetime
- SQL Server Restore Error: Directory lookup for the file "db.mdf" failed
- Stopping repeated XML Headers
- How do I sort "SELECT INTO" queries that are built on top of each other by their "INTO" / "FROM" table links?
- Selecting a Common ID where that ID Matches Certain Criteria
- Error: Recursive common table expression 'EmailLog' does not contain a top-level UNION ALL operator
- SQL Procedure for booking flights
- T-SQL "partition by" results not as expected
- Select first occurrence of a value per group with condition based on the most recent record of group
- How to Join to first row
- Get 2 Digit Number For The Month
- Finding specific day in month SQL
- Why can I not find a foreign key using the OBJECT_ID() function?
- Is it possible to update rows from a key/value pair?
- LIKE and NULL in WHERE clause in SQL
- Remote query, view and where clause performance