Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: POS Problems

  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Unanswered: POS Problems

    i have POS program and i need to get report from it
    i have items that it saled and each item may have one modifiers or more modifiers so i need to search in side the table of each item and get all modifiers that may entered rondomely and count each rows that repeated


    ex:
    HTML Code:
    items       modifier1           modifier2          modifier3         modifier 4
    
    item1       mod1               mod2               mod4              mod3  
    
    item1       mod2               mod1               mod3              mod4
    
    item2       mod1               mod3
    
    item3       mod1
    
    item2       mod3                mod1
    
    
    
    this data get me this
    
    items       modifier1          modifier2          modifier3         modifier 4    count
    
    item1       mod1               mod2               mod4              mod3            2  
    
    item2       mod1               mod3                                                 2
    
    
    item3      mod1                                                                    1
    that is the report i need that compare fields in tabl with each otehr and return count of repeated data >


    any one can help me reply to me about this

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the DDL of the table?

    And would you care tyo explain what Logic you used to get the result set?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2012
    Posts
    10

    schema for table

    HTML Code:
    CREATE TABLE [dbo].[OrderTransactions](
    	[OrderTransactionID] [int] IDENTITY(1,1) NOT NULL,
    	[OrderID] [int] NOT NULL,
    	[MenuItemID] [int] NOT NULL,
    	[Mod1ID] [int] NULL,
    	[Mod2ID] [int] NULL,
    	[Mod3ID] [int] NULL,
    	[Mod4ID] [int] NULL,
    	[Mod5ID] [int] NULL,
    	 CONSTRAINT [aaaaaOrderTransactions_PK] PRIMARY KEY NONCLUSTERED 
    (
    	[OrderTransactionID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    
    
    *******************************************************
    
    all modifiers are get from another table and item get from another table but i
    need the data i get it as 
    
    this
    
     OrderTransactionID, OrderID, MenuItemID, Mod1ID, Mod2ID, Mod3ID, Mod4ID, Mod5ID
    
    
    1	1	1	NULL	NULL	NULL	NULL	NULL
    2	2	1	NULL	NULL	NULL	NULL	NULL
    3	3	48	NULL	NULL	NULL	NULL	NULL
    4	4	48	NULL	NULL	NULL	NULL	NULL
    5	5	4	301	NULL	NULL	NULL	NULL
    6	5	51	60	80	101	98	NULL
    7	6	4	365	NULL	NULL	NULL	NULL
    8	6	51	98	60	80	101	64
    9	7	8	1	2	NULL	NULL	NULL
    
    
    
    ***********************************************
    
    MenuItemID  Mod1ID  Mod2ID   Mod3ID  Mod4ID   Mod5ID   count
    
    1                NULL	     NULL	    NULL	    NULL	   NULL          2
    48	     NULL	     NULL	    NULL	    NULL	   NULL          1
    4	     301	     NULL	    NULL	    NULL	   NULL          1
    51	     60	     80	    101	     98	   NULL          2
    8	      1	      2	     NULL	     NULL	   NULL          1
    
      
    
    

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Shouldn't the count for 48 be 2?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2012
    Posts
    10

    schema for table

    ok yes

    you good


    are you have answer about that?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try this

    Code:
        SELECT * 
          FROM OrderTransactions o
    INNER JOIN (SELECT MenuItemID, COUNT(*) AS ROW_COUNT FROM OrderTransactions GROUP BY MenuItemID) AS c
    		ON o.MenuItemID = c.MenuItemID
    	 WHERE EXISTS(SELECT * FROM OrderTransactions i 
    				GROUP BY MenuItemID HAVING o.OrderTransactionID = MIN(i.OrderTransactionID))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If reordered sample data by MenuItemID
    Code:
     OrderTransactionID, OrderID, MenuItemID, Mod1ID, Mod2ID, Mod3ID, Mod4ID, Mod5ID
    
    
    1	1	1	NULL	NULL	NULL	NULL	NULL
    2	2	1	NULL	NULL	NULL	NULL	NULL
    5	5	4	301	NULL	NULL	NULL	NULL
    7	6	4	365	NULL	NULL	NULL	NULL
    9	7	8	1	2	NULL	NULL	NULL
    3	3	48	NULL	NULL	NULL	NULL	NULL
    4	4	48	NULL	NULL	NULL	NULL	NULL
    6	5	51	60	80	101	98	NULL
    8	6	51	98	60	80	101	64
    Why the following Modifiers disappeared?
    365, 64

    I understood by looking Brett's answer.
    Last edited by tonkuma; 02-13-12 at 15:11. Reason: Add I understood by looking Brett's answer.

  8. #8
    Join Date
    Feb 2012
    Posts
    10

    may be data is incorrect

    the data may be i upload it is in correct because i get sample data and change on it with my hands but now you know what i need about this problem

    i get query but it is not get data correctly

    it is work only on MenuItemText

    can i generate it on all modifiers to get all changes of differenet orders of modifiers that repeated on rows

    please more help
    Last edited by hanysalah; 02-13-12 at 15:31.

  9. #9
    Join Date
    Feb 2012
    Posts
    10

    please quick reply

    please i need quick reply about my problem?!!!!!!!!!!!!!!!

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    did you try my code I GAVE you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2012
    Posts
    10

    about my problem

    i have get your solution but i need to the query to compare each modifier on the database and count the samilar on item and modifier may be modifier enchange on column but tha adding of them must simalrity

    can any one help me?

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not based on your sample result set you don't
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2012
    Posts
    10

    my problem is?

    my problem as you see is

    when there exist one item and then there exist no, one or more modifiers

    when no modifier it count easily the repeated of item

    when there exist only one modifier it count also easily for repeated item and modifier

    but if there exist more modifier then it must compare each item and then each modifier with other modifie if the item is match and all different modifiers are mapped on another row it count

    you now know what i mean sorry my engish is weak

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See, I thought I gave you the answer based on your sample data and results

    Better to post better sample data and expected results so we can see what you mean
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Feb 2012
    Posts
    10

    data

    this is query and get me this data ok


    SELECT OrderTransactionID, OrderID, MenuItemID, Mod1ID, Mod2ID, Mod3ID, Mod4ID, Mod5ID, Mod6ID
    FROM OrderTransactions
    ORDER BY OrderID

    HTML Code:
    
    25	20	50	437	66	90	NULL	NULL  NULL
    26	21	3	285	NULL	NULL	NULL	NULL  NULL
    27	21	48	23	29	NULL	NULL	NULL  NULL
    28	22	48	29	NULL	NULL	23	NULL  NULL
    29	23	4	338	367	354	NULL	NULL  NULL
    30	24	4	362	NULL	NULL	NULL	NULL  NULL
    31	24	4	362	NULL	NULL	NULL	NULL  NULL
    32	24	50	90	66	437	NULL	NULL  NULL
    
    
    it must get me as this
    
     MenuItemID  Mod1ID  Mod2ID  Mod3ID  Mod4ID  Mod5ID  Mod6ID  count 
    
    
           50           437        66	       90	    NULL	   NULL     NULL         2
           3	        285        NULL      NULL   NULL	   NULL     NULL         1
           48	         23         29         NULL   NULL	   NULL     NULL         2
           4	        338        367       354	    NULL     NULL     NULL         1
           4	        362        NULL      NULL   NULL     NULL     NULL         2
    sorry for before data but this is data i hape it havenot problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •