Query doubt

[tammy2512]

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

[kplcjl]

What, besides the comma that goes nowhere right after insumpo? Or the variable (invcount) that doesn't exist in the commented section? Or that you can't do derived calculations in an aggregate query? Or that this is an egregiously expensive query on a big table? Or you could get divide by zero errors?

Why, nothing at all!

declare @inv table(regdate datetime,amt real,nbr varchar(10))
insert into @inv
values(GETDATE()-100,10.3,''),(GETDATE()-100,5.3,'x'),(GETDATE()-300,10.3,''),(GETDATE()-100,100.3,'x'),(GETDATE()-101,10.3,'4')
select *,cast(case when InSum != 0 then (Insumpo / InSum ) else null end as decimal (20,4)) as sp,
CAST(case when incountpo != 0 then (incountpo/incountpo) else null end 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 @inv
	group by year(regdate), month(regdate),CONVERT(varchar(3),regdate,100)) x

[kplcjl]

What I meant by "expensive query" is that you are executing functions. Each time you execute a function, it costs CPU, which means time and possibly money. Functions are handy, but if you can avoid them, do so. You don't have a where clause in your example, but if your query needs to do so, it increases the importance of finding a non"function" query. The group by clause increases the cost because it executes the function when it is calculating the group it goes into AND when it resolves the records. Here is an example table that removes the functions from the query by calculating them in a very short table.
You join this table with your query on regdate between stdate and endate and group your records by the fields in this table. Here is how to produce the table:

DECLARE @Years TABLE([Year] smallint,[Month] tinyint, inmonth char(3), stdate datetime, endate datetime)
DECLARE @styr DATETIME = '19900101', @enyr DATETIME = '19910101',@cnt tinyint = 1
WHILE @styr < @enyr
BEGIN
	INSERT @Years SELECT YEAR(@styr),MONTH(@styr), CONVERT(VARCHAR(3),@styr,100), @styr,DATEADD(millisecond,-3,DATEADD(MONTH,1,@styr))
	SET @styr=DATEADD(MONTH,1,@styr)
END
SET @styr='19900101'
WHILE DATEADD(YEAR,@cnt,@styr) < GETDATE()
BEGIN
	INSERT @Years
		SELECT [Year]+@cnt,[Month], inmonth, DATEADD(YEAR,@cnt,stdate),DATEADD(YEAR,@cnt,endate)
		FROM @Years where stdate between @styr and @enyr-1
	SET @cnt=@cnt+1
END
UPDATE @Years SET endate = DATEADD(MILLISECOND,-3,DATEADD(MONTH,1,stdate))
WHERE Month = 2
SELECT * FROM @Years y
ORDER BY Month, Year