June 11, 2011 at 7:51 pm
I have the following the tsql that gets me exactly what I need, except its based on the whole record set.
I need to filter it out by date range. There is a datetime field - crdate, but don't know where to position it in the tsql....Please advise. TIA
"where crdate between '1-01-11' and '2-01-11' "
SELECT A.JAR1, B.JAR2 FROM
(select COUNT(cookie_id)as JAR1 from cookies
where ctype_id in (29683,29633,98334,83343,29645)
) AS A,
(select COUNT(cookie_id) as JAR2 from cookies
where ctype_id in (29682)
) AS B
June 11, 2011 at 9:09 pm
You are joining two subsets of the cookies table to each other. Assuming you want filter each set by those dates, you simply need to add it to the where clause in each query.
SELECT A.JAR1, B.JAR2 FROM
(select COUNT(cookie_id)as JAR1 from cookies
where ctype_id in (29683,29633,98334,83343,29645)
AND crdate between '1-01-11' and '2-01-11'
) AS A,
(select COUNT(cookie_id) as JAR2 from cookies
where ctype_id in (29682)
AND crdate between '1-01-11' and '2-01-11'
) AS B
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 12, 2011 at 7:43 am
Thx for the quick reply. That was kinda silly - I thought I tried that, but works 🙂
I did want to ask a follow-up, is it possible to take this query, and make percentages out of the values?
Value A%, Value B%
I've used a CTE before, but given the construct of these two values, is there a way to do this?
Example please. Many thanks!!
June 12, 2011 at 9:00 am
First of all, [font="Arial Black"]NEVER EVER DO A BETWEEN ON DATES![/font] Heh... yeah... I'm yelling here. 😀 The reason why to never do a BETWEEN on dates is simply to make your code more "bullet proof" if someone decides they want to start adding times to the cookie entries.
Second, there's absolutely no need to dip the table twice if you do a little "pre-aggregation" using some CROSS TAB technology. You'll see what I mean in the code below.
Third, get into the habit of using the 2-part naming convention for all tables. It'll make your code a bit faster if it's being called from a GUI or in a UDF and, again, it'll make your code more "bullet proof" if some "smart-guy" decides to start using multiple schemas.
Here's the code. Since you didn't include any test data in your original post (see the first link in my signature line below for the [font="Arial Black"]correct[/font] way to do that), I've not tested the code.
--===== Find two types of cookies in a date range and return the counts for each type in the date
-- range and the percentage of the total of the two types that each type represents
WITH
ctePreAggregate AS
( --=== Using a single pass on the table, pre-aggregate all the data we need for the date range
-- using a CROSS TAB.
SELECT Jar1 = SUM(CASE WHEN ctype_id IN (29683,29633,98334,83343,29645) THEN 1 ELSE 0 END),
Jar2 = SUM(CASE WHEN ctype_id IN (29682) THEN 1 ELSE 0 END),
Total = SUM(CASE WHEN ctype_id IN (29683,29633,98334,83343,29645,29682) THEN 1 ELSE 0 END)
FROM dbo.cookies
WHERE crdate >= @StartDate AND crdate < DATEADD(dd,1,@EndDate) --Includes all times on @EndDate
AND ctype_id IN (29683,29633,98334,83343,29645,29682)
) --=== Return all values including the requested percentage calculations.
SELECT Jar1, Jar2, Total,
Jar1Percent = ISNULL((Jar1*100.0)/NULLIF(Total,0),0.0), --Prevents "Divide-by-Zero" errors
Jar2Percent = ISNULL((Jar2*100.0)/NULLIF(Total,0),0.0) --Prevents "Divide-by-Zero" errors
FROM ctePreAggregate
;
As a bit of a side-bar, find a good "indented" standard for formatting your code. It'll make you look better in the eyes of your peers, it gives you a chance to review your own code for errors, and it'll make life a whole lot easier when you actually have to read the code to troubleshoot it or make modifications in the future. 😉 Don't forget to comment your code so you can remember what you did six months from now. My recommendation there is to comment every SELECT, INSERT, UPDATE, and DELETE even if they're in a subquery so you don't actually have to read the code to figure out where you need to make a fix or a change. We reduced research time for many code modifications from and average of 2 days down to just a couple of minutes by using proper comments in the code. Documenting even obvious "trick code" (like I did with some of the "tail" comments) will also help the next person. It takes only a minute or two to properly comment the code as you write it and it saves days in troubleshooting time. Quite the ROI if I do say so myself. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2011 at 11:53 am
The Dixie Flatline (6/11/2011)
That guy's about as useless as a varchar(1). -- Original
I like that quote. Here's another one that I frequently use on managers that promise things to their bosses with unreasonable schedules...
If you want it real bad, you'll normally get it that way. --Jeff Moden 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2011 at 11:54 am
Jeff Moden (6/12/2011)
As a bit of a side-bar, find a good "indented" standard for formatting your code.
I use someone's formatting as the first and fastest indicator of how mature their SQL is, and I like having vertical rivers like you have here, but even I don't right align my clause keywords. That's pretty boss, Jeff 😀
By the way, what's your formatting for multiline case statements? The asymmetry of the construct kills me.
June 12, 2011 at 11:57 am
Jeff Moden (6/12/2011)
The Dixie Flatline (6/11/2011)
That guy's about as useless as a varchar(1). -- OriginalI like that quote. Here's another one that I frequently use on managers...
If you want it real bad, you'll normally get it that way. --Jeff Moden 😀
I work every day with a (vendor) system that was built with varchar(1)'s all over the place. It's awesome. It also uses tinyints for booleans, you know, so you can index them... :crazy:
June 12, 2011 at 1:34 pm
allmhuran (6/12/2011)
Jeff Moden (6/12/2011)
As a bit of a side-bar, find a good "indented" standard for formatting your code.I use someone's formatting as the first and fastest indicator of how mature their SQL is, and I like having vertical rivers like you have here, but even I don't right align my clause keywords. That's pretty boss, Jeff 😀
By the way, what's your formatting for multiline case statements? The asymmetry of the construct kills me.
Thanks :blush:... it's probably the reason why I have to replace a space-bar and left-shift key on my keyboard that's been worn through every year or so. I also set TABs to 4 and convert TABs to SPACEs so that the code stays formatted even if you paste it into notepad. Of course, I don't space-in for "non-clause" lines. I use 2 tabs to get there.
As a sidebar, it doesn't take very much time to format this way (right aligned clauses) once you've practiced it a bit. I will admit, however, it's way over the top for most folks and that's definitely OK with me. I'm normally pretty happy with anyone thoughtful enough to left-align clauses in their own "column" especially if they adopt a consistent set of rules for which "case" to use and when.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2011 at 2:17 pm
Hah, allow me to quote from my own blog post here...
Certain aspects of formatting can easily be automatically changed to a user’s preference by the environment. Block indenting is perhaps the easiest of any of these aspects to automatically reformat. SSMS can do this natively.
Vertical whitespace rivers are purely organizational and not strictly related to surrounding logic or formatted elements in the way block indenting is related to other indented blocks. Vertical rivers are therefore very difficult to automatically reformat. Redgate’s toolbelt contains two code reformatters that attempt to perform vertical alignment, but neither works very well.
A formatting standard for SQL should therefore be focused on maintaining the formatting and readability of vertical alignment within large and complex statements.
When a fixed width font is used, a space character can always produce vertical alignment with any character in a previous line. Fixed width font therefore must be used to guarantee the preservation of vertical alignment both within one’s own code, and when viewing the code of others.
If the IDE is set to keep tabs instead of inserting spaces, any vertical alignment set by the use of the tab key will be completely misaligned when another user views the code with a different tab size. If more than one user has worked on a section of code then trying to set the IDE to match the original tab size may fix one part of the code while breaking another part. If the IDE is set to insert spaces then vertical alignment is preserved across all users’ environments.
“Insert spaces” will also preserve alignment across different IDEs and text editors.
It is true that enforcing “insert spaces” is bad because it requires all users to set up their IDE the same way in terms of this setting. It is, however, equally true that “keep tabs” is bad because it requires all users to set up their IDE the same way in terms of tab size. The argument from vertical alignment is the trump card for “insert spaces”.
Oh and, /hijack :Whistling:
June 12, 2011 at 10:53 pm
Daaannnggg.... 😀 Sounds almost like what I'd write except the "block code" setting slows me down. 😛 Nicely done.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2011 at 10:58 pm
p.s. I guess todays caffeine has finally failed me. You had the following near the bottom of one of your posts. What does it mean?
Oh and, /hijack
I also missed your question about multiline CASE statements. I'll try to dig up an example from existing code but not tonight. I'm poopin' out and already did one fine multi-key depression with my forhead. 😀 I've gotta go to bed.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2011 at 8:47 am
Thank you, Jeff! I see quite a lot of activity around my post (which doesn't really have anything to do w/my original question 🙂 But, I am quite grateful for Jeff's expertise - even if he yelled at me 🙁
June 13, 2011 at 9:03 am
Mine look something like this:
select case
when 1 = 1 then 1
when 1 = 2 then 2
else 3
end,
case when 4 = 5 then 6 else 7 end,
case
when case -- never found a nice way to do
when 8 = 9 -- nested cases, especially
and 10 = 11 -- with long, complex conditions
and 12 = 13
then 14
else 15
end = 16
then 17
else 18
end
/hijack "means sorry for the thread hijack, I'll stop now". Except I haven't. 😛
June 13, 2011 at 9:24 am
Jeff Moden (6/12/2011)
The Dixie Flatline (6/11/2011)
That guy's about as useless as a varchar(1). -- OriginalI like that quote. Here's another one that I frequently use on managers that promise things to their bosses with unreasonable schedules...
If you want it real bad, you'll normally get it that way. --Jeff Moden 😀
I'll remember that one. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 13, 2011 at 9:51 am
sql_jr (6/13/2011)
Thank you, Jeff! I see quite a lot of activity around my post (which doesn't really have anything to do w/my original question 🙂 But, I am quite grateful for Jeff's expertise - even if he yelled at me 🙁
Heh... sorry. Didn't mean to make it look like I was yelling at you specifically. Using BETWEEN on dates has become a viral problem so I've become prone to making sure no one misses the suggestion to not use BETWEEN with dates.
I also apologize for the bit of a highjack of your thread. As a bit of a side bar (I don't know if it matters to you), but the problem you posted has inspired me to begin work on an article on the subject so I really appreciate your original post on the subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply