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 |
|
Gudiya
Starting Member
14 Posts |
 Posted - 2012-08-13 : 22:05:58
|
| I have this task where I need to get the counts of Historical data while working for the current month of June. Here is the scenario:Suppose a person does his first transaction in the year 2011/December for which the count should be 1. So when I run my scrip for that person(which is recognized by some ID) in the month of February/2012, the count should be 0 because the count is 1 for the first time, as it should not read the transaction each time for same ID. So how can I implement this case in SQL? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-08-13 : 22:17:56
|
1. you need to show us your table schema2. Show us your current query3. "the count should be 0". You want the count to reset for each year ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Gudiya
Starting Member
14 Posts |
Posted - 2012-08-13 : 22:29:48
|
| Yes, the count should be refresh every year. I haven't started the query yet. I am just trying to figure out the logic how could it be implement in SQL. I don't know the logic behind this. So plz help. Just can tell me the simple logic. It has nothing to do with schema or anything. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-08-13 : 23:41:23
|
[code]select ID, year(transaction_date), count(*)from sometablegroup by ID, year(transaction_date)[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-14 : 00:21:40
|
| group by id value and take min(datevalue) in a derived table. add a left join to that and add a count(id) field so that it matches only for min date and thereafter it wont causing count to be considered only for the first case------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Calculating counts of Historical Data