Solved

SQL 2005 / 2008 query

Posted on 2010-11-10
20
347 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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