Solved

SQL 2005 / 2008 query

Posted on 2010-11-10
20
348 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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