Monthly Attendance Report Generation

Jan 4, 2009

I have created a database table in MSSQL 2000 like this

[empcode] [leave_date] [type] [reason]
100 2008-12-29 00:00:00.000 T Tour
100 2008-12-30 00:00:00.000 T Tour
101 2008-12-31 00:00:00.000 CL Casual Leave
102 2009-01-01 00:00:00.000 R Restricted holiday
100 2009-01-02 00:00:00.000 T Tour

This table contains only leave details.... but i need to create monthly attendance report such as below

empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 .............
100 P P P P S P T CL P P P S P P T ............
101 P T R R S R R T CL P P S P P P..............
102 P P P P S P P P P T T S CL P P P............

P-present
T-Tour
CL- causal leave
R- Restricted holiday
S-sunday

is there any way in SQL query to get the report like that.....

View 20 Replies

How To Display Data At Bottom Of Report By Daily, Weekly, Monthly, SYTD Using Ssrs Report

Dec 14, 2007

hai iam new to ssrs, please help me.

i have student billbale information assume what ever data it. i need to to dispaly total amount for the student at

Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.

thanks to advanced

Jacks v

View 1 Replies View Related

Monthly Report

Apr 15, 2008

Hi,

I m Maran. I am trying to write a SQL Query to retrieve the following report format. But I'm not sure how to go about it.

Input values:

Starting Date: 09/14/2007
End Date: 12/06/2007

Monthly Report :

Start Date - End Date - Number of companies
09/14/2007 - 09/30/2007 1
10/01/2007 - 10/31/2007 0
11/01/2007 - 11/30/2007 4
12/01/2007 - 12/06/2007 0

Please its very urgent, Plz do the needful help. Actually this same report format i was posted already and got some methodology, but its not satisfied my requirements :( :(

Used Table: CompanyHistorytrackTable

companyId changed_date
50198 2007-09-05 13:11:17.000
48942 2007-09-14 12:42:30.000
48945 2007-11-06 12:05:31.000
47876 2007-11-14 10:58:21.000
43278 2007-11-16 16:14:25.000
43273 2007-11-16 16:16:11.000
51695 2008-02-04 11:05:09.000
47876 2008-01-21 14:10:02.000
44604 2008-02-04 19:33:02.000
46648 2008-02-04 19:35:30.000

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

View 3 Replies View Related

Reg: Weekly - Monthly Report

Apr 5, 2008

Hi All,

I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.

Input values:

Start Date: 2/17/2008
End Date : 5/8/2008

Output Format:

2/17/08 - 2/29/08 (Partial Month) 12
3/1/08 - 3/31/08 (Full month) 0
4/1/08 - 4/30/08 (Full month) 22
5/1/08 - 5/8/08 (Full month) 10

I want the above format of the monthly report. I really could use some help on this. thanks.

~ Maran

Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028

View 3 Replies View Related

Display Weekly , Monthly Report

Sep 27, 2006

hello friends!

I want to display the reports in weekly format suppose

today is sept 27 2006, so i know from datepart(weekday,..) its value is 4 and end of this week is sept 30 2006 and again next week will start like that....also search should be monthly...

my report looks like
Weekly Report (09/27 - 10/12)
Week====09/27-09/30======10/01-10/07======10/08-10/12
Sales======50===============100===============80

like that my output looks like

T.I.A

View 2 Replies View Related

Monthly Based Report Parameter

Oct 24, 2007

Hi guys,

I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months?
In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.

Thanks in advance for any suggestion.
Claudio

View 2 Replies View Related

SQL Report Generation

Jul 20, 2005

HelloFor my client, I need to generate reports from the information storedin the database. The client has fixed format forms (on paper e.g. USCustoms forms etc).Will I need to redesign the forms in the application and then show theinformation?Another approach is to scan the forms as image and print theinformation on top of that image, so when it is printed , theinformation will be displayed at the right places.Is there any other way? How is the reporting done if the forms arepre-defined and the information is stored in a databaseThanks for your input

View 1 Replies View Related

Writing A Monthly Report With Stored Procedure

Aug 13, 2007

Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
            1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
            2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
            3. There are around 500 employees data im dealing with
            4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
            5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
            6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
            7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...

View 7 Replies View Related

Getting Daily Average Of Sales From Monthly Report?

Oct 9, 2014

I have this small project, I have this report that have the total of order along with the date of the order

SELECT sf.ORDER_QNT, dd.ACTUAL_DATE, dd.MONTH_NUMBER
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;

ORDER_QNT ACTUAL_DATE MONTH_NUMBER
1100 05/01/13 1
100 05/01/13 1
140 06/01/13 1
110 07/01/13 1
200 08/01/13 1
500 08/01/13 1
230 08/01/13 1
500 08/01/13 1
200 08/01/13 1
53 15/01/13 1
53 22/01/13 1

Now, I want to get the average for that month (average per day).

SELECT sum(sf.ORDER_QNT)/31 as AVGPERDAY
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;

AVGPERDAY MONTH_NUMBER
---------- ------------
113.785714 1

but instead putting 31, I'd like to pull the totaldays from the actual_date using the Extract function so I try this

SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(to_date('05/01/13','dd/mm/rr'))) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;

AVGPERDAY MONTH_NUMBER
---------- ------------
113.785714 1

The result is nice, but now when I change the date with the dd.actual_date it gives error

SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;
Error at Command Line : 1 Column : 53

Error report -
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"

View 1 Replies View Related

Cancelling A Report Generation

Mar 13, 2008

Hello all,
Say I have a report that is running for a long time and I want to cancel it.
Is there a way to cancel the report generation once we submit the Report Generation Button? If I close my Browser, Am I closing my session in the server side? If server is still churning out the old report and I open another browser to request the same report, I might easily crash the server if the requests for the same report keep growing?

How do I handle or how does SSRs handle this?

Thanks in advance to you all experts..

Phewa Taal

View 9 Replies View Related

Report Generation Image

Dec 8, 2005

Is there any way to change the image "Report is being generated" to something else or at least change the location of it. I have a report that is very long and our users have to scroll down to see that. They think the report is frozen when in all reality it is still generating. It is postioning in the center of the page and I want it to position at the top.

View 6 Replies View Related

Slow Report Generation

Dec 14, 2005

I have developed several reports with selectable parameters. When the report is first requested three stored procedures are triggered and return the parameters (+2 min), following parameters being returned the default report is returned (+3 min), this time is unacceptable. Is there anything I can do to speed up the report generation? Any help here is greatly appreciated.

View 5 Replies View Related

Report Generation And Notification

Nov 9, 2007

Hi,
We need to generate the reports in a file share location and notify the Users about the location. Since the subscriptions support either File share or Email notification, we have configured file share mode in our subscriptions.
We tried writing a custom C# component to send emails to the Users but getting the status of reports generation is difficult.
Is there a way to notify the users after generating the reports in a file share?
Subash

View 6 Replies View Related

2nd Question - Date Parameters - To Run A Monthly Report Automatically

Nov 26, 2007

hi there

I am using SQL Server 2005 with Reporting Services (Using the Visual side - not direct code)

I am having problems understanding the dates. eg where to put them,

I want a report that runs on the 1st day of the month for the previous month. I know you can set up something in subscriptions but then how do I get my report header to say from .......to...............

I have been through the AW reports but can't see what I need.

Happy if someone wants to direct me to somewhere that has date examples.

cheers
Dianne

View 5 Replies View Related

Daily Report Generating Monthly Rollup Stats

Jan 2, 2007

Daily report generating Monthly rollup stats

I have a daily report which each morning generates monthly information for the current month which was implemented in December. Everything was working correctly untill January 1st. On the 1st the report generated blank since it was suppose to generate 1-31 Dec but but the currently month was Jan, so it failed. How do I program it so if it is the 1st of a month generates the previous month but still would generate current month but while in the current month? Any help is appreciated.

SELECT GETDATE() - 1 AS rptdate, Errors.WTG_ID, lookup.Phase, Errors.STATUS_TYPE, Errors.STATUS_CODE, STATUS_CODES.STATUS_DEF, Errors.TIME_STAMP,
Errors.ANSI_TIME, lookup.WTG_TYPE, Errors.POSITION
FROM Errors INNER JOIN lookup ON Errors.WTG_ID = lookup.WTG_id RIGHT OUTER JOIN STATUS_CODES ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE
WHERE (STATUS_CODES.STATUS_DEF IS NOT NULL) AND (Errors.TIME_STAMP BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
ORDER BY Errors.WTG_ID, Errors.TIME_STAMP, position

View 5 Replies View Related

Probs With T-SQL Script For Report Generation

Jun 30, 2006

Hi All,

It would be great if anybody could help me how to write a T-SQL script to generate a report in spreadSheet from a table in SQLserver 2000 database.

And this report should be generated as per the following timings(Sheduled timings only).<script></script>

As well it should be able to send a mail regarding the availability of the report to user groups.

Incase if the generation of report fails then we need to send a mail reg the failure.

Any HELP is greatly appreciated!!!

Thanks in Advance!!!

Regards,

Sashi

View 1 Replies View Related

Report Generation In Sql Server 2000

Aug 20, 2007

Just by using SQL Server 2000, what's the best way to run a stored procedure outputing the results to an excel sheet? I don't have Crystal Reports or any other 3rd party reporting tool to help me, a restriction of our client.

Found this http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1057989,00.html sp_write2Excel. Would this be the best way? Any other methods? The SQL server does not have MS Office so I think I need to ouput to a csv file.

View 2 Replies View Related

Report Server Model Generation

Jan 28, 2008

I'm attempting to generate a model for our manufacturing database in Report Server. I can create the Data Source, but when I attempt to generate the model, I get several errors in the following format ...

More than one item in the Entity 'CUST ADDRESS' has the name 'COUNTRY'. Item names must be unique among immediate siblings. (DuplicateItemName) Get Online Help

More than one Field in the Entity 'CUST ADDRESS' has the name 'COUNTRY'. Field names must be unique within an Entity. (DuplicateFieldName) Get Online Help

There are several pairs of similar error messages (DuplicateItemName and DuplicateFieldName) listed.

The tables (i.e. CUST_ADDRESS) do contain the column name (i.e. COUNTRY) but are otherwise unremarkable.

Clicking the "Get Online Help" link leads to a page with an apology and no useful information.

The database is at Compatibility Level 80, but I am able to generate models for other databases at that level.
I've run DBCC CHECKDB against the database and it returns without errors.

This is on SQL Server 2005, Standard Edition x64 with SP2.

I'm at a loss for what to do next, there doesn't seem to be any documentation for this error available.

Thanks for any insight into this matter.

------------------------------------------------------------------------------------------

After some additional research, I've discovered that in the problem entities, i.e. "CUST ADDRESS", there is a column named "COUNTRY" and a separate column named "COUNTRY_ID". I tried renaming the COUNTRY_ID column in a test copy of the database and was then able to generate the data model in Reporting Services. The problem appears to be that the model generator is unable to differentiate between a column name "COUNTRY" with one named "COUNTRY_ID".

However, I cannot do this to my production database. Is there any fixes or workarounds in Reporting Services to handle this type of situation?

View 1 Replies View Related

User Input During Report Generation.

Nov 30, 2007

Hello,

I'm trying to do something simple that has turned out to be a frustrating problem. I have a field in my report that needs to be populated by user input. Based on this user input, I will use the field in a calculation. The simple solution to me, was to create a custom assembly that has a function that uses Console.Write("Input Data") and Console.readline() to assign the input to a string, and then return that string to the report. This works fine in a test console application, but when I copy and paste the code to my custom assembly, it returns #Error to the report. I've debugged, and found that when I hit F10 on Console.Write("test"); it skips right over it, and nothing happens. It also skips over Console.readline(); with nothing happening. This makes me unable to take the user input and set the textbox equal to that value.

Am I doing this completely wrong? Is there something I'm missing, or just not understanding correcly? It seems like reporting services has to have this option. I'd really appreciate some advice. Thanks!

-Paul

View 3 Replies View Related

Provide Monthly Report To Shows How Successful Deployment Was For Particular Patchgroup

Sep 1, 2015

I need to calculate the success rate of our OS Patch deployments. the data from system is stored in SQL with corresponding states (installed, missing, ...)

I would need to provide monthly report that shows how successful the deployment was for particular patchgroup. I have the following 2 dummy tables (just used as example)

table5 ==> Table containing patch groups + patches
table6 ==> Table with machines names, patches and patch state

select * from table5
pgrouppatch
GROUP1PATCH1
GROUP1PATCH2
GROUP1PATCH3
GROUP1PATCH4

[Code] ....

Result would be
pgroup install missing
group1 80% 20%
group2 50% 50%
group3 55% 45%

Ideally I would like to do this in T-SQL but if necessary can also do this in .NET Function. Only mention this but both Patchgroup and machines are dynamic each month can be different.

below are the sql scripts to create tables and populate with data

CREATE TABLE [dbo].[table5](
[pgroup] [varchar](128) NOT NULL,
[patch] [nvarchar](128) NOT NULL
)
CREATE TABLE [dbo].[table6](
[machinename] [varchar](128) NOT NULL,

[Code] ...

View 9 Replies View Related

SQL Server 2012 :: Using WHERE Clause For Report Generation

Sep 11, 2015

Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.

The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.

I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied.

The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask).
SELECT *
FROM dbo.v_InspectionDetailsReports
WHERE ( RefOnly = 0
OR RefOnly IS NULL

[code]...

View 5 Replies View Related

Report Generation Is Consuming 100% Of CPU Client Side.

Jan 8, 2007

Hi,

I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.

Do you have any idea why ?

Sébastien.

View 3 Replies View Related

Report Generation Time Between Users And Admin

Aug 9, 2007

Hi,

When we generate a report with an account that is in the admin group it takes 2-3 seconds but when we do it with another user it takes over 2 minutes... any reason for this?
(The "Report is being generated" thing, by the way)

Not sure if my question is clear, I've been looking and searching for the past 2 hours but can't find anything remotly close to that problem... any help would be appreaciated!

Thanks

View 1 Replies View Related

SQL 2012 :: Multiple User Report Generation And Email?

Oct 13, 2014

Lets say for example I have a table named Drier_Lot_Recipients with columns grower_id int, crop_year int, and email_address varchar(100). This table contains users that would like to receive an SSRS report I created on daily basis.

I created the SSRS report and it is deployed on a reporting services server. The name of the report is Drier_Lot_Report.rdl.

I am not sure what would be the best way to go about this. Should I do it all in SSIS or a stored procedure in SQL Server?OR maybe a combination of both.

Do I need to have calls made to the RS.EXE utility? Do I need to setup database mail in SQL Server?We have two SMTP servers.

So the end solution must call the Drier_Lot_Report and pass in two parameters (Grower_id and Crop_Year). The output must be PDF and either have the grower_id included in the output filename OR generic filename

View 1 Replies View Related

Report Generation Using A Single Dataset, Which Combines Two Tables.

Jan 18, 2008

Hi,

I have an issue in generating the report in sql reporting services. I need to display a report in a table format. The datas of the table should be from two different sql tables. I have tried to write a stored procedure that returns two result sets from two different tables. As reporting services takes datas only from the first result set, i tried to write two different stored procedures each displays one result set. Then i have created two datasets with that two different stored procedure. Even then i cannot proceed as i was not able to use two different dataset in a single table because i was setting the datasetname to one dataset, when i try to retrieve the fields from another dataset i was able to retrieve only first and count values. Then i tried using sub reports. As sub reports for a dataregion(table) repeats for every row of the main report i was not able to fetch the correct datas. Atlast i have tried combining the query using join and wrote a single stored procedure. This stored procedure returns a single result set retrieves data from two tables satisfying the conditions. The issue i am facing with this is, the first table has only one row satisfying the condition and the second table has three rows satisfying the same condition, as i am using join query for the three rows returned by the second table the first table datas are getting duplicated for the rest of the rows in the second table. As I found using join query is the only resolution for the output which I need, and also I have to avoid the duplication of the records. Hence let me know for any solutions.

I have shown the sample datas that is duplicating which is indicated as bold. Phonenumber, Attemptdate and calloutcome are from first table and start time and endtime is from second table. As there are three different datas for the second table, first table datas are duplicated

Phone Number

Attempt Date

Call OutCome

Start Time

End Time

843-577-0800

2007-09-10 15:20:00.000

Contact with Prospect - Expressed Interest

15:08

15:12

843-577-0800

2007-09-10 15:20:00.000

Contact with Prospect - Expressed Interest

15:25

15:25

843-577-0800

2007-09-10 15:20:00.000

Contact with Prospect - Expressed Interest

15:26

15:27

Thanks for any help in advance

Thanks,
Sangeethaa

View 3 Replies View Related

Select Daily, Monthly, Weekly, Quarterly And Yearly Values For Graph Report

May 28, 2008

Hi

I am very new to analysis services and using MDX.

I want to select data from a cube using an MDX statement and show the data on a graph report.

I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.

Then set the 'Date' Column to the x-axis and the Value column to the y-axis.

The user also must have the option to not show certain periods (Switch of daily and weekly)

My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.

----
Code

SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,

NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,

[Measures].[ValueAfterLogic],DESC)

}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS

FROM [Workplace])

WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The case statement will take paramter values when finished

----------------end of code portion

Is this possible or is it suppose to 'split' the columns when moving to SSRS.

Thans in advance

Dev environment - SQL 2008 Feb CTP, VS 2008

View 5 Replies View Related

Report Builder Model Generation - How To Create A Key Attribute Along With The Role?

Mar 28, 2007

Hi,

I have found that in the autogenerated model attributes are missing for those fields that have relations to other tables. At first, it may look reasonable since a user can still get down to the field's value through the relation/related table. However, if the relation's key fields is the only thing the user wants to display, then going down to the related table is an overkill.

I can add an attribute manually and bind it to the key field(s). Is there an option in the autogeneration process to do it automatically? The only post I've found so far suggests to do everything manually (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1152575&SiteID=1). Is this the only way?

Thank you,

Leonid

View 3 Replies View Related

Reporting Services :: Daily / Weekly / Monthly And Yearly Parameter For Scheduled SSRS Report

Jan 7, 2011

Currently, I have a report that takes two parameters: StartDate and EndDate.

I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well with StartDate and EndDate because the parameter is static. What is the most elegant way to implement this change?

View 5 Replies View Related

Attendance System

Dec 13, 2007

Hi everyone,
I am working on a web based tool to handle attendance and leaves.
Our working hours start at 9:00 but managers will be allowed to set different starting hours for some of their staff.
I will eventually need to view the employee attendance record over a period of say a month.

My question is about storing the modified working hours
I can either:
1) record the (employee name, starting date of modified time, end date of modified time, new start time) as one record and use the f_table_date function

OR

2) Generate the days between the start and end in the application front end and store values for each day in the modified working table.

I feel option 2 will be easier to calculate the reports and prevent managers from inserting modifed times for the same employees in overlapping periods but also think it's a bit not effecient to generate day values when the start and end date would be represent the same information.

Any guidance or am I not clear?
Thanks

View 1 Replies View Related

Storing Attendance - Design Question

Mar 16, 2006

I'm new to database design and i need som advice concerning the design of my tables.
What I want to accomplish is this:
A attendace tracking system that allows tracking of meeting attendance. The project manager is setting up a meeting and after the meeting was held he/she reports who in the project team was at the meeting. It should then be possible to track the meeting attendance of the different team members.
I have a table containing the data for the team members and i thought of adding an "attended meetings" field but the field is actually a number of fileds depending on how many meetings the person has attended so far. How do I accomplish the described functionality?
Can someone pleas give me some advice
Thanks

View 4 Replies View Related

SELECT Statement With An Attendance Table

Feb 4, 2005

I have a table that contains the following structure and information:

Code:

UID Member_Number Time_Stamp Status Reason Event Reg F_Name L_Name
18772054062/3/2005 11:48:27 AMInNonenoneNoneWendyBoud
86930082522/3/2005 12:39:35 PMInNonenoneNoneJeremyAhlman
98772054062/3/2005 12:40:20 PMOutNonenoneNoneWendyBoud
106930082522/3/2005 12:40:45 PMOutNonenoneNoneJeremyAhlman
118772054062/3/2005 12:40:50 PMInNonenoneNoneWendyBoud
128772054062/3/2005 12:46:25 PMOutNonenoneNoneWendyBoud

