Previous Date

Question

Hi All,

I have a table call Census_Master. We have a census 3 times a year, in the table there is a list of ID's with the next to them. ie

GUID Date

9253000 17/05/2012

9256000 17/05/2012

9258000 17/05/2012

9253000 19/01/2012

9256000 19/01/2012

9258000 19/01/2012

Is a lot bigger with more dates etc but the pictures there.

I can select the most recent date by using the following code, I am creating a derived table:

SELECT     TOP (1) AS ReferenceDate
                       FROM          dbo.SchoolCensus_Master
                       ORDER BY ReferenceDate DESC

What I need to do is get the previous date. This will then be used to create a view which is pulled into access so colleagues can compare the current data with the previous data for validation purposes.

Any ideas?

Thanks in advance,

Steph

Answer 1

Steph

Are you using SQL Server 2012? There are tow great built in functions for this (lead, lag)

Now, do you want to get a previous date  from your above query or GETDATE()?

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Try

SELECT     guid, ReferenceDate, (select max(ReferenceDate) from dbo.SchoolCensus_Master b where b.guid<=a.guid) as Prev_date
                       FROM          dbo.SchoolCensus_Master a
                       ORDER BY ReferenceDate DESC

---

Many Thanks & Best Regards, Hua Min

Answer 3

Unfortunately not, I am using SQL Server 2005.

I want to get the previous date, the query above gets the most recent Census date. If I can get the previous date then I will be able to create a view of the previous data.

Answer 4

Hi,

See if this helps

--Sample data
declare @t table
(
  ID  int
 ,ReferenceDate datetime
)
 
insert into @t
select '9253000', '20120517'
union all
select '9256000', '20120517'
union all
select '9258000', '20120517'
union all 
select '9253000', '20120119'
union all
select '9256000', '20120119'
union all
select '9258000', '20120119'

-- Actual Query
; with CTE
as
( select ID
,ReferenceDate 
, ROW_NUMBER() over (partition by id order by ReferenceDate desc) as rn
from @t
)

select c1.ID
    ,  c1.ReferenceDate 
    ,  c3.ReferenceDate  as PreviousReferenceDate 
from CTE c1
outer apply ( select top 1 c2.ReferenceDate
              from CTE c2
              where c1.ID = c2.ID
              and c2.rn  > c1.rn
              order by c2.rn 
            ) c3
where c1.rn = 1

---

- Chintak (My Blog)

Answer 5

Unfortunately not, I am using SQL Server 2005.

I want to get the previous date, the query above gets the most recent Census date. If I can get the previous date then I will be able to create a view of the previous data.

Try my script above.

---

Many Thanks & Best Regards, Hua Min

Answer 6

create table #t (id int, dt datetime)

insert into #t values (9253000,'20120517')
insert into #t values (9256000,'20120517')
insert into #t values (9258000,'20120517')
insert into #t values (9253000,'20120119')
insert into #t values (9256000,'20120119')
insert into #t values (9258000,'20120119')

WITH cte
AS
(
SELECT     TOP (1) dt AS dt
                       FROM   #t
                       ORDER BY dt DESC
) SELECT TOP 1 #t.dt FROM #t JOIN cte ON #t.dt<cte.dt

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 7

I tried this, SQL turned it into this: 

(SELECT     TOP (100) PERCENT GUID, ReferenceDate,
                                                  (SELECT     MAX(ReferenceDate) AS Expr1
                                                    FROM          dbo.SchoolCensus_Master AS b
                                                    WHERE      (GUID <= GUID)) AS Prev_date
                       FROM          dbo.SchoolCensus_Master AS a
                       ORDER BY ReferenceDate DESC

I am a complete Novice, so I may of messed it up myself.

What if I created a table of distinct dates, would this help?

Answer 8

Try the below:

Drop table T1
create Table T1 (id int identity(1,1), GUIDN Bigint,gdate Date)
Insert into T1 Select 9253000 ,'05/17/2012'
Insert into T1 Select 9256000 ,'05/17/2012'
Insert into T1 Select 9258000 ,'05/17/2012'
Insert into T1 Select 9253000 ,'01/19/2012'
Insert into T1 Select 9256000 ,'01/19/2012'
Insert into T1 Select 9258000 ,'01/19/2012'
Insert into T1 Select 9253000 ,'06/19/2012'
Insert into T1 Select 9256000 ,'06/19/2012'
Insert into T1 Select 9258000 ,'06/19/2012'
;With cte
as
(
Select id,GUIDN,gdate, ROW_NUMBER() over(partition by guidn order by gdate desc) rn From T1
) 
Select * From cte A Where rn in (1,2)

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 9

My problem is the table is I have 350 schools over 18 Census so may take a while.

Answer 10

Help us by providing your output sample to help you better please.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 11

So at the moment I use the code below with a derived table in it that restricts the data to the latest Census. 

SELECT     dbo.xPupilTable_Master.PupilRollTableID AS PupilOnRollTableID, dbo.xPupilTable_Master.NativeID, 
                      dbo.xPupilTable_Master.PupilOnRollOrderSeqColumn, dbo.xPupilTable_Master.SchoolCensusTableID, dbo.xPupilTable_Master.UPN, 
                      dbo.xPupilTable_Master.FormerUPN, dbo.xPupilTable_Master.Surname, dbo.xPupilTable_Master.Forename, 
                      dbo.xPupilTable_Master.UniqueLearnerNumber, dbo.xPupilTable_Master.Middlenames, dbo.xPupilTable_Master.PreferredSurname, 
                      dbo.xPupilTable_Master.FormerSurname, dbo.xPupilTable_Master.DoB, dbo.xPupilTable_Master.FSMeligible, dbo.xPupilTable_Master.PartTime, 
                      dbo.xPupilTable_Master.EntryDate, dbo.xPupilTable_Master.TermlySessionsPossible, dbo.xPupilTable_Master.TermlySessionsAuthorised, 
                      dbo.xPupilTable_Master.TermlySessionsUnauthorised, dbo.xPupilTable_Master.AnnualSessionsPossible, 
                      dbo.xPupilTable_Master.AnnualSessionsAuthorised, dbo.xPupilTable_Master.AnnualSessionsUnauthorised, dbo.xPupilTable_Master.MissingAddress, 
                      dbo.xPupilTable_Master.HoursAtSetting, dbo.xPupilTable_Master.FundedHours, dbo.xPupilTable_Master.DuplicateNotFunded, 
                      dbo.xPupilTable_Master.MoveOffRollFlag, dbo.xPupilTable_Master.Gender, dbo.xPupilTable_Master.Ethnicity, 
                      dbo.xPupilTable_Master.EthnicitySource, dbo.xPupilTable_Master.Connexions, dbo.xPupilTable_Master.Language, 
                      dbo.xPupilTable_Master.EnrolStatus, dbo.xPupilTable_Master.ClassType, dbo.xPupilTable_Master.Boarder, dbo.xPupilTable_Master.NCyearLeaving, 
                      dbo.xPupilTable_Master.NCyearActual, dbo.xPupilTable_Master.SENprovision, dbo.xPupilTable_Master.ServiceChild, 
                      dbo.xPupilTable_Master.ServiceChildSource, dbo.xPupilTable_Master.T_Reason_I, dbo.xPupilTable_Master.T_Reason_M, 
                      dbo.xPupilTable_Master.T_Reason_R, dbo.xPupilTable_Master.T_Reason_S, dbo.xPupilTable_Master.T_Reason_T, 
                      dbo.xPupilTable_Master.T_Reason_H, dbo.xPupilTable_Master.T_Reason_F, dbo.xPupilTable_Master.T_Reason_E, 
                      dbo.xPupilTable_Master.T_Reason_C, dbo.xPupilTable_Master.T_Reason_G, dbo.xPupilTable_Master.T_Reason_U, 
                      dbo.xPupilTable_Master.T_Reason_O, dbo.xPupilTable_Master.T_Reason_N, dbo.xPupilTable_Master.Age_Category, 
                      dbo.xPupilTable_Master.SourceID, dbo.xPupilTable_Master.GandTindicator, dbo.xPupilTable_Master.ModeOfTravel
FROM         dbo.xPupilTable_Master INNER JOIN
                      dbo.SchoolCensus_Master AS SchoolCensus_Master_1 ON 
                      dbo.xPupilTable_Master.SchoolCensusTableID = SchoolCensus_Master_1.SchoolCensusTableID INNER JOIN
                          (SELECT     TOP (1) ReferenceDate
                            FROM          dbo.SchoolCensus_Master
                            ORDER BY ReferenceDate DESC) AS Date ON SchoolCensus_Master_1.ReferenceDate = Date.ReferenceDate
WHERE     (dbo.xPupilTable_Master.On_Or_Off_Roll = N'On')

I essentially want the same view, but for the previous Census Date. The table where the date is coming from holds records for all schools over the past 15 census. So the date is repeated for every school for every census. Would it be better for me to create a new table that just pulls out the date? 

Sorry if I haven't been overly clear. This is all new to me.

Answer 12

I now have a table that looks like this

ID CensusDate

1	17/05/2012 12:00:00
2	19/01/2012 12:00:00
3	06/10/2011 12:00:00
4	19/05/2011 12:00:00
5	20/01/2011 12:00:00
6	07/10/2010 12:00:00
7	20/05/2010 12:00:00
8	21/01/2010 12:00:00
9	01/10/2009 12:00:00
10	21/05/2009 12:00:00
11	15/01/2009 12:00:00
12	02/10/2008 12:00:00
13	15/05/2008 12:00:00
14	17/01/2008 12:00:00
15	04/10/2007 12:00:00
16	17/05/2007 00:00:00
17	18/01/2007 00:00:00
18	21/09/2006 00:00:00
19	18/05/2006 00:00:00
20	19/01/2006 00:00:00
NULL	NULL

Does this help?

Answer 13

I now have a table that looks like this

ID CensusDate

1	17/05/2012 12:00:00
2	19/01/2012 12:00:00
3	06/10/2011 12:00:00
4	19/05/2011 12:00:00
5	20/01/2011 12:00:00
6	07/10/2010 12:00:00
7	20/05/2010 12:00:00
8	21/01/2010 12:00:00
9	01/10/2009 12:00:00
10	21/05/2009 12:00:00
11	15/01/2009 12:00:00
12	02/10/2008 12:00:00
13	15/05/2008 12:00:00
14	17/01/2008 12:00:00
15	04/10/2007 12:00:00
16	17/05/2007 00:00:00
17	18/01/2007 00:00:00
18	21/09/2006 00:00:00
19	18/05/2006 00:00:00
20	19/01/2006 00:00:00
NULL	NULL

Does this help?

Can you repeat your question with a sample output that you are looking for?

It looks like you have deviated from what you asked initally now.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 14

So at the moment I use the code below with a derived table in it that restricts the data to the latest Census. 

SELECT     dbo.xPupilTable_Master.PupilRollTableID AS PupilOnRollTableID, dbo.xPupilTable_Master.NativeID, 
                      dbo.xPupilTable_Master.PupilOnRollOrderSeqColumn, dbo.xPupilTable_Master.SchoolCensusTableID, dbo.xPupilTable_Master.UPN, 
                      dbo.xPupilTable_Master.FormerUPN, dbo.xPupilTable_Master.Surname, dbo.xPupilTable_Master.Forename, 
                      dbo.xPupilTable_Master.UniqueLearnerNumber, dbo.xPupilTable_Master.Middlenames, dbo.xPupilTable_Master.PreferredSurname, 
                      dbo.xPupilTable_Master.FormerSurname, dbo.xPupilTable_Master.DoB, dbo.xPupilTable_Master.FSMeligible, dbo.xPupilTable_Master.PartTime, 
                      dbo.xPupilTable_Master.EntryDate, dbo.xPupilTable_Master.TermlySessionsPossible, dbo.xPupilTable_Master.TermlySessionsAuthorised, 
                      dbo.xPupilTable_Master.TermlySessionsUnauthorised, dbo.xPupilTable_Master.AnnualSessionsPossible, 
                      dbo.xPupilTable_Master.AnnualSessionsAuthorised, dbo.xPupilTable_Master.AnnualSessionsUnauthorised, dbo.xPupilTable_Master.MissingAddress, 
                      dbo.xPupilTable_Master.HoursAtSetting, dbo.xPupilTable_Master.FundedHours, dbo.xPupilTable_Master.DuplicateNotFunded, 
                      dbo.xPupilTable_Master.MoveOffRollFlag, dbo.xPupilTable_Master.Gender, dbo.xPupilTable_Master.Ethnicity, 
                      dbo.xPupilTable_Master.EthnicitySource, dbo.xPupilTable_Master.Connexions, dbo.xPupilTable_Master.Language, 
                      dbo.xPupilTable_Master.EnrolStatus, dbo.xPupilTable_Master.ClassType, dbo.xPupilTable_Master.Boarder, dbo.xPupilTable_Master.NCyearLeaving, 
                      dbo.xPupilTable_Master.NCyearActual, dbo.xPupilTable_Master.SENprovision, dbo.xPupilTable_Master.ServiceChild, 
                      dbo.xPupilTable_Master.ServiceChildSource, dbo.xPupilTable_Master.T_Reason_I, dbo.xPupilTable_Master.T_Reason_M, 
                      dbo.xPupilTable_Master.T_Reason_R, dbo.xPupilTable_Master.T_Reason_S, dbo.xPupilTable_Master.T_Reason_T, 
                      dbo.xPupilTable_Master.T_Reason_H, dbo.xPupilTable_Master.T_Reason_F, dbo.xPupilTable_Master.T_Reason_E, 
                      dbo.xPupilTable_Master.T_Reason_C, dbo.xPupilTable_Master.T_Reason_G, dbo.xPupilTable_Master.T_Reason_U, 
                      dbo.xPupilTable_Master.T_Reason_O, dbo.xPupilTable_Master.T_Reason_N, dbo.xPupilTable_Master.Age_Category, 
                      dbo.xPupilTable_Master.SourceID, dbo.xPupilTable_Master.GandTindicator, dbo.xPupilTable_Master.ModeOfTravel
FROM         dbo.xPupilTable_Master INNER JOIN
                      dbo.SchoolCensus_Master AS SchoolCensus_Master_1 ON 
                      dbo.xPupilTable_Master.SchoolCensusTableID = SchoolCensus_Master_1.SchoolCensusTableID INNER JOIN
                          (SELECT     TOP (1) ReferenceDate
                            FROM          dbo.SchoolCensus_Master
                            ORDER BY ReferenceDate DESC) AS Date ON SchoolCensus_Master_1.ReferenceDate = Date.ReferenceDate
WHERE     (dbo.xPupilTable_Master.On_Or_Off_Roll = N'On')

I essentially want to create this View but for the previous Census. In the view above I have a derived table that pulls through the latest census date but I am unsure how to pull through the previous census date. I have created the table below and I was wondering if that helps or not?

ID CensusDate

1	17/05/2012 12:00:00
2	19/01/2012 12:00:00
3	06/10/2011 12:00:00
4	19/05/2011 12:00:00
5	20/01/2011 12:00:00
6	07/10/2010 12:00:00
7	20/05/2010 12:00:00
8	21/01/2010 12:00:00
9	01/10/2009 12:00:00
10	21/05/2009 12:00:00
11	15/01/2009 12:00:00
12	02/10/2008 12:00:00
13	15/05/2008 12:00:00
14	17/01/2008 12:00:00
15	04/10/2007 12:00:00
16	17/05/2007 00:00:00
17	18/01/2007 00:00:00
18	21/09/2006 00:00:00
19	18/05/2006 00:00:00
20	19/01/2006 00:00:00
NULL	NULL

Answer 15

Any progress?

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 16

Yes, I used the Date table I made and use the ID from that in the view. Whenever i upload data to the server the table gets remade.