0

My below example works fine, the only challenge i am facing is that weeks with 0 results do not show. Here is a sample of my code:

My current output:

week      | amount
4        |  354
6        |  222
7        |  144
8        |  354
9        |  45
10       |  55
11       |  76
12       |  98
13       |  45
14       |  344

The result above is missing many weeks (1,2,3 and 15,16,17 etc.) How do i get to show those with 0 count?

TEST EXAMPLE:

CREATE TABLE TABLE1 ( DATE datetime, COMPANY_ID varchar);
INSERT INTO TABLE1(DATE, COMPANY_ID)
VALUES 
('2019-01-24 00:00:00.000','Arla'),
('2019-01-24 00:00:00.000', 'B/S'),
('2019-01-24 00:00:00.000', 'NAaN'),
('2019-02-20 00:00:00.000', 'SuperBest'),
('2019-02-20 00:00:00.000', 'GS'),
('2019-04-29 00:00:00.000', 'Aldi'),
('2019-04-29 00:00:00.000', 'Netto'),
('2018-01-24 00:00:00.000', 'Arla');


;with ResultsTable as
(

SELECT 
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY_ID, DATE = MIN(DATE)
FROM TABLE1 A 

WHERE YEAR(A.DATE) = '2019' AND 

COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM TABLE1 WHERE DATE < '2019-01-01') 
GROUP BY COMPANY_ID) d    

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE) 

)
,WeekNumbers (WeekNumber)
AS (
SELECT 1 AS Number

UNION ALL

SELECT WeekNumber + 1
FROM WeekNumbers
WHERE WeekNumber < (
        SELECT datename(week, '2019-12-31')
        )
)
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
,COALESCE(rt.[week], 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt ON rt.[week] = wn.WeekNumber
| improve this question | | | | |
  • Can you show table structure and a few rows of sample data? Many of your columns aren't aliased, you interchange company and company_id, etc. – Aaron Bertrand Apr 26 '19 at 13:36
2

Could the solution be as simple as using a recursive CTE to generate a numbers table of the weeks in the year and then left joining to your main query? Using coalesce to output either the real amount or zeros when no match on week number is found?

--demo setup
Declare @ResultsTable table (
  [week] INTEGER,
  amount INTEGER
);

INSERT INTO @ResultsTable
  ([week], amount)
VALUES
  ('4', '354'),
  ('6', '222'),
  ('7', '144'),
  ('8', '354'),
  ('9', '45'),
  ('10', '55'),
  ('11', '76'),
  ('12', '98'),
  ('13', '45'),
  ('14', '344');

--recursive cte to generates week numbers
;WITH WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN @ResultsTable rt
    ON rt.[week] = wn.WeekNumber

| week | amount |
|------|--------|
| 1    | 0      |
| 2    | 0      |
| 3    | 0      |
| 4    | 354    |
| 5    | 0      |
| 6    | 222    |
| 7    | 144    |
| 8    | 354    |
| 9    | 45     |
| 10   | 55     |
| 11   | 76     |
| 12   | 98     |
| 13   | 45     |
| 14   | 344    |
| 15   | 0      |
| 16   | 0      |
| 17   | 0      |
| 18   | 0      |
| 19   | 0      |
| 20   | 0      |
| 21   | 0      |
| 22   | 0      |
| 23   | 0      |
| 24   | 0      |
| 25   | 0      |
| 26   | 0      |
| 27   | 0      |
| 28   | 0      |
| 29   | 0      |
| 30   | 0      |
| 31   | 0      |
| 32   | 0      |
| 33   | 0      |
| 34   | 0      |
| 35   | 0      |
| 36   | 0      |
| 37   | 0      |
| 38   | 0      |
| 39   | 0      |
| 40   | 0      |
| 41   | 0      |
| 42   | 0      |
| 43   | 0      |
| 44   | 0      |
| 45   | 0      |
| 46   | 0      |
| 47   | 0      |
| 48   | 0      |
| 49   | 0      |
| 50   | 0      |
| 51   | 0      |
| 52   | 0      |
| 53   | 0      |

So, using your original query incorporated into a Common Table Expression, the final solution might look something like this:

;with ResultsTable as
(
SELECT 
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY, DATE = MIN(DATE)
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.ID = B.ID
WHERE YEAR(A.DATE) = '2019' AND COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM 
TABLE1 A INNER JOIN TABLE2 B  ON A.ID = B.ID AND DATE < '2019-01-01') GROUP 
BY COMPANY_ID) d    

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE) 
)
,WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt
    ON rt.[week] = wn.WeekNumber
| improve this answer | | | | |
  • Hey Scott. Your example works great, is it possible for you to show me how to add your solution into my example above? – MishMish Apr 29 '19 at 8:36
  • @MishMish - I updated my answer to include your original query - it's not fully tested because I don't have your actual table definitions and data. – Scott Hodgin Apr 29 '19 at 8:44
  • Hi Scott, ill try it out and ill get back to you, incase it doesn't work, ill just post the original query for you :-) – MishMish Apr 29 '19 at 9:11
  • Hey Scott. The code is working, but the result is being updated wrong for weeks. Here is an image: prnt.sc/ni71g1 Would you like me to post my full code above? – MishMish Apr 29 '19 at 9:19
  • @MishMish - updating your question with table definitions (DDL) and sample data that shows the problem would be most helpful -otherwise, we're just guessing :) – Scott Hodgin Apr 29 '19 at 9:26
-1

You need to change JOIN to LEFT JOIN appropriately. Your code is not clean and clear, so just give an idea.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.