Minimum/Maximum of two values

Hallo Cracks,

what I try is a little bit heavy, maybe, but I only miss the minimum/maximum fuction - or I didn´t found it; not here in the Forum and also not in the onlinehelp of the SQL Server.

What I try to do:

I have 2 columns in my table; a start- and an end-date. For this period of time between end and start i have to calculate the days for the years. Here my thoughts (for the current year):

Is the startdate <= 31.12.2004 and the enddate >= 1.1.2004 i have to calculate die datediff between max(1.1.2004/startdate) and min(31.12.2004/enddate)

like this sqlstatement:

SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS
varchar) + '-31-12' AS smalldatetime) AND dbo.Phases.phasenabschlussist >=
CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN 365 ELSE 0 END AS Expr2,
FROM dbo.Phases
WHERE (phasenstart IS NOT NULL) AND (phasenabschlussist IS NOT NULL)

instead of 365 there must be the above calculation. Is start=3.1.2003 and end=30.1.2004 I expect as result only the 30 days in 2004.

thanks in advance and kind regards :-)
Cappuhave a look at MAX, MIN, and DATEDIFF functions in SQL Server Books Online|||thanks, but Max and Min exists only as aggregate functions.

I have a solution now with CASE, it works but it looks terrible ;-)

here is it:

SELECT CASE WHEN dbo.Phases.phasenstart <= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime) AND
dbo.Phases.phasenabschlussist >= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime) THEN DATEDIFF(day,
CASE WHEN dbo.Phases.phasenstart >= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime)
THEN dbo.Phases.phasenstart ELSE CAST(CAST(YEAR(GETDATE()) AS varchar) + '-01-01' AS smalldatetime) END,
CASE WHEN dbo.Phases.phasenabschlussist <= CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime)
THEN dbo.Phases.phasenabschlussist ELSE CAST(CAST(YEAR(GETDATE()) AS varchar) + '-31-12' AS smalldatetime) END)
+ 1 ELSE 0 END AS TageISTJahr0
FROM dbo.Phases´

it works, but do i understand in two years what i did there? ;-)

min/max of x minutes

I am trying to develop a sql statement that will create a recordset of the min (or max) values in x minute increments over a period of time.

e.g. over a period of 7 days, I have data that was collected in 1 minute intervals. I need to know the min (or max) value in each 10 minute interval over that same period of time.

Is there an efficient way of doing this?

Try something like this:

DECLARE @.dt_from DATETIME, @.dt_to DATETIME;
SET @.dt_from = '2006-03-21T00:00:00.000';
SET @.dt_to = '2006-03-28T00:00:00.000';

SELECT MIN(dt) AS dt,
MIN(val) AS min_val, MAX(val) AS max_val
FROM tbl
WHERE dt >= @.dt_from
AND dt < @.dt_to
GROUP BY FLOOR(DATEDIFF(MINUTE,@.dt_from,dt)/10) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

|||Just recently had a chance to try this out. It worked perfectly. Thanks!

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>

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#

SQL server table details
Table nameate_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