Transact SQL :: Selecting First Row After Date Matching Condition?
Sep 22, 2015
I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.
The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.
The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.
I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.
I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:
SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date
FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O
on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...
I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.
View 9 Replies
ADVERTISEMENT
Nov 26, 2015
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3. Â
If the declined date is filled in. Â Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but  i got an error message when trying this out.  Against the code Â
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. Â What do I need to do to get both my outputs working?Â
View 9 Replies
View Related
Apr 3, 2015
Suppose I have string like
@strname varchar= = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG'
and table contains two columns having rows like,
ID text
1 abcd ER
2 abcd AS
3 abcd Oh
4 xyz TR
5 azs WS
6 abcd O
7 OP trx
how can I search a ID's which are exist in my string.
result should be,
3 abcd Oh
6 abcd O
View 4 Replies
View Related
Apr 19, 2007
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
View 4 Replies
View Related
Oct 17, 2015
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
View 3 Replies
View Related
Aug 5, 2004
Hi there,
I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.
So I just want to use a cursor on a table list that meet the criteria:
1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '
Example
declare @strqry varchar(1000)
declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
open dailyYear
fetch next from dailyYear into @DelTable
while @@FETCH_STATUS = 0
begin
Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '
exec(@strqry)
fetch next from dailyYear into @DelTable
end
close dailyYear
deallocate dailyYear
Any help would be greatly appreciated!!
Thanks
S
View 3 Replies
View Related
Apr 12, 2001
Dear Friend,
How to select a desired sequence of records without where condition?
For example I want to select the first 100 records I am using
SELECT TOP 100 * FROM EMPLYEETB;
Like this I want to select the records starts from 101 to 200.
Is it possible? If yes how?
Thanks in advance.
Lovingly,
Meyyarasu
View 1 Replies
View Related
May 6, 2015
I'm developing a Lottery system. Here are some background information:
- Total 48 numbers
- Draw 6 numbers + 1 extra number
- each Bet select 6 numbers
And the Prize:
Prize
Selected Matched
Extra number Matched
1st
6
No
2nd
5
Yes
[Code] ...
Below is my proposed table design (simplified):
Draw table
Column
Datatype
DrawDate
date
Primary Key
ResultNumber1
tinyint
[Code] ....
There will be millions of Bet for a Draw. I need to write a stored procedure to check which bets won the 1st ~ 7th prizes. How to write a query to match the bets with the draw result? The query should be run within 1 minute. And should I change my table design?
View 5 Replies
View Related
Apr 25, 2015
We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd]. If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result. Below is how they designed the table and below that is the stored procedure to pull the records.Â
CREATE TABLE [dbo].[BillingHistory](
[BillingHistoryID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[plincd] [varchar](3) NULL,
[pgrpcd] [varchar](4) NULL,
[pitmcd] [varchar](4) NULL,
[newplincd] [varchar](2) NULL,
[Code] ....
View 13 Replies
View Related
Sep 15, 2015
We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it. The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number. Below is what I have so far but it only matches exact part numbers. One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match. Both PartNumbers are varchar(30).Â
SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake
FROM dbo.PartsInventory INNER JOIN
dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN
dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN
dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake
WHERE (dbo.RepairOrderLines.RecordID = 46001)
View 3 Replies
View Related
Oct 6, 2015
Consider the following: I have a table, say ORDERS, with these entries -
CustID
ProductID
1Â Â Â Â Â Â CAN
2Â Â Â Â Â Â 2
3Â Â Â Â Â Â 1,2
4Â Â Â Â Â Â 4
5Â Â Â Â Â Â 1,2,3,4,5,CAN
6Â Â Â Â Â Â 10
7Â Â Â Â Â Â CAN
8Â Â Â Â Â Â Â 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
View 12 Replies
View Related
Aug 10, 2015
I have two queries as below;
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;
and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
View 4 Replies
View Related
May 4, 2007
Hi,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())�
WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
Any help would be greatly appreciated!
Grtz
Pascal
View 5 Replies
View Related
May 3, 2008
I am trying to link a table A to table B using service_dt : service_dt closer to asessment_dt
Table A
Reg_key service_dt
1 07/01/07
2 08/11/07
3 07/23/07
Table B
Reg_key ID assessment_dt
1 1001 07/11/07
1 1111 07/01/07
2 1012 08/01/07
2 1013 08/30/07
2 1187 09/12/07
3 657 07/20/07
3 789 07/20/07
3 1999 09/11/07
I am looking for a matching assessment_dt.. if it doesn;t exist .. i have to grab the closer assessment_dt..if there is more than one same record , then grap the lowest ID..
so the output should look like
Reg_key service_dt ID assessment_dt
1 07/01/07 1111 07/01/07
2 08/11/07 1012 08/01/07
2 07/23/07 657 07/20/07
View 1 Replies
View Related
Aug 6, 2015
create table target
(Taid int, studenid int, std int,div int,year int ,ActivityId int)
insert into target values(1,1,1,2015,1),
(1,1,1,2015,2),(1,1,1,2015,3),(1,1,2,2015,1)
,(1,1,2,2015,4)Â ,(1,1,2,2015,3);
create table activity(ActivityId int,Activity nvarchar)
insert into activity values (1,'sport')
,(2,'swim')
,(3,'read')
,(4,'dace');
I want to edit(update,delete,insert) target table activity from front end for that I took grid view and that grid all activity come of single student studenid(unique key for each student) that you see design below like thisÂ
that activity present in target table that come as check in check and that activity not select student from activity activity table that come unchecked
I want to update above information means  unchecked alredy check data should delete and new check data add , already check data update
I have use merge statement but problem is that at single time only one row execute that is check because  data come from data gridview.
View 5 Replies
View Related
Jul 10, 2015
I am in assumption that we cannot do NULL value comparison in where clause directly (results will not be proper) we have to use ISNULL. Is my understanding correct? Is using ISNULL efficient when we have to this operation for large volume?
View 5 Replies
View Related
Jul 24, 2015
I have an order with the time 08/03/2015 7:30 08/03/2015 10:30 ..I have availability with the time 08/03/2015 07:00:00 to 08/03/2015 19:30:00...Here the availability falls for 3 hours only..My requirement is that For order if availability< 4hours i don't want to show employee.How to apply the condition.
View 7 Replies
View Related
Jun 29, 2015
I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT Â A.CID,Â
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
View 4 Replies
View Related
Jul 4, 2015
I have a table called names (firstname, lastname, number) then i have a DECLARED variable called @displaynum...If @displaynum id true  my select query should select the number column. if the variable is false , only the first two columns are selected.
e.g  @displaynum = true                                         Â
@displaynum = false
Firstname | Lastname | number                              Firstname | Lastnam
blah          |  blah       |  12345                            Â
blah          | blah
i am not sure if this is possible as a case still selects the column but just changes the values.
View 12 Replies
View Related
Sep 16, 2008
How can I update only one row doing an update query?
Â
For example:
Â
update from table1
set category = 'C'
where country = 'Brazil'
Â
One want to update only one row with country = 'brazil'
View 5 Replies
View Related
Apr 15, 2008
Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated.
I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK.
When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.
Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is:
SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT
FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)
The flat file destination was mainly used to confirm the select.
Having confirmed my select, I changed the select in the OLE DB SOURCE as follows:
DELETE FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)
I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns.
Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters?
Thank you and God Bless.
View 9 Replies
View Related
Apr 27, 2015
I Â have table with filed
EmpProject -Table
Empno, Name, Project NO, cost center
I need to restrict the results by project No by user input. In case if user did not provide any value for the projectNO, then need to fetch all rows.
Select empno, name, projecno, cost_center from empproject where projectno=nvl(:pno, :deptno)
View 6 Replies
View Related
Nov 22, 2015
I have a question about SQL Server.
Table patient:
create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),
[Code] ...
Table Patientref:
create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),
[Code] ...
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Examples: patient
pn |  code  |  date    |  doctorcode
2 Â | Â 10 Â Â |2015-02-12 Â | Â 101
2 Â | Â 10 Â Â |2015-02-13 Â | Â 102
2 Â | Â 10 Â Â |2015-02-14 Â | Â 103
Table : Patientref:
pn |  code  |  sdate    |  edate    | Status
2 Â | Â 10 Â Â |2015-02-08 Â | Â 2015-02-19 | Â 4
2 Â | Â 10 Â Â |2015-02-09 Â | Â 2015-02-19 | Â 2
2 Â | Â 10 Â Â |2015-02-10 Â | Â 2015-02-19 | Â 2
2 Â | Â 10 Â Â |2015-02-11 Â | Â 2015-02-18 | Â 1
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditonÂ
Based on this, the desired output is below:
pn |  code  |  date    |  doctorcode | sdate   |edate    |status
1 Â | Â 10 Â Â |2015-02-19 Â | Â 100 Â Â Â Â |2015-02-19 |2015-03-24 | 2
1 Â | Â 10 Â Â |2015-02-19 Â | Â 101 Â Â Â Â |2015-02-19 |2015-03-24 | 2
1 Â | Â 10 Â Â |2015-02-19 Â | Â 102 Â Â Â Â |2015-02-19 |2015-03-24 | 2
2 Â | Â 10 Â Â |2015-02-12 Â | Â 101 Â Â Â Â |2015-02-10 |2015-02-19 | 2
[Code] ...
I tried it like this:
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
 outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref prÂ
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
 order by case when pr.status=2Â
then 1 when pr.status=4 then 2
 when pr.status=3 then 3Â
when pr.status=1 then 4 end ,pr.sdateÂ
 )pr
but this query not given expected result.here when dos not fall between sdate and edate  that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
View 7 Replies
View Related
Sep 29, 2015
I have an existing store proc with insert and update statements, I want to add a condition at the top of the proc with conditions like;
declare @dayofweek int
set datefirst 1
select @dayofweek = datepart(dw,getdate())
--select @dayofweek
if @dayofweek = 1
[Code] ...
If the condition meets with either of the above 2 conditions, than have to run the actual store proc.
My actual Storeproc in which I need to incorp the above conditions is:
CREATE PROCEDURE [dbo].[Load_Product]
@FileDate date = NULL
AS
BEGIN
DECLARE @EventText varchar(500),
@Rows int = 0,
@RowsTotal int = 0,
[Code] .....
View 12 Replies
View Related
Jul 3, 2015
I am using SQL Server 2008.Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. How to retrieve only the failed rows which means not all zone are 1 or NULL.In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item
Expected result:
Uniid <-> Stockcode <-> FailedRows
1670 <-> GJ-00064 <-> 1
View 8 Replies
View Related
Jul 16, 2015
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
View 15 Replies
View Related
Nov 6, 2015
below is my original query
select Value = count(*) from dbo.testÂ
I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.
View 5 Replies
View Related
Jun 1, 2015
I want to calculate year to month data for month wise . Ihave start range and end range table like below
Start range and end range table
Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015
[Code] ....
Then I want to pass this value one by one in below table for get value month wise
Main table
Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404
[Code] ....
The below query is for november month: (Apr to Nov)
select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)
So I want to pass second row
select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'
Like wise I want to get month wise data of YTM data.
My expected output is
201411
201412
201501
201502
201503
201504
201505
[Code] ....
View 10 Replies
View Related
Jun 3, 2015
I need to write a query on a table..
table defination .. table Client_Master
file_id int identity(1,1) primary keyÂ
, client_id int
,LOAN_SANCTION_DATE datetime
,is_reject bit
has Data ..
FILE_ID CLIENT_ID
LOAN_SANCTION_DATE IS_REJECT
21139 22784
2014-02-03 00:00:00.000 0
21141 22784
2014-02-03 00:00:00.000 NULL
20869 22784
2014-02-03 00:00:00.000 1
20870 22784
2014-02-03 00:00:00.000 0
21571 22784
2014-02-03 00:00:00.000 1
66056 22784
2014-02-03 00:00:00.000 1
Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03 Â out of which 3 are rejected ..
Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE  from Client_Master where all files has been rejected ..
means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..
I have wrote as below .. got the result but not satisfy with the query
SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files
,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected
FROM Â dbo.FILE_MASTERÂ
GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE
HAVING
COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )
View 4 Replies
View Related
Jun 17, 2015
I have a query where i would calculate the counts of three ServersÂ
declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%'Â
[Code] ...
Here i need output like below
Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
View 3 Replies
View Related
Dec 1, 2015
I have a Stored Procedure, wherein I need to use a while loop. essentially the loop looks like this.
Set @Kount = 1
--TestScript
Select 'TempReqTable', * from @TempReq
WHILE Exists(Select * from @TempReq WHERE TempID = @Kount)
BEGIN
--Do stuff with values from table
Set @Kount += 1
END
The test script confirms that there is only one row in the @TempReq table. However the loop never stops running and @Kount keeps being incremented and incremented until I manually stop the execution of the SP.
Is there some rule that I am not aware of that does not allow the use of an Exists statement in the condition of a While loop?
View 12 Replies
View Related
Sep 29, 2015
How do I get the below scenario:
EmpID DepID
12 2
17 3
17 2
13 3
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Desired Output:
EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2
View 5 Replies
View Related
May 1, 2015
I have a table (could have 1M or more rows in it) see structure below. I am looking to get the first and last date/times for each employee for each day. I also need the location GUID for the first read.
EmployeeGUID (uniqueidentifier datatype)
LocationGUID (uniqueidentifier datatype)
DateTime (DateTime datatype)
12345678-0000-0000-0000-000000000000
11111111-0000-0000-0000-000000000000
04/12/2014 07:00:01
[code]....
This would be the ideal output (I can do without the TotalTimeInHours):
EmployeeGUID (uniqueidentifier datatype)
LocationGUID (uniqueidentifier datatype)
FirstRead (DateTime datatype)
LastRead (DateTime datatype)
TotalTimeInHours
12345678-0000-0000-0000-000000000000
11111111-0000-0000-0000-000000000000
04/12/2014 07:00:01
04/12/2014 17:04:02
Total in hours between the first and last read.
44422222-0000-0000-0000-000000000000
22222222-0000-0000-0000-000000000000
04/14/2014 08:00:00
04/14/2014 14:00:03
44422222-0000-0000-0000-000000000000
33333333-0000-0000-0000-000000000000
04/15/2014 07:49:00
04/15/2014 09:00:01
I need the employees first and last reads for each date. They could have many entries per date or just 1.
View 13 Replies
View Related
