Need A Count Of '0', Even If Criteria Is Not Met In A Select SQL Statement
Feb 19, 2008
I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN
SELECT Code, COUNT(Code)as exprCount1a
FROM dbo.[Test]
WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code
END
The results of the statement:
Code | exprCount1a
1 2
2 4
4 1
I would like the following results:
Code | exprCount1a
1 2
2 4
3 0
4 1
Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results.
Thank you in advance
View 2 Replies
ADVERTISEMENT
Jun 25, 2004
I am trying to create a SELECT statement that would allow my users to type in a date parameter like 6/25/04. My SELECT statement would then pull all entries for that date. The problem I am running into is that it seems SQL wants the date to be parameterized as between 6/25/04 and 6/25/04 11:59:30 PM. Is there any way around that? Again I would like my users to simply enter 6/25/04 and have all entries pulled. Thanks for any help.
View 1 Replies
View Related
May 18, 2006
Hello:
I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator?
Thank you
View 2 Replies
View Related
Oct 21, 2007
When I try and execute this query I get the belwo error.
I want to get the ItemName and the Count as one column.
How can this be done?
SELECT itemName, itemName +' - '+ COUNT(itemName) AS itemNameCount FROM tblItems GROUP BY itemName
ERROR: Conversion failed when converting the nvarchar value 'Spark Plug - ' to data type int.
View 3 Replies
View Related
Mar 2, 2006
I'm trying to create a DTS package that uses CDO to send users an email. I need to create a sql query that counts two columns. I also need to create aliases for these two columns and then reference this in the sendEmail function. I have something that looks like this but I'm getting a DTS error. I think that it's because I'm not using an alias to reference Valid and Invalid. Can someone tell me how to alias the subselect columns correctly?? thanks :)
select advertiseremail, accountnumber from miamiherald where AdvertiserEmail is not null (select Valid = (select count (*) from miamiherald where validad = 1), Invalid = (select count (*) as Invalid from miamiherald where validad = 0))
View 1 Replies
View Related
Jul 20, 2005
Could someone assist with getting the count function working correctlyin this example please. I know the count function will return all rowsthat do not have null values, but in this case I want to count all therows except those with a zero sale price, (which are unsold).The table shows works offered for sale by an artist, with a positivefigure under SalePrice indicating a sale, and I want to count thenumber sold by each auction house, and sum the sale price by auctionhouse. The table is as follows:NameSalePriceAuctionDowling12000ChristiesDowling 0ChristiesDowling10000ChristiesDowling 0ChristiesDowling 0ChristiesDowling 6000SothebysDowling 0SothebysDowling 0SothebysDowling 8000SothebysDowling 0SothebysDowling 0SothebysDowling 0SothebysWhen I run this query:SELECT MyTable.Name, Count(MyTable.Name) AS [Number],Sum(MyTable.SalePrice) AS TotalSales, MyTable.AuctionFROM MyTableGROUP BY MyTable.Name, MyTable.AuctionHAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));The results are:NameNumberTotalSalesAuctionDowling 5 22000 ChristiesDowling 7 14000 SothebysThe TotalSales is correct, but the Number (Count) is incorrect, as therows with zero were also included. The results should be:NameNumberTotalSalesAuctionDowling 2 22000 ChristiesDowling 2 14000 SothebysHow do I prevent the unsolds (zeros) being counted?Thanks in advance,John Furphy
View 2 Replies
View Related
Apr 22, 2008
Hi,
I have a query:
-- main select
WITH Orders AS
(
SELECT
ROW_Number() OVER(MyDate ASC) RowNo,
** rest o the query ***
)
SELECT *
FROM Orders
WHERE RowNo BETWEEN 100 AND 200
ORDER BY RowNo
--count of records
DECLARE @COUNT INT
SELECT @COUNT = COUNT(*)
FROM ** the same query as above ***
RETURN @COUNT
In this case it can happen that when counting records there will be different number of records that it was at time of paging. Also server has to execute this query twice and the query is quite complicated means that takes time.
Is there any better way to get number of rows in the same part of query with paging ?
Thanks for help
Przemo
View 12 Replies
View Related
Jun 6, 2008
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)
Looking to run a query that will retrieve the number of results returned from a select statement...
Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:
SELECT * FROM Software WHERE LicensesID = 2
Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?
Thanks all!
View 2 Replies
View Related
Jul 23, 2005
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View 18 Replies
View Related
Sep 17, 2007
Hi all
I have the following tables:
Code Snippet
CREATE TABLE #Lkp_Circle
(
ID INT ,
Abbreviation varchar(50)
)
GO
CREATE TABLE #Lkp_OtherCircles
(
Circle varchar(50)
)
GO
CREATE TABLE #Tbl_User
(
ID INT,
Name VARCHAR(50),
IsActive bit
)
GO
CREATE TABLE #Tbl_UserDetails
(
AssociateID INT,
CircleID INT
)
GO
INSERT INTO #Lkp_Circle VALUES (1,'C1')
INSERT INTO #Lkp_Circle VALUES (2,'C2')
INSERT INTO #Lkp_Circle VALUES (3,'C3')
INSERT INTO #Lkp_Circle VALUES (4,'C4')
INSERT INTO #Lkp_Circle VALUES (5,'C5')
INSERT INTO #Lkp_Circle VALUES (6,'C6')
INSERT INTO #Lkp_Circle VALUES (7,'C7')
GO
INSERT INTO #Lkp_OtherCircles VALUES ('C3')
INSERT INTO #Lkp_OtherCircles VALUES ('C4')
INSERT INTO #Lkp_OtherCircles VALUES ('C5')
INSERT INTO #Lkp_OtherCircles VALUES ('C6')
GO
INSERT INTO #Tbl_User VALUES ( 101,'U 1','True')
INSERT INTO #Tbl_User VALUES ( 102,'U 2','True')
INSERT INTO #Tbl_User VALUES ( 103,'U 3','True')
INSERT INTO #Tbl_User VALUES ( 104,'U 4','True')
INSERT INTO #Tbl_User VALUES ( 105,'U 5','True')
GO
INSERT INTO #Tbl_UserDetails VALUES(101,3)
INSERT INTO #Tbl_UserDetails VALUES(102,4)
INSERT INTO #Tbl_UserDetails VALUES(103,5)
INSERT INTO #Tbl_UserDetails VALUES(104,5)
INSERT INTO #Tbl_UserDetails VALUES(105,3)
GO
SELECT ISNULL(Circle,'Total') Circle, ISNULL(COUNT([HeadCount]),SUM(1)) AS [Total]
FROM
(
SELECT DISTINCT 'Circle' = CASE
WHEN #Lkp_Circle.Abbreviation IN (SELECT Circle FROM #Lkp_OtherCircles) THEN #Lkp_Circle.Abbreviation
WHEN #Lkp_Circle.Abbreviation NOT IN (SELECT Circle FROM #Lkp_OtherCircles) THEN 'Others'
ELSE 'Total' END,ISNULL(#Tbl_UserDetails.AssociateID,0) AS 'HeadCount'
FROM #Tbl_User INNER JOIN #Tbl_UserDetails ON #Tbl_User.ID = #Tbl_UserDetails.AssociateID INNER JOIN
#Lkp_Circle ON #Tbl_UserDetails.CircleID = #Lkp_Circle.ID
WHERE #Tbl_User.IsActive='True' AND #Tbl_User.ID>0 AND #Tbl_UserDetails.AssociateID>0
) AS PivotTable
GROUP BY Circle
WITH Cube
DROP TABLE #Tbl_User,#Tbl_UserDetails,#Lkp_Circle,#Lkp_OtherCircles
----EXPECTED RESULT
--Circle HeadCount
--C3 2
--C4 1
--C5 2
--C6 0
--Others 0
--Total 5
--
----ACTUAL RESULT
--Circle HeadCount
--C3 2
--C4 1
--C5 2
--Total 5
The criteria for Others is that those circles which are not part of #Lkp_OtherCircles i.e. C1,C2,C3 and C7 clubbed together. I have tried checking for the condition ISNULL when for that circle there is no user but the end result is same. Can someone tell me where I am going wrong and how to correct it?
View 9 Replies
View Related
Aug 23, 2013
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2
[code]....
View 1 Replies
View Related
Jul 9, 2014
I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.
Right now I am achieving this by the following case statement
SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl
But can I do this with just one select statement?
View 2 Replies
View Related
Oct 4, 2007
Hi everyone,
sorry to b a pest again! Before I made the decision to change the DB used in my app from SQL Server Express to SSCE, I had no problems with constructing a SELECT statement as laid out in the Title.
Basically, I have 2 tables with a one-many relationship between them. In the Parent table, I had a SQL Statement as follows:
SELECT DeptID, DeptName,
(SELECT DISTINCT COUNT(Active) FROM Documents WHERE (Documents.DeptID = Dept.DeptID) AND
(Documents.Active = 'True') AS CountOfActive
FROM Dept
Now in SSCE 3.1, I get an "Unable to parse query" error message when I construct the same SQL statement in my dataset designer.
Any thoughts on how I may solve this?
Much thanx!
Shalan
View 15 Replies
View Related
Oct 7, 2015
Naming convention and what am I doing wrong here:
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:
Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.
View 9 Replies
View Related
May 12, 2006
Is it possible to count the same field with different criteria. It would be something like this.
car_table
car_id
car_name
car_brand
So you would execute a statement which would count(car_brand) with two different criteria.
I am not sure if this is possible or if there is another way to approach it.
View 1 Replies
View Related
Apr 25, 2008
Hello,
I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)
Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)
MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null
BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')
12-Jan
19-Jan
26-Jan
2-Feb
9-Feb
16-Feb
MTS
New Cases Received
85
84
79
98
79
95
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
44
29
26
35
55
54
CreditB
6
12
9
5
7
13
CreditS
-
-
-
-
3
-
CreditP
10
11
11
24
17
7
MTS Subtotal
140
125
110
144
151
150
BNB
New Cases Received
12
13
14
14
6
11
Sales
-
-
-
-
-
-
SalesD
-
-
-
-
-
-
CreditR
12
11
12
10
5
9
CreditB
8
13
9
17
16
6
CreditS
-
-
2
-
-
-
CreditP
1
1
1
1
4
3
BNB Subtotal
21
25
24
28
26
19
Total
New Cases Received
97
97
93
112
85
106
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
56
40
38
45
60
63
CreditB
14
25
18
22
23
19
CreditS
-
-
2
-
3
-
CreditP
11
12
12
25
21
10
Grand Total
161
150
134
172
177
169
This is just a very brief bit of code
SELECT MTS_new_cases_sales, mts_new_cases_salesd €¦€¦.
FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...
Can you please show me how to accomplish this?
Thank you in advance for your effort,
Rhonda
View 2 Replies
View Related
Aug 6, 2006
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
View 5 Replies
View Related
Sep 23, 2013
I have to produce a report, but not sure of the best way to get the required results
Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]
E.g.
10 Fdmsaccountno in Jan
Those 10 Fdmsaccountno vary in [Retail_amount]
I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>
My query is
SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'
View 5 Replies
View Related
Apr 5, 2006
I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.
I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:
UPDATE xLegHdr
SET SMiles =
IF Dist<250 THEN Round(Dist-25)*1.15)
ELSE Round(Dist-40)*1.15)
END IF
Any ideas?
View 1 Replies
View Related
Mar 11, 2005
I need a little insight on how to select the same field from the same table, but for different criteria.
here are example tables...
Categories
CATSUBCATNAME
10MainTitle
11SubTitle #1
12SubTitle #2
20Section
21Section #1
DataTable
CATSUBCATINFO
11Detail Information for subtitle #1
12Detail information for subtitle #2
desired result would be:
MainTitle, SubTitle #1, Detail Information for subtitle #1
MainTitle, SubTitle #2, Detail Information for subtitle #2
Select c1.Name, c2.Name, d.info
from DataTable d, Categories c1, Categories c2
where c1.CAT = d.CAT
and c2.CAT = d.CAT
and c2.SUBCAT = d.SUBCAT
View 1 Replies
View Related
Feb 1, 2015
I have the following query that should return the most recent FormNote entry for a work order where the note begins with "KPI". However if someone decides to a more recent note, it selects that one, even if it doesn't begin with "KPI".
I would like it to return the most recent record that ALSO begins with "KPI". How can I correct this?
Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join
(select ID, WorksOrder,[CreationDate], TextEntry
from
(
select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,*
from FormNotes
)orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where TextEntry like 'KPI%'
Sample results below, see line 5 - this record should not have been selected as there is a record beginning with "KPI" for that work order, but it is dated before this one.
worknumber date_created itemcode Notes
-------------------- ----------------------- -------------------- -----------------------------------------------------------------------------------
HU-DN-004385 2014-07-21 16:15:00 4261 KPI Hyd oil leak repaired
HU-DN-004707 2014-08-06 11:39:00 8005 KPI Valve replaced on day 2.
HU-DN-004889 2014-08-19 15:44:00 9275A KPI Repaired in 2 days - m/c working
HU-DN-004923 2014-08-22 14:23:00 4261 KPI New tracks fitted
HU-DN-005162 2014-09-12 15:04:00 9360A Mechlock key delivered to site - m/c working
HU-DN-005170 2014-09-15 12:07:00 2130A KPI 28.10.14 Metlock fitted
View 5 Replies
View Related
Sep 13, 2006
Hi
I would like to get records from a table and present a result set based upon the search fields
the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,
they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.
Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?
Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage
adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg
View 12 Replies
View Related
Sep 19, 2006
Hari writes "Sub:- looking for Field name from table on select criteria
dear friends
I have an table [assosories] which have 100 fields. initial 10 fields have some date, varchar, int types rest 90 are bool type.
for a single row I need to know the field name which have true vaules in rest 90 fields
I need to insert this field name into another table as a row.
Thanks
HARI"
View 2 Replies
View Related
Nov 26, 2004
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = @VENDOR
and Sitecode = @SITECODE
and PackageType = @PACKAGETYPE
)HB on HA.VendorPackageId = HB.VendorPackageId
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_1
and ValidItemType = @VALIDITEMTYPE_1
and ItemValue = @ITEMVALUE_1
)
Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in.
Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_1
and ValidItemType = @VALIDITEMTYPE_1
and ItemValue = @ITEMVALUE_1
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_2
and ValidItemType = @VALIDITEMTYPE_2
and ItemValue = @ITEMVALUE_2
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @DESTINATION
and LengthOfStay = @LENGTHOFSTAY
and Ages = @AGE
and ComponentType = @COMPONENTTYPE_3
and ValidItemType = @VALIDITEMTYPE_3
and ItemValue = @ITEMVALUE_3
)
Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''
Thanks for your help in advance.
View 7 Replies
View Related
Nov 6, 2013
Have a need to scan a large table to see if a set of criteria have ever been met.
If/when the scan hits its first record meeting the criteria, the scan can be abandoned.
Is there some syntax/option that accomplishes this?
Right now, I am doing a SELECT with criteria against the table and the @@ROWCOUNT gives me a zero, or non-zero value.
But that methodology means that the SELECT has to execute against the entirety of the table.
I'd like to abandon the SELECT as soon as it detects a first record meeting the criteria.
View 3 Replies
View Related
Sep 24, 2007
Let me start with saying thanks to all of you who have helped me (I'm a SQL newbee after doing OO for the past 12+ years)
I need to do several aggregates on multiple columns, with each column having different SELECT Criteria.
Sample Data:
Dept Project Cost CostFlag Schedule ScheduleFlag
D1 D1P1 495 1 135 3
D1 D1P2 960 2 70 2
D1 D1P3 1375 3 105 2
D1 D1P4 1050 2 160 3
D1 D1P5 1890 3 40 1
D2 D2P1 650 1 155 3
D2 D2P2 890 2 125 2
D2 D2P3 1235 3 85 1
D2 D2P4 430 1 140 3
D3 D3P1 1960 3 45 1
D3 D3P2 1490 3 85 1
D3 D3P3 1025 2 135 3
D3 D3P4 615 1 100 2
D3 D3P5 270 1 70 1
D3 D3P6 815 2 155 3
I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:
SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......
WHERE CostFlag = @InputParameter
GROUP BY Dept, Project
The code above works great - but only for a single column. I need to return a dataset like this:
Dept Project Cost_Mean Cost_Range
D1 D1P1 495 135
D1 D1P2 960 70
D1 D1P3 1375 105
I need to return a dataset like this:
Dept Project Cost_Mean Cost_Range Schedule_Mean Schedule_Range
D1 D1P1 495 135 100 28
D1 D1P2 960 70 42 12
D1 D1P3 1375 105 91 38
I also have working code calculate the MEDIAN (what a pain that was, thank god I found a code example to get me going on the MEDIAN)
Thanks!
View 7 Replies
View Related
Sep 7, 2005
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT
I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT
Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).
Any suggestions on the best way to write this would be valued.
View 2 Replies
View Related
Sep 2, 2014
I have a dataset where i want to select the records that matches my input values. But i only want to try macthing a field in my dataset aginst the input value, if the dataset value is not NULL.
I always submit all 4 input values.
@Tyreid, @CarId,@RegionId,@CarAgeGroup
So for the first record in the dataset i get a succesfull output if my input values matches RegionId and CarAgeGroup.
I cant figure out how to create the SQl script for this SELECT?
My dataset
TyreIdCarIdRegionIdCarAgeGroup
NULLNULL1084 2
65351084 1
5351084 1
NULL411085 NULL
120NULLNULL NULL
NULLNULL1084 2
65NULL1084 NULL
View 9 Replies
View Related
Apr 17, 2008
Hi! I'm new to SQL and have a question...
I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.
Basically my SELECT statement looks something like this, then is followed by an If... Else statement
SELECT * FROM TableName
WHERE LastName = varLastName
AND FirstName = varFirstName
AND Address = varAddress
If RecordSet.EOF = True Then
'Item Not Found, add new record
'code to add new record......
Else
'Item Found, do nothing
End If
RecordSet.Update
RecordSet.Close
Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??
View 5 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Jul 17, 2006
Time Price Volume
090048 510 6749
090122 510 2101
090135 510 1000
090204 505 2840
090213 505 220
090222 505 1260
090232 505 850
090242 505 200
090253 510 1200
090313 510 570
090343 510 250
090353 510 160
Criteria
Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
Result of the above query: -
Time Price Volume
0900 510 6749
0901 510 1000
0901 510 2101
0902 505 200
0902 505 220
0902 505 850
0902 505 1260
0902 505 2840
0902 510 1200
0903 510 160
0903 510 250
0903 510 570
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
0901 510 1000
+
0901 510 2101
=
0901 510 3101 <- I NEED THIS
Can any one advice or give me tips over this. Please
View 3 Replies
View Related
May 24, 2004
For example, consider the following queries:
DECLARE @SomeParam INT
SET @SomeParam = 44
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
Thanks.
View 3 Replies
View Related
Jun 21, 2006
I have created two select clauses for counting weekdays. Is there a way to combine the two select together? I would like 1 table with two columns:
Jobs Complete Jobs completed within 5 days
10 5
-------------------------------------------------------------------------------------------------
SELECT COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Jobs Completed within 5 days'
FROM dbo.Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ') AND
(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2 <= 5)
---------------------------------------------------------------------------------------
Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed'
From Project
WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')
View 9 Replies
View Related