Grouping By A Certain Range

I would like to create a report that would group the items by their
price. For example, I would like a group for all of the items that are
between 0-50 and then 51-100 (and so on).
Does anyone know an easy way for doing that using Visual Studio?
ANY information is appreciated. Thanks in advance!you can create a "calulated" field in your dataset like:
=iif(MyPrice > 50, '51-100', '0-50')
then use this new field in your report to render it. (group, filter, sort or
any other option which could use a formula)
"axels22" <ericspreher@.hotmail.com> wrote in message
news:1137429986.049744.315060@.g44g2000cwa.googlegroups.com...
>I would like to create a report that would group the items by their
> price. For example, I would like a group for all of the items that are
> between 0-50 and then 51-100 (and so on).
> Does anyone know an easy way for doing that using Visual Studio?
> ANY information is appreciated. Thanks in advance!
>

Group Update of Prices

I am trying to write a SP that will update the price of products that are in a particular category.

I have 3 tables;
PRODUCT
- productID, price

CATEGORY
- categoryID, categoryName, parentID

PRODUCT_CATEGORY_MAP
- productID, categoryID

What I want to do, is allow admin to pass is a new price and categoryID, and it will update all the products that are within the supplied category.

I will start with:
UPDATE PRODUCT SET price = @.price WHERE categoryID = @.categoryID
however I do not have categoryID within the PRODUCT table

Do I place a SELECT statement after the WHERE?

DECLARE @.productID int
UPDATE PRODUCT SET price = @.price WHERE (SELECT @.productID = productID FROM PRODUCT_CATEGORY_MAP WHERE categoryID=@.categoryID)

Not sure if that would work or not or if I am on the right track.
Thanks for any help,
Mick

You just join in the other tables like this (given that you only need categoryID and that's in the map table, there is no need to join Category too, although you could if you were filtering on Category name for example)

UPDATE Product SET price = @.price
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID = @.categoryID

|||

Ok, I ended up figuring it out, but using IN instead of INNER JOIN. I like yours better.

So I want to extend this a little more...
Say I have the categories
ID Name ParentID
1 Cat1 0
2 Cat2 1

I have assigned a product to Cat2
productID categoryID
1 2

I now want my SP to work so that if a user selects the category Cat1, it will also update all child products.
I have a SP that gets all the child categories...
SELECT categoryID FROM Category
WHERE categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))

I have this so far, but it only updates categories in the selected category, not it's childs as well.
UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
WHERE PCM.categoryID IN
(SELECT categoryID FROM Category C
WHERE C.categoryID IN (SELECT id FROM dbo.GetChildren(@.categoryID))
AND PCM.categoryID=@.categoryID)

Thanks again,
Mick

|||

UPDATE Product SET ourUSDPrice = @.newPrice
FROM Product P
INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID
INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id

--or

UPDATE Product SET ourUSDPrice = @.newPrice
Where Exists (Select ProductId From Product P INNER JOIN Product_Category_Map PCM ON PCM.productID = P.productID INNER JOIN dbo.GetChildren(@.categoryID)) as ChildCategory on PCM.categoryID = ChildCategory.Id)

|||Mani has posted two ways to make your query work - do you have the GetChildren function working, so that it gets all the children at all levels? I wasn't sure from your post whether you were asking about that or not.

Group By, Max(date) query problem

Hello all:

I have an invoice header and detail tables and a customer table using sqlserver 2005. The Detail invoice table has price and product id. The header has the date and customerID.

I need to create a list of the most recent invoice date (and the product price) for each product for each customer. I can't use the group by because when I select both the max(date) and the price (as well as the productID and customerID which all have to be included in the group by) , I get more than one date per product per customer. I can only get the most recent date when I leave out the price and headerdetailID from the field selection.

Any help would be appreciated. Here is sample data & results.

invoiceHeadertable

invheaderID CustomerID InvoiceDate

1 40 1/1/2006

2 40 4/1/2006

3 80 3/1/2006

4 80 7/1/2006

5 80 8/12/2006

invoicedetailtable

invdetail ID invheaderID productcode price

11 1 AA 1.50

12 1 BB 1.30

13 1 CC 1.00

21 2 AA 1.40

23 2 CC 2.00

24 3 AA 2.00

25 3 CC 2.10

26 3 EE 1.10

27 4 AA 1.00

28 4 CC 2.00

29 4 EE 0.99

34 5 EE 1.55

CustomerTable

CustomerID Customername

40 johnCorp

80 maryCorp

Results

customer product most recent invoice(for this product) price

JohnCorp 40 AA 4/1/2006 1.40

JohnCorp 40 BB 1/1/2006 1.30

JohnCorp 40 CC 4/1/2006 2.00

maryCorp 80 AA 7/1/2006 1.40

maryCorp 80 CC 7 /1/2006 2.00

maryCorp 80 EE 8/12/2006 1.55

Something like this 'should' work for you. (Untested)

SELECT
dt.Customer,
dt.Product,
dt.InvoiceDate
d.Price
FROM InvoiceDetailTable d
JOIN ( SELECT
Customer,
Product,
InvoiceDate = max( InvoiceDate )
FROM InvoiceHeaderTable h
JOIN InvoiceDetailTable d
ON h.InvHeaderID = d.InvHeaderID
GROUP BY
Customer,
Product
) dt
ON ( d.Customer = dt.Customer
AND d.Product = dt.Product
AND d.InvoiceDate = dt.InvoiceDate
)

|||Crapola, didn't check your tables first. So ignore the prevoius exercise in 'egg on my face'.|||

IF there was no more than one invoice per day for a customer, you could, in the derived table (dt), include in the SELECT list:

InvHeaderID = max( InvHeaderID )

And then JOIN ON InvHeaderID instead of the three fields I indicated.

Of course, if there were two invoices for the same customer in a day, that would still not be the correct solution.

|||

Well, most of the time there would only be one invoice. But as is always the case, there can be an exception.

I had also wondered if the rank and partition function in sql server 2005 could apply, then one could just use a select query (if this is possible) to return the # 1 invoice per group but I have found no examples showing this used in a group by and where there are multiple tables involved.

Thanks

smhaig

|||

select

ct.CustomerName as Customer,

ct.CustomerID as CustomerID,

idt.ProductCode as Product,

iht.InvoiceDate as MostRecentInvoiceDate,

idt.Price as Price

from InvoiceDetailTable as idt

join InvoiceHeaderTable as iht

on idt.invheaderID = iht.invheaderID

join CustomerTable as ct

on ct.CustomerID = iht.CustomerID

where not exists

(select 1

from InvoiceDetailTable as idtx

join InvoiceHeaderTable as ihtx

on idtx.InvHeaderID = ihtx.InvHeaderID

where idtx.ProductCode = idt.ProductCode

and ihtx.CustomerID = iht.CustomerID

and ihtx.InvoiceDate > iht.InvoiceDate)

order by

ct.CustomerName,

idt.ProductCode

|||

-- Using SQL Server 2005

set nocount on
set dateformat mdy

create table invoiceHeadertable(invheaderID int,CustomerID int,InvoiceDate datetime)
insert into invoiceHeadertable(invheaderID ,CustomerID ,InvoiceDate )
select 1, 40, '1/1/2006' union all
select 2, 40, '4/1/2006' union all
select 3, 80, '3/1/2006' union all
select 4, 80, '7/1/2006' union all
select 5, 80, '8/12/2006'

create table invoicedetailtable(invdetailID int, invheaderID int, productcode char(2), price decimal(5,2))
insert into invoicedetailtable(invdetailID , invheaderID , productcode , price )
select 11, 1, 'AA', 1.50 union all
select 12, 1, 'BB', 1.30 union all
select 13, 1, 'CC', 1.00 union all
select 21, 2, 'AA', 1.40 union all
select 23, 2, 'CC', 2.00 union all
select 24, 3, 'AA', 2.00 union all
select 25, 3, 'CC', 2.10 union all
select 26, 3, 'EE', 1.10 union all
select 27, 4, 'AA', 1.00 union all
select 28, 4, 'CC', 2.00 union all
select 29, 4, 'EE', 0.99 union all
select 34, 5, 'EE', 1.55

create table CustomerTable(CustomerID int, Customername varchar(10))
insert into CustomerTable(CustomerID , Customername )
select 40, 'johnCorp' union all
select 80, 'maryCorp';

with cte(customer,product,[most recent invoice(for this product)],price,rn)
as (
select c.Customername,
d.productcode,
h.InvoiceDate,
d.price,
rank() over(partition by c.Customername,d.productcode order by h.InvoiceDate desc)
from CustomerTable c
inner join invoiceHeadertable h on h.CustomerID=c.CustomerID
inner join invoicedetailtable d on d.invheaderID=h.invheaderID
)
select customer,
product,
[most recent invoice(for this product)],
price
from CTE
where rn=1
order by customer,product

|||

I want to thank Mark and Ron for their solutions and Arnie for getting me to think about two invoices on the same day for the same product and customer (which do exist actually).

I found these solutions to be on the level of advanced lessons for me to study. I have always had trouble with group by when I needed a unique ID on a table where I was selecting a max or min or first one, etc. on another field in the same table.

I have not found any good examples other than very basic ones for rank and partition so if anyone has a good site let me know. Meanwhile I will study what I have as I now have two great ways to solve my problem.

I did not give a duplicate item with my sample data so I will see how this sql 2005 query deals with this. I seem to remember something about ties and ranking and perhaps I could also use select distinct when I select the rank = 1.

The second standard sql query (Ron's) shows me both invoices when there are 2 on same date. It may be that this is the way the data should be displayed if the prices are different, so I will need to check further on that and see if I can tweak these 2 queries to deal with that.

Thank you all again

smHaig

|||

And my thanks to Mark for demonstrating the more modern solution of the two!

Ron

GROUP BY DATEPART issues...

Hi,

I'm trying:

Code Snippet

SELECT SUM(price), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

and, while this executes w/o a problem, I am a bit concerned with my results...

i get a SUM(price) = 2140.21 when running the above SQL for 11/2006

however, when i try:

Code Snippet

SELECT SUM(price)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i get a SUM(price) = 1950.45

if i bump the second date up by one day (i.e. '12/01/2006') i get a SUM(price) = 2140.21, the same value as when I used GROUP BY

any help would be greatly appreciated!

Hi,

What is the result of this query

Code Snippet

SELECT SUM(price), month(order_date), year(order_date)

FROM orders

GROUP BY month(order_date), year(order_date)

HAVING month(order_date)=6 AND year(order_date)=2006

If result is 1950.45 then

u try to use like this syntax

else

check your data one by one ..

|||

Thank you for the response.

I have tried

Code Snippet

...

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and received 2140.21

|||

Is the order_date always truncated at midnight? Try replacing "SUM(price)" with "COUNT(*)" with your queries. Do you get the same record counts for November 2006?

Thanks,
Bryan Smith

|||

I'm not sure about order_date being truncated at midnight... order_date is a datetime, so i am assuming that up until 11:59:59.99PM on 11/30, the date parts I am after remain the same.

I ran the COUNT(*) as suggested and get 804 using:

Code Snippet

SELECT COUNT(*)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

and get 838 when using:

Code Snippet

SELECT COUNT(*), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

the number remains 838 when I add:

Code Snippet

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and drops to 804 if i add:

Code Snippet

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i am baffled, but then again, i'm no expert

thanks for the assistance!

|||

Cool! I think we're on the right track here.

Your original code used "WHERE order_date BETWEEN '11/01/2006' and '11/30/2006'". That means orders created between midnight Nov 1 2006 and midnight at the top of Nov 30 2006. You're dropping orders that occurred from 11/30/2006 12:00:00.001 AM to 11/30/2006 11:59:59.997 PM. That's why the BETWEEN statement gives you 804 records while the DATEPART statement gives you 838 records.

(Please note, SQL Server is only accurate to 3 ms when a datetime data type is used. Times of 11:59:59.998 PM and 11:59:59.999 PM are recorded as 12:00:00.000 AM the next day.)

If you re-write your query to use "WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006 11:59:59.997 PM'" you should get 838 records and your SUM should match the one in the DATEPART query.

Good luck,
Bryan

|||

Ahhh... that makes sense...

would it be correct to assume that the GROUP BY DATEPART... query accurately sums up each months data?

thanks again for your help!

|||

It would. It calculates the month of the date without regard for time, so any orders placed at anytime on 11/30/2006 would fall into November.

B.

group by

I have table
date,type,price
date and price is changing
i need to group it, to have
max(date), type and price (from the row with max date)
it's no problem to take
max(date) and type bat without the third column, i need all columns
HelpUSE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([date] datetime, type char(10), price money
, PRIMARY KEY (type, [date]))
GO

INSERT INTO myTable99([date],type,price)
SELECT '1/1/2001','bat' ,10.00 UNION ALL
SELECT '1/1/2002','bat' ,20.00 UNION ALL
SELECT '1/1/2003','bat' ,30.00 UNION ALL
SELECT '1/1/2004','bat' ,40.00 UNION ALL
SELECT '1/1/2001','ball',40.00 UNION ALL
SELECT '1/1/2002','ball',30.00 UNION ALL
SELECT '1/1/2003','ball',20.00 UNION ALL
SELECT '1/1/2004','ball',10.00
GO

SELECT l.*
FROM myTable99 AS l
JOIN ( SELECT type, MAX([date]) AS MAX_date
FROM myTable99
GROUP BY type) AS r
ON l.type = r.type
AND l.[date] = r.MAX_date

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Long thanks