Query help

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I don't think this is really the right place to post this question but I
couldn't find any sql groups...

Say there are 3 columns:
idx (key)
product (varchar)
mandate (datetime)


I want to run a query for all products that were sold on more than one day.
For example we may not make a product more than one day and some we might
make for 3 days although a product may have been made more then 1 time in a
day.

Here's a sample:

1 widgetA 1/1/2007
2 widgetB 1/1/2007
3 widgetA 1/1/2007
4 widgetC 1/2/2007
5 widgetD 1/3/2007
6 widgetD 1/3/2007
7 widgetB 1/5/2007
8 widgetD 1/6/2007


So I would want the query to return widgetB and widgetD because they were
both made on more then 1 day.

-Joe
 
Joe said:
I don't think this is really the right place to post this question but I
couldn't find any sql groups...

Say there are 3 columns:
idx (key)
product (varchar)
mandate (datetime)


I want to run a query for all products that were sold on more than one day.
For example we may not make a product more than one day and some we might
make for 3 days although a product may have been made more then 1 time in a
day.

Here's a sample:

1 widgetA 1/1/2007
2 widgetB 1/1/2007
3 widgetA 1/1/2007
4 widgetC 1/2/2007
5 widgetD 1/3/2007
6 widgetD 1/3/2007
7 widgetB 1/5/2007
8 widgetD 1/6/2007


So I would want the query to return widgetB and widgetD because they were
both made on more then 1 day.

-Joe
Click to expand...

SELECT COUNT (dat.idx) as idx
, dat.mandate
, MAX(dat.product) as product
FROM dbo.my_data dat
GROUP BY dat.mandate
HAVING COUNT (dat.idx) > 1

HTH,
Petar Atanasov
http://a-wake.net
 
Hi Petar, This doesn't seem to give me what I want. It looks like the date
is being grouped correctly.

When I run this against ~3200 rows I only get 4 returned although I can
manually see that many, many more should match.

Any idea?

Thanks for the help.
Joe
 
I think the problem is that there is a time component to the datetime field.
This would need to be truncated. hmmm....
 
You could try grouping by convert(int,mandate) instead, like thus:

select
product = product
, days_sold = count(distinct convert(int,mandate))
, num_sold = count(*)
from
data
group by
product
having
count(distinct convert(int,mandate)) > 0


Converting to an integer lops off the time portion of the date, conveniently
=) If you convert to a float/decimal you will see the time portion there.

Also, "mandate" is actually a word. If you want to refer to the date the
product was manufactured, it might be good to say "date_manufactured" or
"manufacture_date", etc. More typing, but it doesn't slow anything down.

//Andrew
 
Stupid me, I adapted without testing enough! We actually use floor() on
the float (or convert to a decimal/18,0), but I just said 'to hell with it'
and thought that INT truncated (it should), but it rounds up instead :(
The things you forget, they are...

so use this instead:
floor(convert(float,mandate))
 
Hello Joe,
Thanks for Petar and Andrew's great idea.

Have you resolved the issue so far? It seems like the group by query
command is what you need.
Does it work on your side?

If the issue still persists, please feel free to update here again. We are
glad to assist you.
Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top