Help with Cross-tab queries

How would you write the SQL statement to do a cross-tab queries.

My Table
DCNID-unique ID
AuditID-FK to the main table
DCN-int
Error Type (combo box with values tooth, date, provider, etc)
Line ID (combo box with values 1-40)
Adjustment Code-text

I would like my report to look like this.
DCN
LineID Tooth Date Provider
1 1 1
2 1
3
4
5
..
40

Thank you so much!

CristyYou could have done some research, this question has been asked and aswered thousands of times, here are some solutions from asktom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:rolleyes:|||How would you write the SQL statement to do a cross-tab queries.that depends -- what database system are you using?

Help with combining two queries

I have a stored procedure that needs to retrieve the top 1000 sent items and
the top 1000 received items - each ordered by date. So, effectively, the
most recent 1000 sent items and the most recent 1000 received items. Then, I
need to combine them into one result set and again take the top 1000 items
when ordered by date.
Originally, the stored procedure created a temp table and inserted the
results of each query consecutively, then did a SELECT TOP to get the final
results. Because of high traffic, this is killing the DB server. I suspect
that the queries could be combined into one query. I also tried a UNION of
the two selects, but that doesn't work because I need to do have accurate
results on the subquery first (top 1000 ordered by date). Can anyone help me
determine a more effecient solution, preferrably to combine the two queries
into one? I have slimmed down the two queries significantly to show only the
differences. They are below.
DECLARE @.userName AS VARCHAR(25)
SELECT @.userName = 'MyUserName'
-- Gets the sent items
--
SELECT TOP 1000
@.userName as SenderName,
'Sent' AS SentReceived,
u.[user_name] as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[contact_user_id] =
u.[user_id]
WHERE
ch.[user_id] = @.UserID
ORDER BY
ch.createstamp DESC
-- Gets the received items
--
SELECT TOP 1000
u.[user_name] as SenderName,
'Received' AS SentReceived,
@.userName as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[user_id] =
u.[user_id]
WHERE
ch.[contact_user_id] = @.UserID
ORDER BY
ch.createstamp DESCHello, karch
You probably want a query like this:
SELECT TOP 1000 *
FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
UNION ALL
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) x
ORDER BY TheDate
However, I think the above query will give the same results as this
query (as long as TheDate is unique among all rows):
SELECT TOP 1000 *
FROM (
SELECT <your columns>
FROM <sent items>
UNION ALL
SELECT <your columns>
FROM <sent items>
) x
ORDER BY TheDate
Razvan|||Yes, but I dont think you can have ORDER BY in the subqueries if you UNION -
I tried this approach and received an error. But, yes, logically that is
what I want to accomplish.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1146597584.838889.18770@.v46g2000cwv.googlegroups.com...
> Hello, karch
> You probably want a query like this:
> SELECT TOP 1000 *
> FROM (
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> UNION ALL
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> ) x
> ORDER BY TheDate
> However, I think the above query will give the same results as this
> query (as long as TheDate is unique among all rows):
> SELECT TOP 1000 *
> FROM (
> SELECT <your columns>
> FROM <sent items>
> UNION ALL
> SELECT <your columns>
> FROM <sent items>
> ) x
> ORDER BY TheDate
> Razvan
>|||In this case, try another level of subqueries:
SELECT TOP 1000 *
FROM (
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) a
UNION ALL
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) b
) x
ORDER BY TheDate
Razvan

help with case

Good afternoon...
I look for a forum which one I can as for queries.
If this isn't the one, sorry. Please say me where I can find it.
My problem:
I have a table where ParentID is foreign key to ID in the same table.
the others columns are type, value and author.
Type can get 3 values: FAMILY, GENUS and SPECIES.
To take all the FAMILY-GENUS-SPECIES from the table I use:
select distinct
case 'FAMILY'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as FAMILY,
case 'GENRE'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as GENRE,
case 'SPECIES'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as SPECIES
from TaxonName TN
left outer join TaxonName TN1 on TN1.TaxonNameID = TN.ParentTaxonNameID
left outer join TaxonName TN2 on TN2.TaxonNameID = TN.ParentTaxonNameID
and it worked fine. But I want to take the major level author not NULL
too. For example:
if SPECIES is NOT NULL return author from register where type is SPECIES;
else if GENRE is NOT NULL return author from register where type is GENRE;
else if FAMILY is NOT NULL return author from register where type is FAMILY;
Anyone can help me?
thanks for the help
Giscar Paiva
www.cria.org.brit's not entirely clear what you want, but perhaps this will help:
SELECT isnull(tn.Author,isnull(tn1.Author, tn2.Author)) as MajorAuthor,
CASE etc...
Cheers
Will|||Take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Or wait for Joe Celko to guide you to one of his books: "Trees and
Hierarchies".
ML
http://milambda.blogspot.com/|||Will wrote:
> it's not entirely clear what you want, but perhaps this will help:
I'll try to iluminate you more...
in the table, I can have the author in all records. For Example...
if my table is:
ID ParentID Type Value Author
---
1 <null> FAMILY Leguminosae Britton
2 1 GENUS Leucaena Rose
3 2 SPECIES diversifolia Zarate
---
I want the query returns:
FAMILY GENUS SPECIES AUTHOR
----
Leguminosae <null> <null> Britton
Leguminosae Leucaena <null> Rose
Leguminosae Leucaena diversifolia Zarate

