• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

How to solve this complex query.

I have a table, in which dataentry is done from 48 locations field is district which are inside some states and those further belong to field called source.
The hierarchy is State,District,Source
In short,
There are 7 states, 48 districts and 3 source
I want to pick up the count  of dataentry done on date range basis and cumulative till to date.
The problem is how to pick those records from district where 0 data entry was done.
Output i need is as follows
State   District  Source   Date Entry Done Cumulative

I am using the following to get the date range data.
select state,district,source,count(*)
from mstchvs where flagr=1 and rfeeddate between '2012-3-1' and '2012-3-31'
group by state,district,source
order by 1,2,3

select state,district,source,count(*)
from mstchvs where flagr=1 and rfeeddate<='2012-3-31'
group by state,district,source
order by 1,2,3

The sample data is attached.
sample-data.xlsx
0
searchsanjaysharma
Asked:
searchsanjaysharma
  • 4
  • 3
1 Solution
 
patriktCommented:
If I understand you need to report all State,District combinations even if there is no record in selected range (it is report 0 for it).

You have to join your table to "list" table of districts. Something as this:

select DistricstTable.state,DistricstTable.district,source,count(*)
from DistricstTable
left join mstchvs 
ON DistricstTable.state=mstchvs.state and DistricstTable.district=mstchvs.district
where flagr=1 and rfeeddate<='2012-3-31'
group by DistricstTable.state,DistricstTable.district,source
order by 1,2,3

Open in new window


You can use physical DistrictsTable or construct it from your data if it is possible.
0
 
searchsanjaysharmaAuthor Commented:
Thats done, but how to get the data within range.
Example if to a query 2 dates are there fromdate and todate
The output shoud be
State District Source Total (Within between dates) Cumulativetotal(Till todate)
0
 
patriktCommented:
I understand now. You need both aggregates in one query.

For this you should use "CASED" counts.
Something like this:
select state,district,source,
count(
       CASE 
         WHEN rfeeddate between '2012-3-1' and '2012-3-31' THEN 1 
         ELSE NULL 
       END) as cnt,
count(
       CASE 
         WHEN rfeeddate<='2012-3-31' THEN 1 
         ELSE NULL 
       END) as cntTill
from mstchvs where flagr=1 and 
group by state,district,source
order by 1,2,3

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
patriktCommented:
Note:
Do not use date literals in form 'YYYY-MM-DD' or other locale dependent form. Parsing of literals is dependent on language settings of connection. It can cause problems.

Only safe form is ISO date exact 'YYYYMMDD'. This is language neutral.
0
 
searchsanjaysharmaAuthor Commented:
Great, now is it further possible to append the further data for same condition of dates.
and same base data, i.e state,district,source
from other tables like t1 and t2.

The idea is i want to generate the consolidated report from all the tables in horizontal output.
0
 
patriktCommented:
If you want partitioned results you should look on window agregate functions also:
COUNT() OVER (PARTITION BY <>)  see syntax in help.
0
 
searchsanjaysharmaAuthor Commented:
Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now