transact sql
-
Hello,
I have a sql table used for tasking; each record in the table represents a task. Each record has jan - dec columns representing the month(s) the tasks will be performed.
- I need to determine the tasks frequency by quantifying how many of the jan - dec columns have an X (or value) in them:
- 1 X = annual task
- 3 Xs = trimonthly task
- 4 Xs = quarterly task
- 6 Xs = bimonthly
- 12 Xs = monthly task
- Additionally, I will need to determine in which field (jan - dec) the first x resides. This determines the tasks schedule.
Thanks in advance for any assistance provided.
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec -
Hmmm ... one way would be to create a calculated field on your table - call it 'concat', say, which is the concatenation of all twelve fields, replacing all nulls with spaces:
Formula for concat:
isnull(f1,' ') + isnull(f2, ' ') + ,..., + isnull(f12, ' ')
Then your queries become relatively straightforward. The following example demonstrates a partial solution (using a string variable in place of the calculated field).
declare @concat varchar(12)
set @concat = ' x x x ' --(replace this with the calculated field)
select (len(@concat) - len(ltrim(@concat)) + 1) FirstX
select len(replace(@concat, ' ', '')) FreqNumber
If you need to explicitly return 'annual', 'trimonthly' etc from the query, you will have to expand the above to use the CASE construct.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I used #temp_output as my table to test my sql. Hopefully this will help.
create table #temp_output
(id integer,
jan varchar(1),
feb varchar(1),
mar varchar(1),
apr varchar(1),
may varchar(1),
jun varchar(1),
jul varchar(1)
)
select id, SUM (CASE when Jan = 'X' then 1 ELSE 0 END +
CASE when feb = 'X' then 1 ELSE 0 END +
CASE when mar = 'X' then 1 ELSE 0 END +
CASE when apr = 'X' then 1 ELSE 0 END +
CASE when may = 'X' then 1 ELSE 0 END +
CASE when jun = 'X' then 1 ELSE 0 END +
CASE when jul = 'X' then 1 ELSE 0 END
 
Total_Count,COALESCE (CASE when Jan = 'X' then 'Jan' ELSE NULL END,
CASE when feb = 'X' then 'Feb' ELSE NULL END ,
CASE when mar = 'X' then 'Mar' ELSE NULL END ,
CASE when apr = 'X' then 'Apr' ELSE NULL END ,
CASE when may = 'X' then 'May' ELSE NULL END ,
CASE when jun = 'X' then 'Jun' ELSE NULL END ,
CASE when jul = 'X' then 'Jul' ELSE NULL END
 
first_month,CASE SUM (CASE when Jan = 'X' then 1 ELSE 0 END +
CASE when feb = 'X' then 1 ELSE 0 END +
CASE when mar = 'X' then 1 ELSE 0 END +
CASE when apr = 'X' then 1 ELSE 0 END +
CASE when may = 'X' then 1 ELSE 0 END +
CASE when jun = 'X' then 1 ELSE 0 END +
CASE when jul = 'X' then 1 ELSE 0 END
 
WHEN 1 THEN 'Annual Task'
WHEN 3 THEN 'Trimonthly Task'
WHEN 4 THEN 'Quarterly Task'
WHEN 6 THEN 'Bimonthly'
WHEN 12 THEN 'Monthly Task'
END task_type
from #temp_output
group by id, COALESCE (CASE when Jan = 'X' then 'Jan' ELSE NULL END,
CASE when feb = 'X' then 'Feb' ELSE NULL END ,
CASE when mar = 'X' then 'Mar' ELSE NULL END ,
CASE when apr = 'X' then 'Apr' ELSE NULL END ,
CASE when may = 'X' then 'May' ELSE NULL END ,
CASE when jun = 'X' then 'Jun' ELSE NULL END ,
CASE when jul = 'X' then 'Jul' ELSE NULL END
 
-
I've already worked with a similar table design... but for weekdays (sunday to saturday). The table kept the days we visited a client for a given week. I found out that working with a normalized table greatly helped on almost all queries I had to perform on that table.
Might I suggest a new table to be added as such :
create table JobsSchedule(
JobId int not null
JobMonth tinyint not null,
CONSTRAINT [PK_JobsSchedule] PRIMARY KEY CLUSTERED
(
JobId, JobMonth
) ON [PRIMARY] ,
CONSTRAINT [CK_JobsSchedule_JobMonth] CHECK ([JobMonth] > 0 and [JobMonth] < 12)
Then your 2 queries become a piece of cake.
Select Min(month) from dbo.JobsSchedule where JobId = ?
Select case count(*)
when 1 then 'annual'
when 3 then '...'
end as JobFrequency
from dbo.JobsSchedule where JobId = ?
Of course you need a trigger to keep both tables synchronised at all times. Maybe you don't really need to normalize the model to that point but I know it saved me a lot of efforts.
-
Thank you all for your input, I will try these and see which works best.
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply