CODEWITHSUNDEEP
sqlsql-server
Andreas
Andreas

Reputation: 137

get multiple sql results in one table

For statistics I would like to get the number of valid inapp purchase per airline code per months. For a single month I do already have a query:

SELECT 
    AirlineCode, 
   COUNT(DISTINCT AppUser.Id) AppUser
FROM [followme.aero.live].[dbo].[AppUser]
JOIN [followme.aero.live].[dbo].[UserInAppPurchase]
ON AppUser.Id = UserInAppPurchase.UserId
WHERE ValidTo > '2018-01-01' AND PurchaseDate < '2018-01-01'
GROUP BY AirlineCode 
ORDER BY AppUser DESC;

That would give me the January data. What I would like to get is the output not only for January but also for February, March and so on in 2018 in one single table.

How would I manage to do that?

Background:

In our database we have two tables, AppUser and UserInAppPurchase.

A user may or may not have inapp purchases in the table. A user may also have multiple entries here.

Important columns are AppUser.AirlineCode, UserInAppPurchase.ValidTo and UserInAppPurchase.PurchaseDate.

As I said, an AppUser may have multiple table entries or non at all. I'd like to check if one AppUser has at least one inapp purchase valid > than month x, then sum that per AirlineCode per month.

We are using mssql

Upvotes: 2

Views: 64

Answers (2)

EzLo
EzLo

Reputation: 14189

As each purchase might be computed for different months, you can't simply GROUP BY, you will have to somehow multiply each record for each month that applies before grouping.

You can use a CTE to generate month reference dates, then join against these. Note that I added the year of each month, you can omit if you don't need it (or just generate months up to 2018-12-01).

;WITH MonthsStartDates AS
(
    SELECT
        MonthStartDate = CONVERT(DATE, '2018-01-01')

    UNION ALL

    SELECT
        MonthStartDate = DATEADD(MONTH, 1, M.MonthStartDate)
    FROM
        MonthsStartDates AS M
    WHERE
        M.MonthStartDate <= '2020-01-01'
)
SELECT 
    AirlineCode,
    Year = DATEPART(YEAR, M.MonthStartDate),
    Month = DATEPART(MONTH, M.MonthStartDate),
    AppUser = COUNT(DISTINCT AppUser.Id)
FROM 
    [followme.aero.live].[dbo].[AppUser]
    INNER JOIN [followme.aero.live].[dbo].[UserInAppPurchase] ON AppUser.Id = UserInAppPurchase.UserId
    INNER JOIN MonthsStartDates AS M ON
        PurchaseDate < M.MonthStartDate AND
        ValidTo > M.MonthStartDate
GROUP BY 
    AirlineCode,
    DATEPART(YEAR, M.MonthStartDate),
    DATEPART(MONTH, M.MonthStartDate)

Upvotes: 1

Zeki Gumus
Zeki Gumus

Reputation: 1484

Add YEAR() and MONTH() for PurchaseDate and add them to GROUP BY clause. Also change PurchaseDate range if you want to see the data from '2018-01-01'. Check this sample : 

SELECT
    AirlineCode
    ,YEAR(PurchaseDate) PurchaseYear
    ,MONTH(PurchaseDate) PurchaseMonth
   ,COUNT(DISTINCT AppUser.Id) AppUser
FROM [followme.aero.live].[dbo].[AppUser]
JOIN [followme.aero.live].[dbo].[UserInAppPurchase]
    ON AppUser.Id = UserInAppPurchase.UserId
WHERE ValidTo > '2018-01-01'
AND ( PurchaseDate >= '2018-01-01' AND PurchaseDate <= '2018-12-31')
GROUP BY AirlineCode,
    ,YEAR(PurchaseDate) 
    ,MONTH(PurchaseDate) 
ORDER BY AppUser DESC;

Upvotes: 0

Related Questions