CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsqlite
Smartboy
Smartboy

Reputation: 1006

Need help in many to many query in Sql

i have two tables Coupon and Tags they have many to many relation now i want to create some sql view satisfy following situation

1- Need count with tag that how many coupons are there in every tag ( Done )

2- Need to get a list of tags sort by popular tags ( popular tags is the tag which have most coupons )

3- Need count of all coupon created in last 7 days on every tag

the sql view which i have written for first condition is following

SQLVIEW

Create VIEW [dbo].[TagsWithCount] AS

  SELECT Tag.Id as Id , Tag.Name as TagName ,Tag.description as Description,
   COUNT(*) AS CouponCount
   FROM Tags AS Tag
   inner JOIN couponsTotags c ON c.Tagid = Tag.id  
    GROUP BY Tag.Name , Tag.Id ,Tag.description
    GO

and it gives me the following results

enter image description here

how can i get , rest of things can anyone help me , Tell me if you need any other detail

Thanks in advance

Upvotes: 1

Views: 80

Answers (1)

Rahul Vasantrao Kamble
Rahul Vasantrao Kamble

Reputation: 251

    Create VIEW [dbo].[TagsWithCount] AS

    select * from  (SELECT Tag.Id as Id , Tag.Name as TagName ,Tag.description as Description,
       case when DATEDIFF(day, CreateDate, systemdate)<=7 then 'Its sevendays old'
else 'its more than 7 days old' end as SevendaysOldStatus,COUNT(*) AS CouponCount    
       FROM Tags AS Tag
       inner JOIN couponsTotags c ON c.Tagid = Tag.id  
        GROUP BY Tag.Name , Tag.Id ,Tag.description,CreateDate) temp
    order by CouponCount desc
        GO

Verify for sytem date not tried in any editor but hope will work for your requirement

Upvotes: 1

Related Questions