help needed in sophisticated joning query...

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 2000 Forums

Transact-SQL (2000)

help needed in sophisticated joning query...

Author

Topic

great_mamun
Starting Member

14 Posts

Posted - 2008-08-19 : 07:54:33

Dear all,

table t1(userid,name)
talbe t2(userid,edate,status)
every day there is a insertion for each user in t2 table, and status will be either A or B

now for getting result,
user will input edate in ranage for e.g-> from 8/15/2008 to 8/25/2008

i want to show the result in following format.
userid | name | total_day (between given range) | A (how many days during that time) | B (how many day) | Standing

suppose, i enter 8/1/008 to 8/31/2008, 31 days
and userid is id001, name Mr. text,
then total days is 31 (diff between), A is how many As during that time period, B is how many Bs during that time (A and B are two status and A+B<=31 days for this case)
if A+B=31 then standing will 0 otherwise
standing = total_day - (A+B)

I know this makes you boring but I need that.

Best Regards,
Abdullah Al Mamun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 07:57:45

[code]SELECT t1.userid, t1.name,
total_day = COUNT(*),
A = SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END),
B = SUM(CASE WHEN status = 'B' THEN 1 ELSE 0 END)
FROM t1 INNER JOIN t2
ON t1.userid = t2.userid
GROUP BY t1.userid, t1.name[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 08:03:12

[code]select t1.userid,
t1.name,
count(edate),
count(case when status='A' then edate else null end) as A,
count(case when status='B' then edate else null end) as B,
count(edate)-count(case when status='A' or status='B' then edate else null end)>0 as standing
from t1
inner join t2
on t1.userid=t2.userid
where edate between @startdate and dateadd(dd,1,@enddate)
group by t1.userid,t1.name[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 08:16:52

oh .. . i missed out the last column standing

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 08:19:54

quote:
Originally posted by khtan

oh .. . i missed out the last column standing

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

and ON clause too

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 08:22:41

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

oh .. . i missed out the last column standing

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

and ON clause too

thanks Visakh

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 09:51:19

quote:
Originally posted by khtan

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

oh .. . i missed out the last column standing

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

and ON clause too

thanks Visakh

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

you're welcome

great_mamun
Starting Member

14 Posts

Posted - 2008-08-20 : 00:33:33

Dear Visakh16 & khtan,
many many and many thanks both of you.
It is really a great help for me within very short time.
Thanks once again both of you.

Best Regards,
Abdullah Al Mamun

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 00:39:24

quote:
Originally posted by great_mamun

Dear Visakh16 & khtan,
many many and many thanks both of you.
It is really a great help for me within very short time.
Thanks once again both of you.

Best Regards,
Abdullah Al Mamun

you're welcome

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-20 : 00:41:01

quote:
Originally posted by great_mamun

Dear Visakh16 & khtan,
many many and many thanks both of you.
It is really a great help for me within very short time.
Thanks once again both of you.

Best Regards,
Abdullah Al Mamun

You are welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources