SQLTeam.com | Weblogs | Forums

How to get the latest date in sql server 2008


#1

How to get the latest date in sql server 2008, I have attached the files what exactly i am looking for and what i am trying through code.. please guide me how to do this..


#2

The picture you attached does not help us recreating your situation (tables and data).
Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.


#3

Sir, below i have pasted my code, and the requirement is attached in my last post

CREATE TABLE [dbo].[EFT_Benf_ReleaseMaster1](
[DistrictCode] [int] NULL,
[BenfCode] [int] NULL,
[F] [int] NULL,
[L] [int] NULL,
[R] [int] NULL,
[C] [int] NULL,
[T] [int] NULL,
[Amount] [float] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]

INSERT INTO EFT_Benf_ReleaseMaster1 VALUES (16,123,1,0,0,0,0,'100','12-01-2016')
INSERT INTO EFT_Benf_ReleaseMaster1 VALUES (16,123,1,1,0,0,0,'200','12-02-2016')
INSERT INTO EFT_Benf_ReleaseMaster1 VALUES (16,123,1,1,1,0,0,'300','12-03-2016')
INSERT INTO EFT_Benf_ReleaseMaster1 VALUES (16,123,1,1,1,1,0,'400','12-04-2016')
INSERT INTO EFT_Benf_ReleaseMaster1 VALUES (16,123,1,1,1,1,1,'500','12-05-2016')
INSERT INTO EFT_Benf_ReleaseMaster1 VALUES (16,123,1,1,1,1,1,'600','12-06-2016')

select a.DistrictCode,a.BenfCode,1 as F,0 as L,0 as R,0 as C, 0 as T,Sum(Amount) as Amt,Date
from EFT_Benf_ReleaseMaster1 a
where (a.F=1 and L=0 and R=0 and C=0 and T=0)and a.DistrictCode=16
group by a.DistrictCode,a.BenfCode,Date

UNION ALL

select a.DistrictCode,a.BenfCode,1 as F,1 as L,0 as R,0 as C, 0 as T,Sum(Amount) as Amt,Date
from EFT_Benf_ReleaseMaster1 a
where (a.F=1 and L=1 and R=0 and C=0 and T=0)and a.DistrictCode=16
group by a.DistrictCode,a.BenfCode,Date

union all
select a.DistrictCode,a.BenfCode,1 as F,1 as L,1 as R,0 as C, 0 as T,Sum(Amount) as Amt,Date
from EFT_Benf_ReleaseMaster1 a
where (a.F=1 and L=1 and R=1 and C=0 and T=0)and a.DistrictCode=16
group by a.DistrictCode,a.BenfCode,Date

union all
select a.DistrictCode,a.BenfCode,1 as F,1 as L,1 as R,1 as C, 0 as T,Sum(Amount) as Amt,Date
from EFT_Benf_ReleaseMaster1 a
where (a.F=1 and L=1 and R=1 and C=1 and T=0)and a.DistrictCode=16
group by a.DistrictCode,a.BenfCode,Date

union all
select a.DistrictCode,a.BenfCode,1 as F,1 as L,1 as R,1 as C, 1 as T,Sum(Amount) as Amt,Date
from EFT_Benf_ReleaseMaster1 a
where (a.F=1 and L=1 and R=1 and C=1 and T=1)and a.DistrictCode=16
group by a.DistrictCode,a.BenfCode,Date


#4

Sorry, but I don't understand what you're after.
Your sample data looks identical to what you want to display. Am I missing something?


#5

I think you need to remove Date from the GROUPing, something like this:

select a.DistrictCode,a.BenfCode,1 as F,0 as L,0 as R,0 as C, 0 as T,Sum(Amount) as Amt,MAX(Date) AS Date 
from EFT_Benf_ReleaseMaster1 a 
where (a.F=1 and L=0 and R=0 and C=0 and T=0)and a.DistrictCode=16 
group by a.DistrictCode,a.BenfCode

UNION ALL

select a.DistrictCode,a.BenfCode,1 as F,1 as L,0 as R,0 as C, 0 as T,Sum(Amount) as Amt,MAX(Date) AS Date 
from EFT_Benf_ReleaseMaster1 a 
where (a.F=1 and L=1 and R=0 and C=0 and T=0)and a.DistrictCode=16
group by a.DistrictCode,a.BenfCode

union all 
select a.DistrictCode,a.BenfCode,1 as F,1 as L,1 as R,0 as C, 0 as T,Sum(Amount) as Amt,MAX(Date) AS Date 
from EFT_Benf_ReleaseMaster1 a 
where (a.F=1 and L=1 and R=1 and C=0 and T=0)and a.DistrictCode=16
group by a.DistrictCode,a.BenfCode

union all 
select a.DistrictCode,a.BenfCode,1 as F,1 as L,1 as R,1 as C, 0 as T,Sum(Amount) as Amt,MAX(Date) AS Date 
from EFT_Benf_ReleaseMaster1 a 
where (a.F=1 and L=1 and R=1 and C=1 and T=0)and a.DistrictCode=16 
group by a.DistrictCode,a.BenfCode

union all 
select a.DistrictCode,a.BenfCode,1 as F,1 as L,1 as R,1 as C, 1 as T,Sum(Amount) as Amt,Max(Date) AS Date
from EFT_Benf_ReleaseMaster1 a 
where (a.F=1 and L=1 and R=1 and C=1 and T=1)and a.DistrictCode=16 
group by a.DistrictCode,a.BenfCode

#6

If I understand the code correctly, I think you could simplify to a single SELECT. You could add WHERE conditions for the F, L, R, C and T combinations to restrict them if you needed to.

select a.DistrictCode,a.BenfCode,F,L,R,C,T,Sum(Amount) as Amt,MAX(Date) AS Date 
from EFT_Benf_ReleaseMaster1 a
where a.DistrictCode=16 
/*if needed
    and (F=1 and L=0 and R=0 and C=0 and T=0) 
    or  (F=1 and L=1 and R=0 and C=0 and T=0) 
    or  (F=1 and L=1 and R=1 and C=0 and T=0)
    or  (F=1 and L=1 and R=1 and C=1 and T=0)
    or  (F=1 and L=1 and R=1 and C=1 and T=1)
*/
group by a.DistrictCode,a.BenfCode,F,L,R,C,T