Geek asks and answers

I need only min

I have a request that should return the lowest balance for this month for each identifier. The problem I get is to return multiple balances instead of the minimum balance on the Balance. I keep getting these results:

ID      Name   Month     Year  TodayMonth   TodayYear  BalMin 
1        A      4         12     4            2012      10,000.00 
1        A      4         12     4            2012      20,000.00

When I need to return the lowest balance:

ID      Name   Month     Year  TodayMonth   TodayYear  BalMin 
1        A      4         12     4            2012      10,000.00

Here is what I still have:

SELECT DISTINCT
     TOP (100) PERCENT History.ID, info.Name, DATEPART(mm, History.ReportDate) AS Month, DATEPART(yy,History.ReportDate) AS Year, DATEPART(mm, { fn CURDATE() }) AS TodayMonth, DATEPART(yy, { fn CURDATE() }) AS TodayYear, MIN(History.Balance) AS BalMin
    FROM         History LEFT OUTER JOIN Info ON History.ID = Info.ID
    WHERE     (DATEPART(yy, History.ReportDate) = DATEPART(yy, { fn CURDATE() })) AND (DATEPART(mm, History.ReportDate) = DATEPART(mm, 
                          { fn CURDATE() })) AND (History.Balance > 0)
    GROUP BY History.ID, History.ReportDate, Info.Name, History.Balance
    ORDER BY History.ID
+3
source share
3 answers

Yours group byshould not contain a balance (since you use it on it), and you should probably use the datepartone you use in select (otherwise you do not group by month). left joinit doesn't make sense since you've been looking for non-zero entries in your where where article. This might work:

SELECT History.ID,
    Info.Name,
    DATEPART(mm, History.ReportDate) AS [Month],
    DATEPART(yy, History.ReportDate) AS [Year],
    MIN(History.Balance) AS BalMin
FROM History
    JOIN Info ON History.ID = Info.ID
WHERE (DATEPART(yy, History.ReportDate) = DATEPART(yy, { fn CURDATE() }))
    AND (DATEPART(mm, History.ReportDate) = DATEPART(mm, { fn CURDATE() }))
    AND (History.Balance > 0)
GROUP BY History.ID,
    DATEPART(mm, History.ReportDate),
    DATEPART(yy, History.ReportDate),
    Info.Name
ORDER BY History.ID

distinct top, . distinct group by ( ), , top , , .

+3
SELECT DISTINCT
     TOP (100) PERCENT History.ID, info.Name, DATEPART(mm, History.ReportDate) AS Month, DATEPART(yy,History.ReportDate) AS Year, DATEPART(mm, { fn CURDATE() }) AS TodayMonth, DATEPART(yy, { fn CURDATE() }) AS TodayYear, MIN(History.Balance) AS BalMin
    FROM         History LEFT OUTER JOIN Info ON History.ID = Info.ID
    WHERE     (DATEPART(yy, History.ReportDate) = DATEPART(yy, { fn CURDATE() })) AND (DATEPART(mm, History.ReportDate) = DATEPART(mm, 
                          { fn CURDATE() })) AND (History.Balance > 0)
    GROUP BY History.ID, DATEPART(mm, History.ReportDate), Info.Name
    ORDER BY History.ID

, GROUP BY , , . .

0
SELECT  i.*, minbalance
FROM    (
        SELECT  id, MIN(balance) AS minbalance
        FROM    history
        WHERE   reportDate >= DATEADD(day, -DAY(GETDATE()), CAST(GETDATE() AS DATE))
                AND reportDate < DATEADD(month, 1, DATEADD(day, -DAY(GETDATE()), CAST(GETDATE() AS DATE)))
                AND 
        GROUP BY
                id
        ) b
JOIN    info i
ON      i.id = h.id
0
source

More articles:


All Articles