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.
Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-26 : 07:27:08
|
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 DataAs 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.FDMSAccountNowhere fee_sequence = '32r'and fee_wholesale_date > Open_Dateand 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_dateOrder 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
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-26 : 08:10:40
|
DateColumn>= dateadd(dd,-day(DateColumn)+1,DATEADD(mm,-1,DateColumn)) --FistDateOfPreviousMonth,AND DateColumn<= DateAdd(dd,-DAY(DateColumn),DateColumn) --LastDateOfPreviousMonthReplace the "dateColumn" with required fieldCheersMIK |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-26 : 08:25:43
|
Hi Mik2008 With you particular method its returning Zero results. I have three columns FdmsaccountnoOpendatefee_wholesale_dateWithin 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 |
|
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2013-06-26 : 08:35:22
|
if you dont want to include timestamp in your comparisiontry this..DateColumn>=DATEADD(mm,datediff(mm,0,DateColumn)-1,0) andDateColumn<=DATEADD(mm,datediff(mm,0,DateColumn),0)-1Thanks..M.MURALI kRISHNA |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-26 : 08:56:46
|
quote: Originally posted by masond Hi Mik2008 With you particular method its returning Zero results. I have three columns FdmsaccountnoOpendatefee_wholesale_dateWithin 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) --LastDateOfPreviousMonthCheersMIK |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-26 : 09:01:57
|
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.FDMSAccountNowhere fee_sequence = '32r'and fee_wholesale_date > Open_DateAND fee_wholesale_date >='2013-05-01' and fee_wholesale_date <='2013-05-31'CheersMIK |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-26 : 09:05:03
|
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 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-26 : 09:09:22
|
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); |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-26 : 09:19:25
|
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 Month878323645886 20130508 2013-05-30 1878323542885 20130507 2013-05-24 1878323202886 20130430 2013-05-23 1878323212885 20130430 2013-05-24 1878322613885 20130424 2013-05-22 1SELECT 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 #SignedThisMonthFROM Audit_FDMS_Billing_Fees_Hist inner JOIN Dim_Outlet ON Audit_FDMS_Billing_Fees_Hist.FDMSAccountNo = Dim_Outlet.FDMSAccountNowhere fee_sequence = '32r'and fee_wholesale_date > Open_DateAND 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_dateorder by Open_Date desc |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-26 : 09:23:20
|
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).... |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-26 : 09:30:42
|
James K your a diamond :) Thank you very much x |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-26 : 11:18:21
|
You are very welcome, masond; I am only happy to come and take all the credit after MIK_2008 did all the hard work |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-26 : 12:43:47
|
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) :)CheersMIK |
|
|
|
|
|
|
|