[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

How to solve this complex query.

Posted on 2012-03-27
Medium Priority
249 Views
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
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
• 4
• 3

LVL 12

Expert Comment

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
``````

You can use physical DistrictsTable or construct it from your data if it is possible.
0

Author Comment

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

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
``````
0

LVL 12

Expert Comment

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

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

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

ID: 37776543
Great
0

Featured Post

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security): Â  Â . Check for publisher's certificate revocation Â  Â . Check for server ceâ€¦
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vulnâ€¦
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonstâ€¦
Suggested Courses
Course of the Month13 days, 4 hours left to enroll