September 9, 2009 at 1:58 pm
Good evening All,
I would like to query a date range to output data in a weekly format. Ex.
M T W Th Fr
13 27 15 6 90
Does this make sense? Histogram?
Thanks,
DJ Khalif
September 9, 2009 at 2:26 pm
No, Khalif, it does not make sense. You have been asked before to please submit DDL statements and sample data, as well as what the expected output should be based on that data, and to as well show us what you have tried to do yourself to solve your problem before you ask a question. Please do so, and any of us will be more than happy to help you out.
Jan
September 9, 2009 at 3:24 pm
Jan,
I did not know that was the criteria for getting help. I have resolved most of my issues or moved on to something else. I don't own the data to sample for your, the company I work for owns it. I just wanted to know can I query data inputted on as Monday without using Monday as a keyword? The same would go for the other 4 days of the work week.
September 9, 2009 at 3:49 pm
To describe your scenario providing ready to use data is not a "criteria for getting help" but it'll increase your chance of getting answers that meet your scenario.
Based on your question:
I just wanted to know can I query data inputted on as Monday without using Monday as a keyword?
The answer is: It depends, but in most cases: Yes, you can.
With reference to your last post:
I would like to query a date range to output data in a weekly format. Ex.
M T W Th Fr
13 27 15 6 90
The answer is simple as well:
SELECT * FROM YourTable
-- assuming your table has the appropriate columns and the values you'd like to query are entered...
See the point?
We don't see the data the same way you see it.
We don't look over your shoulder.
All we're asking for is to help us help you.
Please read the article referenced in my signature on how to post data to get fast and accurate/verified answers.
I don't want to sound too harsh but like Jan already told you: The information you provided so far doesn't make sense. Neither to Jan nor to me.
September 9, 2009 at 5:28 pm
How do I submit the data?
September 10, 2009 at 1:20 am
kabaari (9/9/2009)
How do I submit the data?
Khalif, did you follow the link in Lutz's signature? That should tell you all about how to submit test data. And no, we are not asking for any confidential data, just reasonable samples of made-up data that helps YOU in explaining what your issue is and what the expected result should be, which in turn helps US in getting a reasonably accurate and quick answer. We can't read your mind, you know, and as Lutz said, we can't look over your shoulder either.
September 10, 2009 at 1:32 am
Im i correct in guessing , next time please give more detail as others have suggested, that you want totals sales (or something) on a day by day / week by week basis ?
Select WeekNo = datepart(week,datecol),
Mon = sum(case when datepart(dw,datecol)=2 then Sales else 0 end),
Tues = sum(case when datepart(dw,datecol)=3 then Sales else 0 end) -- etc repeat for each day of week
from yourtable
group by datepart(week,datecol)
If you are feeling adventurous you could investigate PIVOT in BOL (Books on line)
September 10, 2009 at 12:52 pm
Dave Ballantyne (9/10/2009)
...
Select WeekNo = datepart(week,datecol),
Mon = sum(case when datepart(dw,datecol)=2 then Sales else 0 end),
Tues = sum(case when datepart(dw,datecol)=3 then Sales else 0 end) -- etc repeat for each day of week
from yourtable
group by datepart(week,datecol)
I'd like to address a side effect when using datepart together with week or weekday: It's important to have control over the value of @@datefirst.
The default value depends on the default language a user/login is set to and may end in different results of the same query. A user with a default language of e.g. English will get different results than one with German as default.
Therefore, I asked for valid test data to make sure the code sample will work... 😉
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply