Fizor
Reputation: 1530
SQL Server joining trouble
Scenario:
I am trying to build a query which has a start and end date, And the result of this query gives me the days in between and the day name. I then want to JOIN to another table which has expected pay dates and amounts. The JOINED table may have more days outside the range of the start and end date, which I want to exclude.
Progress:
I sort-of have what I want, but not in the correct output, I have created the following thus far:
DECLARE
@startDate DATETIME,
@endDate DATETIME
SET @startDate = CONVERT(VARCHAR(4), DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())))+'-21'
SET @endDate = CONVERT(VARCHAR(4), DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, DATEADD(MONTH, -0, GETDATE())))+'-20'
;WITH dates AS
(
SELECT @startdate as Date,DATENAME(Dw,@startdate) As DayName
UNION ALL
SELECT DATEADD(d,1,[Date]),DATENAME(Dw,DATEADD(d,1,[Date])) as DayName
FROM dates
WHERE DATE < @enddate
)
SELECT LEFT(CONVERT(VARCHAR(30),Date, 106), 2) + '-' + LEFT(CONVERT(VARCHAR(30),Date, 10), 2) Date,DayName, SUM(ExpectedAmount), ExpectedDate FROM dates
FULL JOIN Commissions.dbo.ThreeMonthPayment on CONVERT(VARCHAR(30),Date) = Commissions.dbo.ThreeMonthPayment.ExpectedDate
GROUP BY Date, DayName, ExpectedDate
Order by ExpectedDate
Which results in this table (Sorry so long):
+-------+-----------+------------------+--------------+
| Date | DayName | (No column name) | ExpectedDate |
+-------+-----------+------------------+--------------+
| NULL | NULL | 0 | NULL |
| 21-03 | Friday | NULL | NULL |
| 22-03 | Saturday | NULL | NULL |
| 23-03 | Sunday | NULL | NULL |
| 24-03 | Monday | NULL | NULL |
| 25-03 | Tuesday | NULL | NULL |
| 26-03 | Wednesday | NULL | NULL |
| 27-03 | Thursday | NULL | NULL |
| 28-03 | Friday | NULL | NULL |
| 29-03 | Saturday | NULL | NULL |
| 30-03 | Sunday | NULL | NULL |
| 31-03 | Monday | NULL | NULL |
| 01-04 | Tuesday | NULL | NULL |
| 02-04 | Wednesday | NULL | NULL |
| 03-04 | Thursday | NULL | NULL |
| 04-04 | Friday | NULL | NULL |
| 05-04 | Saturday | NULL | NULL |
| 06-04 | Sunday | NULL | NULL |
| 07-04 | Monday | NULL | NULL |
| 08-04 | Tuesday | NULL | NULL |
| 09-04 | Wednesday | NULL | NULL |
| 10-04 | Thursday | NULL | NULL |
| 11-04 | Friday | NULL | NULL |
| 12-04 | Saturday | NULL | NULL |
| 13-04 | Sunday | NULL | NULL |
| 14-04 | Monday | NULL | NULL |
| 15-04 | Tuesday | NULL | NULL |
| 16-04 | Wednesday | NULL | NULL |
| 17-04 | Thursday | NULL | NULL |
| 18-04 | Friday | NULL | NULL |
| 19-04 | Saturday | NULL | NULL |
| 20-04 | Sunday | NULL | NULL |
| NULL | NULL | 89466 | 01-03 |
| NULL | NULL | 86058 | 01-04 |
| NULL | NULL | 23356 | 01-05 |
| NULL | NULL | 1858 | 01-06 |
| NULL | NULL | 13597 | 02-03 |
| NULL | NULL | 55587 | 02-04 |
| NULL | NULL | 7857 | 02-05 |
| NULL | NULL | 1377 | 02-06 |
| NULL | NULL | 6947 | 03-03 |
| NULL | NULL | 49626 | 03-04 |
| NULL | NULL | 0 | 03-05 |
| NULL | NULL | 0 | 03-06 |
| NULL | NULL | 6054 | 04-03 |
| NULL | NULL | 31639 | 04-04 |
| NULL | NULL | 0 | 04-05 |
| NULL | NULL | 0 | 04-06 |
| NULL | NULL | 26421 | 05-03 |
| NULL | NULL | 28154 | 05-04 |
| NULL | NULL | 15036 | 05-05 |
| NULL | NULL | 634 | 05-06 |
| NULL | NULL | 0 | 05-07 |
| NULL | NULL | 20832 | 06-03 |
| NULL | NULL | 0 | 06-04 |
| NULL | NULL | 0 | 06-05 |
| NULL | NULL | 0 | 06-06 |
| NULL | NULL | 5406 | 07-03 |
| NULL | NULL | 12864 | 07-04 |
| NULL | NULL | 4257 | 07-05 |
| NULL | NULL | 537 | 07-06 |
| NULL | NULL | 0 | 08-03 |
| NULL | NULL | 363 | 08-04 |
| NULL | NULL | 426 | 08-05 |
| NULL | NULL | 0 | 08-06 |
| NULL | NULL | 0 | 09-03 |
| NULL | NULL | 23240 | 09-04 |
| NULL | NULL | 0 | 09-05 |
| NULL | NULL | 0 | 09-06 |
| NULL | NULL | 12670 | 10-03 |
| NULL | NULL | 6790 | 10-04 |
| NULL | NULL | 0 | 10-05 |
| NULL | NULL | 0 | 10-06 |
| NULL | NULL | 2914 | 11-03 |
| NULL | NULL | 19053 | 11-04 |
| NULL | NULL | 0 | 11-05 |
| NULL | NULL | 0 | 11-06 |
| NULL | NULL | 6402 | 12-03 |
| NULL | NULL | 0 | 12-04 |
| NULL | NULL | 0 | 12-05 |
| NULL | NULL | 0 | 12-06 |
| NULL | NULL | 4166 | 13-03 |
| NULL | NULL | 0 | 13-04 |
| NULL | NULL | 0 | 13-05 |
| NULL | NULL | 0 | 13-06 |
| NULL | NULL | 50534 | 14-03 |
| NULL | NULL | 23854 | 14-04 |
| NULL | NULL | 15435 | 14-05 |
| NULL | NULL | 4003 | 14-06 |
| NULL | NULL | 475330 | 15-03 |
| NULL | NULL | 451014 | 15-04 |
| NULL | NULL | 103210 | 15-05 |
| NULL | NULL | 19947 | 15-06 |
| NULL | NULL | 12084 | 16-03 |
| NULL | NULL | 22203 | 16-04 |
| NULL | NULL | 517 | 16-05 |
| NULL | NULL | 0 | 16-06 |
| NULL | NULL | 31423 | 17-03 |
| NULL | NULL | 32150 | 17-04 |
| NULL | NULL | 0 | 17-05 |
| NULL | NULL | 0 | 17-06 |
| NULL | NULL | 33402 | 18-03 |
| NULL | NULL | 900 | 18-04 |
| NULL | NULL | 289 | 18-05 |
| NULL | NULL | 0 | 18-06 |
| NULL | NULL | 33929 | 19-03 |
| NULL | NULL | 6942 | 19-04 |
| NULL | NULL | 0 | 19-05 |
| NULL | NULL | 0 | 19-06 |
| NULL | NULL | 161806 | 20-03 |
| NULL | NULL | 141319 | 20-04 |
| NULL | NULL | 26659 | 20-05 |
| NULL | NULL | 4695 | 20-06 |
| NULL | NULL | 21074 | 21-03 |
| NULL | NULL | 15579 | 21-04 |
| NULL | NULL | 2693 | 21-05 |
| NULL | NULL | 0 | 21-06 |
| NULL | NULL | 28401 | 22-03 |
| NULL | NULL | 46258 | 22-04 |
| NULL | NULL | 11409 | 22-05 |
| NULL | NULL | 1672 | 22-06 |
| NULL | NULL | 76562 | 23-03 |
| NULL | NULL | 66804 | 23-04 |
| NULL | NULL | 32853 | 23-05 |
| NULL | NULL | 3168 | 23-06 |
| NULL | NULL | 47008 | 24-03 |
| NULL | NULL | 35888 | 24-04 |
| NULL | NULL | 4528 | 24-05 |
| NULL | NULL | 459 | 24-06 |
| NULL | NULL | 1108747 | 25-03 |
| NULL | NULL | 543351 | 25-04 |
| NULL | NULL | 152852 | 25-05 |
| NULL | NULL | 15712 | 25-06 |
| NULL | NULL | 343379 | 26-03 |
| NULL | NULL | 117657 | 26-04 |
| NULL | NULL | 41793 | 26-05 |
| NULL | NULL | 5645 | 26-06 |
| NULL | NULL | 0 | 27-02 |
| NULL | NULL | 401110 | 27-03 |
| NULL | NULL | 87571 | 27-04 |
| NULL | NULL | 39192 | 27-05 |
| NULL | NULL | 2801 | 27-06 |
| NULL | NULL | 313274 | 28-03 |
| NULL | NULL | 92607 | 28-04 |
| NULL | NULL | 21901 | 28-05 |
| NULL | NULL | 1852 | 28-06 |
| NULL | NULL | 77999 | 29-03 |
| NULL | NULL | 27693 | 29-04 |
| NULL | NULL | 3341 | 29-05 |
| NULL | NULL | 0 | 29-06 |
| NULL | NULL | 229556 | 30-03 |
| NULL | NULL | 261036 | 30-04 |
| NULL | NULL | 9109 | 30-05 |
| NULL | NULL | 545 | 30-06 |
| NULL | NULL | 460871 | 31-03 |
| NULL | NULL | 28710 | 31-05 |
+-------+-----------+------------------+--------------+
Out of the above results, I am trying to match the ExpectedDate to the date column , so instead of seeing the above results, I would have something that looks like this: (to keep it short, i haven't created all of the days I receive from my start and end date)
+-------+-----------+------------------+--------------+
| Date | DayName | (No column name) | ExpectedDate |
+-------+-----------+------------------+--------------+
| NULL | NULL | 0 | NULL |
| 21-03 | Friday | 21074 | 21-03 |
| 22-03 | Saturday | 28401 | 22-03 |
| 23-03 | Sunday | 76562 | 23-03 |
| 24-03 | Monday | 47008 | 24-03 |
+-------+-----------+------------------+--------------+
But you can see above that the expectedDate and date column are grouped / joined nicely together. And the expectedDates that are not in the date range are not displayed.
I have been struggling with this the entire morning :( is this possible ??
Any help or links to threads that I may have missed would be great!
I am using SQL SERVER 2008
Thanks so much.
Upvotes: 0
Views: 52
Answers (1)
Brett Schneider
Reputation: 4103
first of all the full join includes everything. from both tables. if you only want the dates from the dates cte, use left join.
secondly, the CONVERT(VARCHAR(30),Date) = Commissions.dbo.ThreeMonthPayment.ExpectedDate seems to not work. are you sure you need to convert?
i suggest you try this:
DECLARE
@startDate DATETIME,
@endDate DATETIME
SET @startDate = CONVERT(VARCHAR(4), DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())))+'-21'
SET @endDate = CONVERT(VARCHAR(4), DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, DATEADD(MONTH, -0, GETDATE())))+'-20'
;WITH dates AS
(
SELECT @startdate as Date,DATENAME(Dw,@startdate) As DayName
UNION ALL
SELECT DATEADD(d,1,[Date]),DATENAME(Dw,DATEADD(d,1,[Date])) as DayName
FROM dates
WHERE DATE < @enddate
)
SELECT LEFT(CONVERT(VARCHAR(30),Date, 106), 2) + '-' + LEFT(CONVERT(VARCHAR(30),Date, 10), 2) Date
, DayName, SUM(ExpectedAmount), ExpectedDate
FROM dates
LEFT JOIN Commissions.dbo.ThreeMonthPayment
on Date = Commissions.dbo.ThreeMonthPayment.ExpectedDate
GROUP BY
Date
, DayName
, ExpectedDate
Order by
ExpectedDate
Upvotes: 1