March 8, 2012 at 5:16 pm
[SOLVED] Remember to bind your subquery kids!
We see by my data the current query doesn't pull the total records for #2 but only record 1 & 3, I've adjusted my SQL to use a join on the TBLSD with a sub query... SQL Doesn't like how this is written... I'd love any help someone might be able to provide! Thanks!
My example data is:
01Base 2012-03-18 23:00:00100000.24
03Base 2012-03-18 23:00:0085000.66
Suggested Fix SQL:
Select
o.Number,
'Measure' = CASE when ISNULL(DT.Name,0) = 'BF' THEN 'Base' else 'missing data' END,
'Day' = CASE when ISNULL(MAX(SD.IntervalTime),0) <> 0 THEN MAX(SD.IntervalTime) ELSE 'missing data' END,
'total' = CASE when isnull(SUM(sd.IntervalValue),0) <> 0 THEN SUM(SD.IntervalValue) ELSE 'missing data' END
From TBLZR ZR
Left Join TBLOp O on O.ZRID = ZR.ZRID
join (select * from TBLOp O
join TBLSD SD on SD.ZROID = O.ZROID
join dbo.TBLDT DT on DT.RID = SD.DataTypeID
Where SD.IntervalTime >= convert(char(8),(DATEADD(day,4,GETDATE())), 112) AND
SD.IntervalTime < convert(char(8),(DATEADD(day,11,GETDATE())), 112) and
sd.DataTypeID = 49
group by o.Number, dt.Name, SD.DataTypeID
having SUM(SD.IntervalValue)> 0)
Order by o.StoreNumber
March 8, 2012 at 5:34 pm
jjohnson 40837 (3/8/2012)
We see by my data the current query doesn't pull the total records for #2 but only record 1 & 3, I've adjusted my SQL to use a join on the TBLSD with a sub query... SQL Doesn't like how this is written... I'd love any help someone might be able to provide! Thanks!My example data is:
01Base 2012-03-18 23:00:00100000.24
03Base 2012-03-18 23:00:0085000.66
Um, no, I'm afraid I don't see record #2 at all.
Suggested Fix SQL:
Select
o.Number,
'Measure' = CASE when ISNULL(DT.Name,0) = 'BF' THEN 'Base' else 'missing data' END,
'Day' = CASE when ISNULL(MAX(SD.IntervalTime),0) <> 0 THEN MAX(SD.IntervalTime) ELSE 'missing data' END,
'total' = CASE when isnull(SUM(sd.IntervalValue),0) <> 0 THEN SUM(SD.IntervalValue) ELSE 'missing data' END
From TBLZR ZR
Left Join TBLOp O on O.ZRID = ZR.ZRID
join (select * from TBLOp O
join TBLSD SD on SD.ZROID = O.ZROID
join dbo.TBLDT DT on DT.RID = SD.DataTypeID
Where SD.IntervalTime >= convert(char(8),(DATEADD(day,4,GETDATE())), 112) AND
SD.IntervalTime < convert(char(8),(DATEADD(day,11,GETDATE())), 112) and
sd.DataTypeID = 49
group by o.Number, dt.Name, SD.DataTypeID
having SUM(SD.IntervalValue)> 0)
Order by o.StoreNumber
What error are you getting? I don't see an alias on the subquery though, that's probably it. After the HAVING SUM() > 0) put in an alias, like AS SubQ or something.
Also, if SD.IntervalTime is a Datetime field, there's no reason to do the convert to character. There's faster and more efficient ways to strip the time portion using DATEADD/DATEDIFF.
If you look at the first link in my signature it'll walk you through schema/data setup for us to be able to easily consume it, test your code, and get you tested results.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 9, 2012 at 9:15 am
Thanks! that was what it was that I needed to not only give the subquery but bind it on a unique field!
Sadly someone stored the data as a string...
March 9, 2012 at 11:37 am
Here we go again.
CELKO (3/9/2012)
This is minimal polite behavior ON a SQL forum.
Please, remove this comment from your cut and paste. The hypocrisy is stifling and I don't have enough rags for the amount of drooling sarcasm it causes you.
We do not use CONVERT(); it is an old Sybase leftover for doing display formatting in the days before client/server architecture.
Funny that, did you notice that he mentioned that the original data was stored as a string. Unless you want to break indexing by forcing a convert of the base data, yes, you DO use CONVERT. You also use it whenever you want to change something out of julian when you're passing it to another system, not just for display.
Those single quotes and equal sign columns are 1970's Sybase; we have ANSI now;
The single quotes are overkill, I won't argue, but the column = statement has been brought up as a best practice for easier readability. While I find it personally annoying to read, many people practice this for readibility. Try not to fall off the horse as each of the straps comes loose beneath you.
and we never embedded blanks in a column. Read your ISO.
Where do you see blanks embedded as a column name? I see it in the DATA. As to reading the ISO, where can I get a FREE copy of the standards? Until I can, I'm not buying shinola from the ivory tower.
I tried to clean up what you did post confused.
No less confusing than the grammar of that statement.
Names are not numeric, times are not numeric. The table names are useless. We never use SELECT * in production code.
1) What in heck's name are you trying to say about things 'not being numeric'.
2) The table names are quick and easy to type and are intrinsic to their design, not your ability to read what's there like it was a book.
3) I use SELECT * in particular cases constantly (audit triggers), and regularly in certain cases, such as exists clauses. I also use it during query development before finalization when I restrict everything down to the specific pieces. If this was previously working, I might buy this one, but it's in development.
Do you want to try again?
Who in their right mind would EVER want to have you involved AGAIN in their concerns, other than the desperate?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply