Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to solve this complex query.

Posted on 2012-03-27
7
Medium Priority
?
252 Views
Last Modified: 2012-03-28
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
Comment
Question by:searchsanjaysharma
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 37775228
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
 

Author Comment

by:searchsanjaysharma
ID: 37775378
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
 
LVL 12

Accepted Solution

by:
patrikt earned 2000 total points
ID: 37775591
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:patrikt
ID: 37775605
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
 

Author Comment

by:searchsanjaysharma
ID: 37776184
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
 
LVL 12

Expert Comment

by:patrikt
ID: 37776240
If you want partitioned results you should look on window agregate functions also:
COUNT() OVER (PARTITION BY <>)  see syntax in help.
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 37776543
Great
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

580 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