Monthly count for search - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Monthly count for search

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-02-24 : 14:01:28

Hi,

I have table as below for package and its monthly count..
like..
Package1 - 1 dollor per search - 50 Search (PER MONTH) included in package and extra search FOR MONTH will be counted seperately.

I am passing data in my table like..
user package table has user's package information with its detail.


Userid   SearchPerMonth    StartDatePackage    EndDatePackage
1             50              25/02/2012         25/02/2013  (1 YEAR)

Detail table


Userpackageid      SearchDate      SearchCost      
xxx                26/02/2012       1 Dollor

Now I want to summarize data like
monthly package search count - as I have search per month 50 for that package.. and if extra count happens over 50 than It will show other count for extra IN MONTHLY WISE..

Please help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-24 : 14:05:41

count by what?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-02-24 : 14:10:10

Count by month..
If I have yearly package selected then I want each month's search count and extra search count

Like 50 search included for selected package per month and user searches 57 then month wise count will be 50 and extra count 7 in that month.

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-02-24 : 22:29:26

Is there anyone please help on this once?

Thanks..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-25 : 03:21:01

The tables that you have listed seem way too insufficient for modeling the problem you described. For example, what does UserPackageId represent? Is it a specific package Id bought by a specific user? If that is true, how do we figure out which package they bought?

If you are still in the process of designing the database schema, you may want to add more tables. For example:
1. A Users table - this would have a user_id and additional information about the user such as last name, first name, address etc.
2. A Packages table - this would have a package id and additional information such as free searches per month, cost per additional search etc.
3. A Subscriptions table - this would list the subscriptions that users have bought. So there would be a subscription_id, as well as user id, a package id (both foreign keys from the first two tables) and additional information such as date on which they bought etc.
4. A Usage table - this would have the subscription Id and additional information - may be one row for each search.

Once you have the data like that, it is easy to do the queries that you described. If you already have something like that, post the details of the tables and some sample data, and I am sure many people on this forum would be able to help. If you need help posting table DDL etc., take a look at Brett's blog here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-02-25 : 06:47:41

Hi,

I have created tables as you described-- But I have not showed package id in my first table shown.

Can you please provide query tsql idea from usage table for user's package with monthly count with extra count as I described?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-25 : 08:48:01

Hard to write the query without seeing the table DDL. But, I would start out with something simple - for example, how do I find the search count for a given subscription_id. That would be like this:

SELECT
	s.subscription_id,
	SUM(u.usage_id) AS SearchCount
FROM
	Subscriptions s
	INNER JOIN Usage u ON u.subscription_id = s.subscription_id
WHERE
	s.subscription_id = 1234
GROUP BY
 	s.subscription_id;

Here I am trying to find the count for a single subscription_id. Once that seems correct, I would join with the Packages table to get the information about the package this specific subscription belongs to etc.

If this does not help you, post the table DDL's and some sample data.

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-02-25 : 11:22:57

Hi sunita,

Thank you for reply..
I put sample DDL above.

You are understanding right with question.. Usage table will have multiple record for each search ...

Suppose I bought yearly package -- and 50 search available in that package per Month -- then

I want to summarize above table's data month wise.. For ex. username xyz searched 34 employees for first month from when his package started (guessing yearly package) - so above example data has 50 search available for his package .. so for each month he can search 50 and if over 50 then I want those count seperately.

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-02-25 : 13:23:12

Hi Visakh,

can you please help on this query as well?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-25 : 13:26:43

You can add additional criteria etc. to the query - for example, to count for each month and figure out the overage, you would do something like this:

SELECT
	s.subscription_id,
	DATEPART(YEAR,u.date_of_use) AS Yr,
	DATEPART(MONTH,u.date_of_use) AS Mnth
	SUM(u.usage_id) AS SearchCount,
	CASE WHEN SUM(u.usage_id)-50 > 0 THEN SUM(u.usage_id)-50 ELSE 0 END
		AS Overage
FROM
	Subscriptions s
	INNER JOIN Usage u ON u.subscription_id = s.subscription_id
WHERE
	s.subscription_id = 1234
GROUP BY
 	s.subscription_id
 	DATEPART(YEAR,u.date_of_use),
	DATEPART(MONTH,u.date_of_use);

In reality though, if you have several subscription plans with varying number of free uses per month you would need to join on the Packages table. Similarly, if the user signed up in the middle of the month, and you want to give them only a pro-rated number of free searches, you will need to add some more logic to do that, etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources