Max Dates For Cost Query

Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

DavidOriginally posted by hidvegi
Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

David

SELECT
T1.PART,
T1.COST,
T1.DATE
FROM
[DATA/COSTFILE] AS T1
WHERE
T1.DATE= (
SELECT MAX(T2.DATE) AS MAXDATE
FROM DATA/COSTFILE AS T2
WHERE T2.PART=T1.PART)

FYI, you shouldn't use ANY special characters in your column names or table names, especially "/\-_.,&%@.+"=".

The only exception to this is "_" which can be used after for sp_name on stored procedures that must reside in master and run on every database.

Max date in a row

Hi,
I would like to know how to find the max of 4 dates in one row.
so if we have ssn, date1, date2, date3, date4
456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
my output should give me
456123789, 1/2/2006
ThanksCREATE TABLE #foo
(
ssn CHAR(9),
d1 SMALLDATETIME,
d2 SMALLDATETIME,
d3 SMALLDATETIME,
d4 SMALLDATETIME
);
SET NOCOUNT ON;
INSERT #foo SELECT '111111111', '20050101', '20050505', '20050603',
'20050401';
INSERT #foo SELECT '222222222', '20050601', '20050505', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050601', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050602', '20050603',
'20050604';
SELECT ssn, d = MAX(d)
FROM
(
SELECT ssn, d = d1 FROM #foo
UNION ALL SELECT ssn, d = d2 FROM #foo
UNION ALL SELECT ssn, d = d3 FROM #foo
UNION ALL SELECT ssn, d = d4 FROM #foo
) x
GROUP BY ssn;
DROP TABLE #foo;
Can I recommend this structure instead:
CREATE TABLE dbo.People
(
ssn CHAR(9) PRIMARY KEY
);
CREATE TABLE dbo.PeopleDates
(
ssn CHAR(9) NOT NULL FOREIGN KEY REFERENCES dbo.People(ssn),
dateInstance TINYINT NOT NULL, -- check for 1-4?
dateValue SMALLDATETIME
);
INSERT dbo.People
SELECT '111111111'
UNION ALL SELECT '222222222'
UNION ALL SELECT '333333333';
INSERT dbo.PeopleDates
SELECT '111111111', 1, '20050101'
UNION ALL SELECT '111111111', 2, '20050505';
/* ...... */
More work up front, and slightly larger storage cost (though you could
offset that a bit by using an INT for the key), but it is more relational in
nature, and look how easy it makes your queries:
SELECT ssn, MAX(dateValue)
FROM dbo.PeopleDates
GROUP BY ssn;
And as well as making this type of query much simpler, you don't have to go
change things when you add a 5th date. (In your current model, you need to
change the schema *and* change the query.)
In addition, I encourage not thinking about dates in these string formats,
or at least when you are explaining an issue to other people, to avoid
confusion and ambiguity. Are your dates:
(a) Mar 12 2005, May 12 2005, Aug 11 2005, Feb 1 2006
or
(b) Dec 3 2005, Dec 5 2005, Nov 8 2005, Jan 2 2005
?
In this case, it was easy to pick out the latest date you expected in the
result, because it was the only one in 2006. But if you included 2/1/2006
as well, I'd be at a loss without requesting further clarification.
You should strive to use string representations of dates that are 100%
unambiguous to both people and code. For example, 'YYYYMMDD' will always
work, no matter who you're talking to or what your SQL Server's regional
settings, dateformat, language, etc.
A
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:8E25E6E0-E4E0-4739-908A-B995314FFFF6@.microsoft.com...
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks|||Amit wrote:
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks
Table design aside, you can use a scalar function here:
Select
ssn,
dbo.fnGetMaxDate(date1, date2, date3, date4)
From
dbo.MyTable
Create Function dbo.fnGetMaxDate (
@.date1 datetime, @.date2 datetime, @.date3 datetime, @.date4 datetime )
Returns datetime
as
Begin
declare @.datefinal datetime
set @.datefinal = @.date1
If @.date2 > @.datefinal
set @.datefinal = @.date2
If @.date3 > @.datefinal
set @.datefinal = @.date3
If @.date4 > @.datefinal
set @.datefinal = @.date4
Return @.datefinal
End
David Gugick - SQL Server MVP
Quest Software|||SELECT SSN,
CASE WHEN date1 > date2
AND date1 > date3
AND date1 > date4
THEN date1
WHEN date2 > date3
AND date2 > date4
THEN date2
WHEN date3 > date4
THEN date3
ELSE date4
END as MaxDate
FROM SomeTable
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 11:35:02 -0800, "Amit"
<Amit@.discussions.microsoft.com> wrote:

