Max Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max Date

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-26 : 09:55:48

Hi Guys

I promise that this will be the final question of the day

I have noticed that my query is returning duplicate fdmsaccountno due to there being multiple fee_wholesale_date

How can i return one fdmsaccountno, but fdmsaccountno i want returned is the one with the most recent fee_wholesale_date

FDMSAccountNo Open_Date fee_wholesale_date
878218871886 20010917 2010-12-16
878218871886 20010917 2013-05-07

this is my query

SELECT
Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,
Dim_Outlet.Open_Date,
MAX(Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)as fee_wholesale_date,
DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) AS "Months calculation",
Case WHEN Open_Date = fee_wholesale_date THEN 1 else 0 end "Added initial application",
Case WHEN fee_wholesale_date < open_date THEN 1 else 0 end "Added before account opened",
case when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 0 and 3 then '0 to 3'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 3 and 6 then '3 to 6'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 6 and 9 then '6-9'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 9 and 12 then '9-12'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 12 and 24 then '12-24'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) > 24 then '24+'
else 'need to check' end as [Added Clientline Indicator]
--into #clientline
FROM Audit_FDMS_Billing_Fees_Hist
inner JOIN Dim_Outlet ON Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
where fee_sequence = '32r'
and Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo ='878218871886'
and DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)<> '1361'
group by Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,Dim_Outlet.Open_Date,fee_wholesale_date
order by "Added before account opened" desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 10:04:55

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo ORDER BY MAX(Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) DESC) AS Seq,
Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,
Dim_Outlet.Open_Date,
MAX(Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)as fee_wholesale_date,
DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) AS "Months calculation",
Case WHEN Open_Date = fee_wholesale_date THEN 1 else 0 end "Added initial application",
Case WHEN fee_wholesale_date < open_date THEN 1 else 0 end "Added before account opened",
case when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 0 and 3 then '0 to 3'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 3 and 6 then '3 to 6'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 6 and 9 then '6-9'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 9 and 12 then '9-12'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 12 and 24 then '12-24'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) > 24 then '24+'
else 'need to check' end as [Added Clientline Indicator]
--into #clientline
FROM Audit_FDMS_Billing_Fees_Hist
inner JOIN Dim_Outlet ON Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
where fee_sequence = '32r'
and Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo ='878218871886'
and DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)<> '1361'
group by Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,Dim_Outlet.Open_Date,fee_wholesale_date
)t
WHERE Seq=1
order by [Added before account opened] desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-26 : 10:09:57

HI visakh16
with a cte can i put this into a temp table ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 10:16:22

quote:
Originally posted by masond

HI visakh16
with a cte can i put this into a temp table ?

Sorry which cte?
I've not used any cte its just a derived table

Even otherwise you dont need a cte just for putting results in a temporary table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-26 : 10:22:18

Sorry Visakh16

With your derived table, how can i put this into a temp table,

I only ask, as i built an other query which inner joins onto the results of the table above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 10:25:18

[code]
if you want to create table on the fly use

SELECT * INTO #YourTable
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo ORDER BY MAX(Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) DESC) AS Seq,
Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,
Dim_Outlet.Open_Date,
MAX(Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)as fee_wholesale_date,
DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) AS "Months calculation",
Case WHEN Open_Date = fee_wholesale_date THEN 1 else 0 end "Added initial application",
Case WHEN fee_wholesale_date < open_date THEN 1 else 0 end "Added before account opened",
case when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 0 and 3 then '0 to 3'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 3 and 6 then '3 to 6'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 6 and 9 then '6-9'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 9 and 12 then '9-12'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 12 and 24 then '12-24'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) > 24 then '24+'
else 'need to check' end as [Added Clientline Indicator]
--into #clientline
FROM Audit_FDMS_Billing_Fees_Hist
inner JOIN Dim_Outlet ON Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
where fee_sequence = '32r'
and Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo ='878218871886'
and DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)<> '1361'
group by Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,Dim_Outlet.Open_Date,fee_wholesale_date
)t
WHERE Seq=1
order by [Added before account opened] desc

If you have precreated table you can use

INSERT INTO #YourTable
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo ORDER BY MAX(Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) DESC) AS Seq,
Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,
Dim_Outlet.Open_Date,
MAX(Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)as fee_wholesale_date,
DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) AS "Months calculation",
Case WHEN Open_Date = fee_wholesale_date THEN 1 else 0 end "Added initial application",
Case WHEN fee_wholesale_date < open_date THEN 1 else 0 end "Added before account opened",
case when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 0 and 3 then '0 to 3'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 3 and 6 then '3 to 6'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 6 and 9 then '6-9'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 9 and 12 then '9-12'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) between 12 and 24 then '12-24'
when DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date) > 24 then '24+'
else 'need to check' end as [Added Clientline Indicator]
--into #clientline
FROM Audit_FDMS_Billing_Fees_Hist
inner JOIN Dim_Outlet ON Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
where fee_sequence = '32r'
and Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo ='878218871886'
and DATEDIFF(month,dim_Outlet.Open_Date,Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date)<> '1361'
group by Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,Dim_Outlet.Open_Date,fee_wholesale_date
)t
WHERE Seq=1
order by [Added before account opened] desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources