Solved

SQL 2005 / 2008 query

Posted on 2010-11-10
20
351 Views
Last Modified: 2012-05-10
Hi Experts,

I would get some samples on how to make the following :

1. I have product table where price for the same product can be different even during one day
2. I need to get report giving me average price for each product grouped by product and time frame in items 3 and 4
3. I have to get it using first Biweekly [excluding days when this price was null]
4. and Bimonthly  [excluding days when this price was null]

Table has multiple rows for the same product just a reminder.

Thanks.
0
Comment
Question by:fpoyavo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
20 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34103610
can you give your table description and data sample
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34105306
Try the below:
--for bi-weekly-
select 	dateadd(d, datediff(d, 0, yourdatecolumn / 14) * 14, 0),
	avg(price)
from yourtable
where price is not null
group by dateadd(d, datediff(d, 0, yourdatecolumn / 14) * 14, 0)

--for bi-monthly----
select 	dateadd(m, datediff(m, 0, yourdatecolumn / 2) * 2, 0),
	avg(price)
from yourtable
where price is not null
group by dateadd(m, datediff(m, 0, yourdatecolumn / 2) * 2, 0)

Open in new window

0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34113457
Ralmada, May you provide function [like stored proc]  to get weeks of the present month ?  Thanks.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 41

Expert Comment

by:ralmada
ID: 34113485
you don't need a function to do so, just add it to the where clause

select 	dateadd(d, datediff(d, 0, yourdatecolumn / 14) * 14, 0),
	avg(price)
from yourtable
where price is not null and yourdatecolumn >= dateadd(m, datediff(m, 0, getdate()), 0) and yourdatecolumn < dateadd(m, datediff(m, 0, getdate())+1, 0)
group by dateadd(d, datediff(d, 0, yourdatecolumn / 14) * 14, 0)

Open in new window

0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34113626
Msg 260, Level 16, State 3, Line 6
Disallowed implicit conversion from data type smalldatetime to data type int, table 'yourtable', column 'yourdatecolumn'. Use the CONVERT function to run this query.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34114014
well, first make sure you adjust the column names accordingly. I used 'yourtable' and 'yourdatecolumn' as an example because I don't know your real table and column names
 
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34114230
:))) I took your names and already did some work.

Please what your query is producing. I think we are getting close.
Wrong-result.png
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34114279
And for BiMonthly it should not take prior or future month row into calculations. It does now.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34114374
So what is your expected result? can you post how it should like?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34114430
>>Please what your query is producing. I think we are getting close. << it's not that you get wrong results, you mentioned that you wanted to filter by current month, so that's what the query is doing. so, values from October are not taken into consideration.
 
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34114555
In the mean time please give this a try for bi-weekly

select 	dateadd(d, datediff(d, 0, real_Date / 14) * 14, 0),
	avg(price)
from yourtable
where 	price is not null and 
	Real_Date >= dateadd(d, datediff(d, 0, getdate() / 14) * 14, 0) - 14 and 
	Real_Date < dateadd(d, datediff(d, 0, getdate() / 14) * 14, 0) + 14
group by dateadd(d, datediff(d, 0, Real_Date / 14) * 14, 0)

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 34114587
and for bi-monthly
select 	dateadd(m, datediff(m, 0, real_Date / 2) * 2, 0),
	avg(price)
from yourtable
where 	price is not null and 
	Real_Date >= dateadd(m, datediff(m, 0, getdate() / 2) * 2, 0) - 1 and 
	Real_Date < dateadd(m, datediff(m, 0, getdate() / 2) * 2, 0)
group by dateadd(m, datediff(m, 0, Real_Date / 2) * 2, 0)

Open in new window

0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34115009
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34115035
For you earlier post :
So what is your expected result? can you post how it should like? >>>>>>>

The result is missing week of 11/1/2010 and does not properly calculates average price as shown in the attached image.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34115040
For some reason your latest queries cannot be even executed.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 34115087
Please post an example of the expected result, I don't understand what you mean by "is missing week fo 11/1/2010". The results for week 11/1/2010 is grouped in with the bi-weekly period starting 25/10/2010. And if you check the avg for the period starting 25/10/2010 the result of (11 + 11.50 + 12 ) / 3 = 11.50 . Since you mentioned you wanted the current month the two october dates that belongs to the period starting to 25/10 are filtered out.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 34115096
regarding the error message check the below
select 	dateadd(d, datediff(d, 0, real_Date / 14) * 14, 0),
	avg(price)
from yourtable
where 	price is not null and 
	Real_Date >= dateadd(d, datediff(d, 0, getdate() / 14) * 14 - 14, 0) and 
	Real_Date < dateadd(d, datediff(d, 0, getdate() / 14) * 14 + 14, 0)
group by dateadd(d, datediff(d, 0, Real_Date / 14) * 14, 0)

Open in new window

0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34115189
That's what I would see.
The-result-needed.png
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34115202
Your query still cannot be executed.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 34115263
Ok its fine now. Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question