January 3, 2011 at 10:36 pm
I have the following Schema with the data. Posting table structure with data for your ease.
VTRCheckList (Master Table)
----------------------------
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,
[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]
Structure of TALLY Table
-------------------------
--===== Create a Tally table using a loop (890-1000 ms)
SET NOCOUNT ON
CREATE TABLE dbo.Tally (N INT NOT NULL)
DECLARE @Counter INT
SELECT @Counter = 1
WHILE @Counter <= 11000
BEGIN
INSERT INTO dbo.Tally (N) --(11000 INSERTS!!!!)
SELECT @Counter AS N
SET @Counter = @Counter + 1
END
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Here are the Inserts for VTRCheckList (Master Table)
--------------------------------------------------------
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)')
Here are the Inserts for VTRCheckListDetails (Detail Table)
--------------------------------------------------------
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('1','144','14','2','2010-12-30','1','301','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('2','144','14','2','2010-12-30','2','302','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('3','144','14','2','2010-12-30','3','303','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('4','144','14','2','2010-12-30','4','304','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('5','144','14','2','2010-12-30','5','305','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('6','144','14','2','2010-12-30','6','306','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('7','144','14','2','2010-12-30','7','307','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('8','144','14','2','2010-12-30','8','308','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('9','144','14','2','2010-12-30','9','309','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('10','144','14','2','2010-12-30','10','3010','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('11','144','14','2','2010-12-30','11','3011','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('12','144','14','2','2010-12-30','12','3012','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('13','144','14','2','2010-12-30','13','3013','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('14','144','14','2','2010-12-30','14','3014','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('15','144','14','2','2010-12-30','15','3015','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('16','144','14','2','2010-12-30','16','3016','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('17','144','14','2','2010-12-30','17','3017','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('18','144','14','2','2010-12-30','18','3018','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('19','144','14','2','2010-12-30','19','3019','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('20','144','14','2','2010-12-30','20','3020','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('21','144','14','2','2010-12-30','21','3021','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('22','144','14','2','2010-12-30','22','3022','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('23','144','14','2','2010-12-30','23','3023','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('24','144','14','2','2010-12-30','24','3024','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('25','144','14','2','2010-12-30','25','3025','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('26','144','14','2','2010-12-30','26','3026','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('27','144','14','2','2010-12-30','27','3027','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('28','144','14','2','2010-12-30','28','3028','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('29','144','14','2','2010-12-31','1','311','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('30','144','14','2','2010-12-31','2','312','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('31','144','14','2','2010-12-31','3','312','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('32','144','14','2','2010-12-31','4','313','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('33','144','14','2','2010-12-31','5','314','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('34','144','14','2','2010-12-31','6','315','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('35','144','14','2','2010-12-31','7','316','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('36','144','14','2','2010-12-31','8','317','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('37','144','14','2','2010-12-31','9','318','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('38','144','14','2','2010-12-31','10','319','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('39','144','14','2','2010-12-31','11','320','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('40','144','14','2','2010-12-31','12','321','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('41','144','14','2','2010-12-31','13','322','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('42','144','14','2','2010-12-31','14','323','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('43','144','14','2','2010-12-31','15','324','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('44','144','14','2','2010-12-31','16','325','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('45','144','14','2','2010-12-31','17','326','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('46','144','14','2','2010-12-31','18','327','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('47','144','14','2','2010-12-31','19','328','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('48','144','14','2','2010-12-31','20','329','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('49','144','14','2','2010-12-31','21','330','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('50','144','14','2','2010-12-31','22','331','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('51','144','14','2','2010-12-31','23','332','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('52','144','14','2','2010-12-31','24','333','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('53','144','14','2','2010-12-31','25','334','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('54','144','14','2','2010-12-31','26','335','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('55','144','14','2','2010-12-31','27','336','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('56','144','14','2','2010-12-31','28','338','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('57','144','14','2','2011-01-02','1','201','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('58','144','14','2','2011-01-02','2','202','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('59','144','14','2','2011-01-02','3','203','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('60','144','14','2','2011-01-02','4','204','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('61','144','14','2','2011-01-02','5','205','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('62','144','14','2','2011-01-02','6','206','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('63','144','14','2','2011-01-02','7','207','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('64','144','14','2','2011-01-02','8','208','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('65','144','14','2','2011-01-02','9','209','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('66','144','14','2','2011-01-02','10','2010','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('67','144','14','2','2011-01-02','11','2011','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('68','144','14','2','2011-01-02','12','2012','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('69','144','14','2','2011-01-02','13','2013','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('70','144','14','2','2011-01-02','14','2014','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('71','144','14','2','2011-01-02','15','2015','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('72','144','14','2','2011-01-02','16','2016','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('73','144','14','2','2011-01-02','17','2017','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('74','144','14','2','2011-01-02','18','2018','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('75','144','14','2','2011-01-02','19','2019','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('76','144','14','2','2011-01-02','20','2020','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('77','144','14','2','2011-01-02','21','2021','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('78','144','14','2','2011-01-02','22','2022','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('79','144','14','2','2011-01-02','23','2023','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('80','144','14','2','2011-01-02','24','2024','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('81','144','14','2','2011-01-02','25','2025','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('82','144','14','2','2011-01-02','26','2026','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('83','144','14','2','2011-01-02','27','2027','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('84','144','14','2','2011-01-02','28','2028','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('225','144','14','2','2011-01-03','1','301','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('226','144','14','2','2011-01-03','2','302','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('227','144','14','2','2011-01-03','3','303','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('228','144','14','2','2011-01-03','4','304','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('229','144','14','2','2011-01-03','5','305','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('230','144','14','2','2011-01-03','6','306','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('231','144','14','2','2011-01-03','7','307','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('232','144','14','2','2011-01-03','8','308','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('233','144','14','2','2011-01-03','9','309','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('234','144','14','2','2011-01-03','10','310','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('235','144','14','2','2011-01-03','11','311','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('236','144','14','2','2011-01-03','12','312','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('237','144','14','2','2011-01-03','13','313','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('238','144','14','2','2011-01-03','14','314','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('239','144','14','2','2011-01-03','15','315','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('240','144','14','2','2011-01-03','16','316','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('241','144','14','2','2011-01-03','17','317','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('242','144','14','2','2011-01-03','18','318','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('243','144','14','2','2011-01-03','19','319','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('244','144','14','2','2011-01-03','20','320','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('245','144','14','2','2011-01-03','21','321','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('246','144','14','2','2011-01-03','22','322','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('247','144','14','2','2011-01-03','23','323','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('248','144','14','2','2011-01-03','24','324','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('249','144','14','2','2011-01-03','25','325','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('250','144','14','2','2011-01-03','26','326','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('251','144','14','2','2011-01-03','27','327','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
INSERT VTRCheckListDetails(rec_id,userid,branchid,hub_id,vtrRespDate,CLid,VtrValue,trans_status,last_updated,update_user) VALUES('252','144','14','2','2011-01-03','28','3228','1',convert(datetime,'Jan 3 2011 12:00AM'),'144')
Here is the query I got from this forum a SQL guru helped me 🙂 fantastic i must say.
USE Samforms
GO
--=====================================================================================================================
-- Parameters and presets
--=====================================================================================================================
--===== Conditionally drop the temp tables to make reruns easier.
IF OBJECT_ID('tempdb..#DesiredDates','U') IS NOT NULL
DROP TABLE #DesiredDates
;
IF OBJECT_ID('tempdb..#PreAggregation','U') IS NOT NULL
DROP TABLE #PreAggregation
;
--===== This would be the parameter for a stored procedure
DECLARE @pDesiredMonth DATETIME
;
SELECT @pDesiredMonth = 'January 2011'
;
--===== These are some working variables. Their names tell what they do.
DECLARE @MonthStart DATETIME,
@NextMonthStart DATETIME,
@Days INT,
@SQL VARCHAR(MAX)
;
SELECT @MonthStart = DATEADD(mm,DATEDIFF(mm,'1753',@pDesiredMonth),'1753'),
@NextMonthStart = DATEADD(mm,1,@MonthStart),
@Days = DATEDIFF(dd,@MonthStart,@NextMonthStart)
;
SELECT DesiredDay = N,
DesiredDate = DATEADD(dd,t.N-1,@MonthStart)
INTO #DesiredDates
FROM dbo.Tally t
WHERE N BETWEEN 1 AND @Days
;
--===== This pre-aggregates the data and, yeah, it uses an index seek to do so.
-- It's just easier than joining the dates above with a million row table and the "reflection" in a CTE
-- will still cause that join to happen. Instead, we used "Divide'n'Conquer" on this.
SELECT CLid,
TheDay = DAY(vtrRespDate),
Total_Count = SUM(Cast(VtrValue as Int))
INTO #PreAggregation
FROM VTRCheckListDetails
WHERE vtrRespDate >= @MonthStart
AND vtrRespDate < @NextMonthStart
GROUP BY CLid , vtrRespDate
;
--===== This creates the "Task_ID" portion of the report query.
-- READ about "GROUP BY WITH CUBE" to learn what "GROUPING" does for WITH ROLLUP and WITH CUBE.
SELECT @SQL = 'SELECT CLid = CASE WHEN GROUPING(CLid) = 1 THEN SPACE(5)+''Total'' ELSE RIGHT(SPACE(10)+CAST(CLid AS VARCHAR(10)),10) END,'
;
--===== This creates the section of the report query that creates the columns for each day of the selected month.
SELECT @SQL = @SQL + CHAR(10)
+ 'SUM(CASE WHEN date.DesiredDay = ' + CAST(t.DesiredDay AS VARCHAR(2))
+ ' THEN preagg.Total_Count ELSE 0 END) AS ' + QUOTENAME(CONVERT(CHAR(6),t.DesiredDate,13))+','
FROM #DesiredDates t
;
--===== This creates the total for each Task_ID and finishes up the query with criteria, grouping, etc, etc.
SELECT @SQL = @SQL + ' Total = SUM(Total_Count)
FROM #DesiredDates date
LEFT JOIN #PreAggregation preagg
ON date.DesiredDay = preagg.TheDay
WHERE preagg.CLid > 0
GROUP BY preagg.CLid WITH ROLLUP --READ ABOUT GROUP BY, WITH ROLLUP, and WITH CUBE (for "GROUPING")
ORDER BY CLid
;
'
;
;
--===== This executes the dynamic SQL to actually produce the report
EXEC (@SQL)
;
Now I want 2 things.
1. Need to display CheckList Name instead of ID
2. want to display 'Sunday' in output column if the day is sunday or something else to show there was sunday on that day.
January 4, 2011 at 8:07 am
First of all: EXCELLENT way to provide sample data!! VERY GOOD JOB!!
Now to your questions:
1. Need to display CheckList Name instead of ID
2. want to display 'Sunday' in output column if the day is sunday or something else to show there was sunday on that day.
#1: First, you'd need to include it in the #PreAggregation table:
SELECT VTRCheckList.CLid,
CLName,
TheDay = DAY(vtrRespDate),
Total_Count = SUM(Cast(VtrValue as Int))
INTO #PreAggregation
FROM VTRCheckListDetails
INNER JOIN VTRCheckList on VTRCheckListDetails.CLid= VTRCheckList.CLid
WHERE vtrRespDate >= @MonthStart
AND vtrRespDate < @NextMonthStart
GROUP BY VTRCheckList.CLid , CLName,vtrRespDate
Then you'd need to change the related sections of your dynamic SQL:
SELECT @SQL = 'SELECT CLName = CASE WHEN GROUPING(CLName) = 1 THEN SPACE(5)+''Total'' ELSE CLName END,'
...
GROUP BY preagg.CLName WITH ROLLUP --READ ABOUT GROUP BY, WITH ROLLUP, and WITH CUBE (for "GROUPING")
ORDER BY CLName
#2
You'd need to find out if a date is Sunday. Among various options I decided to use a version that does not depend on the settings of DATEFIRST or LANGUAGE. It counts the days between Jan. 1st 1900 (Monday) and the current day and takes the Modulo of it.
DATEDIFF(dd,0,t.DesiredDate)%7 = 0
So your code snippet may look like the following or whatever you like to display differently on a Sunday:
SELECT @SQL = @SQL + CHAR(10)
+ 'SUM(CASE WHEN date.DesiredDay = ' + CAST(t.DesiredDay AS VARCHAR(2))
+ ' THEN preagg.Total_Count ELSE 0 END) AS ''' + CONVERT(CHAR(6),t.DesiredDate,13)+ CASE WHEN DATEDIFF(dd,0,t.DesiredDate)%7 = 0 THEN ' Sun' ELSE '' END+''','
January 4, 2011 at 10:37 am
Lutz,
Thank you for appreciating the way I've posted my data. Actually I've observed that ppl do not bother to provide scripts which is ethically wrong. Its the place where your kinda guys share knowledge with us solve our problems by spending your valuable time so the least we can do is to provide scripts for your ease.
I appreciate your solution its great so thank you so much. Just two things
1. Sunday is showing on wrong day according to your query its showing on Monday 3rd in month of January 2011 or any year or month.
(I've Solved #1 by putting this -> ) CASE WHEN DATEDIFF(dd,-1,t.DesiredDate)%7 = 0
2. On Sunday I want to put text in place of the value column along with the header. is this possible? rest is great.
Regards,
January 4, 2011 at 11:36 am
joshtheflame (1/4/2011)
Lutz,Thank you for appreciating the way I've posted my data. Actually I've observed that ppl do not bother to provide scripts which is ethically wrong. Its the place where your kinda guys share knowledge with us solve our problems by spending your valuable time so the least we can do is to provide scripts for your ease.
I appreciate your solution its great so thank you so much. Just two things
1. Sunday is showing on wrong day according to your query its showing on Monday 3rd in month of January 2011 or any year or month.
(I've Solved #1 by putting this -> ) CASE WHEN DATEDIFF(dd,-1,t.DesiredDate)%7 = 0
2. On Sunday I want to put text in place of the value column along with the header. is this possible? rest is great.
Regards,
It's people with an attitude like you just show that makes it all worth it!!
Regarding the open issues:
#1: *OUCH!!* It needs to be compared to be = 6, not = 0, of course!!! again *OUCH!!*
Or you could use the solution you came up with. Matter of preference, I'd say.
#2: Look at the code snippet I provided and see if you can figure out how it works. Put the calculation into different parts of the CASE statement. I'm sure you'll figure it out... Strong hint:
+ ' THEN preagg.Total_Count ELSE 0 END) AS ''' + CASE WHEN DATEDIFF(dd,0,t.DesiredDate)%7 = 6 THEN ' SomeValue' ELSE CONVERT(CHAR(6),t.DesiredDate,13) END+''','
January 4, 2011 at 9:07 pm
🙂 Lutz I want 'Holiday' to appear in the column value along with the header 'Sunday' actually in place of '0' 🙂
January 5, 2011 at 3:15 am
joshtheflame (1/4/2011)
🙂 Lutz I want 'Holiday' to appear in the column value along with the header 'Sunday' actually in place of '0' 🙂
Oh, I finally got it...
In that case you'd need to add another CASE statement instead of the 0 to the ELSE part of the following code snippet:
THEN preagg.Total_Count ELSE 0
This will cause a conversion error, if preagg.Total_Count is still a numeric value (SQL Server will try to convert 'Holiday' into a number due to higher data type precedence). Therefore, you need to change the data type for preagg.Total_Count to VARCHAR() as well. The easiest would be to cast the value when populating the #PreAggregation table.
January 5, 2011 at 4:50 am
copied from another thread:
I got stuck in a really nasty situation with the dynamic query in my previous thread. If I run the procedure it sure populates the column and display them but for asp.net reporting the dataset needs physical select columns from the procedure but it is coming up with only two columns (CLName and Total) ...hope I am making sense.
If the app calls the procedure it should get the same data as if you'd call the sproc from SSMS. Make sure all the data are returned by calling the sproc. It seems like there is some processing in the app afterwards eliminating the other columns (e.g. by trying to assign the data into an array based on predefined column names that are not returned by the sproc...
January 5, 2011 at 4:55 am
Lutz,
Can we do the same with different technique? I mean one simple select with all the 32 desired columns?
January 5, 2011 at 5:25 am
joshtheflame (1/5/2011)
Lutz,Can we do the same with different technique? I mean one simple select with all the 32 desired columns?
What do you mean by "the same" and "desired columns"?
Are you talking about the other thread or T-SQL vs .NET?
Please clarify.
January 5, 2011 at 5:50 am
Well can we achieve the same result with different approach as i am stuck with this dynamic thing and .NET DATA set requirement is column in Select statement ..somehow its now understanding the exec(@SQL).
hummm
January 5, 2011 at 6:14 am
joshtheflame (1/5/2011)
Well can we achieve the same result with different approach as i am stuck with this dynamic thing and .NET DATA set requirement is column in Select statement ..somehow its now understanding the exec(@SQL).hummm
Edit: original posted solution (TableValuedFunction) won't work here...
January 5, 2011 at 6:36 am
is there any other way ?
January 5, 2011 at 7:16 am
joshtheflame (1/5/2011)
is there any other way ?
SQL side: Yes.
Please check if you can Edit:call a SQL stored proc from .NET that will return a dataset (don't know, I'm still a T-SQL guy only ...). I'd expect it's possible...
If so, then wrap your code in an stored proc and call it from your app.
If not, wrap a view around the sproc on the SQL side.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply