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 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 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 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 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'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.
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED") I tried the following which doesn't work: =IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"), IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.
I also need to adjust rows, say mth and nth that meet condition , say y.
My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)
This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.
Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:
ID Int1 Type1 Ideal Rank by Type
1 1 1 1
2 1 1 2
3 2 1 3
4 3 1 4
5 5 1 5
6 8 2 1
7 13 1 6
8 21 1 7
9 34 1 8
10 55 2 2
11 89 1 9
12 144 1 10
13 233 1 11
14 377 1 12
15 610 1 13
16 987 2 3
17 1597 1 14
18 2584 1 15
19 4181 1 16
20 6765 1 17
The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...
I hope this posting is clear, albeit long.
Thanks in advance
Greg
PS The code:
USE
master
GO
CREATE DATABASE CertainRowsToChange
GO
USE CertainRowsToChange
GO
CREATE TABLE InitialisedValues
(
InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,
I use SQL 2000 I have a Column named Bool , the value in this Column is 0?0?1?1?1 I no I can use Count() to count this column ,the result would be "5" but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid as the True is 2 and False is 3 the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count could it be done by Count()? please help. thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select -- count returns unit data Count(substring(m.PTNumber,3,3)) as PTCnt, -- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt, -- attempting to calculate the percent by PTCnt/TotalCnt returns 0 (Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct -- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
da = New Data.SqlClient.SqlDataAdapter("SELECT [Products].[Names], Count([ProductList].[Products]) AS [Total] FROM [Products] LEFT JOIN [ProductList] ON [ProductList].[Names] = [Products].[Names] GROUP BY [Products].[Names] ", strConnection) can we use a where condition in the statement.If so how can we use it.
Hi all,I'm building a DataSet on Visual Studio and don't know how to do a condition (if/else) with SQL... I have a search form, with a DropDownList and have 2 options in it: Search by Title or Search by Author. If the "Title" is selected, then the value is "title and if "Author" is selected, then the value is "author".Here is what I have right now for the DataSet, as seperated queries but I think I can combine them to be one single query 1.This will returns the songs that matches the title:SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK, LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BYFROM t_lyricsWHERE ((@LYRICS_TITLE IS NULL) OR (LYRICS_TITLE LIKE '%' + @LYRICS_TITLE + '%') OR (LYRICS_TITLE2 LIKE '%' + @LYRICS_TITLE + '%')) AND (@LYRICS_TYPE = 'title') 2. This returns the songs that matches the author: SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK, LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY FROM t_lyrics
WHERE ((@LYRICS_AUTHOR IS NULL) OR (LYRICS_AUTHOR LIKE '%' + @LYRICS_AUTHOR + '%') OR (LYRICS_AUTHOR2 LIKE '%' + @LYRICS_AUTHOR + '%')) AND (@LYRICS_TYPE = 'author') This is very inefficient because I have 2 queries, and I need to build 2 ObjectDataSources as well as 2 different GridViews to display the results. I think we can do something likeSELECT .... ... FROM t_lyricsif (@LYRICS_TYPE = 'title') DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH TITLEelse if (@LYRICS_TYPE = 'author') DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH AUTHOR But I don't know how to write that in T-SQL.Any help would be greatly appreciated,Thank you very much,Kenny.
i'm working on a query and have discovered something fairly simple regarding "and" / "or" condition.
if I use e.g.
id_product in ('1111','2222')
as a result i should get all products that match id = 1111 and id = 2222.
But if I do it like
id_product in ('1111') and id_product in ('2222')
as a result i get 0 rows returned, where as i want to find invoices that have both products, and not those which have either product 1111 or 2222 or even both :)
If prmA is something like generic all, any, I want this query return value without condition. Is there a way to do this with sql or I should write stored procedure that checks @prmA and all other condition parameters and generate new SQL statement?
In following query, I use three conditions in WHERE caluse. When I use only CreditUnion.Id=@CreditUnionID Then I get the right value set. But when I join other two conditions , i get all the values instead of relevant data for that parameter.
Can anyone say why it happenes?
Code Block SELECT Member.LastName + ' ' + Member.FirstName AS MemberName, CASE WHEN CuStatus = 'Existing' THEN 'Existing' ELSE 'New' END AS MemberType, EnumCUMembershipStatus.UIText AS Status, CDOrder.DecidedOnCU, SysUserLogon.LastName + ' ' + SysUserLogon.FirstName AS CUDecisionOfficer, 'CD' AS ProductType, CreditUnion.Name
FROM Member INNER JOIN CDOrder ON Member.LastCDOrderFK = CDOrder.Id AND Member.Id = CDOrder.MemberFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id INNER JOIN EnumCUMembershipStatus ON Member.CuStatus = EnumCUMembershipStatus.Name INNER JOIN SysUserLogon ON CreditUnion.Id = SysUserLogon.CreditUnionFK
WHERE (CreditUnion.Id = @CreditUnionID) AND (Member.CuStatus = 'Approved') OR (Member.CuStatus = 'Declined')
UNION
SELECT Member_1.LastName + ' ' + Member_1.FirstName AS MemberName, CASE WHEN CuStatus = 'Existing' THEN 'Existing' ELSE 'New' END AS MemberType, EnumCUMembershipStatus_1.UIText AS Status, LoanApplication.DecidedOnCU, SysUserLogon_1.LastName + ' ' + SysUserLogon_1.FirstName AS CUDecisionOfficer, 'Loan' AS ProductType, CreditUnion_1.Name
FROM Member AS Member_1 INNER JOIN LoanApplication ON Member_1.LastLoanApplicationFK = LoanApplication.Id AND Member_1.Id = LoanApplication.MemberFK LEFT OUTER JOIN CreditUnion AS CreditUnion_1 ON Member_1.CreditUnionFK = CreditUnion_1.Id LEFT OUTER JOIN EnumCUMembershipStatus AS EnumCUMembershipStatus_1 ON Member_1.CuStatus = EnumCUMembershipStatus_1.Name LEFT OUTER JOIN SysUserLogon AS SysUserLogon_1 ON LoanApplication.SysUserLogonFK = SysUserLogon_1.Id AND LoanApplication.SysUserLogonCUFK = SysUserLogon_1.Id AND CreditUnion_1.Id = SysUserLogon_1.CreditUnionFK
WHERE (CreditUnion_1.Id = @CreditUnionID) AND Member_1.CuStatus = 'Approved' OR Member_1.CuStatus = 'Declined'
I am an ASP.NET Developer I am using two SQL Server databases, 2005 and sql express.I am using a select statement on an IN CONDITION One table, Table name = SOP10100 resides in a SQL Server 2005 DatabaseThe other table, Table name = ORDER resides on a SQL server express Database I am writing the following sql statement SELECT ORDERNO, CARRIER FROM SOP10100WHERE ORDERNO IN ('ORD000234','ORD000384',....) My question is how many values can I fit on this IN conditionI mean the maximum number of values (upperlimit) Is there a better way to do this Using ASP.NET ?
Hi This is madhavi am working with a project with ASP.NET Using VB.NET.. i have requirement that i have to provide the result based on search condition.... First : For Serach i have to search based on given CITY and CATEGORY....
For this i have written a StoredProcedure like: ****************************************************************************************************************** Create PROCEDURE YellowPages_Search(@city nvarchar(50),@SearchWord nvarchar(200),@Name varchar(50) OUTPUT,@CompanyName varchar(50) OUTPUT,@Address varchar(1000) OUTPUT,@PhoneNo varchar(50) OUTPUT,@MobileNo varchar(50) OUTPUT,@Fax varchar(50) OUTPUT,@Email varchar(50) OUTPUT,@WebSite varchar(50) OUTPUT)AS declare @sql nvarchar(1000)set @sql='select * from YellowPages_Userdetails where city='''+@city + '''and (category like ''%' + @SearchWord + '%'' or subcategory like ''%' + @SearchWord + '%'') ' exec(@sql) GO ************************************************************************************************************************************************************************* Now i want to extend this search condition for LOCATION and SUBCATEGORY means my search condition should include CITY , LOCATION , CATEGORY and SUBCATEGORY (here the location and subcategory may be given or may not be given) so please help me out Thanks in Advance, Madhavi
i have two tables: "Person" and "Year". "Person" can have many "Year" (one to many relation). i want a query which returns all the records from "Person" where "Year" is 2005 but exclude if there is any "Year" with 2004. how can i write that query? any help will be appreciated. i did try <code> SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004 </code> but it doesn't seem to work. i want this query to return records from Person where there is no any year with 2004 but only 2005. If a person has both 2004 and 2005 exclude that person.
i have two tables A and B with the same fields, If the id field of table B equals id field in Table A i need to update th edata for that id row.If the id field doesn;t match then i need to insert a new record in tale A for that id that is i need to perform insertion or updation into table A depending on table B dataCan anyone give me some idea how to start?
