November 5, 2007 at 2:08 am
Hi frnds,
Could u pls help me in solving this:
I have a table where i store the Daily details of the stock ( Columns:
Id, Name,Qty,Date)..here in this table it has been populated for the entire year.
I have to get the Sum of the Qty for the Week1,Week2,Week3,Week4,Week5 for all the Stocks(stockid) for any Month given as input.
Pls help me out.
Thanks in Advance
Banu
November 5, 2007 at 2:09 am
Sample Information : [font="Times New Roman"][/font]
ID Name Qty Date(mm/dd/yyyy)
st001 Wuuu 18 01/02/2006
st001 Wuuu 20 01/03/2006
st001 Wuuu 18 01/04/2006
st001 Wuuu 20 01/05/2006
st001 Wuuu 18 01/06/2006
st001 Wuuu 20 01/09/2006
st001 Wuuu 18 01/10/2006
st001 Wxxx 20 01/02/2006
st002 Wxxx 40 01/03/2006
st002 Wxxx 15 01/04/2006
st002 Wxxx 40 01/05/2006
st002 Wxxx 15 01/06/2006
st002 Wxxx 40 01/09/2006
st002 Wxxx 15 01/10/2006
st002 Wxxx 40 01/11/2006
st002 Wxxx 15 01/12/2006
Similary there are n no. of stocks populated for the entire year
Input : YearMonth- 200601
The required Output
ID Name Week1 Week2 Week3 Week4 Week5
st001 Wuuu 94 38 - - -
st002 Wxxx 140 110 - - -
--
Thanks
Bhanu
November 5, 2007 at 3:02 am
Here is the one of the ways to achieve the weekly results
SELECTID, [Name],
SUM( CASE WHEN CEILING( DATEPART( DAY, [Date] ) / 7.0 ) = 1 THEN Qty ELSE 0 END ) Week1,
SUM( CASE WHEN CEILING( DATEPART( DAY, [Date] ) / 7.0 ) = 2 THEN Qty ELSE 0 END ) Week2,
SUM( CASE WHEN CEILING( DATEPART( DAY, [Date] ) / 7.0 ) = 3 THEN Qty ELSE 0 END ) Week3,
SUM( CASE WHEN CEILING( DATEPART( DAY, [Date] ) / 7.0 ) = 4 THEN Qty ELSE 0 END ) Week4,
SUM( CASE WHEN CEILING( DATEPART( DAY, [Date] ) / 7.0 ) = 5 THEN Qty ELSE 0 END ) Week5
FROMSomeTable
WHERE[Date] >= '20060601' AND [Date] <= '20060630'
GROUP BY ID, [Name]
--Ramesh
November 6, 2007 at 10:39 am
I doubt that is what the OP wants. That assumes that the first day of the month is always the first day of the week. Is that what you had in mind?
You need to ensure that DATEFIRST is set correctly for you... 1 = Monday, 7 = Sunday.
That determines the first day of the week.
Then from there, you can use datepart(week,thedate) to get the week number of the year.
/*
drop table #tblDate
CREATE TABLE #tblDate(Date DATETIME,Qty INT)
INSERT INTO [#tblDate] (
Date,Qty
)
VALUES('11/6/2007',10)
INSERT INTO [#tblDate] (
Date,Qty
)
VALUES('11/1/2007',20)
INSERT INTO [#tblDate] (
Date,Qty
)
VALUES('10/31/2007',30)
*/
SELECT -- ID,NAME,
DATEPART(YEAR,date) AS YEAR,DATEPART(MONTH,date) AS MONTH,
SUM(CASE DATEPART(week,date)-DATEPART(week,DATEADD(dd,1-DATEPART(DAY,date),date)) + 1 WHEN 1 THEN Qty ELSE 0 END) AS Week1,
SUM(CASE DATEPART(week,date)-DATEPART(week,DATEADD(dd,1-DATEPART(DAY,date),date)) + 1 WHEN 2 THEN Qty ELSE 0 END) AS Week2,
SUM(CASE DATEPART(week,date)-DATEPART(week,DATEADD(dd,1-DATEPART(DAY,date),date)) + 1 WHEN 3 THEN Qty ELSE 0 END) AS Week3,
SUM(CASE DATEPART(week,date)-DATEPART(week,DATEADD(dd,1-DATEPART(DAY,date),date)) + 1 WHEN 4 THEN Qty ELSE 0 END) AS Week4,
SUM(CASE DATEPART(week,date)-DATEPART(week,DATEADD(dd,1-DATEPART(DAY,date),date)) + 1 WHEN 5 THEN Qty ELSE 0 END) AS Week5
FROM [#tblDate]
GROUP BY DATEPART(YEAR,date),DATEPART(MONTH,date)
ORDER BY DATEPART(YEAR,date),DATEPART(MONTH,date)
November 7, 2007 at 5:46 am
The standard way of dealing with splitting up date values into periods is to use a date lookup table.
You need 1 row for every date likely to be in your system. You need 1 column for every period type. Often a date value is given a surrogate key tha tis used in the fact tables, as there is often no need to hold the actual date in the fact table.
e.g.
Date WeekNo FinancialPeriodNo DateKey
20/01/2000 4 1 1
21/01/2000 5 1 2
22/01/2000 5 2 3
You then have complete freedom to assign whatever week value you need to a given date, according to how your organisation calculates weeks. (e.g. week day 1 is Sunday or Monday, week 1 of year starts on Jan 1, or after week 52 of previous year has had 7 days, etc)
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply