Solved

How to get the cumulative output for this query.

Posted on 2012-04-02
2
302 Views
Last Modified: 2012-04-02
I have 2 tables.
1. geotable containing - state,district,source.
2. mstchvs where dataentry is done.
It contains state,source,district,rfeeddate,other fields.
I want this kind of output for given range of rfeeddate.
State district source Count Cum.Count

I am using the following query.


select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0))
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and rfeeddate>='2012-3-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
order by 1,2,3

This is the result for between dates.
Now to get the cumulative result i will write rfeeddate<='2012-3-31'

But how to append the cumulative result for above query
0
Comment
Question by:searchsanjaysharma
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 37795644
Select X.state,X.district,X.source,X.res,Y.cum_res From

(select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0)) as  res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and rfeeddate>='2012-3-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
) X
inner join
(
select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0)) as cum_res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 andrfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
) Y
on X.state = Y.state and X.district = Y.district and X.source = Y.source
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 37795734
Great
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PK numbers not follow 8 61
SQL Merige returns error code when updating 15 51
insert query with value having 's 2 47
SQL query to summarize items per month 5 53
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now