May 4, 2017 at 2:56 pm
Hi Guys! Hope you can help me with this.
I have 3 tables as:
table 1
A B C1
1 1/1/17 10
2 1/7/17 20
3 1/14/17 30
table 2
A B C2
1 1/5/17 40
2 1/10/17 50
3 1/16/17 60
table 3
A B C2
1 1/6/17 10
2 1/11/17 10
3 1/18/17 10
I will be joining by field A. B is the date field. C is the quantity field.
I need to sum the C field from table 1 and 2, substract the value from table 3 and group the results by week of the month (week is monday to sunday) using field B. I'm assuming that for example in the first row, the week is the same for the 3 dates in the tables (1/1, 1/5 and 1/6). So the final result should be something like.
A B C1 C2 C3 C(final value)
1 week from 1/1 to 1/7 10 40 10 40 (10+40-10)
2 week from 1/8 to 1/14 20 50 10 60 (20+50-10)
3 week from 1/15 to 1/21 30 60 10 80 (30+60-10)
How can I do this? I'm having trouble mainly figuring out how to get the week of the month.
Thanks for the help!
May 4, 2017 at 3:51 pm
You haven't really defined your data well enough for me to determine what's going on. For example, what is the point of Column A? Is that the week number? Or is that a meaningless number? And what are you defining as a week? Sunday-Saturday? Or day 1-7? Why are you expecting Table 1 Column B Row 2 (1/7/17) to fall in the second week when you are defining your week from 1/1 to 1/7 in your expected results? Too many questions... But perhaps your solution will look something like this?
WITH
Table1 AS
(
SELECT A = CAST(1 AS INT), B = CAST('20170101' AS DATE), C = CAST(10 AS INT) UNION ALL
SELECT 2, '20170107', 20 UNION ALL
SELECT 3, '20170114', 30
),
Table2 AS
(
SELECT A = CAST(1 AS INT), B = CAST('20170105' AS DATE), C = CAST(40 AS INT) UNION ALL
SELECT 2, '20170110', 50 UNION ALL
SELECT 3, '20170116', 60
),
Table3 AS
(
SELECT A = CAST(1 AS INT), B = CAST('20170106' AS DATE), C = CAST(10 AS INT) UNION ALL
SELECT 2, '20170111', 10 UNION ALL
SELECT 3, '20170118', 10
)
SELECT
Table1.A,
B = 'month ' + CAST(DATEPART(MONTH,Table1.B) AS VARCHAR(25)) + ' week ' + CAST(DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, Table1.B), 0), Table1.B) + 1 AS VARCHAR(25)),
C1 = Table1.C,
C2 = Table2.C,
C3 = Table3.C,
[C(final value)] = Table1.C + Table2.C - Table3.C
FROM Table1
INNER JOIN Table2 ON
Table1.A = Table2.A
INNER JOIN Table3 ON
Table1.A = Table3.A
May 5, 2017 at 12:53 pm
Thanks autoexcrement! Sorry for being vague about the data I need.
The week will be defined from Monday to Sunday, the dates that I put were merely referential; looking at it now, it surely looks confusing.
What I need help with is on how setting the weeks that I need and how I can join the tables depending on the week.
So how I tell SQL that 1/1/2017 from table 1 falls in the same week that 1/5/2017 from table 2?
Hope that clarifies things.
Thanks again!
May 5, 2017 at 2:54 pm
If you just need a week number within a year, the DATEPART function can handle that, but if you really need to figure out weeks by month, it might make more sense to use a calendar table where you keep track of all the attributes of each week:
http://qa.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/
May 5, 2017 at 5:45 pm
If what I posted isn't sufficient, then yes, you need a calendar table as the above guys have suggested.
May 5, 2017 at 8:21 pm
Chris Harshman - Friday, May 5, 2017 2:54 PMIf you just need a week number within a year, the DATEPART function can handle that, but if you really need to figure out weeks by month, it might make more sense to use a calendar table where you keep track of all the attributes of each week:
http://qa.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/
That's one of the better calendar table articles I've seen.
May 7, 2017 at 9:07 am
Ed Wagner - Friday, May 5, 2017 8:21 PMChris Harshman - Friday, May 5, 2017 2:54 PMIf you just need a week number within a year, the DATEPART function can handle that, but if you really need to figure out weeks by month, it might make more sense to use a calendar table where you keep track of all the attributes of each week:
http://qa.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/That's one of the better calendar table articles I've seen.
Man, I miss Dwain Camps. He was definitely one of the good guys.
--Jeff Moden
May 8, 2017 at 12:55 pm
Yeah, me too, Jeff. Definitely didn't mind getting schooled by someone that smart, but really humble (unlike some folk, like JC?)
May 8, 2017 at 1:06 pm
pietlinden - Monday, May 8, 2017 12:55 PMYeah, me too, Jeff. Definitely didn't mind getting schooled by someone that smart, but really humble (unlike some folk, like JC?)
That's a whole different class in many ways. I learned things from Dwain.
May 9, 2017 at 5:43 am
Back to the OP, there seems to be some inconsistency in your data and desired results.
First you say that the week is Monday through Sunday. This will create months with varying numbers of weeks, some with 5 and some with 6. (In a few years, February will have exactly 4 weeks.) Look at January 2017.
Week 1 - consists of 1 Jan 2017 only - because the month starts on Sunday, which is the end of your week. How do you handle this? Is it meant to be part of the last week of December 2016?
Week 2 - 2 through 8
Week 3 - 9 through 15
Week 4 - 16 through 22
Week 5 - 23 through 29
Week 6 - 30 through 31 - Or do we start adding in days from February to make a full 7-day week?
But even if you work through that, your results say the first week is 1/1 through 1/7. Accepting that premise (that you really meant Sunday through Saturday for the week), you have 2 values from Table A for that week, dated 1/1 and 1/7. But you put the value 20 (from 1/7) into the second week which you identify as 1/8 through 1/14. Why?
This process needs more definition, or no one will be able to help you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply