6

I have the following query:

SELECT DISTINCT
           COUNT(CD_BarCode)
           ,CD_Carrier
           ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date
FROM TB_AGIL 
WHERE 
           DT_Arriving >= @date
           AND DT_Arriving < DATEADD(MONTH,+1,@date)
           AND CD_TRACKING = 14
GROUP BY CD_Carrier, SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11)
ORDER BY Date 

I use this to see the results from last month's cargo movimentation. The results are a list of cd_tracking amount grouped by date like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |

My problem is that if there is no cd_tracking = 14 on a given day, that day will not show on the result list. As you can see, there is no Aug 2nd on the list.

I'd like to know if there is a way to bring every day on the result list, even if there is no cd_tracking = 14 on that day. Like this:

| Amount | Carrier |    Date    |
|--------|---------|------------|
|  2599  |   44    | 01/08/2015 |
|  NULL  |   44    | 02/08/2015 |
|  2504  |   44    | 03/08/2015 |
|  4597  |   44    | 04/08/2015 |
|  5058  |   44    | 05/08/2015 |
|  2413  |   44    | 06/08/2015 |
|  4853  |   44    | 07/08/2015 |
4

Move the condition from the WHERE clause to a conditional count:

SELECT
    COUNT(CASE WHEN CD_TRACKING = 14 THEN CD_BarCode ELSE NULL END),
    CD_Carrier,
    DATEPART(DAY, DT_Arriving) as [Date]
FROM TB_AGIL 
WHERE
    DT_Arriving >= @date
    AND DT_Arriving < DATEADD(MONTH,+1,@date)
GROUP BY 
    CD_Carrier, 
    DATEPART(DAY, DT_Arriving)
ORDER BY 
    [Date];

Other approaches to implement the deprecated GROUP BY ALL syntax can be found here.

| improve this answer | |
2

For these kind of reports I prefer to use a table of numbers or calendar table. Table of numbers is simply a table that has one column with integer numbers from 1 to some large enough number, say, 100,000. I personally use table with 100K numbers. Aaron Bertrand wrote a good article explaining how to generate such table. Calendar table is simply a table that has one column with dates covering large enough range. Usually all dates from 1900 to 2200 would be enough.

So, once you have a table Calendar with column dt, which has date values and is a primary key, we can use it in your query.

CREATE TABLE [dbo].[Calendar](
    [dt] [date] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED 
(
    [dt] ASC
))

DISTINCT doesn't do anything in your SELECT, so I removed it. To group by date I cast the DT_Arriving to date type, rather than varchar. I assume DT_Arriving is not of date type. If it is, then cast is not needed. If you really need to return dates to the client as varchar, do it in the final SELECT.

CTE_Main is your original query. We need full month of dates for each CD_Carrier that appears in the result of CTE_Main. So, CTE_Carriers gives us a list of all CD_Carrier values. We CROSS JOIN it with the Calendar table to get a list of all dates of the month for each CD_Carrier. Then we LEFT JOIN CTE_Main to this result to get Amount.

WITH
CTE_Main
AS
(
    SELECT
        COUNT(CD_BarCode) AS Amount
        ,CD_Carrier
        ,CAST(DT_Arriving AS date) AS dt
    FROM TB_AGIL 
    WHERE 
        DT_Arriving >= @date
        AND DT_Arriving < DATEADD(MONTH,+1,@date)
        AND CD_TRACKING = 14
    GROUP BY CD_Carrier, CAST(DT_Arriving AS date)
)
,CTE_Carriers
AS
(
    SELECT CD_Carrier
    FROM CTE_Main
    GROUP BY CD_Carrier
)
SELECT
    CTE_Main.Amount
    ,CTE_Carriers.CD_Carrier
    ,Calendar.dt
FROM
    CTE_Carriers
    CROSS JOIN Calendar
    LEFT JOIN CTE_Main ON 
        CTE_Main.CD_Carrier = CTE_Carriers.CD_Carrier
        AND CTE_Main.dt = Calendar.dt
WHERE
    Calendar.dt >= @date
    AND Calendar.dt < DATEADD(MONTH,+1,@date)
ORDER BY dt;
| improve this answer | |
1

If the dates aren't in your resultset then you need to get them from somewhere right? You could create a join with a derived table full of dates.

| 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.