Is 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.
I am trying to write query that will select calls that only show up when the resolution returned from the case in the sub query is 'y'. When I do the group by some calls will show up twice because of a null and a 'y' resolution return. I want to only select a call if there is a 'y' that shows for all resolutions associated with that call. If there are any null resolutions for the call I don't want it to show up in my returned values. This is what I have so far, but I can't figure out how to make it drop both instances of the callID if one instance is null.
Code:
SELECT C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution, CASE C.CallID WHEN T1.Resolution IS NOT NULL THEN 'Y' ELSE 'N' END
FROM HEAT.dbo.CallLog C,
(SELECT CallID, CASE WHEN A1.Resolution = '' THEN 'Y' ELSE NULL END AS Resolution
FROM HEAT.dbo.Asgnmnt A1) T1, HEAT.dbo.Profile P
WHERE C.CallID = T1.CallID AND C.CustID = P.CustID AND (P.PrimarySupportGroupID = 'ATS') AND (P.PrimaryTeamName = 'Beta')
GROUP BY C.CallID, P.PrimarySupportGroupID, P.PrimaryTeamName, T1.Resolution
If anyone could help me out with this or at least give me a little information and point me in the right direction I would really appreciate it.
Thank you
Court
Dear GroupI'm having trouble with the statement below. I tried CASE and IFwithout success. What I'm trying to do:There is a field in the database called Business_TelNo. If the fieldhas some value, I would like to return a generated field(LaBusinessTelNo), which is the label of Busines_TelNo, reading'Phone:'If Business_TelNo has no value, the label should be set to ''.Something like this:SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,IF (LEN(Business_TelNo) > 0) BEGIN SELECT 'Phone: ' AS LaBusinessTelNoEND ELSE BEGIN SELECT '' AS LaBusinessTelNo ENDFROM i2b_vw_contactThis is working:SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,'Phone: ' AS LaBusinessTelNoFROM i2b_vw_contactPS: I know it would be much easier to add some logic in theapplication but need to do this in SQL.Thanks very much for your time and efforts!Martin
View 2 Replies View RelatedI want to create a query to summarize sales for each of the last five weeks and group by product type ie.
select product, sum(amount this week), sum of (amount last week) ... group by product
my sales table looks like this
product date amount
x d1 xx
x d2 xx
y d3 xx
y d4 xx
x d5 xx
How can this be done?
thanks
Hello dbForumers,
Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?
Thank You!
Table with water consumption per month and customer.
I want to sum up total consumption per customer with a select statement
TSQL statement, it should select the first date of the month at 11:59 PMÂ as Firstdate and Closedate as 10 days later at 11:59 PM.
View 14 Replies View RelatedI have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like:
CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));
CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1)
DECLARE @id INT;
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
IF (@id is null)
BEGIN
INSERT MyLookup ('some key value');
SET @id = SCOPE_IDENTITY();
END
(Approach 2)
DECLARE @id INT;
INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');
IF (@@ROWCOUNT = 0)
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
ELSE
SET @id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
Hi 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.
Would 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
I 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.
Does 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.
I 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 RelatedHi 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
Hey 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
Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255) )
AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Please help,
Thanks,
bullpit
I 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 RelatedI 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
HI, 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
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!
Hi All,
I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.
My BASIC Select Statement is:
SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
Address2, Suburb, State, Postcode, Phone
FROM Customers
WHERE Customer_No IN
(SELECT Customer_No FROM temp_Customers)
Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:
SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
FROM Customers
GROUP BY Customer_No
ORDER BY First(Cust_Ref_No), Customer_No;
There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?
P.S. I really need to run the select statement as one step rather than splitting it up into parts.
Regards, Trog28
I 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
I 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?
I'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)
I'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.
Hi 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.
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
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
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedExample, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies View RelatedUsing SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
View 11 Replies View RelatedUsing SQL Server 2000. How can I refer to one alias in another column?
E.g., (this a contrived example but you get the idea)
SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
View 1 Replies View Related