Solved

SQL 2005 / 2008 query

Posted on 2010-11-10
20
344 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

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.

Question has a verified solution.

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

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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
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…

947 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

22 Experts available now in Live!

Get 1:1 Help Now