Solved

SQL 2005 / 2008 query

Posted on 2010-11-10
20
342 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now