Complex query

I have two tables
Daily_Production (Tracks daily production data)
pdate,totalOil
1-Sep-05,30
2-Sep-05,28
'
'
3-Oct-05,30
4-Oct-05,35
Well_test (Welltest happens at any random date)
wellid,testdate,Oil
A,25-Aug-05,2
A,5-Sep-05,3
A,3-Oct-05,2
B,28-Aug-05,4
B,7-Sep-05,5
B,2-Oct-05,5
C,30-Aug-05,1
C,10-Sep-05,1
C,1-Oct-05,2
I want data in this format
ddate TotalOil
-- ddate is daily date which i can take from Daily_Production
-- Total Oil is the sum Oil from last tests on Wells A,B & C on or
before ddate
eg
ddate TotalOil
10-Sep-2005, 9
9= 3 ->from test on well A on 5-Sep-05 (It should not consider the test
oof 25-Aug-05)
+
5-> test on well B on 7-Sep-05
+
1-> test on well C on 10-Sep-05
i.e 3+5+1=9
Basically if my ddate is 10-Sep-2005 then I want the sum of Oil from
the last welltest done on wells A,B,C and <= ddate.
I hope my question is clear.>> I hope my question is clear.
Not exactly. Post your table structures, sample data & expected results.
Read www.aspfaq.com/5006 for details on how you can provide this without any
ambiguity.
Anith|||I believe this will work if I understand the problem correctly:
select pdate,
totaloild = (select sum(totaloil) from
(select max(oil) as totaloil from welltest where testdate <= pdate group by
wellid) as sub)
from dailyprod
JR
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:1128518309.256857.182330@.g47g2000cwa.googlegroups.com...
>I have two tables
> Daily_Production (Tracks daily production data)
> pdate,totalOil
> 1-Sep-05,30
> 2-Sep-05,28
> '
> '
> 3-Oct-05,30
> 4-Oct-05,35
> Well_test (Welltest happens at any random date)
> wellid,testdate,Oil
> A,25-Aug-05,2
> A,5-Sep-05,3
> A,3-Oct-05,2
> B,28-Aug-05,4
> B,7-Sep-05,5
> B,2-Oct-05,5
> C,30-Aug-05,1
> C,10-Sep-05,1
> C,1-Oct-05,2
> I want data in this format
> ddate TotalOil
> -- ddate is daily date which i can take from Daily_Production
> -- Total Oil is the sum Oil from last tests on Wells A,B & C on or
> before ddate
> eg
> ddate TotalOil
> 10-Sep-2005, 9
> 9= 3 ->from test on well A on 5-Sep-05 (It should not consider the test
> oof 25-Aug-05)
> +
> 5-> test on well B on 7-Sep-05
> +
> 1-> test on well C on 10-Sep-05
> i.e 3+5+1=9
>
> Basically if my ddate is 10-Sep-2005 then I want the sum of Oil from
> the last welltest done on wells A,B,C and <= ddate.
> I hope my question is clear.
>|||Thanks Jim.
But i think my question was not clear.
For each date in the TotalProduction table i want the total oil (i.e
sum of oil from A+B+C) from Welltest Table. But calculate the total it
should sum the oil from wells A,B,C ONLY from their last wellTest data
(read record) before the Production date.
Let me Modify the table data to make it more clear
Daily_Production (Tracks daily production data)
pdate,totalOil
1-Sep-05,30
2-Sep-05,28
3-Sep-05,50
'
'
3-Oct-05,30
4-Oct-05,35
Well_test (Welltest happens at any random date)
wellid,testdate,Oil
A, 2-Sep-05, 2
A, 3-Sep-05, 3
A, 5-Sep-05, 2
B, 2-Sep-05, 4
B, 4-Sep-05, 5
C, 1-Sep-05, 3
C, 2-Sep-05, 1
C, 4-Sep-05, 2
My output should be
DDate OIL
3-Sep-05 8 (3 [well A,testdate 3-Sep]+ 4 [well B, 3-Sep]+ 1 [Well C,
2-Sep]
4-Sep-05 12 (3 [well A,testdate 3-Sep]+ 5 [well B, 4-Sep]+ 4 [Well C,
4-Sep]
5-Sep-05 11 (2 [well A,testdate 5-Sep]+ 5 [well B, 4-Sep]+ 4 [Well C,
4-Sep]
i HOPE NOW IT IS CLEAR
Jim Ross wrote:
> I believe this will work if I understand the problem correctly:
> select pdate,
> totaloild = (select sum(totaloil) from
> (select max(oil) as totaloil from welltest where testdate <= pdate group b
y
> wellid) as sub)
> from dailyprod
> JR
> "Pradeep" <agarwalp@.eeism.com> wrote in message
> news:1128518309.256857.182330@.g47g2000cwa.googlegroups.com...|||Pradeep,
I have used your sample data (from 2nd post).
select p_date, sum(oil) totoil
from Well_test WT, Daily_Production DP
where (test_date = p_date or test_date = (select max(test_date) from
Well_test WTS where WTS.wellid = WT.wellid and test_date <= p_date))
group by p_date
will return the following result:
p_date totoil
--- --
2005-09-01 00:00:00.000 3
2005-09-02 00:00:00.000 7
2005-09-03 00:00:00.000 8
2005-10-03 00:00:00.000 9
2005-10-04 00:00:00.000 9
If you just want to get for single date
SELECT max(test_date), sum(oil)
FROM Well_test WT, (select wellid WID, max(test_date) TDT
from Well_test
where test_date <= @.singledt
group by wellid) DT
WHERE WT.wellid = DT.WID
AND WT.test_date = DT.TDT
===========
Let me know this helped you.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
My guess would that you need a table of test ranges
CREATE TABLE Tests
(test_start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
test_end_date DATETIME,
CHECK (test_start_date < test_end_date),
well_id CHAR(1) NOT NULL
REFERENCES Wells(well_id)
ON UPDATE CASCADE,
test_result INTEGER DEFAULT 0 NOT NULL);
A nuill endding date means that the test has not be done yet. When it
is, insert the results based on the date range and open up a new test.|||>> A nuill endding date means that the test has not be done yet. When it
A completed test may be different from a uncompleted one. The attributes
results and completion time are applicable only for completed tests which
suggests they are distinct entity types. Why club them together in a single
table with NULLs?
Anith|||On 5 Oct 2005 06:18:29 -0700, Pradeep wrote:

>I have two tables
>Daily_Production (Tracks daily production data)
>pdate,totalOil
>1-Sep-05,30
>2-Sep-05,28
>'
>'
>3-Oct-05,30
>4-Oct-05,35
>Well_test (Welltest happens at any random date)
>wellid,testdate,Oil
>A,25-Aug-05,2
>A,5-Sep-05,3
>A,3-Oct-05,2
>B,28-Aug-05,4
>B,7-Sep-05,5
>B,2-Oct-05,5
>C,30-Aug-05,1
>C,10-Sep-05,1
>C,1-Oct-05,2
>I want data in this format
>ddate TotalOil
>-- ddate is daily date which i can take from Daily_Production
>-- Total Oil is the sum Oil from last tests on Wells A,B & C on or
>before ddate
>eg
>ddate TotalOil
>10-Sep-2005, 9
>9= 3 ->from test on well A on 5-Sep-05 (It should not consider the test
>oof 25-Aug-05)
>+
>5-> test on well B on 7-Sep-05
>+
>1-> test on well C on 10-Sep-05
>i.e 3+5+1=9
>
>Basically if my ddate is 10-Sep-2005 then I want the sum of Oil from
>the last welltest done on wells A,B,C and <= ddate.
>I hope my question is clear.
Hi Pradeep,
Here's my (untested!!!) attempt. If the results are not as expected,
then please post CREATE TABLE and INSERT statments, as indicated at
www.aspfaq.com/5006.
SELECT dp.pdate, SUM(wt.Oil)
FROM Daily_Production AS dp
CROSS JOIN Well_test AS wt
WHERE wt.testdate <= dp.pdate
AND NOT EXISTS
(SELECT *
FROM Well_test AS wt2
WHERE wt2.wellid = wt.wellid
AND wt2.testdate > wt.testdate
AND wt2.testdate <= dp.pdate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> A completed test may be different from a uncompleted one.<<
When you have no specs, you do the best you can. But ask how it could
be different? I assume that an incompleted test become a completed
test at some point (how it completes is another question).
My assumption is that when a test is not finished, there are default
values should as zero for the stat work; or that you would construct a
VIEW that drops out the tests still in progress.
Because they are the same entities. Because the NULL represent an
actual missing value. Why split them into two tables? You then have
to move a test result from uncompleted tests to completed tests in
spite of the fact that they are BOTH tests.|||On 8 Oct 2005 18:46:54 -0700, --CELKO-- wrote:

> My assumption is that when a test is not finished, there are default
> values should as zero for the stat work; or that you would construct a
> VIEW that drops out the tests still in progress.
"... should as zero for the stat work"? That seems like a German sentence
construction.

Complex Query

Hi,
I want to create a query with which I must compare the production demands with the production results. The production demands can be get by the join of two tables. The production results can be get from an aggregate of 4 tables. The connection of these two objects rely on two fields that exist in both two objects. In order to show all the production demands I must left join the two fields from the demands object to the two fields exist in the aggegate production object. In MsAccess the only way to do it is to create 2 queries one for the demands and one aggregate for the production and in a third query create two left joins from the demands query to the production query and get the right results. How can I do it with MSSQL Server with a query??

Best Regards,
ManolisYou could do this the same way in SQL Server by creating separate VIEWS (equivalent of MS Access Queries) for your two sub-sets and then joining them in a third view or stored procedure.

More efficient, though more difficult to code, would be to write the entire thing as a single TSQL Statement with subqueries.|||need a single sql statement

How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second??

e.g. How can I left join these two queries with the joinfield1,joinfield2 fields??

1st query
Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value

2nd query
Select sum(agfield1), sum(agfield2),joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2|||SELECT field1,field2,agfield1,agfield2 FROM

(Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value ) as A
INNER JOIN
(Select sum(agfield1) as agfield1, sum(agfield2) as agfield2,joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2) as B

ON A.field1=B.joinfield1
WHERE A.field2=B.joinfield2

That should do it. You may want to switch between field1 and field2 as the inner join condition will perform better with a field that is more specific.

Cheers,
-Kilka|||...
ON A.field1=B.joinfield1
AND A.field2=B.joinfield2|||left joins, the man wanted left joins

why, his question is almost exactly the same as what this other guy wanted in this other thread!! --

http://www.dbforums.com/t1118727.html|||Uncanny coincidence!

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
ArthurArthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Queries
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
> > My company's production SQL Server 2000 becomes very slow after SP4 is
> > installed.
> >
> > We have figured out that several complex queries which only took 2
seconds
> > to complete under SP3a now take more than 10 minutes to finish in SP4!
The
> > sympton is similar to the following KB:
> >
> > http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> >
> > Has anyone experienced similar problems?
> >
> > Where can I get the hotfix?
> >
> > regards
> >
> > Arthur
> >
> >
> >|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826906/#XSLTH3120121122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/8269...22120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
Arthur
Arthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Queries
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:

> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/8269...22120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>
|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries[vbcol=seagreen]
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
seconds[vbcol=seagreen]
The[vbcol=seagreen]
|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J
|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/8269...22120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

Complex queries run slow after SP4 installed

My company's production SQL Server 2000 becomes very slow after SP4 is
installed.
We have figured out that several complex queries which only took 2 seconds
to complete under SP3a now take more than 10 minutes to finish in SP4! The
sympton is similar to the following KB:
http://support.microsoft.com/kb/826...122120121120120
Has anyone experienced similar problems?
Where can I get the hotfix?
regards
ArthurArthur,
I had the same problem. Product support recommended I install build
2145, but that really didn't help in my case. I wound up re-writing my
queries to not use views -- if you can, I recommend you do the same. Querie
s
on the base tables are still (mostly) fast.
Regards,
Jonathan
"Arthur" wrote:

> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826...122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>
>|||Dear Jonathan
Thanks for your response.
The bad thing is that over 90% of our stored procedures or queries are based
on views and thus rewritting and testing all of them is just impossible.
Do you think I can fall back to SP3 safely (suppose I have the database
backup before the upgrade)?
regards
Arthur
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:C2490F12-DBF9-4854-9F78-06FE833FD131@.microsoft.com...
> Arthur,
> I had the same problem. Product support recommended I install build
> 2145, but that really didn't help in my case. I wound up re-writing my
> queries to not use views -- if you can, I recommend you do the same.
Queries[vbcol=seagreen]
> on the base tables are still (mostly) fast.
> Regards,
> Jonathan
> "Arthur" wrote:
>
seconds[vbcol=seagreen]
The[vbcol=seagreen]|||Arthur,
"Arthur" wrote:
> Do you think I can fall back to SP3 safely (suppose I have the database
> backup before the upgrade)?
If you can roll back, I would do so ASAP. The problem just gets worse
and worse as the queries get longer (I had queries that took 3 or 4 minutes
in SP3a that took several hours in SP4).
-- J|||This may or may not apply to you but we experienced some problems recently
where queries that run fast on my computer were taking forever on the
client's site.
We discovered that SQL Server was reporting an error about parallel queries.
We weren't doing any but we configured the server to only use 1 CPU instead
of "all available processors" and the problem went away.
Good luck
Richard Speiss
"Arthur" <arthurw@.newgroup.nospam> wrote in message
news:u9pGzO9lFHA.2156@.TK2MSFTNGP14.phx.gbl...
> My company's production SQL Server 2000 becomes very slow after SP4 is
> installed.
> We have figured out that several complex queries which only took 2 seconds
> to complete under SP3a now take more than 10 minutes to finish in SP4! The
> sympton is similar to the following KB:
> http://support.microsoft.com/kb/826...122120121120120
> Has anyone experienced similar problems?
> Where can I get the hotfix?
> regards
> Arthur
>

complete backup of database except values in a particular column of a table

What if we want to take complete backup of
production database except values in a particular column of a table.
Suppose if
we don't want to reveal social Security number of employees . Can we
take a backup to send the offshore team with no data in SSN column of
employee table.Unfortunately, you can't do that in a backup.
What I have done before is make a copy of the database, and then execute
some 'scrubbing' scripts that would make the appropriate data alterations.
Things like changing Staff names to 'Staff', SSN to 'XXX-XX-XXXX', etc.
With SQL 2005, there are several encryption options available. But if
someone will have physical possession of the database, and a lot of time,
I'm still not ready to completely trust encryption.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arun" <toarunmishra@.gmail.com> wrote in message
news:1156974085.063812.206780@.m79g2000cwm.googlegroups.com...
> What if we want to take complete backup of
> production database except values in a particular column of a table.
> Suppose if
> we don't want to reveal social Security number of employees . Can we
> take a backup to send the offshore team with no data in SSN column of
> employee table.
>|||Hi,
For client specific data we normally take a back of the database and restore
in a new name and do some data masking manually
and then take a backup and send to offsite. Ensure that you mask the
critical info before you send.
Thanks
Hari
SQL Server MVP
"Arun" <toarunmishra@.gmail.com> wrote in message
news:1156974085.063812.206780@.m79g2000cwm.googlegroups.com...
> What if we want to take complete backup of
> production database except values in a particular column of a table.
> Suppose if
> we don't want to reveal social Security number of employees . Can we
> take a backup to send the offshore team with no data in SSN column of
> employee table.
>

Compilation/sec is more on production where recompilation / sec is zero

Hi Team,

Our production Environment is SQL server 2000 with SP3. I noticed lot of compilation/sec (where recompilation/sec is closed to ZERO). I know every stored procedure will get compiled initially then it may get recompiled for lot many reasons & I have very good understanding of reasons for recompilation & lot of document on that. Where as I am trying to understand what causes for compilation.

I noticed we are not using DBO prefix while calling the stored procedure & also recommended using sp_executesql instead of exec. Is there anything else I need to check?

Is there any document on what causes for compilation other than creating the stored procedure. Once again I am not seeing recompilations.

Any help will be really appreciated.

Thanks

DJ Pallerla

Try these two articles, which you may or may not have already read.

http://support.microsoft.com/?id=263889

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

|||

Thanks Greg for those two articles. I have already read those I am looking for specific article on reasons for compilation.