Taking out section of 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

Taking out section of query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-13 : 14:32:28

I need to take out the bold section and then write a stored procedure to check the T2Reports table with the part I've taken out.


--Truncate table t2report
--INSERT INTO T2report 


Select b.doc, sum(b.[PendinFo]) as [PendinFo], sum(b.[PendInFo300]) as [PendInFo300],sum([PendInDDS]) as [PendInDDS],
sum([PendInDDS300]) as [PendInDDS300],
sum([TotPendFoDDS300]) as [TotPendFoDDS300], sum([TotPendFoDDS350]) as [TotPendFoDDS350],
sum([TotPendFoDDS399]) as [TotPendFoDDS399], sum([TotPendFoDDS400]) as [TotPendFoDDS400],
sum([PendinFo]) + sum([PendInDDS]) as Totpend
--into T2DibPendnew
from
(
SELECT t.doc, ISNULL(COUNT(distinct t.clmssn), 0)AS [PendinFo], 
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clmssn else null end), 0)AS [PendInFo300],
CAST(NULL as int) AS [PendInDDS],
CAST(NULL as int) AS [PendInDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400]
   FROM   seb2a54.pendingclaims.dbo.t2dibpend t  
Where DIB_MVT_SEQ_NUM is NULL and t.cos not in (select cos from seb2a21.specnew.dbo.people where completedt is null)     GROUP  BY t.doc

union all
SELECT t.doc, Isnull(COUNT(distinct t.clmssn), 0) AS [PendinFo], 
Isnull(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0) AS [PendInFo300],
NULL,
NULL,
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clmssn else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clmssn else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clmssn else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400]
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From seb2a54.pendingclaims.dbo.t2dibpend t 
group by clm)b
left join 
seb2a54.pendingclaims.dbo.t2dibpend t on b.dib_mvt_seq_num=t.dib_mvt_seq_num and b.clm=t.clm
 where(DIB_MVT_TYP='r')  and not (LOREC4 like 'R%' or LOREC4 like 'S%' or LOREC4 like 'V%' or
LOREC4 like 'P%') and t.cos not in (select cos from seb2a21.specnew.dbo.people where completedt is null)
group by t.doc

 union all

SELECT t.doc, Isnull(COUNT(distinct t.clm), 0) AS [PendinFo], 
Isnull(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0) AS [PendInFo300],
NULL,
NULL,
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400]
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From seb2a54.pendingclaims.dbo.t2dibpend t 
group by clm)c
left join 
seb2a54.pendingclaims.dbo.t2dibpend t on c.dib_mvt_seq_num=t.dib_mvt_seq_num and c.clm=t.clm
 where (DIB_MVT_TYP='T') and t.cos not in (select cos from seb2a21.specnew.dbo.people where completedt is null)group by t.doc
--WHERE  (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
--       group by t.fo
  
union all

 SELECT t.doc,NULL,NULL, 
Isnull(COUNT(distinct t.clm), 0) AS [PendInDDS],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0)AS [PendInDDS300],
Isnull(COUNT(distinct case when Datediff(Day,t. app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400]
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From seb2a54.pendingclaims.dbo.t2dibpend t 
group by clm)d
left join 
seb2a54.pendingclaims.dbo.t2dibpend t on d.dib_mvt_seq_num=t.dib_mvt_seq_num and d.clm=t.clm
 where(DIB_MVT_TYP='r') and (LOREC4 like '[RSV]%')
 and t.cos not in (select cos from seb2a21.specnew.dbo.people where completedt is null)group by t.doc)
as b
Group By b.doc
order by b.doc

How would I then write a stored procedure to check and t.cos not in (select cos from seb2a21.specnew.dbo.people where completedt is null)

If it's in there I want to delete the claim or should I move it into a different table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 01:32:42

add column cos to table T2report
remove bold lines from above code and uncomment insert part and make it like


Truncate table t2report
INSERT INTO T2report 


Select b.doc, sum(b.[PendinFo]) as [PendinFo], sum(b.[PendInFo300]) as [PendInFo300],sum([PendInDDS]) as [PendInDDS],
sum([PendInDDS300]) as [PendInDDS300],
sum([TotPendFoDDS300]) as [TotPendFoDDS300], sum([TotPendFoDDS350]) as [TotPendFoDDS350],
sum([TotPendFoDDS399]) as [TotPendFoDDS399], sum([TotPendFoDDS400]) as [TotPendFoDDS400],
sum([PendinFo]) + sum([PendInDDS]) as Totpend,sum(cos) as cos
--into T2DibPendnew
from
(
SELECT t.doc, ISNULL(COUNT(distinct t.clmssn), 0)AS [PendinFo], 
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clmssn else null end), 0)AS [PendInFo300],
CAST(NULL as int) AS [PendInDDS],
CAST(NULL as int) AS [PendInDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400],t.cos
   FROM   seb2a54.pendingclaims.dbo.t2dibpend t  
Where DIB_MVT_SEQ_NUM is NULL      GROUP  BY t.doc

union all
SELECT t.doc, Isnull(COUNT(distinct t.clmssn), 0) AS [PendinFo], 
Isnull(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0) AS [PendInFo300],
NULL,
NULL,
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clmssn else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clmssn else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clmssn else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400],t.cos
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From seb2a54.pendingclaims.dbo.t2dibpend t 
group by clm)b
left join 
seb2a54.pendingclaims.dbo.t2dibpend t on b.dib_mvt_seq_num=t.dib_mvt_seq_num and b.clm=t.clm
 where(DIB_MVT_TYP='r')  and not (LOREC4 like 'R%' or LOREC4 like 'S%' or LOREC4 like 'V%' or
LOREC4 like 'P%') group by t.doc

 union all

SELECT t.doc, Isnull(COUNT(distinct t.clm), 0) AS [PendinFo], 
Isnull(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0) AS [PendInFo300],
NULL,
NULL,
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400],t.cos
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From seb2a54.pendingclaims.dbo.t2dibpend t 
group by clm)c
left join 
seb2a54.pendingclaims.dbo.t2dibpend t on c.dib_mvt_seq_num=t.dib_mvt_seq_num and c.clm=t.clm
 where (DIB_MVT_TYP='T') --WHERE  (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
--       group by t.fo
  
union all

 SELECT t.doc,NULL,NULL, 
Isnull(COUNT(distinct t.clm), 0) AS [PendInDDS],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0)AS [PendInDDS300],
Isnull(COUNT(distinct case when Datediff(Day,t. app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as [TotPendFoDDS300],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as [TotPendFoDDS350],
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as [TotPendFoDDS399],
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS [TotPendFoDDS400],t.cos 
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From seb2a54.pendingclaims.dbo.t2dibpend t 
group by clm)d
left join 
seb2a54.pendingclaims.dbo.t2dibpend t on d.dib_mvt_seq_num=t.dib_mvt_seq_num and d.clm=t.clm
 where(DIB_MVT_TYP='r') and (LOREC4 like '[RSV]%')
as b
Group By b.doc
order by b.doc

then in new procedure write code like


CREATE PROC ExcludeUncompletedData
AS
SELECT t.*
FROM T2report t
LEFT JOIN seb2a21.specnew.dbo.people p
ON p.cos = t.cos 
WHERE p.cos IS NULL
GO

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-16 : 19:55:44

Great thanks! I will try it in the morning!

Question for you can you explain this at the bottom:

as b
Group By b.doc
order by b.doc

Why do I only use the b.PendinFO and b.PendInFo300? Shouldn't I use that for all of them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 01:23:55

b is just an alias ie short name for the table. Ideally you need to add alias to all columns. But so far as query engine can relate the column names to correct object without ambiguity it will work fine. In case where same columns are coming from more than one object, then usage of alias is mandatory to determine from which object you want to retrive the info. else query engine will throw error as ambiguos column name as it doesnt understand from which table column you want to retrive the info.

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-17 : 13:59:30

How could I write it so I can delete from the T2report table if cos match the below criteria?

CREATE PROC ExcludeUncompletedData
AS
SELECT t.*
FROM T2report t
LEFT JOIN seb2a21.specnew.dbo.people p
ON p.cos = t.cos
WHERE p.cos IS NULL
GO

I tried this but getting incorrect syntax near 't'

Delete from T2Report t
LEFT JOIN seb2a21.specnew.dbo.people p
ON p.cos = t.cos
WHERE p.cos IS NULL
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 14:18:07

it should be


....
Delete t
from T2Report t
LEFT JOIN seb2a21.specnew.dbo.people p
ON p.cos = t.cos 
WHERE p.cos IS NULL
GO

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-17 : 14:27:51

Perfect thanks. Why couldn't I put the table name there and just the alias?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 14:31:50

because you've already assigned alias to table

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

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-17 : 14:54:54

Okay thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 15:05:01

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources