• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

SQL 2005 / 2008 query

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
fpoyavo
Asked:
fpoyavo
  • 10
  • 9
1 Solution
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
can you give your table description and data sample
0
 
ralmadaCommented:
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
 
fpoyavoAuthor Commented:
Ralmada, May you provide function [like stored proc]  to get weeks of the present month ?  Thanks.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ralmadaCommented:
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
 
fpoyavoAuthor Commented:
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
 
ralmadaCommented:
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
 
fpoyavoAuthor Commented:
:))) 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
 
fpoyavoAuthor Commented:
And for BiMonthly it should not take prior or future month row into calculations. It does now.
0
 
ralmadaCommented:
So what is your expected result? can you post how it should like?
0
 
ralmadaCommented:
>>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
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
fpoyavoAuthor Commented:
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
0
 
fpoyavoAuthor Commented:
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
 
fpoyavoAuthor Commented:
For some reason your latest queries cannot be even executed.
0
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
fpoyavoAuthor Commented:
That's what I would see.
The-result-needed.png
0
 
fpoyavoAuthor Commented:
Your query still cannot be executed.
0
 
fpoyavoAuthor Commented:
Ok its fine now. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now