Need To Do Some Tricky SQL Sorting

Dec 13, 2007

This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.

I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).

View 2 Replies

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.

Any suggestions would help.

Thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

Tricky SQL - Need Help

Nov 8, 2004

I have 2 tables joined together by the IDs, People and the pets they
own

PEOPLE
ID NAME
1 JohnSMith
2 JaneDoe

PETS
ID PET
1 Dog
2 Cat
2 Hamster
2 Hamster
2 Fish

I have create another where the PETS are in one column separated by
semi-colons and removing the dups

NEW TABLE
ID NAME ALLPETS
1 JohnSmith Dog
2 JaneDoe Cat;Hamster;Fish

What is the best way to do it? The only way I can think of is to run
an update where it checks to see if the value already exists

THanks!

View 1 Replies View Related

Tricky SQL

Mar 18, 2008

Hello,
I have four diffrent tabels:
bo_ Class, bo_Competition, bo_Result, bo_Licence
And list all Licence where bo_Class.classRankingNbr >0

ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------
M70891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M050887PON01 Pontus Svensson U23 SM 2007 - Herrar 2 1 H
M181188MAR01 Marcus Edlund U23 SM 2007 - Herrar 3 1 H
M190291JOH01 Johan Helldén U23 SM 2007 - Herrar 4 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K050785CAR01 Carin Johansson U23 SM 2007- Damer 3 1 D

If bo_Class.ClassRowNbr for an Class is 1 get out place 1 from that competition,
and if an ClassRowNbr is 2 get out top2 from that competition.
And so on.

From that list i want an SubQuery list where all licens order by where
bo_Result.ResultPlace = bo_Class.ClassRowNbr.

In text form:

ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------------------
M170891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D

Hope someone can take the time and help me out.

Best regards
Gert Lindholm

View 6 Replies View Related

Tricky SQL!

Oct 18, 2005

One statistic questiong from an db Resultat.

SELECT m.Namn + ' ' + m.ENamn AS Spelare, SUM(r.Serier) AS Ser, SUM(r.Poang) AS Po, ROUND(SUM(r.Resultat) / SUM(r.Serier * 1.0), 2, 1)AS Snitt, ROUND(SUM(r.Poang * 1.0) / SUM(r.Serier), 2) AS [P Snitt], ROUND(SUM(r.Miss * 1.0 / r.Serier), 2, 1) AS Miss, SUM(r.Miss) AS [Sa Miss], MAX(r.Resultat) AS Bästa, MIN(r.Resultat) AS Sämsta
FROM Resultat r INNER JOIN
Medlemmar m ON r.Spelare = m.ID
WHERE (r.Omgang IN (SELECT DISTINCT TOP 3 Omgang
FROM Resultat
ORDER BY Omgang DESC))
GROUP BY m.Namn + ' ' + m.ENamn
ORDER BY 4 DESC

What i want to do is sort out Min(r.Resultat) where serier is mor than 3, Not WHERE (r.Resultat) >3. More like in Access "Min(IIf(r.serier=4,r.resultat,Null)) AS [Sämsta]" But that i cant do in SQL

Have also tryed with "WHERE (SELECT MIN(r.Serier) FROM Resultat
Resultat r INNER JOIN
medlemmar m ON r.Spelare = m.ID"
Get lowest result same on all players.

Best reg
Gerten

View 2 Replies View Related

Help With Tricky T-SQL

Feb 7, 2006

Hello allI've got this tricky situation that I would like to solve in SQL, butdon't know how to do. This is the table:Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00GetRows @Time='15:30' will return row with Id=4GetRows @Time='16:30' will return row with Id=4 and row=9Logic behind this:Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and sameVId.Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, andTime1 of Id = 4 is higher than @Time => return row with Id = 4.This got a bit messy but if someone could decipher this and possiblygive an answer I'd be very glad.regardsJohnny

View 5 Replies View Related

Here's A Tricky One

Jan 28, 2008

I have an integer in the database that was saved in reverse byte order (BigEndian). Due to some backward compatibility issues (long story) I can't just convert the number to the normal format and save it that way in the database.
Instead, when I read the number in my program, I just reverse its bytes and display the proper value, and translate the number back when it has to be saved back to the database.

Now, the problem is that there are some views that pull this numbers directly from the database and display it.

My question is: can this number be converted from BigEndian to LittleEndian similarly to how I do in my program using T-SQL?

Thanks.

View 4 Replies View Related

Tricky SELECT

Jul 18, 2007

I have a LastName field which holds this dataLastNameJohnson|VasquesAdams|Fox|JohnsonVasques|Smith Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.The @LastName can be something like this: “Fox|Smith�?.I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith . Thank you.

View 14 Replies View Related

Tricky Query??

Mar 21, 2004

I have a table as follows:

Fixtures(ID, HomeTeam, AwayTeam, WeekNumber)

Each team plays alternately at home then away throughout the course of a season.

I want perform 2 seperate queries on this table.

Query 1:

I want to select a particular teams opposition for the entire season.

Query 2:

I want to select a particular teams opposition for a particular weekNumber.

Thanks

View 3 Replies View Related

Tricky SQL Statement (for Me)

Oct 6, 2004

I have a stored procedure called TC3_GetAllJobOrders which takes 8 parameters as filter values and dynamically builds a statement to filter the data. If you pass in null values for the filters, then the data is not filtered.

I want to write another SP called TC3_GetNewestJobOrders which takes 9 parameters. The first 8 parameters are the same as TC3_GetAllJobOrders and the 9th parameter is numRecs which defines the number of records to return. The procedure should call TC3_GetAllJobOrders, sort the data by date and then return the top numRecs. However, I can't figure out the best way to write this stored procedure because it references another one.

I am trying to stay away from dynamic SQL if at all possible. But I am thinking I will have to use dynamic SQL because I don't think the number of records to be returned (as defined using the TOP keyword) can be parameterized. However, I was trying to write a dynamic SQL statement so that the end statement looked something like:

SELECT TOP 10 * FROM ( EXECUTE TC3_GetAllJobOrders ... ) ORDER BY createdOn DESC

However, I guess having the EXECUTE in parens like that is no good and SQL Server doesn't like it. What is the best/correct way to do it?

View 4 Replies View Related

Tricky SQL Statement

Mar 10, 2005

Hello,

I have a SQL statement which is working OK:

SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1

I need to have a third selector, I have used the following code :

SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod3)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1

This is giving me to many answers, does anyone have any good suggestions to improve it?

View 1 Replies View Related

Tricky Delete

Mar 7, 2000

I have 2 tables which are related to each other, each having a Foreign Keys of the other table . When I delete company table, it gives me an error that I'm violating a FK constraint of the table owner. When I try to delete employee, it gives the same error.

CREATE TABLE OWNER {
employee_id PK
company_id - this is a FK of COMPANY
}

CREATE TABLE COMPANY{
company_id PK
owner_id - this is a FK of OWNER
}

Must I drop the constraints before I can delete? I don't want to do that because I don't want so many other tables are dependent on those tables.

Joyce

View 2 Replies View Related

Tricky Sql Query...

Feb 9, 2001

I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...

the problem is:
The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.

I want to find the records where Keys values 1, 2 has the same Item value
(and if they don't I don't want to find any post at all, of course)

something like this:
SELECT * FROM table1
WHERE ???

ex of table1:
Key | Item
----------
1 | 1
1 | 2
2 | 2
3 | 1

please help...

View 3 Replies View Related

Help - With A Tricky Select, Pls

May 13, 2003

Hi, I'm tring to break my head finding a solution of how to return the following result:

I have the source table -> S_TAB with some data like

COL1 COL2 COL3

ABC DTT COL
ANC DRT COL
ANC DRT COL
......
......

what I need is come up with a single select statement (a view) to get the following output:

select <something> as RID, COL1, COL2, COL3 from S_TAB

RID COL1 COL2 COL3
1 ABC DTT COL
2 ANC DRT COL
3 ANC DRT COL
4 .....
5 .....

Any Idea will be appreciated
Dim

View 2 Replies View Related

Tricky Query

Aug 31, 1999

I have a table that keeps track of account access errors. When there are three access errors in one day, the account is locked out. How can i construct a query to select any accounts that have three access errors on the same date. The pertinent fields would be ACCOUNTNUMBER AND ERRORDATE.

View 1 Replies View Related

Tricky Query Need Help

Aug 29, 2001

I have a feeling it is very easy to do what I want. But I don't know how.
I have 2 queries that return 2 results sets. I'd like to have just 1 query
that returns 1 result set that contains all the data of the 2 results sets.

Example.
Query 1 returns
Item Expected
--------------------
Lion 2
Tiger 2
Bear 2

Query 2 returns
Item Actual
-------------------
Lion 1
Bear 1

What I want is 1 query that will combine the results
Item Expected Actual
-------------------------------
Lion 2 1
Tiger 2 0
Bear 2 1

I tried using a unions between the 2 queries but that doesn't work.
I am pulling my hair out. I have been struggling with this for several
days now. Any help would be greatly appreciated.

Thanks
Josh

View 3 Replies View Related

Tricky SQL Query

Feb 9, 2001

View 2 Replies View Related

Tricky Query

Nov 16, 2005

Hi,

I need to return a number of records at specifik days, i do it with this query;

SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS Days, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10))
AS Numbers_total, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_In
FROM tb_SecurityLog
WHERE (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(SL_PolicyName LIKE N'%')
GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
ORDER BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)

i also need to have a criteria at that second COUNT and if the criteria is not met that row should not be counted, is this possible at all?

//Mr

View 6 Replies View Related

Tricky Query

Feb 22, 2007

I have a data table like this:

MachineIDProductSales
-------------------------------
1Magazine$20.00
1Drink$30.00
2Drink$30.00
3Magazine$30.00
3Drink$40.00
4Magazine$30.00
5Food$40.00
5Drink$30.00
6Drink$40.00

One of the reports the user needs to see looks like this:

ProductNumber of MachinesTotal Sales
Magazine/Drink2$120.00
Drink2$70.00
Magazine1$30.00
Food/Drink1$70.00

To clarify:

There are two magazine/drink machines (ID 1 and 3)
There are two drink only machines (ID 2 and 6)
There is one magazine only machine (ID 4)
There is one food and drink machine (ID 5)

How do I do this query?

Ideally, I wouldn't limit the number of products in a given machine, but I can do so if necessary.

I'm using SQL Server 2000 so I can't use the newer PIVOT/UNPIVOT functions in SQL Server 2005.

Here is some setup T-SQL code:

CREATE TABLE SalesData
(
MachineIDINTEGER,
ProductNameVARCHAR(50),
SalesMONEY
)

INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Magazine', 20)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (2, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Drink', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (4, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Food', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (6, 'Drink', 40)

Of course, this is a much simplified version of the real business problem I'm facing. Any help is greatly appreciated. Thanks!

View 5 Replies View Related

A Tricky Query

Aug 10, 2007

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <--- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <--- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <--- Would not pass

How can I accomplish that?

Thanks a lot.

View 2 Replies View Related

Tricky Select With An AVG

May 22, 2008

I don't even know if this is possible, but I need to find a way to do the following:

I have a select statement that returns the the Top (x) scores from a table called Rounds. The number of rows (x) will vary based on another calculation that I have, in this example I used 3.

SELECT TOP (3) Scores
FROM Rounds AS Rounds_1
WHERE (UserID = 'testuser')

I need to take the 3 values from this example, and calculate the AVERAGE. How do I do that?

Thank you.

View 2 Replies View Related

Tricky Sql Problem

Jul 26, 2006

Hello all,
I'm very new to SQL, but find myself with this problem that i've been working on for a while, but I just can't figure out how to work through it.
I've got a database of appointments for vehicles. This database holds start time, month, day, year, end day month, year... etc.

I want to make a query that allows users to select a begining year, month day etc. and ending month year etc.

so i've got this query (I'm using PHP, so the $...'s are just $_Post variables.

so here's my query:
$query = "SELECT * FROM `appointments` WHERE start_year BETWEEN $start_year AND $end_year AND start_month >= $start_month AND end_month <= $end_month AND start_day >= $start_day ORDER BY start_year, start_month, start_day, start_time ASC";

When I try to run this query, if the start month is january, and the end month is january (regardless of year), it returns nothing (because nothing is between january and january, and it's not factoring in the year change. How can I factor in the year?)

Any input is greatly appreciated, I hope you understand where I'm running into trouble.
Thanks again!
-Robert

View 2 Replies View Related

Tricky SELECT

Jul 18, 2007

I have a LastName field which holds this data
LastName
Johnson|Vasques
Adams|Fox|Johnson
Vasques|Smith

Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.

The @LastName can be something like this: “Fox|Smith�?.
I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).

In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith .

Thank you.

View 13 Replies View Related

Tricky SQL Statement.

Oct 7, 2007

Hi guys,

Im trying to devise a statement with will return a true/false value based on if a field in a related table is null or not.

That is I have two tables: ServiceRepairOrders (Parent Table), LoanPhones (Child Table) related by the field "IMEI_ESN", which is a 15 character string.

When returning a "LoanPhone" record, i need one field "InStore", to return true/false based on if one or more related "CompletionDate" fields in ServiceRepairOrders is null or not.

At the moment I have the following:

SELECT LoanPhones.IMEI_ESN, LoanPhones.BoxNumber, LoanPhones.Make, LoanPhones.Model, [ServiceRepairOrders].[CompletionDate] Is Null AS InStore
FROM LoanPhones INNER JOIN ServiceRepairOrders ON LoanPhones.IMEI_ESN = ServiceRepairOrders.LoanPhoneIMEI_ESN;

But i need to ensure its a "one or more" relationship. That is, if all related service repair orders are complete (i.e. a completion date is supplied - not null), then the IsStore should be "True", otherwise false.

View 5 Replies View Related

A Tricky Task

Dec 3, 2007

IN sql server 2005, you can rename a db table as sp_tbl1 by right clicking the table in object explorer and rename it to sp_tbl1,

The task is if you want to again change back the name from sp_tbl1 to tbl1 then sql server does not alow you to do that.

Is there any other way using dbcc commands or anything else to get that renamed back??

Thanks,
Kumar

View 2 Replies View Related

Tricky Questions.

Aug 19, 2005

Most questions of exam 70-228 have a selection of answers that all seemcorrect, but in reality, the right answer is the one that best solves thequestion.There a few trick questions like how to allow someone access toSQL Server using the Guest account over the Internet, and i solved suchtype of tricky questions before in ucertify. I feel i could have donebetter. Good luck to all who are preparing to take this exam.

View 2 Replies View Related

Tricky SQL Question

Feb 22, 2006

Ok so I'm gonna attempt to explain my problem as easy as I can. I havea database of contacts (they are actually vendors). I am writing aprocedure that will query the vendors and generate a tab delimited filethat will be used an accounting program (MYOB). In the vendors databaseI am exporting from, I have a field for "First Name", a field for "LastName" and a field (looked up by ID) for "Company Name". Now here is theproblem. In the usual case there will be a company name, a first nameand a last name for example:Company Name: ACME CorporationFirst Name: JohnLast Name: SmithHOWEVER, sometimes the vendor is not part of a company and the users ofthe database have just been entering in the vendors name as the name ofthe company. So it would end up looking like this:Company Name: John SmithFirst Name: JohnLast Name: SmithSo the problem is that the accounting program that I am importing thesecontacts into uses a weird schema. Instead of separate fields for firstname, last name and company, they have a combined field for "CompanyName/Last Name" and then a separate field for first name. So if thefirst name field is blank then we know this contact is a company andnot an individual. As soon as we provide a first name, the recordbecomes an individual as opposed to a company.SO the MYOB import procedure is expecting two fields. One called"[Co./Last Name]" and one called "[First Name]". So when I query mycurrent vendor database, I need to figure out if the company namecontains both the first and last name of the vendor and if so thenreturn only the vendors "last name" for the "[Co./Last Name]" field.However if the company name is completely different then the first andlast name of the vendor, then I need to return the company name forthe "[Co./Last Name]" field and make the "[First Name]" field blank.Here is what I thought would work but it doesn't:SELECTCASEWHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName+ '%'AND cu.vchrLastName LIKE '%' + co.vchrCompanyName+ '%'THEN cu.vchrLastNameELSE co.vchrCompanyNameEND AS [Co./Last Name],CASEWHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName+ '%'AND cu.vchrLastName LIKE '%' + co.vchrCompanyName+ '%'THEN cu.vchrFirstNameELSE ''END AS [First Name],I don't get an error when I use this code, but it just always fails andends up using the company name no matter what even though I know forsure that the first and last names ARE in the company name.Sorry for my long winded explanation.THANKS

View 5 Replies View Related

Tricky Query Help

Jul 20, 2005

I have 2 tables joined together by the IDs, People and the pets theyownPEOPLEID NAME1 JohnSMith2 JaneDoePETSID PET1 Dog2 Cat2 Hamster2 Hamster2 FishI have create another where the PETS are in one column separated bysemi-colons and removing the dupsNEW TABLEID NAME ALLPETS1 JohnSmith Dog2 JaneDoe Cat;Hamster;FishWhat is the best way to do it? The only way I can think of is to runan update where it checks to see if the value already existsTHanks!

View 4 Replies View Related

Tricky TOP TEN Query

Aug 3, 2006

I'm self-taught at SQL, so this may be an easy one for others, but I can't even figure out how to search for an answer.

I need to put together a query as a datasource for a chart showing the firm's top ten clients by revenue AND the top ten clients by hours worked. It's easy to do either query separately, but the problem comes in when the two are combined. Then top ten by revenue doesn't always include all the top ten by hours clients, and vice versa [at the moment, I'm running a top twenty for each, then hand-compiling the top ten in Excel--oy!].

How can I write a query that will guarantee to include the top ten of both revenue and hours lists?

Thanks,

elinde

View 6 Replies View Related

Tricky JOIN....

Aug 27, 2007

I have a quick question about an INSERT statement I've got that relies on a JOIN to populate rows in a table.

Here's the query in question:

SELECT

PE.PayeeId, --PAYEEID

PAA.PartyAddressId, --PARTYADDRESSID

DS.DistributionId, --DISTRIBUTIONID

EN.EntitlementId, --ENTITLEMENTID

401, --DELIVERYTYPEITEMENUMID

--@ACCOUNTPAYMENTID, --ACCOUNTPAYMENTID

NULL, --PARENTPAYMENTID

--@PAYMENTAMOUNT, --PAYMENTAMOUNT

GETDATE(), --PAYMENTDATE

501, --PAYMENTSTATUSENUMITMEID

GETDATE(), --PAYMENTSTATUSDATE

NULL, --RELEASERUNID

NULL, --RELEASEDATE

NULL, --ACCOUNTTRANSACTIONLOGID

601, --ACCOUNTSTATUSENUMITEMID

GETDATE(), --ACCOUNTSTATUSDATE

NULL, --ACCOUNTPAIDAMOUNT

0, --RECONCILEDIND

0, --UNDELIVERABLEIND

0, --REISSUEDIND

NULL, --REISSUENOTE

GETDATE(), --CREATEDATE

REPLACE(SYSTEM_USER, 'DOMAIN', ''), --CREATEID

GETDATE(), --MODIFIEDDATE

REPLACE(SYSTEM_USER, 'DOMAIN', '') --MODIFIEDID

FROM Distribution AS DS INNER JOIN

Entitlement AS EN ON DS.DistributionId = EN.DistributionId INNER JOIN

PartyAddress AS PAA inner JOIN

Party ON PAA.PartyId = Party.PartyId INNER JOIN

Payee AS PE ON Party.PartyId = PE.PartyId ON EN.clm_no = Party.clm_no INNER JOIN

clm ON EN.clm_no = clm.clm_no AND PE.clm_no = clm.clm_no

WHERE (clm.clm_no = 11178)

The portion I'm having an issue with is the PartyAddress piece. A PARTY may or may not have a corresponding PARTYADDRESS record. Regardless of whether or not they have one, I still want to insert the rest of the data - not including the PartyAddressID.

But when the query is run, if a Party doesn't have a PArtyAddressID, nothing is returned, although the rest of the data does exist. I know the isssue is the

PartyAddress AS PAA inner JOIN

Party ON PAA.PartyId = Party.PartyId INNER JOIN

query but am out of ideas...

Any help would be appreciated.

Thanks!!

View 3 Replies View Related

Tricky SQL Question

Jul 27, 2006

Hello all,
I'm
very new to SQL, but find myself with this problem that i've been
working on for a while, but I just can't figure out how to work through
it.
I've got a database of appointments for vehicles. This database holds start time, month, day, year, end day month, year... etc.

I want to make a query that allows users to select a begining year, month day etc. and ending month year etc.

so i've got this query (I'm using PHP, so the $...'s are just $_Post variables.

so here's my query:
$query
= "SELECT * FROM `appointments` WHERE start_year BETWEEN $start_year
AND $end_year AND start_month >= $start_month AND end_month <=
$end_month AND start_day >= $start_day ORDER BY start_year,
start_month, start_day, start_time ASC";

When I try to run this
query, if the start month is january, and the end month is january
(regardless of year), it returns nothing (because nothing is between
january and january, and it's not factoring in the year change. How can
I factor in the year?)

Any input is greatly appreciated, I hope you understand where I'm running into trouble.
Thanks again!
-Robert

P.S. I accidentally posted this question in another forum, but i don't think I chose the right one... I hope this forum is right for this type of question. If not, I'm sorry

View 3 Replies View Related

Tricky SQL Query ??

Mar 26, 2008

Hi All,

We've a table which has about 1.5 mil records.
The table has info like AccountNum FName LName, Flag, Address etc.
There are duplicate Account Numbers.

What we're trying to accomplish is:

If I query the table as in the following,
SELECT AccountNum, Flag, COUNT(*) AS CountStar FROM Table1
GROUP BY AccountNum, Flag
HAVING COUNT(*) > 1

I'll get something like this:

AccountNum Flag CountStar
1234567 Y 2
9876543 Y 4
9184382 Y 3
7439831 Y 5
6958373 Y 4
....... . .
....... . . etc..

First, I want to display the result as in the following:

AccountNum Flag
1234567 Y (along with other columns)
1234567 Y
9876543 Y
9876543 Y
9876543 Y
9876543 Y
9184382 Y
9184382 Y
9184382 Y
....... .
....... . etc...

Is it possible?

Once I've the result in the above format, the next step in plan is to update the flag with 'N' leaving the first occurrence flag as 'Y' but all others as 'N' for a particular AccountNum.
Once I do this, the result should look like the following:

AccountNum Flag
1234567 Y (along with other columns)
1234567 N
9876543 Y
9876543 N
9876543 N
9876543 N
9184382 Y
9184382 N
9184382 N
....... .
....... . etc...

Can anybody suggest any ideas how to accomplish this?

Thanks much,
Siva.

View 11 Replies View Related