I need to be able to take this information and display it in a data grid so that on each row I see the Member Number, First and Last Name and the In and Out Time.

I am not sure how to group the In and Out times together so that the query knows which time out corresponds to which time time for the member?

Any help is greatly appreciated!

View 9 Replies View Related

Derive Attendance Data From CLOCK IN And OUT

Jan 28, 2014

I am trying to develop an attendance application which calculates the shift details as per the Clock IN OUT Data , the following are the table details

CREATE TABLE [dbo].[INOUTData](
EmpID VARCHAR(10),
CLockDate Date NULL,
[INTIME] Time NULL,
[OUTTIME] Time NULL,
) ON [PRIMARY]

[Code] ....

-- I am trying to get the data as follows combining these two tables

EMpID ClockDateSession1StartTimeSession1EndTimeActualSession1StartTimeActualSession1EndTime
Session2StartTimeSession2EndTimeActualSession2StartTimeActualSession2EndTime
E1 28-Jan-201408:00:0012:00:0007:50:0012:15:0017:00:0021:00:0017:15:00 20:55:00
E2 28-Jan-201408:00:0012:00:0008:30:00NULL17:00:0021:00:0016:15:0021:55:00
E3 28-Jan-201408:00:0012:00:00NULL 11:34:0017:00:0021:00:0016:15:00 21:55:00
E4 28-Jan-201408:00:0012:00:0008:30:00 11:34:0017:00:0021:00:00NULL 21:55:00
E5 28-Jan-201410:00:0014:00:0010:35:0014:44:0019:00:0023:00:0018:55:00 NULL

