Net Send notifications

Is it possible to specify multiple Net Send addresses when creating new
operator?
Is it possible to specify multiple Email addresses?
Thank you in advance for your help,
Leon Shargorodsky
Hi
Not sure about net send (in general I think this is now off by default
nowadays!)
I believe you can create an operator that has multiple email addresses or
alternatively you can specify multiple operators to notify.
You may find that a better solution (my preference!) is to create a
distribution list and create an operator for that.
John
"Leon Shargorodsky" wrote:

> Is it possible to specify multiple Net Send addresses when creating new
> operator?
> Is it possible to specify multiple Email addresses?
> Thank you in advance for your help,
> Leon Shargorodsky

Net Send notifications

Is it possible to specify multiple Net Send addresses when creating new
operator?
Is it possible to specify multiple Email addresses?
Thank you in advance for your help,
Leon ShargorodskyHi
Not sure about net send (in general I think this is now off by default
nowadays!)
I believe you can create an operator that has multiple email addresses or
alternatively you can specify multiple operators to notify.
You may find that a better solution (my preference!) is to create a
distribution list and create an operator for that.
John
"Leon Shargorodsky" wrote:
> Is it possible to specify multiple Net Send addresses when creating new
> operator?
> Is it possible to specify multiple Email addresses?
> Thank you in advance for your help,
> Leon Shargorodsky

Net Send notifications

Is it possible to specify multiple Net Send addresses when creating new
operator?
Is it possible to specify multiple Email addresses?
Thank you in advance for your help,
Leon ShargorodskyHi
Not sure about net send (in general I think this is now off by default
nowadays!)
I believe you can create an operator that has multiple email addresses or
alternatively you can specify multiple operators to notify.
You may find that a better solution (my preference!) is to create a
distribution list and create an operator for that.
John
"Leon Shargorodsky" wrote:

> Is it possible to specify multiple Net Send addresses when creating new
> operator?
> Is it possible to specify multiple Email addresses?
> Thank you in advance for your help,
> Leon Shargorodsky

nested stored procs

Hi Guys, is there a way of nesting a stored procedure within another...i dont mean calling one within another...more like

creating one within another e.g

Create proc TEST1

as

Create proc TEST2

as

select 'this is inner proc'

where by running test one creates test2

i tried this and got an error:

Msg 156, Level 15, State 1, Procedure TEST1, Line 3

Incorrect syntax near the keyword 'proc'.

is this possible if so how, cos i am trying to create a master Proc that creates a DB, and Tables and store procs, hence i want to use it like a Batch file or script.

To nest stored procedures, you must create them separately and have one proc call the other -- maybe something like this:

create procedure A

as

print 'This is procedure a.'

go

create procedure B

as

exec A

go

exec B

-- - Output: -
-- This is procedure a.

If you are trying to create a procedure that creates other procedures, you will need to take a different approach. Stored procedures cannot directly create other database objects.

|||

You cannot create a stored procedure from within a stored procedure.

You can create a T-SQL script file, and then execute that script file from the command line using OSQL.exe or SQLCommand.exe

Refer to Books Online for OSQL utility, or SQLCommand.

|||Thanks Kent yeah i do now this form of nesting but its not suitable as i am not calling a proc from a proc|||

thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

ta.

|||

One thing to remember is that when using scripts, each use of 'GO' will clear all variables. You may have to re-establish them for the next object.

Sometimes, from the stored procedure, I have loaded a [Name-Value] table with input parameter values that will be used as variables in the script file. The values can be pulled whenever needed.

|||

>> thanks Arnie, yes that was my second option to basically i wanted a stored proc to create a database i could pass parameters for the dbase name and a time stamp and then create stored procs, tables and inserts. but i guess i can do it 2 step : 1 proc i script

You would have to create the SPs in the newly created database so your SP create statements couldn't be in-line anyway.

Usually I do this via osql or just concatenated scripts.

|||

One other option would be to use dynamic sql to create the procedure. The code below in option 1 creates a permanent procedure that is then executed. The code in option 2 creates a temporary stored procedure that is then executed. Temporary procedures are nice when the task you are executing doesn't need to be permanent. HTH.

-Chris

--Option 1

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE myProc2 AS select getdate()')

exec myProc2

END;

GO

EXEC myProc1

--Option 2

create procedure myProc1 as

BEGIN

EXEC ('CREATE PROCEDURE #myProc2 AS select getdate()')

exec #myProc2

END;

GO

EXEC myProc1

|||

Why Not..! Yes you can create... But it is bit different....

You have to use the procedure numbers...

The main storedproc will be visible to world rest will be hidden.

You can call them from your main proc / externally..

Example:

Code Snippet

Create proc MyGroupedProc

(

@.Param as int

)

as

Begin

Exec MyGroupedProc;2 @.Param

End

Go

Create Proc MyGroupedProc;2

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [2]'

Select @.Param as [@. 2]

Exec MyGroupedProc;3 @.param

End

Go

Create Proc MyGroupedProc;3

(

@.Param as int

)

as

Begin

Print 'This is Inner Proc [3]'

Select @.Param as [@. 3]

End

Go

ExecMyGroupedProc 1

Select * from Sysobjects Where Name Like 'MyGroupedProc%' --It only list the Main Proc not other 2

ExecMyGroupedProc;2 1 --You can execute the hidden stored proc directly

nest subreport in a tables detail section

I'm running ssrs 2k and need to put a sub report in a table row. I ran a
simple test by creating a sub report with no data source and just has
textboxes with different colored backgrounds and named it zColors.rdl. I
dragged zColors into a table cell in the detail section. I also dragged a
valid data field into another table cell and ran the report. I can see the
data cell but zColors does not show. I even resized the table row big
enough to assure being able to see the contents of zColors.
Is this even possible in ssrs 2k? and if so, how do I do this?
Thanks.
--
moondaddy@.newsgroup.nospamHello moondaddy,
It is possible.
Instead of increase the main report table cell size, you could reduce the
subreport body size to only include a textbox.
This will be OK.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks its working now.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:OWtVsdm%23HHA.5604@.TK2MSFTNGHUB02.phx.gbl...
> Hello moondaddy,
> It is possible.
> Instead of increase the main report table cell size, you could reduce the
> subreport body size to only include a textbox.
> This will be OK.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Moondaddy,
My pleasure. If you have any questions, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Needs Help With Creating a New Stored Procedure

I already know how you create a stored procedure to add information to a database or retrieve a value for one record. But I don't know how to create a stored procedure that will retrieve many records for a certain querystring value.

Here's my simple stored procedure to show one record:

CREATE PROCEDURE DisplayCity
(
@.CityID int
)
AS

SELECT City From City where CityID = @.CityID
GO

My code for displaying the City:

Sub ShowCity()

Dim strConnect As String

Dim objConnect As SqlConnection

Dim objCommand As New SqlCommand

Dim strCityID As String

Dim City As String

'Get connection string from Web.Config

strConnect = ConfigurationSettings.AppSettings("ConnectionString")

objConnect = New SqlConnection(strConnect)

objConnect.Open()

'Get incoming City ID

strCityID = request.params("CityID")

objCommand.Connection = objConnect

objCommand.CommandType = CommandType.StoredProcedure

objCommand.CommandText = "DisplayCity"

objCommand.Parameters.Add("@.CityID", CInt(strCityID))

'Display SubCategory

City = "" & objcommand.ExecuteScalar().ToString()

lblCity.Text = City

lblChosenCity.Text = City

objConnect.Close()

End Sub

Here's the code I'd like to get help with changing into a stored procedure:

Sub BindDataList()

Dim strConnect As String

Dim objConnect As New System.Data.SqlClient.SQLConnection

Dim objCommand As New System.Data.SqlClient.SQLCommand

Dim strSQL As String

Dim dtaAdvertiser As New System.Data.SqlClient.SQLDataAdapter()

Dim dtsAdvertiser As New DataSet()

Dim strCatID As String

Dim strCityID As String

Dim SubCategory As String

Dim SubCategoryID As String

Dim BusinessName As String

Dim City As String

'Get connection string from Web.Config

strConnect = ConfigurationSettings.AppSettings("ConnectionString")

objConnect = New System.Data.SqlClient.SQLConnection(strConnect)

objConnect.Open()

