number that increment at teh start of each cycle and resets for each new district
-
Hi,
I want the value in the column dq_cycle_cnt, there should be a number that increment at the start of each cycle and resets for each new district.
So please request you to help me out for this issue.
DDL Script
DROP TABLE [dbo].tmp_distgo
CREATE TABLE [dbo].tmp_dist(
[dq_src_id] [int] NOT NULL,
[dq_org_dist_id] [nvarchar](140) NULL,
[dq_rec_seq_nbr] [nvarchar](140) NULL,
[dq_status_cd] [nvarchar](220) NULL,
[dq_prev_status_cd] [nvarchar](230) NULL,
[dq_curr_status][nvarchar](230) NULL,
[dq_rec_eff_dt] [nvarchar](200) NULL,
[dq_rec_upd_user] [nvarchar](330) NULL,
)
go
Insert Script
INSERT INTO tmp_dist VALUES(44,'1130','1','Draft','-','Draft','2012-01-24','Test')INSERT INTO tmp_dist VALUES(43,'1130','2','Qualified','-Draft','Draft','2012-01-24','Test')
INSERT INTO tmp_dist VALUES(42,'1130','3','Suspended','Qualified','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(41,'1130','4','Qualified','Suspended','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(40,'1130','5','Pending Qualification','Qualified','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(39,'1130','6','Pending','Pending Qualification','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(38,'1130','7','Suspended','Pending','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(37,'1130','8','Pending','-Suspended','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(36,'1130','9','Suspended','Pending','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(35,'1130','10','Qualified','Suspended','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(34,'1130','11','Suspended','Qualified','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(33,'1130','12','Pending Qualification','Suspended','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(32,'1130','13','Restricted','Pending Qualification','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(31,'1130','14','Pending', 'Restricted','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(30,'1130','15','Restricted','Pending','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(29,'1130','16','Qualified','Restricted','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(28,'1130','17','Suspended','Qualified','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(27,'1130','18','Pending','Suspended','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(26,'1130','19','Suspended','Pending','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(25,'1130','20','Qualified','Suspended','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(24,'1130','21','Suspended','Qualified','Draft','2012-01-31','Test')
INSERT INTO tmp_dist VALUES(23,'1130','22','Draft','-None','Draft','2012-02-10','ABC')
INSERT INTO tmp_dist VALUES(61,'1450','1','Daft','-','Pending','2012-01-24','XYZ')
INSERT INTO tmp_dist VALUES(60,'1450','2','Qualified','-Draft','Pending','2012-01-24','UVW')
INSERT INTO tmp_dist VALUES(59,'1450','3','Qualified','None','Pending','2012-03-07','DQC')
INSERT INTO tmp_dist VALUES(57,'1450','4','Pending','-Qualified','Pending','2014-03-08','DQC')
INSERT INTO tmp_dist VALUES(58,'1450','5','Pending','-Pending','Pending','2014-03-08','DQC')
GO
See the attached XLS for an example
Regards,
Kiran
-
How do you calculate dq_cycle_cnt in your spreadsheet? How do you know when a new district starts? How did you calculate the dq_rec_start_cycle_ind in the spreadsheet?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Cannot see your attachment and your question is not very clear. However, I guess, what you need is:
ROW_NUMBER() OVER (PARTITION BY [ditsrict column name] ORDER BY [whatever column you want to order by your "cycle" numbers])
-
HI
here is my sql which i hvae written
I have included only two district id for the testing purpose.
WITH q AS (SELECT
dq_src_id
,dq_org_dist_id
,dq_rec_seq_nbr
,dq_status_cd
,dq_prev_status_cd
,dq_curr_status
,dq_rec_eff_dt
,dq_rec_upd_user
FROM tmp_dist
WHERE dq_org_dist_id IN('1130', '1450')
)
SELECT main.dq_src_id,
main.dq_org_dist_id,
main.dq_rec_seq_nbr,
main.dq_status_cd,
q_prev.dq_status_cd AS dq_prev_status_cd,
NULL AS dq_cycle_cnt, -- this should be reset for each district and incremented at each new dq_rec_start_cycle_ind
CAST(CASE WHEN q_next.dq_rec_eff_dt IS NULL THEN 1 ELSE 0 END AS BIT) AS dq_rec_latest_ind,
CAST(CASE WHEN main.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_qualified_ind,
CAST(CASE WHEN q_prev.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_start_cycle_ind,
main.dq_rec_eff_dt,
COALESCE(q_next.dq_rec_eff_dt,'2079-06-06') AS dq_rec_end_dt,
DATEDIFF(day,main.dq_rec_eff_dt,q_next.dq_rec_eff_dt) AS dq_duration,
main.dq_rec_upd_user
FROM q main
LEFT JOIN q q_next
ON main.dq_org_dist_id = q_next.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_next.dq_rec_seq_nbr - 1
LEFT JOIN q q_prev
ON main.dq_org_dist_id = q_prev.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_prev.dq_rec_seq_nbr + 1
ORDER By main.dq_src_id ,main.dq_org_dist_id, main.dq_rec_seq_nbr DESC, q_next.dq_rec_seq_nbr DESC
-
The ROW_NUMBER example provided by Eugene is exactly what you need.
_______________________________________________________________
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/ -
I have tried but not getting the proper result.
-
Can you post what you tried? Not sure what you mean by not the proper result.
_______________________________________________________________
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/ -
Just a shot in the dark because it is pretty unclear what you want for output.
SELECT main.dq_src_id,
main.dq_org_dist_id,
main.dq_rec_seq_nbr,
main.dq_status_cd,
q_prev.dq_status_cd AS dq_prev_status_cd,
ROW_NUMBER() over (partition by main.dq_org_dist_id order by main.dq_org_dist_id) as RowNum,
--NULL AS dq_cycle_cnt, -- this should be reset for each district and incremented at each new dq_rec_start_cycle_ind
CAST(CASE WHEN q_next.dq_rec_eff_dt IS NULL THEN 1 ELSE 0 END AS BIT) AS dq_rec_latest_ind,
CAST(CASE WHEN main.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_qualified_ind,
CAST(CASE WHEN q_prev.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_start_cycle_ind,
main.dq_rec_eff_dt,
COALESCE(q_next.dq_rec_eff_dt,'2079-06-06') AS dq_rec_end_dt,
DATEDIFF(day,main.dq_rec_eff_dt,q_next.dq_rec_eff_dt) AS dq_duration,
main.dq_rec_upd_user
FROM q main
LEFT JOIN q q_next
ON main.dq_org_dist_id = q_next.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_next.dq_rec_seq_nbr - 1
LEFT JOIN q q_prev
ON main.dq_org_dist_id = q_prev.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_prev.dq_rec_seq_nbr + 1
ORDER By main.dq_src_id ,main.dq_org_dist_id, main.dq_rec_seq_nbr DESC, q_next.dq_rec_seq_nbr DESC
_______________________________________________________________
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/ -
this should be reset for each district and incremented at each new dq_rec_start_cycle_ind
If I understand you correctly, you want something like:
district | dq_rec_start_cycle_ind | cycle_count
1 | 1 | 1
1 | 1 | 1
1 | 0 | 2 -- indicator changed, count increased
1 | 0 | 2
1 | 0 | 2
1 | 1 | 3 -- indicator changed, count increased
2 | 0 | 1 -- reset per district
2 | 0 | 1
etc.
If I'm right, looks like the best option would be using "calculating running totals" method. You should be able to find article written by Jeff Moden on this site.
-
Hi,
Yes i want data as like you have mentioned..So can you help me how can I impliment in my below sql,
WITH q AS (SELECT
dq_src_id
,dq_org_dist_id
,dq_rec_seq_nbr
,dq_status_cd
,dq_prev_status_cd
,dq_curr_status
,dq_rec_eff_dt
,dq_rec_upd_user
FROM tmp_dist
WHERE dq_org_dist_id IN('1130', '1450')
)
SELECT main.dq_src_id,
main.dq_org_dist_id,
main.dq_rec_seq_nbr,
main.dq_status_cd,
q_prev.dq_status_cd AS dq_prev_status_cd,
NULL AS dq_cycle_cnt, -- this should be reset for each district and incremented at each new dq_rec_start_cycle_ind
CAST(CASE WHEN q_next.dq_rec_eff_dt IS NULL THEN 1 ELSE 0 END AS BIT) AS dq_rec_latest_ind,
CAST(CASE WHEN main.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_qualified_ind,
CAST(CASE WHEN q_prev.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_start_cycle_ind,
main.dq_rec_eff_dt,
COALESCE(q_next.dq_rec_eff_dt,'2079-06-06') AS dq_rec_end_dt,
DATEDIFF(day,main.dq_rec_eff_dt,q_next.dq_rec_eff_dt) AS dq_duration,
main.dq_rec_upd_user
FROM q main
LEFT JOIN q q_next
ON main.dq_org_dist_id = q_next.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_next.dq_rec_seq_nbr - 1
LEFT JOIN q q_prev
ON main.dq_org_dist_id = q_prev.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_prev.dq_rec_seq_nbr + 1
ORDER By main.dq_src_id,main.dq_rec_seq_nbr DESC, q_next.dq_rec_seq_nbr DESC
-
hi,
My requirement is to show the value in the dq_cycle_cnt column on the basis of
the column dq_prev_status_cd i.e.,
when the dq_org_dist_id first value would be null or Draft will be
1 and the next values would be on the basic of "Qualified" in the column dq_prev_status_cd
or 1 in the column dq_rec_start_cycle_ind.
The values in the column dq_cycle_cnt will be increment
on the basis of the value "Qualified"
in the column dq_prev_status_cd or if values 1 in the column dq_rec_start_cycle_ind
I have attached excel sheet as well posted my SQL.
Please look in the excel sheet columns,
dq_org_dist_id,dq_rec_seq_nbr,dq_prev_status_cd and dq_rec_start_cycle_ind
means you will get the idea what I want to need.
My sql ,
WITH q AS (SELECT
dq_src_id
,dq_org_dist_id
,dq_rec_seq_nbr
,dq_status_cd
,dq_prev_status_cd
,dq_curr_status
,dq_rec_eff_dt
,dq_rec_upd_user
FROM tmp_dist
WHERE dq_org_dist_id IN('1130', '1450')
)
SELECT main.dq_src_id,
main.dq_org_dist_id,
main.dq_rec_seq_nbr,
main.dq_status_cd,
q_prev.dq_status_cd AS dq_prev_status_cd,
NULL AS dq_cycle_cnt, -- this should be reset for each district and incremented at each new dq_rec_start_cycle_ind
CAST(CASE WHEN q_next.dq_rec_eff_dt IS NULL THEN 1 ELSE 0 END AS BIT) AS dq_rec_latest_ind,
CAST(CASE WHEN main.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_qualified_ind,
CAST(CASE WHEN q_prev.dq_status_cd = 'Qualified' THEN 1 ELSE 0 END AS BIT) AS dq_rec_start_cycle_ind,
main.dq_rec_eff_dt,
COALESCE(q_next.dq_rec_eff_dt,'2079-06-06') AS dq_rec_end_dt,
DATEDIFF(day,main.dq_rec_eff_dt,q_next.dq_rec_eff_dt) AS dq_duration,
main.dq_rec_upd_user
FROM q main
LEFT JOIN q q_next
ON main.dq_org_dist_id = q_next.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_next.dq_rec_seq_nbr - 1
LEFT JOIN q q_prev
ON main.dq_org_dist_id = q_prev.dq_org_dist_id
AND main.dq_rec_seq_nbr = q_prev.dq_rec_seq_nbr + 1
ORDER By main.dq_src_id,main.dq_rec_seq_nbr DESC, q_next.dq_rec_seq_nbr DESC
My DDL and Insert scriprs are as above
So request you to please provide me the logic how to implement in my mentioned SQL.
-
Try this
Select
dq_src_id
dq_org_dist_id,
dq_rec_seq_nbr,
dq_status_cd,
dq_prev_status_cd,
dq_curr_status,
dq_rec_eff_dt,
dq_rec_upd_user,
rank() over (partition by dq_org_dist_id Order By dq_src_id Desc) as Rank
From
tmp_dist
Order By
dq_org_dist_id
-
its just geting the rank i have added what I want means my requirment in details
-
See if this works
DECLARE @t TABLE(dq_org_dist_id INT, dq_rec_seq_nbr INT, dq_rec_start_cycle_ind INT)INSERT INTO @t(dq_org_dist_id, dq_rec_seq_nbr, dq_rec_start_cycle_ind)
SELECT 1130, 22, 0 UNION ALL
SELECT 1130, 21, 1 UNION ALL
SELECT 1130, 20, 0 UNION ALL
SELECT 1130, 19, 0 UNION ALL
SELECT 1130, 18, 0 UNION ALL
SELECT 1130, 17, 1 UNION ALL
SELECT 1130, 16, 0 UNION ALL
SELECT 1130, 15, 0 UNION ALL
SELECT 1130, 14, 0 UNION ALL
SELECT 1130, 13, 0 UNION ALL
SELECT 1130, 12, 0 UNION ALL
SELECT 1130, 11, 1 UNION ALL
SELECT 1130, 10, 0 UNION ALL
SELECT 1130, 9, 0 UNION ALL
SELECT 1130, 8, 0 UNION ALL
SELECT 1130, 7, 0 UNION ALL
SELECT 1130, 6, 0 UNION ALL
SELECT 1130, 5, 1 UNION ALL
SELECT 1130, 4, 0 UNION ALL
SELECT 1130, 3, 1 UNION ALL
SELECT 1130, 2, 0 UNION ALL
SELECT 1130, 1, 0 UNION ALL
SELECT 1450, 5, 0 UNION ALL
SELECT 1450, 4, 1 UNION ALL
SELECT 1450, 3, 1 UNION ALL
SELECT 1450, 2, 0 UNION ALL
SELECT 1450, 1, 0;
WITH CTE AS (
SELECT dq_org_dist_id, dq_rec_seq_nbr, dq_rec_start_cycle_ind,
ROW_NUMBER() OVER(PARTITION BY dq_org_dist_id ORDER BY dq_rec_seq_nbr) AS rn1,
ROW_NUMBER() OVER(PARTITION BY dq_org_dist_id,dq_rec_start_cycle_ind ORDER BY dq_rec_seq_nbr) AS rn2
FROM @t)
SELECT dq_org_dist_id, dq_rec_seq_nbr, dq_rec_start_cycle_ind,
CASE WHEN dq_rec_start_cycle_ind=0 THEN rn1-rn2+1 ELSE rn2+1 END AS dq_cycle_cnt
FROM CTE
ORDER BY dq_org_dist_id,dq_rec_seq_nbr;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Ya its working but I have many district so what can I do? and I want to show all columns as I have mentioned in my SQL.
So please do the need full Sir
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply