Solved

How to get the cumulative output for this query.

Posted on 2012-04-02
2
303 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 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