Count Records in a Group

Hello All.
I'm running SSRS 2005 on top of a sql2000 database.
I need to create a report that counts the number of invoices salesreps
process per month.
The fields I have selected are;
SalesRep ID Invoice No Invoice Date
1 5467 2/22/2006 3:06:17 PM
5 4526 2/22/2006 3:29:56 PM
8 6589 6/14/2005 4:20:26 PM
5 8569 2/22/2006 3:29:56 PM
5 2563 6/10/2007 8:29:56 AM
5 1523 2/22/2006 3:29:56 PM
8 9876 8/23/2006 5:29:56 PM
1 7563 4/23/2006 1:29:56 PM
What i want to do is group by Salesrep ID, then show the total number of
invoices that rep did per month;
SalesRep ID Month TotalInvoices Processed
1 Jan 4
Feb 2
Mar 5
5 Jan 5
Feb 6
Mar 3
8 Jan 2
Feb 10
Mar 20 ... and so on.
I'm not sure exactly how to do this. Also how do I convert the date format
into just showing the month, not every minute of every day?
Any help is much appreciated.
Thanks.You want to do this with the sql statement. Since you didn't show your SQL I
had to make up names.
select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
invoices_count from yourtable a
group by a.salesrepid, datepart(month, a.invoicedate)
order by a.salesrepid, month
Note this gives you month by number which is what you need in order to order
it properly, if you want by month name then add that in and still order by
the month number to keep in in the proper order.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> Hello All.
> I'm running SSRS 2005 on top of a sql2000 database.
> I need to create a report that counts the number of invoices salesreps
> process per month.
> The fields I have selected are;
> SalesRep ID Invoice No Invoice Date
>
> 1 5467 2/22/2006 3:06:17 PM
> 5 4526 2/22/2006 3:29:56 PM
> 8 6589 6/14/2005 4:20:26 PM
> 5 8569 2/22/2006 3:29:56 PM
> 5 2563 6/10/2007 8:29:56 AM
> 5 1523 2/22/2006 3:29:56 PM
> 8 9876 8/23/2006 5:29:56 PM
> 1 7563 4/23/2006 1:29:56 PM
> What i want to do is group by Salesrep ID, then show the total number of
> invoices that rep did per month;
> SalesRep ID Month TotalInvoices Processed
> 1 Jan 4
> Feb 2
> Mar 5
> 5 Jan 5
> Feb 6
> Mar 3
> 8 Jan 2
> Feb 10
> Mar 20 ... and so on.
>
> I'm not sure exactly how to do this. Also how do I convert the date format
> into just showing the month, not every minute of every day?
> Any help is much appreciated.
> Thanks.
>|||Thank you soo much Bruce.
Here is the statement;
SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
dbo.invoice_hdr.order_date
FROM dbo.contacts INNER JOIN
dbo.invoice_hdr ON dbo.contacts.id =dbo.invoice_hdr.salesrep_id
"Bruce L-C [MVP]" wrote:
> You want to do this with the sql statement. Since you didn't show your SQL I
> had to make up names.
> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> invoices_count from yourtable a
> group by a.salesrepid, datepart(month, a.invoicedate)
> order by a.salesrepid, month
> Note this gives you month by number which is what you need in order to order
> it properly, if you want by month name then add that in and still order by
> the month number to keep in in the proper order.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > Hello All.
> >
> > I'm running SSRS 2005 on top of a sql2000 database.
> >
> > I need to create a report that counts the number of invoices salesreps
> > process per month.
> >
> > The fields I have selected are;
> >
> > SalesRep ID Invoice No Invoice Date
> >
> >
> > 1 5467 2/22/2006 3:06:17 PM
> > 5 4526 2/22/2006 3:29:56 PM
> > 8 6589 6/14/2005 4:20:26 PM
> > 5 8569 2/22/2006 3:29:56 PM
> > 5 2563 6/10/2007 8:29:56 AM
> > 5 1523 2/22/2006 3:29:56 PM
> > 8 9876 8/23/2006 5:29:56 PM
> > 1 7563 4/23/2006 1:29:56 PM
> >
> > What i want to do is group by Salesrep ID, then show the total number of
> > invoices that rep did per month;
> >
> > SalesRep ID Month TotalInvoices Processed
> > 1 Jan 4
> > Feb 2
> > Mar 5
> >
> > 5 Jan 5
> > Feb 6
> > Mar 3
> >
> > 8 Jan 2
> > Feb 10
> > Mar 20 ... and so on.
> >
> >
> > I'm not sure exactly how to do this. Also how do I convert the date format
> > into just showing the month, not every minute of every day?
> >
> > Any help is much appreciated.
> > Thanks.
> >
>
>|||Bruce that worked perfectly!!
Thanks again!
"Damon Johnson" wrote:
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
> > You want to do this with the sql statement. Since you didn't show your SQL I
> > had to make up names.
> >
> > select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> > invoices_count from yourtable a
> > group by a.salesrepid, datepart(month, a.invoicedate)
> > order by a.salesrepid, month
> >
> > Note this gives you month by number which is what you need in order to order
> > it properly, if you want by month name then add that in and still order by
> > the month number to keep in in the proper order.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> > news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > > Hello All.
> > >
> > > I'm running SSRS 2005 on top of a sql2000 database.
> > >
> > > I need to create a report that counts the number of invoices salesreps
> > > process per month.
> > >
> > > The fields I have selected are;
> > >
> > > SalesRep ID Invoice No Invoice Date
> > >
> > >
> > > 1 5467 2/22/2006 3:06:17 PM
> > > 5 4526 2/22/2006 3:29:56 PM
> > > 8 6589 6/14/2005 4:20:26 PM
> > > 5 8569 2/22/2006 3:29:56 PM
> > > 5 2563 6/10/2007 8:29:56 AM
> > > 5 1523 2/22/2006 3:29:56 PM
> > > 8 9876 8/23/2006 5:29:56 PM
> > > 1 7563 4/23/2006 1:29:56 PM
> > >
> > > What i want to do is group by Salesrep ID, then show the total number of
> > > invoices that rep did per month;
> > >
> > > SalesRep ID Month TotalInvoices Processed
> > > 1 Jan 4
> > > Feb 2
> > > Mar 5
> > >
> > > 5 Jan 5
> > > Feb 6
> > > Mar 3
> > >
> > > 8 Jan 2
> > > Feb 10
> > > Mar 20 ... and so on.
> > >
> > >
> > > I'm not sure exactly how to do this. Also how do I convert the date format
> > > into just showing the month, not every minute of every day?
> > >
> > > Any help is much appreciated.
> > > Thanks.
> > >
> >
> >
> >|||First, you do not need TOP 100 percent. That means you want all the records
which is what you get without the TOP syntax.
SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
datename(month,b.order_date) as Month_Name,count(*) as invoices_count
FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id =b.salesrep_id
where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
group by a.salesrep_id, datepart(month,b.order_date)
order by a.salesrep_id, month
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
>> You want to do this with the sql statement. Since you didn't show your
>> SQL I
>> had to make up names.
>> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
>> invoices_count from yourtable a
>> group by a.salesrepid, datepart(month, a.invoicedate)
>> order by a.salesrepid, month
>> Note this gives you month by number which is what you need in order to
>> order
>> it properly, if you want by month name then add that in and still order
>> by
>> the month number to keep in in the proper order.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
>> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
>> > Hello All.
>> >
>> > I'm running SSRS 2005 on top of a sql2000 database.
>> >
>> > I need to create a report that counts the number of invoices salesreps
>> > process per month.
>> >
>> > The fields I have selected are;
>> >
>> > SalesRep ID Invoice No Invoice Date
>> >
>> >
>> > 1 5467 2/22/2006 3:06:17 PM
>> > 5 4526 2/22/2006 3:29:56 PM
>> > 8 6589 6/14/2005 4:20:26 PM
>> > 5 8569 2/22/2006 3:29:56 PM
>> > 5 2563 6/10/2007 8:29:56 AM
>> > 5 1523 2/22/2006 3:29:56 PM
>> > 8 9876 8/23/2006 5:29:56 PM
>> > 1 7563 4/23/2006 1:29:56 PM
>> >
>> > What i want to do is group by Salesrep ID, then show the total number
>> > of
>> > invoices that rep did per month;
>> >
>> > SalesRep ID Month TotalInvoices Processed
>> > 1 Jan 4
>> > Feb 2
>> > Mar 5
>> >
>> > 5 Jan 5
>> > Feb 6
>> > Mar 3
>> >
>> > 8 Jan 2
>> > Feb 10
>> > Mar 20 ... and so on.
>> >
>> >
>> > I'm not sure exactly how to do this. Also how do I convert the date
>> > format
>> > into just showing the month, not every minute of every day?
>> >
>> > Any help is much appreciated.
>> > Thanks.
>> >
>>|||OK Bruce I think this is my last request.
the DATENAME(month, invoice_date) returns the month only.
How do i get it to return the month and year. I will be setting this up as a
parameter query;
Between @.StartDate and @.EndDate
and want the user to put in May 07 and June 07
thanks again.
"Bruce L-C [MVP]" wrote:
> First, you do not need TOP 100 percent. That means you want all the records
> which is what you get without the TOP syntax.
> SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
> datename(month,b.order_date) as Month_Name,count(*) as invoices_count
> FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id => b.salesrep_id
> where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
> group by a.salesrep_id, datepart(month,b.order_date)
> order by a.salesrep_id, month
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> > Thank you soo much Bruce.
> > Here is the statement;
> >
> > SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> > dbo.invoice_hdr.order_date
> > FROM dbo.contacts INNER JOIN
> > dbo.invoice_hdr ON dbo.contacts.id => > dbo.invoice_hdr.salesrep_id
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You want to do this with the sql statement. Since you didn't show your
> >> SQL I
> >> had to make up names.
> >>
> >> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> >> invoices_count from yourtable a
> >> group by a.salesrepid, datepart(month, a.invoicedate)
> >> order by a.salesrepid, month
> >>
> >> Note this gives you month by number which is what you need in order to
> >> order
> >> it properly, if you want by month name then add that in and still order
> >> by
> >> the month number to keep in in the proper order.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> >> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> >> > Hello All.
> >> >
> >> > I'm running SSRS 2005 on top of a sql2000 database.
> >> >
> >> > I need to create a report that counts the number of invoices salesreps
> >> > process per month.
> >> >
> >> > The fields I have selected are;
> >> >
> >> > SalesRep ID Invoice No Invoice Date
> >> >
> >> >
> >> > 1 5467 2/22/2006 3:06:17 PM
> >> > 5 4526 2/22/2006 3:29:56 PM
> >> > 8 6589 6/14/2005 4:20:26 PM
> >> > 5 8569 2/22/2006 3:29:56 PM
> >> > 5 2563 6/10/2007 8:29:56 AM
> >> > 5 1523 2/22/2006 3:29:56 PM
> >> > 8 9876 8/23/2006 5:29:56 PM
> >> > 1 7563 4/23/2006 1:29:56 PM
> >> >
> >> > What i want to do is group by Salesrep ID, then show the total number
> >> > of
> >> > invoices that rep did per month;
> >> >
> >> > SalesRep ID Month TotalInvoices Processed
> >> > 1 Jan 4
> >> > Feb 2
> >> > Mar 5
> >> >
> >> > 5 Jan 5
> >> > Feb 6
> >> > Mar 3
> >> >
> >> > 8 Jan 2
> >> > Feb 10
> >> > Mar 20 ... and so on.
> >> >
> >> >
> >> > I'm not sure exactly how to do this. Also how do I convert the date
> >> > format
> >> > into just showing the month, not every minute of every day?
> >> >
> >> > Any help is much appreciated.
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>

count property on group?

Is there any way to show the count of a collapsed group on the row set to
toggle visibility. For example, if I have rows for Purchase Orders and
grouped under that, rows for line detail that are hidden with Purcharse
Orders number as the toggle textbox, is there some way to show the detail
line count on the Purchase Order row? That is, short of establishing a
dataset?
--
Thanks,
CGWjust a thought, create a parameter and set it equal to the the line detail
row count and display the parameter for every purchase order.
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> Is there any way to show the count of a collapsed group on the row set to
> toggle visibility. For example, if I have rows for Purchase Orders and
> grouped under that, rows for line detail that are hidden with Purcharse
> Orders number as the toggle textbox, is there some way to show the detail
> line count on the Purchase Order row? That is, short of establishing a
> dataset?
> --
> Thanks,
> CGW|||I'm used to setting paramater values with either a constant or a query (as in
dataset). How would the expression look? And if the count is some property I
can have with a parameter, could I not also have it for the group and/or
detail row count? I'm sorry, but I'm still new to a bit of this. If you could
give me just a little more clarification, it would really help.
Thanks
"Vaibhav" wrote:
> just a thought, create a parameter and set it equal to the the line detail
> row count and display the parameter for every purchase order.
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> > Is there any way to show the count of a collapsed group on the row set to
> > toggle visibility. For example, if I have rows for Purchase Orders and
> > grouped under that, rows for line detail that are hidden with Purcharse
> > Orders number as the toggle textbox, is there some way to show the detail
> > line count on the Purchase Order row? That is, short of establishing a
> > dataset?
> > --
> > Thanks,
> >
> > CGW
>
>|||I am assuming that to retrieve the line details you are passing purchase
order to the details dataset. In the retireved dataset along with the other
columns add one more column with row count and then set the parameter equal
to the column value for the dataset.and display paramter.value for every
PO(line details count).
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> I'm used to setting paramater values with either a constant or a query (as
> in
> dataset). How would the expression look? And if the count is some property
> I
> can have with a parameter, could I not also have it for the group and/or
> detail row count? I'm sorry, but I'm still new to a bit of this. If you
> could
> give me just a little more clarification, it would really help.
> Thanks
> "Vaibhav" wrote:
>> just a thought, create a parameter and set it equal to the the line
>> detail
>> row count and display the parameter for every purchase order.
>> "CGW" <CGW@.discussions.microsoft.com> wrote in message
>> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
>> > Is there any way to show the count of a collapsed group on the row set
>> > to
>> > toggle visibility. For example, if I have rows for Purchase Orders and
>> > grouped under that, rows for line detail that are hidden with Purcharse
>> > Orders number as the toggle textbox, is there some way to show the
>> > detail
>> > line count on the Purchase Order row? That is, short of establishing a
>> > dataset?
>> > --
>> > Thanks,
>> >
>> > CGW
>>|||The dataset query returns both Purchase Order and Detail information with
grouping done at the report level. To put a count for PO detail in is
possible, but would require additional querying. I suppose I was just hoping
that SQL Reporting might have a property on groups, or something like that,
which could be used. Thank you.
"Vaibhav" wrote:
> I am assuming that to retrieve the line details you are passing purchase
> order to the details dataset. In the retireved dataset along with the other
> columns add one more column with row count and then set the parameter equal
> to the column value for the dataset.and display paramter.value for every
> PO(line details count).
> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> > I'm used to setting paramater values with either a constant or a query (as
> > in
> > dataset). How would the expression look? And if the count is some property
> > I
> > can have with a parameter, could I not also have it for the group and/or
> > detail row count? I'm sorry, but I'm still new to a bit of this. If you
> > could
> > give me just a little more clarification, it would really help.
> >
> > Thanks
> >
> > "Vaibhav" wrote:
> >
> >> just a thought, create a parameter and set it equal to the the line
> >> detail
> >> row count and display the parameter for every purchase order.
> >>
> >> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> >> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> >> > Is there any way to show the count of a collapsed group on the row set
> >> > to
> >> > toggle visibility. For example, if I have rows for Purchase Orders and
> >> > grouped under that, rows for line detail that are hidden with Purcharse
> >> > Orders number as the toggle textbox, is there some way to show the
> >> > detail
> >> > line count on the Purchase Order row? That is, short of establishing a
> >> > dataset?
> >> > --
> >> > Thanks,
> >> >
> >> > CGW
> >>
> >>
> >>
>
>|||You should be able to to do this thru the Count aggregation. Try
"=Count(Fields.MyField.Value)". This does require that the Purchase Order is
an actual group within the table.
"CGW" wrote:
> The dataset query returns both Purchase Order and Detail information with
> grouping done at the report level. To put a count for PO detail in is
> possible, but would require additional querying. I suppose I was just hoping
> that SQL Reporting might have a property on groups, or something like that,
> which could be used. Thank you.
> "Vaibhav" wrote:
> > I am assuming that to retrieve the line details you are passing purchase
> > order to the details dataset. In the retireved dataset along with the other
> > columns add one more column with row count and then set the parameter equal
> > to the column value for the dataset.and display paramter.value for every
> > PO(line details count).
> >
> > "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> > > I'm used to setting paramater values with either a constant or a query (as
> > > in
> > > dataset). How would the expression look? And if the count is some property
> > > I
> > > can have with a parameter, could I not also have it for the group and/or
> > > detail row count? I'm sorry, but I'm still new to a bit of this. If you
> > > could
> > > give me just a little more clarification, it would really help.
> > >
> > > Thanks
> > >
> > > "Vaibhav" wrote:
> > >
> > >> just a thought, create a parameter and set it equal to the the line
> > >> detail
> > >> row count and display the parameter for every purchase order.
> > >>
> > >> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > >> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> > >> > Is there any way to show the count of a collapsed group on the row set
> > >> > to
> > >> > toggle visibility. For example, if I have rows for Purchase Orders and
> > >> > grouped under that, rows for line detail that are hidden with Purcharse
> > >> > Orders number as the toggle textbox, is there some way to show the
> > >> > detail
> > >> > line count on the Purchase Order row? That is, short of establishing a
> > >> > dataset?
> > >> > --
> > >> > Thanks,
> > >> >
> > >> > CGW
> > >>
> > >>
> > >>
> >
> >
> >|||Funny, I thought I had tried that. It works great now!!! Thanks!
"John W" wrote:
> You should be able to to do this thru the Count aggregation. Try
> "=Count(Fields.MyField.Value)". This does require that the Purchase Order is
> an actual group within the table.
> "CGW" wrote:
> > The dataset query returns both Purchase Order and Detail information with
> > grouping done at the report level. To put a count for PO detail in is
> > possible, but would require additional querying. I suppose I was just hoping
> > that SQL Reporting might have a property on groups, or something like that,
> > which could be used. Thank you.
> >
> > "Vaibhav" wrote:
> >
> > > I am assuming that to retrieve the line details you are passing purchase
> > > order to the details dataset. In the retireved dataset along with the other
> > > columns add one more column with row count and then set the parameter equal
> > > to the column value for the dataset.and display paramter.value for every
> > > PO(line details count).
> > >
> > > "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > > news:88F8172F-58C3-46B9-B691-AAFD48AD9D9D@.microsoft.com...
> > > > I'm used to setting paramater values with either a constant or a query (as
> > > > in
> > > > dataset). How would the expression look? And if the count is some property
> > > > I
> > > > can have with a parameter, could I not also have it for the group and/or
> > > > detail row count? I'm sorry, but I'm still new to a bit of this. If you
> > > > could
> > > > give me just a little more clarification, it would really help.
> > > >
> > > > Thanks
> > > >
> > > > "Vaibhav" wrote:
> > > >
> > > >> just a thought, create a parameter and set it equal to the the line
> > > >> detail
> > > >> row count and display the parameter for every purchase order.
> > > >>
> > > >> "CGW" <CGW@.discussions.microsoft.com> wrote in message
> > > >> news:CDE25805-C0F8-41B6-A4E1-9CA4F2C0EFA0@.microsoft.com...
> > > >> > Is there any way to show the count of a collapsed group on the row set
> > > >> > to
> > > >> > toggle visibility. For example, if I have rows for Purchase Orders and
> > > >> > grouped under that, rows for line detail that are hidden with Purcharse
> > > >> > Orders number as the toggle textbox, is there some way to show the
> > > >> > detail
> > > >> > line count on the Purchase Order row? That is, short of establishing a
> > > >> > dataset?
> > > >> > --
> > > >> > Thanks,
> > > >> >
> > > >> > CGW
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >

count problem

Hi.
I have a table containing two groups and a detail area.
I want to add a counter near the second group area.
It should start from 1 and increment by one as second group value changes.(1, 2, 3, ...)
I tried to use count, countRows .... but they didn't work.
I don't want to learn total count of group value.
How can I do this?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=489400&SiteID=1

Take a look at this post and see if this can help you out! You may have to make some changes, but this might be a start.

If you think this could help, and are unsure about how you could get it to work I brainstormed along this line...

This assumes that you will be doing all your orderring and selection criteria in your SQL statements.

Select your group into a temporary table counting the "detail" rows (this will be your groups). Then join the temp table into your dataset. If all is orderred properly, you should get a dataset that contains your records and your group number in a column.

Like I said, this is just a brain storm. I believe it would work, I just haven't tried it. I do know that the post I've linked to contains some of my code that I tested and verified to work. I have had others tell me it works for them as well. Let me know what you think, and if you need me to expand upon my brainstorm.

Good Luck!

Curtis

|||I have looked at this link lots of times but it didn't work.
You wrote about temp table method.
How can I create a temp table and how can I use it?|||

I'm working on something similiar right now. I'll have a description and a sample before long, unless someone else beats me to the punch.

Curtis

|||

In the group header, you can use a running value based on the CountDistinct function:

=RunningValue(Fields!Id.Value, CountDistinct, Nothing)

Explanation:
The first argument in the running value should be identical to your grouping expression.
The second argument specifies that the CountDistinct function should be used during the calculation.
The third argument specifies that the running value should never reset in your table. If you want it to reset e.g. based on the outer group scope, then specify the outer group name as "reset"-scope.

-- Robert

|||

RunningValue(Fields!Id.Value, CountDistinct, Nothing)

I tried using this. My situation is, I have groups with sub-groups. Some of my sub-groups are not distinct. So something like this may work if every group is distinct, but how do we take in account non-distinct groups? Changing CountDistinct to Count counts all the detail rows, and that's not what I want. I just want a running count of ALL my sub-groups.

This is why I am going the route that I brain stormed. Unless there is an easier way to do it?

Thanks

Curtis

|||

In that case, you will need to look into the custom aggregation approach, similar to the sample in this blog article: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

|||

Robert Bruckner MSFT wrote:

In the group header, you can use a running value based on the CountDistinct function:

=RunningValue(Fields!Id.Value, CountDistinct, Nothing)

Thanks Robert!
It worked successfully.

count of 'groups' returned by a statement using a GROUP BY clause

Hi all,
I have a real tricky one here that should seem very simple to do but for the
life in me I cannot work out how to do it. My problem is as follows:-
The following query returns 2 rows:-
SELECT @.@.rowcount as numofgroups FROM [TransactionFile]
WHERE [ProductID] = 820 and type = 10
GROUP BY [Ref], [Sell]
Results:
numofgroups
--
2
2
the problem is I dont want 2 rows being returned. I want a single row
returned with the value 2. ie) the number of 'groups'. The data can be
summarized as shown below:-
Ref | Sell
--
a | 1.1
a | 1.1
a | 1.3
As you can see there are 'two' groups where the ref AND sell are the same.
How can i write a query that will simply return the value '2'.
Many TIA,
Leigh.Try
SELECT DISTINCT @.@.rowcount as numofgroups FROM [TransactionFile]
WHERE [ProductID] = 820 and type = 10
GROUP BY [Ref], [Sell]
-
"Leigh" wrote:

> Hi all,
> I have a real tricky one here that should seem very simple to do but for t
he
> life in me I cannot work out how to do it. My problem is as follows:-
> The following query returns 2 rows:-
> SELECT @.@.rowcount as numofgroups FROM [TransactionFile]
> WHERE [ProductID] = 820 and type = 10
> GROUP BY [Ref], [Sell]
> Results:
> numofgroups
> --
> 2
> 2
> the problem is I dont want 2 rows being returned. I want a single row
> returned with the value 2. ie) the number of 'groups'. The data can be
> summarized as shown below:-
> Ref | Sell
> --
> a | 1.1
> a | 1.1
> a | 1.3
> As you can see there are 'two' groups where the ref AND sell are the same.
> How can i write a query that will simply return the value '2'.
> Many TIA,
> Leigh.|||Hi, Leigh
You can use this query:
SELECT COUNT(*) FROM (
SELECT Ref, Sell FROM [TransactionFile]
WHERE ProductID = 820 and type = 10
GROUP BY Ref, Sell
) x
which is equivalent to:
SELECT COUNT(*) FROM (
SELECT DISTINCT Ref, Sell FROM [TransactionFile]
WHERE ProductID = 820 and type = 10
) x
Razvansql

Count Group Members

Hi,

I'm trying to make a matrix report for current and previous month. I've gotten the matrix to show current month dat on the top data region and previous month on the side data region. Now i'm trying to compare and count group members for current and previous month.

example: count for students with the same exam score. If a student got 20 on thier exam Previous month and 20 on their current month exam, they will be counted as 1.

20

40

60

80

100

20

2

4

0

7

1

60

0

1

0

4

0

80

2

0

3

0

0

so the data shows 2 students who got 20 last month also got 20 this month. It also shows 4 students who got 20 last month managed to get 40 this month, and so on.

How do i count the students as i'm using student ID.

Thanks in advance.

Hi,

count is overloaded. YOu can also use that with a group expressions COUNT(SomeValue, SomeGroup)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Thanks for the reply. I've already tried that method. the count only produced 0, but there is data to be counted.

the data grouping is like this: Year -> Month -> Class -> ID -> Score becuase the score is the measure or detail data. However i group the data in the report by Year -> Month -> Class -> Score and then count the IDs that are associated with each distinct score value.

This is to compare how many student got a certain score amount and what are thier scores for the current month as in the sample I have given previous post.

Hope this is much clearer explanation than before.

|||Here's an idea for getting this to work. When querying for the data, join the table with itself, based on the student's id and the month. This will give you one dataset with the student's data for each month compared to each of the following months that you can use to build the matrix.
Using this data

TestDate ID Score
1/1/2000 1 20
1/1/2000 2 20
1/1/2000 3 20

1/1/2000 4 20
1/1/2000 5 20

1/1/2000 6 20
2/1/2000 1 20

2/1/2000 2 20

2/1/2000 3 40

2/1/2000 4 40

2/1/2000 5 40

2/1/2000 6 40

The query would look something like this

Select t1.TestDate AS TestDate, t2.TestDate AS TestDate2, t1.Score AS Score, t2.Score AS Score2, t1.ID FROM TestScores As t1 INNER JOIN TestScores As t2 ON t1.ID = t2.ID AND t1.TestDate < t2.TestDatesql

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!
Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!
|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!
|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =
dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 ?
"Kalen Delaney" wrote:

> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
> us more info, and maybe even some sample data and sample output, or at least
> an explanation of what you're doing with that value if you want the rest of
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
>
>
|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that[vbcol=seagreen]
> this
> query would have returned the value of 2 -- not 3 ?
>
> "Kalen Delaney" wrote:
give[vbcol=seagreen]
least[vbcol=seagreen]
of[vbcol=seagreen]
My[vbcol=seagreen]
the[vbcol=seagreen]
|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
(Please reply only to the newsgroup)

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 '
"Kalen Delaney" wrote:
> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
> us more info, and maybe even some sample data and sample output, or at least
> an explanation of what you're doing with that value if you want the rest of
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > The following SQL statement returns 38 records:
> >
> > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Rather than retrieving the recordset, I want to simply get a count. My
> > syntax must be wrong (same syntax as above but I added COUNT) because the
> > following query simply returns 38 records with the value "1":
> >
> > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Any suggestions for this beginner? Thanks!
>
>|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that
> this
> query would have returned the value of 2 -- not 3 '
>
> "Kalen Delaney" wrote:
> > Hi
> >
> > GROUP BY always returns 1 row for each different value of the GROUP BY
> > column, so you're still getting one row for each different
> > Base_Contacts.ID_Contact
> > value. If you want the count of all values, don't use GROUP BY.
> >
> > I'm not exactly sure what you want to do with
> > Base_ContactAttributes.ID_Level_Value in the new query. You'll need to
give
> > us more info, and maybe even some sample data and sample output, or at
least
> > an explanation of what you're doing with that value if you want the rest
of
> > the solution.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> > news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > > The following SQL statement returns 38 records:
> > >
> > > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Rather than retrieving the recordset, I want to simply get a count.
My
> > > syntax must be wrong (same syntax as above but I added COUNT) because
the
> > > following query simply returns 38 records with the value "1":
> > >
> > > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Any suggestions for this beginner? Thanks!
> >
> >
> >|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
--
(Please reply only to the newsgroup)

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =
dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 '
"Kalen Delaney" wrote:

> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to giv
e
> us more info, and maybe even some sample data and sample output, or at lea
st
> an explanation of what you're doing with that value if you want the rest o
f
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
>
>|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that[vbcol=seagreen]
> this
> query would have returned the value of 2 -- not 3 '
>
> "Kalen Delaney" wrote:
>
give[vbcol=seagreen]
least[vbcol=seagreen]
of[vbcol=seagreen]
My[vbcol=seagreen]
the[vbcol=seagreen]|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
(Please reply only to the newsgroup)

COUNT Expression Values

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.

COUNT Expression Values

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.sql

Count days between to dates group by month

Hi i have an database that looks like this:
http://www.cyren.no/diagram3.gif
And the output here http://www.cyren.no/ods
(Se picture)
I want to get an query where a user can search in a date range.
The output should be like this:
Department(ANavn)
JANUAR 2003
VF54423(RegNr) 21.01.2003(GjelderFra) 23.01.2003(SiOppforsikringFra)
3(AntDays)
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra)
9(AntDays)
SUM JANUAR 12 days 2 cars
FEBRUARY 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra)
30(AntDays)
SUM JANUAR 30 days 1 car
MARS 2003
VF54422(RegNr) 21.01.2003(GjelderFra) 23.03.2003(SiOppforsikringFra)
23(AntDays)
SUM JANUAR 23 days 1 car
The error is if a car has been made inactive and the make a new post
with the same regnr. Or the sp works fine...
CREATE PROCEDURE sp_aktive
(
@.datoFOM datetime,
@.datoTOM datetime,
@.avdeling int
)
AS
Declare @.period as datetime
Set @.period =3D @.datoFOM
if(object_id('tempdb.dbo.#temp')<>1)
drop table #temp
if(object_id('tempdb.dbo.#temp2')<>1)
drop table #temp2
--SET dateformat dmy
If(object_id('tempdb.dbo.#periods')<>1)
drop table #periods
Select @.period as dato
into #periods
while @.Period < @.datoTOM
begin
Select @.Period =3D dateadd(month,1,@.period)
Insert into #periods
Select @.period
End
SELECT fkbravdeling, a.SiOppForsikringFra,
case when year(b.dato) =3D year(gjelderFra) and month(b.dato) =3D
month(gjelderFra) then
gjelderfra
else
b.dato
end as gjelderfra, a.Regnr
into #temp
FROM
cyren.tblbiler a
join
(select distinct cast('01' + datename(month, gjelderFra) + cast(year(
gjelderFra) as varchar) as datetime) as dato from cyren.tblbiler) b
on b.dato between cast('01' + datename(month, gjelderFra) + cast(year(
gjelderFra) as varchar) as datetime) and
coalesce(SiOppForsikringFra,'01jan2050')
join #periods c
on b.dato =3D c.dato
select *
into #temp2
from
(
SELECT anavn,
sum(
case when month(SiOppForsikringFra) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1,
gjelderfra)) + cast(year(dateadd(month,1, gjelderfra)) as varchar)) +1
)
end
) as AntDays,
'Antall aktive biler denne m=E5ned' as regnr, count(*) as AntCars,
year(gjelderfra) as [year], month(gjelderfra) as [month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
as dato,
2 as [order]
FROM
#temp a join
cyren.tblAvdeling
on FKBRAvdeling=3DAID
WHERE fkbravdeling=3D@.avdeling AND gjelderfra between @.datoFOM and
@.datoTOM
group by anavn, month(gjelderfra), year(gjelderfra),
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
) a
union
(
SELECT anavn,
case when month(SiOppForsikringFra) =3D month(gjelderfra) and
year(SiOppForsikringFra) =3D year(gjelderfra)then
DateDiff(d, gjelderfra, SiOppForsikringFra) +1
else
DateDiff(d, gjelderfra,
dateadd(day, -1, '01' + datename(month, dateadd(month,1, gjelderfra))
+ cast(year(dateadd(month,1, gjelderfra)) as varchar)) + 1
)
end AntDays,
Regnr, 1 as AntCars ,year(gjelderfra) as [year], month(gjelderfra) as
[month],
datename(month,gjelderfra) + ' ' + cast(year(gjelderfra) as varchar)
as dato,
1 as [order]
FROM
#temp a join
cyren.tblAvdeling b
on FKBRAvdeling=3DAID
WHERE fkbravdeling=3D@.avdeling AND gjelderfra between @.datoFOM and
@.datoTOM
)
SELECT a.*, BID, GjelderFra, SiOppForsikringFra, Aktiv
FROM #Temp2 a
left join cyren.tblbiler b
on a.regnr =3D b.regnr and b.Fkbravdeling=3D@.avdeling--Denne er kun hvis
den skal kj=F8res p=E5 avdeling
order by anavn, [year], [month], [order]
GOOn 16 Jan 2006 05:04:07 -0800, web@.cyren.no wrote:

>Hi i have an database that looks like this:
>http://www.cyren.no/diagram3.gif
>And the output here http://www.cyren.no/ods
>(Se picture)
>
>I want to get an query where a user can search in a date range.
>
>The output should be like this:
(snip)
>The error is if a car has been made inactive and the make a new post
>with the same regnr. Or the sp works fine...
>CREATE PROCEDURE sp_aktive
(snip)
Hi web,
First: never use sp_ as the first three characters for your stored
procedure names. This prefixed is resserved for system stored
procedures. SQL Server will look for them in the master database first,
then in the current database. Apart from the performance hit, you'll run
into much more severe problems if MS adds a system stored procedure with
the same name in the next release or service pack...
Second: I don't understand what exactly it is you're asking. I've seen
the picture of the table structure, and the ~20,000 row report you've
posted links to (and I even have a feeling that I understand what some
of the Norwewgian words mean <g> ). But I fail to see what you want. And
the highly complex code in the stored proc you posted doesn't help
either.
A better way to describe your problem would be to post:
* CREATE TABLE statements for the tables involved. You may simplify them
by omitting irrelevant columns (and translating remaining column names
to English would not be a bad idea either), but please do include all
constraints and properties.
* INSERT statements for a few rows of sample data. The amount of data
should be enough to illustrate the problem you're trying to solve, but
not more. Of course, they should run against the CREATE TABLE statements
you post.
* Expected output (based on the posted sample data, of course).
* And a description of why the sample data has to yield the results you
posted - in other words, a description of the actual busniess problem
that you're trying to solve.
Check out www.aspfaq.com/5006 for some useful hints on how to assemble
the info for this follow-up post.
Hugo Kornelis, SQL Server MVP

Count CustomersOrders

Assuming you have the CustomerID in the Orders table as a foreign key back to the Customers table, you can do a group by on the Orders.CustomerID and do a count. Using the Northwind database schema: select CustomerID, count(OrderID)from Ordersgroup by CustomerID This will only give you results for the customers that have orders. If you need the customers without orders as well, you will need to include the Customers table. For example with a left outer join: select C.CustomerID, count(O.OrderID)from Customers C left outer join Orders O on C.CustomerID=O.CustomerIDgroup by C.CustomerID Best regards Michael <Hassano@.discussions.microsoft.com> wrote in message news:d8b7a568-55d1-4bf8-a2f3-3a5635706ab4@.discussions.microsoft.com...Dear Team if there are three tables customers, Order and OrderDetails how to count the number of orders for each certain customer Thank you very muchDear Team
if there are three tables customers, Order and OrderDetails
how to count the number of orders for each certain customer

Thank you very much|||Mr NNTP
The Query looks good thank you very much
i will take some time to test it

Count and Group By with DateTime!

Hi all,

I have a problem with my query which is suppose to select count posts group by the date, the query works but doesnt return the count as i want, i think the problem that the datetime column contains also Time in hours which ofcourse isnt same in all rows in same day, so i dont know what to do, Here's my Query:

SELECT

PostID,Date,COUNT(Date)AS'TotalPosts'
FROMPosts
GROUP BYPostID,Date

Thank you for help

I didn't have that table available but I tested the same query against Sales.SalesOrderDetail table in AdventureWorks sample database. I believe you get the idea from it

SELECT Count(*) TheCount, [Day]
FROM (
SELECT
CAST(
CAST(MONTH(ModifiedDate) AS varchar(2) ) + '/' +
CAST(DAY(ModifiedDate) AS varchar(2) ) + '/' +
CAST(YEAR(ModifiedDate) AS varchar(4) )
AS datetime
)
as [Day]
FROM Sales.SalesOrderDetail

) TMP
GROUP BY [Day]
ORDER BY [Day]

Just change the table and field names respectively in the query

|||Amazing, Thank you alot

Count & LastNonEmpty

Is there any way to make Count work properly on a measure group where aggregation for every single measure is set to LastNonEmpty?

Basically it's a fact table that contains periodic snapshots of data & so count only makes sense within the slice based on insertion time.

Thanks!

any help please?|||What kind of count do you want in this snapshot scenario - is it a count of all objects for which there is data - could you illustrate with some examples? And are the snapshots synchronized, or could they be created at different times?|||

Here's an example:

Product Amount ImportDate

a 1 Jan 2007

b 10 Jan 2007

c 100 Jan 2007

a 2 Jan 2007

b 11 Feb 2007

b 12 Feb 2007

so in this case for Jan 2007 there's 2 products 'a' for a total amount of 3, so count =2.

for Feb 2007 there's 2 products 'b' for a total amount of 23

So basically the aggregation on the amount column is set to lastnonempty using 'import date' to determine the last child

so for the entire year 2007 for product B the amount would be equal to the amount from Feb 2007 - count =2, amount = 23.

But the count always runs across all dates & so for the set above & entire year 2007 it would return 3 for product b - not 2.

all the snapshots are done for the same set of products - they're common across all dates & it's product measures that are being aggregated.

|||Add a Named Calculation to the fact table like [LastCount] = 1, then create a corresponding measure: [LastCount], with LastNonEmpty aggregation.|||

what a great idea - and how didn't I think of that?!

Thanks!

Could not obtain information about Windows NT group/user .......error code 0x534...

== I asked this question directly to Remus and wanted to share the response to all of those people using this forum ==

We recently moved our database server from SQL Server 2000 to SQL Server 2005. All applications on our intranet development server stay the same [VS.NET 2003], but recently resources in our Dev DB server ran out of space. While doing a thorough investigation, I noticed ERRORLOG file was occupying about 35 Gig of HDD space. I immediately checked SQL Server error log and noticed an entry which says –

===========================================================================================

Date 7/7/2006 4:45:37 PM

Log SQL Server (Current - 7/7/2006 4:45:00 PM)

Source spid77s

Message

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-5eaf8465-d0cb-4be7-93b6-44bb979dd41c] running on queue BW_Content.dbo.SqlQueryNotificationService-5eaf8465-d0cb-4be7-93b6-44bb979dd41c output the following: 'Could not obtain information about Windows NT group/user 'BWCINC\HoffK', error code 0x534.'

===========================================================================================

What is this SqlQueryNotificationService in my database? Is it a SQL Server 2005 thing? Why the same kind of stored procedure does not exist in other databases, but BW_Content? This error is getting repeated most probably every second and is filling up our server.

I believe our corporate IT people removed our domain accounts from BWCINC domain to BWCORP domain and probably some application which is using BWCINC\HoffK credential is getting errored out. I tried to locate this application and was not successful.

Is there anyway that I can stop this ERRORLOG from growing? How can I delete these log entries so that I can make space on our Hard Drive? Is there an easy way in SQL Server 2005 to locate which application is creating this error?

Response from Remus:

The 'SqlQueryNotificationService-...' is the service created by SqlDependency when you call SqlDependency.Start (). The problem you describe appears because the 'dbo' user of the database is mapped to the login that originally created this database. The SqlDependency created queue has an EXECUTE AS OWNER clause, owner is 'dbo' and therefore this is equivalent to an EXECUTE AS USER = 'dbo'. The error you see is reported by the domain controller when asked to give information about the original account 'dbo' mapps to (that is, BWCINC\HoffK'): Error code: (Win32) 0x534 (1332) - No mapping between account names and security IDs was done.

To solve the issue, change 'dbo' to match a correct login, using either sp_changedbowner or ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somavalidlogin]

To find the databases that have this problem, run this query:

select name, suser_sname(owner_sid) from sys.databases

The databses that have the problem will show NULL on the second column.

A similar problem is described here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

To remove the entries, use sp_cycle_errorlog to force a new errorlog file, then delete the huge log file.

I executed ALTER AUTHORIZATION ON DATABASE::[BW_Content] TO [sa];

I got this error in SQL Error Log once and the growth of ERRORLOG was stopped.

===============================================================

Date 7/10/2006 1:16:55 PM
Log SQL Server (Current - 7/10/2006 1:17:00 PM)

Source spid20s

Message

The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a&apos; because it does not exist.</Description></Error>'.

===============================================================

Should I be concerned about this error?

Thanks

-Binoy

Thanks for sharing this!|||

Binoy Sankar wrote:

I got this error in SQL Error Log once and the growth of ERRORLOG was stopped.

===============================================================

Date 7/10/2006 1:16:55 PM
Log SQL Server (Current - 7/10/2006 1:17:00 PM)

Source spid20s

Message

The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a&apos; because it does not exist.</Description></Error>'.

===============================================================

Should I be concerned about this error?

Don't worry abouyt it. The error means that the client disconnected and the temporary service was dropped before the notification was fired.

Thanks,

~ Remus

Could not create an user to SQL Server 2005

Hi,

I am trying to add "Remote Desktop Users" as a login group to SQL Server 2005 (i.e. so they can log into management studio). I get a "Not Found Error" (see below). But it is of course found, because I selected it :-) What is going on here? I see the "Administrators" group and various SQL Server groupls added automatically there, so there must be a way, right?

I know I can add each user in the group individually, but that seems overkill when I can just use the group in which they are contained.

Here is the error I get:

TITLE: Microsoft SQL Server Management Studio

Create failed for Login 'TESTSERVER\Remote Desktop Users'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Windows NT user or group 'TESTSERVER\Remote Desktop Users' not found. Check the name again. (Microsoft SQL Server, Error: 15401)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476

BUTTONS:

OK

This appears to be a defect within Management Studio: it qualifies the group using the machine name instead of "Builtin". I suggest that you open a bug at http://lab.msdn.microsoft.com/productfeedback/Default.aspx and put in it the information from this thread. Let me know if you need any help with filing the bug or if you wish that I would file it instead.

In the meantime, you can create such login by executing:

create login [Builtin\Remote Desktop Users] from windows;

Thanks
Laurentiu

|||Thank you Laurentiu. The script worked. I have indeed submitted a bug.|||Please note that I am the furthest thing from an expert that one can imagine, but...

I had a similar problem, but found an easy way to solve it.

Expand Security, right click Logins and select New Login
Click Search to the right of the Login Name box
Click the button Object Types
Select the box next to Groups|||

Hi ,

I am trying to create a new user in SQL Server 2005 with windows authentication.

This is the query i am trying to run.

create login [Builtin\User1] from windows

But i am getting error like "Windows NT user or group 'Builtin\waveset1' not found. Check the name again."

And the sql server is running on SQL and windows authuntication mode.

So anybody can help me what is the wrong.

I appreciate your response.

Thanks!