using sql to make several calculations 2

[e106199]

hi all,
i had a request about creating a grid type report which will show the result of several calculation of my db fields in .NET section and a good suggestion was to use sql for this. So sql will make all calculations and i ll show the results in a grid. The problem is i dont have much experience on this and need some guidance.
Here is what i m trying to accomplish:
i have a table with say 5 columns. each column keeps 0 or 1 or <null>. the last column is the date column.
user will select a start and end date and i will find the sum off all 1's that are in the interval for each column and group them by month. And the result will be the source of my grid.
here is a sample db schema:
col1 col2 col3 col4 coldate
----- ---- ---- ---- -------
<null> 1 1 1 1/2/2005
0 0 <null> 1 1/7/2005
1 1 <null> <null> 3/1/2005

if user selects 1/1/2005 as his start date and 3/2/2005 as his end date here is what i want him to see:
month totalcol1 totalcol2 totalcol3 totalcol4
----- --------- --------- --------- ---------
jan 05 0 1 1 2
feb 05 0 0 0 0
mar 05 1 1 0 0

this will show in my grid so i have to give grid such structure.
How can i make the calculations on sql side and create such structure for the grid?
thank you
-shane

 

[carlen]

select select right(convert(char(11),getdate(),13),8), sum(col1),sum(col2),sum(col3),sum(col4)
group by right(convert(char(11),getdate(),13),8)


if col 1 is a bit collumn u will get an error as cant perform math on bits so will need to cast or convert to an int

 

[carlen]

ignore the double select thats my fat fingers

 

[vongrunt]

That won't display record for February...

Create temp table holding all relevant months first, then use left join on something similar to query above.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

Do you have an ID column in your table?

 

[e106199]

no there is no id column, is it necessary in such scenerio?
actually this is not a table. its a view.

thanks for the answers, sorry for being a pain but can this be a stored procedure so i can create a datatable from it and bind it to my grid. if so how?
thank you
-shane

 

[jbenson001]

You can create the SP, to return the data that you want and how you want.
From Visual Studio, you can use a sqldataadapter object to call the SP, and fill a dataset or datatable. Then you bind that to your grid.

 

[e106199]

jbenson001,
this is what i m asking for. how can i create this stored procedure? basically it will take start date and end date as parameters and return me all the column totals grouped in months.
thanks,
-shane

 

[jbenson001]

Like me and c8msm have said, you can google for examples. If you don't know any sql at all, there will be a learning curve, but not hard. Is there anyone you work with that know SQL and can help you?
We don't want to write the code for you here, we are here to help. Take a look around and make an attempt, then we can help as you go along.
What you are asking for is not very complicated. So I suggest you poke around, and learn some SQL, this is a great exercise for you and you will learn a lot.

 

[Catadmin]

A few things to get you started. Books Online has some examples of "Create Procedure". Also, look into Input parameters to use for your dates. And lastly, look up Temporarily Tables (I don't know the exact syntax in BOL). These three things should get you what you need.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[e106199]

its not that i dont know anything about sql. but this is kinda new concept for me.

ok i have a temp table with all columns declared.
say the temp table has <month, c1,c2,c3,c4 >
how do i set the rows with what i get from:

select right(convert(char(11),getdate(),13),8), sum(col1),sum(col2),sum(col3),sum(col4)
group by right(convert(char(11),getdate(),13),8)

thank you
-shane

 

[jbenson001]

What do you mean by :

ok i have a temp table with all columns declared.
say the temp table has <month, c1,c2,c3,c4 >
how do i set the rows with what i get from:

....

 

[e106199]

in the stored procedure i ll create a temp table and set its columns from the select statement carlen stated, right? this is what i m asking. if i have a @tmp table with all coolumns declared, how can i set its rows from the select statement which calculates the sums and groups them.

thank you
-shane

 

[jbenson001]

try:

insert into @tmp
select right(convert(char(11),getdate(),13),8), sum(col1),sum(col2),sum(col3),sum(col4)
group by right(convert(char(11),getdate(),13),8)

 

[vongrunt]

Argh... :X

create procedure blahproc( @dateFrom datetime, @dateTo datetime )
as
set nocount on

declare @months table( monthdate datetime )
declare @d datetime

set @d = dateadd(mm, (datediff(mm, 0, @dateFrom)), 0)
while @d <= @dateTo
begin
	insert into @months values (@d)
	set @d = dateadd(mm, 1, @d)
end

select M.monthdate as month, 
	sum(convert(int, isnull(col1, 0))) as totalcol1,
	sum(convert(int, isnull(col2, 0))) as totalcol2,
	sum(convert(int, isnull(col3, 0))) as totalcol3,
	sum(convert(int, isnull(col4, 0))) as totalcol4
from @months M
	left outer join blah B on B.coldate >= M.monthdate and B.coldate < dateadd(mm, 1, m.monthdate)
group by M.monthdate

go

------------
-- exec blahproc '1/1/2005', '3/2/2005'

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[e106199]

about the select statement:
the getdate() should be my date column, right? is it a mistake or does it have a role there?
-shane

 

[jbenson001]

It should be your column, I just copied and pasted and didn't change it.. sorry

 

[e106199]

thanks so much,
i think i got it working
-shane

 

[e106199]

One more thing guys,
now i have a situation where i have to find the count of all rows which has a column like "abc".
i tried using count but how can i write a like statement in count?

it will be like:

select count (col1 like 'abc') as abcrows

i know the syntax is wrong.
any ideas?
thank you
-shane

 

[e106199]

By the way, i cant use the statement in where clause because i have several of them. i wnat to find the count of abc rows, and def rows ...
-shane