Display Minimum Of Two Values
Apr 13, 2015I want the minimum of two values to be display like below without using the case
MIN(3.00,4.00)
output:
3.00
ADVERTISEMENT
Minimum/Maximum Of Two Values
Dec 6, 2004Hallo Cracks,
what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.
What I try to do:
I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):
Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)
like this sqlstatement:
SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)
instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.
thanks in advance and kind regards :-)
Cappu
Select Minimum Between Two Values
Jul 27, 2006Hello,I need to select the minimum between the result of a function and anumber, but i can't find a smart way. By now I'm doing like thefollowing, but I think is very expensive because it evaluates thefunction twice:select case when (myfunction())<100 then (myfunction()) else 100 endAny idea is appreciated.Thank youRegards--elimina carraro per rispondere via email
View 4 Replies View RelatedGet Minimum And Maximum Values From Field?
Sep 16, 2012I am trying to get the minimum and maximum values from a field in SQL Server 2008 Express, but I cannot even get started because I keep getting this error that I cannot figure out.
View 6 Replies View RelatedTransact SQL :: Include Minimum Item Values Only
Aug 3, 2015My sql gives me the results as shown in #TEMTAB Table.some times Mitem_id is repeated for the same Group_id,SGroup_id with different item_id
ex: SQ322,SQ323,SQ324,SQ325 are repeated here.
CREATE TABLE #TEMTAB
(Group_id int, SGroup_id int, item_id int, Mitem_id varchar(10)
,YN varchar(2),value varchar(8),reason varchar(20))
[code]...
T-SQL (SS2K8) :: Getting Minimum And Maximum Values In A Large Table
May 23, 2014Table definition:
Create table code (
id identity(1,1)
code
parentcode
internalreference)
There are other columns but I have omitted them for clarity.
The clustered index is on the ID.
There are indexes on the code, parentcode and internalreference columns.
The problem is the table stores a parentcode with an internalreference and around 2000 codes which are children of the parentcode. I realise the table is very badly designed, but the company love orms!!
Example:
ID| Code| ParentCode| InternalReference|
1 | M111| NULL | 1|
2 | AAA | M111 | 2|
3 | .... | .... | ....|
4 | AAB | M111 | 2000|
5 | M222 | NULL | 2001|
6 | ZZZ | M222 | 2002|
7 | .... | .... | .... |
8 | ZZA | M222 | 4000|
The table currently holds around 300 millions rows.
The application does the following two queries to find the first internalreference of a code and the last internal refernce of a code:
--Find first internalrefernce
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference
-- Find last ineternalreference
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference DESC
These queries are running for a very long time, only because of the sort. If I run the query without the sort, then they return the results instantly, but obviously this doesn't find the first and last internalreference for a parentCode.
I realize the best way to fix this is to redesign the table, but I cannot do that at this time.
Is there a better way to do this so that two queries which individually run very slowly, can be combined into one that is more efficient?
T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B
May 6, 2014I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
Display Of Variable Values
Sep 23, 2004Hello Experts:
I want to access the values coming from the table but my code is just printing the varibale name.
e.g. It is printing right now
@lev0
@lev1
@lev2
@lev3
@lev4
but infact in variable @lev1, the actual value from table is [prodcut]. the value in varibale @lev1 is [category].
Can you pl. help me?
--- My code
Declare
@lev0 varchar(50),
@lev1 varchar(50),
@lev2 varchar(50),
@lev3 varchar(50),
@lev4 varchar(50),
@stmnt varchar(2000),
@counter int,
@st varchar(50)
Select @lev0=Dimnsion, @Lev1= Lev1, @Lev2= Lev2, @Lev3= Lev3, @lev4= lev4 from dbo.Lookup where dimnsion = '[Product]'
Set @counter = 0
while @counter < 5
begin
set @st = '@lev'+cast(@counter as varchar(50))
print @st
set @counter= @counter+1
end
-------
Thanks
Pick And Display TOP 3 Values
May 18, 2014I have the below data with two columns (Posting Date and Pond Crop, construct an SQL where i will pick and display only the TOP 3 Posting date of EACH Pond Crop. I only attached less data but in reality there are lots of Ponds and posting date in this table.
Table Name : Weekly Harvest
Fields:
1. Posting Date
2. PondCrop
Desired Output:
Posting Date PondCrop
2011-12-12 00:00:00.00001PA01-15
2011-12-19 00:00:00.00001PA01-15
2011-12-26 00:00:00.00001PA01-15
2012-03-19 00:00:00.00001PA01-16
2012-03-20 00:00:00.00001PA01-16
2012-03-26 00:00:00.00001PA01-16
Raw Data
Posting Date PondCrop
2011-11-21 00:00:00.00001PA01-15
2011-11-28 00:00:00.00001PA01-15
2011-12-02 00:00:00.00001PA01-15
2011-12-05 00:00:00.00001PA01-15
[Code] ....
Row Values Display In Columns
Jun 5, 2015One Table have 2 Columns.
1.Order_number
2.PSON NUmber
In Table Contains 6 rows with 2 order_numbers and different PSON Number.I want Order Number,PSON1,PSON2,PSON3,PSON4 and its Values
Ex:--Order_number   PSON NUmber
       1           ONT10
       1           ONT11
       1          ONT12
       2          ONT13
        2          ONT14
I want  data :-    Order_number   PSON1      PSON2     PSON3
                 1           ONT10    ONT11    ONT12
                  2            ONT13   ONT14    Null
Display Null Values
Jul 17, 2007Hello,
I'm facing a problem in my reporting.
I have a Customer table where is record various events like CustomerEventId, DateTime, StatusId, StatusTime, GroupId, ...
I also have a status table (Id, Description) and a group table (Id, Description).
I want to create a report where for a selected date range (From ... To ...) i can see (grouped by date) all status's the customer
went in. The possible status are :
Id Description
-------------------------------------
1 status 1
2 status 2
3 status 3
4 status 4
My query looks something like this :
SELECT CustomerEventId, DateTime, CONVERT(varchar, DateTime), 103) AS DATEVAL, StatusId,
status.description as StatusDescription, StatusTime, GroupId, group.Description as GroupDescription
From Customers inner join status on customers.StatusId = status.id
inner join group on customers.GroupId = group.id
Group By CustomerEventId, DateTime, StatusId, status.description, StatusTime, GroupId, group.Description
My reports has 3 parameters (From date, To date, Group)
In my report i have a table with two groups : GroupByDate (grouped on DATEVAL) and GroupByStatus
now my problem : let's say i have values for statusid 1,2 and 4
then my report will only display those 3 status.
How can i display the status where there is no data for :
now it shows :
DATEVAL Occurrences Time
01/07/2007
Status 1 15 125
Status 2 25 366
Status 4 8 66
I would like it to show:
DATEVAL Occurrences Time
01/07/2007
Status 1 15 125
Status 2 25 366
Status 3 0 0
Status 4 8 66
Anybody (i hope i have provide enough details ...)
Vinnie
Display Null Values
Apr 17, 2007Hi,
I have the following problem.
I have created different tables in my database with descriptions in from other tables (example : a table named errors with errorid and errordescription as fields in it)
In a report i want to display the total nr of errors during a certain period for a certain department.
i have created that report with a list (by department). in this list i have a table where i group my errors.
until here all ok. when i display my report it shows all the errors.
but what it doesn't show is all the errors who have value 0 (or errors that didn't occur during that period i defined)
how can i display all my errors, even if they did not occur (0 times)
Greetings
vinnie
Query To Display Max Values For Each ID
Jun 5, 2015I have table that contains below data
Date                             ID           Â
Message
2015-05-29 7:00:00     AOOze           abc
2015-05-29 7:05:00     AOOze           start
2015-05-29 7:10:00     AOOze           pqy
2015-05-29 7:15:00     AOOze           stop
2015-05-29 7:20:00     AOOze           lmn    Â
[code]....
and so on following the series for every set of different ID with 5 entries.I need to Find Maximum interval time for each ID and for condition in given message (between Start and Stop)For example, in above table
-For ID AOOze, in message "start" is logged at 7:05 and stop and 7:15, so interval is 10 mins
-For ID LaOze, in message "start" is logged at 7:30 and stop and 7:45, so interval is 15 mins
I am looking for a sql query that will return in below format
IDÂ Â Â Â MAX interval
AOOze  15
LaOze  10
How To Display The Values From Two Different Columns (was RS Question)
Jan 3, 2008Does somebody know how do you get a column in Reporting Services to display the values from two different columns? I don't mean a concat, but the result of the two columns.
This has been a headache for 2 days now.
Thanks!!
T-SQL (SS2K8) :: Display Values Up To 1 Decimal Without Function?
Sep 11, 2014I am having values as below:
99.87
99.96
8.67
And my output should be as:
99.8
99.9
8.6
How can I do this
Display Custom Values In Select Statement
May 8, 2008Hi,
I have a table called emp, having 2 field name & sex
values are:
name sex
a 1
b 2
c 1
now i want to display the values in above table as like below...
name sex
a Male
b Female
c Male
How to do that...?
NUL L Date Values In Report Builder Display As...
Apr 13, 2007Null date values within a table in SQL Server are displays as (12/30/1899) by Report Builder. Does anyone know how to display the null value as blank?
Thanks in advance
Display Null Values As 0 On Line Chart
May 16, 2008I have a report I'm writing and have got a problem that has stumped me.
The data the report is based in is in this format:
Date Type
1/1/08 A
1/3/08 B
3/1/08 C
3/5/08 B
3/10/08 A
3/12/08 C
3/20/08 A
Management wants a report showing a line chart that summarizes the data by Month and by Type. So, there are 2 series depending on the Type and data values are based on the count of each Type. So, in the example above the x-axis group would be month, the values would be count(Type) and there would be 2 series €“ Series 1. A, B and Series 2. C
I€™ve defined the x-axis to be Month(Fields!DateOccured.Value). However, they want the x-axis to show every month for the entire year not just what is in the database. So, I defined the x-axis to have a scale of 1 to 12 and the major interval is set to 1. This displays 1 through 12 on the x-axis.
The problem is when there is no data (null) for a particular month. Instead of showing 0, the line chart does not plot anything and the line is drawn to the next displayed point. So, for example on the data above the line chart would plot:
January
Series 1 €“ Qty. 2 Series 2 €“ Qty. 0
February
Null
March
Series 1 €“ Qty. 3 Series 2 €“ Qty. 2
The line chart would draw a line from January to March skipping February. I need to display the null values as 0 and not null indicating no occurrences for the month rather than no data present.
Thanks!
Display Flag For OrderType Cycle Values
Aug 9, 2015I have a table with EquipmentNumber, OrderType, BreakdownIndicator and RowID columns and below are the values for one equipmentnumber,
EuipmentNumber OrderType BreakdownIndicator RowId Flag
145812 PM04 1 Yes
145812 PM07 2 Yes
145812 PM07 3 Yes
145812 PM04 4 Yes
145812 PM07 5
145812 PM07 X 6
 145812 PM07 7
 145812 PM04 8 Yes
145812 PM04 9 Yes
145812 PM04 10 Yes
I need to display the 'Flag' column in the result set as mentioned above to identify the cycle values without breakdown indicator values. Here is the logic for this,
1. Need to set the flag 'Yes' for the cycle values PM04 to PM04 in the OrderType and if there is any breakdownindicator value 'X' then we can not consider that cycle for Flag.
To Display Two Values In Combobox But In Database Will Store Only One Value Out Of That
Jan 24, 2008
hi friends
i am using visual studio 2005. i need one combo box which will display two values. one is "Designation" i.e.("clerk,accountant,manager,officer,shopkeeper) and another "Designation ID" i.e.(1,2,3,4,5 and respectively) i made table of "Designation Master" in sql server 2000 including field of "Designation ID, Designation". manually i am inserting id and designation in that table.
what i want exactly i have one customer form which contain a "combobox" for "Designation" column i achieved "Designation ID" and "Designation" in that combo box also but when i will go for save i want that i will store only "Designation ID" i don't want to store "Designation" in my "Designation" column which is in "Customer table" i am attaching code which store "Designation ID" and "Designation" in customer table but i want only "Designation ID" only
bold line which is difficult for me to inserting only "designationId" in customer table.
Inventory_Master.cntData.Open()
cmdstr = "Select DesigID,Designation from Designation_Master"
cmd = New SqlCommand(cmdstr, Inventory_Master.cntData)
cdrCustomer = cmd.ExecuteReader()
If cdrCustomer.HasRows = True Then
While cdrCustomer.Read()
cmddesig = cdrCustomer.Item(0).ToString.PadLeft(1) + cdrCustomer.Item(1).ToString.PadLeft(25)
cbodesignation.Items.Add(cmddesig)
End While
Else
End If
Inventory_Master.cntData.Close()
Inventory_Master.cntData.Open()
cmd = New SqlCommand("insert CUSTOMER(Membership_No,Member_Initial,Member_Name,Member_Birthdate,Gender,Res_Telno,Mobile_No,Res_Addr1,Res_Addr2,Res_City,Res_Pincode,Landmark,Company_Name,Comp_Addr1,Comp_Addr2,Comp_Telno,Comp_City,Comp_Pincode,Designation,Credit_Limit,Income_Mthly,Email_ID,Note) values (@Membership_No,@Member_Initial,@Member_Name,@Member_Birthdate,@Gender,@Res_Telno,@Mobile_No,@Res_Addr1,@Res_Addr2,@Res_City,@Res_Pincode,@Landmark,@Company_Name,@Comp_Addr1,@Comp_Addr2,@Comp_Telno,@Comp_City,@Comp_Pincode,@Designation,Convert(money,@creditlimit),@Income_Mthly,@Email_ID,@Note)", Inventory_Master.cntData)
cmd.Parameters.AddWithValue("@Membership_No", txtmebno.Text)
cmd.Parameters.AddWithValue("@Member_Initial", cbomebname.Text)
cmd.Parameters.AddWithValue("@Member_Name", txtmebname.Text)
cmd.Parameters.AddWithValue("@Member_Birthdate", dtime.Value)
cmd.Parameters.AddWithValue("@Gender", cbogender.Text)
cmd.Parameters.AddWithValue("@Res_Telno", txttelno.Text)
cmd.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text)
cmd.Parameters.AddWithValue("@Res_Addr1", txtresaddr1.Text)
cmd.Parameters.AddWithValue("@Res_Addr2", txtresaddr2.Text)
cmd.Parameters.AddWithValue("@Res_City", txtrescity.Text)
cmd.Parameters.AddWithValue("@Res_Pincode", txtrespin.Text)
cmd.Parameters.AddWithValue("@Landmark", txtlandmark.Text)
cmd.Parameters.AddWithValue("@Company_Name", txtcompname.Text)
cmd.Parameters.AddWithValue("@Comp_Addr1", txtcompaddr1.Text)
cmd.Parameters.AddWithValue("@Comp_Addr2", txtcompaddr2.Text)
cmd.Parameters.AddWithValue("@Comp_Telno", txtcomptelno.Text)
cmd.Parameters.AddWithValue("@Comp_City", txtcompcity.Text)
cmd.Parameters.AddWithValue("@Comp_Pincode", txtcomppin.Text)
cmd.Parameters.AddWithValue("@Designation", cbodesignation.Text.PadLeft(1, CChar(CStr(1)))) [ help me for inserting designation in this line]
cmd.Parameters.AddWithValue("@creditlimit", txtcreditlimit.Text)
cmd.Parameters.AddWithValue("@Income_Mthly", txtincomemthly.Text)
cmd.Parameters.AddWithValue("@Email_ID", txtemailid.Text)
cmd.Parameters.AddWithValue("@Note", rtxtNote.Text)
cmd.ExecuteNonQuery()
Inventory_Master.cntData.Close()
MsgBox("RECORD SAVED")
SQL Server 2008 :: Display All Months Even If Values Are NULL
May 13, 2015I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
[code]...
T-SQL (SS2K8) :: Query To Display Different Values From Same Column In Same Table?
Sep 18, 2015I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:
OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C
So the logic is as follows:
I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.
Not sure how to create my Select here since the values are in the same table
Reporting Services :: Display Only Certain Values In Pie And Graph Chart
Sep 3, 2015I have a table that contains some data.Â
LabelName
LabelValue
TotalForRetire
10337
dummy1
0
TotalForRelatives
5850
dummy2
0
TotalForDisability
7337
TotalForOrange
23
I have a pie chart and a bar chart.
In the pie chart I want to plot only TotalForRetire and TotalForDisability and TotalForOrange
In the bar chart I want to plot only TotalForRetire,dummy1,TotalForRelatives.
Also, here I want the horizontal axis not to show the label  for dummy1 as the value is 0. How can i do that for each chart?
Reporting Services :: Display Values By Color Group
May 25, 2015I am new to SSRS. I created a reporting services with 3 groups. I would like to know how to create different colors for each group so that all my values displayed by color group ?.
View 4 Replies View RelatedDisplay Column Names And Its Values Programatically From The Dataset
Apr 18, 2008Hi.
In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:
@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM
PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'
EXEC(@sql)
So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?
Thanks
Reporting Services :: Display Only Two Values Before Decimal Points
Sep 24, 2015In my report I am having one calculation filed, in that field I want to display only two values before decimal point.
For example, if I am having calculation field value as 3456.54, I need to display as 35.54 or 34.54.
Get All The Matching Values In ColB And Display It Against ColA As Comma Seperated In The Same Row
Nov 6, 2007
Hi all sql gurus,
I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name .
The tables below might give a better idea
current scenario:
Product Name SubmissionID columnC Column D
AAA 123
AAA 456
BBB 111
ccc 121
AAA 789
Expected result:
Product Name SubmissionID columnC Column D
AAA 123,456,789
BBB 111
CCC 121
Any suggestions/pointers would be highly appreciated.
Power Pivot :: Selected Report Filter Values Display In A Cell
Oct 28, 2015I have a power pivot with 2 multi valued report filters  student_branch & blood_group. These report filters are used to fetch the data set that contain below result set
student_branch        blood_group      count
Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.
How To Display Multiple Values On Report Page From A Multi-value Report Param
Nov 5, 2007How do I display multiple parameter values on report page from a multi-value report parameter. For example, I have a report parameter where users can select multiple attendance codes and I want them displayed at the top of the report after it's run.
Currently, only the first value is showing on the report.
Thanks.
Transact SQL :: Query To Display Avg Values For Each Timestamp And Count Of Timestamp
Jun 23, 2015date       time        s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date       time        s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400                 3
6/9/2015 11:56:26 TestSite 900                  2
6/12/2015 12:56:26 TestSite 700                  1
Get The Minimum Value
Sep 17, 2007In my table I have values like (2,3). I want to get the minimum value from the column
View 12 Replies View RelatedSet The Minimum Permissions
Apr 26, 2007Hi All,How do I set just enough permissions on a database so that a developer cancreate new objects and modify them without giving him administratorprivilege. At the moment the db objects are dbo and execute permissions aregranted to indevidual through a schema.GRANT EXECUTE ON [dbo].[My_Prcsedure] TO [MySchema]Thanks
View 2 Replies View RelatedMinimum Value Of 2 Fields
Dec 20, 2004i need to create a dynamic field, which returns the minimum value of 2 fields.
A B C(i need)
500 600 500
350 250 250
825 950 825
select A,B,min(A,B) as C from MyTable
i know this query is wrong,
pls help me to solve this problem
RGDS
BAHA