December 23, 2008 at 11:16 am
the columns are as follows
date c_type c_status
dd/mm/yy L P
'' L p
'' L d
date D p
date D d
date D p
I want monthly report as follows
date total total L total p total d Total D totalp totald
mm/yy 6 3 2 1 3 2 1
mm/yy ------so on -----------------
for each month
Based on month i should get total, total L total p total d Total D ...etc
Can any one help
December 23, 2008 at 11:45 am
Couple of things that may help you...
1) read Jeff's article on how to post questions to get the best answers. If you give us a bit more to work with I think you'll find the quality of your answers will increase dramatically...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
2) read Jeff's Articles on Creating Cross Tabs, as that's basically what you're doing. I thin kyou'll find what you are looking for there.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/cross+tab/65048/
-Luke.
December 23, 2008 at 12:36 pm
I am looking for a query which can give monthly totals of total (L and D)
total (L) and total p( in L) and total d(in L) similarlyt total p( in D) and total d ( in D) as per the table. i have no idea abt cross tabs. this query is used to generate a report
December 23, 2008 at 1:16 pm
understaning your data is quite difficult - its not clear. can you provide better data and expected output?
---- [font="Tahoma"]Live and Let Live![/font] ----
December 23, 2008 at 1:50 pm
As suggested by Luke - review the articles that he gave you the links for. Those articles will show you how to solve your problem.
If not, then the Best Practices article will show you how to post the question so you can get a tested and validated response.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/cross+tab/65048/
Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Please, help us help you by reviewing the above articles.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2008 at 2:05 pm
date c_type c_status
5/11/08 L P
5/21/08 L NP
5/25/08 D NP
6/5/08 D P
6/18/08 D NP
6/25/08 L P
6/30/08 D P
NOW MY MONTHLY REPORT SHOULD LOOK LIKE THIS
L D
-------------------- --------------------
DATE TOTAL(c_type) Total total P total np total total p total np
5/08 3 2 1 1 1 0 1
6/08 4 1 1 0 3 2 1
The above is the monthly report - which is expected
more clarification L is lost D is damaged p is paid and np is not paid
December 23, 2008 at 3:27 pm
Ranjit,
When we ask for sample scripts, we don't mean a copy paste of the first few lines of table.
This is what was required of you ...... (or something similar)
create table #foo
(date datetime,
c_type char(1),
c_status varchar(5))
insert into #foo(date,c_type,c_status) (
select '5/11/08','L', 'NP' union all
select '5/21/08','L','NP' union all
select '5/25/08','D','NP' union all
select '6/5/08','D','P' union all
select '6/18/08','D','NP' union all
select '6/25/08','L','P' union all
select '6/30/08','D','P'
)
I will look into this when I reach home...
December 24, 2008 at 8:04 am
Thank you all.
I found the solution
The sql query looks something like this
select fdate, total(c_type),
total(c_status) = (select count(*) where c_type = 'L' and date = fdate)
total(c_status P) = select count(*) where c_type = L and c_status = P and date = fdate)
from
(selec CONVERT( CHAR(6), date, 112 ) AS fDate,
total(c_type),= Count(*)
FROM WHERE AND (c_type= 'L' OR c_type='D')
GROUP BY convert( CHAR(6), claim_close_dt, 112 )
)
order by fdate
December 24, 2008 at 8:05 am
The above solution will give partial report we can get complete report by adding more conditions
Thank you all once again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply