I have 2 sql queries..Based on that 2 queries i am getting the 2 results.
The below is the query for Top Collection(New). Based on that i am getting the result (Top collection(new)(left side))
select userinfo.name, count(EMAIL.ID) As num from companyinfo COMPANY INNER JOIN emailinfo EMAIL
ON COMPANY.dataID = EMAIL.DataID inner join userinfo ON COMPANY.userid=userinfo.ID where
date >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)
AND date < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)
group by userinfo.name order by num desc -----new
Result:
name num(Total collection new)
xyz 24
The below is the query for Top Collection(New). Based on that i am getting the result(Top collection(Total with Duplicates)(right side))
select userinfo.name, sum(userdataloginfo.total_count) as num from userdataloginfo
inner join userinfo on userdataloginfo.userid=userinfo.id where
userdataloginfo.todaydate >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)
AND userdataloginfo.todaydate < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)
group by userdataloginfo.userid,userinfo.name order by num desc ----duplicate
Result:
name num(total collection duplicates)
xyz 50
I wanted to combine this both query into 1 so that i can get the result as:
name num(Total collection new) num(Total collection duplicates)
xyz 24 50
i made 1 query by combining this two queries.
select userinfo.name,SUM(userdataloginfo.total_count) as Excel,count(EMAIL.ID) As New,(sum(userdataloginfo.total_count)-count(EMAIL.ID)) as Duplicate
from companyinfo COMPANY INNER JOIN emailinfo EMAIL ON COMPANY.dataID = EMAIL.DataID inner join userinfo
ON COMPANY.userid=userinfo.ID inner join userdataloginfo on userdataloginfo.userid=userinfo.id where
userdataloginfo.todaydate >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)
AND userdataloginfo.todaydate < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)
And date >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)
AND date < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)
group by userinfo.name,userdataloginfo.total_count order by New desc
i am getting the result like:
name num(Total collection new) num(Total collection duplicates)
xyz 24 230
i am getting the num(Total collection new ) value correct but not getting the correct num(Total collection duplicates) value.