CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Cory Dee
Cory Dee

Reputation: 2891

Select With Multiple Where Criteria

I'm trying to do a select for a whole series of different criteria. I'm essentially going to take today's date, and use it against our invoice table to figure out how we're shaping up this year, compared to the last several years.

In the end, I essentially want to have columns for Year, Year To Date Sales, Quarter To Date Sales, and Month to Date Sales. I've started to create a loop for this, but the problem I'm running into is that it's executing several select's, and not returning me one dataset, but rather 3.

Is the best way to tackle this to build a virtual table, fill it with my results, and then select them out of that?

Here's what I have so far:

DECLARE @startDate date, @endDate date;
DECLARE @counter int = 0;


WHILE @counter < 3 BEGIN
    --set start and end dates
    SELECT @startDate = CONVERT(date, (CONVERT(nvarchar, YEAR(DATEADD(YEAR,-1 * @counter, CONVERT(date, GETDATE())))) + '-02-01'));
    SELECT @endDate = DATEADD(YEAR,-1 * @counter, CONVERT (date, GETDATE()));

    --select results
    SELECT YEAR(ib.DateDelivered) [Year], SUM(ib.TotalAmountLessFreight) [YTD Sale]
    FROM InvoiceBase ib
    WHERE ib.DateDelivered < @endDate
        AND ib.DateDelivered > @startDate
    GROUP BY YEAR(ib.DateDelivered);

    --increment
    SET @counter = @counter + 1;
END

Upvotes: 0

Views: 110

Answers (1)

user359040
user359040

Reputation:

The best way to do it would be in a single query. Try:

;with cte as
(select i.*, 
        dateadd(year, datediff(year,DateDelivered,getdate()), getdate()) [YearEnd]
 FROM InvoiceBase i)
select YEAR(DateDelivered) [Year],
       SUM(case when DateDelivered <= YearEnd 
                then TotalAmountLessFreight end) [YTD Sales],
       SUM(case when DateDelivered <= YearEnd and 
                     month(DateDelivered) >= floor((month(YearEnd)+2)/3)*3-2
                then TotalAmountLessFreight end) [QTD Sales],
       SUM(case when DateDelivered <= YearEnd and 
                     datediff(month, DateDelivered, YearEnd) = 0
                then TotalAmountLessFreight end) [MTD Sales]
FROM cte
GROUP BY YEAR(DateDelivered)

Upvotes: 1

Related Questions