sql query to get data from three table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hello friends ....

i have three tables as follows

SQL

create table pds(
edatetime datetime not null,
	caustic decimal(10,5),
	ratio decimal(10,5),
	solid decimal(10,5),
)


create table ast_wash_gpl_soda(
edatetime datetime not null,
	nt_6o decimal(10,5),
	nt_6u decimal(10,5),
)

create table dms1
(
edatetime datetime not null,
	sodagpldms decimal(10,5),
	soliddms decimal(10,5),
)

all three tables have no reationship among them. they just contained edatetime field as common.
i want to generate report in which user will provide month no and year as parameter. i need to fetch data (average of every fields) from these three tables. it should show average of day of every field in month such as follows

SQL

date         caustic_average         nt_6o_average        soliddms_average
1-apr-2014      25.00                   23.00                 18.32
2-apr-2014      89.65                   65.47                 56.21
3-apr-2014      25.95                   65.65                 51.71
.
.
.
31-apr-2014     95.36                   68.47                 51.25

caustic_avarage is average for day in that month and year.
i.e 25.00 is a caustic average for 1-apr-2014.

nt_6o_average is average for day in that month and year.
i.e 65.47 is a nt_6o average for 2-apr-2014.

soliddms_average is average for day in that month and year.
i.e 51.71 is a soliddms average for 3-apr-2014.

i wanted to make such query in sql server.
plz help

Posted 17-Aug-14 20:08pm

TAUSEEF KALDANE

Updated 17-Aug-14 20:41pm

Magic Wonder

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vinay Mistry · Answer 1 · 2014-08-17T20:44:00

SQL

SELECT CONVERT(varchar(50), pds.edatetime, 106) , AVG(pds.caustic) AS caustic_average,
 AVG(ast_wash_gpl_soda.nt_6o) AS nt_6o_average, AVG(dms1.soliddms) AS soliddms_average
FROM ast_wash_gpl_soda INNER JOIN
dms1 ON CONVERT(varchar(50), ast_wash_gpl_soda.edatetime, 106) = CONVERT(varchar(50), dms1.edatetime, 106) INNER JOIN
pds ON CONVERT(varchar(50), dms1.edatetime, 106) = CONVERT(varchar(50), pds.edatetime, 106)
GROUP BY pds.edatetime

You can use where condition for date range

SQL

Where pds.edatetime between cast(@Month as varchar(2)) + '/01/' + cast(@Year as varchar(4)) and DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(cast(@Month as varchar(2)) + '/01/' + cast(@Year as varchar(4)) as datetime)))

Mangesh4 · Answer 2 · 2014-08-17T20:28:00

Solution 1

HI
TRY THIS

SQL

SELECT PD.edatetime,AVG(PD.caustic ) AS CAUSTIC ,AVG(AW.nt_6o) AS NT_60,AVG(AW.nt_6u) AS NT6U,
   AVG(DM.sodagpldms) AS SODA

  FROM pds PD
INNER JOIN ast_wash_gpl_soda  AS AW ON AW.edatetime=PD.edatetime
INNER JOIN dms1 AS DM ON DM.edatetime=PD.edatetime
GROUP BY PD.edatetime

THANKS
MANGESH

Posted 17-Aug-14 20:28pm

Mangesh4

Updated 17-Aug-14 20:40pm

v2

Comments

TAUSEEF KALDANE 18-Aug-14 2:39am

i have already tried it but i want to pass month and year to query

Mangesh4 · Answer 3 · 2014-08-17T20:56:00

Solution 3

HI
ADD WHERE CONDITION
AS MONTH(PD.edatetime)='8',YEAR(PD.edatetime)='2014'

THANKS
MANGESH

Posted 17-Aug-14 20:56pm