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
-
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply