Post reply

Need help in complex query/subquery

  • December 15, 2009 at 3:43 pm

    #213604

    May be its not complex for many experts, but I don't know how to write it in SQL 2K.

    Here is my tables

    emplyees: EmpId, EmpName,

    EmpTours: EmpId, Region, TourStartDate, TourEndDate, etc...

    orders: orderdate, EmpId, ordertype, ordertotal etc...

    What I want is on a given date for each employee I need to know their first order of the tour, ordertype, ordertotal, lastorder, ordertype and ordertotal.

    Example:

    Employees:

    EmpId ImpName

    1 John

    2 Mike

    EmpTours:

    EmpId Region TourStartDate TourEndDate

    1 North 5/1/09 6/30/09 --igonore for the query tour ended for given date

    1 West 8/15/09 - (on going)

    2 South 9/1/09 11/15/09

    Orders:

    orderdate EmpId ordertype ordertotal

    5/5/09 1 A 15,000.00

    5/7/09 1 B 75,000.00

    5/21/09 1 A 18,000.00

    8/17/09 1 B 57,000.00 --first order of John for the tour

    8/20/09 1 B 120,000.00

    9/4/09 2 A 32,000.00 --first order of Mike for the tour

    9/10/09 2 B 81,000.00

    9/17/09 1 A 78,000.00 -- last order of John before report date

    10/15/09 2 A 7,000.00

    10/31/09 2 A 13,000.00 --last order of Mike before report date

    11/10/09 2 B 64,500.00

    11/14/09 1 A 14,500.00

    12/05/09 1 B 77,000.00

    Now I choose my reportdate is 10/31/09, my results would be

    EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt

    John West 8/15/09 - 8/17 B 57K 9/17 A 78K

    Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K

    Is this possible with sql query? I did that creating temp table and cursor and looping each record, but I am wondering if someone can help to write single query with subquery.

  • December 15, 2009 at 4:02 pm

    #1092764

    Probably, but then how would you learn how to do it if we do it for you? Give it a try first, and as you have problems, ask for help.

    If you really want help, though, I suggest that you read the first article I reference below in my signature block regarding asking for assistance. Please follow the instructions in that article with posting code and sample data. You did nice job as it is, but anyone wanting to help you would have to do some editting of your code and such be fore it is usable. The more you do up front, the better assistance you will get.

    I'd also post the code you have written so far as well. It can be the starting point for you moving from cursor-based code to set-based code.

  • December 15, 2009 at 4:04 pm

    #1092765

    I second Lynn's reply. Read the referred article and post all what you have tried, that gives a head start for some who wants to solve.

    Help us to help you. 🙂

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • December 16, 2009 at 12:37 pm

    #1093260

    Thanks Lynn and Bru for pointing me the direction. Here is my script to create tables and sample data, can you please guide me to get expected results?

    --===== If the employees table already exists, drop it

    IF OBJECT_ID('TempDB..#emplyees','U') IS NOT NULL

    DROP TABLE #emplyees

    --===== Create the employees table with

    CREATE TABLE #employees(EmpID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    EmpName VARCHAR(30))

    --===== If the EmpTours table already exists, drop it

    IF OBJECT_ID('TempDB..#EmpTours','U') IS NOT NULL

    DROP TABLE #EmpTours

    --===== Create the EmpTours table with

    CREATE TABLE #EmpTours(TourId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, EmpId INT,

    Region VARCHAR(15), TourStartDate DateTime, TourEndDate DateTime)

    --===== If the Orders table already exists, drop it

    IF OBJECT_ID('TempDB..#Orders','U') IS NOT NULL

    DROP TABLE #Orders

    --===== Create the Orders table with

    CREATE TABLE #Orders(OrderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    OrderDate DateTime, EmpId Int, OrderType char(1), OrderTotal Money)

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #Employees ON

    --===== Insert the test data into the employees table

    INSERT INTO #Employees (EmpID, EmpName)

    SELECT '1','John'

    UNION ALL

    SELECT '2','Mike'

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #employees OFF

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #EmpTours ON

    --===== Insert the test data into the EmpTours

    INSERT INTO #EmpTours (TourId, EmpID, Region, TourStartDate, TourEndDate)

    select '1','1','North','5/1/09','6/30/09' --igonore for the query result since tour ended for given date

    union all

    select '2','1','West','8/15/09','' -- (on going)

    union all

    select '3','2','South','9/1/09','11/15/09'

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #EmpTours OFF

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #Orders ON

    --===== Insert the test data into the Orders

    INSERT INTO #Orders (OrderId, OrderDate, EmpID, OrderType, OrderTotal)

    select '1','5/5/09','1','A',15000.00

    union all

    select '2','5/7/09','1','B',75000.00

    union all

    select '3','5/21/09','1','A',18000.00

    union all

    select '4','8/17/09','1','B',57000.00 --first order of John for the tour

    union all

    select '5','8/20/09','1','B',120000.00

    union all

    select '6','9/4/09','2','A', 32000.00 --first order of Mike for the tour

    union all

    select '7','9/10/09','2','B',81000.00

    union all

    select '8','9/17/09','1','A', 78000.00 -- last order of John before report date

    union all

    select '9','10/15/09','2','A',7000.00

    union all

    select '10','10/31/09','2','A', 13000.00 --last order of Mike before report date

    union all

    select '11','11/10/09','2','B',64500.00

    union all

    select '12','11/14/09','1','A',14500.00

    union all

    select '13','12/05/09','1','B', 77000.00

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #Orders OFF

  • December 16, 2009 at 12:51 pm

    #1093264

    Creating the expected results is really a manual process. Look at your sample data you provided, what should the result set returned look like, what is the correct answer? You may also need to explai why if it doesn't appear intuative, ie what rules did you use to come to the answer.

  • December 16, 2009 at 2:27 pm

    #1093317

    What I need is, on a given date which employees were on tour and which region? Along with it, I need their tour start date, tour end date, first order of the tour and last order of ther tour with order date and order totals.

    So based on my rules and sample data for given date 10/31/09 I should get following results:

    EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt

    John West 8/15/09 - 8/17 B 57K 9/17 A 78K

    Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K

  • December 17, 2009 at 9:20 am

    #1093567

    mak101, while I agree with Lynn's assessment that you need to try it yourself first, I also can see where to someone new to SQL, this problem would be difficult to even conceptualize, and since you took the time to type up your sample data... So, I have a solution that works with the data you provided, and returns your desired results for this data only. I can think of many ways this will break depending on your data. However, I think this will whet your appetite for SQL. Sorry for the lack of comments, I'm in a rush now, so post back if you have questions about any of it, and you will probably see other solutions, any of which may be very different from mine.

    DECLARE @date DATETIME

    SELECT @date = '10/31/09'

    SELECT

    t1.EmpName,

    t1.Region,

    t1.TourStartDate,

    t1.TourEndDate,

    t1.FOrdDate,

    FOrdType = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType ELSE NULL END),

    FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END),

    t1.LOrdDate,

    LOrdType = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType ELSE NULL END),

    LOrdTotal = MAX(CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END)

    FROM

    (--Derived table t1 returns the tourdates, and the order dates

    SELECT

    e.EmpId,

    e.EmpName,

    et.Region,

    et.TourStartDate,

    et.TourEndDate,

    FOrdDate = MIN(o.OrderDate),

    LOrdDate = MAX(o.OrderDate)

    FROM #Employees e INNER JOIN #EmpTours et

    ON e.EmpId = et.EmpId INNER JOIN #Orders o

    ON e.EmpId = o.EmpId

    WHERE et.TourStartDate <= @date

    AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)

    AND o.OrderDate BETWEEN et.TourStartDate AND @date

    GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate

    ) t1 INNER JOIN #Orders o

    ON t1.EmpId = o.EmpId

    AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)

    GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • December 17, 2009 at 10:38 am

    #1093608

    Actually Greg, now that mak101 put some effort into setting things up, I'm more than willing to help, just haven't had a lot of time to work on it. You put something together, and if I get time between SQL Server 2008 installs, I may see what I can do as well.

    I'd still like to see mak101 try something as well, instead of just using what we may put together. Would definately help with his understanding of what is happening in the code. For example, does he understand the use of the derived table in your code and what it is doing?

  • December 17, 2009 at 10:44 am

    #1093613

    Good work Greg !

    I spent sometime last evening in solving the issue, but could not move forward after a certain point.

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • December 17, 2009 at 11:03 am

    #1093633

    Regarding the expected results:

    EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt

    John West 8/15/09 - 8/17 B 57K 9/17 A 78K

    Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K

    Since John went on two tours, one in the North Region and one in the West, why is only the West tour reported? Didn't see anything in your criteria for that and just wanted to confirm that yo are only interested in each employee's most recent tour.

  • December 17, 2009 at 12:13 pm

    #1093682

    Yeah Lynn, he wants the data for the tours that are going on, so tour end date can be NULL or some future date when compared to a particular day, here it is "10/31/09".

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • December 17, 2009 at 12:15 pm

    #1093684

    Lynn Pettis (12/17/2009)

    Regarding the expected results:

    EmpName Reg TourStart TourEnd FOrdDate FOrdType FtOrdAmt LOrdDate LOrdType LOrdAmt

    John West 8/15/09 - 8/17 B 57K 9/17 A 78K

    Mike South 9/1/09 11/15/09 9/4 A 32K 10/31 A 13K

    Since John went on two tours, one in the North Region and one in the West, why is only the West tour reported? Didn't see anything in your criteria for that and just wanted to confirm that yo are only interested in each employee's most recent tour.

    Edit: This is why...select '1','1','North','5/1/09','6/30/09' --igonore for the query result since tour ended for given date

    That's a good point. I inferred from mak101's comment to ignore the first tour since it was closed that the desire was to include information only for tours that were in progress at the desired target date. That is the reason for...

    AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)

    I also made the assumption that it is okay for the tour to have ended on the target date.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • December 17, 2009 at 12:35 pm

    #1093700

    We can make all the assumations in the world and still be wrong. I am just asking for clarification of the rules as they aren't clear IMHO.

  • December 17, 2009 at 1:54 pm

    #1093757

    Thanks all of you working on it.

    I was working hard on it and I finished it but not in one query, I dumped all data into #temp table and loop through cursor to find first and last order with other details, I know its long way but it worked. I have not tested Greg's code yet, but I wanted it something like that in one query.

    As I mentioned earlier in my post

    ''What I need is, on a given date which employees were on tour and which region? Along with it, I need their tour start date, tour end date, first order of the tour and last order of ther tour with order date and order totals.''

    So I need tours details only if on given date tour is on. Sorry for any confusion.

  • December 18, 2009 at 8:40 am

    #1094081

    mak101 (12/17/2009)

    I wanted it something like that in one query.

    Ok, here is where a little explanation may come in handy. If you look at my solution, the one comment I added, since I thought that would be the most confusing aspect, was the derived table. The solution is actually two queries, or a query within a query. You should google for "derived table". It is simply a query that acts like an object, and you can select from it, do calculations on it, join to it, etc. In SQL Server 2005 and above, a CTE does the same thing, and is much simpler to use. Anyhow, the derived table first gets the start and end dates, then you can get the other stuff by joining it back to #Orders. It may be confusing at first, but it will become second nature. To see what is happening, you can run the derived table by itself, like this...

    DECLARE @date DATETIME

    SELECT @date = '10/31/09'

    SELECT

    e.EmpId,

    e.EmpName,

    et.Region,

    et.TourStartDate,

    et.TourEndDate,

    FOrdDate = MIN(o.OrderDate),

    LOrdDate = MAX(o.OrderDate)

    FROM #Employees e INNER JOIN #EmpTours et

    ON e.EmpId = et.EmpId INNER JOIN #Orders o

    ON e.EmpId = o.EmpId

    WHERE et.TourStartDate <= @date

    AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)

    AND o.OrderDate BETWEEN et.TourStartDate AND @date

    GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate

    Once you have the dates, the rest is simply a matter of putting the order data in the right place. You may have wondered why I did this..

    FOrdType = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType ELSE NULL END),

    FOrdTotal = MAX(CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END)

    To best understand it, it would be easiest to run the whole thing without using MAX() for the order data...

    DECLARE @date DATETIME

    SELECT @date = '10/31/09'

    SELECT

    t1.EmpName,

    t1.Region,

    t1.TourStartDate,

    t1.TourEndDate,

    t1.FOrdDate,

    FOrdType = CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderType ELSE NULL END,

    FOrdTotal = CASE WHEN o.OrderDate = t1.FOrdDate THEN o.OrderTotal ELSE NULL END,

    t1.LOrdDate,

    LOrdType = CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderType ELSE NULL END,

    LOrdTotal = CASE WHEN o.OrderDate = t1.LOrdDate THEN o.OrderTotal ELSE NULL END

    FROM

    (--Derived table t1 returns the tourdates, and the order dates

    SELECT

    e.EmpId,

    e.EmpName,

    et.Region,

    et.TourStartDate,

    et.TourEndDate,

    FOrdDate = MIN(o.OrderDate),

    LOrdDate = MAX(o.OrderDate)

    FROM #Employees e INNER JOIN #EmpTours et

    ON e.EmpId = et.EmpId INNER JOIN #Orders o

    ON e.EmpId = o.EmpId

    WHERE et.TourStartDate <= @date

    AND (et.TourEndDate > = @date OR et.TourEndDate IS NULL)

    AND o.OrderDate BETWEEN et.TourStartDate AND @date

    GROUP BY e.EmpId,e.EmpName,et.Region,et.TourStartDate,et.TourEndDate

    ) t1 INNER JOIN #Orders o

    ON t1.EmpId = o.EmpId

    AND (t1.FOrdDate = o.OrderDate OR t1.LOrdDate = o.OrderDate)

    --GROUP BY t1.EmpName,t1.Region,t1.TourStartDate,t1.TourEndDate,t1.FOrdDate,t1.LOrdDate

    Now you can see that in the results, you really have two sets of data. There are two rows with the FOrd information where the LOrd information is null, and there are two rows with the LOrd information where the FOrd information is null. The only way to put the FOrd and LOrd information on the same row is to use a GROUP BY, which means you are going to have to use an aggregate function on at least one of the columns. The MAX() serves this function, and it is really not doing anything other than making the GROUP BY happy. If you want to verify that it is doing nothing, change them to MIN(), and you will see that the results do not change. Now, I'm a trial and error kind of guy, so there are probably many better ways to explain this, but I don't know the terminology for a lot of this stuff. Maybe Lynn or Bru would be able to explain it better. Anyhow, if you have any questions, please let us know.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy