grouping similar data

In layout view: how do I group all the company names together who have the same value for a certain field? For instance, 10 companies all have the same booth size, but in Preview mode, it lists each company seprately with the booth size on each line. I need to have the booth size listed once with all the companies who share that common size. I have tried adding a group and using the expression for the company name as well as the booth size and it does not work. Can someone provide some detailed instructions?
Thank you,
Bil
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf I am understanding correctly you want to have the booth size and then
show all companies under that that match that description. Have you tried
grouping it by the booth size and then put your companies in the detail?
"Bkana" <nospam@.developmentnow.com> wrote in message
news:97f68ac4-a6e2-429d-a4e5-3b98fed63020@.developmentnow.com...
> In layout view: how do I group all the company names together who have the
> same value for a certain field? For instance, 10 companies all have the
> same booth size, but in Preview mode, it lists each company seprately with
> the booth size on each line. I need to have the booth size listed once
> with all the companies who share that common size. I have tried adding a
> group and using the expression for the company name as well as the booth
> size and it does not work. Can someone provide some detailed instructions?
> Thank you,
> Bill
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Grouping question

Hi guys,

on my report i have 3 separate matrices with monthly sales results, each gives a different view on the same data. This all works nicely, but the customer wants these matrices grouped by product (the product is a parameter of the report, they can choose several or all products to report on).

Exactly what would be the best way to achieve this grouping? It seems i can't house them within a table, and i can't put them in another matrix without running into scope errors.

Thanks!!!

sluggy

Sounds like those three matrics are using the same dataset. In that case, you should be able to remove the dataset set on these matrices respectively. Add a list that includes the three matrices, set the dataset on the list, and add a group on the list by the product.|||

Thanks Fang,

that did the trick nicely.

One further (possibly stupid) question: how could i then have a set of matrices at the end with the totals (ie the aggregations across all products)? Should i just have a copy of the original matrices and make them invisible if there was only one product selected? Or is there a more proper or cooler way to do it?

Many thanks,

sluggy

|||

There are multiple ways to do this:

1. Add a copy of the original matrices outside of the list.

2. Use a matrix instead of list to host the three matrices, add a matrix row group by product, and enable subtotal on the row group. This way you will automatically get the matrics across all products in the subtotal row.

|||

Excellent, thanks!!

sluggy

GROUPING problem

I am trying to get counts of jobs accum into 4 columns by date. When I try
to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause."
I don't want to group by JobSize. Below is my code if someone can help me
resolve this. Thanks.
David
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
XsmallJobs = CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END,
SmallJobs = CASE
WHEN JobSize = 'S' THEN 1
ELSE 0
END,
MedJobs = CASE
WHEN JobSize = 'M' THEN 1
ELSE 0
END,
HeavyJobs = CASE
WHEN JobSize = 'H' THEN 1
ELSE 0
END
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))All that is missing is the SUM() - the missing aggregate function of
the error message - around each CASE expression:
XsmallJobs = SUM(CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END),
Roy Harvey
Beacon Falls, CT
On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
wrote:

>I am trying to get counts of jobs accum into 4 columns by date. When I try
>to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
>invalid in the select list because it is not contained in either an
>aggregate function or the GROUP BY clause."
>I don't want to group by JobSize. Below is my code if someone can help me
>resolve this. Thanks.
>David
>ALTER VIEW dbo.vw_JobSizeByDate
>AS
>SELECT ScheduledInDate,
>XsmallJobs = CASE
>WHEN JobSize = 'X' THEN 1
>ELSE 0
>END,
>SmallJobs = CASE
>WHEN JobSize = 'S' THEN 1
>ELSE 0
>END,
>MedJobs = CASE
>WHEN JobSize = 'M' THEN 1
>ELSE 0
>END,
>HeavyJobs = CASE
>WHEN JobSize = 'H' THEN 1
>ELSE 0
>END
>FROM dbo.RepairOrder
>WHERE (RepairOrderID IS NOT NULL)
>GROUP BY ScheduledInDate
>HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>|||why are you grouping at all, maybe I've missed it, but I don't see any
aggregate functions, just add the having clause as an AND to the where,
and remove the group by:
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
AND (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))|||Use the CASE expressions inside aggregate functions:
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
SUM(CASE WHEN JobSize = 'X' THEN 1 ELSE 0 END) AS "XsmallJobs",
SUM(CASE WHEN JobSize = 'S' THEN 1 ELSE 0 END) AS "SmallJobs",
SUM(CASE WHEN JobSize = 'M' THEN 1 ELSE 0 END) AS "MedJobs",
SUM(CASE WHEN JobSize = 'H' THEN 1 ELSE 0 END) AS "HeavyJobs"
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
"David" wrote:

> I am trying to get counts of jobs accum into 4 columns by date. When I tr
y
> to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' i
s
> invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
> I don't want to group by JobSize. Below is my code if someone can help me
> resolve this. Thanks.
> David
> ALTER VIEW dbo.vw_JobSizeByDate
> AS
> SELECT ScheduledInDate,
> XsmallJobs = CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END,
> SmallJobs = CASE
> WHEN JobSize = 'S' THEN 1
> ELSE 0
> END,
> MedJobs = CASE
> WHEN JobSize = 'M' THEN 1
> ELSE 0
> END,
> HeavyJobs = CASE
> WHEN JobSize = 'H' THEN 1
> ELSE 0
> END
> FROM dbo.RepairOrder
> WHERE (RepairOrderID IS NOT NULL)
> GROUP BY ScheduledInDate
> HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>
>|||Someone actually put a "vw-"prefix on your view name! They did not
know the ISO-11179 standards - unless this table deals with
Volkswagens. Also, use the portable AS syntax instead of dialect =.
Can I assume that you have more than one repair order, in spite of a
singular table name?
Your WHERE and HAVING clauses made no sense. How can a
"repair_order_id" ever be NULL? What is the definition of an
identifier? Why are you casting temporal data to strings? That would
imply your DDL is soooo screwed up that temporal data is in strings!
Try this, after you clean up the DDL.
CREATE VIEW JobsizeByDate
(xsmalljob_cnt,
smalljob_cnt,
medjob_cnt,
heavyjob_cnt)
AS
SELECT scheduledin_date,
SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
FROM RepairOrders
GROUP BY scheduledin_date;|||Perfect. That worked. Thanks.
David
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:fimi42l7diferi1jmlaa6k1anf8lvo6t1d@.
4ax.com...
> All that is missing is the SUM() - the missing aggregate function of
> the error message - around each CASE expression:
> XsmallJobs = SUM(CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END),
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
> wrote:
>|||Ah the beauty of an sql dbms...the overblown importance of columns names :P
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145658584.662773.174100@.i39g2000cwa.googlegroups.com...
> Someone actually put a "vw-"prefix on your view name! They did not
> know the ISO-11179 standards - unless this table deals with
> Volkswagens. Also, use the portable AS syntax instead of dialect =.
> Can I assume that you have more than one repair order, in spite of a
> singular table name?
> Your WHERE and HAVING clauses made no sense. How can a
> "repair_order_id" ever be NULL? What is the definition of an
> identifier? Why are you casting temporal data to strings? That would
> imply your DDL is soooo screwed up that temporal data is in strings!
> Try this, after you clean up the DDL.
> CREATE VIEW JobsizeByDate
> (xsmalljob_cnt,
> smalljob_cnt,
> medjob_cnt,
> heavyjob_cnt)
> AS
> SELECT scheduledin_date,
> SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
> FROM RepairOrders
> GROUP BY scheduledin_date;
>|||Why do you think the vague, non-standard names will make for a good
database? That they will port? That a data dictionary will appear
magically from them? That ISO is a waste of time? That 30+ years of
SE research is wrong?|||There is a certain quality to your quantity of orthodoxy.
But you have missed the mark:)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145668737.205833.119960@.j33g2000cwa.googlegroups.com...
> Why do you think the vague, non-standard names will make for a good
> database? That they will port? That a data dictionary will appear
> magically from them? That ISO is a waste of time? That 30+ years of
> SE research is wrong?
>

Grouping on multiple Datasets

is it possible to group 2 or more datasets?
i.e i have a row that returns count of sales, ATV etc from one view
and paidout sales from another view this has to come from 2 datasets
due to them having different selection criteria
i am grouping by region on dataset1 but need to group by region on
dataset2 aslo?
Any help much appreciatednot possible today.
An option is to create a subreport.
The first report group the objects from the first dataset, the subreport
display the data from the second dataset filtered by a paremeter which is
the region.
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1181571015.624028.95320@.c77g2000hse.googlegroups.com...
> is it possible to group 2 or more datasets?
> i.e i have a row that returns count of sales, ATV etc from one view
> and paidout sales from another view this has to come from 2 datasets
> due to them having different selection criteria
> i am grouping by region on dataset1 but need to group by region on
> dataset2 aslo?
> Any help much appreciated
>|||many thanks|||It depends on what you are doing.
Are the datasets just select commands?
If so a simple union query is all that is required.
It you have a table per dataset, then you group each one accordingly.
More information is required but there are always many
'workarounds'...
Regards,
Tom Bizannes
Reporting Services Designer
Sydney Australia

Grouping Data and Selecting highest date

In SQL 2005 I have the following view:

SELECT TOP (100) PERCENT StockCode, Warehouse, QtyOnHand, QtyAllocated, QtyOnOrder, QtyOnBackOrder, DateLastSale, DateLastStockMove,
DateLastPurchase
FROM dbo.MBL_VW_AgedStock_Sales
ORDER BY StockCode

This basically shows a list of stock codes (there are multiple stock codes the same) and the last sold date. What i need to do is group the stock codes which are the same together, and show the latest date.

For example I could have the following:

STOCK CODE Last Date Sold

PC1113 11/01/2007

PC1104 15/03/2007

PC1113 15/02/2007

What I want to see is a list that shows PC1113 with its latest sold date, i.e.

STOCK CODE Last Date Sold

PC1113 15/02/2007

PC1104 15/03/2007

Any ideas?

Thanks

Kris

select StockCode, max(DateLastStockMovie) from your_table_or_view

group by stockcode

order by stockcode

|||

Something like this:

SELECT

'Stock Code' = StockCode,

'Last Sold Date' = max( DateLastSale )

FROM dbo.MBL_VW_AgedStock_Sales

GROUP BY StockCode

ORDER BY StockCode

Grouping By Problems

I'h having some throughput problems, and so, I decided to create a view with the info I need.

The problem now is this: I have to select all the days where there were associations in my clients website in this format: dd/MM/yyyy (xx) where XX is the number of associations on that day. Here is the first code, wich worked but resulted in timeout:

SELECT DISTINCT (CONVERT(varchar, buy_date, 103) + ' (' + CONVERT(varchar(10), (SELECT COUNT(*) FROM user_plan up2 WHERE CONVERT(datetime, CONVERT(varchar, up2.buy_date, 101)) = CONVERT(datetime, CONVERT(varchar, up1.buy_date, 101)))) + ')') AS 'text',

CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'

FROM user_plan up1

WHERE CONVERT(varchar, buy_date, 101) <= CONVERT(varchar, getdate(), 101)

ORDER BY value DESC

Then I tried to create a view in wich I intented to save the number of associations to avoid the n² complexity of my query...

This is the create view script:

CREATE VIEW quadro_social AS
SELECT COUNT(1) AS total,
CONVERT(VARCHAR, buy_date, 103) as buy_date,
CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'
FROM user_plan
GROUP BY buy_date

But what happens is, becaus the "buy_date" column is datetime, they are not beign grouped because they have different times, but the same days... How can I group the registers with the same date (dd/MM/yyyy) ignoring the hour, minutes and seconds?

Thanks a lot!

Guilherme Bertini Boettcher

SELECTCONVERT(varchar(10),DATEADD(d,0,DATEDIFF(d,0,buy_date)),103)+' ('+COUNT(*)+')'AS'text',DATEADD(d,0,DATEDIFF(d,0,buy_date))as'value'FROM user_plan up1WHERE buy_date<DATEADD(d,1,DATEDIFF(d,0,getutcdate()))GROUP BYDATEADD(d,0,DATEDIFF(d,0,buy_date))

You can convert the second column to varchar if you want, but I always prefer passing back dates and/or datetimes as a true datetime, since many of my applications are of global scope, and the display format for it is unknown at query time. (Displaying mm/dd/yyyy to american users, dd/mm/yyyy to french & british, dd.mm.yyyy for the rest of europe, etc).

|||

Thanks for your help Motley!

Actually, before you answered I had already solved the problem by using an extra column with the same datetime, only with the time part with zeros on my view. Since that column was actually the value wich represented every registry on that date, I would need that column anyway...

After looking at your answer, actually what you said is far more trustful and independent oh gegraphical locations...

Tahnkls a lot mate!

Grouping based on input parameters

I'm not even sure if this is possible. Our requirement is that the user can input whether to view hourly, daily, or monthly data. Originally I had created three separate reports, but I've been asked to try again.

Is it possible to specify when grouping to group by the whole operation date, or group by days, or group by weeks?

Thanks.Its possible if the grouping is done in logical way say days->weeks->months->years.
Trying to say that if you want to group by Monday or tuesday then the answer is no.
Now what you do is in one report you start from inside grouping by days, then by weeks, then by months and then by years.
Make a parameter through which you can suppress the section accordingly.
Hope you can understand my theory.|||Thank you for your explanation. That was a lot easier than I thought!

:)|||I've been re-doing one of my other reports and noticed that my solution still has one problem with it. I have grouped by (from outer level) : Year, Month, Product Type.

When I view the product type by month, all are listed as one would expect. However, when viewing by year they are not grouped correctly, ie :

Jan 2005
prod x - 5
prod y - 3
prod z - 4

Feb 2005
prod x - 1
prod y - 7

So when viewing by year, I would like to see Prod x - 6. Instead, I get the two individual listings of prod x, etc.

Is there a way around this?

Thanks.|||You should expect only one entry per year if you have done gouping by year->month->type.
Right click on Group Producttype and click on change group and make sure that Order is in ascending and not Original order.

B.thakkar|||Thanks, though everything is grouped with order ascending. I think the problem is that they are essentially still grouped by month (we just choose not to acknowledge/display this by suppressing the header).

Group Ranking #2

