June 16, 2008 at 12:10 pm
I have a table with column days(varchar(255),null) which has data like
1;2;3;4;11;18;17;10;9;8;7;14;15;16;21;22;23;24;25;30;29;28.
these are the days in a month.
If i am given a date say "9" how can i count the no of days left after 9th and if possible what are those days.
June 16, 2008 at 12:18 pm
Mike - when you say "after" 9, do you mean after the "9" element in the set? or after the nine in the ORDERED version of the set?
As in:
1;2;3;4;11;18;17;10;9;8;7;14;15;16;21;22;23;24;25;30;29;28.
or
1;2;3;4;11;18;17;10;9;8;7;14;15;16;21;22;23;24;25;30;29;28.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 12:23 pm
I looking for the second result you have given.
1;2;3;4;11;18;17;10;9;8;7;14;15;16;21;22;23;24;25;30;29;28.
How many days left after 9th from the given numbers.
that means for this particulat row the count is 15
June 16, 2008 at 12:46 pm
any help?
June 16, 2008 at 1:01 pm
First - yikes - this is quite the design. Change it if at all possible.
Barring getting away from this multi-value column, you need to split the data into a table to handle it properly. Search this site for Split functions - particularly looking for Jeff Moden's Tally Table split functions. This will allow you to get this data into a table format so you can simply count the records greater than 9.
June 16, 2008 at 1:02 pm
Your best bet will be to use something like the SPLIT described here...
http://www.sqlservercentral.com/articles/TSQL/62867/[/url]
(Edit: for what it's worth - it's the very article Michael is getting at).
That way you can easily run the query against the normalized data (even if you don't hold onto it past doing the calculation).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 1:30 pm
thanks guys..
i cant change the design. I just want the result as i need and doesnt mind if am using temp table or any long tsql query.
I am not sure how to get it done without changing the design. just looking for some tsql to do this job.
June 16, 2008 at 1:42 pm
Mike Levan (6/16/2008)
thanks guys..i cant change the design. I just want the result as i need and doesnt mind if am using temp table or any long tsql query.
I am not sure how to get it done without changing the design. just looking for some tsql to do this job.
Then do it in a temp table, and run the count there. Except possibly for having a Tally table added, there's no permanent change to the design. Here's the direct example, modified to pull a grouped count of all items over 5:
--===== Create a sample denormalized table with a CSV column
CREATE TABLE #MyHead
(PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CsvColumn VARCHAR(500))
INSERT INTO #MyHead
SELECT '1,5,3,7,8,2' UNION ALL
SELECT '7,2,3,7,1,2,2' UNION ALL
SELECT '4,7,5' UNION ALL
SELECT '1' UNION ALL
SELECT '5' UNION ALL
SELECT '2,6' UNION ALL
SELECT '1,2,3,4,55,6'
--===== Split or "Normalize" the whole table at once
select pk, count(*)
from
(SELECT mh.PK,
SUBSTRING(','+mh.CsvColumn+',',N+1,CHARINDEX(',',','+mh.CsvColumn+',',N+1)-N-1) AS Value
FROM dbo.Tally t CROSS JOIN #MyHead mh
WHERE N < LEN(','+mh.CsvColumn+',') AND SUBSTRING(','+mh.CsvColumn+',',N,1) = ',') t
where value>5
group by pk
You haven't provided table structure, but you should be able literally transcribe with your table names.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 1:53 pm
this is my table structure
CREATE TABLE [dbo].[FAS_LaborCalendar](
[LC_Id] [int] IDENTITY(1,1) NOT NULL,
[ProgramInstanceId] [int] NOT NULL,
[FiscalPeriod] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HoursPerDay] [float] NOT NULL CONSTRAINT [DF_FAS_LaborCalendar_HoursPerDay] DEFAULT ((8)),
[NumberOfDays] [int] NOT NULL CONSTRAINT [DF_FAS_LaborCalendar_NumberOfDays] DEFAULT ((0)),
[SelectedDays] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_FAS_LaborCalendar] PRIMARY KEY CLUSTERED
(
[LC_Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
When i trid this i get an error
Msg 8116, Level 16, State 1, Line 15
Argument data type varchar is invalid for argument 2 of substring function.
June 16, 2008 at 1:59 pm
select pk, count(*)
from
(SELECT lc_id pk,
SUBSTRING(','+SelectedDays+',',N+1,
CHARINDEX(',',','+SelectedDays+',',N+1)-N-1) AS Value
FROM dbo.Tally t CROSS JOIN FAS_LaborCalendar mh
WHERE N < LEN(','+SelectedDays+',') AND SUBSTRING(','+SelectedDays+',',N,1) = ',') t
where value>9
group by pk
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 2:06 pm
thanks matt
I am not getting about Tally table and value N from your query.
June 16, 2008 at 2:08 pm
Tally table is basically the glue that makes it work. It's a utility table or sorts....
Take a look at the article. perfect good code on how to set one up.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 4:18 pm
I have a function where if i pass parameters (string,delimiter) it gives each row. something like
If Run this
select * from dbo.myfunction('1;2;3;4;11;10;9;8;7;14;15;16;17;18;25;24;23;22;21;28;30;29',';')
I get result as
1
2
3
4
11
10
9
8
7
14
15
16
17
18
25
24
23
22
21
28
30
29.
How Can i use this function and CTE's to do my job.
June 16, 2008 at 4:30 pm
If you're going to go for a function, consider passing it the value (i.e. the 9), and have it count the values above that. You've alrage for the table variable, so you would just need to run a
select @myReturn=count(*) from @mytablvar where value>@refValue
and return @myreturnvar.
You'll get better perf that way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 16, 2008 at 4:33 pm
but for the function i can pass string a parameter, how can i pass my select query so tht I can iterate through each row.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply