March 18, 2011 at 12:18 am
I need to build a query to calculate the sum/total of a field and display the output like the following. And I would appreciate if it shows the starting date and ending date instead of Week-1, Week-2...I am just cluless how to do it.
Week-1.........Week-2...........Week-3.......Week-4.......TOTAL
-------------------------------------------------------------
March 18, 2011 at 1:02 am
Josh, with 162 visits and 47 points, you would atleast be knowing how to post a question.. without your table structure, sample data we are just going to guess and give a reply.. Please post the current table structure, some mock-up data and your expected result.. I am sure i know a way on how to do it.. a small help from your end will make me (us) work on the query right away..
March 18, 2011 at 4:33 am
ColdCofee you are right I am embarrassed so please accept my apology. I want to come up with a stored procedure where i will just pass month number and year and it will show the output in matrix style.
here is the structure
----------------------
CREATE TABLE [dbo].[VTRCheckList](
[CLid] [int] NOT NULL,
[CLName] [varchar](90) NULL,
CONSTRAINT [PK_VTRCheckList] PRIMARY KEY CLUSTERED
(
[CLid] 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].[VTRCheckListDetails](
[rec_id] [bigint] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL,
[branchid] [int] NULL,
[hub_id] [int] NULL,
[sysdate_of_entry] [date] NULL,
[vtrRespDate] [date] NULL,
[CLid] [int] NULL,
[VtrValue] [varchar](5) NULL,
[trans_status] [int] NULL,
[last_updated] [datetime] NULL,
[update_user] [int] NULL,
CONSTRAINT [PK_VTRCheckListDetails] PRIMARY KEY CLUSTERED
(
[rec_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
here is the sample data for VTRCheckList
----------------------------------------------
INSERT VTRCheckList(CLid,CLName) VALUES('1','Cash Withdrawals (Including Online)')
INSERT VTRCheckList(CLid,CLName) VALUES('2','Cash Deposits (Including Online)')
INSERT VTRCheckList(CLid,CLName) VALUES('3','Inward Clearing (Including Online)')
INSERT VTRCheckList(CLid,CLName) VALUES('4','Outward Clearing (Including Online)')
INSERT VTRCheckList(CLid,CLName) VALUES('5','Inward Remittances (Including Online)')
INSERT VTRCheckList(CLid,CLName) VALUES('6','Outward Remittances (Including Online)')
INSERT VTRCheckList(CLid,CLName) VALUES('7','Inward Bills for Collection (IBC) (Include FCY)')
INSERT VTRCheckList(CLid,CLName) VALUES('8','Outward Bill for Collection (OBC) (Include FCY)')
INSERT VTRCheckList(CLid,CLName) VALUES('9','Payment Orders Issued')
INSERT VTRCheckList(CLid,CLName) VALUES('10','Payment Orders Paid')
INSERT VTRCheckList(CLid,CLName) VALUES('11','Demand Draft Issued')
INSERT VTRCheckList(CLid,CLName) VALUES('12','Demand Draft Paid')
INSERT VTRCheckList(CLid,CLName) VALUES('13','NAB Enquires handled')
INSERT VTRCheckList(CLid,CLName) VALUES('14','Salary Payments')
INSERT VTRCheckList(CLid,CLName) VALUES('15','Account to Account Transfer')
INSERT VTRCheckList(CLid,CLName) VALUES('16','Accounts Opened (Walk in)')
INSERT VTRCheckList(CLid,CLName) VALUES('17','Speciment Signature Capture')
INSERT VTRCheckList(CLid,CLName) VALUES('18','Cheque Books Issued')
INSERT VTRCheckList(CLid,CLName) VALUES('19','Stop Payment Marked')
INSERT VTRCheckList(CLid,CLName) VALUES('20','Release of Stop Payment')
INSERT VTRCheckList(CLid,CLName) VALUES('21','Account Closed')
INSERT VTRCheckList(CLid,CLName) VALUES('22','ATM Cards Issued')
INSERT VTRCheckList(CLid,CLName) VALUES('23','ATM Cards Cancelled')
INSERT VTRCheckList(CLid,CLName) VALUES('24','ATM Cards Re-Issued')
INSERT VTRCheckList(CLid,CLName) VALUES('25','ATM Operations')
INSERT VTRCheckList(CLid,CLName) VALUES('26','Branch Service Staff (Permanent Operations Staff)')
INSERT VTRCheckList(CLid,CLName) VALUES('27','Other Admin Staff (Parmanent Telephone Operator, Drivers, Runners, etc)')
INSERT VTRCheckList(CLid,CLName) VALUES('28','Other Admin Staff (Outsource Telephone Operator, Drivers, Runners, etc)')
Data for VTRCheckListDetails
-------------------------------
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('309','63','15','2','2011-01-10','2011-01-03','1','21','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('310','63','15','2','2011-01-10','2011-01-03','2','27','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('311','63','15','2','2011-01-10','2011-01-03','3','6','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('312','63','15','2','2011-01-10','2011-01-03','4','5','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('313','63','15','2','2011-01-10','2011-01-03','5','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('314','63','15','2','2011-01-10','2011-01-03','6','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('315','63','15','2','2011-01-10','2011-01-03','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('316','63','15','2','2011-01-10','2011-01-03','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('317','63','15','2','2011-01-10','2011-01-03','9','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('318','63','15','2','2011-01-10','2011-01-03','10','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('319','63','15','2','2011-01-10','2011-01-03','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('320','63','15','2','2011-01-10','2011-01-03','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('321','63','15','2','2011-01-10','2011-01-03','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('322','63','15','2','2011-01-10','2011-01-03','14','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('323','63','15','2','2011-01-10','2011-01-03','15','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('324','63','15','2','2011-01-10','2011-01-03','16','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('325','63','15','2','2011-01-10','2011-01-03','17','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('326','63','15','2','2011-01-10','2011-01-03','18','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('327','63','15','2','2011-01-10','2011-01-03','19','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('328','63','15','2','2011-01-10','2011-01-03','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('329','63','15','2','2011-01-10','2011-01-03','21','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('330','63','15','2','2011-01-10','2011-01-03','22','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('331','63','15','2','2011-01-10','2011-01-03','23','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('332','63','15','2','2011-01-10','2011-01-03','24','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('333','63','15','2','2011-01-10','2011-01-03','25','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('334','63','15','2','2011-01-10','2011-01-03','26','5','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('335','63','15','2','2011-01-10','2011-01-03','27','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('336','63','15','2','2011-01-10','2011-01-03','28','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('337','63','15','2','2011-01-10','2011-01-04','1','28','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('338','63','15','2','2011-01-10','2011-01-04','2','27','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('339','63','15','2','2011-01-10','2011-01-04','3','8','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('340','63','15','2','2011-01-10','2011-01-04','4','12','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('341','63','15','2','2011-01-10','2011-01-04','5','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('342','63','15','2','2011-01-10','2011-01-04','6','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('343','63','15','2','2011-01-10','2011-01-04','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('344','63','15','2','2011-01-10','2011-01-04','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('345','63','15','2','2011-01-10','2011-01-04','9','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('346','63','15','2','2011-01-10','2011-01-04','10','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('347','63','15','2','2011-01-10','2011-01-04','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('348','63','15','2','2011-01-10','2011-01-04','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('349','63','15','2','2011-01-10','2011-01-04','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('350','63','15','2','2011-01-10','2011-01-04','14','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('351','63','15','2','2011-01-10','2011-01-04','15','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('352','63','15','2','2011-01-10','2011-01-04','16','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('353','63','15','2','2011-01-10','2011-01-04','17','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('354','63','15','2','2011-01-10','2011-01-04','18','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('355','63','15','2','2011-01-10','2011-01-04','19','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('356','63','15','2','2011-01-10','2011-01-04','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('357','63','15','2','2011-01-10','2011-01-04','21','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('358','63','15','2','2011-01-10','2011-01-04','22','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('359','63','15','2','2011-01-10','2011-01-04','23','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('360','63','15','2','2011-01-10','2011-01-04','24','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('361','63','15','2','2011-01-10','2011-01-04','25','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('362','63','15','2','2011-01-10','2011-01-04','26','5','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('363','63','15','2','2011-01-10','2011-01-04','27','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('364','63','15','2','2011-01-10','2011-01-04','28','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('365','63','15','2','2011-01-10','2011-01-05','1','12','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('366','63','15','2','2011-01-10','2011-01-05','2','5','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('367','63','15','2','2011-01-10','2011-01-05','3','13','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('368','63','15','2','2011-01-10','2011-01-05','4','3','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('369','63','15','2','2011-01-10','2011-01-05','5','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('370','63','15','2','2011-01-10','2011-01-05','6','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('371','63','15','2','2011-01-10','2011-01-05','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('372','63','15','2','2011-01-10','2011-01-05','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('373','63','15','2','2011-01-10','2011-01-05','9','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('374','63','15','2','2011-01-10','2011-01-05','10','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('375','63','15','2','2011-01-10','2011-01-05','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('376','63','15','2','2011-01-10','2011-01-05','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('377','63','15','2','2011-01-10','2011-01-05','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('378','63','15','2','2011-01-10','2011-01-05','14','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('379','63','15','2','2011-01-10','2011-01-05','15','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('380','63','15','2','2011-01-10','2011-01-05','16','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('381','63','15','2','2011-01-10','2011-01-05','17','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('382','63','15','2','2011-01-10','2011-01-05','18','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('383','63','15','2','2011-01-10','2011-01-05','19','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('384','63','15','2','2011-01-10','2011-01-05','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('385','63','15','2','2011-01-10','2011-01-05','21','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('386','63','15','2','2011-01-10','2011-01-05','22','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('387','63','15','2','2011-01-10','2011-01-05','23','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('388','63','15','2','2011-01-10','2011-01-05','24','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('389','63','15','2','2011-01-10','2011-01-05','25','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('390','63','15','2','2011-01-10','2011-01-05','26','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('391','63','15','2','2011-01-10','2011-01-05','27','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('392','63','15','2','2011-01-10','2011-01-05','28','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('393','63','15','2','2011-01-10','2011-01-06','1','26','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('394','63','15','2','2011-01-10','2011-01-06','2','28','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('395','63','15','2','2011-01-10','2011-01-06','3','7','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('396','63','15','2','2011-01-10','2011-01-06','4','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('397','63','15','2','2011-01-10','2011-01-06','5','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('398','63','15','2','2011-01-10','2011-01-06','6','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('399','63','15','2','2011-01-10','2011-01-06','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('400','63','15','2','2011-01-10','2011-01-06','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('401','63','15','2','2011-01-10','2011-01-06','9','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('402','63','15','2','2011-01-10','2011-01-06','10','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('403','63','15','2','2011-01-10','2011-01-06','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('404','63','15','2','2011-01-10','2011-01-06','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('405','63','15','2','2011-01-10','2011-01-06','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('406','63','15','2','2011-01-10','2011-01-06','14','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('407','63','15','2','2011-01-10','2011-01-06','15','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('408','63','15','2','2011-01-10','2011-01-06','16','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('409','63','15','2','2011-01-10','2011-01-06','17','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('410','63','15','2','2011-01-10','2011-01-06','18','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('411','63','15','2','2011-01-10','2011-01-06','19','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('412','63','15','2','2011-01-10','2011-01-06','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('413','63','15','2','2011-01-10','2011-01-06','21','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('414','63','15','2','2011-01-10','2011-01-06','22','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('415','63','15','2','2011-01-10','2011-01-06','23','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('416','63','15','2','2011-01-10','2011-01-06','24','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('417','63','15','2','2011-01-10','2011-01-06','25','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('418','63','15','2','2011-01-10','2011-01-06','26','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('419','63','15','2','2011-01-10','2011-01-06','27','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('420','63','15','2','2011-01-10','2011-01-06','28','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('421','63','15','2','2011-01-10','2011-01-07','1','17','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('422','63','15','2','2011-01-10','2011-01-07','2','11','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('423','63','15','2','2011-01-10','2011-01-07','3','3','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('424','63','15','2','2011-01-10','2011-01-07','4','7','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('425','63','15','2','2011-01-10','2011-01-07','5','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('426','63','15','2','2011-01-10','2011-01-07','6','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('427','63','15','2','2011-01-10','2011-01-07','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('428','63','15','2','2011-01-10','2011-01-07','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('429','63','15','2','2011-01-10','2011-01-07','9','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('430','63','15','2','2011-01-10','2011-01-07','10','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('431','63','15','2','2011-01-10','2011-01-07','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('432','63','15','2','2011-01-10','2011-01-07','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('433','63','15','2','2011-01-10','2011-01-07','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('434','63','15','2','2011-01-10','2011-01-07','14','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('435','63','15','2','2011-01-10','2011-01-07','15','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('436','63','15','2','2011-01-10','2011-01-07','16','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('437','63','15','2','2011-01-10','2011-01-07','17','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('438','63','15','2','2011-01-10','2011-01-07','18','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('439','63','15','2','2011-01-10','2011-01-07','19','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('440','63','15','2','2011-01-10','2011-01-07','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('441','63','15','2','2011-01-10','2011-01-07','21','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('442','63','15','2','2011-01-10','2011-01-07','22','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('443','63','15','2','2011-01-10','2011-01-07','23','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('444','63','15','2','2011-01-10','2011-01-07','24','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('445','63','15','2','2011-01-10','2011-01-07','25','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('446','63','15','2','2011-01-10','2011-01-07','26','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('447','63','15','2','2011-01-10','2011-01-07','27','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('448','63','15','2','2011-01-10','2011-01-07','28','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('449','63','15','2','2011-01-10','2011-01-08','1','7','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('450','63','15','2','2011-01-10','2011-01-08','2','30','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('451','63','15','2','2011-01-10','2011-01-08','3','9','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('452','63','15','2','2011-01-10','2011-01-08','4','7','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('453','63','15','2','2011-01-10','2011-01-08','5','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('454','63','15','2','2011-01-10','2011-01-08','6','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('455','63','15','2','2011-01-10','2011-01-08','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('456','63','15','2','2011-01-10','2011-01-08','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('457','63','15','2','2011-01-10','2011-01-08','9','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('458','63','15','2','2011-01-10','2011-01-08','10','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('459','63','15','2','2011-01-10','2011-01-08','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('460','63','15','2','2011-01-10','2011-01-08','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('461','63','15','2','2011-01-10','2011-01-08','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('462','63','15','2','2011-01-10','2011-01-08','14','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('463','63','15','2','2011-01-10','2011-01-08','15','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('464','63','15','2','2011-01-10','2011-01-08','16','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('465','63','15','2','2011-01-10','2011-01-08','17','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('466','63','15','2','2011-01-10','2011-01-08','18','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('467','63','15','2','2011-01-10','2011-01-08','19','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('468','63','15','2','2011-01-10','2011-01-08','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('469','63','15','2','2011-01-10','2011-01-08','21','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('470','63','15','2','2011-01-10','2011-01-08','22','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('471','63','15','2','2011-01-10','2011-01-08','23','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('472','63','15','2','2011-01-10','2011-01-08','24','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('473','63','15','2','2011-01-10','2011-01-08','25','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('474','63','15','2','2011-01-10','2011-01-08','26','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('475','63','15','2','2011-01-10','2011-01-08','27','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('476','63','15','2','2011-01-10','2011-01-08','28','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('477','63','15','2','2011-01-10','2011-01-10','1','24','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('478','63','15','2','2011-01-10','2011-01-10','2','30','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('479','63','15','2','2011-01-10','2011-01-10','3','9','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('480','63','15','2','2011-01-10','2011-01-10','4','6','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('481','63','15','2','2011-01-10','2011-01-10','5','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('482','63','15','2','2011-01-10','2011-01-10','6','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('483','63','15','2','2011-01-10','2011-01-10','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('484','63','15','2','2011-01-10','2011-01-10','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('485','63','15','2','2011-01-10','2011-01-10','9','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('486','63','15','2','2011-01-10','2011-01-10','10','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('487','63','15','2','2011-01-10','2011-01-10','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('488','63','15','2','2011-01-10','2011-01-10','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('489','63','15','2','2011-01-10','2011-01-10','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('490','63','15','2','2011-01-10','2011-01-10','14','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('491','63','15','2','2011-01-10','2011-01-10','15','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('492','63','15','2','2011-01-10','2011-01-10','16','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('493','63','15','2','2011-01-10','2011-01-10','17','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('494','63','15','2','2011-01-10','2011-01-10','18','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('495','63','15','2','2011-01-10','2011-01-10','19','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('496','63','15','2','2011-01-10','2011-01-10','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('497','63','15','2','2011-01-10','2011-01-10','21','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('498','63','15','2','2011-01-10','2011-01-10','22','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('499','63','15','2','2011-01-10','2011-01-10','23','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('500','63','15','2','2011-01-10','2011-01-10','24','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('501','63','15','2','2011-01-10','2011-01-10','25','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('502','63','15','2','2011-01-10','2011-01-10','26','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('503','63','15','2','2011-01-10','2011-01-10','27','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('504','63','15','2','2011-01-10','2011-01-10','28','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('505','18','1','1','2011-01-11','2011-01-03','1','45','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('506','18','1','1','2011-01-11','2011-01-03','2','38','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('507','18','1','1','2011-01-11','2011-01-03','3','35','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('508','18','1','1','2011-01-11','2011-01-03','4','42','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('509','18','1','1','2011-01-11','2011-01-03','5','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('510','18','1','1','2011-01-11','2011-01-03','6','2','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('511','18','1','1','2011-01-11','2011-01-03','7','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('512','18','1','1','2011-01-11','2011-01-03','8','1','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('513','18','1','1','2011-01-11','2011-01-03','9','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('514','18','1','1','2011-01-11','2011-01-03','10','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('515','18','1','1','2011-01-11','2011-01-03','11','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('516','18','1','1','2011-01-11','2011-01-03','12','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('517','18','1','1','2011-01-11','2011-01-03','13','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('518','18','1','1','2011-01-11','2011-01-03','14','50','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('519','18','1','1','2011-01-11','2011-01-03','15','8','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('520','18','1','1','2011-01-11','2011-01-03','16','2','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('521','18','1','1','2011-01-11','2011-01-03','17','2','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('522','18','1','1','2011-01-11','2011-01-03','18','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('523','18','1','1','2011-01-11','2011-01-03','19','1','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('524','18','1','1','2011-01-11','2011-01-03','20','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('525','18','1','1','2011-01-11','2011-01-03','21','1','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('526','18','1','1','2011-01-11','2011-01-03','22','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('527','18','1','1','2011-01-11','2011-01-03','23','2','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('528','18','1','1','2011-01-11','2011-01-03','24','2','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('529','18','1','1','2011-01-11','2011-01-03','25','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('530','18','1','1','2011-01-11','2011-01-03','26','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('531','18','1','1','2011-01-11','2011-01-03','27','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('532','18','1','1','2011-01-11','2011-01-03','28','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('533','18','1','1','2011-01-11','2011-01-04','1','53','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('534','18','1','1','2011-01-11','2011-01-04','2','42','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('535','18','1','1','2011-01-11','2011-01-04','3','37','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('536','18','1','1','2011-01-11','2011-01-04','4','32','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('537','18','1','1','2011-01-11','2011-01-04','5','6','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('538','18','1','1','2011-01-11','2011-01-04','6','4','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('539','18','1','1','2011-01-11','2011-01-04','7','2','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('540','18','1','1','2011-01-11','2011-01-04','8','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('541','18','1','1','2011-01-11','2011-01-04','9','9','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('542','18','1','1','2011-01-11','2011-01-04','10','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('543','18','1','1','2011-01-11','2011-01-04','11','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('544','18','1','1','2011-01-11','2011-01-04','12','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('545','18','1','1','2011-01-11','2011-01-04','13','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('546','18','1','1','2011-01-11','2011-01-04','14','45','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('547','18','1','1','2011-01-11','2011-01-04','15','11','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('548','18','1','1','2011-01-11','2011-01-04','16','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('549','18','1','1','2011-01-11','2011-01-04','17','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('550','18','1','1','2011-01-11','2011-01-04','18','6','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('551','18','1','1','2011-01-11','2011-01-04','19','2','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('552','18','1','1','2011-01-11','2011-01-04','20','1','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('553','18','1','1','2011-01-11','2011-01-04','21','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('554','18','1','1','2011-01-11','2011-01-04','22','8','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('555','18','1','1','2011-01-11','2011-01-04','23','3','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('556','18','1','1','2011-01-11','2011-01-04','24','6','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('557','18','1','1','2011-01-11','2011-01-04','25','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('558','18','1','1','2011-01-11','2011-01-04','26','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('559','18','1','1','2011-01-11','2011-01-04','27','0','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('560','18','1','1','2011-01-11','2011-01-04','28','5','1',convert(datetime,'2011-01-11 00:00:00.000',121),'18')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('561','18','1','1','2011-01-11','2011-01-05','1','37','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('562','18','1','1','2011-01-11','2011-01-05','2','58','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('563','18','1','1','2011-01-11','2011-01-05','3','39','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('564','18','1','1','2011-01-11','2011-01-05','4','35','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('565','18','1','1','2011-01-11','2011-01-05','5','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('566','18','1','1','2011-01-11','2011-01-05','6','3','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('567','18','1','1','2011-01-11','2011-01-05','7','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('568','18','1','1','2011-01-11','2011-01-05','8','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('569','18','1','1','2011-01-11','2011-01-05','9','9','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('570','18','1','1','2011-01-11','2011-01-05','10','3','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('571','18','1','1','2011-01-11','2011-01-05','11','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('572','18','1','1','2011-01-11','2011-01-05','12','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('573','18','1','1','2011-01-11','2011-01-05','13','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('574','18','1','1','2011-01-11','2011-01-05','14','8','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('575','18','1','1','2011-01-11','2011-01-05','15','6','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('576','18','1','1','2011-01-11','2011-01-05','16','3','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('577','18','1','1','2011-01-11','2011-01-05','17','3','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('578','18','1','1','2011-01-11','2011-01-05','18','5','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('579','18','1','1','2011-01-11','2011-01-05','19','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('580','18','1','1','2011-01-11','2011-01-05','20','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('581','18','1','1','2011-01-11','2011-01-05','21','2','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('582','18','1','1','2011-01-11','2011-01-05','22','5','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('583','18','1','1','2011-01-11','2011-01-05','23','1','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('584','18','1','1','2011-01-11','2011-01-05','24','3','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('585','18','1','1','2011-01-11','2011-01-05','25','4','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('586','18','1','1','2011-01-11','2011-01-05','26','5','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('587','18','1','1','2011-01-11','2011-01-05','27','0','1',convert(datetime,NULL,121),NULL)
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,sysdate_of_entry,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('588','18','1','1','2011-01-11','2011-01-05','28','5','1',convert(datetime,NULL,121),NULL)
Now based on the above VTRCheckList Detail table I am developing a matrix report which should show the "CLName" all 28 list of names on left hand side and on top I need all the weeks of a given month and year as parameter and sum of VTRValue from detail table of that particualr week...and on every row at the end I need row total also i mean total of all the weeks of the particular "CLName" ...
March 18, 2011 at 4:03 pm
Here you go:
IF OBJECT_ID('DisplayMatrixAsWeeks','P') IS NOT NULL
DROP PROC DisplayMatrixAsWeeks
GO
CREATE PROCEDURE DisplayMatrixAsWeeks
@Input_Month INT,
@Input_Year INT
AS
BEGIN
IF OBJECT_ID('tmpPivotSourceTable') IS NOT NULL
DROP TABLE tmpPivotSourceTable
DECLARE @Year_Month DATETIME
SELECT @Year_Month = DATEADD (M , (@Input_Month - 1) , DATEADD(YEAR,(@Input_Year - 1900),0))
; WITH Tens (N) AS
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
)
, Hundreds (N) AS
(
SELECT T1.N FROM Tens T1 CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N FROM Hundreds T1 CROSS JOIN Hundreds T2
)
, NumbersTable(N) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM Thousands
)
, Preliminary_Date_Table (Dates) AS
(
SELECT DATEADD(DD , N , '31-DEC-1999') FROM NumbersTable
)--SELECT Dates FROM Preliminary_Date_Table
, Grouped_Dates AS
(
SELECT @Input_Year Yr
, @Input_Month Mnth
, DATEPART( Week , Dates) Wk
FROM Preliminary_Date_Table
WHERE YEAR(Dates) = @Input_Year
AND MONTH( Dates ) = @Input_Month
GROUP BY DATEPART( Week , Dates)
) -- SELECT YR , MNTH , WK FROM Grouped_Dates ORDER BY YR , MNTH , WK
, Named_Weeks AS
(
SELECT ChkLst.CLid, ChkLst.CLName ,YR , MNTH , WK
FROM Grouped_Dates
CROSS JOIN VTRCheckList ChkLst
) --select * from Named_Weeks
, Aggregated_data AS
(
SELECT COALESCE( ChkLst.CLid , GD.CLid) CLid
, COALESCE( ChkLst.CLName , GD.CLName) CLName
, COALESCE( DATEPART( WEEK , Details.vtrRespDate),GD.Wk) WK
, ISNULL ( CAST(Details.VtrValue AS INT) ,0) VtrValue
FROM VTRCheckListDetails Details
INNER JOIN VTRCheckList ChkLst
ON Details.CLid = ChkLst.CLid
AND YEAR(Details.vtrRespDate) = @Input_Year
AND MONTH(Details.vtrRespDate ) = @Input_Month
RIGHT JOIN Named_Weeks GD
ON GD.Mnth = MONTH( Details.vtrRespDate)
AND GD.YR = YEAR( Details.vtrRespDate)
AND GD.Wk = DATEPART( WEEK , Details.vtrRespDate )
)
SELECT CLid , CLName
, [1]
, [2]
, [3]
, [4]
, [5]
, [6]
INTO tmpPivotSourceTable
FROM Aggregated_data
PIVOT ( SUM(VtrValue) FOR WK IN ([1],[2],[3],[4],[5],[6]) ) Pivot_Handle
ORDER BY CLid
DECLARE @COLS VARCHAR(MAX);
DECLARE @SQL_STMT VARCHAR(MAX);
DECLARE @SQL_FINAL VARCHAR(MAX);
SELECT @SQL_STMT = '' , @COLS = ''
SELECT @COLS = @COLS + ' ,[' + COLUMN_NAME +'] AS ['
+CONVERT(varchar(11), DATEADD( WEEK , DATEDIFF(WEEK,0 ,DATEADD (WEEK , (CAST (COLUMN_NAME AS INT) - 1 ) ,@Year_Month)) , 0),106 )+']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tmpPivotSourceTable'
AND COLUMN_NAME NOT IN ('CLid','CLName')
SELECT @SQL_STMT = 'SELECT CLid ,CLName ' + @COLS + ' FROM tmpPivotSourceTable'
PRINT @SQL_STMT
EXEC (@SQL_STMT)
IF OBJECT_ID('tmpPivotSourceTable') IS NOT NULL
DROP TABLE tmpPivotSourceTable
END
March 19, 2011 at 1:52 pm
ColdCofee,
Thanks for the lovely query. Actually I am developing a application in ASP.NET where datasets for reporting doesnt populate through dynamic query which has EXEC(@SQL) statement and tmp tables. it requires a pure select with return columns.
I came with my own this way please have a look and help me to modify this.
select *
from
(
select
c.CLName, month(cd.vtrRespDate) [month],'Week-' + cast(datepart(wk,cd.vtrRespDate) as varchar) Weekno,sum(isnull(cast(cd.VtrValue as int),0)) as 'VTRValue' from dbo.VTRCheckList c
LEFT OUTER JOIN VTRCheckListDetails cd ON cd.CLid = c.CLid
where cd.branchid = 1 --and cd.CLid=21
GROUP BY c.CLName, month(cd.vtrRespDate),datepart(wk,cd.vtrRespDate)
) P
PIVOT
(
SUM(VTRValue) FOR Weekno IN
(
[Week-1],[Week-2],[Week-3],[Week-4],[Week-5],[Week-6]
)
) V
Now with my query i have only one problem left which is if week is in between the ending year and beginning of new year. Or what can be the possible problems my weekly report can face?
thanks for your time and efforts.
March 19, 2011 at 11:20 pm
joshtheflame (3/19/2011)
Actually I am developing a application in ASP.NET where datasets for reporting doesnt populate through dynamic query which has EXEC(@SQL) statement and tmp tables. it requires a pure select with return columns.
Unless you're stuck with something like SQL Server CE, why does it have to be all in one query? Why can't you use a stored procedure?
--Jeff Moden
March 20, 2011 at 12:57 am
Jeff,
I do need it in stored procedure but in ASP.NET report builder ask for a stored procedure and then it fetches the name of colums return by the procedure and in dynamic query case it simply cant fetch the name of the columns. In short the dataset needs to know the column names in advance.
March 20, 2011 at 1:31 am
ColdCofee,
I've executed your stored procedure. The results are not correct for some reason. For example if you run the following query and check the outcome for "Accounts Closed" these figures are correct
SELECT CLid, VTRCheckListDetails.vtrRespDate, SUM(CAST(vtrValue as numeric)) from VTRCheckListDetails where branchid = 1 and CLid = 21
GROUP BY CLid, VTRCheckListDetails.vtrRespDate
March 20, 2011 at 2:39 am
This is the procedure i already have which is showing day wise sum of a given month and i want to convert this into week wise. To cross check the values day wise please run the following procedure and check the output then tele the week total...
CREATE PROCEDURE [dbo].[VTR_Report_DaysOfMonthWise]
@Month int,
@Year int,
@Branch_ID int
AS
BEGIN
DECLARE @startDate varchar(20)
DECLARE @endDate varchar(20)
SELECT @startDate = Convert(varchar(10),DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)),105)
SELECT @endDate = Convert(varchar(10),DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0))),105)
SELECT
*
,ISNULL([1],0) + ISNULL([2],0) + ISNULL([3],0) + ISNULL([4],0) + ISNULL([5],0) + ISNULL([6],0) + ISNULL([7],0) + ISNULL([8],0) + ISNULL([9],0) + ISNULL([10],0) + ISNULL([11],0) + ISNULL([12],0) + ISNULL([13],0) + ISNULL([14],0) + ISNULL([15],0) +
ISNULL([16],0) + ISNULL([17],0) + ISNULL([18],0) + ISNULL([19],0) + ISNULL([20],0) + ISNULL([21],0) + ISNULL([22],0) + ISNULL([23],0) + ISNULL([24],0) + ISNULL([25],0) + ISNULL([26],0) + ISNULL([27],0) + ISNULL([28],0) + ISNULL([29],0) +
ISNULL([30],0) + ISNULL([31],0) AS Total
FROM
(
SELECT c.CLName, DATEPART(dd,cd.vtrRespDate) as 'Day', ISNULL(sum(cast(cd.vtrvalue as int)),0) as 'VTRValue'
FROM dbo.VTRCheckList c
LEFT OUTER JOIN VTRCheckListDetails cd ON cd.CLid = c.CLid
AND Convert(date,cd.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, cd.vtrRespDate, 105) <= convert(date,@endDate,105)
AND cd.branchid = @Branch_ID
GROUP BY c.CLName, DATEPART(dd,cd.vtrRespDate)
) a
PIVOT
(
SUM(VTRValue) FOR Day IN
(
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15],
[16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29],
[30], [31]
)
) b
END
March 21, 2011 at 12:23 pm
I prefer the CASE statement:
select . . .,
SUM(CASE WHEN DATEPART(wk, mydate) = 1 THEN myfield ELSE 0 END) as Week1Total,
SUM(CASE WHEN DATEPART(wk, mydate) = 2 THEN myfield ELSE 0 END) as Week2Total,
.
.
.
SUM(CASE WHEN DATEPART(wk, mydate) = 53 THEN myfield ELSE 0 END) as Week53Total,
FROM...
group by ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 22, 2011 at 3:00 pm
select . . .,
SUM(CASE WHEN DATEPART(wk, mydate) = 1 THEN myfield ELSE 0 END) as Week1Total,
SUM(CASE WHEN DATEPART(wk, mydate) = 2 THEN myfield ELSE 0 END) as Week2Total,
.
.
.
SUM(CASE WHEN DATEPART(wk, mydate) = 53 THEN myfield ELSE 0 END) as Week53Total,
FROM...
group by ...
-- Kevin G. Boles
Hi Kevin,
Your query is for one year. How would you go about creating a query for 20 years, where users can select each week of the year. Users must only be able to select one week from SQL Report, the week should exclude Sundays and every week must start with a Monday. I used a kind of calendar lookup table to accomplish same, it caters for 20 years. Would like to compare my method with other methods to see if there is a more efficient way.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply