Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.
SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));Which bit are you struggling with? How far have you got?|||The format$ is the part thats giving me fits sQL server doesnt recognize that function|||try this --SELECT DATEPART(year,[Main Table].[Date]) AS [Date By Year]
, DATEPART(month,[Main Table].[Date]) AS [Date By Month]
, [Main Table].[Action Type]
, Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type]
INNER
JOIN [Main Table]
ON [Action Type].ID = [Main Table].[Action Type]
WHERE [Main Table].[Date] >= '2007-03-01' /* first day of this month */
AND [Main Table].[Date] < '2007-03-01' /* first day of next month */
GROUP
BY DATEPART(year,[Main Table].[Date])
, DATEPART(month,[Main Table].[Date])
, [Main Table].[Action Type]note you could concatenate those two datepart expressions if you need to
also note the index-friendly method of extracting rows for a certain month (anything else will involve table scans)
and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING|||and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVINGUh? How can it not know the difference? Are you confusing access and access developers?|||no, access itself generates that crap|||Only if you don't add a second column into your query designer and make that a "Where".
But yes - Access does not encourage thoughtful & efficient query design.|||Thanks guys its just that the query he created gives you a dialog box "like the edit parameters" box and you can put the month and year in yourself and it generates a report. Now I have one for searching my subforms last name field ALTER FUNCTION dbo.SearchLName
(@.Enter_SubjLastName nvarchar(100))
RETURNS TABLE
AS
RETURN ( SELECT dbo.Report.[Incident Report No], dbo.Subjects.SubjFirstName, dbo.Subjects.SubjLastName, dbo.Report.TypeOfIncident
FROM dbo.Report INNER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
WHERE (dbo.Subjects.SubjLastName = @.Enter_SubjLastName) )
This is a function and you enter in the last name and it generate a report for you, now from what I see hes doing the same thing but with Month and Year, does that make sense??|||Here is what I have so far but I need to do this by month too they get a pretty detailed report
SELECT DATEPART(year, dbo.[Main Table].Date) AS [Date By Year], DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
dbo.[Main Table].[Action Type], SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type] INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
GROUP BY DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
HAVING (DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)|||Create an MS-Access form with two date controls, an Ok and a Cancel button on it When the user clicks Ok, validate the data in the controls, then use them to call the stored procedure.
-PatP|||The results would have to show in a report, I'm so frustrated, this is what I got so far but its spitting out an error message "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Main Table'."
SELECT Year(3/01/2001)([Main Table].[Date],'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].[Loss])
AS [Sum Of Loss], Count([Main Table].[Violation Type])
AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Year(3/01/2001)([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].[Date])
HAVING ((Year()([Main Table].Date)=[Please Enter the Year]))
GO|||You'll have to excuse me, but can we go back a few posts to that first query. Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...|||SELECT
DATEPART(year, dbo.[Main Table].Date) AS [Date By Year],
DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type]
INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
WHERE
(DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)
GROUP BY
DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
Incidently i'm not seeing a need for the inner join above...|||Quick guess :
SELECT
([Main Table].[Date],'yyyy') AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I inherited this database it was an MDB and I'm trying to convert it to an ADP I ran that select statement in the query analyzer and this is the error message i'm gettting
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.|||Hold on, i need some clarity here for piece of mind, to make sure that i'm working with the right thing :
Are you running these queries in MSSQL or MSAccess ?|||Found my error :
SELECT
DATEPART(year,[Main Table].[Date]) AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I am running them in MSSQL, I imported the tables over from Access 2003 onto SQL server. The user set up his reports a certain way and since Jet SQL and SQL Server dont exactly speak the same language I have to redo allot of the queries. Does that make sense??|||Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...the necessity for doing an inner join is not always predicated on returning something from the joined table
sometimes it is just those rows which have a match that you are interested in
example: say you are the HR department and you have an employees table
suppose you needed to store the email address of their parole officer (let's say to email them confirmation of the employee's attendance)
not every employee has a parole officer, check? so you don't keep the parole officer's email in the employees table (it would need to be NULL for most employees), you keep it in a separate table (incidentally, this is another example of proper normalization, it's 4nf i believe)
now you want to produce a list of employee names and departments for all employees who have a parole officer, but you don't actually need the parole officer's email address
what does your query look like?
that's right, an inner join, selecting only from the left table
:)|||See this the query he set up in Access, if you go into the [Main table] down where the field properties are, he has made one of the fields a combo box and the rowsource is that table [Action Type], and Violation Type is another table that also linked to it. Does that make sense??
SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type], [Action Type].[Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], [Action Type].[Action Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));
Showing posts with label guys. Show all posts
Showing posts with label guys. Show all posts
Friday, March 30, 2012
Migrating from 2000 to 2005
Hello guys, I have vry basic question. I'm migrating from 2000 to 2005. MY question is do we have to recode all packages or just migrating of old 2000 DTS is fine. If so, then I can see only small icon of DTS package in 2005, whihc is not showing the details of DTS. Kindly, explain me what do i really do, migrating old one or re-creating new in 2005. Thanks in Advance
There is some information in this forum and on the web:
http://forums.microsoft.com/MSDN/Search/Search.aspx?words=DTS+migration&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80
http://www.google.com/search?hl=en&q=migration+ssis+to+dts
Migrating DTS packages Question: Dynamic Properties
Hi guys,
After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?
' Add your code here
' Source Type = 2
' Global variable = glvTrade
' Destination = 'Connections';'Trade';'OLEDBProperties';'Data Source';'Properties';'Value'
Regards
Hi,I have same problem too.
' Source(Type = 2)
' Global variable = SPLastDate
' Destination = 'Tasks';'DTSTask_DTSExecuteSQLTask_7';'Properties';'SQLStatement'
How can i convert this code for ssis ?
Does anyone have any solution ?
Thans
Yasemin
Migrating DTS packages Question: Dynamic Properties
Hi guys,
After DTS Migration is "successful", I opened up dynamic properties step but I saw codes were all commented out inside the public class ScriptMain. Since I am not familiar with Microsoft.SqlServer.Dts.Runtime, I'm not sure what methods I should use as there aren't much examples available online. How do I do the following in SISS way?
' Add your code here
' Source Type = 2
' Global variable = glvTrade
' Destination = 'Connections';'Trade';'OLEDBProperties';'Data Source';'Properties';'Value'
Regards
Hi,I have same problem too.
' Source(Type = 2)
' Global variable = SPLastDate
' Destination = 'Tasks';'DTSTask_DTSExecuteSQLTask_7';'Properties';'SQLStatement'
How can i convert this code for ssis ?
Does anyone have any solution ?
Thans
Yasemin
Monday, March 19, 2012
Migrate DTC
Hi guys,
I have an existing sql 2k enterprise server and we're going to configure it
for server clustering. We have custom made DTC package and most of the
application use it very often.
My concern is how do we migrate this custom made DTC package to the new
environment(sql cluster)?
Appreaciate ur guys help.
Hidayat
Message posted via http://www.droptable.com
I maybe missing something; due my true lack of understanding MSDTC, but I
don't think you have anything to migrate. MSDTC as far as I know, is just a
log file, and a new install will use a new log file. This should be a walk
in the park.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:4FE03924D8C20@.droptable.com...
> Hi guys,
> I have an existing sql 2k enterprise server and we're going to configure
> it
> for server clustering. We have custom made DTC package and most of the
> application use it very often.
> My concern is how do we migrate this custom made DTC package to the new
> environment(sql cluster)?
> Appreaciate ur guys help.
> Hidayat
> --
> Message posted via http://www.droptable.com
|||tks for your prompt reply.
Another question i would like to ask is do we need different virtual ip for
MSDTC & SQL cluster? Can we use the existing virtual ip that we configure for
MSCS?
Tks
Message posted via http://www.droptable.com
|||If MSDTC has heavy usage then give it its own Group - with an Virtual IP,
Virtual Name, Physical Disk, and the MSDTC resource. If it is not a heavy
hitter, then don't worry about it.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FE4339FB56C0@.droptable.com...
> tks for your prompt reply.
> Another question i would like to ask is do we need different virtual ip
> for
> MSDTC & SQL cluster? Can we use the existing virtual ip that we configure
> for
> MSCS?
> Tks
> --
> Message posted via http://www.droptable.com
|||Can the physical disk for MSDTC be the same(sharing) for the SQL cluster
physical disk?
In SQL cluster environment, how is the procedure of restoring the database?
Is it the same as normal SQL restore using it's own utility. As i know the
data will centrally reside in the SAN(EMC) storage.
Tks
Message posted via http://www.droptable.com
|||You can share the space. But if it is heavily used that maybe a bad idea.
Everything about a database is the same, restore, attach, etc. The Books
Online are wonderful and full of great information.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FE89EBC9A9E0@.droptable.com...
> Can the physical disk for MSDTC be the same(sharing) for the SQL cluster
> physical disk?
> In SQL cluster environment, how is the procedure of restoring the
> database?
> Is it the same as normal SQL restore using it's own utility. As i know the
> data will centrally reside in the SAN(EMC) storage.
> Tks
> --
> Message posted via http://www.droptable.com
|||Clustered SQL servers look and act just like normal SQL servers for amost
every purpose, including backup and restore. There are some extra steps for
things like restoring master, adding disks, and adding/dropping cluster
nodes but these are well documented. From the client's point of view, they
look and act exactly like any other SQL server.
Geoff N. Hiten
Microsoft SQL Server MVP
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FE89EBC9A9E0@.droptable.com...
> Can the physical disk for MSDTC be the same(sharing) for the SQL cluster
> physical disk?
> In SQL cluster environment, how is the procedure of restoring the
> database?
> Is it the same as normal SQL restore using it's own utility. As i know the
> data will centrally reside in the SAN(EMC) storage.
> Tks
> --
> Message posted via http://www.droptable.com
|||Tks for the explaination. Just to confirm with u guys. Let's say that i have
2 node(A & B). I install and configure SQL cluster on Node A. Must i install
the SQL cluster on Node B?
Message posted via http://www.droptable.com
|||Tks a million guys. Let's say that i have 5GB each on 2 servers. Should i
enabled /3GB or /PAE or AWE? What is u guys best recommendation. I'm going to
configure clustering this Saturday and will be in production.
This clustering is new to me so tks for your patient in answering my question.
U guys have been very helpful.
Tks.
Message posted via http://www.droptable.com
|||Great question. You need to 1) talk to you hardware vendor and find out if
/PAE or /AWE are even supported. 2) test the /3GB with whatever the vendor
supports, you may not see any difference or worse, things could get slower,
3) I am guessing you will only need /PAE to see the 5 GB of memory, but this
is a guess - testing will tell all.
Lastly, you are on a very aggressive schedule. It sounds like you need more
testing time to me BEFORE you go live. This being your first cluster, can
you restore it or a node in the case of a failure? Do you fully understand
how to handle service packs, etc. I am not trying to beat you up, you have a
learning curve ahead. After you in production is really not the way to
manage and maintain your Highly Available SQL Failover Cluster.
Just my two cents...
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FF0A99441520@.droptable.com...
> Tks a million guys. Let's say that i have 5GB each on 2 servers. Should i
> enabled /3GB or /PAE or AWE? What is u guys best recommendation. I'm going
> to
> configure clustering this Saturday and will be in production.
> This clustering is new to me so tks for your patient in answering my
> question.
>
> U guys have been very helpful.
> Tks.
> --
> Message posted via http://www.droptable.com
I have an existing sql 2k enterprise server and we're going to configure it
for server clustering. We have custom made DTC package and most of the
application use it very often.
My concern is how do we migrate this custom made DTC package to the new
environment(sql cluster)?
Appreaciate ur guys help.
Hidayat
Message posted via http://www.droptable.com
I maybe missing something; due my true lack of understanding MSDTC, but I
don't think you have anything to migrate. MSDTC as far as I know, is just a
log file, and a new install will use a new log file. This should be a walk
in the park.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:4FE03924D8C20@.droptable.com...
> Hi guys,
> I have an existing sql 2k enterprise server and we're going to configure
> it
> for server clustering. We have custom made DTC package and most of the
> application use it very often.
> My concern is how do we migrate this custom made DTC package to the new
> environment(sql cluster)?
> Appreaciate ur guys help.
> Hidayat
> --
> Message posted via http://www.droptable.com
|||tks for your prompt reply.
Another question i would like to ask is do we need different virtual ip for
MSDTC & SQL cluster? Can we use the existing virtual ip that we configure for
MSCS?
Tks
Message posted via http://www.droptable.com
|||If MSDTC has heavy usage then give it its own Group - with an Virtual IP,
Virtual Name, Physical Disk, and the MSDTC resource. If it is not a heavy
hitter, then don't worry about it.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FE4339FB56C0@.droptable.com...
> tks for your prompt reply.
> Another question i would like to ask is do we need different virtual ip
> for
> MSDTC & SQL cluster? Can we use the existing virtual ip that we configure
> for
> MSCS?
> Tks
> --
> Message posted via http://www.droptable.com
|||Can the physical disk for MSDTC be the same(sharing) for the SQL cluster
physical disk?
In SQL cluster environment, how is the procedure of restoring the database?
Is it the same as normal SQL restore using it's own utility. As i know the
data will centrally reside in the SAN(EMC) storage.
Tks
Message posted via http://www.droptable.com
|||You can share the space. But if it is heavily used that maybe a bad idea.
Everything about a database is the same, restore, attach, etc. The Books
Online are wonderful and full of great information.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FE89EBC9A9E0@.droptable.com...
> Can the physical disk for MSDTC be the same(sharing) for the SQL cluster
> physical disk?
> In SQL cluster environment, how is the procedure of restoring the
> database?
> Is it the same as normal SQL restore using it's own utility. As i know the
> data will centrally reside in the SAN(EMC) storage.
> Tks
> --
> Message posted via http://www.droptable.com
|||Clustered SQL servers look and act just like normal SQL servers for amost
every purpose, including backup and restore. There are some extra steps for
things like restoring master, adding disks, and adding/dropping cluster
nodes but these are well documented. From the client's point of view, they
look and act exactly like any other SQL server.
Geoff N. Hiten
Microsoft SQL Server MVP
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FE89EBC9A9E0@.droptable.com...
> Can the physical disk for MSDTC be the same(sharing) for the SQL cluster
> physical disk?
> In SQL cluster environment, how is the procedure of restoring the
> database?
> Is it the same as normal SQL restore using it's own utility. As i know the
> data will centrally reside in the SAN(EMC) storage.
> Tks
> --
> Message posted via http://www.droptable.com
|||Tks for the explaination. Just to confirm with u guys. Let's say that i have
2 node(A & B). I install and configure SQL cluster on Node A. Must i install
the SQL cluster on Node B?
Message posted via http://www.droptable.com
|||Tks a million guys. Let's say that i have 5GB each on 2 servers. Should i
enabled /3GB or /PAE or AWE? What is u guys best recommendation. I'm going to
configure clustering this Saturday and will be in production.
This clustering is new to me so tks for your patient in answering my question.
U guys have been very helpful.
Tks.
Message posted via http://www.droptable.com
|||Great question. You need to 1) talk to you hardware vendor and find out if
/PAE or /AWE are even supported. 2) test the /3GB with whatever the vendor
supports, you may not see any difference or worse, things could get slower,
3) I am guessing you will only need /PAE to see the 5 GB of memory, but this
is a guess - testing will tell all.
Lastly, you are on a very aggressive schedule. It sounds like you need more
testing time to me BEFORE you go live. This being your first cluster, can
you restore it or a node in the case of a failure? Do you fully understand
how to handle service packs, etc. I am not trying to beat you up, you have a
learning curve ahead. After you in production is really not the way to
manage and maintain your Highly Available SQL Failover Cluster.
Just my two cents...
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"Hidayat via droptable.com" <forum@.droptable.com> wrote in message
news:4FF0A99441520@.droptable.com...
> Tks a million guys. Let's say that i have 5GB each on 2 servers. Should i
> enabled /3GB or /PAE or AWE? What is u guys best recommendation. I'm going
> to
> configure clustering this Saturday and will be in production.
> This clustering is new to me so tks for your patient in answering my
> question.
>
> U guys have been very helpful.
> Tks.
> --
> Message posted via http://www.droptable.com
Monday, March 12, 2012
midian calculation
Hi guys,
I have a dilemma, I used the following query to find the Q-by-q median. It
uses a temporary table. I think it's because it has no index and foreign
key, it takes over 5 minutes to run. When we look at subquestions, there are
over 10,000 records and cross join takes forever. I don't know if I can
create indexes on temp table and wonder if the process will take long
anyway.
--Put the course marks in temp table
--DROP TABLE #QBYQ
CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
INSERT INTO #QBYQ
SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
from QUESTION_MARK QM, Question q, Enrollment e
where
e.ENROLLMENT_ID = qm.ENROLLMENT_ID
and q.QUESTION_ID = qm.QUESTION_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
--and q.PARENT_QUESTION_ID is null
AND QM.QUESTION_POINT_MARK IS NOT NULL
and q.QUESTION_TYPE_CODE = 'EN'
ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
-- It is currently doing statistical median, the smaller of the two if even
rows
-- Get this working first before calculating the financial median (avg of
middle 2 rows)
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION Q,
#QBYQ QM1 CROSS JOIN #QBYQ QM2
WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
AND Q.QUESTION_ID = QM1.QUESTION_ID
GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL
I then try to put the select in-line but didn't get the correct results. Can
you tell what's wrong? Looks like HAVING is not doing what it is supposed to
do.
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
WHERE
QM1.QUESTION_ID = QM2.QUESTION_ID
AND QM1.QUESTION_ID = Q.QUESTION_ID
AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
AND QM2.QUESTION_ID = Q.QUESTION_ID
AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
and q.PARENT_QUESTION_ID is null
and q.QUESTION_TYPE_CODE = 'EN'
GROUP BY
Q.LABEL,
QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
ThanksHi Ray5531,
Medians have been discussed many times in this newsgroup. You may want
to google past threads.
Maybe the following two threads help you out:
http://tinyurl.com/7ba4w
http://tinyurl.com/7fsjr
Hope this helps,
Gert-Jan
Ray5531 wrote:
> Hi guys,
> I have a dilemma, I used the following query to find the Q-by-q median. It
> uses a temporary table. I think it's because it has no index and foreign
> key, it takes over 5 minutes to run. When we look at subquestions, there a
re
> over 10,000 records and cross join takes forever. I don't know if I can
> create indexes on temp table and wonder if the process will take long
> anyway.
> --Put the course marks in temp table
> --DROP TABLE #QBYQ
> CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
> INSERT INTO #QBYQ
> SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> from QUESTION_MARK QM, Question q, Enrollment e
> where
> e.ENROLLMENT_ID = qm.ENROLLMENT_ID
> and q.QUESTION_ID = qm.QUESTION_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> --and q.PARENT_QUESTION_ID is null
> AND QM.QUESTION_POINT_MARK IS NOT NULL
> and q.QUESTION_TYPE_CODE = 'EN'
> ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> -- It is currently doing statistical median, the smaller of the two if eve
n
> rows
> -- Get this working first before calculating the financial median (avg of
> middle 2 rows)
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION Q,
> #QBYQ QM1 CROSS JOIN #QBYQ QM2
> WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
> AND Q.QUESTION_ID = QM1.QUESTION_ID
> GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL
> I then try to put the select in-line but didn't get the correct results. C
an
> you tell what's wrong? Looks like HAVING is not doing what it is supposed
to
> do.
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
> WHERE
> QM1.QUESTION_ID = QM2.QUESTION_ID
> AND QM1.QUESTION_ID = Q.QUESTION_ID
> AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
> AND QM2.QUESTION_ID = Q.QUESTION_ID
> AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> and q.PARENT_QUESTION_ID is null
> and q.QUESTION_TYPE_CODE = 'EN'
> GROUP BY
> Q.LABEL,
> QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
> Thanks|||On Mon, 25 Apr 2005 10:26:30 -0700, Ray5531 wrote:
(snip)
>I don't know if I can
>create indexes on temp table
Hi Ray,
You can. The best way is to use CREATE TABLE to create the table (with
PRIMARY KEY and UNIQUE constraints; accompanying indexes will be generated
automatically), then use CREATE INDEX to add extra (non-unique) indexes,
then use INSERT INTO ... SELECT to load the temp table with data. (This
order of creation minimizes the number of recompiles). OTOH, there might
be cases where definin g an index after loading the table with data might
be quicker - test all variation to find the quickest way in your scenario.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have a dilemma, I used the following query to find the Q-by-q median. It
uses a temporary table. I think it's because it has no index and foreign
key, it takes over 5 minutes to run. When we look at subquestions, there are
over 10,000 records and cross join takes forever. I don't know if I can
create indexes on temp table and wonder if the process will take long
anyway.
--Put the course marks in temp table
--DROP TABLE #QBYQ
CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
INSERT INTO #QBYQ
SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
from QUESTION_MARK QM, Question q, Enrollment e
where
e.ENROLLMENT_ID = qm.ENROLLMENT_ID
and q.QUESTION_ID = qm.QUESTION_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
--and q.PARENT_QUESTION_ID is null
AND QM.QUESTION_POINT_MARK IS NOT NULL
and q.QUESTION_TYPE_CODE = 'EN'
ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
-- It is currently doing statistical median, the smaller of the two if even
rows
-- Get this working first before calculating the financial median (avg of
middle 2 rows)
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION Q,
#QBYQ QM1 CROSS JOIN #QBYQ QM2
WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
AND Q.QUESTION_ID = QM1.QUESTION_ID
GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL
I then try to put the select in-line but didn't get the correct results. Can
you tell what's wrong? Looks like HAVING is not doing what it is supposed to
do.
SELECT Q.LABEL AS QUESTION,
QM1.QUESTION_POINT_MARK AS MEDIAN
from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
WHERE
QM1.QUESTION_ID = QM2.QUESTION_ID
AND QM1.QUESTION_ID = Q.QUESTION_ID
AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
AND QM2.QUESTION_ID = Q.QUESTION_ID
AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
and e.COURSE_YEAR = 2005
and e.COURSE_SESSION = 1
and e.COURSE_CODE = 'FA4'
and q.PARENT_QUESTION_ID is null
and q.QUESTION_TYPE_CODE = 'EN'
GROUP BY
Q.LABEL,
QM1.QUESTION_POINT_MARK
HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
ThanksHi Ray5531,
Medians have been discussed many times in this newsgroup. You may want
to google past threads.
Maybe the following two threads help you out:
http://tinyurl.com/7ba4w
http://tinyurl.com/7fsjr
Hope this helps,
Gert-Jan
Ray5531 wrote:
> Hi guys,
> I have a dilemma, I used the following query to find the Q-by-q median. It
> uses a temporary table. I think it's because it has no index and foreign
> key, it takes over 5 minutes to run. When we look at subquestions, there a
re
> over 10,000 records and cross join takes forever. I don't know if I can
> create indexes on temp table and wonder if the process will take long
> anyway.
> --Put the course marks in temp table
> --DROP TABLE #QBYQ
> CREATE TABLE #QBYQ (QUESTION_ID INT, QUESTION_POINT_MARK DECIMAL)
> INSERT INTO #QBYQ
> SELECT QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> from QUESTION_MARK QM, Question q, Enrollment e
> where
> e.ENROLLMENT_ID = qm.ENROLLMENT_ID
> and q.QUESTION_ID = qm.QUESTION_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> --and q.PARENT_QUESTION_ID is null
> AND QM.QUESTION_POINT_MARK IS NOT NULL
> and q.QUESTION_TYPE_CODE = 'EN'
> ORDER BY QM.QUESTION_ID, QM.QUESTION_POINT_MARK
> -- It is currently doing statistical median, the smaller of the two if eve
n
> rows
> -- Get this working first before calculating the financial median (avg of
> middle 2 rows)
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION Q,
> #QBYQ QM1 CROSS JOIN #QBYQ QM2
> WHERE QM1.QUESTION_ID = QM2.QUESTION_ID
> AND Q.QUESTION_ID = QM1.QUESTION_ID
> GROUP BY Q.LABEL, QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL
> I then try to put the select in-line but didn't get the correct results. C
an
> you tell what's wrong? Looks like HAVING is not doing what it is supposed
to
> do.
> SELECT Q.LABEL AS QUESTION,
> QM1.QUESTION_POINT_MARK AS MEDIAN
> from QUESTION_MARK QM1, QUESTION_MARK QM2, ENROLLMENT E, QUESTION Q
> WHERE
> QM1.QUESTION_ID = QM2.QUESTION_ID
> AND QM1.QUESTION_ID = Q.QUESTION_ID
> AND QM1.ENROLLMENT_ID = E.ENROLLMENT_ID
> AND QM2.QUESTION_ID = Q.QUESTION_ID
> AND QM2.ENROLLMENT_ID = E.ENROLLMENT_ID
> and e.COURSE_YEAR = 2005
> and e.COURSE_SESSION = 1
> and e.COURSE_CODE = 'FA4'
> and q.PARENT_QUESTION_ID is null
> and q.QUESTION_TYPE_CODE = 'EN'
> GROUP BY
> Q.LABEL,
> QM1.QUESTION_POINT_MARK
> HAVING sum(CASE WHEN QM2.QUESTION_POINT_MARK <= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> AND sum(CASE WHEN QM2.QUESTION_POINT_MARK >= QM1.QUESTION_POINT_MARK
> THEN 1 ELSE NULL END)>=(COUNT(*)+1)/2
> ORDER BY Q.LABEL, QM1.QUESTION_POINT_MARK
> Thanks|||On Mon, 25 Apr 2005 10:26:30 -0700, Ray5531 wrote:
(snip)
>I don't know if I can
>create indexes on temp table
Hi Ray,
You can. The best way is to use CREATE TABLE to create the table (with
PRIMARY KEY and UNIQUE constraints; accompanying indexes will be generated
automatically), then use CREATE INDEX to add extra (non-unique) indexes,
then use INSERT INTO ... SELECT to load the temp table with data. (This
order of creation minimizes the number of recompiles). OTOH, there might
be cases where definin g an index after loading the table with data might
be quicker - test all variation to find the quickest way in your scenario.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)