SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Max Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  09:55:48  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/26/2013 :  10:04:55  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  10:09:57  Show Profile  Reply with Quote
HI visakh16
with a cte can i put this into a temp table ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/26/2013 :  10:16:22  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  10:22:18  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/26/2013 :  10:25:18  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000