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
I want to have a select that will pull the following information out with count = the number of rows that have matching data in data4
ID data1 count 1 535 2 2 325 2 3 654 1 4 765 1
Right now I am using a VB script to loop thru get the current data4 value then using SELECT COUNT(data1) AS count FROM tbl_toolerrors WHERE data4 = {data4 value currently looking at}
Of course this take a bunch of trips to database and I think there should be a way to do it. I was thinking of a nested SQL querry like
Select data1, data2, ID, data3, (select count ...) Order by data1
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
I have a question regarding the total count of the table rows
Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".
I have 3 tables: Suppliers, Documents and Verification, each document may have multiple verification where I need to get the last verification according to verification date. So I just need a "Select top 1 result from Documents, Verification where doc_iddoc=ver_iddoc and result='True' order by ver_date desc" so far I get the result of the last verification, but here's the problem:
I need to get a row with the count of documents for each supplier, I mean:
Supplier Name Docs NegativeVerification Acme Co 10 1
that is I would need to loop for each supplier and each document and get the last verification, if one of any of documents have negative verification then add it to negative results. Is it possible to achieve this with a query or do I have to do it through stored procedure?
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
I need to do a count on a column in my table but i have to check for a condition first.
Here is my sample data
Providerdate Region DHB DHBName PHO PHOName Practice Practicename PracticeAddress Practice_Startdate Practice_Enddate Practitioner PractitionerName Practitioner_Startdate Practitioner_Enddate Locum
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000045 Horatepai 15 Epiha Street, Paraparaumu 1/07/2003
29201 Geraldine Victoria MacKenzie Jordan 8/06/2005 31/10/2005 no
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000045 Horatepai 15 Epiha Street, Paraparaumu 1/07/2003
22079 Glenn Morton Colquhoun 1/03/2006
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000045 Horatepai 15 Epiha Street, Paraparaumu 1/07/2003
14121 Meaburn Charles Staniland 1/07/2003
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000045 Horatepai 15 Epiha Street, Paraparaumu 1/07/2003
9877 Philip White 1/07/2003
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000045 Horatepai 15 Epiha Street, Paraparaumu 1/07/2003
11678 Susan Prudence Wilson 1/07/2003
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000045 Horatepai 15 Epiha Street, Paraparaumu 1/07/2003
3881 Wilfred Travis Wilson 5/01/2002
yes
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000052 Paraparaumu Medical Centre 92-94 Kapiti Road, Paraparaumu 1/07/2003
22742 Adrian Howard Beaumont Gilliland 13/01/2005 28/10/2005 NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000052 Paraparaumu Medical Centre 92-94 Kapiti Road, Paraparaumu 1/07/2003
18188 Amanda Mary Clarke 1/07/2003
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000052 Paraparaumu Medical Centre 92-94 Kapiti Road, Paraparaumu 1/07/2003
32909 Andre Honda Garib 29/12/2005
no
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000052 Paraparaumu Medical Centre 92-94 Kapiti Road, Paraparaumu 1/07/2003
13042 Christopher Alan Fawcett 1/07/2003
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000052 Paraparaumu Medical Centre 92-94 Kapiti Road, Paraparaumu 1/07/2003
16556 Jennifer Maree O'Donnell 1/07/2003 22/12/2005 YES
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000052 Paraparaumu Medical Centre 92-94 Kapiti Road, Paraparaumu 1/07/2003
13650 Timothy Stapleton Smith 1/07/2003
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000046 Paul Norton Surgery 51 Kapiti Road, Paraparaumu 1/07/2003
14621 Paul Stephen Norton 1/07/2003
NO
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000053 Raumati Road Surgery 23 Raumati Road, Raumati Beach, Paraparaumu 1/07/2003
11202 Katherine Dana Stone 3/10/2005 7/10/2005 yes
1/01/2006 Central 091 Capital & Coast DHB 585171 Kapiti PHO-585171 585171_0000053 Raumati Road Surgery 23 Raumati Road, Raumati Beach, Paraparaumu 1/07/2003
13048 Robyn Lynette Crow 1/07/2003
NO
now i have to add two new columns as LocumYes and LocumNo In Locumyes column I have to count the number of 'yes' in the locum column grouped by practitioner and in locumno i have to count the number of 'no' for that practitioner.
The situation is a practitioner can work on more than one practice and he can work as a 'yes' locum in one,'no' locum' in another or vice versa.It can be of any sort of combination.
so I have to do a query which should group by practitioner and count the number of yes and no for each practitioner..
I have the following code to send out an email if the row count of a table exceeds a certain number. But it seems to fire an email alert no matter what. condition check part of the code?
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.
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?
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.
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.
I have two tables : Students and StuHistory. The structure of the Student table is as follows :
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Student]( [RID] [int] NOT NULL, [Class] [int] NULL, [Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SubSection] [int] NULL, [RollNo] [int] NULL, [DesiredRoll] [int] NULL, [TrackingNo] [int] NULL, [Original_rollno] [int] NULL, [StudentStatus] [int] NULL ) END GO
A section has subsections where students are allocated rollno's. Every student has a unique roll no in that subsection. However he is also given a choice to enter his desired roll no. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count. For the first unique desired roll no in that subsection/section the tracking no is always 0. [StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted).
After every fortnight, i have to run a batchquery that does the following:
1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student.
2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno
So if 4 students have chosen the desired roll no as 5 and their current roll no is scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted.
How do i write query for this? I have been struggling.
I thought of posting this as a new post as it was mixed in the previous post.
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.
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
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.
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.
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.
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.
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 )
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.
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?
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?
Create table #table (id int identity , from_country varchar(20) , to_country varchar(20),noofdays int, datetravel datetime ) insert into #table(from_country,to_country,noofdays,datetravel) values ('Malaysia','India',2,getdate()-99), ('India','Singapore',4,getdate()-88), ('Singapore','China',5,getdate()-77), ('China','Japan',6,getdate()-66), ('Japan','USA',7,getdate()-55) select * from #table
I want to insert data to another table based on "noofdays" columns. If "noofdays" is 4 then 4 rows should inserted to new table with 1 day increment in "datetravel" column.
Ex : #table 1MalaysiaIndia22015-02-09 02:04:09.247 2IndiaSingapore42015-02-20 02:04:09.247
[code]...
In #table , 1st row noofdays is 2 , so in new table #table_new first 2 rows should inserted with 1 day increment in "datetravel" column.
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
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.
