Information displaying incorrectly.....

  • Hi,

    I have 2 tables:

    CREATE TABLE [dbo].[GV_InwardAtStore](

    [InwardAtStoreID] [int] IDENTITY(1,1) NOT NULL,

    [StoreCode] [int] NULL,

    [STNNo] [varchar](20) NULL,

    [GRNNo] [varchar](10) NULL,

    [VoucherBookletNo] [varchar](10) NULL,

    [ReceivedDate] [datetime] NULL,

    [StoreManagerID] [varchar](50) NULL,

    PRIMARY KEY CLUSTERED

    (

    [InwardAtStoreID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[GV_StoreAllocation](

    [StoreTransactionId] [int] IDENTITY(1,1) NOT NULL,

    [StoreId] [int] NULL,

    [STNNo] [varchar](20) NULL,

    [VoucherBookletNo] [varchar](10) NULL,

    [Quantity] [int] NULL,

    [AllocatedDate] [datetime] NULL,

    [AllocatedBy] [varchar](40) NULL,

    CONSTRAINT [PK_GV_Allocation] PRIMARY KEY CLUSTERED

    (

    [StoreTransactionId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    ALTER TABLE [dbo].[GV_StoreAllocation] ADD DEFAULT (getdate()) FOR [AllocatedDate]

    Let me explained the scenario now:

    Firstly I allocate some vouchers to a store, after allocating follwing information gets saved in table GV_StoreAllocation like this:

    StoreTransactionIdStoreIdSTNNoVoucherBookletNoQuantityAllocatedDateAllocatedBy

    110061004000066B0634922013-07-03 11:42:45.480012a967b-50b5-4af6-825f-f064af4d9327

    210061004000066B0635022013-07-03 11:42:45.480012a967b-50b5-4af6-825f-f064af4d9327

    After this I have inwarded those vouchers in my store, when I inward some voucher it will get stored into GV_InwardAtStore like this:

    lets suppose I have inward 2 voucher with information as below:

    InwardAtStoreIDStoreCodeSTNNoGRNNoVoucherBookletNoReceivedDateStoreManagerID

    110061004000066000000001B063492013-07-03 11:43:35.033012a967b-50b5-4af6-825f-f064af4d9327

    210061004000066000000001B063502013-07-03 11:43:35.033012a967b-50b5-4af6-825f-f064af4d9327

    Now, I am not able to sell voucher B06349 so I want to allocate it to another store 5001, as I allocate it to store 5001 table GV_StoreAllocation gets following rows:

    StoreTransactionIdStoreIdSTNNoVoucherBookletNoQuantityAllocatedDateAllocatedBy

    110061004000066B0634922013-07-03 11:42:45.480012a967b-50b5-4af6-825f-f064af4d9327

    210061004000066B0635022013-07-03 11:42:45.480012a967b-50b5-4af6-825f-f064af4d9327

    350011006000006B0634912013-07-03 11:46:14.927012a967b-50b5-4af6-825f-f064af4d9327

    Now, store 5001 have to inward this voucher untill that it should not get displayed on the screen, but its displaying.

    These are the stored procedures that I have created to display vouchers inforamtion for both scrrens Inward at Store and Allocation To Store,

    ALTER PROCEDURE [dbo].[BS_InwardAtStore_ShowVouchers]

    @user-id varchar(40) = '012A967B-50B5-4AF6-825F-F064AF4D9327'

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @IsAdmin INT = 0

    SELECT @IsAdmin = a.IsAdmin FROM GV_StoreUserDetails a WHERE a.UserID = @user-id

    SELECT

    gias.StoreCode ToStore,

    --gias.STNNo,

    gias.grnno GRNNo,

    MIN(gias.VoucherBookletNo)FirstVoucherNo,

    MAX(gias.VoucherBookletNo)LastVoucherNo,

    gv.Denomination Denomination,

    gvt.VoucherType VoucherType,

    CAST(MAX(RIGHT(gias.VoucherBookletNo,7))AS int) - CAST(MIN(RIGHT(gias.VoucherBookletNo,7)) AS int) +1 Quantity,

    CASE WHEN LEFT(MIN(gias.VoucherBookletNo),1)='V' THEN 'VOUCHER' ELSE 'BOOKLET' END AS [Type]

    INTO#Voucher

    FROMGV_InwardAtStore gias

    JOIN GV_Voucher gv ON gv.VoucherNo = gias.VoucherBookletNo

    JOIN GV_VoucherType gvt ON gvt.VoucherTypeID = gv.VoucherTypeId

    JOIN GV_StoreAllocation gsa ON gsa.VoucherBookletNo = gias.VoucherBookletNo

    JOIN GV_StoreUserDetails sud ON sud.StoreCode = gias.StoreCode

    --WHEREsud.UserID = @user-id

    GROUP BY gias.StoreCode,

    gias.grnno,

    gv.Denomination,

    gsa.Quantity,

    gvt.VoucherType

    --UNION ALL

    SELECT

    gias.StoreCode ToStore,

    --gias.STNNo,

    gias.grnno GRNNo,

    MIN(gias.VoucherBookletNo) FirstBookletNo,

    MAX(gias.VoucherBookletNo) LastBookletNo,

    gv.Denomination Denomination,

    gvt.VoucherType VoucherTYpe,

    CAST(MAX(RIGHT(gias.VoucherBookletNo,5))AS int) - CAST(MIN(RIGHT(gias.VoucherBookletNo,5)) AS int) +1 Quantity,

    CASE WHEN LEFT(MIN(gias.VoucherBookletNo),1) = 'B' THEN 'BOOKLET' ELSE 'VOUCHER' END AS [Type]

    INTO#Booklet

    FROMGV_InwardAtStore gias

    JOIN GV_Booklet gv ON gv.BookletID = gias.VoucherBookletNo

    JOIN GV_VoucherType gvt ON gvt.VoucherTypeID = gv.VoucherTypeId

    JOIN GV_StoreAllocation gsa ON gsa.VoucherBookletNo = gias.VoucherBookletNo

    JOIN GV_StoreUserDetails sud ON sud.StoreCode = gias.StoreCode

    --WHEREsud.UserID = @user-id

    GROUP BY gias.StoreCode,

    gias.grnno,

    gv.Denomination,

    gsa.Quantity,

    gvt.VoucherType

    IF @IsAdmin = 1

    SELECT

    v.ToStore,

    v.GRNNo,

    v.FirstVoucherNo,

    v.LastVoucherNo,

    v.Denomination,

    v.VoucherType,

    v.Quantity,

    v.[Type]

    FROM #Voucher v

    UNION ALL

    SELECT

    b.ToStore,

    b.GRNNo,

    b.FirstBookletNo,

    b.LastBookletNo,

    b.Denomination,

    b.VoucherTYpe,

    b.Quantity,

    b.[type]

    FROM #Booklet b

    ELSE

    SELECT

    v.ToStore,

    v.GRNNo,

    v.FirstVoucherNo,

    v.LastVoucherNo,

    v.Denomination,

    v.VoucherType,

    v.Quantity,

    v.[Type]

    FROM #Voucher v

    WHERE v.ToStore = (SELECT a.StoreCode FROM GV_StoreUserDetails a WHERE a.UserID = @user-id)

    UNION ALL

    SELECT

    b.ToStore,

    b.GRNNo,

    b.FirstBookletNo,

    b.LastBookletNo,

    b.Denomination,

    b.VoucherTYpe,

    b.Quantity,

    b.[type]

    FROM #Booklet b

    WHERE b.ToStore = (SELECT a.StoreCode FROM GV_StoreUserDetails a WHERE a.UserID = @user-id)

    ORDER BY GRNNo DESC

    END

    ALTER PROCEDURE [dbo].[BS_StoreAllocation_ShowVouchers]

    AS

    BEGIN

    SET NOCOUNT ON;

    WITH Voucher AS

    (

    SELECT

    gsa.StoreId StoreCode,

    gsa.STNNo STNNo,

    MIN(gsa.VoucherBookletNo) FirstID,

    MAX(gsa.VoucherBookletNo) LastID,

    gv.Denomination,

    CAST(MAX(RIGHT(gsa.VoucherBookletNo,7))AS int) - CAST(MIN(RIGHT(gsa.VoucherBookletNo,7)) AS int) +1 Quantity,

    gv.VoucherTypeId,

    CASE WHEN LEFT(MIN(gsa.VoucherBookletNo),1) = 'B' THEN 'BOOKLET'

    ELSE 'VOUCHER' END AS Booklet,

    ISNULL(gvstn.AWBNo, '') AS AWBNo

    FROMGV_StoreAllocation gsa

    JOIN GV_Voucher gv ON gv.VoucherNo = gsa.VoucherBookletNo

    JOIN GV_VoucherType gvt ON gv.VoucherTypeId = gvt.VoucherTypeID

    JOIN GV_STNDetails gvstn ON gsa.STNNo = gvstn.STNNO

    AND gvstn.TO_StoreCode = gsa.StoreId

    --AND gvstn.AWBNo IS NULL

    GROUP BY gsa.StoreId,

    gsa.STNNo,

    --gsa.Quantity,

    gv.Denomination,

    gv.VoucherTypeId,

    AWBNo

    --gsa.VoucherBookletNo

    ),

    Booklet AS

    (

    SELECT

    gsa.StoreId StoreCode,

    gsa.STNNo STNNo,

    MIN(gsa.VoucherBookletNo) FirstID,

    MAX(gsa.VoucherBookletNo) LastID,

    gv.Denomination,

    CAST(MAX(RIGHT(gsa.VoucherBookletNo,5))AS int) - CAST(MIN(RIGHT(gsa.VoucherBookletNo,5)) AS int) +1 Quantity,

    gv.VoucherTypeId,

    CASE WHEN LEFT(MIN(gsa.VoucherBookletNo),1) = 'B' THEN 'BOOKLET'

    ELSE 'VOUCHER' ENDAS Booklet,

    ISNULL(gvstn.AWBNo, '') AS AWBNo

    FROMGV_StoreAllocation gsa

    JOIN GV_Booklet gv ON gv.BookletID = gsa.VoucherBookletNo

    JOIN GV_VoucherType gvt ON gv.VoucherTypeId = gvt.VoucherTypeID

    JOIN GV_STNDetails gvstn ON gsa.STNNo = gvstn.STNNO

    AND gvstn.TO_StoreCode = gsa.StoreId

    --AND gvstn.AWBNo IS NULL

    GROUP BY gsa.StoreId,

    gsa.STNNo,

    --gsa.Quantity,

    gv.Denomination,

    gv.VoucherTypeId,

    AWBNo

    )

    SELECT * FROM Voucher

    UNION ALL

    SELECT * FROM Booklet

    ORDER BY STNNo DESC

    END

    Please help....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I have solved my issue 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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