transactionDate
2005-07-15 16:12:46.000
2005-07-15 16:12:46.000
2005-07-15 16:19:22.000
2005-07-18 09:31:29.000
2005-07-18 09:31:29.000
2005-07-18 11:00:36.000
2005-07-18 11:00:36.000
2005-07-20 10:58:54.000
2005-07-20 14:21:49.000
2005-07-20 14:21:49.000
2005-07-20 04:04:59.000
2005-07-20 16:13:38.000
2005-07-20 16:13:38.000
2005-07-21 08:32:49.000
2005-07-21 08:32:49.000
2005-07-21 08:36:49.000
2005-07-21 08:45:13.000
2005-07-21 15:14:59.000
NULL
2005-07-22 14:03:08.000
2005-07-22 14:03:08.000
2005-07-25 09:07:57.000
2005-07-25 09:07:57.000
2005-07-25 11:43:37.000
2005-07-25 11:45:24.000
2005-07-25 11:46:52.000
2005-07-25 11:47:34.000
2005-07-25 11:48:25.000
2005-07-25 11:50:22.000
2005-07-25 12:39:06.000
2005-07-25 12:48:04.000
2005-07-25 12:48:59.000
2005-07-26 10:38:08.000
2005-07-26 10:38:08.000
2005-07-26 10:44:08.000
2005-07-26 10:44:08.000
2005-07-26 18:16:36.000
2005-07-26 18:16:36.000
2005-07-27 09:30:16.000
2005-07-29 10:51:09.000
2005-08-01 17:31:21.000
Select
'ZCNV'+ Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS BatchID,
'NCA' AS AcctCompany,
min([transactionDate]) AS ControlDate,
'Conversion'+ ' ' + 'Batch' + ' ' + Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS [Description],
max([transactionDate]) AS LastDate
FROM [ncausa].[dbo].[ams_trans]
Where transactionDate Is Not Null
Group By [transactionDate]
Order by BatchID
Select
'ZCNV'+ Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS BatchID,
'NCA' AS AcctCompany,
min([transactionDate]) AS ControlDate,
'Conversion'+ ' ' + 'Batch' + ' ' + Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS [Description],
max([transactionDate]) AS LastDate
FROM [ncausa].[dbo].[ams_trans]
Where transactionDate Is Not Null
Group By [transactionDate]
Order by BatchID
declare @a table(a datetime)
insert into @a values([ncausa].[dbo].[ams_trans].[transactionDate]),([ncausa].[dbo].[ams_trans].[transactionDate]),([ncausa].[dbo].[ams_trans].[transactionDate]),([ncausa].[dbo].[ams_trans].[transactionDate])
select a1 as controldt, (select min(a) from @a where datepart(month,a1)+1=datepart(month,a)) as lastdt,
(select min(a) from @a where datepart(month,a1)+1=datepart(month,a)) as postdt
from(
select a as a1,rank() over(order by a desc) rnk
from @a where datepart(mm,a) = (select datepart(MONTH,min(a)) from @a)
) t1
where rnk=1
Select
'ZCNV'+ Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS BatchID,
'NCA' AS AcctCompany,
min([transactionDate]) AS ControlDate,
'Conversion'+ ' ' + 'Batch' + ' ' + Cast(Right(DatePart(yy, [transactionDate]),2) AS nVARCHAR(2)) + right('0'+Cast(DatePart(mm,[transactionDate]) AS nvarchar(2)),2) AS [Description],
max([transactionDate]) AS LastDate
FROM [ncausa].[dbo].[ams_trans]
Where transactionDate Is Not Null
Group By [transactionDate]
Order by BatchID
transactionDate
2005-07-15 16:12:46.000
2005-07-15 16:12:46.000
2005-07-15 16:19:22.000
2005-07-18 09:31:29.000
2005-07-18 09:31:29.000
2005-07-18 11:00:36.000
2005-07-18 11:00:36.000
2005-07-20 10:58:54.000
2005-07-20 14:21:49.000
2005-07-20 14:21:49.000
2005-07-20 04:04:59.000
2005-07-20 16:13:38.000
2005-07-20 16:13:38.000
2005-07-21 08:32:49.000
2005-07-21 08:32:49.000
2005-07-21 08:36:49.000
2005-07-21 08:45:13.000
2005-07-21 15:14:59.000
2005-07-22 14:03:08.000
2005-07-22 14:03:08.000
2005-07-25 09:07:57.000
2005-07-25 09:07:57.000
2005-07-25 11:43:37.000
2005-07-25 11:45:24.000
2005-07-25 11:46:52.000
2005-07-25 11:47:34.000
2005-07-25 11:48:25.000
2005-07-25 11:50:22.000
2005-07-25 12:39:06.000
2005-07-25 12:48:04.000
2005-07-25 12:48:59.000
2005-07-26 10:38:08.000
2005-07-26 10:38:08.000
2005-07-26 10:44:08.000
2005-07-26 10:44:08.000
2005-07-26 18:16:36.000
2005-07-26 18:16:36.000
2005-07-27 09:30:16.000
2005-07-29 10:51:09.000
2005-08-01 17:31:21.000
Column: FirstDay
2005-07-15
Next Column: LastDay
2005-08-01
Next Column: PostDay
2005-08-01
Like:DECLARE @dt1 Datetime
DECLARE @dt2 Datetime
Select @dt1 = dbo.fncUtlDateBeginning(MIN(transactionDate),'mm'), @dt2 = dbo.fncUtlDateBeginning(MAX(transactionDate),'mm')
From ams_Trans
While @dt1 <= @dt2
Begin
Insert Into [NOAH2-NCA].dbo.tblNAsBatchControl(BatchID, AcctCompany, ControlDate, Description, LastDate, Posted, Assn)
Select 'ZCNV' + Right('0' + Convert(nvarchar, Year(@dt1)),2) + Right('0' + Convert(nvarchar, Month(@dt1)),2) AS BatchID,
'NCA' AS AcctCompany,
@dt1 AS [ControlDate],
'Conversion Batch' As [Description],
DateAdd(m,1,@dt1) AS LastDate,
DateAdd(m,1,@dt1) AS Posted,
'NCA' AS Assn
Set @dt1 = DateAdd(m,1,@dt1)
End