Help with query

Help with query

Post by Denny Leste » Thu, 30 Dec 1999 04:00:00



I have 1 table with empid, currentdate, totalfare,totalhours

I want to

SELECT empid, sum(totalfare) as monthendfare,sum(totalhours)monthendhours,
if currentdate between date3 and date4 then sum(totalfare) as week1fare, if
currentdate between date3 and date4 then sum(totalhours)
FROM tripsheets
WHERE currentdate between date1 and date2
GROUP BY empid

With MS Access I could use an IF in a select statement.. How can I
accomplish this through views or  stored procedures in SQL 6.5? I am trying
to query a months worth of data and total that then show the 4 weeks worth
of data totaled all in the same row.

 
 
 

Help with query

Post by Roy Harve » Fri, 31 Dec 1999 04:00:00


Denny,

Quote:>I have 1 table with empid, currentdate, totalfare,totalhours

>I want to

>SELECT empid, sum(totalfare) as monthendfare,sum(totalhours)monthendhours,
>if currentdate between date3 and date4 then sum(totalfare) as week1fare, if
>currentdate between date3 and date4 then sum(totalhours)
>FROM tripsheets
>WHERE currentdate between date1 and date2
>GROUP BY empid

>With MS Access I could use an IF in a select statement.. How can I
>accomplish this through views or  stored procedures in SQL 6.5? I am trying
>to query a months worth of data and total that then show the 4 weeks worth
>of data totaled all in the same row.

The equivelent in SQL Server (and standard SQL) is CASE.  The
aggregate SUM needs to go arount the CASE expression, not inside.

SELECT empid,
       sum(totalfare) as monthendfare,
       sum(totalhours) as monthendhours,

                THEN totalfare
                ELSE 0
           END) as week1fare,

                THEN totalhours
                ELSE 0
           END) as week1hours
  FROM tripsheets

 GROUP BY empid

Roy

 
 
 

Help with query

Post by Denny Leste » Fri, 31 Dec 1999 04:00:00


Thank you very much. I have been pulling my hair out for 3 days trying
different work arounds..


>Denny,

>>I have 1 table with empid, currentdate, totalfare,totalhours

>>I want to

>>SELECT empid, sum(totalfare) as monthendfare,sum(totalhours)monthendhours,
>>if currentdate between date3 and date4 then sum(totalfare) as week1fare,
if
>>currentdate between date3 and date4 then sum(totalhours)
>>FROM tripsheets
>>WHERE currentdate between date1 and date2
>>GROUP BY empid

>>With MS Access I could use an IF in a select statement.. How can I
>>accomplish this through views or  stored procedures in SQL 6.5? I am
trying
>>to query a months worth of data and total that then show the 4 weeks worth
>>of data totaled all in the same row.

>The equivelent in SQL Server (and standard SQL) is CASE.  The
>aggregate SUM needs to go arount the CASE expression, not inside.

>SELECT empid,
>       sum(totalfare) as monthendfare,
>       sum(totalhours) as monthendhours,

>                THEN totalfare
>                ELSE 0
>           END) as week1fare,

>                THEN totalhours
>                ELSE 0
>           END) as week1hours
>  FROM tripsheets

> GROUP BY empid

>Roy

 
 
 

Help with query

Post by Denny Leste » Wed, 05 Jan 2000 04:00:00


I am again seeking help with a new problem.  I have 2 select statements and
I need to join the data from the 2 and return the results in a single row
grouped on van numbers Example

Query #1

Select vanid,max(startmiles)
from vantable

Query #2

Select vanid,max(endmiles)
from vantable

I want to join the 2 on vanid to return van, start miles, end miles.

Any help would be appreciated

 
 
 

Help with query

Post by Roy Harve » Wed, 05 Jan 2000 04:00:00


Denny,

>I have 2 select statements and
>I need to join the data from the 2 and return the results in a single row
>grouped on van numbers Example

>Query #1

>Select vanid,max(startmiles)
>from vantable

>Query #2

>Select vanid,max(endmiles)
>from vantable

>I want to join the 2 on vanid to return van, start miles, end miles.

There are a number of ways to do this.  Here is one using derived
tables:

SELECT A.vanid, maxstart, maxend
  FROM (SELECT vanid, max(startmiles) as maxstart
          FROM vantable

         GROUP BY vanid) as A
  JOIN (SELECT vanid, max(endmiles) as maxend
          FROM vantable

         GROUP BY vanid) as B
    ON A.vanid = B.vanid

Here is another:

SELECT vanid,

                THEN startmiles
                ELSE NULL
           END) as maxstart,

                THEN endmiles
                ELSE NULL
           END) as maxend
  FROM vantable
 GROUP BY vanid

I would use this second alternative myself.

Roy

 
 
 

Help with query

Post by Michael Spragg » Thu, 06 Jan 2000 04:00:00



> Query #1

> Select vanid,max(startmiles)
> from vantable

> Query #2

> Select vanid,max(endmiles)
> from vantable


Also UNION is possible.


UNION

--

Greetings, Michael

Visual C++ & Crystal Reports & Visual Engine
Supported by www.visual-engine.com

 
 
 

Help with query

Post by Roy Harve » Thu, 06 Jan 2000 04:00:00


Micheal,

There are a few of problems with this UNION solution.  One is that the
question asked for one row back with both numbers, and the UNION
returns two rows.  The big problem is that, as written, there is no
way to tell the two rows apart so you can tell the startmiles from the
endmiles.  And finally if the startmiles and endmiles happen to be the
same for a vanid only one row would be returned; UNION removes
duplicate rows.

If we want to use UNION, and return two rows, we need to add another
column to the query so the rows can be told apart.  Also we can use
UNION ALL; once we have made the rows unique this only serves to avoid
the overhead of checking for duplicate rows.  It would look something
like:

SELECT vanid, 'Start, max(startmiles)

UNION ALL
SELECT vanid, 'End, max(endmiles)

Roy



>> Query #1

>> Select vanid,max(startmiles)
>> from vantable

>> Query #2

>> Select vanid,max(endmiles)
>> from vantable

>Also UNION is possible.


>UNION


 
 
 

1. Need help Access queries ===> SQL queries

I am converting  the following Access queries to Oracle Sql queries. I
have no problems
converting  the tables and field names to Oracle standards. I have set
up the procedure
to accept the proper parameters input. For you oracle only people in the

where statements like this  (([Domtable].[Industry Code])=[Select Ind
Code])  the
field [Select Ind Code] on the right is a parameter passed to the
procedure.
My problem is How would I represent the following Access queries in Sql.

In access they are queries that essentially run other queries. So to run
the
whole thing in ACCESS I would run SELECTED_TYPE2 to get my results

 I am especially confused by query SELECTED_TYPE2B  which is joined with

another query SELECT_TYPE2
This access query

Thanks the queries in question are all listed below

Access query name: SELECTED_TYPE2
SELECT DISTINCTROW Selected_Type2a.* FROM Selected_Type2a
UNION
SELECT DISTINCTROW Selected_Type2b.* FROM Selected_Type2b;

Access query name: SELECTED_TYPE2A
SELECT DISTINCTROW [Level]+1 AS [Next Level], 0 AS [Split Set],
[Domtable].[Primary Key], [Domtable].[Industry Code],
[Domtable].[Primary Key] AS [Parent PK Selected]
FROM [Domtable]
WHERE ((([Domtable].[Primary Key])=(select[MinOfPrimary Key] from
Select_Type2))
AND (([Domtable].[Industry Code])=[Select Ind Code])
AND (([Domtable].Level)=[Select Level])
AND (([Domtable].[Split Set])=[Select Split Set])
AND (([Domtable].[Parent PK Selected])=[Select Parent PK]));

Access query name: SELECTED_TYPE2B
SELECT DISTINCTROW [Level]+1 AS [Next Level], 0 AS [Split Set],
[Domtable].[Primary Key], [Domtable].[Industry Code],
Select_Type2.[MinOfPrimary Key] AS [Parent PK Selected]
FROM Select_Type2, [Domtable] INNER JOIN [Initdata] ON
([Domtable].[Industry Code] = [Initdata].[Industry Code]) AND
([Domtable].[Primary Key] = [Initdata].[Primary Key])
WHERE ((([Domtable].[Industry Code])=[Select Ind Code]) AND
(([Domtable].Level)=[Select Level]) AND (([Domtable].[Split
Set])=[Select Split Set]) AND (([Domtable].[Parent PK Selected])=[Select
Parent PK]) AND (([Initdata].F2)="C"));

Access query name: SELECT_TYPE2
SELECT DISTINCTROW Min([Domtable].[Primary Key]) AS [MinOfPrimary Key]
FROM [Initdata] INNER JOIN [Domtable] ON ([Initdata].[Industry Code] =
[Domtable].[Industry Code]) AND ([Initdata].[Primary Key] =
[Domtable].[Primary Key])
WHERE ((([Domtable].[Industry Code])=[Select Ind Code])
AND (([Initdata].F2)=[Select F2])
AND (([Domtable].Level)=[Select Level])
AND (([Domtable].[Split Set])=[Select Split Set])
AND (([Domtable].[Parent PK Selected])=[Select Parent PK]));

2. Restoring a DB from MDF/LDF file (SQL2000).

3. Help!Help!Query too slow!

4. Compare the structure of two Databases

5. Help!Help!Query Too Slow!

6. GUID for a database instance

7. HELP! Need help with Query

8. Oracle Server H/W Configuration

9. Help on query syntax

10. Help on query

11. Need help with query that needs Dynamic SQL, variables, and Temporary tables

12. Help Exporting Query Results

13. Help with query...