How to evaluate data in multi rows

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

How to evaluate data in multi rows

Author

Topic

newbie_01
Starting Member

1 Post

Posted - 2013-10-19 : 23:22:49

I am trying to evaluate dates within my sub queries for A,B,C,D. If there is a count > 0 in the 'A' category, I need to evaluate if the servicedates in B,C,D are within 30 days of 'A'. How would I code this within my current code? I appreciate any assistance on this!! Thank you very much.

select
customernumber,
SUM(CASE WHEN category = 'A' then 1 else 0 end) 'A',
SUM(CASE WHEN category = 'B' then 1 else 0 end) 'B',
SUM(CASE WHEN category = 'C' then 1 else 0 end) 'C',
SUM(CASE WHEN category = 'D' then 1 else 0 end) 'D'
from
(
select
customernumber,category,servicedate
from
(
select
customernumber, servicedate,
CASE WHEN service IN ('OS','NS') then 'A'
WHEN service IN ('PQ') then 'B'
WHEN service IN ('PR') then 'C'
WHEN repair IN ('ZZ') then 'D'
END category,
MAX(part)part_cnt,MAX(item)item_cnt,ordernumber
from dbo.repairs
where
( servicedate between '2012-01-01' and '2012-12-31' and
( (service in ('OS','NS') and
part in (‘11111’,’11112’,’11113’)))
or
(service in ('PQ','PR') and
item in ('AAAAA',’BBBBB’,’CCCCC’,’DDDDD’)) ) )
or
( servicedate between '2012-01-01' and '2012-12-31' and
part in (‘22222’,’33333’) and item in ('57B789234',’57Z987234’) )
GROUP BY CUSTOMERNUMBER, SERVICEDATE,
CASE WHEN service IN ('OS','NS') then 'A'
WHEN service IN ('PQ') then 'B'
WHEN service IN ('PR') then 'C'
WHEN repair IN ('ZZ') then 'D'
END,ordernumber

) as temp

GROUP BY CUSTOMERNUMBER,category,SERVICEDATE

) temp2

GROUP BY CUSTOMERNUMBER

My BEFORE the case statement:

Customernumber A B C D SERVICEDATE
123456789 1 0 0 0 2012-02-07
123456789 0 0 1 0 2012-02-28
123456789 0 0 1 0 2012-03-06
123456789 0 1 0 0 2012-12-17

My output after case statement:

Customernumber A B C D
123456789 1 1 2 0

What I need my output to look like after case statement:

Customernumber A B C D Service w/in 3 mnths
123456789 1 1 2 0 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 06:31:55

[code]
select
customernumber,
SUM(CASE WHEN category = 'A' then 1 else 0 end) 'A',
SUM(CASE WHEN category = 'B' then 1 else 0 end) 'B',
SUM(CASE WHEN category = 'C' then 1 else 0 end) 'C',
SUM(CASE WHEN category = 'D' then 1 else 0 end) 'D',
SUM(CASE WHEN category IN ('B','C','D') AND SERVICEDATE <= DATEADD(dd,30,Adt) THEN 1 ELSE 0 END) AS [Service w/in 3 months]
from
(
select
customernumber,category,servicedate,
MAX(case when category ='A' then SERVICEDATE end) over (partition by customernumber) as Adt
from
(
select
customernumber, servicedate,
CASE WHEN service IN ('OS','NS') then 'A'
WHEN service IN ('PQ') then 'B'
WHEN service IN ('PR') then 'C'
WHEN repair IN ('ZZ') then 'D'
END category,
MAX(part)part_cnt,MAX(item)item_cnt,ordernumber
from dbo.repairs
where
( servicedate between '2012-01-01' and '2012-12-31' and
( (service in ('OS','NS') and
part in ('11111','11112','11113')))
or
(service in ('PQ','PR') and
item in ('AAAAA',’BBBBB’,’CCCCC’,’DDDDD’)) ) )
or
( servicedate between '2012-01-01' and '2012-12-31' and
part in (‘22222’,’33333’) and item in ('57B789234',’57Z987234’) )
GROUP BY CUSTOMERNUMBER, SERVICEDATE,
CASE WHEN service IN ('OS','NS') then 'A'
WHEN service IN ('PQ') then 'B'
WHEN service IN ('PR') then 'C'
WHEN repair IN ('ZZ') then 'D'
END,ordernumber
) as temp
GROUP BY CUSTOMERNUMBER,category,SERVICEDATE
) temp2
GROUP BY CUSTOMERNUMBER
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources