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
 Selecting Previous Month data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  07:27:08  Show Profile  Reply with Quote
Hi visakh16
Hi Guys

i am trying to build a report, that selects the previous month data only.

So if we are in august i only want to retrieve Julys Data
As we currently in July i want mays data etc

This is my query

SELECT
Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,
Dim_Outlet.Open_Date,
Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date,
--case when max (dateadd(mm,0,fee_wholesale_date)) > Open_Date THEN 1 else 0 end "After initial application"
case when fee_wholesale_date > Open_Date THEN 1 else 0 end "After initial application"
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 fee_wholesale_date > Open_Date
and fee_wholesale_date BETWEEN Cast (DATEADD(dd,-(DAY(GetDate())-1),GetDate()) as Date)
AND Cast (DATEADD(dd,-(DAY(DATEADD(mm,1,GetDate()))),DATEADD(mm,1,GetDate())) as Date)
--and max(fee_wholesale_date)
group by Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,Open_Date, fee_wholesale_date
Order by fee_wholesale_date desc

I think i am very close with the query but i am only returning this months data.

Any ideas ?

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/26/2013 :  08:10:40  Show Profile  Reply with Quote


DateColumn>= dateadd(dd,-day(DateColumn)+1,DATEADD(mm,-1,DateColumn)) --FistDateOfPreviousMonth,
AND DateColumn<= DateAdd(dd,-DAY(DateColumn),DateColumn) --LastDateOfPreviousMonth

Replace the "dateColumn" with required field

Cheers
MIK

Edited by - MIK_2008 on 06/26/2013 08:11:06
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  08:25:43  Show Profile  Reply with Quote
Hi Mik2008

With you particular method its returning Zero results.

I have three columns

Fdmsaccountno
Opendate
fee_wholesale_date

Within the fee_wholesale_date i have data in relation to june.

All the date function should do is provide me a list of fdmsaccountno where fee_wholesale_date = to last month
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
94 Posts

Posted - 06/26/2013 :  08:35:22  Show Profile  Reply with Quote
if you dont want to include timestamp in your comparision
try this..

DateColumn>=DATEADD(mm,datediff(mm,0,DateColumn)-1,0) and
DateColumn<=DATEADD(mm,datediff(mm,0,DateColumn),0)-1

Thanks..

M.MURALI kRISHNA
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/26/2013 :  08:56:46  Show Profile  Reply with Quote
quote:
Originally posted by masond

Hi Mik2008

With you particular method its returning Zero results.

I have three columns

Fdmsaccountno
Opendate
fee_wholesale_date

Within the fee_wholesale_date i have data in relation to june.

All the date function should do is provide me a list of fdmsaccountno where fee_wholesale_date = to last month



Since having no information about the underlaying structure and data, I can only guess. However, I believe if you replace the dateColumn with Getdate() you'll see that it would return the first and last date of previous month. But as you mentioned that "if we are in August I only want to retreive July's data, and If in "July" then May's data and so on". In that case I guess the "dateColumn" might be a variable which will be passed into this query/stored procedure but will be compared with a column of a table.

With that, can you check if your query is working correctly by providing explicit values (for previous month) and see if it is returning any record?

e.g.
DateColumn >= '2013-05-01' and DateColumn<='2013-05-31'


---------
DateColumnOfaTable>= dateadd(dd,-day(DateColumn)+1,DATEADD(mm,-1,DateColumn)) --FistDateOfPreviousMonth,
AND DateColumnOfaTable<= DateAdd(dd,-DAY(DateColumn),DateColumn) --LastDateOfPreviousMonth

Cheers
MIK
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/26/2013 :  09:01:57  Show Profile  Reply with Quote
Perhaps you're applying the previous month logic on the "fee_wholesale_date" field?? if so, then check if the following query is giving any result?

SELECT
Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,
Dim_Outlet.Open_Date,
Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date,
--case when max (dateadd(mm,0,fee_wholesale_date)) > Open_Date THEN 1 else 0 end "After initial application"
case when fee_wholesale_date > Open_Date THEN 1 else 0 end "After initial application"
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 fee_wholesale_date > Open_Date
AND fee_wholesale_date >='2013-05-01' and fee_wholesale_date <='2013-05-31'


Cheers
MIK
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  09:05:03  Show Profile  Reply with Quote
HI Mik_2008

Your soultion works,

however i dont want to manually type those dates in, i want it to automatically calculate every time i run the report which will be every month
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/26/2013 :  09:09:22  Show Profile  Reply with Quote
quote:
Originally posted by masond

HI Mik_2008

Your soultion works,

however i dont want to manually type those dates in, i want it to automatically calculate every time i run the report which will be every month

Use this (notice the strictly less than in the second condition, make sure you have it that way).
....
       AND fee_wholesale_date >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) 
       AND fee_wholesale_date < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0);
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  09:19:25  Show Profile  Reply with Quote
Hi James K

Thank you for your support

This is an example of the data which i am getting. As you can see from the top two lines, i am stil getting data comming through when the open_date month is equal to fee_wholesale_date

HOw can i exclude this from my query

FDMSAccountNo Open_Date fee_wholesale_date Added This Month
878323645886 20130508 2013-05-30 1
878323542885 20130507 2013-05-24 1
878323202886 20130430 2013-05-23 1
878323212885 20130430 2013-05-24 1
878322613885 20130424 2013-05-22 1


SELECT
Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,
Dim_Outlet.Open_Date,
Audit_FDMS_Billing_Fees_Hist.fee_wholesale_date,
case when fee_wholesale_date > Open_Date THEN 1 else 0 end "Added This Month"
--into #SignedThisMonth
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 fee_wholesale_date > Open_Date
AND fee_wholesale_date >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND fee_wholesale_date < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
group by Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo,Open_Date, fee_wholesale_date
order by Open_Date desc

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/26/2013 :  09:23:20  Show Profile  Reply with Quote
If you want to get ONLY data where Open_Date month is earlier than May 1, 2013 (given that we are in June, 2013 now) add another condition to your query like this:
....
AND fee_wholesale_date >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) 
AND fee_wholesale_date < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND Open_Date < DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
....
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/26/2013 :  09:30:42  Show Profile  Reply with Quote
James K

your a diamond :)

Thank you very much x
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 06/26/2013 :  11:18:21  Show Profile  Reply with Quote
You are very welcome, masond; I am only happy to come and take all the credit after MIK_2008 did all the hard work
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/26/2013 :  12:43:47  Show Profile  Reply with Quote
lol@James, well mine was just a guess but you were on spot. I wrote a comment straight after yours solution - covering even the difference of seconds till 23:59:59.59999 and suggesting the requester to use yours one. but I removed it since by the time I clicked submit, it was almost over (solved) :)

Cheers
MIK
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.11 seconds. Powered By: Snitz Forums 2000