Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: Need help with Stored Procedure for SQL:

    I need a solution for this problem!! Any help would be great.. can't fiqure out the logic for this one.

    I have a item in Budget Table that is link to a Sales Table.

    There are items that has sales in both Company A and B
    There are items that has sales in Company A only
    There are items that has sales in Company B only

    The problem is that item that only suppose to have sale in Company A only has sales in B as well. But in the Budget Table that item belong to A only so my procedure doesn't pull it. Item in A only pull Item in A only sales.

    So how do I go about getting sales for Item in A only, but has sales A and B company?

    One solution is to create a dummy item in the Budget Table so that it will pick up the sales? But there problem with that as well.

    I'm a novice a this ...any help would be appreciated. If needed, I can post my store procedure for it.

    Thanks,
    Duc.
    Last edited by cuemaster98; 04-14-04 at 14:39.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The DDL of the tables and sample Data will probably get you an answer..

    Like

    CREATE TABLE TableA (Col1int, ect
    GO

    INSERT INTO TableA(Col1,Ect...)
    SELECT 1, ect UNION ALL
    SELECT 1, ect UNION ALL
    SELECT 1, ect
    ..
    ..
    ..

    And maybe what the sproc looks like...
    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
    Apr 2004
    Posts
    6
    This is what I did but it still didn't pull the data???

    Particle SP_ONETIME

    Insert Into WeeklyReports
    (Book, Company, WeekNum, Item, CustomerClass,
    TotalUnits, ReturnUnits, LostUnits, CancelUnits,
    TotalDemand, ReturnDemand, LostDemand, CancelDemand,
    Category, Item_Group, Type, Cat_NonCat)
    Select
    CIBOOK,
    CICONO,
    CIWEEK,
    CIITEM,
    CICCLS,
    sum(CIDMQT),
    abs(sum(CIRTQT)),
    abs(sum(CILSQT)),
    abs(sum(CICNQT)),
    sum(CIDMVL),
    abs(sum(CIRTVL)),
    abs(sum(CILSVL)),
    abs(sum(CICNVL)),
    Category,
    Item_Group,
    Type,
    Cat_NonCat
    FROM
    BUDGETS, QCTITA
    Where

    Catalogue = CIBOOK and
    Company = CICONO and
    Item = CIITEM and
    Category IN ('BA', 'CA', 'CP', 'DO', 'EN', 'FA', 'HD', 'HF', 'HP', 'HS', 'KI', 'RE', 'ST', 'WA', 'WR') and
    CIBOOK = @Book and
    (CIWEEK between @StartWeek1 and @EndWeek1 or CIWEEK between @StartWeek2 and @EndWeek2)
    Group by
    CIBOOK, CICONO, CIWEEK, CIITEM, CICCLS,
    Category, Item_Group, Type, Cat_NonCat
    Order by
    CICONO, Category, Item_Group, Type


    Then SP_ONETIME2

    Book char(10),
    @Book_PREV char(10)

    AS


    Declare @RegalNum int, @PDLNum int

    select @RegalNum = 10, @PDLNum = 30

    Create table #Temp1
    (Book char(10),
    Company int,
    WeekNum int,
    Item char(15),
    CustomerClass char(3),
    TotalUnits int NULL,
    ReturnUnits int NULL,
    LostUnits int NULL,
    CancelUnits int NULL,
    TotalDemand float NULL,
    ReturnDemand float NULL,
    LostDemand float NULL,
    CancelDemand float NULL)

    Create table #ExtraData
    (Book char(10),
    Company int,
    WeekNum int,
    Item char(15),
    CustomerClass char(3) ,
    TotalUnits int NULL,
    ReturnUnits int NULL,
    LostUnits int NULL,
    CancelUnits int NULL,
    TotalDemand float NULL,
    ReturnDemand float NULL,
    LostDemand float NULL,
    CancelDemand float NULL)

    Insert into #Temp1
    Select
    CIBOOK, @RegalNum, CIWEEK, CIITEM, CICCLS,
    sum(CIDMQT),
    abs(sum(CIRTQT)),
    abs(sum(CILSQT)),
    abs(sum(CICNQT)),
    sum(CIDMVL),
    abs(sum(CIRTVL)),
    abs(sum(CILSVL)),
    abs(sum(CICNVL))
    from QCTITA
    where
    CICONO = @PDLNum and
    CIITEM in (select Item from WeeklyReports where Company = @RegalNum) and
    CIITEM not in (select Item from WeeklyReports where Company = @PDLNum) and
    CIBOOK = @Book
    group by CIBOOK, CIWEEK, CIITEM, CICCLS
    Order by
    CIBOOK, CIWEEK, CIITEM, CICCLS

    Insert into #Temp1
    Select
    CIBOOK, @PDLNum, CIWEEK, CIITEM, CICCLS,
    sum(CIDMQT),
    abs(sum(CIRTQT)),
    abs(sum(CILSQT)),
    abs(sum(CICNQT)),
    sum(CIDMVL),
    abs(sum(CIRTVL)),
    abs(sum(CILSVL)),
    abs(sum(CICNVL))
    from QCTITA
    where
    CICONO = @RegalNum and
    CIITEM in (select Item from WeeklyReports where Company = @PDLNum) and
    CIITEM not in (select Item from WeeklyReports where Company = @RegalNum) and
    CIBOOK = @Book
    group by CIBOOK, CIWEEK, CIITEM,CICCLS
    Order by
    CIBOOK, CIWEEK, CIITEM, CICCLS

    Insert into #Temp1
    Select
    CIBOOK, @RegalNum, CIWEEK, CIITEM, CICCLS,
    sum(CIDMQT),
    abs(sum(CIRTQT)),
    abs(sum(CILSQT)),
    abs(sum(CICNQT)),
    sum(CIDMVL),
    abs(sum(CIRTVL)),
    abs(sum(CILSVL)),
    abs(sum(CICNVL))
    from QCTITA
    where
    CICONO = @PDLNum and
    CIITEM in (select Item from WeeklyReports where Company = @RegalNum) and
    CIITEM not in (select Item from WeeklyReports where Company = @PDLNum) and
    CIBOOK = @Book_PREV
    group by CIBOOK, CIWEEK, CIITEM, CICCLS
    Order by
    CIBOOK, CIWEEK, CIITEM, CICCLS

    Insert into #Temp1
    Select
    CIBOOK, @PDLNum, CIWEEK, CIITEM, CICCLS,
    sum(CIDMQT),
    abs(sum(CIRTQT)),
    abs(sum(CILSQT)),
    abs(sum(CICNQT)),
    sum(CIDMVL),
    abs(sum(CIRTVL)),
    abs(sum(CILSVL)),
    abs(sum(CICNVL))
    from QCTITA
    where
    CICONO = @RegalNum and
    CIITEM in (select Item from WeeklyReports where Company = @PDLNum) and
    CIITEM not in (select Item from WeeklyReports where Company = @RegalNum) and
    CIBOOK = @Book_PREV
    group by CIBOOK, CIWEEK, CIITEM, CICCLS
    Order by
    CIBOOK, CIWEEK, CIITEM, CICCLS

    Insert Into #ExtraData
    (Book, Company, WeekNum, Item, CustomerClass)
    Select distinct Book, Company,WeekNum, Item, CustomerClass from WeeklyReports order by Book, Company, WeekNum, Item, CustomerClass

    Update #ExtraData
    Set TotalUnits = (select TotalUnits from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)
    Update #ExtraData
    Set ReturnUnits = (select ReturnUnits from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)
    Update #ExtraData
    Set LostUnits = (select LostUnits from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)
    Update #ExtraData
    Set CancelUnits = (select CancelUnits from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)

    Update #ExtraData
    Set TotalDemand = (select TotalDemand from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)
    Update #ExtraData
    Set ReturnDemand = (select ReturnDemand from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)
    Update #ExtraData
    Set LostDemand = (select LostDemand from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)
    Update #ExtraData
    Set CancelDemand = (select CancelDemand from #Temp1 Where #Temp1.Company = #ExtraData.Company and #Temp1.Item = #ExtraData.Item and #Temp1.CustomerClass = #ExtraData.CustomerClass and #Temp1.Book = #ExtraData.Book and #Temp1.WeekNum = #ExtraData.WeekNum)

    Update #ExtraData
    Set
    TotalUnits = isnull(TotalUnits, 0),
    ReturnUnits = isnull(ReturnUnits, 0),
    LostUnits = isnull(LostUnits, 0),
    CancelUnits = isnull(CancelUnits, 0),
    TotalDemand = isnull(TotalDemand, 0),
    ReturnDemand = isnull(ReturnDemand, 0),
    LostDemand = isnull(LostDemand, 0),
    CancelDemand = isnull(CancelDemand, 0)

    Update WeeklyReports
    Set TotalUnits = TotalUnits + isnull((select TotalUnits from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)
    Update WeeklyReports
    Set ReturnUnits = ReturnUnits + isnull((select ReturnUnits from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)
    Update WeeklyReports
    Set LostUnits = LostUnits + isnull((select LostUnits from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)
    Update WeeklyReports
    Set CancelUnits = CancelUnits + isnull((select CancelUnits from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)

    Update WeeklyReports
    Set TotalDemand = TotalDemand + isnull((select TotalDemand from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)
    Update WeeklyReports
    Set ReturnDemand = ReturnDemand + isnull((select ReturnDemand from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)
    Update WeeklyReports
    Set LostDemand = LostDemand + isnull((select LostDemand from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)
    Update WeeklyReports
    Set CancelDemand = CancelDemand + isnull((select CancelDemand from #ExtraData Where #ExtraData.Company = WeeklyReports.Company and #ExtraData.Item = WeeklyReports.Item and #ExtraData.CustomerClass = WeeklyReports.CustomerClass and #ExtraData.Book = WeeklyReports.Book and #ExtraData.WeekNum = WeeklyReports.WeekNum), 0)

    Update WeeklyReports
    Set ClassFlag =
    Case CustomerClass
    when 'F' then 'F'
    when 'D' then 'F'
    when 'RTF' then 'F'
    else 'R'
    end

    drop table #Temp1
    drop table #ExtraData
    GO

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    never ever name a stored procedure SP_
    use some other naming context

  5. #5
    Join Date
    Apr 2004
    Posts
    6

    Naming

    Is there any reason why SP_ can't be used?

    Thanks.

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


    uummmmm...ouch...

    How do you plan on QAing that?

    Anyway, I was asking for the table DDL and sample data so we could attach the question you have...


    This part doesn't make sense

    There are items that has sales in both Company A and B
    There are items that has sales in Company A only
    There are items that has sales in Company B only
    Tables have data...not Companies

    So if you could show us the DDL and what the data in the tables looks like, maybe we can help...

    debugging or proving that sproc....is totally another matter...

    How about it Rudy....should only take a few weeks....to a month...
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And just curious...how long does that take to run?
    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.

  8. #8
    Join Date
    Apr 2004
    Posts
    6
    Query takes about 17 mins to run.

    Table Structure for BUDGET is:

    1 Company int 4 1
    0 Catalogue varchar 255 1
    0 Item varchar 255 1
    0 Page varchar 255 1
    0 SellQty float 8 1
    0 UnitCost float 8 1
    0 SellPrice float 8 1
    0 BudgetUnits float 8 1
    0 TotalCost float 8 1
    0 TotalRetail float 8 1
    0 TotalGM float 8 1
    0 GM_Perc float 8 1
    0 MasterWeight float 8 1
    0 ItemDescription varchar 255 1
    0 FrDescription varchar 255 1
    0 Category varchar 255 1
    0 Item_Group varchar 255 1
    0 Type varchar 255 1
    0 New_Carry varchar 255 1
    0 Mfg varchar 255 1
    0 PM varchar 255 1
    0 Vendor float 8 1
    0 MasterQty float 8 1
    0 MasterVolume float 8 1
    0 UnitWeight float 8 1
    0 UnitLength float 8 1
    0 UnitHeight float 8 1
    0 PGCode varchar 255 1
    0 PG varchar 255 1
    0 Cat_NonCat varchar 255 1
    0 DispCode varchar 255 1
    0 Finalized varchar 255 1


    And Sales Table is:

    1 CICONO decimal 5 0
    0 CIBOOK char 10 0
    0 CIMEDA char 10 0
    0 CIOROR char 3 0
    0 CICCLS char 3 0
    0 CIYEAR decimal 5 0
    0 CIPERD decimal 5 0
    0 CIWEEK decimal 5 0
    0 CIITEM char 15 0
    0 CIFRCC decimal 5 0
    0 CIFRYY decimal 5 0
    0 CIFRMM decimal 5 0
    0 CIFRDD decimal 5 0
    0 CILSCC decimal 5 0
    0 CILSYY decimal 5 0
    0 CILSMM decimal 5 0
    0 CILSDD decimal 5 0
    0 CIDMQT decimal 5 0
    0 CIDMVL decimal 9 0
    0 CISLQT decimal 5 0
    0 CISLVL decimal 9 0
    0 CIRTQT decimal 5 0
    0 CIRTVL decimal 9 0
    0 CILSQT decimal 5 0
    0 CILSVL decimal 9 0
    0 CICNQT decimal 5 0
    0 CICNVL decimal 9 0
    0 CIOPQT decimal 5 0
    0 CIOPVL decimal 9 0
    0 CIGDSC decimal 9 0
    0 CIODSC decimal 9 0
    0 CICOGS decimal 9 0
    0 CICPQT decimal 5 0
    0 CICPVL decimal 9 0

    Sample DAta:

    Some Items in Budget Table has a Budget for RGL and a Budget for PDL. (PDL is Company B and RGL is Company A). Some item has Budget for RGL only in the Budget Table but in the sales table, Sales exist for that item in PDL. It a bit confusion, but I though my logic for it was correct.

    Grab item in Budget and Qctita (Sales) where item = item, company = company, book = book. Then, Look for item in RGL that exist in PDL or vise versa and add to table (Weekly).
    Last edited by cuemaster98; 04-14-04 at 16:15.

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Re: Naming

    Originally posted by cuemaster98
    Is there any reason why SP_ can't be used?

    Thanks.
    not can't
    Shouldn't...( i got a little carried away with the never never ever ever stuff)

    [Books OnLine] Creating Stored Procedures

    System Stored Procedures
    Many of your administrative activities in Microsoft® SQL Server™ 2000 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

    It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

    1. The stored procedure in the master database.

    2. The stored procedure based on any qualifiers provided (database name or owner).

    3. The stored procedure using dbo as the owner, if one is not specified.
    Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

    Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.


  10. #10
    Join Date
    Apr 2004
    Posts
    6

    Thanks

    Hey Scott,
    Any solutions to this problem?? I give the database structure and my stored procedure that I create to solve this problem... it should have worked but it didn't? It store procedure run fine but the data is not correct!! It still didn't capture the data..

    Thanks,
    Duc.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Please be more clear about this

    So how do I go about getting sales for Item in A only, but has sales A and B company?
    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.

  12. #12
    Join Date
    Apr 2004
    Posts
    6
    To Clarify:

    Item 01564 Has a budget Unit and Budget dollar in the Budget Table in Company RGL only.

    The Budget Table and Sales Table has an inner join where Budget.Item = QCTITA.CIITEM, BUDGET.Company = QCTITA.COCONO

    Since Item 01564 only has budget in Company RGL (A), Sales in QCTITA would be pull for company RGL (A) only. However in the QCTITA Table, sales for Item 01564 in Company PDL(B) exist as well.

    So, I create my procedure to pull everything that relate to the Budget Table First into the WeeklyReport Tables.

    Then, I created another procedures that check if the item 01564 in RGL only had sales in PDL... if there is sales in PDL to added it into Weekly Report for that item.

    CIITEM in (Select Item from WeeklyReport where company= RGL)
    CIITEM Not in (Select Item from WeeklyReport where company = PDL)

    I check that if the Item exist in WeeklyReport for RGL Only or PDL only, then to proceed with this adding the amount it found to the Weekly Report.

    Check my procedure SP-ONETIME2 that I posted.

    This logic sound good but it didn't work!! The SProc run fine!! But the data is not correct.
    Last edited by cuemaster98; 04-15-04 at 14:44.

Posting Permissions

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