How To Make NULL Values Show Last
Jun 23, 2014
I have the following table records structure:
Code:
drop table ##t1
create table ##t1(col1 int, col2 int, col3 int, col4 int)
insert into ##t1(col1 , col2, col3, col4)
values (1,2,3,4),
(5,6,7,8)
[Code] .....
I can't figure out how to put all nulls at the bottom and have all of my records on the top.
View 11 Replies
ADVERTISEMENT
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
My script is here, and the sample result is attached. How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
[code]....
View 1 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
View 6 Replies
View Related
Apr 22, 2008
Hi All,
I want to show 0, if the field contains NULL values.
I use the following expression.
=IIf(Fields!MTD_TotGrossBKCOAmt.Value = "NULL" , SUM(Fields!MTD_TotGrossBKCOAmt.Value), 0 )
But this works if the field contains only NULL values.
If it has a value, then it shows as #Error
Can anyone tell me how to make this work?
Thanks
View 7 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
May 13, 2015
It's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table.
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
View 4 Replies
View Related
Jul 18, 2007
I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?
John Smith
Title: MSTP Location: Main Campus IP:127.0.0.1
Softwares:
Adobe Standard 7.0 Access 5.0
Internet Explore 6.0 Office XP
Any suggestion is appreciated.
View 1 Replies
View Related
Dec 14, 2011
I have to show 11 rows:
The first 10 rows would be the top ten values for a Measure and a Dimension that has 20 members. I can get it with the following MDX expresion:
SELECT
TopCount(EXCEPT([Dim Category].[Dim Category].AllMembers,[Dim Category].[Dim Category].[All]),10,[Measures].[Value]) ON ROWS,
[Measures].[Value] ON COLUMNS
FROM [My Cube]
View 4 Replies
View Related
May 20, 2008
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
Am I just missing a simple option?
Nathan
View 1 Replies
View Related
May 22, 2008
Hi all,
In my cube there is a customer dimension and a product dimension.
If I filter on a single customer, and then click on the filter dropdown for product, all of the products for every customer are shown.
Is it possible to make it so it only show the members for the customer selected?
I suspect the answer is no, as that would mean you can't untick any previously ticked members, but our account management team have got the right hump about them showing so I need to at least attempt it.
Regards,
Phil
View 2 Replies
View Related
Feb 23, 2007
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies
View Related
Dec 2, 2005
I have a table with a TEXT column that allows nulls.
Now I need to change it, and make it NOT NULL, but I Can't
ALTER TABLE BUSINESS_UNIT_LANG
alter column VISION text not null
And I get:
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'VISION' because it is 'text'.
Is it impossible to make a text column NOT NULL when it accepts NULLS ?
All rows and columns are populated, in consequence there isn't any col with null values
Any idea?
Thanks!
View 1 Replies
View Related
Apr 29, 2008
the Table columns is like this NO ProductNo Area In Out1 0001 US NULL NULL2 0002 UK NULL Y3 0003 FR Y NULL 4 0004 FR Y NULL5 0005 UK Y NULL I have Query get the result belowArea Count In&OutUS 1UK 2FR 2 the Area is Group By by Area and the Count columns is counting how many recoreds Table for each Areathe problem is the column "In & OUt'I have to make sure if the "In" or "Out" is Null , if one of them is Null the plus 1 so the result would like I have Query get the result belowArea Count In&OutUS 1 1
UK 2 1
FR 2 0 which syntax I can use for the problem? I just think maybe I can use IsNULL?but I have no idea how to wirte a query ...can you give me a hint? thank you
View 4 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Mar 29, 2006
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
View 1 Replies
View Related
Aug 29, 2006
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
View 2 Replies
View Related
Mar 18, 2008
I have a stored procedure where I gather some data and then insert the data into a table variable. I then attempt to go through each row of the table variable, asign the values to local variables to be inserted into other tables. However, the local variables show as NULL.BEGIN
DECLARE @tblcontact table
(
SOKey int,
Cntctkey varchar(60),
Cntctownerkey int,
LASTNAME varchar(32),
FIRSTNAME varchar(32),
WORKPHONE varchar(32),
EMAIL varchar(128),
processed int DEFAULT 0
)
INSERT INTO @tblcontact (SOKey, Cntctkey, Cntctownerkey, LASTNAME, FIRSTNAME, WORKPHONE, EMAIL)
SELECT ...
DECLARE @ID int,
@sokey int,
@cntctkey int,
@cntctownerkey int,
@name varchar(65),
@email varchar(128),
@phone varchar(32)
WHILE EXISTS (SELECT * FROM @tblcontact WHERE processed = 0)
BEGIN
SELECT @ID = MIN(SOKey) FROM @tblcontact WHERE processed = 0
SELECT @cntctkey = (CAST(LTRIM(REPLACE(Cntctkey,'CN',' '))AS int)),@cntctownerkey = Cntctownerkey, @name = FIRSTNAME + ' ' + LASTNAME, @phone = WORKPHONE, @email = EMAIL, @sokey = SOKey
FROM @tblcontact
WHERE @ID = SOKey AND @cntctkey <> '43778'
INSERT INTO tciContact (Cntctkey, Cntctownerkey, CreateType, EMailAddr, EmailFormat, EntityType, ExtUser, Name, Phone, UpdateCounter)
VALUES (@cntctkey, @cntctownerkey, '0', @email, '3', '401', '0', @name, @phone, '0')
UPDATE tsoSalesOrder
SET Cntctkey = @cntctkey, UserFld4 = 'temp'
WHERE SOKey = @sokey
UPDATE @tblcontact
SET processed = 1 WHERE @ID = SOKey
END
END
View 4 Replies
View Related
Sep 21, 2007
I have a subquery that grabs all the sales reps with less then 6 visits. Only problem is, when i have a date when there are any number of sales reps that dont make visits, so the column would be null, they dont show up. I want to display these, because this report is supposed to show the visits made, so if they made none, i want it to show zero, instead of not showing the whole date column, since zero visits were made on that date that were below 6 and more then zero. Here's my stored procedure: (the subquery is highlighted)
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Visits_6]
(@Region_Key int=null)
AS
BEGIN
SELECT dbo.Qry_Visits.Status,
dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],
B.VisitsTotal
FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status
FROM dbo.Qry_Visits
WHERE Qry_Visits.Status=2
GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status
HAVING SUM(Visits) < 6)B
ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND
CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate
WHERE REGION_KEY=@Region_Key and Qry_Visits.Status=2
ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)
View 5 Replies
View Related
Nov 9, 2000
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
View 1 Replies
View Related
Apr 15, 2007
Is there a way in rs to have a textbox more than the first value when it is dragged on the rs form when creating a report? currently when I drag a text box on the rs form, it only shows the first record on a page/form. There are twenty records on the table where the first value came from. How can I make so that twenty text boxes representing twenty records appear on twenty different pages. In other words, I am trying to build a report with a non - tabular structure that can repeat on different pages.
View 4 Replies
View Related
May 29, 2008
I made a number of dimension and calculated member mods and notice that all now no base or calculated measures display values, they just show blanks in the browser and query window. To try to isolate, I've
(1) removed every dimension and calculated member
(2) verified there are no defaultmembers for dimensions
(3) verified there are no dimension attributes set to IsAggregatable = False
(4) verified there are no deploy or script errors
(5) fully processed every dm and fact
(6) verified there are dim and claim records loaded with values in all appropriate columns
When I process the cube, it shows that rows are read, etc... All that looks normal. Just when I browse or query, all measures display as blanks or nulls.
What could cause this behavior? Any ideas on how to futher isolate? I can go to a backup but very concerned that this can happen with no error messages or warnings, so I want to find the cause.
View 4 Replies
View Related
Sep 27, 2006
I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.
View 14 Replies
View Related
May 19, 2014
I've the table like
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)
Like I've 30th onwards.....
Expecting o/p:
month Travel Fixed Food Lodge
apr-14 200 550 500 600
These cum column values how to make a code ?????
View 5 Replies
View Related
Apr 21, 2015
In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)
INSERT INTO ##TEMP_TEST
SELECT '2014-09-30','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-29','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-28','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
GO
INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULL
[Code] ....
View 6 Replies
View Related
Feb 19, 2014
This is my table structure
--=====
create table calculate(
ID int identity (1,1),
PreviousYear_Profit float not null,
)
insert into calculate values (12500)
insert into calculate values (22700)
insert into calculate values (18500)
insert into calculate values (25800)
--======
I want to calculate variance and expected output is
ID PreviousYear PriorVersion Variance
1 12500 sum(PreviousYear) PreviousYear-PriorVersion
2 22700 sum(PreviousYear) PreviousYear-PriorVersion
3 18500 sum(PreviousYear) PreviousYear-PriorVersion
4 25800 sum(PreviousYear) PreviousYear-PriorVersion
I don't have PriorVersion and Variance columns.
PriorVersion will be like sum(PreviousYear)
Variance will be (PreviousYear - PriorVersion)
I can query if I have column, since I don't have these two column how could I possibly achieve the output using my existing table schema.
View 1 Replies
View Related
Nov 14, 2007
Is there a quick and easy way, other than scripting a MsgBox(), to show the value of a package variable while the package is running?
View 3 Replies
View Related
Nov 7, 2006
I am editing a pre-existing view.This view is already bringing data from 40+ tables so I am to modify itwithout screwing with anything else that is already in there.I need to (left) join it with a new table that lists deposits and thedates they are due. What I need is to print, for each record in theview, the due date for the next deposit due and the total of allpayments that they will have made by the next due date.So this is how things are. I join the table and it obviously bringsmultiple records for each record (one for each matching one in the newtable). I need, instead, to be able to make out what due date I shouldprint (the first one that is GETDATE()?) and the total of deposits upto that date.Now, payments can be either dollar amounts or percentages of anotheramount in the view. So if it's an amount I add it, if it's a % Icalculate the amount and add it.Example:for group X of clients...Deposit 1 due on oct 1: $20Deposit 2 due on oct 15: $30Deposit 3 due on nov 15: $40Deposit 4 due on nov 30: $50for group Y of clients...Deposit 1 due on Oct 30: $200Deposit 2 due on Nov 30: $300Deposit 3 due on Dec 30: $400So when if I execute the view today (Nov 7th) each client from group Xshould have:Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)Group Y should have:Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)And so on.
View 4 Replies
View Related
Jan 28, 2008
Hi,
I've designed a report and assigned it a datasource that binds with a SP with a parameter. In the XML code of the dataset I can see the input parameter has been recognised by the wizard. Then, I built a report with that Dataset as the source. Everything is fine till this time. Now through my aspx code I pass on the value of the parameter to the report. But the report doesn't show any values at all. The aspx code is like
Code Snippet
Dim reportParameter As New ReportParameter("@UserID", Convert.ToInt32(TextBox1.Text), True)
Dim test() As ReportParameter = {reportParameter}
ReportViewer1.LocalReport.SetParameters(test)
Any ideas what I could be doing wrong?
VS 2005 & SSRS 2005.
TIA.
View 2 Replies
View Related
Dec 3, 2007
I know this is an easy one, but for some reason i keep gettin the wrong results.
This displays something like this :
Question_description Visit_Activity_Id SR_Name
Vacation 5 Judy Smith
Sick 2 Judy Smith
Visit 1 Tom Mathews
Training 3 Karen Williams
But i want it to show all the SR_Name's ..like this:
Question_description Visit_Activity_Id SR_Name
Vacation 5 Judy Smith
Sick 2 Judy Smith
Visit 1 Tom Mathews
Training 3 Karen Williams
NULL null Tom Jones
NULL null Kim Jones
NULL null Jon Travis
Any help will be grately appreciated! thanks!
Code Block
ALTER PROCEDURE [dbo].[PROC_RPT_SR_DAILY_ACTIVITIES]
(@Region_Key int=null, @Daily_activity_statistics_datetime datetime )
AS
BEGIN
SELECT Customer.Name as Store_Name,
Tbl_Daily_Activity_Statistics.SR_Code,
Tbl_Daily_Activity_Statistics.Territory_Code,
Tbl_Daily_Activity_Statistics.Customer_code,
Tbl_Daily_Activity_Statistics.in_progress_time,
Tbl_Daily_Activity_Statistics.completed_time,
Tbl_Daily_Activity_Statistics.Visit_Activity_Id,
Tbl_Daily_Activity_Statistics.dial_in_datetime,
Tbl_Daily_Activity_Statistics.question_code,
tbl_Questions_to_RC_Question.RC_Question_description,
Qry_Sales_Group.SR_Name, dbo.Qry_Sales_Group.Region,
Qry_Sales_Group.Region_Key,
Tbl_Daily_Activity_Statistics.Daily_activity_statistics_datetime
FROM Tbl_Daily_Activity_Statistics
INNER JOIN tbl_Questions_to_RC_Question
ON Tbl_Daily_Activity_Statistics.question_code = tbl_Questions_to_RC_Question.question_code
INNER JOIN Qry_Sales_Group
ON Tbl_Daily_Activity_Statistics.SR_Code = Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
LEFT OUTER JOIN customer
ON dbo.Tbl_Daily_Activity_Statistics.Customer_code = dbo.customer.customer_code
WHERE Region_key=@Region_key AND Daily_activity_statistics_datetime = @Daily_activity_statistics_datetime
END
View 3 Replies
View Related
May 22, 2008
Hi,
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
At the moment I have something like:
Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15
and I need a view that looks like:
Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)
I've tried using pivots but they don't seem to help
Any ideas?
View 7 Replies
View Related
Feb 10, 2014
The below stored procedure is used to create a vertical benchmark line on the X-Axis which has a hour scale. I use the stored procedure to find out which temperature crosses or equals the threshold temperature (340), then plot the vertical benchmark line at the hour the first temperature is equal to or greater than 340 degrees and less than 1000 degrees.
The logic below works if the temperature is equal to or greater than 340 degrees and less than 1000 degrees. THE ISSUE is I have 8 temperatures if they don't cross the threshold of 340 degrees I need to set a default value for my vertical line. In other words if the temperature is 180 and my threshold is 340 then set my vertical line on the highest temperature close to 340.
I tried removing my Where clause (but then it breaks the logic for those temperatures that are equal to or greater than 340). I tried using Case When but this didn't give me what I want either. I tried UNION as well. All giving me results I don't want.
Here is what I am looking for:
This first example is one where there was a temperature that was equal to or greater than the threshold of 340 degrees. This is CORRECT
Code:
first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-04 19:24:003.3500002014-02-04 16:02:13.0002014-02-05 05:02:13.000
If 8 temperatures did not equal or cross the threshold then give me the hour of the highest temperature close to the threshold but do not return 0.
For Example:
temp1 92
temp2 108
temp3 0
temp4 284 <<< this is the closest to the threshold so give me the hour when this occurred.
temp5 2192 *Remember I can only count temperatures less than 1000 degrees. Anything above 1000 degrees mean there is nothing in the oven. So it is false/positive.
temp6 102
temp7 0
temp8 12
Code:
first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-05 00:30:001.3000002014-02-05 02:00:13.0002014-02-05 02:00:13.000
Code:
CREATE PROCEDURE [dbo].[AgeScoreCardThreshold_JJ_12232013]
-- Add the parameters for the stored procedure here
@LicenseNumber int = NULL,
@Lot varchar(50) = NULL
[code].....
View 3 Replies
View Related
Feb 15, 2007
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
View 3 Replies
View Related
Jul 17, 2015
I'm trying to run the following sql
SELECT DISTINCT
Anvendelseskoder.[Usage Code] AS [Building Code],
Anvendelseskoder.[Usage Code Value] AS [Building Description],
HeleDanmark_DAWA.KVHx
FROM Anvendelseskoder
RIGHT JOIN HeleDanmark_DAWA
[Code] ...
It gives me the following error:
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "aa.KVHx" could not be bound.
When clicked, it marks the first time i call "aa.KVHx"
Which I do in: "WHERE S2.Nr=2 AND s2.KVHx=aa.KVHx) AS Kode2,"
View 2 Replies
View Related