SQL Server 2012 :: How To Calculate Attendance For Single Student For Yearly Basis
Jul 2, 2015
I have to calculate the Total number of days present and absent for a singel student.
AS of now i have 3 tables.
1.Daily attendance - Columns -[Guid][,AcademyId],[StudentId],[Date],[Status],[Reason]
2.Student details - Columns - [Guid],[FullName],[DOB],[Address]
3.Class Details - Columns - [Guid],[AcademyId],[Class],[Section],[Startdate],Enddate]
So now i have loaded all the data into the table.
I can fetch the counts for total present and absent
Query i have tried is
Declare
@StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458'
Begin
select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
View 7 Replies
ADVERTISEMENT
Apr 5, 2012
I have a csv that contains attendance records that I get daily from a 3rd party grade book solution. I need to import directly into the attend table in our student database.
Code:
Attend File-"1112","0021","404550","20120402","ABU","2300000","06","05"
The file is setup as follows, School Year, school number, student_id, absence date, absence code, course number, section number.
I need to check the student schedule to see if they are scheduled for that class when the import runs. So if they had a schedule change in the middle of the day it won't post attend to a dropped class.
I have done something similar to this before with the way I export teachers out to our grade book. I have it check the master schedule to see if the teacher is teaching at least one class, that way it won't export tutors and office staff to the grade book. I used the script below to do that but not sure who to apply it to a bulk insert.
Code:
Script Used to export teachers note last four lines, checks master
USE [GSchool]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Secondly I need to check the date of the record and overwrite a record if one already exists for that exact course and section for that student, I need this because if they make changes to a previous day from absence un excused to excused I need to get rid of the unexcused by overwriting it.
One more thing that would be nice but is optional, is there a way to send log of errors on the import via email?
View 3 Replies
View Related
Feb 25, 2014
I'm using this query to to calculate yearly finance values.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance]
Now I need to multiply the [FinanceValue Variance] * 2.50 and for that how can I use the alias name as column in the query. I tried this but it says invalid column name.
select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance], [FinanceValue Variance] * 2.50 as [NewVariance] from Finance
SumofVariance output will be like 5690.5893656 Also how can I show the SumofVariance to round off 4 decimal places like this 5690.5894.
View 1 Replies
View Related
May 16, 2015
I have a table like below
i need to calculate total working time against 'AttID' (where I - intime, O - OutTime)
ex :
AttID TotalTime
1 08:02:00
2 07:45:00
View 7 Replies
View Related
May 12, 2014
I need to get results on quarterly basis, matching 2 quarters AUTOMATICALLY.
- As the new quarter starts, it needs to match the last quarter results.
SELECT DATEADD(mm, (QUARTER - 1) * 3, year_date) StartDate,
DATEADD(dd, -1, DATEADD(mm, QUARTER * 3, year_date)) EndDate,
QUARTER QuarterNo
[Code] ....
Here is my Query, I don't know whether I'm getting it right?
--Quarter 1
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter1
FROMdbo.tblRPT_Spend AS A INNER JOIN
dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
[Code] ....
View 4 Replies
View Related
Feb 15, 2015
I have create Store procedure for all the Source and destination table for loading new and updated record.
For example I have below Store procedure
1-SP_Archive_using_merge_Fdoor
2-SP_Archive_using_merge_Fdoop
now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .
below is the 2 sp .
--First SP
ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int
[code].....
View 1 Replies
View Related
Oct 6, 2015
I have query on grouping data on weekly basis..
1. Week should start from Monday to Sunday
2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).
3. I want output like below
Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12
View 1 Replies
View Related
Sep 14, 2015
I have update schema and I upload image with my desire result.
CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
[Code] .....
View 7 Replies
View Related
Jul 28, 2014
Here is sample data I am working with:
Create table cattimelines (categoryID int, EffectiveDate datetime, CategoryValue varchar(11))
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-01-01', 'A')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-02-01', 'B')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-04-01', 'C')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-07-01', 'A')
I need to calculates a term date for each record which will be 1 day before the effective date of any new record, thus:
CATEGORYIDEFFECTIVEDATETERMDATECATEGORYVALUE
10002014-01-012014-01-31A
10002014-02-012014-03-21B
10002014-04-012014-06-30C
10002014-07-01NULLA
View 3 Replies
View Related
Aug 31, 2014
In a sql server 2012 database, I have a field called date of birth that is stored in a tha that is stored in a datetime format. I need to determine the age of various students in the school system. Thus can you show me sql to that I can use to determine the a child is currently?
View 6 Replies
View Related
Sep 22, 2014
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
View 1 Replies
View Related
Sep 4, 2015
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
View 5 Replies
View Related
Jul 9, 2014
I want to write sql query which runs in a background on cyclic basis. Basically i want to count the row entries of 1 table and store the data and the count in two distinct columns.
View 3 Replies
View Related
Nov 25, 2014
SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
View 9 Replies
View Related
Jun 19, 2015
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test
where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest;
USE SpecTest
GO
CREATE TABLE [dbo].[Spec1](
[Spec1ID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,
[Code] ....
Here is some dummy data to use
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
[Code] ....
Example desired results (I hope the formatting works)
Channel | Timestamp | Lambda | Power | Elapsed_Time
______________________________________________________________
273 | '2014-05-29 14:44:17.713', | 800, | '-64.91' | 0
273 | '2014-05-29 15:05:09.507', | 800, | '-64.91' | 00:20:51
273 | '2014-05-29 15:26:00.520', | 800, | '-64.91' | 00:41:42
273 | '2014-05-29 16:28:34.213', | 800, | '-64.91' | 01:44:16
273 | '2014-05-29 16:49:25.853', | 800, | '-64.91' | 02:05:08
273 | '2014-05-29 17:10:17.010', | 800, | '-64.91' | 02:25:59
View 9 Replies
View Related
Mar 11, 2015
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME
[Code] ...
Below is Our Sample Table Data.
Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015
How to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17
View 5 Replies
View Related
Feb 2, 2014
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.
[Code] ......
View 4 Replies
View Related
Jun 15, 2015
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end
and in the second Temp Table, I have
select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
View 9 Replies
View Related
Jun 5, 2014
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
View 8 Replies
View Related
Sep 20, 2015
How to retrieve all possible sets of values from a table, with each set having a unique identifier.
Here's sample data, where any number of rows can be in the "animal" set:
select animal.name
from (
select 'Cat' as name
union all select 'Dog'
union all select 'Bird'
) animalHere's what I'm trying to get:
[Code] ....
It'd be an easy task if I knew how many rows were in the set, but without knowing how many (and being able to do x number of cross joins and CUBE/ROLLUP to produce the list of permutations) or writing a horrid complex of while loops, I'm at a loss.
A single column. All permutations of the values in a single column. Perhaps I should stay off here nearer the end of the day .
View 2 Replies
View Related
May 30, 2014
I have the following table
CREATE Table #Table1
(
ID INT, Name VARCHAR(50), Class VARCHAR(10)
)
INSERT INTO #Table1
Select 1, 'name1', 'a' UNION ALL
Select 2, 'name1', 'a' UNION ALL
[Code] ....
Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?
CREATE Table #Table2
(
ID INT, CommaSeparated VARCHAR(100)
)
INSERT INTO #Table2
Select 1, 'name1, a' UNION ALL
[Code] ...
What I have
Select * FROM #Table1
Final Result
Select * FROM #Table2
Note that I still want to see all the IDs regardless.
If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.
CREATE Table #Table3
(
CommaSeparated VARCHAR(100)
)
INSERT INTO #Table3
Select 'name1, a' UNION ALL
Select 'name2, b, c, d' UNION ALL
Select 'name3, e, f'
Select * FROM #Table3
View 3 Replies
View Related
Oct 16, 2014
Here is my problem:-
declare @test as varchar(32)
declare @test2 as varchar(32)
set @test='today''s problem'
set @test2='my <string> '
select @test as '@attribute' for xml path ('myrow')
select @test2 as '@attribute' for xml path ('myrow')
I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.
View 4 Replies
View Related
May 26, 2015
I have records like below, single query to get result below, basically records that has single entry only, which has type '0'
table : temp_test
idtype
c10
c25
c30
c40
c47
c59
c64
c60
c77
c80
c90
Result out of query
idtype
c10
c30
c80
c90
View 4 Replies
View Related
Apr 29, 2015
have a Prod Server A having TDE enabled on 2 of those databases. I have a Prod Server B having TDE enabled on 3 of those databases. Now I have to create a single Dev server Server C for all the above 5 databases residing on the two servers. So how can I restore all the 5 database backup files on server C.
Does it mean that I need to copy the certificates and Keys from both the Prod server to this Dev Box and then restore the backup files. Once done, I can enable the encryption ON on those 5 database on Dev box or is there any different approach.Also how will tempdb behave in this scenario.
View 5 Replies
View Related
Mar 18, 2014
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
View 2 Replies
View Related
Nov 21, 2013
I have this SP
ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
AS
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
DECLARE @i bigint
SET @i=0
[Code] ....
View 1 Replies
View Related
Jan 27, 2014
I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together.
People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801
[code]....
Results
PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom
View 8 Replies
View Related
Nov 17, 2014
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
View 4 Replies
View Related
Feb 12, 2015
I am looking a script which allow me add single coilumn to multiple table of my database.
For Example :-
I am having 4 table
1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table
Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.
Alter table EMP
Add Rowchecksum varbinary(8000)
View 1 Replies
View Related
Mar 3, 2015
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
For Eg:
Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L
View 9 Replies
View Related
Jul 17, 2015
I have Table Staffsubjects
with columns and Values
Guid AcademyId StaffId ClassId SegmentId SubjectId Status
1 500 101 007 101 555 1
2 500 101 007 101 201 0
3 500 22 008 105 555 1
I need to do 3 scenarios in this table.
1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier
[Code] ....
View 8 Replies
View Related
Oct 28, 2015
I have the following results:
CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110
And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.
View 9 Replies
View Related
Nov 21, 2015
i am having problem putting this query to calculate students grade using the condition and legend bellow.
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
[code]...
View 7 Replies
View Related