>Hi,
>I would like to know how to find the max of 4 dates in one row.
>so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
>my output should give me
>456123789, 1/2/2006
>Thanks|||Another approach:
SELECT ssn,
MAX( CASE n WHEN 1 THEN dt1
WHEN 2 THEN dt2
WHEN 3 THEN dt3
WHEN 4 THEN dt4
END )
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) N ( n )
GROUP BY ssn ;
Anith

Matrix Totals - left formatted

I have successfully created a matrix consisting of payroll dates as column
headers, with task types as my rows and hours/task/day the detail data. The
columns total perfectly, but the totals only display to the RIGHT of all the
data. We display our totals FIRST, then the detail data. Can I do this?
total hrs day 1 day 2
task 1 16 8 8Yes. Click on the little green triangle in the (row/column) heading to get
the subtotal properties and look at the properties window. There is a
"Position" property which is set to "After" by default. You can set it to
"Before", which gives you the effect you want.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
>I have successfully created a matrix consisting of payroll dates as column
> headers, with task types as my rows and hours/task/day the detail data.
> The
> columns total perfectly, but the totals only display to the RIGHT of all
> the
> data. We display our totals FIRST, then the detail data. Can I do this?
> total hrs day 1 day 2
> task 1 16 8 8|||Thank you. Wasn't sure what "Position" meant. I appreciate your quick
response.
"Robert Bruckner [MSFT]" wrote:
> Yes. Click on the little green triangle in the (row/column) heading to get
> the subtotal properties and look at the properties window. There is a
> "Position" property which is set to "After" by default. You can set it to
> "Before", which gives you the effect you want.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
> news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
> >I have successfully created a matrix consisting of payroll dates as column
> > headers, with task types as my rows and hours/task/day the detail data.
> > The
> > columns total perfectly, but the totals only display to the RIGHT of all
> > the
> > data. We display our totals FIRST, then the detail data. Can I do this?
> > total hrs day 1 day 2
> > task 1 16 8 8
>
>

Matrix hide zeros

Hi,
I have built a matrix report that counts the number of orders in a day by
customer and displays all dates regardless of whether any orders were placed
then. I would like to hide the zeros for customers without orders, but I
cannot figure out how to filter those out or alternately make the zeros
display as white on a white background.
I want it to look like this:
1/1/07 1/2/07 1/3/07 1/4/07 1/5/07 Totals
Acme 1 1 4
7
Bernett 2 1 3 2
7
Chapmen 5 1 2
8
Totals 3 6 5 0 8
22
Any help is greatly appreciated!
KathyI can think of three options.
1. Alter the query to return NULL where the value is 0
2. Place a filter expression on the Dataset in Reporting Services. This is
accessed from the Data tab
3. Place an expression on the cell in the Matrix layout to test for 0 and
return nothing or a blank space
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:2C3FB54D-8014-4771-A15E-C22551196B0D@.microsoft.com...
> Hi,
> I have built a matrix report that counts the number of orders in a day by
> customer and displays all dates regardless of whether any orders were
> placed
> then. I would like to hide the zeros for customers without orders, but I
> cannot figure out how to filter those out or alternately make the zeros
> display as white on a white background.
> I want it to look like this:
> 1/1/07 1/2/07 1/3/07 1/4/07 1/5/07
> Totals
> Acme 1 1
> 4
> 7
> Bernett 2 1 3
> 2
> 7
> Chapmen 5 1 2
> 8
> Totals 3 6 5 0
> 8
> 22
> Any help is greatly appreciated!
> Kathy|||Did you try to don't select the lines at zero ?
SELECT * FROM [table] WHERE
[field]>0
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:2C3FB54D-8014-4771-A15E-C22551196B0D@.microsoft.com...
> Hi,
> I have built a matrix report that counts the number of orders in a day by
> customer and displays all dates regardless of whether any orders were
> placed
> then. I would like to hide the zeros for customers without orders, but I
> cannot figure out how to filter those out or alternately make the zeros
> display as white on a white background.
> I want it to look like this:
> 1/1/07 1/2/07 1/3/07 1/4/07 1/5/07
> Totals
> Acme 1 1
> 4
> 7
> Bernett 2 1 3
> 2
> 7
> Chapmen 5 1 2
> 8
> Totals 3 6 5 0
> 8
> 22
> Any help is greatly appreciated!
> Kathy

Matrix Cross-tab Report Not Counting Records

I am new to Reporting Services and cross-tab reporting in general. I
need to create a basic report that has dates across the top and reps
down the left. The data I would like to see reflected is the number of
entries created in the table by rep by day with cross sums. What I am
getting is a report that has a column for every entry with the date
repeated over and over and no totals, only 1 in each column.
Visually I need to see:
Day 1 Day 2 Day 3 Total
__________________________________
Rep1 4 10 1 15
Rep2 2 4 3 9
Total 6 14 4 24
What I am getting is a report where Day 1 would be repeated 6 times
with an entry of 1.
Can someone point me in the right direction?I would like to add that my table uses a datetime field that has a
unique value for each record. I do need a specific timestamp.
Probably part of my problem, but how do I resolve it?
zanecolvin@.gmail.com wrote:
> I am new to Reporting Services and cross-tab reporting in general. I
> need to create a basic report that has dates across the top and reps
> down the left. The data I would like to see reflected is the number of
> entries created in the table by rep by day with cross sums. What I am
> getting is a report that has a column for every entry with the date
> repeated over and over and no totals, only 1 in each column.
> Visually I need to see:
> Day 1 Day 2 Day 3 Total
> __________________________________
> Rep1 4 10 1 15
> Rep2 2 4 3 9
> Total 6 14 4 24
> What I am getting is a report where Day 1 would be repeated 6 times
> with an entry of 1.
> Can someone point me in the right direction?|||Solved my own problem. Created a view converting data type from and
back to datetime to get 12:00 time on all entries. Used that for the
report.
zanecolvin@.gmail.com wrote:
> I would like to add that my table uses a datetime field that has a
> unique value for each record. I do need a specific timestamp.
> Probably part of my problem, but how do I resolve it?
>

Matix Grouping error need advice fast

SQLServer 2000 SP4 with RS SP2:
I have a matrix that displays URLs and Dates. The grouping is by URL
within Date. The URL is being formatted through custom code that strips
of everything after the first '/' ignoring 'http://' as below:
<Code>
Public Function FormatUrl(ByRef url As String) As String
Dim r As New
System.Text.RegularExpressions.Regex("[^http://].*/|[^http://].*\?",
System.Text.RegularExpressions.RegexOptions.IgnoreCase)
Dim m As System.Text.RegularExpressions.Match = r.Match(url)
return m.ToString()
End Function
</Code>
When I display the URL as in
<Textbox Name="Address">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Address</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Code.FormatUrl(Fields!HttpReferrer.Value)</Value>
</Textbox>
It works as I hoped but when I try to group the matrix of off
'=Code.FormatUrl(Fields!HttpReferrer.Value)' as:
<Grouping Name="matrix1_DateRow">
<GroupExpressions>
<GroupExpression>=Code.FormatUrl(Fields!HttpReferrer.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
the report compiles, but throughs an exception at runtime as below:
--
Processing Errors
--
An error has occurred during report processing.
Exception of type
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
was thrown.
--
OK
--
Can someone explain why I am getting this exception? Can you group off
of this type of expression?
Thanks for any helpI'm not sure, I'd call MS Support. The help doesn't offer a whole lot...
http://msdn2.microsoft.com/zh-cn/library/ms153581.aspx
Steve MunLeeuw
"p91473" <p91473@.sbcglobal.net> wrote in message
news:1159896928.329150.68180@.h48g2000cwc.googlegroups.com...
> SQLServer 2000 SP4 with RS SP2:
> I have a matrix that displays URLs and Dates. The grouping is by URL
> within Date. The URL is being formatted through custom code that strips
> of everything after the first '/' ignoring 'http://' as below:
> <Code>
> Public Function FormatUrl(ByRef url As String) As String
> Dim r As New
> System.Text.RegularExpressions.Regex("[^http://].*/|[^http://].*\?",
> System.Text.RegularExpressions.RegexOptions.IgnoreCase)
> Dim m As System.Text.RegularExpressions.Match = r.Match(url)
> return m.ToString()
> End Function
> </Code>
> When I display the URL as in
> <Textbox Name="Address">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <FontSize>9pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>Address</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Code.FormatUrl(Fields!HttpReferrer.Value)</Value>
> </Textbox>
> It works as I hoped but when I try to group the matrix of off
> '=Code.FormatUrl(Fields!HttpReferrer.Value)' as:
> <Grouping Name="matrix1_DateRow">
> <GroupExpressions>
> <GroupExpression>=Code.FormatUrl(Fields!HttpReferrer.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> the report compiles, but throughs an exception at runtime as below:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Exception of type
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> was thrown.
> --
> OK
> --
> Can someone explain why I am getting this exception? Can you group off
> of this type of expression?
> Thanks for any help
>|||Seems like what you are doing is correct, can you try a dummy select
statement with the strings?
SELECT 'http://foo.com'
UNION
SELECT 'http://foo.a.com'
UNION
SELECT 'http://foo.c.com'
UNION
SELECT 'http://foo.b.com'
Steve MunLeeuw
"p91473" <p91473@.sbcglobal.net> wrote in message
news:1159896928.329150.68180@.h48g2000cwc.googlegroups.com...
> SQLServer 2000 SP4 with RS SP2:
> I have a matrix that displays URLs and Dates. The grouping is by URL
> within Date. The URL is being formatted through custom code that strips
> of everything after the first '/' ignoring 'http://' as below:
> <Code>
> Public Function FormatUrl(ByRef url As String) As String
> Dim r As New
> System.Text.RegularExpressions.Regex("[^http://].*/|[^http://].*\?",
> System.Text.RegularExpressions.RegexOptions.IgnoreCase)
> Dim m As System.Text.RegularExpressions.Match = r.Match(url)
> return m.ToString()
> End Function
> </Code>
> When I display the URL as in
> <Textbox Name="Address">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <FontSize>9pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>Address</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Code.FormatUrl(Fields!HttpReferrer.Value)</Value>
> </Textbox>
> It works as I hoped but when I try to group the matrix of off
> '=Code.FormatUrl(Fields!HttpReferrer.Value)' as:
> <Grouping Name="matrix1_DateRow">
> <GroupExpressions>
> <GroupExpression>=Code.FormatUrl(Fields!HttpReferrer.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> the report compiles, but throughs an exception at runtime as below:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Exception of type
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException
> was thrown.
> --
> OK
> --
> Can someone explain why I am getting this exception? Can you group off
> of this type of expression?
> Thanks for any help
>