Help with 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 with query

Author

Topic

phil3061
Starting Member

4 Posts

Posted - 2011-01-20 : 16:52:45

Hello, can someone please help with this query. When I run it separately, it will work, but when I try and combine the 2 it will not. This is the full code:

WITH
FirstCalculation AS
(SELECT m_atschunk.employee AS employee,
SUM(CASE
WHEN
M_ATSCHUNK.rolloffDaysCount = '108545043' AND m_atschunk.infractiondate BETWEEN SYSDATE - 365 AND SYSDATE THEN 1 ELSE 0 END) as rolloffs,
M_ATSCHUNK.INFRACTIONDATE + 365 as infractionDate
FROM M_ATSCHUNK
group by employee, infractionDate, rolloffDaysCount
)
(SELECT g4.*,
SUM (rolloffs) OVER (PARTITION BY employee) AS total_rolloffs
FROM FirstCalculation g4
)
SecondCalculation as
SELECT m_atschunk.calculationDate as calcdate
FROM M_ATSCHUNK
SELECT g1.*,
CASE
WHEN calcdate IS NULL THEN 1
END as Final_CalcDate
FROM SecondCalculation g1

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-20 : 20:07:51

They don't appear to be related...how does


WITH
(SELECT g4.*,
SUM (rolloffs) OVER (PARTITION BY employee) AS total_rolloffs
FROM FirstCalculation g4
)

Relate to this:


SecondCalculation as
SELECT m_atschunk.calculationDate as calcdate
FROM M_ATSCHUNK
SELECT g1.*,
CASE
WHEN calcdate IS NULL THEN 1
END as Final_CalcDate
FROM SecondCalculation g1

You appear to want to use the 2nd part (SecondCalculation) as another CTE, but it is unclear how you intend to use the FirstCalculation.

To separate CTE's you need to use COMMA..and after the last one, do your final select. It appears SecondCalculation is intended to be another CTE, but it doesn't appear to use or relate to the first one. This appears to be to entirely separate queries


;WITH CTE1 AS  (Select ....)

, CTE2 AS (Select....)

Select * FROM CTE2

I keep staring it this and I have no idea what you are trying to do..you need to be more clear, provide your results from each query (with the full query that produced them) along with your desired output . Sample data with structures
and insert statements would help as well.

Based on what you have provided, help is almost impossible.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 10:54:10

is this SQL server? Is SYSDATE available in SQL Server? i think it should be SYSDATETIME then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-25 : 03:25:40

If you use ORACLE post at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources