September 1, 2015 at 2:20 am
Can anyone help withthis update query.
I have a stored proceedure that generates this table for me
SITEID TYPE JAN FEB MAR APR MAY JUN JUL (etc for a 5 year span)
ABC B 1 1 1 0 0 0 0
ABC LC 0 0 0 1 1 0 0
ABC P 0 B 0 0 0 1 1
ABC XX 0 0 0 0 0 0 0
The last row for each site always has a Type of xx. All the grid datatypes are varchar(3).
What I am trying to do is update the xx row with the type of the previous columns, if the previous colums are set to 1. Sort of tlike this.
SITEID TYPE JAN FEB MAR APR MAY JUN JUL (etc for a 5 year span)
ABC B 1 1 1 0 0 0 0
ABC LC 0 0 0 1 1 0 0
ABC P 0 B 0 0 0 1 1
ABC XX B B B LC LC P P
I've managed to do it by using loads of update queries
eg
UPDATE T1 SET T1.JAN = CASE T2.JAN WHEN 1 THEN 'B' END FROM #FINALGRID T1 inner join #FINALGRID T2 on T1.Site_ID = T2.Site_ID and T1.Type = 'xx' and T2.Type = 'B'
UPDATE T1 SET T1.FEB = CASE T2.FEB WHEN 1 THEN 'B' END FROM #FINALGRID T1 inner join #FINALGRID T2 on T1.Site_ID = T2.Site_ID and T1.Type = 'xx' and T2.Type = 'B'
UPDATE T1 SET T1.MAR = CASE T2.MAR WHEN 1 THEN 'B' END FROM #FINALGRID T1 inner join #FINALGRID T2 on T1.Site_ID = T2.Site_ID and T1.Type = 'xx' and T2.Type = 'B'
UPDATE T1 SET T1.APR = CASE T2.APR WHEN 1 THEN 'B' END FROM #FINALGRID T1 inner join #FINALGRID T2 on T1.Site_ID = T2.Site_ID and T1.Type = 'xx' and T2.Type = 'B'
UPDATE T1 SET T1.MAY = CASE T2.MAY WHEN 1 THEN 'B' END FROM #FINALGRID T1 inner join #FINALGRID T2 on T1.Site_ID = T2.Site_ID and T1.Type = 'xx' and T2.Type = 'B'
UPDATE T1 SET T1.JUN = CASE T2.JUN WHEN 1 THEN 'B' END FROM #FINALGRID T1 inner join #FINALGRID T2 on T1.Site_ID = T2.Site_ID and T1.Type = 'xx' and T2.Type = 'B'
But I am sure there must be a more efficient way
I would appreciate any help, or pointers to some help
Thank You.
September 1, 2015 at 2:41 am
Hi Debbie,
I think that the problem is that you shouldn't need to update anything that is in this format. I can understand, that you create some report or whatever it is the procedure gives you, but then it should be finished and not requiring any more updates. Even the table is called finalsomething 🙂
It is much easier to do the computing while data is not yet in the final "display" format. I would recommend to make this update part of the procedure, and not as something that is done afterwards with the result. Would this be possible?
September 1, 2015 at 2:51 am
Vladan (9/1/2015)
Hi Debbie,I think that the problem is that you shouldn't need to update anything that is in this format. I can understand, that you create some report or whatever it is the procedure gives you, but then it should be finished and not requiring any more updates. Even the table is called finalsomething 🙂
It is much easier to do the computing while data is not yet in the final "display" format. I would recommend to make this update part of the procedure, and not as something that is done afterwards with the result. Would this be possible?
Good point – I agree with this, in principle, assuming that there are no other reasons not to change the proc.
But the problem still needs to be solved, regardless of where the code goes.
Can I ask whether there is ever a case where a month has a '1' for more than one 'Type' within the same SiteId?
If the answer is no, the ordering of the rows becomes unimportant and that makes this potentially easier to solve.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 1, 2015 at 3:02 am
Yes, but only in the case of Type "LC", this value can overwrite all otheres. All the other types are in consecutive months.
September 1, 2015 at 3:15 am
debbie.coates 19265 (9/1/2015)
Yes, but only in the case of Type "LC", this value can overwrite all otheres. All the other types are in consecutive months.
Can you give us some more test data which shows this scenario please?
Also, I do not understand why you have mentioned consecutive months? My question was referring only to the same month, for different 'Type' values.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 1, 2015 at 3:18 am
Hi,
the fact that several rows of the same siteID can have '1' in a column complicates it... here is some simple code that I wrote before I knew of this, so it doesn't work properly in case of multiple '1'. It also does not do the update, just seletcs the vaues for XX row (so it is easy to check, whether the result is OK). You can easily change it into update, of course :
CREATE TABLE #data(SITEID VARCHAR(3), TYPE VARCHAR(3), JAN VARCHAR(3),FEB VARCHAR(3),
MAR VARCHAR(3),APR VARCHAR(3),MAY VARCHAR(3),JUN VARCHAR(3),JUL VARCHAR(3))
INSERT INTO #data
SELECT 'ABC','B','1','1','1','0','0','0','0'
UNION SELECT 'ABC','LC','0','0','0','1','1','0','0'
UNION SELECT 'ABC','P','0','B','0','0','0','1','1'
SELECT Q.siteid, 'XX', MAX(Q.jan) as jan, MAX(Q.feb) as feb, MAX(Q.mar) as mar, MAX(Q.apr) as apr,
MAX(Q.may) as may, MAX(Q.jun) as jun, MAX(Q.jul) as jul
FROM (SELECT siteid, type
, NULLIF(replace(jan,'1',type),'0') as jan
, NULLIF(replace(feb,'1',type),'0') as feb
, NULLIF(replace(mar,'1',type),'0') as mar
, NULLIF(replace(apr,'1',type),'0') as apr
, NULLIF(replace(apr,'1',type),'0') as may
, NULLIF(replace(jun,'1',type),'0') as jun
, NULLIF(replace(jul,'1',type),'0') as jul
FROM #data d) as Q
GROUP BY Q.siteid
September 1, 2015 at 3:21 am
Phil,
i may be mistaken, but I understand the reply with "consecutive months" precisely in the way you asked the question 🙂 that is, that there is no overlapping except for 'LC', which can appear in the same month as something else and in that case LC has priority.
September 1, 2015 at 3:25 am
Thats exactly right, LC has the priority, I just ran your script and tht is doing exactly what I am trying to do.
September 1, 2015 at 3:25 am
Vladan (9/1/2015)
Phil,i may be mistaken, but I understand the reply with "consecutive months" precisely in the way you asked the question 🙂 that is, that there is no overlapping except for 'LC', which can appear in the same month as something else and in that case LC has priority.
We'll have to wait for Debbie on that one. To me, 'consecutive months' means Jan, Feb, Mar, Apr ... and I do not see any relevance to it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 1, 2015 at 3:30 am
debbie.coates 19265 (9/1/2015)
Thats exactly right, LC has the priority, I just ran your script and tht is doing exactly what I am trying to do.
I think not. This code gives no priority to LC. It works because there is no overlap.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 1, 2015 at 3:32 am
Well, I'm glad it does approximately what you need, but it does not work properly in case where for the same siteID and month you have several rows - in that case it simply chooses the row with highest value of string (i.e. P would win over LC).
And there is one more thing... in the sample data, you have value 'B' in FEB column ... isn't that a mistake? As I understood it, the motnh columns should only have values 1 or 0...?
September 1, 2015 at 4:45 am
Oh right, so i just need to work out a way to prioritise now.
September 1, 2015 at 5:27 am
Hm... and what about the 'B' in column FEB ? Is that a typo or correct value?
In case it is correct value, you need to specify what it means... for the present, I will suppose that only value '1' means that the current type is "active" for the month, any other value means it is not.
Then it would be possible to rewrite the query this way (it depends on the fact that TYPE will never be 'ZZZ' ... I wouldn't like to suppose that in my database, but also I would try to solve this problem before data acquire such unpleasant format) :
SELECT Q.siteid, 'XX',
CASE WHEN MAX(Q.jan)='ZZZ' THEN 'LC' ELSE MAX(Q.jan) END as jan,
CASE WHEN MAX(Q.feb)='ZZZ' THEN 'LC' ELSE MAX(Q.feb) END as feb,
CASE WHEN MAX(Q.mar)='ZZZ' THEN 'LC' ELSE MAX(Q.mar) END as mar,
CASE WHEN MAX(Q.apr)='ZZZ' THEN 'LC' ELSE MAX(Q.apr) END as apr,
CASE WHEN MAX(Q.may)='ZZZ' THEN 'LC' ELSE MAX(Q.may) END as may,
CASE WHEN MAX(Q.jun)='ZZZ' THEN 'LC' ELSE MAX(Q.jun) END as jun,
CASE WHEN MAX(Q.jul)='ZZZ' THEN 'LC' ELSE MAX(Q.jul) END as jul
FROM (SELECT siteid, type
, CASE WHEN jan='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as jan
, CASE WHEN feb='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as feb
, CASE WHEN mar='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as mar
, CASE WHEN apr='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as apr
, CASE WHEN may='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as may
, CASE WHEN jun='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as jun
, CASE WHEN jul='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as jul
FROM #data d) as Q
GROUP BY Q.siteid
As I said, this solution is far from perfect, but it does what you were asking for and it should work. You know more about your data, so you may be able to come up with something similar, but better suited for your environment.
Good luck!
September 1, 2015 at 6:48 am
sorry, Yes it was a Typo, only the value's 1 and 0 will be in the Grid
September 1, 2015 at 7:19 am
I have just been testing your solution, I've fitted it around my data and it work brilliant. I cant thank you enough for all your help, I am so grateful
Debbie
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply