Help needed for a union query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help needed for a union query

Author

Topic

mohandoss
Starting Member

1 Post

Posted - 2011-02-21 : 14:22:47

SELECT SUM(QU.CONTACT_COMPLIANCE_COUNT) CONTACT_COMPLIANCE_COUNT,
QU.CONTACT_COMPLIANCE_TYPE CONTACT_COMPLIANCE_TYPE
FROM (
SELECT CASE
WHEN application.filingdate > CURRENT_DATE THEN 'Overdue'
WHEN application.filingdate = CURRENT_DATE THEN 'Due Today'
WHEN application.filingdate BETWEEN CURRENT_DATE +1 AND CURRENT_DATE +4 THEN '1 - 4 Days'
WHEN application.filingdate BETWEEN CURRENT_DATE +5 AND CURRENT_DATE +14 THEN '5 - 14 Days'
WHEN application.filingdate <= CURRENT_DATE + 15 THEN '15+ Days'
END CONTACT_COMPLIANCE_TYPE,
1 CONTACT_COMPLIANCE_COUNT
from application,applicationstatus where
application.applicationid=applicationstatus.applicationid and applicationstatus.status='AS19001'
union
select distinct('Overdue') CONTACT_COMPLIANCE_TYPE, 0 CONTACT_COMPLIANCE_COUNT from codetableitem
union
select distinct('Due Today') CONTACT_COMPLIANCE_TYPE, 0 CONTACT_COMPLIANCE_COUNT from codetableitem
union
select distinct('1 - 4 Days') CONTACT_COMPLIANCE_TYPE, 0 CONTACT_COMPLIANCE_COUNT from codetableitem
union
select distinct('5 - 14 Days') CONTACT_COMPLIANCE_TYPE, 0 CONTACT_COMPLIANCE_COUNT from codetableitem
union
select distinct('15+ Days') CONTACT_COMPLIANCE_TYPE, 0 CONTACT_COMPLIANCE_COUNT from codetableitem
) QU
GROUP BY QU.CONTACT_COMPLIANCE_TYPE

With this query I am trying, to add 0's to the types which does not return any value. For example if

"SELECT CASE
WHEN application.filingdate > CURRENT_DATE THEN 'Overdue'
WHEN application.filingdate = CURRENT_DATE THEN 'Due Today'
WHEN application.filingdate BETWEEN CURRENT_DATE +1 AND CURRENT_DATE +4 THEN '1 - 4 Days'
WHEN application.filingdate BETWEEN CURRENT_DATE +5 AND CURRENT_DATE +14 THEN '5 - 14 Days'
WHEN application.filingdate <= CURRENT_DATE + 15 THEN '15+ Days'
END CONTACT_COMPLIANCE_TYPE,
1 CONTACT_COMPLIANCE_COUNT
from application,applicationstatus where
application.applicationid=applicationstatus.applicationid and applicationstatus.status='AS19001'"

this query returns only,"Overdue" "4"

I want to add "Due Today" "0" and "1 - 4 Days" "0" and "5 - 14 Days" "0" and "15+ Days" "0". So that the graph will have all the types.

But the problem with this query is, it is returning as expected. Could somebody help me with this.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-21 : 19:54:18

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions AND formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible AND not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

I am sorry you had only one application, as shown by the singular table name. But why is an attribute like application status is in its own table. It is part of an application; it is in a column. In short, your schema is wrong.

Why are you doing UNION and SELECT DISTINCT? Your programming is wrong.

What can a “Code_Table_Item” be? The name of a table never has the meta-data word “table in it. Did T-SQL finally get a CURRENT_DATE?

You need a view to do this kind of thing

CREATE VIEW Application_Status
AS
SELECT application_id, application_status,
CASE
WHEN application_filing_date > CURRENT_DATE
THEN 'Overdue'
WHEN application_filing_date = CURRENT_DATE
THEN 'Due Today'
WHEN application_filing_date
BETWEEN CURRENT_DATE +1 AND CURRENT_DATE +4
THEN '1 - 4 Days'
WHEN application_filing_date
BETWEEN CURRENT_DATE +5 AND CURRENT_DATE +14
THEN '5 - 14 Days'
WHEN application_filing_date <= CURRENT_DATE + 15
THEN '15+ Days'
ELSE NULL END AS contact_compliance_type,
..
FROM Applications AS A;

>> With this query I am trying, to add 0's to the contact_compliance_types which does not return any value. <<

SELECT CURRENT_DATE, contact_compliance_type,
COUNT(*) AS contact_compliance_type_cnt
FROM (VALUES ('Overdue'), ('Due Today'),
('1 - 4 Days'), ('5 - 14 Days'), ('15+ Days')
) AS X(contact_compliance_type)
LEFT OUTER JOIN
Applications AS A
ON X.contact_compliance_type = A.contact_compliance_type
WHERE ..
GROUP BY contact_compliance_type;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources