get the number of reports per day

I have a table that has a DateTime field that stores something like this "2004/01/19 16:16:15" which lets me know the date and time of the report. Now I need an sql statement that would let me know how many reports are being made per day. Can someone help me out?
This is what I've been doing(see sql), but I have to change the date and then run the query for a certain date. But I want one query that will give me the count on every date. I hope someone understands what I'm talking about. Thanks.

select * from Table where
DateTime >= '2004/03/01' and DateTime < '2004/03/01 23:59:59';Replace hard-coded date reference with a function call:

select * from Table where
DateTime >= convert(char(10), getdate(), 101)
and DateTime < dateadd(day, 1, convert(char(10), getdate(), 101))

get text after a delimiter

In a table I have a column named "Installment" which stores
installment like 9/15, 2/10 etc., where first number is the last
installment deducted and the last number is the total number of
installments. I want to pick any text after "/" using SQL Query. Which
function to use? I am using SQL Server 2005 Express.See funtions LEFT, RIGHT, CHARINDEX, PATINDEX, REPLACE, PARSENAME in BOL.
Example:
select parsename(replace('2/10', '/', '.'), 1)
go
AMB
"RP" wrote:
> In a table I have a column named "Installment" which stores
> installment like 9/15, 2/10 etc., where first number is the last
> installment deducted and the last number is the total number of
> installments. I want to pick any text after "/" using SQL Query. Which
> function to use? I am using SQL Server 2005 Express.
>

Get results from a custom formula

I have a table which stores Allowance/Deductions codes and their standard amounts. The structure looks somewhat like this:

ADCode ADAmount
-
GIS 100
DPF 150
DPF.ADV 200
HRA 100

I want to give the user a capability to create any formula using column values and there by create a new ADCode depending upon other ADCodes, example:

New_ADCode_1 = (GIS + DPF + DPF.ADV)/20 * 100

New_ADCode_2 = 3 * (HRA)

Any idea on how to substitute values in the formula of the ADCodes that are present and create a new ADCode that uses this formula every time an employees pay is calculated.

I am using SQL Server 2005 with C#.How many ADCode do you have? If not many. You can simply SELECT them out through ADO.NET, cache them through Dictionary datastructure in C#. And use them whenever you want.

Because for formula caculation, .NET is much powerful than T-SQL.

Hope it'll be helpful.

|||

Here it is,

Code Snippet

Create Table data (

[Empid] int ,

[ADCode] Varchar(100) ,

[ADAmount] int

);

Insert Into data Values('1','GIS','100');

Insert Into data Values('1','DPF','150');

Insert Into data Values('1','DPF.ADV','200');

Insert Into data Values('1','HRA','100');

Insert Into data Values('2','GIS','100');

Insert Into data Values('2','DPF','150');

Insert Into data Values('2','DPF.ADV','200');

Insert Into data Values('2','HRA','100');

Approach 1:

Code Snippet

create function formula1(@.Empid int)

returns float

as

Begin

Declare @.result as float;

Select @.result = cast(Sum(AdAmount) as float)/20.0 * 100.0

From

data

Where

Adcode in('GIS','DPF','DPF.ADV')

and Empid = @.Empid;

return @.result;

End

create function formula2(@.Empid int)

returns float

as

Begin

Declare @.result as float;

Select @.result = 3 *AdAmount

From

data

Where

Adcode = 'HRA'

and Empid = @.Empid;

return @.result;

End

select *, dbo.formula1(empid), dbo.formula2(empid) from data

Approach 2:

Code Snippet

create function FormulaResults(@.Empid int)

returns table

as

Return

(

Select formula1 = cast(Sum(case when Adcode in('GIS','DPF','DPF.ADV') Then AdAmount End) as float)/20.0 * 100.0,

formula2 = cast(Sum(case when Adcode = ('HRA') Then AdAmount End) as float) * 3

From

data

Where

Empid = @.Empid

)

select *, formula1, formula2 from data d CROSS APPLYFormulaResults(d.empid) f

|||Manivannan,

Thanks for the solution. It seems a good one.

As you might be knowing, any Payroll system has Allowances and Deductions. Some Allowances and Deductions are applied directly, i.e., either they are added to Basic Pay or subtracted from Gross Pay. Some Allowances/Deductions have a special formula.

I want to know whether all calculations must reside as stored procedures/functions or they can be written entirely in a DLL.

|||

Frankly speaking about the application design, it is not good practice to overload your database with business logics, the new programming languages are much easier to create these functional requirements and rules. It is much easier to maintain also.