Solved

MULTIPLE CTE PROBLEM

Posted on 2013-05-13
2
188 Views
Last Modified: 2013-05-13
Hi All,

I have below code :

WITH ATTENDDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
)

WITH OVERTIMEDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
)

SELECT * FROM ATTENDDATA

UNION ALL

SELECT * FROM OVERTIMEDATA

What should I do ?

Thank you
0
Comment
Question by:emi_sastra
[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
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
Ross Turner earned 500 total points
ID: 39161022
What should I do ?

what are you trying to do ?

Using Multiple Selects:
http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

i noticed your syntax is wrong between the cte's
if you add a comma and drop the with on the second cte it should graft.

Also it might worth adding a marker so a least on the end result you can see what data has come from which table.

you could do this on your select e.g

SELECT *,'ATTENDDATA' as [Type] FROM ATTENDDATA

UNION ALL

SELECT *,'OVERTIMEDATA' as [Type] FROM OVERTIMEDATA

then with your end result you can see what comes from where.


if it not working try below

WITH ATTENDDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
),
OVERTIMEDATA AS (
SELECT
A.*
FROM TDATTEND000101 AS A
INNER JOIN TMEMPLOYEE AS B
ON A.EmpCode = B.EmpCode
WHERE  CONVERT(Char(8), AttendDate, 112) BETWEEN '20130401' AND '20130431'
AND TotalOvertime <> '00:00'
)

SELECT * FROM ATTENDDATA

UNION ALL

SELECT * FROM OVERTIMEDATA

Open in new window

0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 39161065
Hi RossTurner,

Great. It works.

Thank you very much for your help.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

624 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