September 18, 2014 at 8:30 am
Hello everyone,
I am trying to create a report that shows how many accounts have been payed in the past 30 days of every month and year. I have already created my Stored Proc. I just need to know of a sample query that generates every 30 days of every month. Is a GetDate function the function to use for this? Any help would be appreciated.
September 18, 2014 at 8:33 am
waseemshaikh345 (9/18/2014)
Hello everyone,I am trying to create a report that shows how many accounts have been payed in the past 30 days of every month and year. I have already created my Stored Proc. I just need to know of a sample query that generates every 30 days of every month. Is a GetDate function the function to use for this? Any help would be appreciated.
What do you mean by "generates every 30 days of every month"? Are you trying to find each months beginning date and the date that would be 30 days prior to that? You could use a tally table this pretty easily if that is what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 8:51 am
Like for example lets say I Have 14 people who made payments to their account, I want to know in those 30 days of September how many people payed their account. So it would be 14. So for next month lets say its 25 people so if I click September on the parameter it should show the users information and show how many accounts were payed for that month.
September 18, 2014 at 8:57 am
waseemshaikh345 (9/18/2014)
Like for example lets say I Have 14 people who made payments to their account, I want to know in those 30 days of September how many people payed their account. So it would be 14. So for next month lets say its 25 people so if I click September on the parameter it should show the users information and show how many accounts were payed for that month.
What about January, March, May, July, August, October, December? Those months have 31 days. What are the 30 days of those months? Which day do you exclude?
Here is where the real challenge is. What you have provided so far is a very vague explanation of what you want to do. We have no idea what you are really trying to do. We don't know what your data structures are like. We don't know what you want for output. Help us to help you by providing details for the question. I don't think you need go so far as to generate tables and data here but tell me what you want with some rules and I can help you figure it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 10:05 am
Sean Lange (9/18/2014)
waseemshaikh345 (9/18/2014)
Like for example lets say I Have 14 people who made payments to their account, I want to know in those 30 days of September how many people payed their account. So it would be 14. So for next month lets say its 25 people so if I click September on the parameter it should show the users information and show how many accounts were payed for that month.What about January, March, May, July, August, October, December? Those months have 31 days. What are the 30 days of those months? Which day do you exclude?
Here is where the real challenge is. What you have provided so far is a very vague explanation of what you want to do. We have no idea what you are really trying to do. We don't know what your data structures are like. We don't know what you want for output. Help us to help you by providing details for the question. I don't think you need go so far as to generate tables and data here but tell me what you want with some rules and I can help you figure it out.
And what about February? are you going to include days?
September 18, 2014 at 11:22 am
Maby my select statement might help. So I am trying to see how many contracts are purchased for every month. And if its Feburary than up to the 28th is fine. In my WHERE clause you can see
Where t1.state = 9 is the ID that shows all purchased plans. So while I was thinking maby a *COUNT function might help?
Select t5.name,
t3.DealerCode,
t3.Name,
t1.AppNumber,
t1.EnteredVin,
t1.NumberOfPayments,
t1.ChangeDate
from tPurchase as t1
inner join tPurchaseProfile as t2 on t1.PurchaseProfileOID = t2.oid
inner join tBranch as t3 on t1.BranchOID = t3.OID
inner join tAutoGroupDealerShips as t4 on t3.DealershipOID=t4.DealerShipOID
inner join tAutoGroup as t5 on t4.AutoGroupOID=t5.AutoGroupOID
Where t1.state =9
September 18, 2014 at 12:10 pm
waseemshaikh345 (9/18/2014)
Maby my select statement might help. So I am trying to see how many contracts are purchased for every month. And if its Feburary than up to the 28th is fine. In my WHERE clause you can seeis the ID that shows all purchased plans. So while I was thinking maby aWhere t1.state = 9
*COUNTfunction might help?
Select t5.name,
t3.DealerCode,
t3.Name,
t1.AppNumber,
t1.EnteredVin,
t1.NumberOfPayments,
t1.ChangeDate
from tPurchase as t1
inner join tPurchaseProfile as t2 on t1.PurchaseProfileOID = t2.oid
inner join tBranch as t3 on t1.BranchOID = t3.OID
inner join tAutoGroupDealerShips as t4 on t3.DealershipOID=t4.DealerShipOID
inner join tAutoGroup as t5 on t4.AutoGroupOID=t5.AutoGroupOID
Where t1.state =9
No idea what your where clause is. What is t1.state? Regardless I guess you want count(*) from your table. I have no idea how you know if purchased or not. Then you just need to another predicate to the where clause to filter the date. I guess this is ChangeDate? Do you want the whole month? That would be a lot simpler than some strange rolling 30 days that doesn't capture the month very well. I would think you would need some date checks. Lynn has an excellent blog post here (http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/%5B/url%5D) which demonstrates a lot of typical date checks.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2014 at 12:17 pm
Thank you Sean for you help. Really appreciate it
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply