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

Help With DATE Criteria In SELECT Statement

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

SELECT Statement With Multiple Criteria That Returns The Criterion Matched

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

Trying To Get A Count In A Select Statement

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

Using A Count W/in A Sub Select Statement

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

Help With COUNT In SELECT Statement

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

Count(*) And Select In The Same WITH Statement

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

Count # Of Results From SELECT Statement

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

TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'

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

Zero Count Values Not Appearing In SELECT Statement

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

Select Statement With Count Within Another Select

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

SQL 2012 :: Using Count Function And Case In One Select Statement

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

Cannot Construct A SELECT DISTINT COUNT... Statement In SSCE 3.1

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

SQL Server 2008 :: Incorrect Prefix Error (select Count Statement)

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

Count Same Field Mutiple Times With Different Criteria

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

How To Count Cases For Different Groups Based On Different Criteria

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

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

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

Case Statement Based On Criteria

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

UPDATE Statement With Multiple Criteria

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

Select Same Field Twice For Different Criteria

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

Select Most Recent By Date Plus Other Criteria

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

Select Based Upon Multiple Criteria

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

Looking For Field Name From Table On Select Criteria

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

Adding Optional Criteria In A Select Proc

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

Syntax To Abandon SELECT On First Occurrence Of Criteria?

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

Aggregate Multiple Columns With Different SELECT Criteria

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

Select Criteria Based On Date - Cleaner Way To Write?

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

SQL Server 2012 :: SELECT Multiple Criteria If Not Null

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

Select Record Based On Multiple Criteria (vars)

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

Select Statement Within Select Statement Makes My Query Slow....

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

SQL Select Query Need For Following Criteria. Please Help, Retrieve Records With Independent Price And Its Total Volume Per Min

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

Best Practice Question: JOIN Criteria Vs. WHERE Criteria

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

Combining 2 Select With Count And Datediff Into 1 Select. Need Help.

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







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