CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Lisbon
Lisbon

Reputation: 49

How to show monthly data even if there are no results yet SQL Server 2008

So I wrote a script that would show monthly premium. Say if you want to view the total premium up to November, you can pass through a parameter in in SSRS to pick 1/1/2016 - 11/30/2016. This would only show the data up until november, hoever, I would like to show it up until december even if there are no records there. How do I go about doing this in SQL? Here is my script so far:

SELECT lc.[Date]
      ,lc.Carrier
      ,lc.[Direct Ceded Written Premium]
      ,cast(cast(year(lc.[date]) as varchar(4)) + '-' + cast(month(lc.[date]) as varchar(2)) + '-01' as date) as [begofmonth]
from
(
SELECT
    CASE
    WHEN pd.TransactionEffDate < pd.TransactionDate THEN cast(pd.TransactionDate as DATE)
    WHEN pd.TransactionEffDate < pd.EffectiveDate THEN cast(pd.EffectiveDate as DATE)
    ELSE cast(pd.TransactionEffDate as date)
    END AS [Date]
    ,CASE WHEN LEFT(PD.POLICYNUM, 3) = 'ORV'
          THEN 'Palomar Value Select OR'
          WHEN LEFT(PD.POLICYNUM, 3) = 'VSE'
          THEN 'Palomar Value Select CA'
          WHEN LEFT(PD.POLICYNUM, 3) = 'WAV'
          THEN 'Palomar Value Select WA'
          ELSE 'Palomar' END AS [Carrier]
    ,ISNULL(SUM(pd.WrittenPremium), 0) AS [Direct Ceded Written Premium]
FROM   premdetail pd 
JOIN transactionpremium tp ON pd.systemid = tp.systemid
AND pd.transactionpremiumid = tp.id
JOIN transactionhistory th ON tp.systemid = th.systemid
AND tp.cmmcontainer = th.cmmcontainer
AND tp.parentid = th.id
JOIN basicpolicy bp ON th.systemid = bp.systemid
AND th.cmmcontainer = bp.cmmcontainer
AND th.parentid = bp.id 
WHERE  
(CASE
    WHEN pd.TransactionEffDate < pd.TransactionDate THEN pd.TransactionDate
    WHEN pd.TransactionEffDate < pd.EffectiveDate THEN pd.EffectiveDate
    ELSE pd.TransactionEffDate
    END) > = CAST(@StartDate AS DATE)
AND (CASE
    WHEN pd.TransactionEffDate < pd.TransactionDate THEN pd.TransactionDate
    WHEN pd.TransactionEffDate < pd.EffectiveDate THEN pd.EffectiveDate
    ELSE pd.TransactionEffDate
  END) < CAST(@EndDate + 1 AS DATE)
AND (bp.carriercd = @ResEQCarrierCd
   OR @ResEQCarrierCd = 'All')  
GROUP  BY
    CASE
    WHEN pd.TransactionEffDate < pd.TransactionDate THEN cast(pd.TransactionDate as DATE)
    WHEN pd.TransactionEffDate < pd.EffectiveDate THEN cast(pd.EffectiveDate as DATE)
    ELSE cast(pd.TransactionEffDate as date)
    END   
    ,CONVERT(VARCHAR, pd.EffectiveDate, 101)
    ,CONVERT(VARCHAR, pd.ExpirationDate, 101)
    ,CASE
        WHEN LEFT(PD.POLICYNUM, 3) = 'ORV'
        THEN 'Palomar Value Select OR'
        WHEN LEFT(PD.POLICYNUM, 3) = 'VSE'
        THEN 'Palomar Value Select CA'
        WHEN LEFT(PD.POLICYNUM, 3) = 'WAV'
        THEN 'Palomar Value Select WA'
        ELSE 'Palomar'
     END
    ,CASE
       WHEN pd.TransactionCode = 'EN' THEN CONVERT(VARCHAR, th.TransactionEffectiveDt, 101)
       ELSE ''
     END
    ,CONVERT(VARCHAR, DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, th.transactiondt) + 1, 0)), 101)
    ,CASE
       WHEN pd.TransactionEffDate < CAST(CONVERT(VARCHAR, pd.TransactionDate, 101) AS SMALLDATETIME) THEN CONVERT(VARCHAR, pd.TransactionDate, 101)
       WHEN pd.TransactionEffDate < pd.EffectiveDate THEN CONVERT(VARCHAR, pd.EffectiveDate, 101)
       ELSE CONVERT(VARCHAR, pd.TransactionEffDate, 101)
     END
) lc
ORDER  BY lc.[Date], lc.[Carrier], lc.[Direct Ceded Written Premium]

With the parameter that I have, it would only show up until November. However, I would like it to show the whole year, up to December at in this case, even if there are no data there since I didn't pick the enddate variable to be december. I attached an example screenshot of what it should look like when exported to excel.

Excel Sample

Upvotes: 0

Views: 76

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

Just to give you an idea:

declare @tbl TABLE(ID INT IDENTITY,SomeValue VARCHAR(100),SomeDate DATE);
INSERT INTO @tbl VALUES('Some date in March',{d'2016-03-05'}),('Some date in June',{d'2016-06-30'});

WITH AllMonths AS
(
              SELECT 1 AS MonthIndex
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
    UNION ALL SELECT 11
    UNION ALL SELECT 12
)
SELECT MonthIndex
      ,t.*
FROM AllMonths
LEFT JOIN @tbl AS t ON MONTH(t.SomeDate)=MonthIndex

The result

1   NULL    NULL                NULL
2   NULL    NULL                NULL
3   1       Some date in March  2016-03-05
4   NULL    NULL                NULL
5   NULL    NULL                NULL
6   2       Some date in June   2016-06-30
7   NULL    NULL                NULL
8   NULL    NULL                NULL
9   NULL    NULL                NULL
10  NULL    NULL                NULL
11  NULL    NULL                NULL
12  NULL    NULL                NULL

There are many ways to create a tally table

  • CTE with ROW_NUMBER()
  • A list like in my example
  • A physical table

It is a good idea to maintain a numbers/DATE table!

In a previous answer I showed one way to create such a table.

Upvotes: 1

Related Questions