June 4, 2013 at 11:47 pm
Hi,
I have a table in which I have stored some data from XML. Now I want to validate that data from database tables.
Create table #temp
(
StoreCode int,
TerminalId int,
TransactionNo varchar(10),
RequestDate date,
RequestTime char(5),
VoucherDetails varchar(10)
)
StoreCodeTerminalIdTransactionNoRequestDateRequestTimeVoucherDetails
1234201234567892013-03-0511:30VVD0000021
1234201234567892013-03-0511:30VVD0000022
1234201234567892013-03-0511:30BVD0003213
There is a voucher table in my table where all these voucherno in voucherDetails exists. If the status of these vouchers are 'Store Active' or 'Not Expired' then I have to insert these information into another table.
How can I achive this?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 5, 2013 at 12:01 am
It would help to have the DDL for the tables involved, some sample data for the tables and the expected results when the processing is completed.
June 5, 2013 at 12:15 am
Lynn Pettis (6/5/2013)
It would help to have the DDL for the tables involved, some sample data for the tables and the expected results when the processing is completed.
Here is the DDl statments of the table involved in this:
CREATE TABLE [dbo].[GV_Booklet](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BookletID] [varchar](6) NULL,
[LeafCount] [int] NULL,
[Denomination] [int] NULL,
[VoucherTypeId] [int] NOT NULL,
[VoucherNo] [varchar](20) NOT NULL,
[Quantity] [int] NULL,
[CreatedDate] [date] NULL,
[ModifyDate] [datetime] NULL,
[ExpiryDate] [datetime] NULL,
[VoucherStatusId] [int] NOT NULL,
[TransactionID] [varchar](20) NOT NULL,
[AmountValue] [int] NULL,
[ModifiedBy] [nvarchar](50) NULL,
[CreatedBy] [nvarchar](50) NULL,
[IsDeleted] [bit] NULL,
[ValidateDays] [int] NULL,
CONSTRAINT [PK_GV_Booklet] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_Booklet_VoucherNo] UNIQUE NONCLUSTERED
(
[VoucherNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[GV_Booklet] WITH CHECK ADD CONSTRAINT [FK_GV_Booklet_GV_VoucherStatus] FOREIGN KEY([VoucherStatusId])
REFERENCES [dbo].[GV_VoucherStatus] ([VoucherStatusId])
GO
ALTER TABLE [dbo].[GV_Booklet] CHECK CONSTRAINT [FK_GV_Booklet_GV_VoucherStatus]
GO
ALTER TABLE [dbo].[GV_Booklet] WITH CHECK ADD CONSTRAINT [FK_GV_Booklet_GV_VoucherType] FOREIGN KEY([VoucherTypeId])
REFERENCES [dbo].[GV_VoucherType] ([VoucherTypeID])
GO
ALTER TABLE [dbo].[GV_Booklet] CHECK CONSTRAINT [FK_GV_Booklet_GV_VoucherType]
GO
ALTER TABLE [dbo].[GV_Booklet] ADD CONSTRAINT [DF_GV_Booklet_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
IdBookletIDLeafCountDenominationVoucherTypeIdVoucherNoQuantityCreatedDateModifyDateExpiryDateVoucherStatusIdTransactionIDAmountValueModifiedByCreatedByIsDeletedValidateDays
1B06119105551BVB0000042102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
2B06119105551BVB0000043102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
3B06119105551BVB0000044102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
4B06119105551BVB0000045102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
5B06119105551BVB0000046102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
6B06119105551BVB0000047102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
7B06119105551BVB0000048102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
8B06119105551BVB0000049102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
9B06119105551BVB0000050102013-06-052013-06-05 11:28:42.7102013-12-05 11:28:42.7101TRN0000065011030
---------------------------------------------------------------------------------
CREATE TABLE [dbo].[GV_Voucher](
[VoucherId] [int] IDENTITY(1,1) NOT NULL,
[VoucherTypeId] [int] NOT NULL,
[VoucherNo] [varchar](20) NOT NULL,
[Denomination] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[CreatedDate] [date] NULL,
[ModifyDate] [datetime] NULL,
[VoucherStatusId] [int] NOT NULL,
[TransactionID] [varchar](20) NOT NULL,
[Quantity] [int] NOT NULL,
[AmountValue] [int] NULL,
[CreatedBy] [nvarchar](50) NULL,
[ModifiedBy] [nvarchar](50) NULL,
[Validatedays] [int] NULL,
[IsDeleted] [bit] NULL,
CONSTRAINT [PK_GV_Voucher] PRIMARY KEY CLUSTERED
(
[VoucherId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_GVVoucher_VoucherNo] UNIQUE NONCLUSTERED
(
[VoucherNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UN_Vocuher_TransactionID] UNIQUE NONCLUSTERED
(
[VoucherNo] ASC,
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[GV_Voucher] WITH NOCHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus] FOREIGN KEY([VoucherStatusId])
REFERENCES [dbo].[GV_VoucherStatus] ([VoucherStatusId])
GO
ALTER TABLE [dbo].[GV_Voucher] NOCHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus]
GO
ALTER TABLE [dbo].[GV_Voucher] WITH NOCHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherType] FOREIGN KEY([VoucherTypeId])
REFERENCES [dbo].[GV_VoucherType] ([VoucherTypeID])
GO
ALTER TABLE [dbo].[GV_Voucher] NOCHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherType]
GO
ALTER TABLE [dbo].[GV_Voucher] ADD CONSTRAINT [DF_GV_Voucher_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
VoucherIdVoucherTypeIdVoucherNoDenominationExpiryDateCreatedDateModifyDateVoucherStatusIdTransactionIDQuantityAmountValueCreatedByModifiedByValidatedaysIsDeleted
211591VVB0000015152013-11-22 18:19:08.6402013-05-222013-05-22 18:19:08.6409TRN000005910012a967b-50b5-4af6-825f-f064af4d9327012a967b-50b5-4af6-825f-f064af4d9327100
211603VPD0000005102013-11-22 18:22:57.9672013-05-222013-05-22 18:22:57.9679TRN00000601100012a967b-50b5-4af6-825f-f064af4d9327012a967b-50b5-4af6-825f-f064af4d9327300
211612VVD0000002102013-11-22 18:30:55.6502013-05-222013-05-22 18:30:55.6509TRN00000611100012a967b-50b5-4af6-825f-f064af4d9327012a967b-50b5-4af6-825f-f064af4d932750
211621VVB0000016102013-11-29 15:09:07.0672013-05-292013-05-29 15:09:07.0674TRN000006250012a967b-50b5-4af6-825f-f064af4d9327012a967b-50b5-4af6-825f-f064af4d9327600
--------------------------------------------------------------------------
CREATE TABLE [dbo].[GV_VoucherStatus](
[VoucherStatusId] [int] IDENTITY(1,1) NOT NULL,
[VoucherStatus] [varchar](30) NOT NULL,
[Description] [varchar](50) NULL,
[CreatedDate] [datetime] NULL,
[IsDeleted] [bit] NULL,
CONSTRAINT [PK_GV_VoucherStatus] PRIMARY KEY CLUSTERED
(
[VoucherStatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[GV_VoucherStatus] ADD CONSTRAINT [DF_GV_VoucherStatus_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
VoucherStatusIdVoucherStatusDescriptionCreatedDateIsDeleted
1NewNewly created voucher2013-03-18 00:00:00.0000
2PrintedVoucher gets printed2013-03-30 19:00:58.6270
3Active at HOActive at HO2013-03-30 19:00:58.6270
4DispatchedSTN created2013-04-03 17:34:09.4900
5ReceivedGRN is genearated2013-04-03 17:34:09.4900
6Store ActiveAfter GRN created2013-04-03 17:34:09.4900
7HO ExceptionDiscrepancy during Inward atStore2013-04-03 17:34:09.4900
8Store ExceptionDiscrepency during Store transferring2013-04-03 17:34:09.4900
9SoldVoucher is Sold2013-04-09 17:19:10.3970
10CancelledVoucher is cancelled2013-04-09 17:19:10.3970
11RedeemedVoucher is redeemed2013-06-03 17:09:47.9630
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 5, 2013 at 8:00 am
Kapil, you have been around these forums long enough to know that what you posted as sample data is not the best way to post it. Can you turn that into insert statements?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply