Case Statement 1

[IAMINFO]

Hello everyone as you can see in this case statement
my result set returns a row with 5 columns for the period of 4/4/2009 thru 4/11/2009

I need the next row to do the same for the period of 4/12/2009 thru 4/30/2009, not sure how to proceed, please help if you can thank you for reading this post.



SELECT Facility,Block,Cell_Number,
COUNT (CASE WHEN @@ROWCOUNT > 0 AND @@ROWCOUNT <=100 THEN 1 ELSE NULL END)AS [Days Housed]
,COUNT(CASE WHEN CellCount =1 AND DateHoused BETWEEN '4/4/2009' AND '4/11/2009' THEN 1 ELSE NULL END) AS [Single Celled]
,COUNT(CASE WHEN CellCount =2 AND DateHoused BETWEEN '4/4/2009' AND '4/11/2009' THEN 2 ELSE NULL END) AS [Double Celled]
,COUNT(CASE WHEN CellCount=3 AND DateHoused BETWEEN '4/4/2009' AND '4/11/2009' THEN 3 ELSE NULL END) AS [Triple Celled]
,COUNT(CASE WHEN CellCount =4 AND DateHoused BETWEEN '4/4/2009' AND '4/11/2009' THEN 4 ELSE NULL END) AS [Quad Celled]
,COUNT(CASE WHEN CellCount =5 AND DateHoused BETWEEN '4/4/2009' AND '4/11/2009' THEN 5 ELSE NULL END) AS [Five Celled]

INTO #cells1
FROM #Cells
Group By Cell_Number,Block,Facility

 

[markros]

1. What @@RowCount is doing here? Is it @@ROWCOUNT from the previous statement?

2. What is your SQL Server version?

3. Do you have where clause in your select statement to select only records for the DateHoused between 4/4/2009 and 4/11/2009
?

Essentially, if you need different data in the next record, you need to add where clause to the above select and add another one with UNION ALL with the different where clause. You also would not need to include period ranges in your case statements and only use CellCount.

PluralSight Learning Library

 

[Crowley16]

what I'd do is to first try to work out the column the value should belong to first, and then select from it.

For 2005, it'd be a cte, for 2000 it's be a derived table...

I'm assuming that since there's 7 days in the interval, you're looking for weeks. There's plenty of resources online for working out week numbers, just google.

once you've got this week number, you could add it to the group by and that should give you what you want.

p.s.

I'm also puzzled by the @@rowcount, and I'd change Five Celled to Quint Celled, but that's just me...

--------------------
Procrastinate Now!

 

[kjv1611]

Assuming I understand what you are looking for, I think this is more like what you want. Take a look:

[GREEN]--^^-------------------------
--CREATE THE TABLE
--^^-------------------------[/GREEN]
CREATE TABLE #cells1(
	 [Days Housed] [int] NOT NULL
	,[Single Celled] [int] NULL
	,[Double Celled] [int] NULL
	,[Triple Celled] [int] NULL
	,[Quad Celled] [int] NULL
	,[Five Celled] [int] NULL
)

[GREEN]--^^-------------------------
--INSERT YOUR DATA
--^^-------------------------[/GREEN]
INSERT INTO #cells1
SELECT    Facility,Block,Cell_Number,
           COUNT(CASE WHEN @@ROWCOUNT > 0 AND @@ROWCOUNT <=100 THEN 1 ELSE NULL END)AS [Days Housed]
          ,COUNT(CASE WHEN CellCount = 1 THEN 1 ELSE NULL END) AS [Single Celled]
          ,COUNT(CASE WHEN CellCount = 2 THEN 1 ELSE NULL END) AS [Double Celled]
          ,COUNT(CASE WHEN CellCount = 3 THEN 1 ELSE NULL END) AS [Triple Celled]
          ,COUNT(CASE WHEN CellCount = 4 THEN 1 ELSE NULL END) AS [Quad Celled]
          ,COUNT(CASE WHEN CellCount = 5 THEN 1 ELSE NULL END) AS [Five Celled]
FROM	#Cells
WHERE	DateHoused BETWEEN '4/4/2009' AND '4/11/2009'
Group By   Cell_Number,Block,Facility
UNION
SELECT    Facility,Block,Cell_Number,
           COUNT(CASE WHEN @@ROWCOUNT > 0 AND @@ROWCOUNT <=100 THEN 1 ELSE NULL END)AS [Days Housed]
          ,COUNT(CASE WHEN CellCount = 1 THEN 1 ELSE NULL END) AS [Single Celled]
          ,COUNT(CASE WHEN CellCount = 2 THEN 1 ELSE NULL END) AS [Double Celled]
          ,COUNT(CASE WHEN CellCount = 3 THEN 1 ELSE NULL END) AS [Triple Celled]
          ,COUNT(CASE WHEN CellCount = 4 THEN 1 ELSE NULL END) AS [Quad Celled]
          ,COUNT(CASE WHEN CellCount = 5 THEN 1 ELSE NULL END) AS [Five Celled]
FROM	#Cells
WHERE	DateHoused BETWEEN '4/12/2009' AND '4/30/2009'
Group By   Cell_Number,Block,Facility

 

[kjv1611]

Oh, and for consistency, you might want to change Five Celled to Pent Celled. Sounds funny to me, but it matches your other fields better, in context.

 

[markros]

Yes, that's what I meant. Just change UNION to UNION ALL since there is 0 chance of having data duplicated.

PluralSight Learning Library

 

[kjv1611]

I always forget where you can use UNION ALL. I know in some cases, where I've tried to use that, the ALL part caused the statement to error out. But if it works, go for it - in some cases, as markos mentinos, it is best or possibly necessary to use it.

 

[markros]

No, omitting ALL clause could have caused the error. With ALL clause you should not have an error.

Take a look at this nice WiKi page explaining the difference between the two:

http://wiki.lessthandot.com/index.php/Union_All

PluralSight Learning Library

 

[kjv1611]

Thanks for the link, markros, I'll take a look.

I do remember some isntances where UNION ALL is not appropriate, and others where it is. It wasn't a syntax issue for sure, but I don't remember what it is.

I THINK it's just when you're creating a table, and inserting multiple values, not from a table source... so like this:

CREATE TABLE #MyTable([MyDate] [datetime] NOT NULL)
INSERT INTO #MyTable
SELECT '1/1/2010'
UNION
SELECT '1/2/2010'
UNION
SELECT '1/3/2010'
UNION
SELECT '1'4'2010'

That might not have been it, but I think it was... Regardless, I'll take a look at the given reference. I'm sure I've got LOTS more to learn!

 

[kjv1611]

Yeah, just looked. And the text there implies, just like I thought - sometimes you DO want it, and sometimes you don't.

So, if you want "the dups", then use the ALL - "give me all the stuff", and when you DON'T want the dups, then don't use the ALL. I remember that now. That's a good concise wiki doc, so far. I didn't read the whole thing yet, just the first couple lines.. and it reminded me enough of the diff between the 2.