Quarterly data IS NULL

# Thread: Quarterly data IS NULL

1. Member
Join Date
Dec 1969
Posts
70

## Quarterly data IS NULL

I am calculating running year totals for quarterly data. When each column contains data, there is no problem, eg:<BR><BR>QuarterID--QuarterlySales--YearEnded<BR>1--150--150 (=1)<BR>2--400--600 (=2+1)<BR>3--100--700 (=3+2+1)<BR>4--200--900 (=4+3+2+1)<BR>5--130--830 (=5+4+3+2)<BR>6--200--630 (=6+5+4+3)<BR>etc..<BR><BR>I have quarterID data from quarterID 1 to 23 (=6 years of data).<BR><BR>The problem occurs if one of the quarter data is NULL eg:<BR><BR>QuarterID--QuarterlySales--YearEnded<BR>1--150--150 (=1)<BR>2--400--600 (=2+1)<BR>3--100--700 (=3+2+1)<BR>4--NULL--900 (calculating breaks and does not give 4+3+2+1)<BR>5--130--630 (=5+4+3+2) --works fine<BR>6--200--430 (=6+5+4+3) --works fine<BR>etc..<BR><BR>To get the YearEnded figures I join the table to itself and do the following calculation (table aliases are T1 and T2)<BR><BR>....(SELECT........WHERE T1.QuarterID BETWEEN T2.QuarterID-3 AND T2.QuarterID) YearEnded<BR><BR>This of course breaks if one quarter IS NULL because it cannot do a NULL-3. This all needs to be calculated dynamically, not hardcoding quarterIDs.<BR><BR>How do I get around this? Ideas appreciated.

2. Senior Member
Join Date
Dec 1969
Posts
3,195

QuarterlySales field as NULL, yet your SELECT statement is checking &#039;QuarterId-3&#039;. <BR><BR>So which is it? ; )<BR><BR>If it is quarterId that is NULL then you need to simply update or &#039;recalculate&#039; the quarterIDs. You said you have six years of data so I&#039;m sure it wouldn&#039;t be difficult to figure out the ids from the original source. Now a better question is WHY is quarterId NULL in the first place? ; )<BR><BR>If its QuarterlySales field, why not simply update the NULL values to 0?<BR><BR>Pete<BR><BR><BR><BR><BR>

3. Member
Join Date
Dec 1969
Posts
70

## RE: Your example showed the

First of all, the row with NULL whould have YearEnded = 650. And that scenario is what I want to achieve but have not got yet.<BR><BR>My query returns no record for that quarter while it should show QuarterID 4 QuarterEnded = 0 and YearEnded = 650.<BR><BR>Does that make sense?

4. Senior Member
Join Date
Dec 1969
Posts
3,195

## How about showing us your

whole statement? ; )<BR><BR>

5. Member
Join Date
Dec 1969
Posts
70

## This is the YE part

select<BR> T.QuarterID as [Quarter],<BR> RE.[ID] as [Region Visited ID],<BR> RE.[Description] as [Region Visited],<BR> OG.OriginGroup,<BR> PG.AccommodationGroup,<BR> sum(T.Visits) as VisQTR,<BR> sum(YE.Visits) as VisYE,<BR> sum(T.Nights) as VisNitesQTR,<BR> sum(YE.Nights) as VisNitesYE,<BR> sum(T.Nights)/sum(T.Visits) as [ALOS QTR],<BR> sum(YE.Nights)/sum(YE.Visits) as [ALOS YE]<BR>from @TTT T<BR>inner join<BR> tblIDsNVSRegion RE on T.RegionVisitID = RE.[ID]<BR>inner join <BR> tblCrystal_NVSOriginGroupIDs OG on T.OriginGroupID = OG.OriginGroupID<BR>inner join <BR> tblCrystal_NVSAccommodationGroupIDs PG on T.AccommodationGroupID = PG.AccommodationGroupID<BR><BR>--Join to derived table that calculates the YTD totals<BR>inner join <BR> (<BR> select T3.QuarterID, T3.RegionVisitID, T3.OriginGroupID, T3.AccommodationGroupID, sum(T2.Visits) as Visits, sum(T2.Nights) as Nights<BR>from <BR> @TTT T2, @TTT T3<BR>where<BR> T2.RegionVisitID = T3.RegionVisitID and T2.OriginGroupID = T3.OriginGroupID and T2.AccommodationGroupID = T3.AccommodationGroupID and T2.QuarterID between T3.QuarterID-3 and T3.QuarterID<BR><BR>group by<BR> T3.QuarterID, T3.RegionVisitID, T3.OriginGroupID, T3.AccommodationGroupID) YE <BR>on T.QuarterID = YE.QuarterID and T.RegionVisitID = YE.RegionVisitID and T.OriginGroupID = YE.OriginGroupID and T.AccommodationGroupID = YE.AccommodationGroupID<BR><BR>group by<BR> T.QuarterID,<BR> RE.[ID],<BR> RE.[Description],<BR> OG.OriginGroup,<BR> PG.AccommodationGroup

6. Member
Join Date
Dec 1969
Posts
70

## This is in SQL2000 by the way

.

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## I don't understand...

...how you get a YearEnded value of 900 for quarter 4 in the data you show. I could see getting NULL for a result. I could see getting 700 for a result. But 900????<BR><BR>It&#039;s easy enough to convert a NULL to any value you want, in SQL Server:<BR> IsNull( QuarterlySales, 0 )<BR><BR>Look up the ISNULL function.<BR><BR>So I think it&#039;s as simple as<BR> SELECT T2.QuarterID, SUM( IsNull( T1.QuarterlySales, 0 ) ) AS YearEnded<BR> FROM table AS T1, table AS T2<BR> WHERE T1.QuarterID BETWEEN T2.QuarterID-3 AND T2.QuarterID<BR><BR>Is that all you were missing??<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

I *HOPE* that this is what you are after:<BR><BR>QuarterID--QuarterlySales--YearEnded <BR>1--150--150 (=1) <BR>2--400--550 (=2+1) <BR>3--100--650 (=3+2+1) <BR>4--NULL treated as 0--650<BR>5--130--630 (=5+4+3+2) --works fine <BR>6--200--430 (=6+5+4+3) --works fine <BR><BR>Yes? If so, than the ISNULL is all you needed.<BR><BR>

9. Member
Join Date
Dec 1969
Posts
70

## As a matter of fact...

...there is actually no data at all for some quarters, ie it is not even NULL. There is just no record. Do I have to loop through QuarterID 1 to whatever the last quarterid is to get this to work?<BR><BR>So the isNull will not work.<BR><BR>PS. Yes, it should have been 700.

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## Well, you're kind of screwed, but...

There&#039;s an easy solution.<BR><BR>Create a dummy table that just has quarter numbers in it (if they are just integers, than just a dirt simple table with one field, an integer, and sequential numbers from 1 to maybe 400? that will work for next 100 years).<BR><BR>And then LEFT JOIN from that table to the data table to drive getting info for all quarters, no matter what.<BR><BR>[If you have some other table that has data with all quarter ID&#039;s, you *could* LEFT JOIN to something like<BR> SELECT DISTINCT quarterID FROM foo WHERE quarterID BETWEEN ... <BR>but creating the dummy table would give a little better performance, I would bet.]<BR><BR>Hmmm???<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•