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 !!