Here is a try. I created a view to do it readable, you can substitute it by
its definition if you one just one #$%^@.&* shot.
use tempdb;
go
create table t(
invoice int not null unique,
paidontime char(3) not null check(paidontime in ('no', 'yes'))
)
insert into t values(1006, 'yes')
insert into t values(1005, 'yes')
insert into t values(1004, 'no')
insert into t values(1003, 'yes')
insert into t values(1002, 'yes')
insert into t values(1001, 'yes')
insert into t values(1000, 'no')
go
create view my_view
as
select
(
select
case
when (select count(distinct d.paidontime) from t as d) > 1 then
coalesce(max(b.invoice), a.invoice - 1)
else 1
end
from
t as b
where
b.invoice < a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
go
select
t4.rank,
t3.invoice,
t3.paidontime
from
my_view as t3
inner join
(
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
) as t4
on t3.group_id = t4.group_id
order by
t3.invoice desc
go
drop view my_view
go
drop table t
go
AMB
"mekim" wrote:

> Hi All,
> I have a table that I wish to group by the change or switch in the
> "PaidOnTime" column while sequenced by descending invoice #
> I have read a bunch of gr8 articles that describe this situation...
> i.e.
> http://www.databasejournal.com/feat...10894_2244821_2
> http://support.microsoft.com/defaul...b;EN-US;q186133
> however this is with a twist...I don't want to group or number "Groups" -
I
> want to group the "Switch" in the groups.. (I'm trying my best not to make
> this sound confusing - one could only imagine if I wasn't trying :-)
> Anyway - here's sample data
> Input
> --
> Invoice PaidOnTime
> 1006 Yes
> 1005 Yes
> 1004 No
> 1003 Yes
> 1002 Yes
> 1001 Yes
> 1000 No
> -- RIGHT Output :-D
> Group Invoice PaidOnTime
> 1 1006 Yes
> 1 1005 Yes
> 2 1004 No
> 3 1003 Yes
> 3 1002 Yes
> 3 1001 Yes
> 4 1000 No
>
> The below is the best I can get :-(
> -- Wrong Output Since it Groups by PaidOnTime and not the "Switching" of
> PaidOnTime
> Group Invoice PaidOnTime
> 1 1006 Yes
> 1 1005 Yes
> 2 1004 No
> 1 1003 Yes
> 1 1002 Yes
> 1 1001 Yes
> 2 1000 No
>
> Best Regards,
> Mekim
> p.s. Excuse the dbl post plzCorrection,
use tempdb;
go
create table t(
invoice int not null unique,
paidontime char(3) not null check(paidontime in ('no', 'yes'))
)
insert into t values(1006, 'yes')
insert into t values(1005, 'yes')
insert into t values(1004, 'no')
insert into t values(1003, 'yes')
insert into t values(1002, 'yes')
insert into t values(1001, 'yes')
insert into t values(1000, 'no')
go
create view my_view
as
select
(
select
case
when exists(select * from t as c where c.invoice < a.invoice and
c.paidontime <> a.paidontime) then max(b.invoice)
else -1
end
from
t as b
where
b.invoice < a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
go
select * from my_view
go
select
t4.rank,
t3.invoice,
t3.paidontime
from
my_view as t3
inner join
(
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
) as t4
on t3.group_id = t4.group_id
order by
t3.invoice desc
go
drop view my_view
go
drop table t
go
AMB
"Alejandro Mesa" wrote:
> Here is a try. I created a view to do it readable, you can substitute it b
y
> its definition if you one just one #$%^@.&* shot.
> use tempdb;
> go
> create table t(
> invoice int not null unique,
> paidontime char(3) not null check(paidontime in ('no', 'yes'))
> )
> insert into t values(1006, 'yes')
> insert into t values(1005, 'yes')
> insert into t values(1004, 'no')
> insert into t values(1003, 'yes')
> insert into t values(1002, 'yes')
> insert into t values(1001, 'yes')
> insert into t values(1000, 'no')
> go
> create view my_view
> as
> select
> (
> select
> case
> when (select count(distinct d.paidontime) from t as d) > 1 then
> coalesce(max(b.invoice), a.invoice - 1)
> else 1
> end
> from
> t as b
> where
> b.invoice < a.invoice
> and b.paidontime <> a.paidontime
> ) as group_id,
> a.invoice,
> a.paidontime
> from
> t as a
> go
> select
> t4.rank,
> t3.invoice,
> t3.paidontime
> from
> my_view as t3
> inner join
> (
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> ) as t4
> on t3.group_id = t4.group_id
> order by
> t3.invoice desc
> go
> drop view my_view
> go
> drop table t
> go
>
> AMB
> "mekim" wrote:
>|||u r - Alejandro-mazing!!! - it works EXACTLY the way I need to
I know my "posting" was not the clearest - so thx for figuring that out also
Alejandro - I can understand basically "what u did - I'm just not so certain
"how u did" it - kind of intense - but I am certainly putting the effort
into deciphering it
My Very Much Thx!!!
Mekim
"Alejandro Mesa" wrote:
> Correction,
> use tempdb;
> go
> create table t(
> invoice int not null unique,
> paidontime char(3) not null check(paidontime in ('no', 'yes'))
> )
> insert into t values(1006, 'yes')
> insert into t values(1005, 'yes')
> insert into t values(1004, 'no')
> insert into t values(1003, 'yes')
> insert into t values(1002, 'yes')
> insert into t values(1001, 'yes')
> insert into t values(1000, 'no')
> go
> create view my_view
> as
> select
> (
> select
> case
> when exists(select * from t as c where c.invoice < a.invoice and
> c.paidontime <> a.paidontime) then max(b.invoice)
> else -1
> end
> from
> t as b
> where
> b.invoice < a.invoice
> and b.paidontime <> a.paidontime
> ) as group_id,
> a.invoice,
> a.paidontime
> from
> t as a
> go
> select * from my_view
> go
> select
> t4.rank,
> t3.invoice,
> t3.paidontime
> from
> my_view as t3
> inner join
> (
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> ) as t4
> on t3.group_id = t4.group_id
> order by
> t3.invoice desc
> go
> drop view my_view
> go
> drop table t
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||You are welcome. Here is a new version, I changed the view definition a
little bit.
I am supposing that invoice numbers are unique and greater than zero. Based
on that, I am using the max invoice number that is less than current and
which paidontime value is diff from the current as the group_id (excuse my
english). If you select from the view, you will see:
group_id invoice paidontime
-- -- --
1004 1006 yes
1004 1005 yes
1003 1004 no
1000 1003 yes
1000 1002 yes
1000 1001 yes
-1 1000 no
then you can use the tehcnique describe in KB Q186133, to calculate the rank
:
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
if you join previous statement with the view by group_id, then you have all
the info needed.
use tempdb;
go
create table t(
invoice int not null unique check (invoice > 0),
paidontime char(3) not null check(paidontime in ('no', 'yes'))
)
insert into t values(1006, 'yes')
insert into t values(1005, 'yes')
insert into t values(1004, 'no')
insert into t values(1003, 'yes')
insert into t values(1002, 'yes')
insert into t values(1001, 'yes')
insert into t values(1000, 'no')
go
create view my_view
as
select
(
select
coalesce(max(b.invoice), -1)
from
t as b
where
b.invoice < a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
go
select * from my_view
go
select
t4.rank,
t3.invoice,
t3.paidontime
from
my_view as t3
inner join
(
select
t1.group_id,
count(distinct t2.group_id) as rank
from
my_view as t1
inner join
my_view as t2
on t1.group_id <= t2.group_id
group by
t1.group_id
) as t4
on t3.group_id = t4.group_id
order by
t3.invoice desc
go
drop view my_view
go
drop table t
go
AMB
"mekim" wrote:
> u r - Alejandro-mazing!!! - it works EXACTLY the way I need to
> I know my "posting" was not the clearest - so thx for figuring that out al
so
> Alejandro - I can understand basically "what u did - I'm just not so certa
in
> "how u did" it - kind of intense - but I am certainly putting the effort
> into deciphering it
> My Very Much Thx!!!
> Mekim
> "Alejandro Mesa" wrote:
>|||Hi Alejandro,
Hmmm...The 2nd one seems to be more efficent - which I'm not sure why - but
I will try to figure it out - but this brings up an interesting option for m
e
- going the other direction as well
It turns out it's more important to know the "prior" & "Next" invoice out of
the group series then any sort of ranking
Is it possible to get the output to look as follows in one view? I got it
to work separately - but not together w/o a join
group_id2 group_id invoice paidontime
-- -- -- --
-1 1004 1006 yes
-1 1004 1005 yes
1005 1003 1004 no
1004 1000 1003 yes
1004 1000 1002 yes
1004 1000 1001 yes
1001 -1 1000 no
I tried and it works (and again - I have not fully decipered the SQL
Statement so plz excuse the question)
HOWEVER - Is there a way to merge these two in the same statement or does it
need to be a join of some sorts
the follow view gives me these results
group_id invoice paidontime
-- -- --
-1 1006 yes
-1 1005 yes
1005 1004 no
1004 1003 yes
1004 1002 yes
1004 1001 yes
1001 1000 no
drop view my_view
go
create view my_view
as
select
(
select
case
when exists(select * from t as c where c.invoice > a.invoice and
c.paidontime <> a.paidontime) then Min(b.invoice)
else -1
end
from
t as b
where
b.invoice > a.invoice
and b.paidontime <> a.paidontime
) as group_id,
a.invoice,
a.paidontime
from
t as a
The best I can see is I need to join these two views via Invoice - which is
fine - however - greed kicks in to be more efficent
again - I think this is so how u pulled it off - I guess u've probably
figured out that I tried many times w/o success to make it work :-)
Best Regards,
Mekim
"Alejandro Mesa" wrote:
> You are welcome. Here is a new version, I changed the view definition a
> little bit.
> I am supposing that invoice numbers are unique and greater than zero. Base
d
> on that, I am using the max invoice number that is less than current and
> which paidontime value is diff from the current as the group_id (excuse my
> english). If you select from the view, you will see:
> group_id invoice paidontime
> -- -- --
> 1004 1006 yes
> 1004 1005 yes
> 1003 1004 no
> 1000 1003 yes
> 1000 1002 yes
> 1000 1001 yes
> -1 1000 no
> then you can use the tehcnique describe in KB Q186133, to calculate the ra
nk:
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> if you join previous statement with the view by group_id, then you have al
l
> the info needed.
> use tempdb;
> go
> create table t(
> invoice int not null unique check (invoice > 0),
> paidontime char(3) not null check(paidontime in ('no', 'yes'))
> )
> insert into t values(1006, 'yes')
> insert into t values(1005, 'yes')
> insert into t values(1004, 'no')
> insert into t values(1003, 'yes')
> insert into t values(1002, 'yes')
> insert into t values(1001, 'yes')
> insert into t values(1000, 'no')
> go
> create view my_view
> as
> select
> (
> select
> coalesce(max(b.invoice), -1)
> from
> t as b
> where
> b.invoice < a.invoice
> and b.paidontime <> a.paidontime
> ) as group_id,
> a.invoice,
> a.paidontime
> from
> t as a
> go
> select * from my_view
> go
> select
> t4.rank,
> t3.invoice,
> t3.paidontime
> from
> my_view as t3
> inner join
> (
> select
> t1.group_id,
> count(distinct t2.group_id) as rank
> from
> my_view as t1
> inner join
> my_view as t2
> on t1.group_id <= t2.group_id
> group by
> t1.group_id
> ) as t4
> on t3.group_id = t4.group_id
> order by
> t3.invoice desc
> go
> drop view my_view
> go
> drop table t
> go
>
> AMB
> "mekim" wrote:
>

group page break adding blank pages

I have a report that I am grouping the data and setting the page to break
after each group (check box "Page break at end"). When I View Report in
Preview, the report looks perfect. Four pages, four groups. In this case,
each group fits on one page. When I click on the magnifying glass to see the
print view, it returns 8 pages. There is a completely blank page (except for
page header) on every even page. This is the case when a group goes to two
pages as well. If I run the report and get 5 pages with 4 groups, I still
get 10 pages when viewing for printing.
How can I keep the page break and get rid of the extra page? This is really
bad when the report is over 1000 pages... making it over 2000 pages for
nothing."SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
news:0F2CDB4D-6130-4D9B-A382-028941BCA3CF@.microsoft.com...
>I have a report that I am grouping the data and setting the page to break
> after each group (check box "Page break at end"). When I View Report in
> Preview, the report looks perfect. Four pages, four groups. In this
> case,
> each group fits on one page. When I click on the magnifying glass to see
> the
> print view, it returns 8 pages.
Hi Sharin,
Try to figure out what element is causing this behavior. In general if you
set to invisible only one element and everything will get in order. Find the
wrong element and then look for problems in it.
Regards,
--
Martin Kulov
http://www.codeattest.com/blogs/martin
MCAD Charter Member
MCSD.NET Early Achiever
MCSD|||I tried setting each element to hidden one at a time and nothing changed.
I finally got it to stop happening though. My page width property is 5.5
inches. For an 8 inch page with half inch margins. I'm wasting all that
space. But if I make my page 5.5 inches (and therefore my table has to fit
in that), then it doesn't happen anymore. Any suggestions on why this is
happening?
"Martin Kulov" wrote:
> "SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
> news:0F2CDB4D-6130-4D9B-A382-028941BCA3CF@.microsoft.com...
> >I have a report that I am grouping the data and setting the page to break
> > after each group (check box "Page break at end"). When I View Report in
> > Preview, the report looks perfect. Four pages, four groups. In this
> > case,
> > each group fits on one page. When I click on the magnifying glass to see
> > the
> > print view, it returns 8 pages.
> Hi Sharin,
> Try to figure out what element is causing this behavior. In general if you
> set to invisible only one element and everything will get in order. Find the
> wrong element and then look for problems in it.
> Regards,
> --
> Martin Kulov
> http://www.codeattest.com/blogs/martin
> MCAD Charter Member
> MCSD.NET Early Achiever
> MCSD
>
>|||I'm having the same issue. I have a group that does a page break the
customer. That field is hidden. The only way I can get the blank page to
not print is to remove the page break in the table group. Is there a way to
have a page break and not have that blank page print?
"Martin Kulov" wrote:
> "SharinDenver" <SharinDenver@.discussions.microsoft.com> wrote in message
> news:0F2CDB4D-6130-4D9B-A382-028941BCA3CF@.microsoft.com...
> >I have a report that I am grouping the data and setting the page to break
> > after each group (check box "Page break at end"). When I View Report in
> > Preview, the report looks perfect. Four pages, four groups. In this
> > case,
> > each group fits on one page. When I click on the magnifying glass to see
> > the
> > print view, it returns 8 pages.
> Hi Sharin,
> Try to figure out what element is causing this behavior. In general if you
> set to invisible only one element and everything will get in order. Find the
> wrong element and then look for problems in it.
> Regards,
> --
> Martin Kulov
> http://www.codeattest.com/blogs/martin
> MCAD Charter Member
> MCSD.NET Early Achiever
> MCSD
>
>sql

Group by month

Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #TestTry this:
SELECT DATEADD(MONTH,mth,'20000101') AS startdate,
DATEADD(MONTH,mth,'20000131') AS enddate,
person, COUNT(*) AS cnt
FROM
(SELECT DATEDIFF(MONTH,'20000101',datecreated) AS mth, person
FROM #Test) AS T
GROUP BY mth, person ;
If you want to include rows in the result for months that have no data
in your table then join the above query with a calendar table or
numbers table to generate the extra months.
David Portas
SQL Server MVP
--|||Thanx for posting DDL and INSERT's:
select
cast (convert (char (6), DateCreated, 112) + '01' as datetime) StartDate
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01')) EndDate
, Person
, count (*)
from
#Test
group by
cast (convert (char (6), DateCreated, 112) + '01' as datetime)
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01'))
, Person
order by
Person
, StartDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #Test|||Try,
-- for each year and month
SELECT
min(cast(convert(varchar(6), Datecreated, 112) + '01' as datetime)) as
Startdate,
dateadd(day, -1, dateadd(month, 1, min(cast(convert(varchar(6),
Datecreated, 112) + '01' as datetime)))) as Enddate,
Person,
count(*) as [Count]
FROM
#Test
group by
convert(varchar(6), Datecreated, 112),
Person
go
AMB
"Lasse Edsvik" wrote:

> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>|||This is pretty much the same as Tom's one, but with less conversions
SELECT
MonthAdded AS StartDate
, DATEADD( d , -1 , DATEADD( m , 1 , MonthAdded ) ) AS EndDate
, Person
, Total AS Count
FROM
(
SELECT
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 ) AS MonthAdded
, Person
, COUNT(*) AS Total
FROM
#Test
GROUP BY
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 )
, Person
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>

GROUP BY messes up results of view based on UDF !?!

Hi there,
we have a rather strange effect here were a group by on a view does not
return the expected results. We managed to nail it down to the fact that the
view is based on multiple fields being the result of the same User Defined
Function, but with different arguments. The error seems consistent and in
the example below you can easily see how it fails on the simpelest udf/view
on the pubs db.
Anyone can explain this ' Or better, tell us how to work around it ' (we
may have several situations in our application where this goes wrong, ly
we only just found out)
Thanks.
Cu
Roby
An example : (Pubs db)
DROP FUNCTION dbo.fn_to_upper_or_lower
GO
CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
@.int1 int) -- 0 = UPPER, 1 =
lower
RETURNS varchar(1024)
AS
BEGIN
DECLARE @.result varchar(1024)
IF @.int1 = 0
BEGIN
SELECT @.result = Upper(@.string1)
END
ELSE
BEGIN
SELECT @.result = Lower(@.string1)
END
Return(@.result)
END
GO
-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
-- dbo.fn_to_upper_or_lower('hello', 1)
-- GO
DROP VIEW test
GO
CREATE VIEW test
AS
SELECT title_id,
pub_id,
title,
notes,
upper_case = dbo.fn_to_upper_or_lower (title, 0),
lower_case = dbo.fn_to_upper_or_lower (title, 1)
FROM titles
GO
SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
GROUP BY title_id, title, upper_case, lower_case, notesLooks like a bug. I reported it and will be back when I have any info.
Tested on SQL2K Dev/SP3.
Bug seems to be fixed in Yukon (tested on CTP2).
BG, SQL Server MVP
www.SolidQualityLearning.com
"deroby" <deroby@.discussions.microsoft.com> wrote in message
news:3C4C4B2B-0646-4D24-9388-F96613CC3A1B@.microsoft.com...
> Hi there,
> we have a rather strange effect here were a group by on a view does not
> return the expected results. We managed to nail it down to the fact that
> the
> view is based on multiple fields being the result of the same User Defined
> Function, but with different arguments. The error seems consistent and in
> the example below you can easily see how it fails on the simpelest
> udf/view
> on the pubs db.
> Anyone can explain this ' Or better, tell us how to work around it ' (we
> may have several situations in our application where this goes wrong,
> ly
> we only just found out)
> Thanks.
> Cu
> Roby
> --
> An example : (Pubs db)
> DROP FUNCTION dbo.fn_to_upper_or_lower
> GO
> CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
> lower
> RETURNS varchar(1024)
> AS
> BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
> END
> GO
> -- SELECT dbo.fn_to_upper_or_lower('hello', 0),
> -- dbo.fn_to_upper_or_lower('hello', 1)
> -- GO
> DROP VIEW test
> GO
> CREATE VIEW test
> AS
> SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
> GO
>
> SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>|||Roby,
This is a known bug. See this thread for details and some suggested
workarounds.
http://groups.google.co.uk/groups?h...&q=kryszak+kass
The bug occurs only in very restricted situations, so a workaround is
usually possible.
Steve Kass
Drew University
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Roby,
Also see http://support.microsoft.com/kb/883415.
SK
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Thx for the replies guys.
Bit strange to find this has been solved quite a while ago but still is in a
hotfix that only the happy few can get. And then still they make it sound as
if you'd rather prefer not to insall it all...
We'll have a look at the workarounds, they seem do-able, but it sure is a
pain =( I guess optimizers aren't always a developpers best friend...
Cu
Roby
"Steve Kass" wrote:

> Roby,
> Also see http://support.microsoft.com/kb/883415.
> SK
> deroby wrote:
>
>

Group by in a view can this be used ?

In a complex view the group by gives strange (wrong) results.
(Adding a group by and having clause generates more rows instead of less).
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
from view_A
Results in : 720 rows
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F, count(*)
from view_A
group by A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
having count(*) > 1
Results in : 33678 rows (a lot of them containing a 1 in the count(*)
column)
(There should be only 57 rows)
If the view is put into a table : select * into table_A from view_A
First result : 720
Second result : 57
Is this a (known) bug,
(Removing the count(*) from the first query results in a :
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.
)
The view uses union to get the results form 3 queries, which each have
several tables. There are no correlated subqueries.
ben brugmanPlease can you post some code to reproduce the problem: the DDL for the base
tables and the view (just the key columns and the columns involved in the
query will do) plus a small sample of data (post as INSERT statements).
--
David Portas
--
Please reply only to the newsgroup
--|||I could generate a sample, but I would have
to anominise all data and meta data (table, view and column names).
There is a number of tables involved and I would have
to create suetable data.
(Sorry my organisation does not allow me to do this otherwise).
But then it does take such a form that I do not expect anybody to
look at the problem. And it would take a considerable amount of time
to prepare this.
At the end of this message I have done this only for the views and the
offending queries.
(Just as an example to show that this is not very user friendly).
Thanks for your attention
ben brugman
LOOK AT THE EXAMPLE AT YOUR OWN PERIL.
/* View for a selection. */
CREATE VIEW dbo.View_S
AS
SELECT T157TABLE.F637FIELD,
T157TABLE.F687FIELD AS F346FIELD,
T157TABLE.F638FIELD,
'SE' AS F347FIELD,
T116TABLE.F280FIELD AS F345FIELD,
T104TABLE.F703FIELD AS F238FIELD,
T157TABLE.F652FIELD,
T157TABLE.F744FIELD,
T157TABLE.F745FIELD,
T116TABLE.F277FIELD AS F246FIELD,
T116TABLE.F278FIELD AS F342FIELD,
T157TABLE.F324FIELD, T157TABLE.F309FIELD,
T157TABLE.F311FIELD,
T157TABLE.F588FIELD,
T157TABLE.F590FIELD,
T157TABLE.F747FIELD
FROM T157TABLE INNER JOIN
T116TABLE ON
T116TABLE.F637FIELD = T157TABLE.F637FIELD AND
T116TABLE.F687FIELD = T157TABLE.F687FIELD
LEFT OUTER JOIN
T104TABLE ON
T104TABLE.F637FIELD = T157TABLE.F637FIELD AND
T104TABLE.F687FIELD = T157TABLE.F687FIELD
AND T104TABLE.F230FIELD = 'SIS'
/* The main View */
CREATE VIEW dbo.View_A
AS
SELECT T122TABLE.F637FIELD,
T122TABLE.F344FIELD AS F346FIELD,
T122TABLE.F638FIELD,
'CT' AS F347FIELD,
T122TABLE.F262FIELD AS F345FIELD,
T122TABLE.F238FIELD,
T122TABLE.F246FIELD, T122TABLE.F342FIELD,
T122TABLE.F324FIELD,
T122TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T122TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T122TABLE.F747FIELD, NULL AS F652FIELD, NULL
AS F744FIELD, NULL
AS F745FIELD
FROM T122TABLE, T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T122TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T122TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT T123TABLE.F637FIELD,
T123TABLE.F348FIELD AS F346FIELD,
T123TABLE.F638FIELD,
'TT' AS F347FIELD,
T123TABLE.F737FIELD AS F345FIELD,
T123TABLE.F238FIELD,
T123TABLE.F246FIELD,
T123TABLE.F342FIELD,
T123TABLE.F324FIELD,
T123TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T123TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T123TABLE.F747FIELD, NULL
AS F652FIELD, NULL AS F744FIELD, NULL
AS F745FIELD
FROM T123TABLE,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T123TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T123TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT View_S.F637FIELD,
View_S.F346FIELD,
View_S.F638FIELD,
View_S.F347FIELD,
View_S.F345FIELD,
View_S.F238FIELD,
View_S.F246FIELD,
View_S.F342FIELD,
View_S.F324FIELD,
View_S.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
View_S.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
View_S.F747FIELD,
View_S.F652FIELD,
View_S.F744FIELD,
View_S.F745FIELD
FROM View_S,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE View_S.F311FIELD = IHCP_creator.F702FIELD
AND
View_S.F590FIELD = IHCP_mutator.F702FIELD
/* The query which goes 'wrong' */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD, count(*) from View_A
group by F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD
having count(*) > 1
/* The simple Query */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD from View_A

Gridview refresh after update

Hi All,

I am new to development of asp. I have an SQLDataSource set as the data source for a grid view. When I click on the edit link in the Gridview, change the data, and click update, the old data is still displayed in the row.

I found exact same issue as here --http://forums.asp.net/thread/1217014.aspx

Solution in the above thread is to add this

{
if (reader != null) reader.Close();
}
conn.Close();

How do I apply above solution in my situation ?

I am updating through stored procedure.and don't have code at background. My code is

Datasource :

<

asp:SqlDataSourceID="ds"runat="server"ConnectionString="<%$ ConnectionStrings:ds %>"

CancelSelectOnNullParameter="False"ProviderName="<%$ ConnectionStrings:ds.ProviderName%>"UpdateCommand="usp_save"UpdateCommandType="StoredProcedure"EnableCaching="False">

<UpdateParameters>

<asp:ParameterName="field1"Type="String"/><asp:ParameterName="field2"Type="String"/><asp:ParameterName="field3"Type="String"/><asp:ParameterName="field4"Type="String"/><asp:ControlParameterName="field5"Type="String"ControlID="label7"/></UpdateParameters>

Anyone Please ?? Help me with this . I am still not able to find the solution.

Thanks in advacne

|||Where is your select statement?|||

Thanks for the reply .

I do select also using stored procedure. I can post stored procedure code if needed.

Here is the full sqldatasource and function

<asp:SqlDataSource
ID="idpl"
runat="server"
ConnectionString="<%$ ConnectionStrings:idpl %>"
SelectCommand="sp_Mapping"
SelectCommandType="StoredProcedure"
CancelSelectOnNullParameter="False"
ProviderName="<%$ ConnectionStrings:idpl.ProviderName%>"
UpdateCommand="sp_SaveMapping"
UpdateCommandType="StoredProcedure"
OnUpdating="pnl_Updating" EnableCaching="False">
<SelectParameters>
<asp:ControlParameter ControlID="Txt1" Name="OriginalID" Type="String" PropertyName="Text" DefaultValue="" />
<asp:ControlParameter ControlID="Txt2" Name="Name" Type="String" PropertyName="Text" DefaultValue="" />
<asp:ControlParameter ControlID="DDL" Name="sName" Type="String" DefaultValue="None" PropertyName="SelectedValue" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="OriginalID" Type="String" />
<asp:Parameter Name="sName" Type="String" />
<asp:Parameter Name="PartNum" Type="String" />
<asp:Parameter Name="plantName" Type="String"/>
<asp:ControlParameter Name="userID" Type="String" ControlID = "label7" />
</UpdateParameters>
</asp:SqlDataSource>

protected void pnl_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
DbParameterCollection CmdParams = e.Command.Parameters;
ParameterCollection UpdParams = ((SqlDataSourceView)sender).UpdateParameters;

Hashtable ht = new Hashtable();
foreach (Parameter UpdParam in UpdParams)
ht.Add(UpdParam.Name, true);

for (int i = 0; i < CmdParams.Count; i++)
{
if (!ht.Contains(CmdParams[i].ParameterName.Substring(1)))
CmdParams.Remove(CmdParams[i--]);
}

}

|||

Does the database values change?

If no, then the update isn't happening correctly, use the sql profiler to see what is being generated, and why it is failing to update correctly.

If yes, then in the sqldatasource's Updated event, add a gridview.databind and see if that resolves your problem. If it does not, place a breakpoint in the sqldatasource's Selecting event, and make sure that it is getting called after an update.

|||

Yes .. Database value changes.So stored procedure is definitely working.

I'll try to follow your suggestions on updated event and update the post soon.

Thanks for your help.

|||

Hi Motley,

I followed your suggestion.

1. Added gridview.databind at "updated" event.

2. Applied the breakpoint and made sure that the even is getting fired.

Still having the same issue. Gridview still shows two rows. Old and newly updated.

Any more pointers will be greatly appreciated.

Thanks

|||

Not sure if your problem is fixed or not. Sounds like your update statement has truncated to an insert statement. Have you looked into make sure it is pulling the PK of the table that singularly references the field you are looking for?

Grid view-cant update or delete

I put a grid view on a web form ,when I run it -the SELECT, EDIT works

the UPDATE,DELETE makes an error although I use the sama data,I added the error :

Anyone can help?

Server Error in '/CrystalReportsWebSite1' Application.

---

The data types text and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): The data types text and nvarchar are incompatible in the equal to operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140

Hey,

What does those update/delete stored procedures look like? It seems like it may be an issue with the query.

|||I'm guessing he has a text field, and he told it to use optimistic concurrency or (CompareAllValues), which doesn't work with text fields.