Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • 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 TurnerManagement Information Support AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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