SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Information displaying incorrectly.....


Information displaying incorrectly.....

Author
Message
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3053 Visits: 2766
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:

StoreTransactionId StoreId STNNo VoucherBookletNo Quantity AllocatedDate AllocatedBy
1 1006 1004000066 B06349 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d9327
2 1006 1004000066 B06350 2 2013-07-03 11:42:45.480 012a967b-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:
InwardAtStoreID StoreCode STNNo GRNNo VoucherBookletNo ReceivedDate StoreManagerID
1 1006 1004000066 000000001 B06349 2013-07-03 11:43:35.033 012a967b-50b5-4af6-825f-f064af4d9327
2 1006 1004000066 000000001 B06350 2013-07-03 11:43:35.033 012a967b-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:

StoreTransactionId StoreId STNNo VoucherBookletNo Quantity AllocatedDate AllocatedBy
1 1006 1004000066 B06349 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d9327
2 1006 1004000066 B06350 2 2013-07-03 11:42:45.480 012a967b-50b5-4af6-825f-f064af4d9327
3 5001 1006000006 B06349 1 2013-07-03 11:46:14.927 012a967b-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]
@UserID 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 = @UserID

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
FROM GV_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
--WHERE sud.UserID = @UserID
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
FROM GV_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
--WHERE sud.UserID = @UserID
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 = @UserID)
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 = @UserID)
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
FROM GV_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' END AS Booklet,
ISNULL(gvstn.AWBNo, '') AS AWBNo
FROM GV_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/
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3053 Visits: 2766
I have solved my issue Smile

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search