?
Solved

HOw to create sql statement?

Posted on 2007-08-08
7
Medium Priority
?
200 Views
Last Modified: 2013-11-05
Is there any way to create the  output results given the input values:

Input table Merchants:          
Unique Merchant No.                        Date
12345                                                2/9/2006 10:18PM
12345                                                2/9/2006  9:31PM
67890                                                5/8/2007  5:00PM            

Output Case 1:            
Unique Merchant No.                        Date                                      Created Column        
12345                                                2/9/2006 10:18PM                       2
12345                                                2/9/2006  9:31PM                        1
67890                                                5/8/2007  5:00PM                        1

Output Case 2:  Notice that I am displaying only columns with a 1 in the front or items that
                         were created first
Unique Merchant No.                        Date                                      Created Column        
12345                                                2/9/2006  9:31PM                        1
67890                                                5/8/2007  5:00PM                        1

The true objective is to come up with the output displayed below:
Output Objective:  Notice that I am displaying only columns with a 1 in the front or items that
                              were created first
Unique Merchant No.                        Date                                              
12345                                                2/9/2006  9:31PM                      
67890                                                5/8/2007  5:00PM                      











0
Comment
Question by:swansonplace
  • 4
  • 2
7 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 19658124
Select * from YourTable T1
 where T1.date = (select max(t2.date) from YourTable T2 where t2.MerchantNo=T1.MerchantNo)
0
 

Author Comment

by:swansonplace
ID: 19658314
I have not figured out how to create the tables from any of the output cases.

But I have got this so far could you point me in the right direction:

select top 1
   select MerchantNo,  dDate from merchants
   order by MerchantNo, dDate desc
   where
       (value = select distinct MerchantNo from merchants)

how to i loop through every single distinct Merchant No.  The thing is this:
1. I perform a select on each distinct MerchantNo where the merchant number is used to create another
    table that has the merchantNo and date for the particular mechantno I am look at. Then,
    out of that set I get the top 1.  The trick is to perform each merchantNo seperately, get the result, and then go to the next merchant number.

Let me know if you have any ideas.  I really like this one.  Makes you think a little.  Like a puzzle.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 800 total points
ID: 19658511
>how to i loop through every single distinct Merchant No.

You don't.  With SQL set processing you do everything at once.  

Let's suppose this creates the  result set you desire:

Select *
from YourTable T1
where T1.date = (select max(t2.date) from YourTable T2 where t2.MerchantNo=T1.MerchantNo)


Then you can make that into a table like this:

Select *
from YourTable T1
INTO NEWTABLE
where T1.date = (select max(t2.date) from YourTable T2 where t2.MerchantNo=T1.MerchantNo)







0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:swansonplace
ID: 19658622
Sorry, I stil don't see how you created t1 and t2.
0
 

Author Comment

by:swansonplace
ID: 19658706
How do I create a table to work from:

select * from (Merchants as M, I would like to have another table here that I create as NM)
where M.date = select(max(M.date)) and
           M.merchantNo = NM.merchantNo

This other table would also use the same Merchants table but it would return this:
select distinct merchantNo from merchants.
-----------------------------------------------------------------------------------------
Input table Merchants:          
Unique Merchant No.                        Date
12345                                                2/9/2006 10:18PM
12345                                                2/9/2006  9:31PM
67890                                                5/8/2007  5:00PM            

Output Objective:  Notice that I am displaying only that were created first
Unique Merchant No.                        Date                                              
12345                                                2/9/2006  9:31PM                      
67890                                                5/8/2007  5:00PM                      


0
 
LVL 9

Accepted Solution

by:
sas13 earned 1200 total points
ID: 19660796
select distinct m.merchantNo, m.date
from Merchants m
where m.date = (select min(date) from Merchants where merchantNo=m.merchantNo)
0
 

Author Comment

by:swansonplace
ID: 19662135
Thank you for the great help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question