'Get incoming querystring values

strCatID = request.params("CatID")

strCityID = request.params("CityID")

'Start SQL statement

strSQL = "select * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City"

strSQL = strSQL & " where Advertiser.CategoryID=Categories.CategoryID"

strSQL = strSQL & " and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID"

strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID"

strSQL = strSQL & " and Advertiser.CountyID=County.CountyID"

strSQL = strSQL & " and Advertiser.CityID=City.CityID"

strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID = '" & strCatID & "'"

strSQL = strSQL & " and Advertiser.CityID = '" & strCityID & "'"

strSQL = strSQL & " and Approve=1"

strSQL = strSQL & " Order By ListingType, BusinessName,City"

'Set the Command Object properties

objCommand.Connection = objConnect

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

'Create a new DataAdapter object

dtaAdvertiser.SelectCommand = objCommand

'Get the data from the database and

'put it into a DataTable object named dttAdvertiser in the DataSet object

dtaAdvertiser.Fill(dtsAdvertiser, "dttAdvertiser")

'If no records were found in the category,

'display that message and don't bind the DataGrid

if dtsAdvertiser.Tables("dttAdvertiser").Rows.Count = 0 then

lblNoItemsFound.Visible = True

lblNoItemsFound.Text = "Sorry, no listings were found!"

else

'Set the DataSource property of the DataGrid

dtlAdvertiser.DataSource = dtsAdvertiser

'Set module level variable for page title display

BusinessName = dtsAdvertiser.Tables(0).Rows(0).Item("BusinessName")

SubCategory = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategory")

SubCategoryID = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategoryID")

City = dtsAdvertiser.Tables(0).Rows(0).Item("City")

'Bind all the controls on the page

dtlAdvertiser.DataBind()

end if

objCommand.ExecuteNonQuery()

'this is the way to close commands

objCommand.Connection.Close()

objConnect.Close()

End Sub

It's really no different. If I understand what you mean then you'd want the following stored procedure:

CREATE PROCEDURE DisplayCity
(
@.CatID Int,
@.CityID Int
)
AS

SELECT * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City
where Advertiser.CategoryID=Categories.CategoryID
and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID
and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID
and Advertiser.CountyID=County.CountyID
and Advertiser.CityID=City.CityID
and AdvertiserSubCategory.SubCategoryID = @.CatID
and Advertiser.CityID = @.CityID
and Approve=1
Order By ListingType, BusinessName,City

GO|||I actually need help with my Code for displaying the stored procedure and records. I only know how to retrieve one record using a stored procedure.|||Your original question was misleading, then :)

I'd recommend readingthese Microsoft tutorials on performing data access. There are many ways to loop through records and display data so find the one most suitable for you.|||

You would need to do a while statement. If you are using SQL Server then you can do your SQL DataReader. Just do a while loop and tell your code to keep reading until end of records and for each row add it to an array. Then you can bind thay array to a datagrid.

needful parts of MSSQL to create local cube

hi,
I'm creating local cube with Delphi. On my server with MSSQL it work well, but i need to know, which parts of MSSQL is needful to create this local cube (on the server) if i will creat and instal new server with MS SQL.
Thanks for reply.I think you need analysis services, which come with enterprise edition of SQL Server|||Originally posted by dbabren
I think you need analysis services, which come with enterprise edition of SQL Server
I have instaled AS and it works, but then I uninstall AS and it allredy works too.
I don't know whitch libraries wasn't uninstall.
Somewhere i read something about pivot table?? what is it and how to install...
thanks|||I've only ever seen pivot tables used in excel, but there may be other instances of it I'm not aware of.

Haven't actually ever set one up, but I can't imagine they are that complex to do.|||its in SQL Server as well!! - just looked it up in BOL - have a read of that - no need to install as its a feature|||Originally posted by dbabren
its in SQL Server as well!! - just looked it up in BOL - have a read of that - no need to install as its a feature
Thank you.
Can you tell me a title in BOL where it is writen pleas?|||type in "pivot table" in the keyword searh bit.|||Originally posted by dbabren
type in "pivot table" in the keyword searh bit.
If I understand it well, only install MS SQL without AS will work OK...|||if that does the job for you mate, then the answer is yes. Its a standard feature of SQL Server I believe.|||Originally posted by dbabren
if that does the job for you mate, then the answer is yes. Its a standard feature of SQL Server I believe.
Special thanks to you!|||Originally posted by dbabren
if that does the job for you mate, then the answer is yes. Its a standard feature of SQL Server I believe.
But i have once more problem...
If SQL server is on different station than application server, and application server obtain query to SQL I need some DLLs isnt it?
And what DLLs?|||I find it "PivotTable Service, DLLs" BOL|||Unfortunately, I don't know any more than it states in BOL on this topic, although from a brief reading of the article it appears that you need to install some dll files on you app server - perhaps there is someone in this forum who has made use of this pivot table service?

need urgent help

Hi,

I am creating a attendance sheet software for inhouse use.

my data is like this:-

-----------------------------
| name | login time | logout
time |
-----------------------------
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
-----------------------------
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
-----------------------------

My requirement:-

I want to generate an hourly report like this:-
----------------------------
date time range total people logged
in
----------------------------
2007-11-10 0 -2 0
----------------------------
2007-12-10 2-4 0
----------------------------
..
..
----------------------------
2007-11-10 12-14 1
----------------------------
2007-11-10 14-16 2
----------------------------
2007-11-10 16-18 1
-----------------------------
..
..
----------------------------
2007-11-10 22-24 0
----------------------------

This is what I want to creat , but I don't know how can I generate
such kind of report.

Can you please guide me for the same. Please reply urgently.

Thanks & Regards,
BhishmHi Bhishm,

I'm afraid you will need to supply a lot more information than this.
What table(s) exist for storing these details? What technology are you
using to design the report? What does the "Time Range" value in the
report represent (looks like hours?) Do you simply need a SQL
Statement to prepare data in the "report" format you specified?

If I simply assume that everything un-said is as I imagine it, I guess
the solution might be something like:
/* Initialise data table */
CREATE TABLE tblLog (LogName nvarchar(50), LogInTime datetime,
LogOutTime datetime)

INSERT INTO tblLog (LogName, LogInTime, LogOutTime)
SELECT 'personA', '2007-11-10T12:00:00', '2007-11-10T16:00:00'
UNION ALL
SELECT 'personB', '2007-11-10T15:00:00', '2007-11-10T18:00:00'
UNION ALL
SELECT 'personC', '2007-11-10T11:00:00', '2007-11-10T14:00:00'

/* Create supporting table */
CREATE TABLE HrInDay (HrMin INT, HrMax INT, TimeRange VARCHAR(10))

DECLARE @.i INT, @.Date VARCHAR(8)

SET @.i = 0
SET @.Date = '20071110' -- Date parameter for "Report"

WHILE @.i < 24
BEGIN
INSERT INTO HrInDay (HrMin, HrMax, TimeRange)
VALUES (@.i, @.i + 2, CAST(@.i AS VARCHAR) + ' - ' + CAST(@.i + 2 AS
VARCHAR))
SET @.i = @.i + 2
END

/* Select from a derived table so it's sorted - there is probably a
better way to do this but I'm too lazy to find it :) */
SELECT LogDate, TimeRange, NoPplLogged
FROM (
SELECT @.Date AS LogDate,
MAX(TimeRange) AS TimeRange,
HrMin,
COUNT(DISTINCT LogName) AS NoPplLogged
FROM tblLog
RIGHT JOIN HrInDay
ON DATEPART(hh, LogInTime) < HrMax
AND DATEPART(hh, LogOutTime) HrMin
AND CONVERT(VARCHAR(8), LogInTime, 112) = @.Date
GROUP BY HrMin
) AS Report

DROP TABLE HrInDay
DROP TABLE tblLog

Good luck!
J|||Thanks a lot :) JhofM for showing the way.

I got a lot of help from it in solving it.

Now I am looking into possilities and will let you know my results.

Thanks a lot again, it's of great help

need urgent help

