SQL Server Performance Forum – Threads Archive
problem with count function (dynamically)-urgent
Hi , i below is the query i wroe to find the count of "irn" and "groupid" which grouped by date.This date i have given from the current tue to next tue.If there is any count for a date other than tue which is in between this tue and next tue, i shud add that to present tue ‘s count.Please let me know how to do it. E.G 1WkUOP2006-03-21 00:00:00.0001433451971WkUOP2006-03-28 00:00:00.000177227645
now here i want to add the count 17722 to 21 as 28 is not tue and is in between this tue and next tue.Please tell me how to do that. i get these tue’s from a stored proc which is sql server side.
here is the coed i wrote…
declare @str varchar(7000) select @str=’ select * from openquery (onlinedatamart,
”SELECT
W1.Weeks_Out,
””UOP”” as Company,
W1.COURSE_START_UOP,
count(distinct W1.irn),
count(distinct W1.group_id) FROM ONLINEDM.PERSON p,
ONLINEDM.STUDENT_SCHEDULED s,
(
select
p.irn,
s.group_id,
””1Wk”” AS Weeks_Out,
s.course_off_start_date AS COURSE_START_UOP From
onlinedm.person P,
onlinedm.student_scheduled s
where
p.irn=s.irn
and s.course_off_start_date between ””’[email protected]+””’
AND ””’[email protected]+””’
AND s.course_status_type <> ””DR””
AND s.site_code in (””DOC””,””DON””, ””SF””)
)W1 Where
p.irn=s.irn
and p.irn=W1.irn
and s.group_id=W1.group_id
GROUP BY W1.COURSE_START_UOP order by W1.COURSE_START_UOP ”)’
–print (@str)
exec (@str)
Try using a CASE statement within your SELECT statement: DECLARE @t AS numeric
SET @t = (SELECT count(distinct Your_Table_Name.Your_Column_Name) FROM Your_Table_Name) SELECT ‘Your_column_name2’ =
CASE WHEN your_column_name2 = ’21’
THEN (@t + 21)
END
FROM your_table_name2 – Tahsin
More about Dynamic SAL
http://www.sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail
]]>