July 14, 2010 at 9:52 am
Need help to find the count between start date and end date.
Below query shows only count in Y2006 year, i want to display each year count 1, because start date and end date is valid for each year
CREATE TABLE [dbo].[T1](
[STRTDATE] [datetime] NOT NULL,
[ENDDATE] [datetime] NOT NULL
)
INSERT INTO T1
SELECT '2006-03-09', '2011-03-08'
SELECT
SUM(CASE WHEN STRTDATE >= '2006-01-01' AND STRTDATE <= '2006-12-31' THEN 1 ELSE 0 END) AS Y2006,
SUM(CASE WHEN STRTDATE >= '2007-01-01' AND STRTDATE <= '2007-12-31' THEN 1 ELSE 0 END) AS Y2007,
SUM(CASE WHEN STRTDATE >= '2008-01-01' AND STRTDATE <= '2008-12-31' THEN 1 ELSE 0 END) AS Y2008,
SUM(CASE WHEN STRTDATE >= '2009-01-01' AND STRTDATE <= '2009-12-31' THEN 1 ELSE 0 END) AS Y2009,
SUM(CASE WHEN STRTDATE >= '2010-01-01' AND STRTDATE <= '2010-12-31' THEN 1 ELSE 0 END) AS Y2010,
SUM(CASE WHEN STRTDATE >= '2011-01-01' AND STRTDATE <= '2011-12-31' THEN 1 ELSE 0 END) AS Y2010
FROM T1
July 14, 2010 at 9:58 am
try:
SELECT
SUM(CASE WHEN 2006 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2006,
SUM(CASE WHEN 2007 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2007,
SUM(CASE WHEN 2008 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2008,
SUM(CASE WHEN 2009 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2009,
SUM(CASE WHEN 2010 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2010,
SUM(CASE WHEN 2011 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2011,
SUM(CASE WHEN 2005 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2005,
SUM(CASE WHEN 2012 BETWEEN YEAR(STRTDATE) AND YEAR(ENDDATE) THEN 1 ELSE 0 END) AS Y2012
FROM T1
I have also added 2005 and 2012, to show that they are not counted!
July 14, 2010 at 10:03 am
Thank you so much for the help.
July 14, 2010 at 10:24 am
July 14, 2010 at 11:48 am
The problem I have with the case statements is that they're a bit ugly to work with (especially when you get more and more) and they're not dynamic. So, in this example the query may work fine until 2012 but if someone forgets to update the query and add more years it will eventually fail in terms of accuracy. I would suggest using a tally table for this kind of operation.
--If you don't already have a tally table in place ...
;WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),--10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)
--actual query
select t.STRTDATE,
t.ENDDATE,
'Y' + CAST(N as varchar) as theYear
from #temp t
join cteTally cte
on cte.N between year(t.STRTDATE) and year(t.ENDDATE)
July 14, 2010 at 11:53 am
And not sure if you ultimately just want the count of the years which would be:
--actual query
select sq.STRTDATE,
sq.ENDDATE,
COUNT(sq.myYear) as yearCount
from
(
select t.STRTDATE,
t.ENDDATE,
'Y' + CAST(N as varchar) as myYear
from #temp t
join cteTally cte
on cte.N between year(t.STRTDATE) and year(t.ENDDATE)
) sq
group by sq.STRTDATE, sq.ENDDATE
July 14, 2010 at 12:06 pm
bteraberry (7/14/2010)
The problem I have with the case statements is that they're a bit ugly to work with (especially when you get more and more) and they're not dynamic...
1. We are not afraid of "dirty" work here 😀
2. The task given looks like just an one-off query, for which I don't thing you need to over engineer solution.
3. OP wanted pivoted result.
July 14, 2010 at 12:19 pm
OFF-TOPIC:
2. The task given looks like just an one-off query, for which I don't thing you need to over engineer solution.
Wow, that's a STUNNINGLY HUGE!!!! change from your responses here:
http://qa.sqlservercentral.com/Forums/Topic936326-338-1.aspx?Highlight=studid
And that was an actual ONE-TIME situation.
This q looks likely to be an on-going situation.
Scott Pletcher, SQL Server MVP 2008-2010
July 14, 2010 at 12:21 pm
(edit: This is in response to Euguene.)
I think you're being a bit tongue-in-cheek, but still ...
Part of the thing that makes this site such a good resource is the number of experienced professionals who engage in the discussion to the point that provided solutions aren't just ok, but they're solid and thoughtful. If you think using a tally table is "over-engineering" I would have to disagree with you strenuously. The use of tally tables should be a pretty regular occurrence for anyone who writes highly optimized T-SQL code.
Now it may be that this is a one-off. It may be that the OP wanted a quick pivot. But since those ideas were not expressed but merely assumed, I think it's better to offer a more optimized solution. If readers choose to take a "simpler path" that's fine.
July 14, 2010 at 1:04 pm
Thank you both for the solution.
Actual requirement is I need to find Start Date and End Date difference between these 2 dates and see which year it will fall each item.
3 examples.
ITEMNo of Days
1 485
2 364
3 1051
1st item will fall in 2006 & 2007 year
2nd item will fall in only 2006 year because it is year or less than year.
3rd item will fall in 2006, 2007 & 2008
Any suggestions will be greatly helpful.
CREATE TABLE [dbo].[T1](
[ITEM] [char](15) NOT NULL,
[STRTDATE] [datetime] NOT NULL,
[ENDDATE] [datetime] NOT NULL
)
INSERT INTO T1
SELECT '1', '2006-01-01', '2007-05-01'
INSERT INTO T1
SELECT '2', '2006-04-01', '2007-03-31'
INSERT INTO T1
SELECT '3', '2006-04-01', '2009-02-15'
SELECT ITEM, DATEDIFF(DAY, STRTDATE, ENDDATE) FROM T1
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply