April 9, 2009 at 11:56 am
I need help with this script.... (I'm running SQL 2005)
Currently it displays the count of all the Best Practices (dbo.best_practice_version.bp_type_id = 10)
that are current (dbo.best_practice_version.status_code_id = 5) that became effective in the month of Dec 2005.
I would like it to also give me the Best Practice is Draft (status_code_id = 4) and Bestpractice is Archive (status_code_id = 6) and the same for Work Instructions (best_practice_version.bp_type_id = 11)
for every month from dec 2005 to current.
Any help would much appreciated!!!
SELECT count(*)
FROM dbo.best_practice_version
where dbo.best_practice_version.status_code_id = 5
and dbo.best_practice_version.bp_type_id = 10
and effective_date between '12/1/2005' and '12/31/2005'
April 9, 2009 at 12:07 pm
If I'm understanding your requirements correctly, you want this:
SELECT count(*), status_code_id, bp_type_id, dateadd(mm, datediff(mm,0, effective_date),0) AS EffectiveMonth
FROM dbo.best_practice_version
WHERE dbo.best_practice_version.status_code_id IN (5, 4)
AND dbo.best_practice_version.bp_type_id IN (10, 6, 11)
AND effective_date between '12/1/2005' and GETDATE()
GROUP BY status_code_id,
bp_type_id,
dateadd(mm, datediff(mm,0, effective_date),0)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2009 at 1:11 pm
Yes!!! Thank you
April 9, 2009 at 1:46 pm
how would I remove the seconds from the date so it just displays the mm/dd/yyyy?
April 9, 2009 at 1:55 pm
To do that, you'd need to cast the date to a varchar. Typically this is a presentation issue and shouldn't be done if this data is going to be inserted into another table or used for further processing. It should be done either by the presentation layer or just before returning the data to the presentation layer.
SELECT count(*), status_code_id, bp_type_id, CONVERT(VARCHAR(20),dateadd(mm, datediff(mm,0, effective_date),0),101) AS EffectiveMonth
FROM dbo.best_practice_version
WHERE dbo.best_practice_version.status_code_id IN (5, 4)
AND dbo.best_practice_version.bp_type_id IN (10, 6, 11)
AND effective_date between '12/1/2005' and GETDATE()
GROUP BY status_code_id,
bp_type_id,
dateadd(mm, datediff(mm,0, effective_date),0)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply