Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

MULTIPLE CTE PROBLEM

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
emi_sastra
Asked:
emi_sastra
1 Solution
 
Ross TurnerCommented:
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
 
emi_sastraAuthor Commented:
Hi RossTurner,

Great. It works.

Thank you very much for your help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now