Right now i am using a stored procedure which traverses through each record and does the job.

Is there any way to do it with Pivot, or queries instead of cursors.

View 2 Replies View Related

How To Find Conditions Across Rows (attendance)

May 4, 2006

Hello,I need to find students that have 4 consecutive absences. When astudent is absent 4 times in a row, they can be dropped from the class.My class attendance file contains each attendance by date and whetherthey were present or not. When the student has 4 consecutive value 1(absent) for a given session and a given class the are considered to bedropped.If I needed to know the total number of absences, I know I could groupand summarize, but this one has the consecutive twist.Table:CREATE TABLE "dbo"."clsatt"("FULL_CLASS_ID" CHAR(15) NOT NULL,"STUDENT_ID" CHAR(20) NULL,"SESSION_ID" CHAR(10) NULL,"MEETING" SMALLINT NOT NULL,"PRESENT" CHAR(2) NOT NULL);Present value of 1 is absent, value of 2 is present (3 means holiday)Classes typically meet 12 times.I would want something likeFULL_CLASS_ID, STUDENT_ID, SESSION_ID, 'Dropped'as the output.Notice in the example the first student was absent the last 4 meetingsThe second student 5 absensesand the third student was totally absentIn these three examples, they are flagged as dropped.TIARobInserts:---------------------------------------------------------------------------------insert into clsatt values ('BUS100','1675812194','200203',1,'2')insert into clsatt values ('BUS100','1675812194','200203',2,'2')insert into clsatt values ('BUS100','1675812194','200203',3,'2')insert into clsatt values ('BUS100','1675812194','200203',4,'2')insert into clsatt values ('BUS100','1675812194','200203',5,'2')insert into clsatt values ('BUS100','1675812194','200203',6,'2')insert into clsatt values ('BUS100','1675812194','200203',7,'2')insert into clsatt values ('BUS100','1675812194','200203',8,'2')insert into clsatt values ('BUS100','1675812194','200203',9,'1')insert into clsatt values ('BUS100','1675812194','200203',10,'1')insert into clsatt values ('BUS100','1675812194','200203',11,'1')insert into clsatt values ('BUS100','1675812194','200203',12,'1')insert into clsatt values ('BUS100','1712400537','200203',1,'2')insert into clsatt values ('BUS100','1712400537','200203',2,'2')insert into clsatt values ('BUS100','1712400537','200203',3,'2')insert into clsatt values ('BUS100','1712400537','200203',4,'2')insert into clsatt values ('BUS100','1712400537','200203',5,'2')insert into clsatt values ('BUS100','1712400537','200203',6,'2')insert into clsatt values ('BUS100','1712400537','200203',7,'2')insert into clsatt values ('BUS100','1712400537','200203',8,'1')insert into clsatt values ('BUS100','1712400537','200203',9,'1')insert into clsatt values ('BUS100','1712400537','200203',10,'1')insert into clsatt values ('BUS100','1712400537','200203',11,'1')insert into clsatt values ('BUS100','1712400537','200203',12,'1')insert into clsatt values ('BUS100','1801704805','200203',1,'1')insert into clsatt values ('BUS100','1801704805','200203',2,'1')insert into clsatt values ('BUS100','1801704805','200203',3,'1')insert into clsatt values ('BUS100','1801704805','200203',4,'1')insert into clsatt values ('BUS100','1801704805','200203',5,'1')insert into clsatt values ('BUS100','1801704805','200203',6,'1')insert into clsatt values ('BUS100','1801704805','200203',7,'1')insert into clsatt values ('BUS100','1801704805','200203',8,'1')insert into clsatt values ('BUS100','1801704805','200203',9,'1')insert into clsatt values ('BUS100','1801704805','200203',10,'1')insert into clsatt values ('BUS100','1801704805','200203',11,'1')insert into clsatt values ('BUS100','1801704805','200203',12,'1')insert into clsatt values ('BUS100','1922287588','200203',1,'1')insert into clsatt values ('BUS100','1922287588','200203',2,'1')insert into clsatt values ('BUS100','1922287588','200203',3,'2')insert into clsatt values ('BUS100','1922287588','200203',4,'2')insert into clsatt values ('BUS100','1922287588','200203',5,'2')insert into clsatt values ('BUS100','1922287588','200203',6,'2')insert into clsatt values ('BUS100','1922287588','200203',7,'2')insert into clsatt values ('BUS100','1922287588','200203',8,'2')insert into clsatt values ('BUS100','1922287588','200203',9,'2')insert into clsatt values ('BUS100','1922287588','200203',10,'2')insert into clsatt values ('BUS100','1922287588','200203',11,'1')insert into clsatt values ('BUS100','1922287588','200203',12,'2')insert into clsatt values ('BUS100','2188469657','200203',1,'1')insert into clsatt values ('BUS100','2188469657','200203',2,'1')insert into clsatt values ('BUS100','2188469657','200203',3,'2')insert into clsatt values ('BUS100','2188469657','200203',4,'2')insert into clsatt values ('BUS100','2188469657','200203',5,'2')insert into clsatt values ('BUS100','2188469657','200203',6,'2')insert into clsatt values ('BUS100','2188469657','200203',7,'2')insert into clsatt values ('BUS100','2188469657','200203',8,'2')insert into clsatt values ('BUS100','2188469657','200203',9,'1')insert into clsatt values ('BUS100','2188469657','200203',10,'1')insert into clsatt values ('BUS100','2188469657','200203',11,'1')insert into clsatt values ('BUS100','2188469657','200203',12,'2')insert into clsatt values ('BUS100','2515197431','200203',1,'1')insert into clsatt values ('BUS100','2515197431','200203',2,'1')insert into clsatt values ('BUS100','2515197431','200203',3,'2')insert into clsatt values ('BUS100','2515197431','200203',4,'2')insert into clsatt values ('BUS100','2515197431','200203',5,'1')insert into clsatt values ('BUS100','2515197431','200203',6,'2')insert into clsatt values ('BUS100','2515197431','200203',7,'2')insert into clsatt values ('BUS100','2515197431','200203',8,'1')insert into clsatt values ('BUS100','2515197431','200203',9,'2')insert into clsatt values ('BUS100','2515197431','200203',10,'2')insert into clsatt values ('BUS100','2515197431','200203',11,'1')insert into clsatt values ('BUS100','2515197431','200203',12,'2')

View 6 Replies View Related