How to combine 3 separate SQL statements into 1 set of results-CodePudding

I have a SQL query that I am running 3 times (3 changing only the date range) and want to combine the result into one table, instead of running 3 different queries and trying to join outside of SQL. I am trying to find amount of times something has occurred per day/month/year. I'm running this in SQL Server.

I have 2 tables; one has the date of the transaction, and the other has the information I need (first 3 characters of the InventoryNumber table) so I am having to join these tables. I then want to group by the first 3 characters of the inventory number, and add the count in the column.

The end goal is to have something that looks like this:

InvNum | DayCount | MonthCount | YearCount
abc    | 2        | 10         | 40
def    | 0        | 2          | 6
xyz    | 0        | 0          | 2

Here is my query for the single day one. This works exactly like I want it to. But now, I want to add on there the counts for the Month, and then the counts for the year also. The only thing that would change between this query and the other 2 is the count column name, and then the date.:

SELECT 
    LEFT(LINEITEM.InventoryNumber, 3) AS InvNum, 
    COUNT(*) AS DailyCount
FROM
    INVOICE
INNER JOIN  
    LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE
    InventoryNumber IS NOT Null
    AND InventoryNumber != 'Misc'
    AND DateCreated > '5-20-2022'
GROUP BY 
    LEFT(LINEITEM.InventoryNumber, 3)
ORDER BY 
    InvNum ASC;

I have looked through some of the other questions similar to this, but their queries were much simpler and I was not able to replicate the same thing with my queries.

Any help is appreciated.

CodePudding user response：

Without knowing enough here, you should consider a conditional aggregation

Declare @D Date='2022-05-20';

SELECT InvNum     = LEFT(LINEITEM.InventoryNumber, 3)
      ,DayCount   = sum( case when DateCreated = @D then 1 else 0 end ) 
      ,MonthCount = sum( case when month(DateCreated) = month(@D) then 1 else 0 end ) 
      ,YearCount  = sum( case when year(DateCreated) = year(@D) then 1 else 0 end ) 
 FROM  INVOICE
INNER JOIN  LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber IS NOT Null
  AND InventoryNumber <> 'Misc'
  AND DateCreated >= format(@D,'yyyy-01-01')
GROUP BY LEFT(LINEITEM.InventoryNumber, 3)
ORDER BY InvNum ASC;

CodePudding user response：

Looks like you need conditional aggregation with three different start dates.

DECLARE @D date = '2022-05-20';
DECLARE @M date = '2022-04-21';
DECLARE @Y date = '2021-05-21';

SELECT InvNum     = LEFT(li.InventoryNumber, 3)
      ,DayCount   = COUNT(CASE WHEN i.DateCreated >= @D THEN 1 END)
      ,MonthCount = COUNT(CASE WHEN i.DateCreated >= @M THEN 1 END)
      ,YearCount  = COUNT(*)
 FROM INVOICE i
INNER JOIN LINEITEM li ON i.InvoiceID = li.InvoiceID
WHERE li.InventoryNumber <> 'Misc'
  AND i.DateCreated >= @Y
GROUP BY
  LEFT(li.InventoryNumber, 3)
ORDER BY
  InvNum;

Note that <> 'Misc' also excludes nulls, and that ASC is the default.

You can also calculate those start dates dynamically

DECLARE @D date = DATEADD(day, -1, CAST(GETDATE() AS date));
DECLARE @M date = DATEADD(month, -1, CAST(GETDATE() AS date));
DECLARE @Y date = DATEADD(year, -1, CAST(GETDATE() AS date));

CodePudding user response：

This maybe will do the work for you:

SELECT ISNULL(DailyQuery.InvNum, ISNULL(MonthlyQuery.InvNum, YearlyQuery.InvNum)) as InvNum,
       ISNULL(DailyCount,0) as DailyCount,
       ISNULL(MonthlyCount,0) as MonthlyCount,
       ISNULL(YearlyCount,0) as YearlyCount
FROM
(SELECT 
    LEFT(LINEITEM.InventoryNumber, 3) AS InvNum, 
    COUNT(*) AS DailyCount
FROM
    INVOICE
INNER JOIN  
    LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber != 'Misc'
    AND DateCreated > DATEADD(day, DATEDIFF(day, 0,  GETDATE()-1), 0) --first hour day before
GROUP BY 
    LEFT(LINEITEM.InventoryNumber, 3)
) DailyQuery
FULL JOIN 
(SELECT 
    LEFT(LINEITEM.InventoryNumber, 3) AS InvNum, 
    COUNT(*) AS MonthlyCount
FROM
    INVOICE
INNER JOIN  
    LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber != 'Misc'
    AND DateCreated > DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) --first day of current month
GROUP BY 
    LEFT(LINEITEM.InventoryNumber, 3) ) MonthlyQuery ON DailyQuery.InvNum = MonthlyQuery.InvNum
FULL JOIN 
(SELECT 
    LEFT(LINEITEM.InventoryNumber, 3) AS InvNum, 
    COUNT(*) AS YearlyCount 
FROM
    INVOICE
INNER JOIN  
    LINEITEM ON INVOICE.InvoiceID = LINEITEM.InvoiceID
WHERE InventoryNumber != 'Misc'
    AND DateCreated > DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) --first day of current month
GROUP BY 
    LEFT(LINEITEM.InventoryNumber, 3) ) YearlyQuery ON MonthlyQuery.InvNum = YearlyQuery.InvNum

CodePudding user response：

you need to write three queries each query results in these tables based on the column and date in the where clause

just change the column and date value in the where clause for each table

first query result

InvNum | DayCount 
abc    | 2        
def    | 0        
xyz    | 0

second query result:

InvNum  | MonthCount
abc     | 10        
def     | 2          
xyz     | 0

third query result:

InvNum | YearCount
abc    | 40
def    | 6
xyz    | 2

and then join these three tables on InvNum column