时间戳

首页 > 解决方案 > Want month wise net amount and mandays in sql when a date range is selected fromdate to todate

sql - Want month wise net amount and mandays in sql when a date range is selected fromdate to todate

问题描述

SELECT  T0.[U_BRANCH] 'branch',sum(T1.[U_NETAMT]) 'Net amount',sum((CASE when T1.[U_HRSWKD]>0 then 1 else 0 end)) 'mandays' 
FROM [dbo].[@MLD_OLBRATTD]  T0  
INNER JOIN [dbo].[@MLD_LABATTD1]  T1 ON T1.[DocEntry] = T0.[DocEntry] 
Where T0.[U_STARTDT]>=@FromDate and T0.[U_STARTDT]<=@ToDate and T1.[U_HRSWKD]>0
group by T0.U_BRANCH

branch   Net amount     mandays
TOONG   1483651.896266  5345
MRF     2480092.992900  11952
TEJO    1311481.288500  4834

想要按月查询这里的条件是 

Declare @FromDate As DateTime
Declare @ToDate   As DateTime
Set @FromDate='20190401'--{?FromDate}
Set @ToDate ='20190730'--{?ToDate}

标签： sqlsql-server

解决方案

尝试这个。

DATEPART函数将返回日期列中的特定部分。使用MONTH，您可以获得所需的输出。

SELECT  T0.[U_BRANCH] 'branch'
    ,DATEPART(MONTH,T0.[U_STARTDT]) [Month]
    ,sum(T1.[U_NETAMT]) 'Net amount'
    ,sum((CASE when T1.[U_HRSWKD]>0 then 1 else 0 end)) 'mandays' 
FROM [dbo].[@MLD_OLBRATTD]  T0  
INNER JOIN [dbo].[@MLD_LABATTD1]  T1 ON T1.[DocEntry] = T0.[DocEntry] 
Where T0.[U_STARTDT]>=@FromDate and T0.[U_STARTDT]<=@ToDate and T1.[U_HRSWKD]>0
group by T0.U_BRANCH,DATEPART(MONTH,T0.[U_STARTDT])

推荐阅读