Database query for different checking approach

[Lethugs]

Query to make movement of a record

I have this tables

     User               Description                 Item
UID        Name     DeID        Dgroup       ID     DeID      Name
 1        Jose       1          Printer       1      1         Stylus
 2        James      2          Monitor       2      1         Epson
                                              3      2         Viewsonic

                             Transaction Details
                Ttype      IID    UserName      NewUser
                Release     1        Jose 
                Release     2        Jose
                Transfer    1        Jose        James
                Release     3        James


How to query database such that when I select printer in description, It will show the Name in
User table and count how many printer he have. IT will not include if the Item is transferred to
other user.

eg.        Printer                        Monitor
    Name            count       Name                    count
    Jose             1          Jose                     0
    James            1          James                    1

Thanks in advance

[BitBlt]

This one was kind of tricky, due to the limited amount of test data you supplied. The trickiness came in when you realize that any single item should only be counted ONCE, regardless of the number of times it gets transferred. Therefore (if I got this right) you might have multiple "Transfer" rows, but you will only ever have a SINGLE "Release" row. You have to take both cases into account...either there are NO transfers, or there might be MULTIPLE transfers.

The last little bit of nastiness is, what happens if you have the same item transferred back to the original release person? Or transferred back and forth multiple times? You have no way of telling which of the transfers happened first...there is no time or date component to the TransactionDetails table to help order the sequence of events. Very nasty.

Anyway, if you disregard this last bit, the following query should suffice:

declare  @desc varchar(25)
select @desc = 'Printer' -- change this value depending on what you want to isolate

select w.[name] as name, w.UID as UID, COUNT(ISNULL(z.DeId, z1.DeId)) as CountOfItems
from dbo.[User] w
-- part 1, get rows where there is no "new user"
left join dbo.TransactionDetails x
on x.name = w.name
left join dbo.item y
on x.iid = y.id
left join dbo.description z
on y.DeId = z.DeId
and z.Dgroup = @desc
-- part 2, get rows where there is a "new user"
left join dbo.TransactionDetails x1
on x1.newUser = w.name
left join dbo.item y1
on x1.iid = y1.id
left join dbo.description z1
on y1.DeId = z1.DeId
and z1.Dgroup = @desc
where 
(
    x.newuser is null      -- case where there IS NOT a "new user"
    and not exists
    (
        select 1 from dbo.TransactionDetails x2
        where x2.name = w.name
        and x2.iid = x.iid
        and x2.newuser is not null
    )
)
or 
(
    x1.newuser is not null  -- case where there IS a "new user"
    and not exists
    (
        select 1 from dbo.TransactionDetails x3
        where x3.newuser = w.name
        and x3.iid = x1.iid
        and x3.newuser is not null
    )
    and not exists  -- and be sure there isn't a subsequent transfer too!
    (
        select 1 from dbo.TransactionDetails x4
        where x4.name = w.name
        and x4.iid = x1.iid
        and x4.newuser is not null
    )
)
group by w.name, w.UID

Please notice that I'm declaring a variable ("@desc") to hold the kind of item you're selecting for. You will of course have to set the value there based on the context of your program.

Hope this helps you out! Good luck!

[Lethugs]

Yes your right, The single item might be transferred back to original user many times in
the long run, but aslong as it is transferred to another, it should be counted in his account. Anyway sorry for not including it but in my table I actually have a transacton number and date of transaction, if you could include that two info, how will I modify your code? Can you also do it without using stored proc? Because in my project I never used stored proc.

Thanks again

[Lethugs]

Sorry for my last post, the situation should be if the item is transferred to another, it should NOT be included in his account anymore.

I already have this headache thinking of how will I do this. Another problem I encountered is that, If the item is released to Jose, then transferred to James, that item must be deducted to jose's accountabilities. The thing that tricks me is that, when counting jose's accountability, eg. printer, which was released to him before but eventually transferred to another, that should NOT be included to him already.

Hope someone can help me out soon
I need to finished this project this week.

thanks a lot

[Lethugs]

@ BitBlt

left join dbo.TransactionDetails x
on x.name = w.name

what is th x.nae there? because on the transaction, there are two name there, the username and newuser, which one there is the x.name?

[Lethugs]

Code did'nt work. =(

[Lethugs]

I saw some tricky problem again while trying to do the program,
when a user for example mike only transferred item to him, there should also accountability to him.

how can I join the user table with the 2 user in transaction table?

please anyone help me

[Lethugs]

Possible transaction includes repair and retiring

please help anyone

[BitBlt]

I will review my code (I thought I posted the right one..let me double-check).

Although, if you have a transaction date and transaction number, that completely changes the scenario. It would really help us solve your issue if you would post the ACTUAL table definitions and some ACTUAL test data (suitably scrubbed, of course). Otherwise you'll get inappropriate or overly complicated solutions like the ones I posted.

[BitBlt]

Okay, it appears that I got one of the column names when I defined TransactionDetail wrong...I used [name] instead of [userName].

So here is my "corrected" code.

select w.[name] as name, w.UID as UID, COUNT(ISNULL(z.DeId, z1.DeId)) as CountOfItems
from dbo.[User] w
-- part 1, get rows where there is no "new user"
left join dbo.TransactionDetails x
on x.userName = w.name
left join dbo.item y
on x.iid = y.id
left join dbo.description z
on y.DeId = z.DeId
and z.Dgroup = 'Printer'
-- part 2, get rows where there is a "new user"
left join dbo.TransactionDetails x1
on x1.newUser = w.name
left join dbo.item y1
on x1.iid = y1.id
left join dbo.description z1
on y1.DeId = z1.DeId
and z1.Dgroup = 'Printer'
where 
(
    x.newuser is null      -- case where there IS NOT a "new user"
    and not exists
    (
        select 1 from dbo.TransactionDetails x2
        where x2.userName = w.name
        and x2.iid = x.iid
        and x2.newuser is not null
    )
)
or 
(
    x1.newuser is not null  -- case where there IS a "new user"
    and not exists
    (
        select 1 from dbo.TransactionDetails x3
        where x3.newuser = w.name
        and x3.iid = x1.iid
    )
    and not exists  -- and be sure there isn't a subsequent transfer too!
    (
        select 1 from dbo.TransactionDetails x4
        where x4.userName = w.name
        and x4.iid = x1.iid
        and x4.newuser is not null
    )
)
group by w.name, w.UID

In my expanded test data, it still had a problem accounting for an item that was transferred from Jose to James, and then transferred back to Jose. In the absence of actual table names and columns, I will dummy up some new definitions with a transaction number and transaction date to demonstrate the technique I have in mind.

But it would still be good if you could supply all that stuff I mentioned...actual table/column names, actual data, etc.

[john.knapp]

How about some table defs and data? Would that help?

Lethugs had sent this out on a previous post, I do not know whether the db structure has changed since then, but I suspect not - as I suggested rethinking the design and his reply indicated that he was "constrained"...

[BitBlt]

Okay, so based on the original specs and column names from the top, along with a dummied up tran date and tran id column for TransactionDetail, here's a query that works:

select a.[name], SUM(a.ItemCount)
from
(
    select   
    w.name as [name], count(z.Dgroup) as ItemCount
    from dbo.[User] w
    left join dbo.TransactionDetails x
    on w.name = x.userName
    and x.newUser is null
    and not exists
        (select 1 from dbo.TransactionDetails xx
        where xx.IID = x.IID
        and xx.userName = x.userName
        and xx.TranDate > x.TranDate
        and x.newUser is null
        )
    left join dbo.Item y
    on x.IID = y.ID
    left join dbo.Description z
    on y.DeID = z.DeID
    and z.Dgroup = 'Monitor'
    group by w.name
    union all
    select  
    w1.name, COUNT(z1.Dgroup)
    from dbo.[User] w1
    left join dbo.TransactionDetails x1
    on w1.name = x1.newUser
    and not exists
        (select 1 from dbo.TransactionDetails xx1
        where xx1.IID = x1.IID
        and xx1.TranDate > x1.TranDate
        )
    left join dbo.Item y1
    on x1.IID = y1.ID
    left join dbo.Description z1
    on y1.DeID = z1.DeID
    and z1.Dgroup = 'Monitor'
    group by w1.name
) a
group by [name]

Here is the full file of all my table defs and test data:

[Lethugs]

Sorry for not including those columns. I just tried to simplify my post just to have an idea how will I solved my problem with your help. So here's how it looks like:

            User                      Description                              Item
      UID       Name           DeID        Dgroup             ID          DeID        Name
       1        Jose            1          Printer            1             1        Stylus
       2        James           2          Monitor            2             1         Epson
       3        Mark                                          3             2        Viewsonic



                Transaction Details
TranNo     Ttype          IID      OUID(UserName)     UserID(NewUser)        Date
 1       Release          1         1                                      11/23/2010
 2       Release          2         1                                      01/18/2011
 3       ItemTransfer     1         1                  2                   03/26/2011
 4       Release          3         2                                      03/30/2011
 5       ItemTransfer     3         2                  3                   05/18/2011
 6       Repair           2         1                                      07/13/2011
 7       Repair           3         3                                      12/23/2011

They just asked me to include repair as transaction type, This gave me another mess.

Thanks for helping.

[Lethugs]

You can use the attached file of john.knapp for reference

Thanks again

[Lethugs]

The selection is based on description.DeID. There is a combobox for description.Dgroup selection and a textbox for description.DeID. The basis for selection in the query is the DeID from a textbox

[Lethugs]

The code includes item released to a user even if its already transferred to another. Where can I modify your code such that it will deduct the transferred item from its original user?

Possible scenario also is the item may transferred back to original user