Union All and where - 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

Union All and where

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-17 : 11:37:59

Hi, i am very new to this so please be gentle

I have two tables both containing the same type of information , one is historical , one is current data, i am looking to extract the same information from both, ( these are call logging tickets and dates etc ) and then group the information by month and year, so i can see all the calls logged by month and year across historical and current table ,so far so good as i have used a union all to do this, script below ( which works ), what i now need to do is look at ones that remained open per month per year, the field i can look at is the CLOSEDATE field , i.e CLOSEDATE is greater than the end of the month / year i am grouping it by then count the number of occurences up to give me a total .... any help greatly greatly recieved

select month(OPENDATE) as month2, year(OPENDATE) as year2, count(REFERENCE) as total
FROM
(Select suppdeskdev.F0007_HIST_SUPEVENT.OPENDATE, suppdeskdev.F0007_HIST_SUPEVENT.REFERENCE
From suppdeskdev.F0007_HIST_SUPEVENT
UNION ALL
Select suppdeskdev.F0007_SUPEVENT.OPENDATE, suppdeskdev.F0007_SUPEVENT.REFERENCE
FROM suppdeskdev.F0007_SUPEVENT)x

group by Year(OPENDATE) , Month(OPENDATE)
Order by year2 Asc,month2 Asc

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-17 : 11:59:15

without sample data not sure if this is what you needed

select month(OPENDATE) as month2, year(OPENDATE) as year2, count(REFERENCE) as total
FROM
(Select suppdeskdev.F0007_HIST_SUPEVENT.OPENDATE, suppdeskdev.F0007_HIST_SUPEVENT.REFERENCE
From suppdeskdev.F0007_HIST_SUPEVENT
Where (month(closeDate)>month(opendate) or year(closeDate)>year(opendate)
UNION ALL
Select suppdeskdev.F0007_SUPEVENT.OPENDATE, suppdeskdev.F0007_SUPEVENT.REFERENCE
FROM suppdeskdev.F0007_SUPEVENT
Where (month(closeDate)>month(opendate) or year(closeDate)>year(opendate))x

group by Year(OPENDATE) , Month(OPENDATE)
Order by year2 Asc,month2 As

Cheers
MIK

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-18 : 04:06:18

Hi MIK_2008, many thanks for your reply , i tried your suggestion but get the following error message

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'UNION'.
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'x'.

anything obvious ???

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-18 : 04:29:10

pardon missed a bracket

Check the below if its ok ?

select
month(OPENDATE) as month2
, year(OPENDATE) as year2
, count(REFERENCE) as total
FROM
(
Select suppdeskdev.F0007_HIST_SUPEVENT.OPENDATE, suppdeskdev.F0007_HIST_SUPEVENT.REFERENCE
From suppdeskdev.F0007_HIST_SUPEVENT
Where (month(closeDate)>month(opendate) or year(closeDate)>year(opendate))

UNION ALL
Select suppdeskdev.F0007_SUPEVENT.OPENDATE, suppdeskdev.F0007_SUPEVENT.REFERENCE
FROM suppdeskdev.F0007_SUPEVENT
Where (month(closeDate)>month(opendate) or year(closeDate)>year(opendate))
)x

group by Year(OPENDATE) , Month(OPENDATE)
Order by year2 Asc,month2 Asc

emailuser
Yak Posting Veteran

74 Posts

Posted - 2011-02-18 : 07:57:15

Hi MIK that has worked .. really appreciate your help

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-18 : 22:14:27

quote:
Originally posted by emailuser

Hi, i am very new to this so please be gentle

"A problem well stated is a problem half solved." -- Charles F. Kettering

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.

I am not ging ot guess until you follow basic Netiquette.

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

If you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/159327190

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