Re: getting sum of units sold in preceeding weeks

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 05/11/04 

Date: Tue, 11 May 2004 21:06:34 +0530

>>can i have the same in diff columns...e.g. the table column heading would
be

yes possible, you only have to add group by clause. See following example

--sample data

create table tableA
(dt_column datetime,
sold_units int,
itemid int)

insert into tableA values(getdate() ,3, 1)
insert into tableA values(getdate() -1 ,3, 1)
insert into tableA values(getdate() -15,3, 1)
insert into tableA values(getdate() ,3, 2)
insert into tableA values(getdate() -15, 43, 2)

--required query

select itemid,sum(case when dt_column >= dateadd(dd, -7, {fn
current_date()} )
and dt_column <= getdate() then sold_units else 0 end) 'week1',
  sum(case when dt_column >= dateadd(dd, -15, {fn current_date()} ) and
dt_column < dateadd(dd, -7, {fn current_date()} ) then sold_units else 0
end) 'week2'
  from
  tableA
group by itemid 

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Relevant Pages

  • Re: getting sum of units sold in preceeding weeks
    ... Nikhil ... insert into tableA values,3, 1) ...
    (microsoft.public.sqlserver.mseq)
  • RE: Intersecting Date Ranges
    ... dateFrom datetime not null, ... insert into tableA (colName, dateFrom, dateTo) values('WILLIAM', '20040101', ...
    (microsoft.public.sqlserver.programming)
  • Re: Subselect Query Problem
    ... tableA LEFT JOIN tableB ... the result (of the inner join), it is logically re-introduced into the ... result, but since nothing match in tableB, whatever comes from tableB is ... The WHERE clause checks for the presence of this NULL to determine that f1 ...
    (microsoft.public.access.queries)
  • Re: Functions in SQL Server7
    ... You can't use two columns in a contains clause which caused my dilema in the ... >> Select IdentityInd, ColA, ColB ... >> From TableA Where ...
    (microsoft.public.sqlserver.programming)
  • Re: Datetime Semantics
    ... COL1 DATETIME DEFAULT GETDATE() ... INSERT INTO TableA DEFAULT VALUES ... INSERT INTO TableB DEFAULT VALUES ...
    (microsoft.public.sqlserver.programming)