Hi,
I am creating a attendance sheet software for inhouse use.
my data is like this:-
-----
| name | login time | logout
time |
-----
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
-----
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
-----
My requirement:-
I want to generate an hourly report like this:-
----
date time range total people logged
in
-----
2007-11-10 0 -2 0
----
2007-12-10 2-4 0
----
.
.
-----
2007-11-10 12-14 1
-----
2007-11-10 14-16 2
----
2007-11-10 16-18 1
-----
.
.
-----
2007-11-10 22-24 0
----
This is what I want to creat , but I don't know how can I generate
such kind of report.
Can you please guide me for the same. Please reply urgently.
Thanks & Regards,
Bhishm> This is what I want to creat , but I don't know how can I generate
> such kind of report.
An idea is to create a temp table, and filling it op with one row at a time.
The rows can be foud by declaring a datetime variable, and assigning it the
lowest datetime you need to have in your report.
Then you kan do something like this:
create table #temptable (...)
declare startdate datetime
set @.startdate '2007-11-20 00:00:00.000'
while @.startdate < getdate()
begin
insert into #temptable
select @.startdate as time, count(*)
from loggingtable
where login_time between @.startdate and dateadd(hh, 2, @.startdate)
group by login_time
set @.startdate = dateadd(hh,2,@.startdate)
end
The above is not tested, but my idea should shine through, so you can
continue your own work.
/Sjang|||Brishim
Untested
create table #t (name char(1),login datetime,logout datetime)
insert into #t values ('a', '2007-11-10 12:00:00','2007-11-10 16:00:00')
insert into #t values ('b', '2007-11-10 15:00:00','2007-11-10 18:00:00')
select '12-14' [time range] ,
count(case when convert(char(2),login,108) >= 12 and
convert(char(2),login,108)< 15
or convert(char(2),logout,108) >= 12 and convert(char(2),logout,108)<
15
then 1 end) [total people logged] from #t
union all
select '14-16' [time range],
count(case when convert(char(2),login,108) >= 14 and
convert(char(2),login,108)< 17
or convert(char(2),logout,108) >= 14 and convert(char(2),logout,108)<
17
then 1 end)
from #t
"Bhishm" <bhishms@.gmail.com> wrote in message
news:f74d745f-661a-4a57-8b18-a685b8658c43@.i37g2000hsd.googlegroups.com...
> Hi,
> I am creating a attendance sheet software for inhouse use.
> my data is like this:-
> -----
> | name | login time | logout
> time |
> -----
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> -----
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> -----
> My requirement:-
> I want to generate an hourly report like this:-
> ----
> date time range total people logged
> in
> -----
> 2007-11-10 0 -2 0
> ----
> 2007-12-10 2-4 0
> ----
> .
> .
> -----
> 2007-11-10 12-14 1
> -----
> 2007-11-10 14-16 2
> ----
> 2007-11-10 16-18 1
> -----
> .
> .
> -----
> 2007-11-10 22-24 0
> ----
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
> Can you please guide me for the same. Please reply urgently.
> Thanks & Regards,
> Bhishm

need urgent help

Hi,
I am creating a attendance sheet software for inhouse use.
my data is like this:-
----
--
| name | login time | logout
time |
----
--
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
----
--
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
----
--
My requirement:-
I want to generate an hourly report like this:-
----
--
date time range total people logged
in
----
--
2007-11-10 0 -2 0
----
--
2007-12-10 2-4 0
----
--
.
.
----
--
2007-11-10 12-14 1
----
--
2007-11-10 14-16 2
----
--
2007-11-10 16-18 1
----
--
.
.
----
--
2007-11-10 22-24 0
----
--
This is what I want to creat , but I don't know how can I generate
such kind of report.
Can you please guide me for the same. Please reply urgently.
Thanks & Regards,
Bhishm> This is what I want to creat , but I don't know how can I generate
> such kind of report.
An idea is to create a temp table, and filling it op with one row at a time.
The rows can be foud by declaring a datetime variable, and assigning it the
lowest datetime you need to have in your report.
Then you kan do something like this:
create table #temptable (...)
declare startdate datetime
set @.startdate '2007-11-20 00:00:00.000'
while @.startdate < getdate()
begin
insert into #temptable
select @.startdate as time, count(*)
from loggingtable
where login_time between @.startdate and dateadd(hh, 2, @.startdate)
group by login_time
set @.startdate = dateadd(hh,2,@.startdate)
end
The above is not tested, but my idea should shine through, so you can
continue your own work.
/Sjang|||Brishim
Untested
create table #t (name char(1),login datetime,logout datetime)
insert into #t values ('a', '2007-11-10 12:00:00','2007-11-10 16:00:00')
insert into #t values ('b', '2007-11-10 15:00:00','2007-11-10 18:00:00')
select '12-14' [time range] ,
count(case when convert(char(2),login,108) >= 12 and
convert(char(2),login,108)< 15
or convert(char(2),logout,108) >= 12 and convert(char(2),logout,108)<
15
then 1 end) [total people logged] from #t
union all
select '14-16' [time range],
count(case when convert(char(2),login,108) >= 14 and
convert(char(2),login,108)< 17
or convert(char(2),logout,108) >= 14 and convert(char(2),logout,108)<
17
then 1 end)
from #t
"Bhishm" <bhishms@.gmail.com> wrote in message
news:f74d745f-661a-4a57-8b18-a685b8658c43@.i37g2000hsd.googlegroups.com...
> Hi,
> I am creating a attendance sheet software for inhouse use.
> my data is like this:-
> ----
--
> | name | login time | logout
> time |
> ----
--
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> ----
--
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> ----
--
> My requirement:-
> I want to generate an hourly report like this:-
> ----
--
> date time range total people logged
> in
> ----
--
> 2007-11-10 0 -2 0
> ----
--
> 2007-12-10 2-4 0
> ----
--
> .
> .
> ----
--
> 2007-11-10 12-14 1
> ----
--
> 2007-11-10 14-16 2
> ----
--
> 2007-11-10 16-18 1
> ----
--
> .
> .
> ----
--
> 2007-11-10 22-24 0
> ----
--
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
> Can you please guide me for the same. Please reply urgently.
> Thanks & Regards,
> Bhishm

need urgent help

Hi,
I am creating a attendance sheet software for inhouse use.
my data is like this:-
-----
| name | login time | logout
time |
-----
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
-----
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
-----
My requirement:-
I want to generate an hourly report like this:-
date time range total people logged
in
-----
2007-11-10 0 -2 0
2007-12-10 2-4 0
..
..
-----
2007-11-10 12-14 1
-----
2007-11-10 14-16 2
2007-11-10 16-18 1
-----
..
..
-----
2007-11-10 22-24 0
This is what I want to creat , but I don't know how can I generate
such kind of report.
Can you please guide me for the same. Please reply urgently.
Thanks & Regards,
Bhishm
Brishim
Untested
create table #t (name char(1),login datetime,logout datetime)
insert into #t values ('a', '2007-11-10 12:00:00','2007-11-10 16:00:00')
insert into #t values ('b', '2007-11-10 15:00:00','2007-11-10 18:00:00')
select '12-14' [time range] ,
count(case when convert(char(2),login,108) >= 12 and
convert(char(2),login,108)< 15
or convert(char(2),logout,108) >= 12 and convert(char(2),logout,108)<
15
then 1 end) [total people logged] from #t
union all
select '14-16' [time range],
count(case when convert(char(2),login,108) >= 14 and
convert(char(2),login,108)< 17
or convert(char(2),logout,108) >= 14 and convert(char(2),logout,108)<
17
then 1 end)
from #t
"Bhishm" <bhishms@.gmail.com> wrote in message
news:f74d745f-661a-4a57-8b18-a685b8658c43@.i37g2000hsd.googlegroups.com...
> Hi,
> I am creating a attendance sheet software for inhouse use.
> my data is like this:-
> -----
> | name | login time | logout
> time |
> -----
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> -----
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> -----
> My requirement:-
> I want to generate an hourly report like this:-
> ----
> date time range total people logged
> in
> -----
> 2007-11-10 0 -2 0
> ----
> 2007-12-10 2-4 0
> ----
> .
> .
> -----
> 2007-11-10 12-14 1
> -----
> 2007-11-10 14-16 2
> ----
> 2007-11-10 16-18 1
> -----
> .
> .
> -----
> 2007-11-10 22-24 0
> ----
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
> Can you please guide me for the same. Please reply urgently.
> Thanks & Regards,
> Bhishm