grouping data in terms of time (weeks, months, year etc)
-
hi every buddy well I'm trying to make a few queries to make reports of my project here and I wana grup the data according to months, or weeks or years So I'm writing a query which is like
select m.manname+' '+prod.name as Item, SUM(s.salePrice) Total_Sale, dateName(month, b.billdate)+', '+datename(year,b.billdate) as sale_Month from sale s inner join purch p on s.purchid = p.purchid inner join manufacturer on p.manid = m.manid inner join products prod on p.catid = prod.catid inner join bill b on s.billid = b.billid group by m.manname+' '+prod.name, dateName(month, b.billdate)+', '+datename(year,b.billdate)So can u plz tell me how can we make groupings so that I can get seperate records for November 2007, December 2007 and so on u so forth u know. thanks in advance Rocky -
hi every buddy well I'm trying to make a few queries to make reports of my project here and I wana grup the data according to months, or weeks or years So I'm writing a query which is like
select m.manname+' '+prod.name as Item, SUM(s.salePrice) Total_Sale, dateName(month, b.billdate)+', '+datename(year,b.billdate) as sale_Month from sale s inner join purch p on s.purchid = p.purchid inner join manufacturer on p.manid = m.manid inner join products prod on p.catid = prod.catid inner join bill b on s.billid = b.billid group by m.manname+' '+prod.name, dateName(month, b.billdate)+', '+datename(year,b.billdate)So can u plz tell me how can we make groupings so that I can get seperate records for November 2007, December 2007 and so on u so forth u know. thanks in advance RockyYou can use DatePart method[^] to group your data
-
hi every buddy well I'm trying to make a few queries to make reports of my project here and I wana grup the data according to months, or weeks or years So I'm writing a query which is like
select m.manname+' '+prod.name as Item, SUM(s.salePrice) Total_Sale, dateName(month, b.billdate)+', '+datename(year,b.billdate) as sale_Month from sale s inner join purch p on s.purchid = p.purchid inner join manufacturer on p.manid = m.manid inner join products prod on p.catid = prod.catid inner join bill b on s.billid = b.billid group by m.manname+' '+prod.name, dateName(month, b.billdate)+', '+datename(year,b.billdate)So can u plz tell me how can we make groupings so that I can get seperate records for November 2007, December 2007 and so on u so forth u know. thanks in advance Rocky -
-
-
I got the grouping when i use date part and seperate the month and year now but still I cant get it like a single string 'November 2007' I have a bit awkward idea to make another view over it and concatenate those month and year columns in that Rocky
If you have it grouping correctly is the problem what is getting displayed? I did a query like below on an example table and the grouping seems to be working and so does the display: select count(1), max(dateName(month, invcDate)+', '+datename(year,invcDate)) from invc group by dateName(month, invcnbr)+', '+datename(year,invcnbr) I had to add the max around the month string since it wasn't an aggregate, but it displays and groups.
-
If you have it grouping correctly is the problem what is getting displayed? I did a query like below on an example table and the grouping seems to be working and so does the display: select count(1), max(dateName(month, invcDate)+', '+datename(year,invcDate)) from invc group by dateName(month, invcnbr)+', '+datename(year,invcnbr) I had to add the max around the month string since it wasn't an aggregate, but it displays and groups.
well I made grouping with another idea u knwo. I make a view like (name: my_view) select m.manname+' '+prod.name item ,SUM(s.saleprice) total_sale, dateName(month, billdate) mnth, datename(year,billdate) yr from sale inner join bill on s.billid = b.billid group by dateName(month, billdate), datename(year,billdate), m.manname+' '+prod.name etc and later I make a query on it like select Item, total_sale, mnth+', '+yr from myView Atfirst i used inline view for this but then I converted it into a seperate view. and now its groupin it correctly But do u have any ideas on grouping on a weekly basis ? Rocky