How To Find Conditions Across Rows (attendance)

May 4, 2006

Hello,
I need to find students that have 4 consecutive absences. When a
student is absent 4 times in a row, they can be dropped from the class.

My class attendance file contains each attendance by date and whether
they 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 be
dropped.
If I needed to know the total number of absences, I know I could group
and 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 like
FULL_CLASS_ID, STUDENT_ID, SESSION_ID, 'Dropped'
as the output.

Notice in the example the first student was absent the last 4 meetings
The second student 5 absenses
and the third student was totally absent
In these three examples, they are flagged as dropped.
TIA
Rob
Inserts:

---------------------------------------------------------------------------------
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


ADVERTISEMENT

Compare Values In Consecutive Rows And Print Rows Based On Some Conditions

May 8, 2008

I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE

Can someone please help me here?

Thanks,
Romakanta

View 1 Replies View Related

SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)

Jul 8, 2015

Merge two rows into one (conditions or Pivot?)

I have Temptable showing as

Columns:EmpName, Code, Balance

Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10

I want to insert the above temp table to another table with column names defined below like this

Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10

Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

View 3 Replies View Related

Select Rows Based On Multiple Date Conditions

Jun 5, 2008

Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.

SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.

Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?

Thanks,
Marc

View 2 Replies View Related

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:


SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)


No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'


-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')


So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 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

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 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

Employee Attendance With In/out Times (was Query Help Needed...)

Apr 8, 2006

Dear Friends,
I am in problem & have to solve one query.
I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx ....

View 7 Replies View Related

How To Get Employees Attendance As Desired Output From The Input

Jun 27, 2013

I am uploading input sample data and desired output data, can get the desire output.

View 4 Replies View Related

Attendance Records - Bulk Insert In Student Database

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

Transact SQL :: Calculate Total Working Time Against Attendance ID?

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

Self Join To Find Rows With Same Value In A Col

May 14, 1999

Hi,

I forgot the syntax to to a self-join to find all rows that have
duplicate values. Could someone post the answer to the following example:

Column1
-----
x
y
z
x

After the select statment runs "x" would be the output.

Thanks,
Ken

View 2 Replies View Related

How To Find Out The Rows In A Table?

Apr 10, 2002

Hi Frinds,

How can i find out the no of rows in each table. if the no of table are more than thousands(i.e. 1000+,2000+ etc..) are there. Certainly 'select count(0) from <table>' is not the good idea. Egarly awaiting for the solution.


thanks

Nageswara Rao Bomma
nrbomma@yahoo.com
nrbomma@hotmail.com

View 2 Replies View Related

Find Out Who Deleted Rows?

Jan 20, 2004

Yesterday, I had an occurence where someone (one of our developers :) ) deleted many vital rows in a database. I was able to recover via a backup but I'd like to see what was run to delete those rows, when and who if possible? I have the transaction log and the _log.ldf.

Can I read those somehow to find out this information? I have since added auditing to said tables, but I'd really like to go back and see what happened?

I do know about Lumigent, but is there a different production/solution?

Thoughts?

Thank you

View 2 Replies View Related

How To Find Out What Rows Are Not In A Table

Sep 27, 2006

Hi

I have a problem where I must compair an import table with a local datatable and import rows that are missing and correct the rows that are different.

How to best do this?

I was hoping to avoid cursors



thanks



Walter

View 3 Replies View Related

How To Find The Number Of Rows In A Table

May 12, 2006

I try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:




Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb    Line: 41
What to do? I need a complete example to see how it works.
Thanks...

View 3 Replies View Related

How To Find Duplicate Rows In SQL Server

Apr 30, 2004

I would like to locate duplicate rows within a specific table. This table has 12 diffrent rows.

BASENO - POSITION - SEQ - PROD -STYL - DESCR - FIELD01 THRU FIELD05 - VALUE01 THRU VALUE05 - FORMTYPE - ANSWER

I have been playing with the following query but can't seem to get it perfect to locate my dups within sql. Can someone help me with the querry?

I'm playing with the following querry.
SELECT
<list of all columns>
FROM
tablename
GROUP BY
<list of all columns>
HAVING
Count(*) > 1

Can somone possible input my column names into this querry that would possibly get it to locate my dups? I'm missing somehting and not sure what.

Thanks for any help

SQL Newbie

View 9 Replies View Related

T-SQL (SS2K8) :: Find Same Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

CREATE TABLE A
( nRef INT,
nOrd INT,
Token INT,
nML INT,
nNode INT,
sSymbol VARCHAR(50),

[code].....

if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed.

Hence the desired output will be

Symbol Count
Castorseed-Silver 8

How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 1 Replies View Related

Find And List Duplicate Rows

Oct 17, 2013

I'm using this to find duplicates where a person has the same email but varying firstname and lastnames:

select distinct t1.booking_id, t1.first_name, t1.last_name, t1.email_add, t1.booking_status_id
from [aren1002].[BOOKING]
as t1 inner join [aren1002].[BOOKING]
as t2
on t1.last_name=t2.last_name and t1.booking_id<>t2.booking_id
where t1.booking_status_id = 330
order by last_name asc

Sample data:
3927 Greg Smith greg@emailno1.com 303
5012 John Smith greg@emailno1.com 303
6233 John Smith greg@emailno1.com 303
4880 Dulcie Abuud dulcie@theiremail.com 303

However it is listing the non duplicate rows, For example: The record with Abuud as the last name, doesn't have any duplicates in the table, so I don't want it listed.

The data should be like this:
3927 Greg Smith greg@emailno1.com 303
5012 John Smith greg@emailno1.com 303
6233 John Smith greg@emailno1.com 303

View 4 Replies View Related

Find Matching Sets Of Rows

Apr 11, 2006

Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a

View 7 Replies View Related

Find Rows Within Seconds Of Top Of Hour

Oct 24, 2007

I have rows of data that have a datetime stamp. I need to find rowsthat have a datetime stamp within 10 seconds of the top of each hour.I started with datediff(s,getdate(),LeadDate) but I am stumped on howto process for each hour.I.E. records between 9:00:00 and 9:00:10 , 10:00:00 10:00:10, and soforth for each hour.Ideas?

View 1 Replies View Related

Transact SQL :: Find Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

CREATE TABLE A
(    nRef INT,
     nOrd     INT,
     Token     INT,
     nML              INT,
     nNode            INT,
 sSymbol          VARCHAR(50),
 nMessageCode     INT
)
INSERT INTO A
(   nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER',13073),

[code]....

if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 6 Replies View Related

Transact SQL :: Find How Many Rows Replicated

Aug 20, 2015

How Do we find how many ROWS REPLICATED to subscriber at specific interval ?

View 3 Replies View Related

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 View Related

Find Rows With Records Containing More Than Two Numbers After Decimal

Jan 22, 2014

How do I find the rows that have more than two decimal numbers after the decimal point for example 2.787686

I am trying :

select amount, LEN(AMOUNT) - CHARINDEX('.', amount) as DecimalCount from GL_REPORT

but is not working.

View 14 Replies View Related

SQL 2012 :: Find All Rows WHERE DATETIME Within DATE

May 19, 2014

What would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.

So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.

declare @DATE DATE = '01/14/2014'
--A:
SELECT * FROM TT
WHERE CAST( TT.DATETIME AS DATE) = @DATE

--B:
SELECT * FROM TT
WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)

View 3 Replies View Related

Compare 2 Rows To Find The Difference In Columns?

Aug 14, 2014

How would I compare 2 rows to find the difference in the columns?

Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3

Result:
Column1: Text2 New
Column3: Text4 Old Text3 New

View 6 Replies View Related

I Need To Find The Rows That Exist In One Table But Not In The Other With Condition

Jun 20, 2007

I need to find the rows that exist in one table but not in the otherwith this condition:(prod_name exist in table1 and not in table2.prod_name ) AND(prod_name exist in table1 and not in table2.'S'+prod_name )explanation:i want to know if the product not exit and if the combination of thecharachter "S" with the product Name also not exist at the othertableB.Ryuvi

View 2 Replies View Related

How To Find Duplicate Rows In Flat File ?

Sep 6, 2007

In the FLAT FILE source, I have to find the duplicate rows based on the two fields say, "bill number" & "invoice date".

The rows within flat file has like "bill number" which is duplicated on the same "invoice date".

If duplicate rows found then move the duplicate rows into another Flat File.

If not found then move the rows into Sql Server Table.

Pls provide the solution. Thank you

View 9 Replies View Related

How To Find Rows With Matching Numbers In One Table

Oct 19, 2007

Hello All,

I have one table that contains information about invoices.
However I need to find out if invoice numbers have been used more than once.

I've tried to join the table to itself and find matching numbers, however I can't seem to get it to work properly.

Any help will be appreciated.
Thanks,

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved