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

CTE

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-11 : 07:42:29

I am trying to learn how to write them so I don't have to use temp tables. I got this to work:


with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu 
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

select * from offsetd

This query produces this:


Doc    recno      week_start_date        drycde      drydat               dryfu
009	01	2010-06-04 08:02:44.973	   9W	2010-04-17 00:00:00.000	   1
009	05	2010-06-04 08:02:44.990	   5W	2010-07-01 00:00:00.000	   0
009	01	2010-06-04 08:02:44.990	   9W	2010-05-27 00:00:00.000	   1
009	02	2010-06-04 08:02:44.990	   5W	2010-05-21 00:00:00.000	   1
009	01	2010-06-04 08:02:45.007	   7W	2010-04-07 00:00:00.000	   1
009	01	2010-06-04 08:02:45.007	   9W	2010-04-08 00:00:00.000	   1
009	01	2010-06-04 08:02:45.020	   5W	2010-05-13 00:00:00.000	   1
009	02	2010-06-04 08:02:45.037	   9W	2010-06-12 00:00:00.000	   0
009	03	2010-06-04 08:02:45.037	   5W	2010-06-23 00:00:00.000	   0
009	05	2010-06-04 08:02:45.050	   5W	2010-05-29 00:00:00.000	   0

I now want to add this to the CTE:
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M' end

I added this but getting incorrect syntax near '='


with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu 
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu 
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M'  end 
from offsetd


select * from offsetd

what am I missing?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 07:44:52

maybe the missing comma after dryfu?

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-11 : 08:03:29

Yes that was it thanks. Now I'm getting incorrect syntax near the keyword select:

with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu,
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M' end
from offsetd

select * from offsetd

It's referring to the above select statement.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 08:21:32

quote:
Originally posted by JJ297

Yes that was it thanks. Now I'm getting incorrect syntax near the keyword select:

with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu,
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M' end
from offsetd)

select * from offsetd

It's referring to the above select statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-11 : 08:37:15

Thanks! Almost there got another error that says:

The multi-part identifier "w.week_start_date" could not be bound.

It's referrring to this line:

with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 08:47:04

quote:
Originally posted by webfred

quote:
Originally posted by JJ297

Yes that was it thanks. Now I'm getting incorrect syntax near the keyword select:

with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu,
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M' end
from offsetd)

select * from offsetd

It's referring to the above select statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-11 : 08:59:52

I tried that but still getting:

The multi-part identifier "w.week_start_date" could not be bound.


with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu 
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu, 
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M'  end 
from offsetd)


select * from Offsetd

Error is referring to the first line

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 09:05:03

quote:

Originally posted by JJ297

I tried that but still getting:

The multi-part identifier "w.week_start_date" could not be bound.


with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu 
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu, 
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M'  end 
from offsetd)


select * from Offsetd

Error is referring to the first line

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-11 : 09:11:46

THANKS!!!

Okay off to add more to it so stay tuned if I can't get it. Will try it on my own first.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 09:12:32

good luck

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-11 : 11:39:30

I'm back

I got it to work but I don't have the results I'm looking for. Here's what I've done...

This query

with Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu 
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, week_start_date, drycde, drydat, dryfu, 
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M'  
end 
from offsetd)


select * from Over49

Gives me these results which is fine


Doc    recno    week_start_date          drycde      drydat             dryfu   Clear
009	01	2010-06-04 08:02:44.973	  9W	2010-04-17 00:00:00.000	  1	M
009	05	2010-06-04 08:02:44.990	  5W	2010-07-01 00:00:00.000	  0	M
009	01	2010-06-04 08:02:44.990	  9W	2010-05-27 00:00:00.000	  1	M
009	02	2010-06-04 08:02:44.990	  5W	2010-05-21 00:00:00.000	  1	M
009	01	2010-06-04 08:02:45.007	  7W	2010-04-07 00:00:00.000	  1	O
009	01	2010-06-04 08:02:45.007	  9W	2010-04-08 00:00:00.000	  1	O
009	01	2010-06-04 08:02:45.020	  5W	2010-05-13 00:00:00.000	  1	M
009	02	2010-06-04 08:02:45.037	  9W	2010-06-12 00:00:00.000	  0	M
009	03	2010-06-04 08:02:45.037	  5W	2010-06-23 00:00:00.000	  0	M
009	05	2010-06-04 08:02:45.050	  5W	2010-05-29 00:00:00.000	  0	M
009	01	2010-06-04 08:02:45.050	  9W	2010-04-21 00:00:00.000	  1	M
009	02	2010-06-04 08:02:45.050	  9W	2010-06-10 00:00:00.000	  0	M
009	01	2010-06-04 08:02:45.067	  9W	2010-06-13 00:00:00.000	  0	M
009	03	2010-06-04 08:02:45.067	  5W	2010-07-04 00:00:00.000	  0	M

I added this:

Select  b.doc, isnull(totm,0) as totmet, isnull(totc,0) as totclr, b.week_start_date  
From
 (select doc, count(doc) as totm  
from Over49
where clear = 'M'   
group by  doc, clear
)a 
right join
(select doc, count(doc) as totc, week_start_date  
from Over49
group by doc, week_start_date
)b
on b.doc = a.doc
order by doc

which gives me this...

Doc   totmet   totclr    week_start_date             
007	5	2	2010-06-04 08:07:10.347
007	5	1	2010-06-04 08:07:10.363
007	5	2	2010-06-04 08:07:14.160
008	11	3	2010-06-04 08:08:16.427
008	11	2	2010-06-04 08:08:16.443
008	11	4	2010-06-04 08:08:16.457
008	11	2	2010-06-04 08:08:16.473
008	11	2	2010-06-04 08:08:20.270
009	13	1	2010-06-04 08:02:44.973
009	13	3	2010-06-04 08:02:44.990
009	13	2	2010-06-04 08:02:45.007
009	13	1	2010-06-04 08:02:45.020
009	13	2	2010-06-04 08:02:45.037
009	13	3	2010-06-04 08:02:45.050
009	13	3	2010-06-04 08:02:45.067

It's not totaling my doc. What am I doing wrong?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-12 : 11:35:58

It is grouping on week_start_date and they are not equal so every line is displayed.
What is your expected output?

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-16 : 08:34:16

Hello, so sorry for just getting back to you as I was not at work.

Here's the results I'm trying to get from this:

ith Offsetd as (Select doc, pan, hun, recno, w.week_start_date, drycde, drydat, dryfu
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, week_start_date, drycde, drydat, dryfu,
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M'
end
from offsetd)

Select b.doc, isnull(totm,0) as totmet, isnull(totc,0) as totclr, b.week_start_date
From
(select doc, count(doc) as totm
from Over49
where clear = 'M'
group by doc, clear
)a
right join
(select doc, count(doc) as totc, week_start_date
from Over49
group by doc, week_start_date
)b
on b.doc = a.doc
order by doc

To give me this but it's not adding up the doc's.


Doc   totmet   totclr    week_start_date             
007	2	2	6/11/2010 12:00:00 AM	
008	1	3	6/11/2010 12:00:00 AM	
009	2	2	6/11/2010 12:00:00 AM	
016	4	4	6/11/2010 12:00:00 AM
019	3	3	6/11/2010 12:00:00 AM	
020	1	2	6/11/2010 12:00:00 AM	
095	7	7	6/11/2010 12:00:00 AM	
096	5	5	6/11/2010 12:00:00 AM	
192	1	1	6/11/2010 12:00:00 AM	
193	2	2	6/11/2010 12:00:00 AM	
194	6	7	6/11/2010 12:00:00 AM	
195	2	2	6/11/2010 12:00:00 AM	
196	0	2	6/11/2010 12:00:00 AM

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 08:56:43

quote:
Originally posted by JJ297

Hello, so sorry for just getting back to you as I was not at work.

Here's the results I'm trying to get from this:

ith Offsetd as (Select doc, pan, hun, recno, convert(varchar(10),w.week_start_date,101) as week_start_date, drycde, drydat, dryfu
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, week_start_date, drycde, drydat, dryfu,
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M'
end
from offsetd)

Select b.doc, isnull(totm,0) as totmet, isnull(totc,0) as totclr, b.week_start_date
From
(select doc, count(doc) as totm
from Over49
where clear = 'M'
group by doc, clear
)a
right join
(select doc, count(doc) as totc, week_start_date
from Over49
group by doc, week_start_date
)b
on b.doc = a.doc
order by doc

To give me this but it's not adding up the doc's.

Doc totmet totclr week_start_date
007 2 2 6/11/2010 12:00:00 AM
008 1 3 6/11/2010 12:00:00 AM
009 2 2 6/11/2010 12:00:00 AM
016 4 4 6/11/2010 12:00:00 AM
019 3 3 6/11/2010 12:00:00 AM
020 1 2 6/11/2010 12:00:00 AM
095 7 7 6/11/2010 12:00:00 AM
096 5 5 6/11/2010 12:00:00 AM
192 1 1 6/11/2010 12:00:00 AM
193 2 2 6/11/2010 12:00:00 AM
194 6 7 6/11/2010 12:00:00 AM
195 2 2 6/11/2010 12:00:00 AM
196 0 2 6/11/2010 12:00:00 AM

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-16 : 09:03:03

I tried that but got:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '06/11/2010' to data type int.

week_start_date field is set up as datetime


with Offsetd as (Select doc, pan, hun, recno, convert(varchar(10),w.week_start_date,101) as week_start_date, drycde, drydat, dryfu 
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, week_start_date, drycde, drydat, dryfu, 
"clear" = Case when week_start_date - 49 > drydat then 'O' else 'M'  
end 
from offsetd)

Select  b.doc, isnull(totm,0) as totmet, isnull(totc,0) as totclr, b.week_start_date  
From
 (select doc, count(doc) as totm  
from Over49
where clear = 'M'   
group by  doc, clear
)a 
right join
(select doc, count(doc) as totc, week_start_date  
from Over49
group by doc, week_start_date
)b
on b.doc = a.doc
order by doc

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 09:09:32

quote:

Originally posted by JJ297

I tried that but got:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '06/11/2010' to data type int.

week_start_date field is set up as datetime


with Offsetd as (Select doc, pan, hun, recno, convert(varchar(10),w.week_start_date,101) as week_start_date, drycde, drydat, dryfu 
FROM AllDiaries a
inner join weeklyDiariesfile w on a.weeklyid = w.weeklyid
where Drycde in ('5w','7w', '9w')),

Over49 as (Select doc, pan, hun, recno, week_start_date, drycde, drydat, dryfu, 
"clear" = Case when dateadd(dd,-49,convert(datetime,week_start_date)) > drydat then 'O' else 'M'  
end 
from offsetd)

Select  b.doc, isnull(totm,0) as totmet, isnull(totc,0) as totclr, b.week_start_date  
From
 (select doc, count(doc) as totm  
from Over49
where clear = 'M'   
group by  doc, clear
)a 
right join
(select doc, count(doc) as totc, week_start_date  
from Over49
group by doc, week_start_date
)b
on b.doc = a.doc
order by doc

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-16 : 09:25:24

Thank you that did it!!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 10:07:42

my pleasure

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources