Count of same column appearing twice in the query

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS MagazineAdCount
FROM orderformlineitems
where product_variant_id = '4010436709979469536'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek MagazineAdCount
04/16/2007 04/23/2007 8
04/23/2007 04/30/2007 15
04/30/2007 05/07/2007 5

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS BannerAdCount
FROM orderformlineitems
where product_variant_id = '7453910328410493551'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek BannerAdCount
04/16/2007 04/23/2007 15
04/23/2007 04/30/2007 21
04/30/2007 05/07/2007 22

I WANT THE BELOW OUTPUT THRU A SINGLE QUERY.....

StartWeek EndWeek MagazineAdCount BannerAdCount

04/16/2007 04/23/2007 8 15
04/23/2007 04/30/2007 15 21
04/30/2007 05/07/2007 5 22

Can anyone help please ?

Thanks

You can join these 2 query as single query..

Select Data1.Startweek,

Data1.EndWeek,

Data1.MagazineAdCount,

Data2.BannerAdCount

From

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS MagazineAdCount

FROM orderformlineitems

where product_variant_id = '4010436709979469536'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data1

Join

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS BannerAdCount

FROM orderformlineitems

where product_variant_id = '7453910328410493551'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data2 On Data1.StartWeek = Data2.StartWeek And Data1.EndWeek = Data2.EndWeek

|||Thank you !!|||

Use following query:

Code Snippet

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

sum ( case product_variant_id when '4010436709979469536' then 1 else 0 end) AS MagazineAdCount,

sum ( case product_variant_id when '7453910328410493551' then 1 else 0 end) AS BannerAdCount

FROM orderformlineitems

where product_variant_id in ( '4010436709979469536', '7453910328410493551')

group by datediff(day,'19000101',d_datecreated)/7

order by StartWeek ASC

|||Thanks !!