June 4, 2012 at 3:41 am
how do i modify this script to wrk for multiple rows. currently works for jst a row of information.
-------------------------------------------
SELECT ( SELECT COUNT(*)
FROM account A
WHERE STAT='O' and dormant='N' and code='200'
GROUP BY code) active,
(SELECT COUNT(*)
FROM account WHERE OPEN_DATE
BETWEEN '7-MAY-2012' AND '31-MAY-2012' and code='200'
GROUP BY CODE) new,
( SELECT COUNT(*)
FROM account
WHERE STAT='O' and code ='200'
GROUP BY code) open,
GET_REGION (code)REGION, code,GET_NAME (code) NAME
FROM account
where code ='200'
GROUP BY code
want this to generate about 200 rows for diff codes, also want to avoid using union. thanx
June 4, 2012 at 4:35 am
Try like
select
case when STAT='O' and dormant='N' and code='200' then 1 esle null end active,
case WHEN OPEN_DATE BETWEEN '7-MAY-2012' AND '31-MAY-2012' and code='200' then 1 else null end new,
case when STAT='O' and code ='200' then 1 else null end open
From account
where code ='200'
June 4, 2012 at 5:08 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 8:23 am
Here is the sql with some formatting:
SELECT (
SELECT COUNT(*)
FROM account A
WHERE STAT = 'O'
AND dormant = 'N'
AND code = '200'
GROUP BY code
) active
,(
SELECT COUNT(*)
FROM account
WHERE OPEN_DATE BETWEEN '7-MAY-2012'
AND '31-MAY-2012'
AND code = '200'
GROUP BY CODE
) new
,(
SELECT COUNT(*)
FROM account
WHERE STAT = 'O'
AND code = '200'
GROUP BY code
)
OPEN
,GET_REGION(code) REGION
,code
,GET_NAME(code) NAME
FROM account
WHERE code = '200'
GROUP BY code
Now that this is more legible is pretty obvious this won't actually work. You have an OPEN statement off all by itself. It also appears you have a couple of scalar functions in here. But more than anything this entire select statement is an aggregate.
As Gail said, if you want some help you need to post some information to work with. ddl, sample data, desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2012 at 8:52 am
Sean Lange (6/4/2012)
You have an OPEN statement off all by itself.
Open is the alias for the subquery. It's valid, though probably should be in [] as a reserved word.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2012 at 9:02 am
GilaMonster (6/4/2012)
Sean Lange (6/4/2012)
You have an OPEN statement off all by itself.Open is the alias for the subquery. It's valid, though probably should be in [] as a reserved word.
It actually will not even parse for me (2008r2). It says syntax error near ','. Once I wrap it in [] it will at least parse.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply