Using CTE's - 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

Using CTE's

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-25 : 11:41:07

I have two queries where I'm executing the first one and placing into a table then running the second one and adding that to a difrferent table. I wanted to use a CTE so I can add the results from the first query then run the second one and have all of that to go into a new table.

I tried this but getting incorrect syntax near ')' (the bold section below)


With test1 
as
   (select *
    from t16pendmvt
    where cossn in(select cossn from t16pendall)

union all

select n.area, n.reg, n.regionname, n.regionacronym as region, n.dist, count(a.cossn) as pendndds, a.mvt_typ, a.mvt_dest, a.mvt_cdt, a.mvt_loc, b.fo
--into table6
      from test1 a
      join t16pendall  b on a.cossn = b.cossn
      join natdocfile n on n.doc=b.fo
      WHERE (MVT_TYP = 'R') AND (MVT_LOC LIKE ' R%' OR
                      MVT_LOC LIKE ' S%' OR
                      MVT_LOC LIKE ' V%')


group by a.mvt_typ, a.mvt_dest, a.mvt_cdt ,a.mvt_loc,  b.fo, n.area, n.reg, n.regionname, n.regionacronym , n.dist )

Can I use union instead of the CTE?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 12:30:08

What i understand is that you have data in two table which you want to put in single other table.

For that you can simply write a select statement with union why you want to go for CTE.

If possible provide some sample data along your expected output to help us to help you.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-25 : 13:24:17

Here's what I want. I want to join these two queries but the first one has to run first then I need to take those and run the last query.

Here's the query and my expected outcome.



select *
--into table5
from t16pendmvt
where cossn in(select cossn from t16pendall)

select count(a.cossn) as pendndds, a.mvt_typ, a.mvt_dest, a.mvt_cdt, a.mvt_loc, b.fo
from What table do I put here (I need the results from the above query) a 
join t16pendall  b on a.cossn = b.cossn
WHERE     (MVT_TYP = 'R') AND (MVT_LOC LIKE ' R%' OR
                      MVT_LOC LIKE ' S%' OR
                      MVT_LOC LIKE ' V%')
group by a.mvt_typ, a.mvt_dest, a.mvt_cdt ,a.mvt_loc,  b.fo


Expected results:

Area  reg    regname    region  dist  pendndds  myt_typ mvt_dest   Mvt_cdt   mvt_loc   fo
05	E	Chicago	CHI	390	2	R	   	5/21/2010        S3	390
02	I	Denver	DEN	876	1	R	   	10/15/2010 	 R4	876
09	D	Atlanta	ATL	443	1	R	   	8/11/2010 	 S2	444
08	E	Chicago	CHI	517	1	R	   	10/3/2010 	 S1	D04
05	B	New York NYC	178	3	R	   	7/23/2010 	 V2	178
06	D	Atlanta	ATL	325	3	R	   	9/9/2010 	 S3	337
04	D	Atlanta	ATL	600	15	R	   	9/3/2010 	 S1	600
02	C	Phil	PHI	276	4	R	   	10/13/2010 	 S2	529
09	E	Chicago	CHI	467	1	R	   	8/16/2010 	 S1	467
03	C	Phil	PHI	294	2	R	   	8/3/2010 	 S9	A37
09	D	Atlanta	ATL	437	22	R	   	8/30/2010 	 S2	437
07	E	Chicago	CHI	499	8	R	   	10/7/2010 	 S1	499
02	E	Chicago	CHI	540	9	R	   	10/5/2010 	 S5	547

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-25 : 14:34:14

Something like:???

select count(a.cossn) as pendndds, a.mvt_typ, a.mvt_dest, a.mvt_cdt, a.mvt_loc, b.fo
from 
    (
        select *
        from t16pendmvt
        where cossn in(select cossn from t16pendall)
    ) a 
join t16pendall  b on a.cossn = b.cossn
WHERE     (MVT_TYP = 'R') AND (MVT_LOC LIKE ' R%' OR
                      MVT_LOC LIKE ' S%' OR
                      MVT_LOC LIKE ' V%')
group by a.mvt_typ, a.mvt_dest, a.mvt_cdt ,a.mvt_loc,  b.fo

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-25 : 15:07:08

Thank you that's it!!!!!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-27 : 09:31:44

I'm back...

I want to add another field PendDDS300 to the query below. Is this possible?

   
SUM(pendndds) AS penddds300 
FROM   t16mvtcounts 
WHERE  ( mvt_typ = 'R' ) 
       AND ( mvt_loc LIKE ' R%' 
              OR mvt_loc LIKE ' S%' 
              OR mvt_loc LIKE ' V%' ) 
       AND ( Datediff(DAY, mvt_cdt, Getdate()) > 300 ) 
       AND ( Datediff(DAY, mvt_cdt, Getdate()) < 350 )

to this query?

SELECT n.area, 
       n.reg, 
       n.regionname, 
       n.regionacronym AS region, 
       n.dist, 
       COUNT(a.cossn)  AS pendndds, 
       a.mvt_typ, 
       a.mvt_dest, 
       a.mvt_cdt, 
       a.mvt_loc, 
       b.fo 
--into T16mvtCounts 
FROM   (SELECT * 
        FROM   t16pendmvt 
        WHERE  cossn IN(SELECT cossn 
                        FROM   t16pendall)) a 
       JOIN t16pendall b 
         ON a.cossn = b.cossn 
       JOIN natdocfile n 
         ON n.doc = b.fo 
WHERE  ( mvt_typ = 'R' ) 
       AND ( mvt_loc LIKE ' R%' 
              OR mvt_loc LIKE ' S%' 
              OR mvt_loc LIKE ' V%' ) 
GROUP  BY a.mvt_typ, 
          a.mvt_dest, 
          a.mvt_cdt, 
          a.mvt_loc, 
          b.fo, 
          n.area, 
          n.reg, 
          n.regionname, 
          n.regionacronym, 
          n.dist 
ORDER  BY fo

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-27 : 09:46:22

Sorry...I want to add this query:


select mvt_cdt, count(cossn) AS PendDDS300

FROM  T16pendmvt


WHERE  (MVT_TYP = 'R') AND (MVT_LOC LIKE ' R%' OR
                 MVT_LOC LIKE ' S%' OR
                 MVT_LOC LIKE ' V%') AND 
                 (DATEDIFF(day, mvt_cdt, GETDATE()) > 300) AND  
                 (DATEDIFF(day, mvt_cdt, GETDATE()) < 350)
                         
 group by mvt_cdt

To this query:


SELECT n.area, 
       n.reg, 
       n.regionname, 
       n.regionacronym AS region, 
       n.dist, 
       COUNT(a.cossn)  AS pendndds, 
       a.mvt_typ, 
       a.mvt_dest, 
       a.mvt_cdt, 
       a.mvt_loc, 
       b.fo 
--into T16mvtCounts 
FROM   (SELECT * 
        FROM   t16pendmvt 
        WHERE  cossn IN(SELECT cossn 
                        FROM   t16pendall)) a 
       JOIN t16pendall b 
         ON a.cossn = b.cossn 
       JOIN natdocfile n 
         ON n.doc = b.fo 
WHERE  ( mvt_typ = 'R' ) 
       AND ( mvt_loc LIKE ' R%' 
              OR mvt_loc LIKE ' S%' 
              OR mvt_loc LIKE ' V%' ) 
GROUP  BY a.mvt_typ, 
          a.mvt_dest, 
          a.mvt_cdt, 
          a.mvt_loc, 
          b.fo, 
          n.area, 
          n.reg, 
          n.regionname, 
          n.regionacronym, 
          n.dist 
ORDER  BY fo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources