July 3, 2013 at 1:11 am
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/
July 3, 2013 at 4:36 am
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