> SELECT isnull(tn.Author,isnull(tn1.Author, tn2.Author)) as MajorAuthor,
> CASE etc...
The problem is that I don't know if the tn is Family, Genus OR Species,
'cause my CASE.
But thanks anyway...
Giscar Paiva
www.cria.org.br|||add this as another column
coalesce(tn.author, tn1.author,tn2.author)
Btw, just observed that your join condition is wrong. TN1 and TN2 are joined
the same way to TN. I am surprised you are saying its working fine.|||Omnibuzz wrote:
> Btw, just observed that your join condition is wrong. TN1 and TN2 are join
ed
> the same way to TN. I am surprised you are saying its working fine.
U're right... I just saw that after I post...
Thank u...sql

Help with an SQL query

I'm just starting to get involved with SQL queries as a result of some
help desk software we're using and my CIO is asking for a report that I
can't sem to get.
Here's some sample data:
<pre>
refnumber assignee user problem open date
1 bob user1 text1 12/29/2005
2 sally user2 text2 12/29/2005
3 bob user3 text3 12/29/2005
4 sally user1 text4 12/29/2005
5 bob user2 text5 1/5/2006
6 bob user3 etc 1/5/2006
7 sally user5 1/5/2006
8 bob user4 1/15/2006
9 sally user5 1/15/2006
0 bob user4 1/15/2006
</pre>
And here's the report I need (average number of tickets opened per
analyst over the last 4 ws):
<pre>
wbegin wend avg tickets
12/25/2005 12/31/2005 2
1/1/2006 1/7/2006 1.5
1/8/2006 1/14/2006 0
1/15/2006 1/21/2006 1.5
</pre>
Normally I just use MS Access to create the SQL for me and then I can
tweak it as I need to but this one is a little too complex for that.
The date calculations are just throwing me completely. Anyone done
something similar?wkwork (wkwork@.movieland.com) writes:
><pre>
> refnumber assignee user problem open date
> 1 bob user1 text1 12/29/2005
> 2 sally user2 text2 12/29/2005
> 3 bob user3 text3 12/29/2005
> 4 sally user1 text4 12/29/2005
> 5 bob user2 text5 1/5/2006
> 6 bob user3 etc 1/5/2006
> 7 sally user5 1/5/2006
> 8 bob user4 1/15/2006
> 9 sally user5 1/15/2006
> 0 bob user4 1/15/2006
></pre>
> And here's the report I need (average number of tickets opened per
> analyst over the last 4 ws):
><pre>
> wbegin wend avg tickets
> 12/25/2005 12/31/2005 2
> 1/1/2006 1/7/2006 1.5
> 1/8/2006 1/14/2006 0
> 1/15/2006 1/21/2006 1.5
></pre>
I will first have to assume that you have a table with analysts
to which assignee refers. That is, I assume that if bob does not
open any ticket a certain w, and Sally submits three, the
average should be 1.5.
Next, I am introducing a table dates. This table simply has all
dates from 1990-01-01 to 2150-01-01 or whatever. You would have to
fill this table yourself.
SELECT wbegin = d.thedate, wend = dateadd(DAY, 6, d.thedate),
avg(cnt)
FROM (SELECT d.thedate, a.assignee, cnt = COUNT(t.Sunday) * 1.0
FROM thedates d
CROSS JOIN analysts a
LEFT JOIN (SELECT dateadd(day, -datepart(dw, opendate) + 1,
opendate) AS Sunday,
assignee
FROM tickets) AS t ON d.thedate = t.Sunday
AND a.analyst = t.assignee
WHERE datename(wday, d.thedate) = 'Sunday') AS x
This query is not tested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Wow - thanks Erland. There's a lot in there that I've never seen before
so I'll have to take some time to digest it and try to fit it to my
tables.
Could I ask you to explain the innermost select:
SELECT dateadd(day, -datepart(dw, opendate) + 1,
opendate) AS Sunday,
assignee
Looks like datepart pulls the (day? day of the w?), adds 1 to it,
then dateadd subtracts that number of days from "opendate". Does that
give us the "Sunday" before the current w?
FROM tickets) AS t ON d.thedate = t.Sunday
AND a.analyst = t.assignee
Looks like it's pulling from "tickets" (my original table), calling the
whole array "t" and I'm not sure what the ON means.
Thanks and forgive my inexperience. :-)|||I just realized something else about your reply too:
The "user" and the "assignee" actually both refer to the same table
(the contact table: ctct). If I average over all names in that table,
it would be wrong. I just need to average over contacts of the
"analyst" or "administrator" type. In fact it might be easier to just
average those who have opened tickets and leave off all who have not
(in which case we can disregard the contact table and just count the
unique reference numbers in the ticket table).|||On 18 Jan 2006 14:30:50 -0800, wkwork wrote:

>I'm just starting to get involved with SQL queries as a result of some
>help desk software we're using and my CIO is asking for a report that I
>can't sem to get.
>Here's some sample data:
><pre>
>refnumber assignee user problem open date
> 1 bob user1 text1 12/29/2005
> 2 sally user2 text2 12/29/2005
> 3 bob user3 text3 12/29/2005
> 4 sally user1 text4 12/29/2005
> 5 bob user2 text5 1/5/2006
> 6 bob user3 etc 1/5/2006
> 7 sally user5 1/5/2006
> 8 bob user4 1/15/2006
> 9 sally user5 1/15/2006
> 0 bob user4 1/15/2006
></pre>
>And here's the report I need (average number of tickets opened per
>analyst over the last 4 ws):
><pre>
>wbegin wend avg tickets
>12/25/2005 12/31/2005 2
>1/1/2006 1/7/2006 1.5
>1/8/2006 1/14/2006 0
>1/15/2006 1/21/2006 1.5
></pre>
>Normally I just use MS Access to create the SQL for me and then I can
>tweak it as I need to but this one is a little too complex for that.
>The date calculations are just throwing me completely. Anyone done
>something similar?
Hi wkwork,
Here's a different solution. Note that it requires a numbers table. See
http://www.aspfaq.com/show.asp?id=2516 for how to create one.
DECLARE @.startdate datetime, @.enddate datetime
SET @.startdate = '20051225'
SET @.enddate = '20060121'
SELECT DATEADD(day, 7 * (N.Number - 1), @.startdate) AS wbbegin,
DATEADD(day, (7 * N.Number) - 1, @.startdate) AS wend,
1.0 * COUNT(t.OpenDate) / (SELECT COUNT(DISTINCT assignee)
FROM TheTable)
FROM Numbers AS N
LEFT JOIN TheTable AS t
ON t.OpenDate >= DATEADD(day, 7 * (N.Number - 1), @.startdate)
AND t.OpenDate <= DATEADD(day, (7 * N.Number) - 1, @.startdate)
WHERE N.Number >= 1
AND N.Number <= DATEDIFF (day, @.startdate, @.enddate) / 7 + 1
GROUP BY N.Number
Hugo Kornelis, SQL Server MVP|||wkwork (wkwork@.movieland.com) writes:
> Wow - thanks Erland. There's a lot in there that I've never seen before
> so I'll have to take some time to digest it and try to fit it to my
> tables.
> Could I ask you to explain the innermost select:
> SELECT dateadd(day, -datepart(dw, opendate) + 1,
> opendate) AS Sunday,
> assignee
> Looks like datepart pulls the (day? day of the w?), adds 1 to it,
> then dateadd subtracts that number of days from "opendate". Does that
> give us the "Sunday" before the current w?
The intention is that the expression moves back opendate to the first
day of the w. In the example, I have assumed that this is Sunday,
as I noted that your formatted your dates according to the rules unique
to the US, and I am under the impression that in the US the convention
is that ws start on Sundays.
If you were to issue the command SET DATEFIRT 1, to state that your
ws begin on Mondays, the query would then give you ws Monday to
Sunday.
And, yes, dw is day of w. This is covered in Books Online, for the
datetime functions.

> FROM tickets) AS t ON d.thedate = t.Sunday
> AND a.analyst = t.assignee
> Looks like it's pulling from "tickets" (my original table), calling the
> whole array "t" and I'm not sure what the ON means.
The "t" is not an array. This is a derived table. A derived table is
conceptually a temp table within the query, but it is never materialised,
and SQL Server may recast the actual computation order as long as the
result is the same. Here the derived table is used to give us a table
where all opendates are on the first day of the w.
ON is part of the join operator:
SELECT ..
FROM tblA a
JOIN tblB b ON a.col = b.col
To some extent this is the same as a WHERE clause, and the above query
can also be written as
SELECT ...
FROM tblA a, tblB b
WHERE a.col = b.col
However, the query I posted had an outer join, for outer join it matters
whether a condition is in the ON clause or in the WHERE clause.
SELECT ..
FROM tblA a
LEFT JOIN tblB b ON a.col = b.col
AND b.col2 = 1
Here it is the intention is to get all rows in A, and attach the data from
tblB where there is a match with tblA and col2 is 1, else the rows from
tblB should have NULL in all columns.
I used LEFT JOIN in the query, to cover the case that some ws may
not have any tickets opened at all.

> The "user" and the "assignee" actually both refer to the same table
> (the contact table: ctct). If I average over all names in that table,
> it would be wrong. I just need to average over contacts of the
> "analyst" or "administrator" type. In fact it might be easier to just
> average those who have opened tickets and leave off all who have not
> (in which case we can disregard the contact table and just count the
> unique reference numbers in the ticket table).
That's what you get for inclucing partial information. It sounds like
you would need to add a filter:
AND usertype IN ('analyst', 'administrator')
Finally, a standard recommendation for this type of questions is that
you include:
o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.
If you do this, it is very easy to copy and past into a query window, and
you get and a reply with a query that has been tested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with a Query

I have been designing a website for our fishing club and I need help
with one of my queries because I'm not to good at it yet.
The table has FishID, Angler, RecordDate, Species, Length, Photo for
columns.
The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
What I want is display the data like this
Angler Muskie_total Northern_total Walleye_total Catfish_total
Crappie_Total Longest_fish Total_points
All species except Muskie are worth 1 point, Muskie are worth 5,
longest fish is worth 5
So it didn't take long for this to get over my head.
Anybody want to give me a hand at this, leagues start in 2 weeks!!!
Thanks,
MartyOn Apr 30, 5:52 am, Marty <mcoon...@.gmail.com> wrote:
> I have been designing a website for our fishing club and I need help
> with one of my queries because I'm not to good at it yet.
> The table has FishID, Angler, RecordDate, Species, Length, Photo for
> columns.
> The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
> What I want is display the data like this
> Angler Muskie_total Northern_total Walleye_total Catfish_total
> Crappie_Total Longest_fish Total_points
> All species except Muskie are worth 1 point, Muskie are worth 5,
> longest fish is worth 5
> So it didn't take long for this to get over my head.
> Anybody want to give me a hand at this, leagues start in 2 weeks!!!
> Thanks,
> Marty
Try something on these lines
SELECT Angler ,
SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
Northern_total ,
SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
Longest_fish_Total
FROM Fish
Group by Angler|||On Apr 30, 12:55 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 5:52 am,Marty<mcoon...@.gmail.com> wrote:
>
>
> > I have been designing a website for our fishing club and I need help
> > with one of my queries because I'm not to good at it yet.
> > The table has FishID, Angler, RecordDate, Species, Length, Photo for
> > columns.
> > The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
> > What I want is display the data like this
> > Angler Muskie_total Northern_total Walleye_total Catfish_total
> > Crappie_Total Longest_fish Total_points
> > All species except Muskie are worth 1 point, Muskie are worth 5,
> > longest fish is worth 5
> > So it didn't take long for this to get over my head.
> > Anybody want to give me a hand at this, leagues start in 2 weeks!!!
> > Thanks,
> >Marty
> Try something on these lines
> SELECT Angler ,
> SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
> SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
> Northern_total ,
> SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
> SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
> SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
> SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
> Longest_fish_Total
> FROM Fish
> Group by Angler- Hide quoted text -
> - Show quoted text -
Thanks!!!sql

Help with a Query

I have been designing a website for our fishing club and I need help
with one of my queries because I'm not to good at it yet.
The table has FishID, Angler, RecordDate, Species, Length, Photo for
columns.
The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
What I want is display the data like this
Angler Muskie_total Northern_total Walleye_total Catfish_total
Crappie_Total Longest_fish Total_points
All species except Muskie are worth 1 point, Muskie are worth 5,
longest fish is worth 5
So it didn't take long for this to get over my head.
Anybody want to give me a hand at this, leagues start in 2 weeks!!!
Thanks,
Marty
On Apr 30, 5:52 am, Marty <mcoon...@.gmail.com> wrote:
> I have been designing a website for our fishing club and I need help
> with one of my queries because I'm not to good at it yet.
> The table has FishID, Angler, RecordDate, Species, Length, Photo for
> columns.
> The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
> What I want is display the data like this
> Angler Muskie_total Northern_total Walleye_total Catfish_total
> Crappie_Total Longest_fish Total_points
> All species except Muskie are worth 1 point, Muskie are worth 5,
> longest fish is worth 5
> So it didn't take long for this to get over my head.
> Anybody want to give me a hand at this, leagues start in 2 weeks!!!
> Thanks,
> Marty
Try something on these lines
SELECT Angler ,
SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
Northern_total ,
SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
Longest_fish_Total
FROM Fish
Group by Angler
|||On Apr 30, 12:55 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 5:52 am,Marty<mcoon...@.gmail.com> wrote:
>
>
>
>
>
>
> Try something on these lines
> SELECT Angler ,
> SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
> SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
> Northern_total ,
> SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
> SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
> SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
> SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
> Longest_fish_Total
> FROM Fish
> Group by Angler- Hide quoted text -
> - Show quoted text -
Thanks!!!

Help with a Query

I have been designing a website for our fishing club and I need help
with one of my queries because I'm not to good at it yet.
The table has FishID, Angler, RecordDate, Species, Length, Photo for
columns.
The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
What I want is display the data like this
Angler Muskie_total Northern_total Walleye_total Catfish_total
Crappie_Total Longest_fish Total_points
All species except Muskie are worth 1 point, Muskie are worth 5,
longest fish is worth 5
So it didn't take long for this to get over my head.
Anybody want to give me a hand at this, leagues start in 2 weeks!!!
Thanks,
MartyOn Apr 30, 5:52 am, Marty <mcoon...@.gmail.com> wrote:
> I have been designing a website for our fishing club and I need help
> with one of my queries because I'm not to good at it yet.
> The table has FishID, Angler, RecordDate, Species, Length, Photo for
> columns.
> The species can only be - Muskie, Northern, Walleye, Catfish, Crappie
> What I want is display the data like this
> Angler Muskie_total Northern_total Walleye_total Catfish_total
> Crappie_Total Longest_fish Total_points
> All species except Muskie are worth 1 point, Muskie are worth 5,
> longest fish is worth 5
> So it didn't take long for this to get over my head.
> Anybody want to give me a hand at this, leagues start in 2 weeks!!!
> Thanks,
> Marty
Try something on these lines
SELECT Angler ,
SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
Northern_total ,
SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
Longest_fish_Total
FROM Fish
Group by Angler|||On Apr 30, 12:55 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Apr 30, 5:52 am,Marty<mcoon...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> Try something on these lines
> SELECT Angler ,
> SUM(CASE WHEN Species = 'Muskie' THEN 5 END ) AS Muskie_total ,
> SUM(CASE WHEN Species = 'Northern' THEN 1 END ) AS
> Northern_total ,
> SUM(CASE WHEN Species = 'Walleye' THEN 1 END ) AS Walleye_total ,
> SUM(CASE WHEN Species = 'Catfish' THEN 1 END ) AS Catfish_total,
> SUM(CASE WHEN Species = 'Crappie' THEN 1 END ) AS Crappie_Total ,
> SUM(CASE WHEN Species = 'Longest_fish ' THEN 5 END ) AS
> Longest_fish_Total
> FROM Fish
> Group by Angler- Hide quoted text -
> - Show quoted text -
Thanks!!!

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:
>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.

Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT

Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date

Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?

Hi Dwight,

Yes, I think so. But you'll have to provide more info first:

* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.

* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.

* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!

* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.

* Explain the business problem behind your question.

The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?

Hi Dwight,

Yes, I think so. But you'll have to provide more info first:

* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.

* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.

* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!

* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.

* Explain the business problem behind your question.

The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||If I had to do it, I'd probably start with the following:

SELECT DISTINCT employee,
(SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
= maintable.employee GROUP BY employee) as ActiveProjectCount,
(SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
TotalProjectCountPerEmployee
FROM projectdb AS maintable

which would yield something like:

Joe510
Mary78
BrianNull1

I would then look at that Null, say Naaaah... and go about doing it right :)

FN

dwight0 wrote:

> I am having a problem with a query,
> I am not sure if i would use a join or a subquery to complete this
> problem.
> I have two queries, and i need to divide one by the other, but i cant
> seem to get any
> type of join to work with them.
> Here is the situation.
> I have a projectDB table that has a list of different projects for
> each employee to work on.
> Each project has an employee assigned to it.
> The start date is null until the employee starts to work on it.
> I want to find how many percent of all their projects that each
> employee is working on.
> In other words:
> I want to divide query A by query B to see how many percent of
> projects each employee is working on.
>
> Query A count of projects that are being worked because they have a
> date per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
> Query B: Total amount of project per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> Any ideas?|||Oh, I may also look at that first subquery and wonder why the heck I put
a GROUP BY clause when I didn't need one.

FN

fn wrote:

> If I had to do it, I'd probably start with the following:
> SELECT DISTINCT employee,
> (SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
> = maintable.employee GROUP BY employee) as ActiveProjectCount,
> (SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
> TotalProjectCountPerEmployee
> FROM projectdb AS maintable
> which would yield something like:
> Joe 5 10
> Mary 7 8
> Brian Null 1
> I would then look at that Null, say Naaaah... and go about doing it
> right :)
> FN
>
> dwight0 wrote:
>> I am having a problem with a query, I am not sure if i would use a
>> join or a subquery to complete this
>> problem.
>> I have two queries, and i need to divide one by the other, but i cant
>> seem to get any
>> type of join to work with them.
>> Here is the situation.
>> I have a projectDB table that has a list of different projects for
>> each employee to work on.
>> Each project has an employee assigned to it.
>> The start date is null until the employee starts to work on it.
>> I want to find how many percent of all their projects that each
>> employee is working on.
>> In other words:
>> I want to divide query A by query B to see how many percent of
>> projects each employee is working on.
>>
>>
>> Query A count of projects that are being worked because they have a
>> date per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>>
>>
>> Query B: Total amount of project per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>>
>> Any ideas?

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?
On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help using dynamic queries in temp table

The dynamic sql is used for link server. Can someone help. Im getting an error

CREATEPROCEDURE GSCLink

(

@.LinkCompanynvarchar(50),@.Pageint,@.RecsPerPageint)

AS

SET

NOCOUNTON

--Create temp table

CREATE

TABLE #TempTable

(

IDintIDENTITY,Companynvarchar(50),AcctIDint,IsActivebit)

INSERT

INTO #TempTable(Name, AccountID, Active)

--dynamic sql

DECLARE

@.sqlnvarchar(4000)

SET

@.sql='SELECT a.Name, a.AccountID, a.Active

FROM CRMSBALINK.'

+ @.LinkCompany+'.dbo.AccountTable a

LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b

ON a.AccountID = b.oaAccountID

WHERE oaAccountID IS NULL

ORDER BY Name ASC'

EXEC

sp_executesql @.sql

--Find out the first and last record

DECLARE

@.FirstRecint

DECLARE

@.LastRecint

SELECT

@.FirstRec=(@.Page- 1)* @.RecsPerPage

SELECT

@.LastRec=(@.Page* @.RecsPerPage+ 1)

--Return the set of paged records, plus an indication of more records or not

SELECT

*,(SELECTCOUNT(*)FROM #TempTable TIWHERE TI.ID>= @.LastRec)AS MoreRecords

FROM

#TempTable

WHERE

ID> @.FirstRecAND ID< @.LastRec

Error:

Msg 156, Level 15, State 1, Procedure GSCLink, Line 22

Incorrect syntax near the keyword 'DECLARE'.

Here is the problem:

INSERT

INTO #TempTable(Name, AccountID, Active)

It could be something like this:

INTO #TempTable values('Name', 'AccountID', 'Active')

or INTO #TempTable values(@.Name, @.AccountID, @.Active) (if those variables are defined)

or ...

Hope it helps.

|||Isnt the result of the dynamic sql put on the temp table automatically? If not... how can I capture the results of the dynamic sql?|||I got it working... Thanks anyway