September 14, 2010 at 1:50 pm
hello,
I am facing a problem with the following code:
select year(regdate) as [Year],month(regdate) as [Month],
CONVERT(varchar(3),regdate,100)as inmonth,
count(*) as incount, cast(sum(amt)as decimal(20,2))as insum,
count(case when nbr <>' ' then amt else Null end) as incountpo,
SUM(case when nbr <>' ' then (amt)else 0.00 end)as insumpo,
--cast( (Insumpo / InSum ) as decimal (20,4)) as sp,
--CAST(incountpo/invcount)as decimal(20,4))as tp
from invoices
group by year(regdate), month(regdate),CONVERT(varchar(3),regdate,100)
The code is not recognizing the commented lines in the above code which is obvious
Could you please help me in the above query.
Thank you
September 14, 2010 at 1:55 pm
If I understand your question correctly, you want the code to execute with the two lines commented-out, correct? The syntax error is because you have a comma after "insumpo" at the end of the SUM function.
If this doesn't answer your question, please reply with more specifics.
HTH,
Cindy
September 14, 2010 at 2:07 pm
I apologize for not being clear; yes i do want the code to execute with the commented lines;
--cast( (Insumpo / InSum ) as decimal (20,4)) as sp,
--CAST(incountpo/invcount)as decimal(20,4))as tp
but its giving an error, as we can see that the fields insumpo, insum,incountpo and invcount are aliases which is being calculated in the same code itself in the code above the two commented lines.
Thank you for your time
September 14, 2010 at 2:16 pm
to use the alias of your calculation, you have to wrap it up as a sub query; otherwise you would have to use the full calculation inline
this is what you are after, i think:
SELECT
CAST((Insumpo / InSum ) AS DECIMAL(20,4)) AS sp,
CAST((incountpo /invcount) AS DECIMAL(20,4)) AS tp,
MySubQuery.*
FROM (
SELECT
YEAR(regdate) AS [Year],
MONTH(regdate) AS [Month],
CONVERT(varchar(3),regdate,100) AS inmonth,
COUNT(*) AS incount,
CAST(SUM(amt) AS decimal(20,2)) AS insum,
COUNT(CASE WHEN nbr <>' ' THEN amt ELSE NULL END) AS incountpo,
SUM(CASE WHEN nbr <>' ' THEN (amt)ELSE 0.00 END) AS insumpo
FROM invoices
GROUP BY
YEAR(regdate),
MONTH(regdate),
CONVERT(varchar(3),regdate,100)
) AS MySubQuery
Lowell
September 14, 2010 at 2:17 pm
You can't use a computed column value in another computed column on the same table.
You'll need to just duplicate the code for those columns, such as:
select year(regdate) as [Year],month(regdate) as [Month],
CONVERT(varchar(3),regdate,100)as inmonth,
count(*) as incount, cast(sum(amt)as decimal(20,2))as insum,
count(case when nbr <>' ' then amt else Null end) as incountpo,
SUM(case when nbr <>' ' then (amt)else 0.00 end)as insumpo,
cast((SUM(case when nbr <>' ' then (amt)else 0.00 end) / cast(sum(amt)as decimal(20,2)) ) as decimal (20,4)) as sp,
CAST(count(case when nbr <>' ' then amt else Null end) / invcount)as decimal(20,4))as tp
from invoices
group by year(regdate), month(regdate),CONVERT(varchar(3),regdate,100)
Or use CTE
But without table definitions and because of a few case statements, you'd have to test that syntax..
edit: Lowell's example is more elegant 🙂
September 14, 2010 at 2:20 pm
EDIT: (LOL, beaten to it twice. 😉 Ah well, have your choice of formatting then. 🙂 ) /EDIT
Best to do the aggregation as a subquery and your calculations in a wrapper, like so:
select
[year],
[month],
inmonth,
incount,
insum,
incountpo,
insumpo,
cast( (insump/insum) AS decimal(20, 4)) AS sp,
cast( (incountpo/invcount) AS decimal (20,4)) AS tp
FROM
(select
year(regdate) as [Year],
month(regdate) as [Month],
CONVERT(varchar(3),regdate,100)as inmonth,
count(*) as incount,
cast(sum(amt)as decimal(20,2))as insum,
count(case when nbr <> ' ' then amt else Null end) as incountpo,
SUM(case when nbr <> ' ' then (amt)else 0.00 end)as insumpo
from
invoices
group by
year(regdate),
month(regdate),
CONVERT(varchar(3),regdate,100)
) AS drv
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 14, 2010 at 3:19 pm
Thank you all so much for your replies, it really helped me a lot!!!!!
I added more conditions to the code, I am getting the correct result, but could you please check and see I am going the right way; I was wondering if adding so many case conditions in the select statement effects the performance of the query. Wen I executed the code; it hardly took a minute.
select
[year],
[month],
inmonth,
incount,
insum,
incountpo,
insumpo,
cast( (insump/insum) AS decimal(20, 4)) AS sp,
cast( (incountpo*1.0E/invcount) AS decimal (20,4)) AS tp
FROM
(select
year(regdate) as [Year],
month(regdate) as [Month],
CONVERT(varchar(3),regdate,100)as inmonth,
count(case when po='y' and status in ('p','o') then amt else Null end) as invcount,
cast(sum(case when po='y' and status in ('p','o') then amt else Null end)as decimal(20,2))as insum,
count(case when nbr <> ' ' then amt else Null end) as incountpo,
SUM(case when nbr <> ' ' then (amt)else 0.00 end)as insumpo
from
invoices
group by
year(regdate),
month(regdate),
CONVERT(varchar(3),regdate,100)
) AS drv where incountpo!=o and insum!=0
Could you also please let me know how to use an order by year(regdate) clause; as an order by is not possible in a sub-query.
thanks once agn!!
September 14, 2010 at 3:24 pm
I added the order by clause at the end, it worked.. but plz check the code and let me know of any more changes if required.
September 14, 2010 at 3:43 pm
Not sure of your baseline for the data, I'm not sure if just under a minute is good, or bad, for this. However, to case statements:
In the select clause, even though you wrapped your aggregates with them, they're not as heavy as you'd expect. Where you want to *try* to avoid them is in your table joins and where clause, if you can. That'll give you some extra load.
Sometimes it's just unavoidable unless you want to write scripts longer then a football field, though, so for maintainability don't completely toss the idea out the window, just be prepared to look into alternatives if the query won't go fast enough or soaks too many resources for whatever purpose it has.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply