October 19, 2012 at 3:51 am
Hi Expert,
In my column [dq_cumulative_time] I hav to show the values as like I have mentioned below comments,So request you to please provide me how to do this?
--In below case Cumulative time should not include Qualified status time.
--Draft status should have DQ duration of 09/13/2012 till date count, say 35 days.
SELECT * FROM [tmp_dist_qual_test] A
WHERE A.dq_org_dist_id=1020
ORDER BY 3 DESC
--In this case cumulative time should be 35 days as well. We should count DQ duration for all status other than Qualified.
SELECT * FROM [tmp_dist_qual_test] A
WHERE A.dq_org_dist_id=1080
ORDER BY 3 DESC
--For 2720 cumulative time should be 28.
SELECT * FROM [tmp_dist_qual_test] A
WHERE A.dq_org_dist_id=2720
ORDER BY 3 DESC
CREATE TABLE [tmp_dist_qual_test](
[dq_src_id] [int] NOT NULL,
[dq_org_dist_id] [nvarchar](140) NULL,
[dq_rec_seq_nbr] [int] NULL,
[dq_status_cd] [nvarchar](220) NULL,
[dq_cycle_cnt] [nvarchar](10) NULL,
[dq_rec_qualified_ind] [bit] NOT NULL,
[dq_rec_eff_dt] [nvarchar](200) NULL,
[dq_cumulative_time] [int] NULL,
[dq_duration] [int],
) ON [PRIMARY]
GO
INSERT INTO [tmp_dist_qual_test] VALUES (10,1020,1,'Qualified',1,1,'17-08-2012',NULL,25)
INSERT INTO [tmp_dist_qual_test] VALUES (11,1020,2,'None' ,1,0,'11-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (12,1020,3,'Draft' ,1,0,'11-09-2012',NULL,2)
INSERT INTO [tmp_dist_qual_test] VALUES (13,1020,4,'None' ,1,0,'13-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (14,1020,5,'Draft' ,1,0,'13-09-2012',NULL,NULL)
INSERT INTO [tmp_dist_qual_test] VALUES (15,1080,1,'Draft' ,1,0,'09-04-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (16,1080,2,'Qualified' ,1,1,'09-04-2012',NULL,2)
INSERT INTO [tmp_dist_qual_test] VALUES (17,1080,3,'Pending' ,2,0,'09-06-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (18,1080,4,'Qualified' ,2,1,'09-06-2012',NULL,5)
INSERT INTO [tmp_dist_qual_test] VALUES (19,1080,5,'None' ,3,0,'11-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (20,1080,6,'Draft' ,3,0,'11-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (21,1080,7,'Qualified' ,3,1,'11-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (22,1080,8,'Pending' ,4,0,'11-09-2012',NULL,2)
INSERT INTO [tmp_dist_qual_test] VALUES (23,1080,9,'None' ,4,0,'13-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (24,1080,10,'Draft' ,4,0,'13-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (25,1080,11,'Qualified' ,4,1,'13-09-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (26,2720,1,'Draft' ,1,0,'31-07-2012',NULL,28)
INSERT INTO [tmp_dist_qual_test] VALUES (27,2720,2,'Qualified' ,1,1,'28-08-2012',NULL,0)
INSERT INTO [tmp_dist_qual_test] VALUES (28,1110,1,'Draft' ,1,0,'12-09-2012',NULL,NULL)
go
October 19, 2012 at 8:11 am
Nice job posting ddl and sample data. Unfortunately it is very unclear what you are trying to do. Can you explain how you came up with the values that you want returned?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply