coverting to date problem

SELECT DISTINCT CAST(YEAR(classdate) AS varchar(4)) + '/' + CAST(MONTH(classdate) AS varchar(2)) as ok ,{fn MONTH(dbo.classgiven.classdate)} as monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL) order by 1", conNorthwind)

hello how do convert "CAST(YEAR(classdate) AS varchar(4)) + '/1/' + CAST(MONTH(classdate) AS varchar(2)) as ok" to a date in my sql statement?

so that it comes out as MM/DD/YYYY

thank you all

agian "OK" has to come out as a date..thanks

you can use

convert(varchar(20),classdate,101)

in you query, this will converts the date in the MM/dd/yyyy format.

|||

It looks as thoughclassdate is a date type anyway so, rather than create unnecessary function calls in your SQL statement, simply return the date and format it in whatever control you are using to display the data.

Counts on Subquerys

OK I have a query that bring back a unique identifier and text value
for instance

Select Distinct global_id, Page_url from PageList

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kindSELECT COUNT(DISTINCT Page_url) from PageList

??|||Originally posted by Brett Kaiser
SELECT COUNT(DISTINCT Page_url) from PageList

??

Brett, this doesn't seem to accomplich what I'm looking for. It's only bringing a count back of one for answerthis.htm, when there are clearly three records.

any suggestions|||I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Isn't the 1st row of your result set kind of ironic?|||Originally posted by Brett Kaiser
I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

The problem I'm having is that the unique global_id is breaking out each record on the group by. so when it is added i can't get a count of the page_url

Isn't the 1st row of your result set kind of ironic?|||Well was that it?|||How about this:

SELECT page_url, count(page_url)
FROM pagelist
group by page_url

counting records

Hi all,
I have a DTS package that gets information out of a Pervasive db and
drops it into SQL.
I need a count on how many distinct loan number are associated with
specific actions (ie Closing Date, Funded Date etc).
I have 2 tables: 1 contains a reference number and description (ie 170
= Closing Cost, 210 = Funded Date etc) and another table that contains
the reference number and loan number.
So I need a result set that says 20 loan have a closing date, 400 loans
have a funded date etc. There are over 590 reference numbers and over
1.5 million records.
Any help/advice would be immensely apprieciated!!
Thanks,
TonyTry,
select r.ref_desc, count(*) as cnt
from reference as r inner join loan as l
on r.ref_id = l.ref_id
group by r.ref_desc
go
AMB
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi Tony
You don't post ddl but you can do something like (untested):
SELECT [loan number], SUM(CASE WHEN [Closing Date] IS NOT NULL THEN 1 ELSE 0
END) AS NumClosed
SUM(CASE WHEN [Funded Date]IS NOT NULL THEN 1 ELSE 0 END) AS NumFunded
FROM #MyTable
GROUP BY [loan number]
John
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||On 26 Oct 2005 12:23:47 -0700, Tony wrote:

>Hi John,
>I thought of this but I have over 590 reference fields...don't feel
>like writing a sum/case for each one
Hi Tony,
Did you try Allejandro's suggestion?
If that doesn't help you, then please refer to www.aspfaq.com/5006 to
find out what extra information you need to post in order to help us
help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
590 columns in a table seems a large number. It is already causing you
problems by creating unwillingness try a solution!
You can use the information_schema.columns to create the sql for you.
e.g.
USE NORTHWIND
SELECT 'CASE WHEN ' + QUOTENAME (COLUMN_NAME) + ' IS NULL THEN 0 ELSE 1
END AS [SUM_' + COLUMN_NAME + '],'
from informatioN_schema.columns
where table_name = 'orders'
ORDER BY ORDINAL_POSITION
John
Tony wrote:
> Hi John,
> I thought of this but I have over 590 reference fields...don't feel
> like writing a sum/case for each one

counting distint records

i have an sql statement like this:

SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition

i simply want to modify it to give me the count, i tried this, but it doesn't work:

SELECT count (distinct store, dept, sku)
FROM some_table
WHERE some_condition

what am i doing wrong? thank you very much for your help.This is a pure guess, but do you want something like:SELECT Count(*), store, dept, sku
FROM some_table
WHERE 1 = 1 -- or some other condition of your choosing
GROUP BY store, dept, sku-PatP|||thanks for the help, yeah i think that will work, but i ended up doing something like this:

SELECT count(1)
FROM ( SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
)

counting distincts

This won't work...
SELECT count(distinct z.id, c.alias, c.type)
FROM customer c JOIN zsource z ON c.id=z.id
How can I count the number of distinct combinations of 3 separate fields?This should work :
SELECT count(*) FROM (
SELECT distinct z.id, c.alias, c.type
FROM customer c JOIN zsource z ON c.id=z.id) a
-Nags
"Dave" <dave@.nodomain.tv> wrote in message
news:#H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
> This won't work...
> SELECT count(distinct z.id, c.alias, c.type)
> FROM customer c JOIN zsource z ON c.id=z.id
> How can I count the number of distinct combinations of 3 separate fields?
>|||This is one way. There could be other better ways.
select count(RecCount)
from (select count(*) as RecCount
from customer c JOIN zsource z ON c.id=z.id
group by z.id, c.alias, c.type) d
"Dave" <dave@.nodomain.tv> wrote in message
news:%23H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
> This won't work...
> SELECT count(distinct z.id, c.alias, c.type)
> FROM customer c JOIN zsource z ON c.id=z.id
> How can I count the number of distinct combinations of 3 separate fields?
>|||Thanks much
"Nags" <nags@.DontSpamMe.com> wrote in message
news:O4lRaPcFEHA.2560@.TK2MSFTNGP12.phx.gbl...
> This should work :
> SELECT count(*) FROM (
> SELECT distinct z.id, c.alias, c.type
> FROM customer c JOIN zsource z ON c.id=z.id) a
> -Nags
> "Dave" <dave@.nodomain.tv> wrote in message
> news:#H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
fields?
>

counting distinct group rows

I have a report with several groups. the last group (3rd group) displays totals so I only have a group footer displaying. This group only displays 2 rows which are totals for that employee. However in the 2nd Group footer when I try to get a count of the number of rows, which in this case is only 2, it returns 58. I think that the group has a total of 58 records that are summed up into tow rows. I want the number 2 not 58. How can I do this?See the attached example.
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"RobbRoss" <RobbRoss@.discussions.microsoft.com> wrote in message
news:67558989-66BA-4C15-AA3A-7888168523C6@.microsoft.com...
> I have a report with several groups. the last group (3rd group) displays
totals so I only have a group footer displaying. This group only displays 2
rows which are totals for that employee. However in the 2nd Group footer
when I try to get a count of the number of rows, which in this case is only
2, it returns 58. I think that the group has a total of 58 records that are
summed up into tow rows. I want the number 2 not 58. How can I do this?|||Can't see the example here on the MSDN Discussion groups any chance of pasting it in.
"RobbRoss" wrote:
> I have a report with several groups. the last group (3rd group) displays totals so I only have a group footer displaying. This group only displays 2 rows which are totals for that employee. However in the 2nd Group footer when I try to get a count of the number of rows, which in this case is only 2, it returns 58. I think that the group has a total of 58 records that are summed up into tow rows. I want the number 2 not 58. How can I do this?|||Here's an example:
<?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>
<Table Name="table1">
<Height>1.5in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Value>state</Value>
<CanGrow>true</CanGrow>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>city</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>au lname</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="state">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>state</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="city">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>city</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="au_fname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<Color>DarkGreen</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>au_fname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!au_fname.Value & " " &
Fields!au_fname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>pubs</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<Color>Crimson</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!state.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!state.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>PapayaWhip</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Default>Solid</Default>
<Top>Solid</Top>
<Right>None</Right>
<Left>None</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>PapayaWhip</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Default>Solid</Default>
<Top>Solid</Top>
<Right>None</Right>
<Left>None</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>PapayaWhip</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>8pt</FontSize>
<Color>Crimson</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="State total = " &
CountRows("table1_Group1")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<Color>Tomato</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!city.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!city.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Khaki</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Khaki</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Khaki</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>8pt</FontSize>
<Color>Tomato</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="City total = " &
CountRows("table1_Group2")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="pubs">
<rd:DataSourceID>dc55d7a5-424d-4a42-8b88-fded81a53060</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=pubs</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.00001in</Width>
<DataSets>
<DataSet Name="pubs">
<Fields>
<Field Name="au_id">
<DataField>au_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_lname">
<DataField>au_lname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_fname">
<DataField>au_fname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="phone">
<DataField>phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="address">
<DataField>address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="city">
<DataField>city</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="state">
<DataField>state</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="zip">
<DataField>zip</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="contract">
<DataField>contract</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>pubs</DataSourceName>
<CommandText>SELECT *
FROM authors</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>6b64ca82-e1a6-41df-950f-fe0d4cff930f</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Another frustrated developer"
<Anotherfrustrateddeveloper@.discussions.microsoft.com> wrote in message
news:FF5BFD9B-28DC-4656-90E8-51E28F0C500A@.microsoft.com...
> Can't see the example here on the MSDN Discussion groups any chance of
pasting it in.
> "RobbRoss" wrote:
> > I have a report with several groups. the last group (3rd group)
displays totals so I only have a group footer displaying. This group only
displays 2 rows which are totals for that employee. However in the 2nd
Group footer when I try to get a count of the number of rows, which in this
case is only 2, it returns 58. I think that the group has a total of 58
records that are summed up into tow rows. I want the number 2 not 58. How
can I do this?|||RobbRoss:
I read your June 28 posting about the csv export -- I am having the same
problem that you had back in June. How did you go about solving that problem?
Would you please kindly share it with me.
Thanks,
John|||Append this string to the report URL:
&rs%3aCommand=Render&rs%3AFormat=CSV&rc%3ANoHeader=true
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:125676B7-25E4-44BA-84D1-2813B6EC767E@.microsoft.com...
> RobbRoss:
> I read your June 28 posting about the csv export -- I am having the same
> problem that you had back in June. How did you go about solving that
problem?
> Would you please kindly share it with me.
> Thanks,
> John
>

Counting based on a field combination

Having a brainfart...

I need a query that returns a record count, based on two distinct fields.

For example:

Order Revision Customer
001 1 Bob
001 2 Bob
002 1 John
003 1 John
004 1 John
005 1 Bob
006 1 Bob
006 2 Bob

The query on the above data should return a count of orders, regardless of
the revision numbers (each order number should only be counted once).

So WHERE Customer = 'Bob', it should return OrderCount = 3

TIA!

Calan> The query on the above data should return a count of orders,
regardless of
> the revision numbers (each order number should only be counted once).
> So WHERE Customer = 'Bob', it should return OrderCount = 3
> TIA!
> Calan

--something like this?

create table #foo (
Ord int,
Revision int,
Customer varchar(10)
primary key (ord,revision)
)
GO

insert into #foo( Ord, Revision, Customer )Values(001, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(001, 2, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(002, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(003, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(004, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(005, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 2, 'Bob' )

select customer,count( distinct ord) as 'orders'
from #foo
group by customer

drop table #foo
go

--Strider|||Please give proper DDL with your posts, including the definition of
keys and constraints. They can make a big difference to the answer.
Post sample data as INSERT statements so that we can test out solutions
with your data.

Assuming the key in this case is (order,revision):

SELECT customer, count(*)
FROM Orders
GROUP BY customer

If I'm wrong about the key, try:

SELECT customer, count(DISTINCT order)
FROM Orders
GROUP BY customer

(both untested)

The COUNT(DISTINCT...) version typically runs significantly slower.

--
David Portas
SQL Server MVP
--