March 5, 2018 at 8:00 am
I hope I explain this clearly, here is the situation. I have a prior month and current month change report to create. What they want is the most current record from this month (which I have) and what I'm having trouble with is getting the latest from Prior month (meaning the one that is the closest and or the end of the prior month) so for example Current Month would be anything in March to date and Prior month would be anything less than or equal to 2/28/2018'
So if I was looking at today (3/5/2018) would be the current month and (2/28/2018) would be prior month. The issue that i'm having is when I try getting the maxdate for the PriorMonth it's giving me multiple Max dates. Here is a very condensed recordset to look at. I need to be able to get only the last date which is DS_End_Date field in the record set below
If(OBJECT_ID('tempdb..#temp123') Is Not Null)
Begin
Drop Table #Temp123
End
Create Table #temp123 (
C_SS_KEY nvarchar(13)
,DS_Start_date Datetime2
,DS_End_Date datetime2
,CTNumber nvarchar(7)
)
insert into #temp123 values('614-P085199-P', '2017-07-28 16:15:15.0000000','2017-09-19 23:00:05.0000000','P085199')
insert into #temp123 values('614-P085199-P', '2017-09-19 23:00:05.0000000','2018-02-12 19:00:48.6300000','P085199')
insert into #temp123 values('614-P085199-P', '2018-02-12 19:00:48.6300000','2018-02-15 19:00:48.6300000','P085199')
Select * from #Temp123
Any help would be great. Thanks
March 5, 2018 at 8:05 am
Disregard please. I need to get a better example the max works on this for some reason.
March 5, 2018 at 8:25 am
Posting this since I've already written it
😎
Is this what you are looking for?
If(OBJECT_ID('tempdb..#temp123') Is Not Null)
Begin
Drop Table #Temp123
End
Create Table #temp123 (
C_SS_KEY nvarchar(13)
,DS_Start_date Datetime2
,DS_End_Date datetime2
,CTNumber nvarchar(7)
)
insert into #temp123 values('614-P085199-P', '2017-07-28 16:15:15.0000000','2017-09-19 23:00:05.0000000','P085199')
insert into #temp123 values('614-P085199-P', '2017-09-19 23:00:05.0000000','2018-02-12 19:00:48.6300000','P085199')
insert into #temp123 values('614-P085199-P', '2018-02-12 19:00:48.6300000','2018-02-15 19:00:48.6300000','P085199')
;WITH BASE_DATA AS
(
SELECT
T.C_SS_KEY
,T.DS_Start_date
,T.DS_End_Date
,T.CTNumber
,MONTH(T.DS_End_Date) AS MONTH_NO
FROM #Temp123 T
)
SELECT
BD.C_SS_KEY
,MIN(BD.DS_Start_date) AS DS_Start_date
,MAX(BD.DS_End_Date) AS DS_End_Date
,BD.CTNumber
FROM BASE_DATA BD
GROUP BY BD.C_SS_KEY
,BD.CTNumber
,BD.MONTH_NO
;
Output
C_SS_KEY DS_Start_date DS_End_Date CTNumber
------------- --------------------------- --------------------------- --------
614-P085199-P 2017-09-19 23:00:05.0000000 2018-02-15 19:00:48.6300000 P085199
614-P085199-P 2017-07-28 16:15:15.0000000 2017-09-19 23:00:05.0000000 P085199
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply