How to join 2 tables - 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)

How to join 2 tables

Author

Topic

miha198206
Starting Member

2 Posts

Posted - 2010-06-16 : 00:38:54

I have 2 tables:
1. SystemUser (columns: id, name);
2. Quote (columns: id, name, date);

I need a query to show number of quotes grouped by systemuser with names of systemusers and result should be grouped by months.

Columns of result set should be like:
SystemuserName : NumberOfQuotesByCurrentMonth : NumberOfQuotesByCurrentYear : Rate

'Rate' column should be calculated by formula: (NumberOfQuotesByCurrentYear/NumberOfQuotesByCurrentMonth).

How can I achive this record set?
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-16 : 02:59:07

[code]
select Name, NumberOfQuotesByCurrentMonth, NumberOfQuotesByCurrentYear,
Rate = NumberOfQuotesByCurrentMonth * 1.0 / NumberOfQuotesByCurrentYear
from
(
select SystemUser.Name,
NumberOfQuotesByCurrentMonth = count(case when date >= dateadd(month, datediff(month, 0, getdate()), 0)
and date < dateadd(month, datediff(month, 0, getdate()) + 1, -1)
then 1
end),
NumberOfQuotesByCurrentYear = count(*)
from SystemUser
inner join Quote on SystemUser.Name = Quote.Name
where quote.Date >= dateadd(year, datediff(year, 0, getdate()), 0)
and quote.Date < dateadd(year, datediff(year, 0, getdate()) + 1, -1)
group by SystemUser.Name
) a
[/code]

KH
[spoiler]Time is always against us[/spoiler]

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-16 : 03:29:23

Hi,

Select Year(date) , Month(date)
name , Sum(IfCurrMonthCount) MonthCnt, Sum(IfCurrYearCount) YearCnt
FROM
(Select S.name ,Q.date,
CASE WHEN (Month(Q.date) = Month(GetDate())) AND (Year(Q.date) = Year(GetDate())) THEN
1
ELSE
0
END as IfCurrMonthCount,
CASE WHEN (Year(Q.date) = Year(GetDate())) THEN
1
ELSE
0
END as IfCurrYearCount
FROM SystemUser S , Quote Q
WHERE S.name = Q.name) AS SubQ
GROUP BY name ,Year(date) , Month(date)

Let me know if this works

Lets unLearn

miha198206
Starting Member

2 Posts

Posted - 2010-06-22 : 01:30:30

Hi khtan and naveengopinathasari,

your solutions are great. thank you for your replies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources