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

Could anybody tell me how to convert vertical data into horizontal data?

Could anybody tell me how to convert vertical data into horizontal data?
I have a one-to-many relationship in sql server 2K
Product, ProductAccessory, one Product has many ProductAccessories.
My Table design is like this:
Table Product
{
ProdId int,
ProdNameId int,
...
}
Table ProductAccessory
{
ProdId int,
AccNameId int,
AccUnitId int,
...
}
Because one Production has at most 4 ProductAccessory
I want to use a SELECT statement OR function to return
ProdId, ProdNameId, AccNameId1, AccUnitId1, AccNameId2, AccUnitId2, AccNameId3, AccUnitId3, ...
Any help will be appreciated!
Thanks a lot
Joseph
YOU CAN USE cursor or Temp table whatever you like.|||What you are trying to do is called a pivot table or a cross-tab query. Unfortunately SQL Server 2000 does not include the ability to nativelyperform pivot table/cross-tab queries. You will either need to doit in the presentation layer (see the link inthis post) or you can go through some gyrations in your T-SQL code (see this article:Dynamic Cross-Tabs/Pivot Tables).
|||Got the answer
SELECT ProdId,
SUM(CASE AccSeqnoWHEN 1 THEN Amount ELSE 0 END) AS AccUnitId1,
SUM(CASE AccSeqnoWHEN 2 THEN Amount ELSE 0 END) AS AccUnitId2,
SUM(CASE AccSeqnoWHEN 3 THEN Amount ELSE 0 END) AS AccUnitId3,
SUM(CASE AccSeqnoWHEN 4 THEN Amount ELSE 0 END) AS AccUnitId4
FROM ProductAccessory
GROUP BY ProdId
Just add a fieled AccSeqno
Joseph