I need a query that gives me the sum of every rows (time column) with lower 'rownr'
the result:
rownr time timesum
1 10 0
2 10 10
3 10 20
4 10 30
5 10 40
6 10 50
7 10 60
8 10 70
current table looks like this:
rownr time
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
and i want the 'timesum' column to be in format hhhh:mm
current format is rownr=int, time=datetime
thx for all help
//MrDo you have the URL or a PDF for this assignment?
-PatP|||:( no im making a database to store my divelogs in and this is the accumulated time im trying to calculate...|||I have no idea if this is what you are looking for. Nevertheless, to try and guess what the solution to your problem might be I went ahead and created a test database called MiscTests.
Now, I created a table similar to yours but instead of having the time column as datetime I've changed it to an INT. Here's the table code:
USE [MiscTests]
GO
/****** Object: Table [dbo].[Times] Script Date: 08/22/2007 17:50:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Times](
[rownr] [int] IDENTITY(1,1) NOT NULL,
[time] [int] NOT NULL CONSTRAINT [DF_Times_time] DEFAULT ((0)),
CONSTRAINT [PK_Times] PRIMARY KEY CLUSTERED
(
[rownr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
With this table in place I've made a small scalar-valued function to parse the time column. The code of the SVF is as follows:
USE [MiscTests]
GO
/****** Object: UserDefinedFunction [dbo].[parseTime] Script Date: 08/22/2007 17:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[parseTime]
(
@.Time AS Int
)
RETURNS VarChar(10)
AS
BEGIN
DECLARE @.hours AS Int
, @.parsedTime AS VarChar(10);
SET @.hours = 0;
WHILE (@.Time >= 60)
BEGIN
SET @.hours = (@.hours + 1);
SET @.Time = (@.Time - 60);
END
IF (@.hours = 0)
BEGIN
SET @.parsedTime = '0';
END
ELSE
BEGIN
SET @.parsedTime = CAST(@.hours AS VarChar(7));
END
SET @.parsedTime = @.parsedTime + ':';
IF (@.Time < 10)
BEGIN
SET @.parsedTime = @.parsedTime + '0'
END
SET @.parsedTime = @.parsedTime + CAST(@.Time AS VarChar(2));
return @.parsedTime;
END
GO
With this done I went ahead and filled the dbo.Times table with a couple of datarows. After that I ran a simple query to return the desired output. Here's the query SQL
SELECT
t.rownr AS rownr
, t.time AS [time]
, dbo.parseTime((
SELECT
sum(st.time)
FROM
dbo.Times st
WHERE
st.rownr <= t.rownr
)) as timesum
FROM
dbo.Times t
ORDER BY
t.rownr ASC;
The resultset should be something like this:
1 0 0:00
2 10 0:10
3 10 0:20
4 10 0:30
5 10 0:40
6 10 0:50
7 10 1:00
8 10 1:10
9 10 1:20
10 10 1:30
11 10 1:40
12 10 1:50
13 10 2:00
14 10 2:10
15 10 2:20
Hope this helps ;)|||declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10
select s1.rownr,
s1.time,
convert(varchar(5), dateadd(minute, sum(coalesce(s2.time, 0)), 0), 108) as timesum
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,
s1.time|||Peso, your solution is insufficiently complex. How do you ever expect to make a living at this with such concise solutions?
Obfuscate! Obfuscate! Obfuscate!|||thanks alot for all the effort you guys put into this, but i can't solve this. Its my experience that fails here... i really tryed Diabolic's solutions which im sure works but again, i can't apply that solutiuons at my database...
is it possible to make a query that gives me the result i want? or do i need a temp table or similar to accomplish my goal?
//Mr|||It appears that outer theta joins are too elementary for blindman now... :rolleyes:
Not to mention the use of temporal functions which, according to Pat are rather risky, but he's under NDA so can't tell us why...|||MRPCGuy, Peso's method is the standard solution to this common problem, and should work fine.|||declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 500 union all
select 5, 10 union all
select 6, 10 union all
select 7, 4000000 union all
select 8, 10
select s1.rownr,
s1.time,
convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,
s1.time
order by s1.rownr,
s1.time|||would somebody -- and i volunteer mrpcguy for this -- please post the DDL to create a table using rownr=int, time=datetime as specified in post #1
it's fine and dandy to set up test cases using ints, but mrpcguy says he's using datetimes|||im learning, im leraning, alright? Thx Peso for your help...|||don't get this... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?|||don't get this... if i have 100000 rows i want to count accumulated time for each row, how can i achive that?with a query, similar to the ones you've seen in this thread
any chance you could give us some real data, not fake data? i.e. data with datetimes, not integers|||thing is that i dont have any real data yet, im building this database and gona fill it with divelogs later on. But this function to be able to count accumulated time is important. i only have fake data.
My idea was to have datetime at that columt because logg gonna look like "00:30" for 30min. Maybe its better to use int and put in "30" for 30min...
How can i achive what i wont with my first post and use int instead of datetime?|||For int you just have to log the number of mins (int), but that will turn innacurate soon enough. Since you're storing the number of minutes in integer you will soon realise that if your dive was 2.5mins you'll have to round it.
To store the time as an int it's prolly better to store the number of seconds.|||your correct about that, but since i always round it up to whole minutes i don't need seconds...|||you should be able to use the solutions presented here, namely peso's one, if you're going to use int.|||Ok,
I got this:
current table looks like this:
rownr time
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
and I want this:
rownr time timesum
1 10 0
2 10 10
3 10 20
4 10 30
5 10 40
6 10 50
7 10 60
8 10 70
I run this:
declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10
select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time
What if I got a table with 10000 rows… that’s many select statements
I’m very aware that it’s my skills is the biggest problem here, so plz help…|||no, it's still only one SELECT statement, no matter how many rows|||well in my test table i have 50 rows and the output from above gives me 8 rows?!|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)|||is this doable in ms access? certainly is|||maybe not correct forum but anyway... is this doable in ms access? thinking of put this db in access instead? (don't want to run sql local)
of for the love of....|||...pancakes?|||well in my test table i have 50 rows and the output from above gives me 8 rows?!
You are getting 8 rows because you are using the wrong query. If I didn't missunderstand your post, you are using the following SQL to retrieve the desired results:
declare @.sample table (rownr int, time int)
insert @.sample
select 1, 10 union all
select 2, 10 union all
select 3, 10 union all
select 4, 10 union all
select 5, 10 union all
select 6, 10 union all
select 7, 10 union all
select 8, 10
select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from @.sample as s1
left join @.sample as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time
Well, it's only natural that you are only getting 8 rows. If you look closely you'll see that you are selecting the results from the temporary table called @.sample. To retrieve your real data you should use this sql statement:
select s1.rownr,s1.time,convert(varchar, sum(coalesce(s2.time, 0)) / 60) + ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)
from <MyTableName> as s1
left join <MyTableName> as s2 on s2.rownr < s1.rownr
group by s1.rownr,s1.time
order by s1.rownr,s1.time
Please, remember to replace <MyTableName> with the actual name of your dive logs table.|||Please, remember to replace <MyTableName> with the actual name of your dive logs table.and you will also have to change this nonsense --convert(varchar, sum(coalesce(s2.time, 0)) / 60)
+ ':' + RIGHT('00' + cast(sum(coalesce(s2.time, 0)) % 60 as varchar(2)), 2)into the equivalent ms access nonsense
:)|||thx alot it works fine... I'l take your query to another forum and get it translated into access when im have gone nuts trying to translate it myself...|||Good Lord.
It's only 12:08 PM and I am now exhausted.
and the thread is only two pages :)|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?|||and the sarcasm and humiliation at this forum is extraordinary... so what's ur problem beside being exhausted?My problems are numerous and well documented, but are primarily out of scope in the current discussion. One of those I would consider IN scope, however, is my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.
Sarcasm? Guilty as charged.
Humiliation? Nah, I've seen not one virtual wedgie applied here.
Lighten up. If you think this forum is bad, it's the first one you've been to.|||... my inability to grasp the usefulness of the term "ur" within the realm of an otherwise reasonably well-crafted sentence.then you will just have to make another visit to http://icanhascheezburger.com/
it will make sense eventually
:)|||and the sarcasm and humiliation at this forum is extraordinary...
LOL.. Thats why I keep coming back!|||http://icanhascheezburger.com/
lol @. that site :beer:|||then you will just have to make another visit to http://icanhascheezburger.com/
it will make sense eventually
:)
I must admit, I only looked at the first couple pages then got busy with *coff* work *coff* and forwarded the link to myself at home. I will catch up with the rest of the class over the weekend.
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Mining Accuracy Chart, predictable column in nested tables
In the Mining Accuracy Chart, the predictable columns of nested tables does not show up in the "Select predictable mining model columns to show in the lift chart" table. The "Predictable column name" is empty.
Predictable columns in the case table shows up, but not the predictable columns in the nested table. What am I missing?
-Young K
The accuracy chart currently does not support predictable columns in nested tables.
minimum value
i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
minimum value
i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero
"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
sql
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero
"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
sql
minimum value
i have a table with two columns named cust and price, i want to write a rule
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
or something , the mininum value of the column price must be >= cust * 1,4 ,
the ideia is not permit write in price a value minor of 40 % profit, how i
can make this? i have try with a rule but do not work.
Thanks in advance
Alejandro Carnero"alecarnero" <alecarnero@.uol.com.br> wrote in message
news:%23SNZpoFIGHA.3896@.TK2MSFTNGP15.phx.gbl...
>i have a table with two columns named cust and price, i want to write a
>rule
> or something , the mininum value of the column price must be >= cust * 1,4
> ,
> the ideia is not permit write in price a value minor of 40 % profit, how i
> can make this? i have try with a rule but do not work.
> Thanks in advance
> Alejandro Carnero
>
>
Try a CHECK constraint. Something like:
CREATE TABLE #Foo (
ProductID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL, -- Foreign Key
BasePrice money CHECK(BasePrice >= 0.00),
CustomerPrice money,
CONSTRAINT profit_margin CHECK(CustomerPrice >= (BasePrice * 1.4))
)
Rick Sawtell
MCT, MCSD, MCDBA
Monday, March 19, 2012
Min(NULL) shows up as 12/30/1899 in cube
Hi!
I have a cube with a measure based on a datetime column with aggregation Min in AS 2000 SP4. The Data Type is set to Date and the Format is set to Short Date. Somehow the the measure shows 12/30/1899 when all values in the datetime column are NULL. If I check the properties in the cube browser it says Formatted Value: 12/30/1899 and Value: 12:00:00 AM.
I've implemented a work around in the reports that use this measure to look for 1899 or AM and replace with it NULL, but what I really want is the cube to display the real value (= NULL) when I browse it.
Any help would be greatly appreciated. Thanks!
Nulls don't behave the same in an OLAP database. In fact they often evaluate to 0 which is what is happening here.
In a relational DB 1 + NULL = NULL
In an OLAP DB 1 + NULL = 1
You might have to change your approach, so possible avenues to explore would be
use an aggregation of None and use a calculation to rollup the values, but this could be slow as you would need to drill down to the leaf level to get any values when using an aggregtion type of none. You might be able to attach the date as an attribute to another dimension, it depends what the date measure is.|||So basically what I have to do is to create a calculated member based on this measure and use that one in the reports?:
iif(the_date_measure == 12/30/1899, NULL, iif(the_date_measure == 12:00:00 AM, NULL, the_date_measure )), which is the same thing I do in the reports.
I've verified it and a date column with NULL becomes 12/30/1899 in AS (in ver 2000 at least). So gotta use a work around as above to fix this.
|||That would work too.
You could even put that type of calculation in the cube. If it were me I would hide the "raw" measure and create a calculated measure with this sort of logic in it.
|||I've now created a calculated member as:
IIF( (InStr([Measures].[Adate], "00:00") > 0) OR (InStr([Measures].[Adate], "00.00") > 0), NULL, [Measures].[Adate])
and it seems to work very well. Looking for AM or 1899 does not work since the value will be 12:00:00 AM, 0:00:00, 00:00:00 depending on regional settings. I looked through all regional settings on my computer and only Faeroese and Italy have a dot separator instead of colon in their time setting (00.00.00).
Btw, do you know how date columns with NULL show up in AS 2005? Same behavior here?
Thanks for all your help!
|||Well you have some more options in AS2005, it is not really typical to use dates based measures so it is hard to say without knowing more details. The Min aggregation should behave the same, but you could also potential use something like LastNonEmpty or None with a calculation to do the rollup.Min except for zero
We have a complex query with many aggregate functions.
For one column where we return MIN, we would like to return the minimum
non-zero value.
I don't think using HAVING is going to work, because we don't want to
exclude any rows from the result set.
We're thinking of doing something like MIN(case...) but haven't found
something
that works.
An example:
TAB1
NUM1 NUM2
3 0
5 1
7 2
We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.Try
MIN(CASE WHEN NUM2 <> 0 THEN NUM2 END)
or
MIN(NULLIF(NUM2,0))
Steve Kass
Drew University
jhcorey@.yahoo.com wrote:
>We have a complex query with many aggregate functions.
>For one column where we return MIN, we would like to return the minimum
>non-zero value.
>I don't think using HAVING is going to work, because we don't want to
>exclude any rows from the result set.
>We're thinking of doing something like MIN(case...) but haven't found
>something
>that works.
>An example:
>TAB1
>NUM1 NUM2
>3 0
>5 1
>7 2
>
>We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>
>|||One way is to use CASE to convert the 0s to NULL:
SELECT MAX(a), MIN(CASE b WHEN 0 THEN NULL ELSE b END) AS b
FROM
(
SELECT 3 AS a, 0 AS b
UNION
SELECT 5, 1
UNION
SELECT 7, 2
) AS i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhcorey@.yahoo.com> wrote in message news:1108483393.537431.133510@.c13g2000cwb.googlegroups
.com...
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Correction,
select max(num1), min(case when num2 > 0 then num2 else null end) from tab1
go
AMB
"Alejandro Mesa" wrote:
> You are in the correct path. Try:
> select max(num1), min(case when num2 > 2 then num2 else null end) from tab
1
> go
>
> AMB
> "jhcorey@.yahoo.com" wrote:
>|||You are in the correct path. Try:
select max(num1), min(case when num2 > 2 then num2 else null end) from tab1
go
AMB
"jhcorey@.yahoo.com" wrote:
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Hi
WHERE (Num1 > 0 OR Num2 > 0)
Regards
Mike
"jhcorey@.yahoo.com" wrote:
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||MIN(NULLIF(num2,0))
David Portas
SQL Server MVP
--|||Whew, that was quick!
Thanks to all.
As a note, I was trying this in the Northwind database and
having some difficulty (all values are decimals between 0 and 1):
select max(discount),min(discount),min(case when discount = 0 then null
else discount end) from [order details]
Jim
For one column where we return MIN, we would like to return the minimum
non-zero value.
I don't think using HAVING is going to work, because we don't want to
exclude any rows from the result set.
We're thinking of doing something like MIN(case...) but haven't found
something
that works.
An example:
TAB1
NUM1 NUM2
3 0
5 1
7 2
We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.Try
MIN(CASE WHEN NUM2 <> 0 THEN NUM2 END)
or
MIN(NULLIF(NUM2,0))
Steve Kass
Drew University
jhcorey@.yahoo.com wrote:
>We have a complex query with many aggregate functions.
>For one column where we return MIN, we would like to return the minimum
>non-zero value.
>I don't think using HAVING is going to work, because we don't want to
>exclude any rows from the result set.
>We're thinking of doing something like MIN(case...) but haven't found
>something
>that works.
>An example:
>TAB1
>NUM1 NUM2
>3 0
>5 1
>7 2
>
>We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>
>|||One way is to use CASE to convert the 0s to NULL:
SELECT MAX(a), MIN(CASE b WHEN 0 THEN NULL ELSE b END) AS b
FROM
(
SELECT 3 AS a, 0 AS b
UNION
SELECT 5, 1
UNION
SELECT 7, 2
) AS i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhcorey@.yahoo.com> wrote in message news:1108483393.537431.133510@.c13g2000cwb.googlegroups
.com...
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Correction,
select max(num1), min(case when num2 > 0 then num2 else null end) from tab1
go
AMB
"Alejandro Mesa" wrote:
> You are in the correct path. Try:
> select max(num1), min(case when num2 > 2 then num2 else null end) from tab
1
> go
>
> AMB
> "jhcorey@.yahoo.com" wrote:
>|||You are in the correct path. Try:
select max(num1), min(case when num2 > 2 then num2 else null end) from tab1
go
AMB
"jhcorey@.yahoo.com" wrote:
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||Hi
WHERE (Num1 > 0 OR Num2 > 0)
Regards
Mike
"jhcorey@.yahoo.com" wrote:
> We have a complex query with many aggregate functions.
> For one column where we return MIN, we would like to return the minimum
> non-zero value.
> I don't think using HAVING is going to work, because we don't want to
> exclude any rows from the result set.
> We're thinking of doing something like MIN(case...) but haven't found
> something
> that works.
> An example:
> TAB1
> NUM1 NUM2
> 3 0
> 5 1
> 7 2
>
> We want to select MAX(NUM1) and MIN(NUM2) and get 7 and 1 as the result.
>|||MIN(NULLIF(num2,0))
David Portas
SQL Server MVP
--|||Whew, that was quick!
Thanks to all.
As a note, I was trying this in the Northwind database and
having some difficulty (all values are decimals between 0 and 1):
select max(discount),min(discount),min(case when discount = 0 then null
else discount end) from [order details]
Jim
Min and Max Threshold for Column Chart
Hi there,
My project requires me to plot a column chart having minimum and maximum threshold values. I'm not too sure how to implement in the reporting service under the chart option.
I've tried another method where I plot a column chart and then I drew two lines on top of the column chart. When using RDL preview tab, I'm able to see the two lines. However once I deployed to the Reporting Server, the two lines are blocked by the Column Chart. I've tried to bring the two line front and send the column chart back but it is unsuccessful in viewing the lines.
Can anyone help? I'm fine with any method provided.
Thanks In Advnce...:)Pull up Chart Properties dialog, go to X/Y-axis tab(s) and set the
thresholds in Scale: Minimum and Maximum input textboxes.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samie" <Samie@.discussions.microsoft.com> wrote in message
news:3FE43F53-1DC3-4586-9BA4-5174FA2806E8@.microsoft.com...
> Hi there,
> My project requires me to plot a column chart having minimum and maximum
threshold values. I'm not too sure how to implement in the reporting
service under the chart option.
> I've tried another method where I plot a column chart and then I drew two
lines on top of the column chart. When using RDL preview tab, I'm able to
see the two lines. However once I deployed to the Reporting Server, the two
lines are blocked by the Column Chart. I've tried to bring the two line
front and send the column chart back but it is unsuccessful in viewing the
lines.
> Can anyone help? I'm fine with any method provided.
> Thanks In Advnce...:)|||Hi Ravi,
For the column chart, I need to see the whole range value with sub min and max threshold values in the chart. For example :
Y-axis - Total number of hours an employee worked
The single column bar is made up of : Area 1, Area 2 and Area 3
=> to see how much time each employee spend in the particular Area per day
In my case, Area 1 and Area 2 are consider employee's work area while Area 3 is consider non-work area. Thus by specifying a minimum threshold and maximum threshold, a supervisor is able to get an overview on the employee working performance. Below is a simple diagram, hope you can understand my requirement. Thanks for helping
(hrs)
18| | A3 |
--max threshold
| | A2 |
| | A1 |
-- min threshold
| | A1 |
0 -->days
Mon
where A1 : Area 1
A2 : Area 2
A3 : Area 3
"Ravi Mumulla (Microsoft)" wrote:
> Pull up Chart Properties dialog, go to X/Y-axis tab(s) and set the
> thresholds in Scale: Minimum and Maximum input textboxes.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Samie" <Samie@.discussions.microsoft.com> wrote in message
> news:3FE43F53-1DC3-4586-9BA4-5174FA2806E8@.microsoft.com...
> > Hi there,
> >
> > My project requires me to plot a column chart having minimum and maximum
> threshold values. I'm not too sure how to implement in the reporting
> service under the chart option.
> >
> > I've tried another method where I plot a column chart and then I drew two
> lines on top of the column chart. When using RDL preview tab, I'm able to
> see the two lines. However once I deployed to the Reporting Server, the two
> lines are blocked by the Column Chart. I've tried to bring the two line
> front and send the column chart back but it is unsuccessful in viewing the
> lines.
> >
> > Can anyone help? I'm fine with any method provided.
> > Thanks In Advnce...:)
>
>|||You might want to check out the attached sample report below. In particular
look at the "Target" series of the chart which simulates a threshold in a
column chart.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>Blue</BackgroundColor>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderColor>
<Default>Yellow</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderColor>
<Default>Green</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=100000</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>10pt</Default>
</BorderWidth>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style>
<Format>MM/yyyy</Format>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Column</Type>
<Title>
<Caption>Sales / Cost / Target</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)*100+Month(Fields!OrderDate.Va
lue)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!OrderDate.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
<StaticMember>
<Label>Target</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
My project requires me to plot a column chart having minimum and maximum threshold values. I'm not too sure how to implement in the reporting service under the chart option.
I've tried another method where I plot a column chart and then I drew two lines on top of the column chart. When using RDL preview tab, I'm able to see the two lines. However once I deployed to the Reporting Server, the two lines are blocked by the Column Chart. I've tried to bring the two line front and send the column chart back but it is unsuccessful in viewing the lines.
Can anyone help? I'm fine with any method provided.
Thanks In Advnce...:)Pull up Chart Properties dialog, go to X/Y-axis tab(s) and set the
thresholds in Scale: Minimum and Maximum input textboxes.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samie" <Samie@.discussions.microsoft.com> wrote in message
news:3FE43F53-1DC3-4586-9BA4-5174FA2806E8@.microsoft.com...
> Hi there,
> My project requires me to plot a column chart having minimum and maximum
threshold values. I'm not too sure how to implement in the reporting
service under the chart option.
> I've tried another method where I plot a column chart and then I drew two
lines on top of the column chart. When using RDL preview tab, I'm able to
see the two lines. However once I deployed to the Reporting Server, the two
lines are blocked by the Column Chart. I've tried to bring the two line
front and send the column chart back but it is unsuccessful in viewing the
lines.
> Can anyone help? I'm fine with any method provided.
> Thanks In Advnce...:)|||Hi Ravi,
For the column chart, I need to see the whole range value with sub min and max threshold values in the chart. For example :
Y-axis - Total number of hours an employee worked
The single column bar is made up of : Area 1, Area 2 and Area 3
=> to see how much time each employee spend in the particular Area per day
In my case, Area 1 and Area 2 are consider employee's work area while Area 3 is consider non-work area. Thus by specifying a minimum threshold and maximum threshold, a supervisor is able to get an overview on the employee working performance. Below is a simple diagram, hope you can understand my requirement. Thanks for helping
(hrs)
18| | A3 |
--max threshold
| | A2 |
| | A1 |
-- min threshold
| | A1 |
0 -->days
Mon
where A1 : Area 1
A2 : Area 2
A3 : Area 3
"Ravi Mumulla (Microsoft)" wrote:
> Pull up Chart Properties dialog, go to X/Y-axis tab(s) and set the
> thresholds in Scale: Minimum and Maximum input textboxes.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Samie" <Samie@.discussions.microsoft.com> wrote in message
> news:3FE43F53-1DC3-4586-9BA4-5174FA2806E8@.microsoft.com...
> > Hi there,
> >
> > My project requires me to plot a column chart having minimum and maximum
> threshold values. I'm not too sure how to implement in the reporting
> service under the chart option.
> >
> > I've tried another method where I plot a column chart and then I drew two
> lines on top of the column chart. When using RDL preview tab, I'm able to
> see the two lines. However once I deployed to the Reporting Server, the two
> lines are blocked by the Column Chart. I've tried to bring the two line
> front and send the column chart back but it is unsuccessful in viewing the
> lines.
> >
> > Can anyone help? I'm fine with any method provided.
> > Thanks In Advnce...:)
>
>|||You might want to check out the attached sample report below. In particular
look at the "Target" series of the chart which simulates a threshold in a
column chart.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>Blue</BackgroundColor>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderColor>
<Default>Yellow</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderColor>
<Default>Green</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=100000</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>10pt</Default>
</BorderWidth>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style>
<Format>MM/yyyy</Format>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Column</Type>
<Title>
<Caption>Sales / Cost / Target</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)*100+Month(Fields!OrderDate.Va
lue)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!OrderDate.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
<StaticMember>
<Label>Target</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
MIN and MAX strange results
I want to get the MIN and MAX value of a table column from a specified period of time. I execute a query and it return the result. The problem is that the values returned by MIN and MAX are not always correct!!
This is the result table
Date Statement From To
1 2007-01-03 00:00:00 Invoice 1 2 Correct
2 2007-01-04 00:00:00 Receipt 1 1 Correct
3 2007-01-04 00:00:00 Invoice 10 9 Wrong
4 2007-01-05 00:00:00 Receipt 2 5 Correct
5 2007-01-05 00:00:00 Invoice 100 99 Wrong
6 2007-01-08 00:00:00 Invoice 124 175 Correct
7 2007-01-09 00:00:00 Invoice 176 224 Correct
8 2007-01-10 00:00:00 Invoice 225 265 Correct
From =From Statement Number
To= To statement Number
The odd behavior happens when the number of digits changes. If the range of the column is 1 digit ie from 0 to 9 the values reported are ok. If the digits change then there is a problem as in line 3 and 5.
Any ideas why this odd behavior happens?
rectis:
I think you need to provide (1) the SQL Statement that is not working correctly and the definition of the table (or at least the relevant columns). My knee-jerk guess would be that you are coming to grief because your "From" and "To" fields are defined as varchar instead of numeric (or integer).
If in fact your "from" and "to" fields are defined as varchar you first need to make a determination to the usage of these fields -- that is see if the definition needs to be modified such that columns are reformatted into numeric (or integer) columns. You may need to compute your max as MIN(CONVERT(INTEGER, FROM)) and MAX(CONVERT(INTEGER,TO))
|||You are right. Thank you very much. I think my brain was stopped.The field was defined as varchar. Now the values are ok!Monday, March 12, 2012
Millisecond values missing when inserting datetime into datetime column of sql Server
Hi,
I'm inserting a datetime values into sql server 2000 from c#
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table name
ate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows
["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
Look at this post: http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk
You'll have to use a CAST or a CONVERT in that INSERT statement to the format you desire.
|||You have got the SQL Server part right but the .NET type you are using the wrong data type, to get milliseconds you have to use INT32, INT64 or Double the later two does not exist in SQL Server so you have to do conversion. I have found you two links with ready to use code, pay close attention to the string and formatting code. Hope this helps.
http://blogs.msdn.com/kathykam/archive/2006/09/29/.NET-Format-String-102_3A00_-DateTime-Format-String.aspx
http://authors.aspalliance.com/aspxtreme/sys/datetimeclass.aspx
Subscribe to:
Posts (Atom)