Solved

How to get the cumulative output for this query.

Posted on 2012-04-02
2
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Following an example - removing duplicate strings 4 70
Format Date fields 11 64
Stored Procedure needs owner to execute 5 49
What Is an Error? 2 59
In this article I will describe the Backup & Restore 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 article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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