datetime formula - need help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

datetime formula - need help

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2011-11-03 : 04:48:08

Dear all,

im having some trouble with the following:

i need to extract from a database a calculation on the last 6 months based on the current date.

for example if today is 3 of nov i need to update in a table a value for may, june, july, aug, sept, oct.

each month has a corresponding column names [1st month], [2nd month] .... [6th month]

i though of it like this for the select statement:

select product, sum (field) 1st_month from table where -- and here is my problem how to tell to sql that i need to take oct then sept and so on until i get all 6 month. The report will run whenever the user want to so. if current date is between 1 and 31 ( or 30 or 28) then it must take the last month finished and so on until all 6 are selected.

Any ideas are welcome.

Thanks in advance!

Best regards!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:52:03

is [1st month], [2nd month] .... [6th month] columns already present or do you want to generate them in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tempus
Starting Member

47 Posts

Posted - 2011-11-03 : 05:10:32

I allready have the outputs,

but i think i found the solution:

SELECT SC07003, SUM (SC07004)
FROM SC070100
WHERE DATEPART(m, SC07002) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(y,SC07002) = DATEPART(y, DATEADD(m, -1, getdate()))
AND SC07003 LIKE 'MP%'
GROUP BY SC07003

sc07003 - product code
sc07004 - value field
sc07002 - datetime field

it's working as intended.

Thank you verry much for the interest in solving my issue.

Will come back with more :)

Thanks again.

Best wishes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 05:29:52

where are you getting month figures in different columns here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tempus
Starting Member

47 Posts

Posted - 2011-11-03 : 05:38:19

i thought of it like this per each month :

when the report is run first there is a delete:

delete from BT_safety_stock_mp_average_demand_rm

-- clears up the data

then the updates are comming:

--1

UPDATE BT_safety_stock_mp_average_demand_rm
SET [1st month] = progen
FROM ( SELECT SC07003 cod, SUM (SC07004) progen
FROM SC070100
WHERE DATEPART(m, SC07002) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(y,SC07002) = DATEPART(y, DATEADD(m, -1, getdate()))
AND SC07003 LIKE 'MP%' and SC07001 = '01' AND SC07007 LIKE '6%' AND SC07006 NOT LIKE '411%'
GROUP BY SC07003 ) jenquai
WHERE [product code] = cod

UPDATE BT_safety_stock_mp_average_demand_rm
SET [1st month] = 0
WHERE [1st month] is null

--2

UPDATE BT_safety_stock_mp_average_demand_rm
SET [2nd month] = progen
FROM ( SELECT SC07003 cod, SUM (SC07004) progen
FROM SC070100
WHERE DATEPART(m, SC07002) = DATEPART(m, DATEADD(m, -2, getdate()))
AND DATEPART(y,SC07002) = DATEPART(y, DATEADD(m, -2, getdate()))
AND SC07003 LIKE 'MP%' and SC07001 = '01' AND SC07007 LIKE '6%' AND SC07006 NOT LIKE '411%'
GROUP BY SC07003 ) jenquai
WHERE [product code] = cod

UPDATE BT_safety_stock_mp_average_demand_rm
SET [2nd month] = 0
WHERE [2nd month] is null

--3

UPDATE BT_safety_stock_mp_average_demand_rm
SET [3rd month] = progen
FROM ( SELECT SC07003 cod, SUM (SC07004) progen
FROM SC070100
WHERE DATEPART(m, SC07002) = DATEPART(m, DATEADD(m, -3, getdate()))
AND DATEPART(y,SC07002) = DATEPART(y, DATEADD(m, -3, getdate()))
AND SC07003 LIKE 'MP%' and SC07001 = '01' AND SC07007 LIKE '6%' AND SC07006 NOT LIKE '411%'
GROUP BY SC07003 ) jenquai
WHERE [product code] = cod

UPDATE BT_safety_stock_mp_average_demand_rm
SET [3rd month] = 0
WHERE [3rd month] is null

--4

UPDATE BT_safety_stock_mp_average_demand_rm
SET [4th month] = progen
FROM ( SELECT SC07003 cod, SUM (SC07004) progen
FROM SC070100
WHERE DATEPART(m, SC07002) = DATEPART(m, DATEADD(m, -4, getdate()))
AND DATEPART(y,SC07002) = DATEPART(y, DATEADD(m, -4, getdate()))
AND SC07003 LIKE 'MP%' and SC07001 = '01' AND SC07007 LIKE '6%' AND SC07006 NOT LIKE '411%'
GROUP BY SC07003 ) jenquai
WHERE [product code] = cod

UPDATE BT_safety_stock_mp_average_demand_rm
SET [4th month] = 0
WHERE [4th month] is null

--5

UPDATE BT_safety_stock_mp_average_demand_rm
SET [5th month] = progen
FROM ( SELECT SC07003 cod, SUM (SC07004) progen
FROM SC070100
WHERE DATEPART(m, SC07002) = DATEPART(m, DATEADD(m, -5, getdate()))
AND DATEPART(y,SC07002) = DATEPART(y, DATEADD(m, -5, getdate()))
AND SC07003 LIKE 'MP%' and SC07001 = '01' AND SC07007 LIKE '6%' AND SC07006 NOT LIKE '411%'
GROUP BY SC07003 ) jenquai
WHERE [product code] = cod

UPDATE BT_safety_stock_mp_average_demand_rm
SET [5th month] = 0
WHERE [5th month] is null

--6

UPDATE BT_safety_stock_mp_average_demand_rm
SET [1st month] = progen
FROM ( SELECT SC07003 cod, SUM (SC07004) progen
FROM SC070100
WHERE DATEPART(m, SC07002) = DATEPART(m, DATEADD(m, -6, getdate()))
AND DATEPART(y,SC07002) = DATEPART(y, DATEADD(m, -6, getdate()))
AND SC07003 LIKE 'MP%' and SC07001 = '01' AND SC07007 LIKE '6%' AND SC07006 NOT LIKE '411%'
GROUP BY SC07003 ) jenquai
WHERE [product code] = cod

UPDATE BT_safety_stock_mp_average_demand_rm
SET [6th month] = 0
WHERE [6th month] is null

-----

UPDATE BT_safety_stock_mp
SET [std average demand] = stdavgdemand
FROM ( SELECT [product code] cod, STDEV([1st month],[2nd month],[3rd month],[4th month],[5th month],[6th month]) stdavgdemand
FROM BT_safety_stock_mp_average_demand_rm
GROUP BY [product code]) alibaba2
WHERE [product code] = cod

-- finally i actually need a standard deviation for last
6 months . this is how i though of it , but if there is a more simple way please tell me. im here to learn :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources