Conditional Column Selection
Sep 13, 2007I have one table with 6 columns (Entrance1,Exit1,Entrance2,Exit2,Entrance3,Exit3).
This columns can take null values.
I want to select just the last one of this columns that is not null.
ADVERTISEMENT
Complicated Conditional Selection.
Aug 23, 2007Befor question let me give you the tables, so simple:
tblProduct tblProductLang
ID ProductName Cost ID ProductName Lang
------------------------------------- -------------------------------------
1 Pencil 1 1 Bleistift gr-GR
2 Paper 1 2 Papier gr-GR
3 Button 2 3 Taste gr-GR
4 Mouse 15 4 Maus gr-GR
5 Monitor 45 4 Souris fr-FR
Ok. We have two table,
tblProduct: Keeps the product name and cost
tblProductLang: Keeps the product name in different language and short name of the language.
We are about the make a language file to our website. Default language is english. So no problem with the product name, user can easily understand. If yuser selects german , english names of the product will be nothing for him/her. We make a language table for products so when user changes language, he/she will get product name of the preffered language, if there is no equivalent name of that product, result will be default name. Let me give you a sample output:
If user wants gr-GR language , result will be: If user wants fr-FR language , result will be:
ID ProductName Cost ID ProductName Cost
------------------------------------- -------------------------------------
1 Bleistift 1 1 Pencil 1
2 Papier 1 2 Paper 1
3 Taste 2 3 Button 2
4 Maus 15 4 Souris 15
5 Monitor 45 5 Monitor 45
As you can see ID 1,2,3,4 came from tblProductLang as German, but ID 5 has no equivalent so it came from tblProduct as English.(Same thing for the french.) So question is how can i do that?
T-SQL (SS2K8) :: Selection With Conditional Insert Union
Apr 4, 2014In this selection when there is particular segment found in the selection then need to add 2 records per segment otherwise just selected results
--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))
[Code] ....
---Selection results
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'
--2 records
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com'
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'
--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
union
[Code] .....
Urgent Help! - How To Get The Column Selection?
Oct 4, 2004suppose i have a table penalties with
ticket_number, ticket_start_date datetime, ticket_end_date datetime
There are many records of same ticket_number.example:
ticket_number ticket_start_date ticket_end_date
75 2/1/03 3/2/03
75 2/4/03 5/4/03
34 2/1/03 3/3/04
34 3/4/04 3/3/05
I want to write a query which will:
1. select the ticket_number which is not active as of today
in case of ticket_number 75 it is closed so I want to print it in report
in case of 34 it was closed but now opened, so i don't want in my report.
Could anybody suggest a query how to do it?
I was trying with stored procedure and cursor :
-------------------------------------
CREATE PROCEDURE simpleTest
AS
declare @Today datetime
set @Today = '10/04/2004'
declare @colA varchar(38)
declare @colB datetime
declare @colC datetime
declare @myCursor CURSOR
set @myCursor = CURSOR FAST_FORWARD
for
select distinct ticket_number, ticket_start_date, ticket_end_date
from penalties
where ticket_end_date <= @Today
open @myCursor
fetch next from @myCursor
into @colA, @colB, @colC
while @@fetch_status=0
begin
print @colA
print @colB
print @colC
fetch next from @myCursor
into @colA, @colB, @colC
end
close @myCursor
deallocate @myCursor
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE simpleTest
GO
-------------
however this gives closed tickets but multiple records. I want only the last record details of the ticket_number.
I tried last(ticket_number) in my query, but it doesn't affect.
Please suggest! thanks.
I am using MS SQL server.
Wildcard Column Selection?
Sep 21, 2007Is it possible to have some thing like:
SELECT columns beginning with 'a' from mytable
kinda thing?
Dynamic Selection Of Column
Nov 19, 2007
Hi,
I have to choose a particular column from a table whenever a condition met. That means i have to use the column in the select query whenever the condition met otherwise i have to create a dummy column with the same column name.
I tried to use the following sample query.
Eg:
Create table temp
(test_id int,
test varchar(100))
Declare @var varchar(100)
Set @Var='Hi'
Select
case when @Var='HI' then "None" else Test_new End as Test
from Temp
But it throw error as "Invalid column 'Test_new' "
As per my situation the Test_new column will be available in temp table whenever the condition is not met.So when @var<>'HI' i have to get the details of the test_new column otherwise i have to create a dummy column with the same name.
[SQLDatasource]Dynamic Selection Of Column
Mar 29, 2006Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.
If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also.
Can someon put my on the right path?
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
Dynamic Column Selection In Stored Procedure
Feb 10, 2005Hey Guys,
Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.
DECLARE @querycolumn varchar(20),
@queryvalue varchar(35)
SET @querycolumn = ''
SET @queryvalue = ''
IF(@sub_assembly = NULL)
BEGIN
IF(@part = NULL)
BEGIN
PRINT 'This is an error. You must have at least a part'
END
ELSE
BEGIN
SET @querycolumn = 'Part'
SET @queryvalue = @part
END
END
ELSE
BEGIN
SET @querycolumn = 'SubAssembly'
SET @queryvalue = @sub_assembly
END
SELECT SubAssembly, Part, COUNT(RecordID)
FROM Table
WHERE @querycolumn = @queryvalue
GROUP BY SubAssembly, Part
ORDER BY SubAssembly, Part
The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?
Random Selection From Table Variable In Subquery As A Column In Select Statement
Nov 7, 2007Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
Conditional On A Derived Column
Jan 27, 2006Hi everyone,
I have a quizzing application where users log in, answer questions, and are ranked relative to each other. For this final ranking, I calculate their score using this formula -
score = (correct Qs answered) * 150 / (total Qs answered) + (total Qs answered)
The SQL query that i use to get this info is -
Code:
SELECT TOP 50 username, (sum(correct) * 150 / count(1) + count(1)) AS score, count(1) as totalq
FROM questionsstats
GROUP BY username
ORDER BY score DESC
This works just fine.
However, on top of this I need to put an additional restriction that only users who have at least answered 20 questions be counted in. How can I do this? Adding a simple 'WHERE totalq > 20' does not work. I get the error "Invalid column name 'totalq'.".
Surely there must be a simple way to do this?
Thanks.
Conditional Display Of A Column
Apr 28, 2008Would like to know if it is possiblefor any of you to suggest how to conditionally diaplay a Column based on the values of the other columns.
Here's my query
SELECT
[ProductionDay]
,DATEPART(wk,StartTime) AS WeekCount
,(AVG([RNTDuration])) AS AvgRNT
,MIN([RNTDuration]) As LeastRNT
,MAX([RNTDuration]) As WorstRNT
,dbo.fn_MinimumRNT_sp([ProgramNo],[Machine],[ProductionDay]) AS BestRNT
,dbo.fn_MinimumRNT(SubAssemblyNo,[Machine]) AS BestRNT_txt
,SUM([LoadingDuration]) AS LoadingTime
,[ProgramNo] AS ProgramNo
,COUNT(RNTID) AS PartsCount
,[SubAssemblyNo]
,[Sheetsize]
FROM [RPMS].[dbo].[List_MachineShopRNT]
where Machine = @iMachine and dateadd(month, datediff(month, 0, [StartTime]),0) = @dtMonthStartDate
Group by DATEPART(wk,StartTime), [ProductionDay],[Machine],[ProgramNo],[SubAssemblyNo],[Sheetsize]
Here I would like to Run the function dbo.fn_MinimumRNT_sp or dbo.fn_MinimumRNT if the ProgramNo is 100
Thank you
New Column With Conditional Values
Jan 29, 2008Does anyone know how to combine to columns into one using sql?
For example
Say I want to create a column3:
column1 _____ column2
------------------------------
primary _____ secondary
primary _____ [Non Applicable]
tertiary _____ quad..something
and IF column1 = primary AND column2 = secondary THEN column3 = newvalue
This is easy is access (and excel) but it doesn't seem to work in the same way in sql.
Conditional Column Name On Insert
Jan 21, 2006I need to dynamic select a column in which insert a vale based on aparameter value, I have this code, but it throws an incorrect syntaxerror.How do I dinamically select a column to insert based on a parameter?Create PROCEDURE dbo.UpdateDetalleOT (@eotId int,)insert into OT (select Casewhen @eotId = 1 THEN OTFechaBorradorwhen @eotId = 2 THEN OTFechaAAsignarend) values ....Best RegardsFabio Cavassinihttp://www.pldsa.com
View 9 Replies View RelatedConditional Column Formatting
Jul 19, 2007Hi Everyone,
I am trying to hide a column in a matrix table. I have no trouble using conditional formatting to control the visibility of the column, but when I hide the column the main row in the matrix does not shrink. Therefore, I have a big gap€¦ My thought was to use conditional formatting to control the column width of the main matrix row. Listed below is the expression that I used. Can anyone help me with the error message or recommend a better solution?
=IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) = 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be parsed as a unit because it does not contain numeric values. Examples of valid unit strings are "1pt" and ".5in".
Regards,
A.Akin
Conditional Column Select
Aug 9, 2006Is there a statement to change the column in a select clause?
For example:
select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.
Conditional Column Mapping
May 25, 2007Hey all! I have a bunch of questions, but let's start with this one:
Incoming from my flat file, I have two columns:
employee_id
dept_id
These indicate who did the work, and for which department (people can work for more than one department). In my destination table, I have the following two columns:
employee_id_sales
employee_id_wrhs
I want to map the employee id either to employee_id_sales or employee_id_wrhs, depending on the dept_id from the flat file.
How do I specify conditional column mapping?
I'm really new to SSIS, so I might be missing something obvious.
Thanks!
-- Jim
Joining And Conditional Column Data Help
Dec 21, 2006I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.employee table (empid, name)tasks table (taskid, empid, taskname, resolution)If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!SELECT e.empid, e.name, COUNT(t.ID) as 'tcount'FROM tasks t RIGHT JOIN employee e ON c.empid = t.empidGROUP BY e.empid, e.nameorder by 'tcount' desc
View 3 Replies View RelatedInner Join Based On Conditional Column
Mar 5, 2014I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0
Conditional IF In A Derived Column Transform
Apr 12, 2006HI, I was wondering if there is a possibility to use a confitional if like this:
IF(ISNULL(mycolumn value, "new value if null", mycolumnvalue)
into a derived column transform to infer a value to a null column value. I do know I can do it using a script component by it would be simpler to do by using an expression.
Thank you,
Ccote
SSIS Conditional Column Mapping
Aug 3, 2007
I have a condition where if column5 is equal to 1 then put column6 into the destination column "dest6", if it is not equal to 1 then put column6 in destination column "dest7"
What is the best way to do this in SSIS?
If I have to use the conditional split then do I have to copy my complete mappings, exact change this one column?
Thank for the help this mapping will take me a long time!
Conditional Color In Matrix Subtotal Column
Jun 6, 2006I am using matrix in my report with calculated fields, for example I have 3 columns : Actual amount, Budget and Variance. Variance should be in Red if it is negative. I can set up an expression to change the color on the row level, but not in Subtotal ot Total row. I cannot use sum of Fields! values, because Budget amount is also calculated field based on the "Category" value which is columns group on the matrix.
So anyway, if I am trying to reference ReportItems!Variance.Value in Subtotal Level, it gives me an error about group scope.
How can I access the cells values in subtotal and total group levels? If anybody knows any tricks for this fairly simple task?
Thanks for your help.
Olga
Integration Services :: Conditional Concatenation In Derived Column?
Aug 3, 2015I have 10 columns i.e from Segment1 to Segment10. I need to concatenate it with ".". All 10 segments can be null. If any of the segment is null i do not want to show ".". This is the expression I am using
 (DT_STR,100,1252)((ISNULL(SEGMENT1) ? "" : "." + SEGMENT1) + (ISNULL(SEGMENT2) ? "" : "." + SEGMENT2) + (ISNULL(SEGMENT3) ? "" : "." + SEGMENT3) + (ISNULL(SEGMENT4)
? "" : "." + SEGMENT4) + (ISNULL(SEGMENT5) ? "" : "." + SEGMENT5) + (ISNULL(SEGMENT6) ? "" : "." + SEGMENT6) + (ISNULL(SEGMENT7) ? "" : "." + SEGMENT7)
+ (ISNULL(SEGMENT8) ? "" : "." + SEGMENT8) + (ISNULL(SEGMENT9) ? "" : "." + SEGMENT9) + (ISNULL(SEGMENT10) ? "" : SEGMENT10))
But if any of the column contains Null or Blank I get ...... I do not want to show these .... if any segment is null.
What mistake am I doing in the above expression?
Integration Services :: Conditional Split Column Less Than Today
Oct 29, 2015I'm trying to write a conditional split where I want to bring in only records where the date is less than today, but my problem is that I can't simply do this Column < GetDate() because if something comes in today, it takes the time into account and it will bring that record for today. You can do this in SQL, but I'm not sure how to do that in SSIS
SELECTCAST(CONVERT(char(8),GetDate(),112)ASdatetime)
Conditional Number Format On Specific Matrix Column Group
Feb 6, 2008I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
Derived Column Transformations - DT_STR Operand Cannot Be Used With The Conditional Operation
Sep 16, 2006Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value. I have tried to use two different forms of a conditional operator to achieve this end. However I am getting the below errors both can be summed up with the following statement.
DT_STR operand cannot be used with the conditional operation. The expression directly below however is using a type DT_I4 in the conditional clause as this is what FINDSTRING returns. Hence the equivalencey test to the literal integer 0. So I must say I am somewhat confused by this. Does anyone know why neither of the below statements are working?
Also is there an easy way to accomplish what I am trying to do - convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value?
FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1) == 0 ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) != "(null)" ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating
Aug 2, 2007
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
thanks
Conditional Subscription / Conditional Execution Of Report
Mar 7, 2008
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
Conditional Formatting - Not So Conditional??
Dec 15, 2006I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")
I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.
Is it because this is a matrix report? What am I doing wrong?
Thanks in advance . . .
Selection
Oct 22, 2003hello
in the database.....
table:1
DELIVERY_HEADER
INVOICE# PO#
1111 001,0002,0003
2222 005,0006,0007
in one invoice has many po# number i already get the po number individually using explode...
the ouput like this
001
002
003 and so on it is ok for that
but my problem is
i want to the po# should be selected i mean like a menu ..like the user select the which po# should be updated....
but i dont know how..
is there anyone can help me?
to elaborate more...
i can display the po# already without commas like in invoice# 1111
i can already display the po# number like this
001
002
003
after that i want it to be like a menu that user will only select the which po# should be updated..using arrow keys down and up....i mean just like selecting..
but i dont know how..
PLS HELP ME
thank
r3sann
Many-to-many Selection
Jan 17, 2006Hi all, I am stuck on the case of many-to-many selection.
Now, I have 3 tables
Table: candidate_skills
candidate_id (PKey)
skill_id (PKey)
Table: skills
skill_id (PKey)
Table: job_skill_required
job_id(PKey)
skill_id(PKey)
Now, I want to select all cadidates who has the same skills of job_skill_required table.
I really don't know how to select it...
Please help, thank you.
Help With The Selection
Nov 16, 2007SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name ,
b.station_nbr, b.beg_eff_date, b.end_eff_date,
Round( e.C02_pct,3)As C02_pct, MAX( e.sample_date)As Highest_Sample_Date, d.station_name
from [HOU-TIES01].TIES_Gathering.dbo.group_detail a
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_group b on a.group_code = b.group_code
Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_type c on b.station_nbr = c.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.station d ON c.station_nbr = d.station_nbr
Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.gas_analysis e ON d.station_nbr = e.station_nbr
Where (b.station_nbr=e.station_nbr)and (b.group_code = a.group_code) and
(a.group_type = 'NPN')and( b.end_eff_date >=DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0))
and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))and
(e.sample_date Between DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0)
and DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
and e.seq_nbr =
(select max(x.seq_nbr)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
and e.C02_pct =
(Select max(x.C02_pct)
From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
Group by a.group_code, a.group_name, a.group_note, b.station_nbr, b.beg_eff_date, b.end_eff_date, e.c02_pct, d.station_name
order by a.group_code
ok. Here is how the data looks.
Group
code station_nbr beg_eff_date end_eff_date C02 sample_date
0004 D01G000819 2007-09-01 3000-12-31 0.40 2007-10-02
0004 D01G000819 2007-09-01 3000-12-31 2.49 2007-10-09
What I am trying to do is to select the most current sample date. In this case "2007/10/09" when station_nbr and group_code are the same for both dates.
Thank You for all your help!!!!
From the Jr.in SQL
I Need Help In How To Use SUM Selection.
Nov 27, 2007Hi Team:
This is a new query and I am facing a new problem. Here I am requesting all the records for a Contract Number 'GTH00001' for the specific date of 2/2/2007. I want the SUM(c.rcpt_nom_vol)AS Receipt and the b]SUM(c.rcpt_fuel)As Allocated_Fuel[/b]
But Instead I am getting 147 records for Contract Number'GTH00001'for the date of 2/2/2007.
Declare @Req_Contract_nbr char (8),
@Req_Day int,
@Req_month int,
@Req_year int
Set @Req_Contract_nbr = 'GTH00001'
Set @Req_Day = '2'
set @Req_month = '2'
Set @Req_year ='2007'
SELECT Distinct a.contract_nbr,
c.beg_eff_date, c.rcpt_dlvry_ind,
SUM(c.rcpt_nom_vol)AS Receipt, SUM(c.rcpt_fuel)As Allocated_Fuel
from dbo.contract a
Inner Join dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R') and a.contract_nbr= @Req_Contract_nbr
and Day(c.Beg_eff_date) = @Req_Day
and (month(c.Beg_eff_date)= @Req_month )
Group by a.contract_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel
Data Example
Contract_nbr specific Date rcpt_nom_vol rcpt_fuel
GTH000012007-02-02 00:00:00.000R4.00.0
GTH000012007-02-02 00:00:00.000R8.00.0
GTH000012007-02-02 00:00:00.000R11.00.0
GTH000012007-02-02 00:00:00.000R12.01.0
GTH000012007-02-02 00:00:00.000R14.01.0
GTH000012007-02-02 00:00:00.000R15.01.0
GTH000012007-02-02 00:00:00.000R16.01.0
GTH000012007-02-02 00:00:00.000R16.02.0
GTH000012007-02-02 00:00:00.000R16.04.0
Thank you for all your help!!!!!!!!! I deeply appreciate all your help.
Osiris
The future depends of what we do in the present- Mahatma Gandhi
CheckBoxList Selection
Mar 14, 2008Hi
I have CheckBoxList to make my selection as follows:if (CheckBoxList2.SelectedValue == "")
{
strSelect = strSelect;
}else if (CheckBoxList2.SelectedValue == "1")if (strWhere == " where")
{strWhere = strWhere + " pic1 = 'true' ";
}
else
{strWhere = strWhere + " and pic1 = 'true' ";
}if (CheckBoxList2.SelectedValue == "2")if (strWhere == " where")
{strWhere = strWhere + " pic2 = 'true' ";
}
else
{strWhere = strWhere + " and pic2 = 'true' ";
}if (CheckBoxList2.SelectedValue == "3")if (strWhere == " where")
{strWhere = strWhere + " pic3 = 'true' ";
}
else
{strWhere = strWhere + " and pic3 = 'true' ";
}
which mean it will addor remove the fields depends upon the user selection, but it will only select one !!
Thanks in advance