SQL Query Ordering by 0

Question

I have the below query:

SELECT distinct sending_organisation AS [Supplier], t1.date, COUNT(Status) AS [Transactions],  Month(t1.Date) SO_Month, Year(t1.date) SO_Year
 FROM tx

   CROSS APPLY ( VALUES (CONCAT(left(DATENAME(mm, Date_Reported),3),' ',
                             DATENAME(yyyy, Date_Reported)),
                      DATEPART(mm, Date_Reported)
                     )
            ) t1 (Date, Morder)

where  Date >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE))
GROUP BY t1.date, t1.Morder, sending_organisation
ORDER BY sending_organisation, date DESC;

The query gives me a list of suppliers and their transactions for the last year. What I want is a dynamic query to alter the above and allow me to only select the last year's data for suppliers who have '0' transaction value for the current month.

Any ideas?

Answer 1

Determine which suppliers don't have a transaction for current month with NOT EXISTS, then join against their previous data.

;WITH Data AS 
(
    -- This is your query without modifications
    SELECT distinct 
        sending_organisation AS [Supplier], 
        t1.date, 
        COUNT(Status) AS [Transactions],  
        Month(t1.Date) SO_Month, 
        Year(t1.date) SO_Year
    FROM 
        tx
        CROSS APPLY ( VALUES (CONCAT(left(DATENAME(mm, Date_Reported),3),' ',
                             DATENAME(yyyy, Date_Reported)),
                      DATEPART(mm, Date_Reported)
                     )
            ) t1 (Date, Morder)
    where  
        Date >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE))
    GROUP BY 
        t1.date, 
        t1.Morder, 
        sending_organisation
),
AvailableSuppliers AS
(
    SELECT DISTINCT
        Supplier = sending_organisation
    FROM
        tx AS D
),
CurrentMonthTransactionZero AS
(
    -- Determine which sending_organisation have no transactions on the current month
    SELECT
        A.Supplier
    FROM
        AvailableSuppliers AS A
    WHERE
        NOT EXISTS (
            SELECT
                'no transaction for current month'
            FROM
                tx AS T
            WHERE
                A.Supplier = T.sending_organisation AND
                Date_Reported >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AND -- First day of current month
                Date_Reported <  DATEADD(DAY, 1, EOMONTH(GETDATE())) -- First day of next month
            )
)
SELECT
    D.*
FROM
    CurrentMonthTransactionZero AS D
    INNER JOIN CurrentMonthTransactionZero AS C ON D.Supplier = C.Supplier

Answer 2

Is this what you are after? So I have used dynamic SQL for this, any string in SQL server can be executed as a SQL command using EXECUTE/EXEC. I added your exact query as a string with a place holder value to add the extra where clause in the IF block after. Here I have left space for you to add your condition in on whether or not you want to add that extra where clause to your statement, if not then the place holder is removed so the select query can still run.

DECLARE @sql NVARCHAR(MAX)=
'SELECT DISTINCT sending_organisation AS [Supplier], 
                 t1.date, 
                 Count(status)  AS [Transactions], 
                 Month(t1.date) so_month, 
                 Year(t1.date)  so_year 
 FROM            tx 
 CROSS apply     ( VALUES 
                 ( 
                                                 Concat(LEFT(Datename(mm, date_reported),3),'' '', Datename(yyyy, date_reported)), 
                                 Datepart(mm, date_reported) 
                 ) 
                 ) t1 (date, morder) 
 WHERE           date >= Dateadd(month, -13, Cast(Getdate() AS DATE)) 
 placeHolder 
 GROUP BY        t1.date, 
                 t1.morder, 
                 sending_organisation 
 ORDER BY        sending_organisation, 
                 date DESC; ';
IF(condition)
BEGIN
    SET @sql = replace(@sql,'placeHolder','AND WHERE transactionValue = 0 ');
END
ELSE 
BEGIN
    SET @sql = replace(@sql,'placeHolder',' ');
END
EXEC(@sql);