month/year - 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)

month/year

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-03-31 : 15:23:37

i have to input month/year can u tell me what would the datatype of that column? - should it be datetime or just month/year? what would be the datatype?

i have to make function called getQuantityByMonth that when given the following inputs:
input: campaign,begin month/year, end month/year

returns the item sales for the items in a campaign with the following column headers...

output: period, periodint, campaign, itemnmbr, totalquantity

@startmonthyear datetime
@endmonthyear datetime

This is hardcoded, but the output of this is an example of what i would want. The purpose of this is to be able to do a multi-line graph.

select 'Oct 07' as Period, 200710 as PeriodInt, campaign,itemnmbr, totalquantity from

mycampaignitemquantity('COPD','10/1/2007','10/31/2007')

union

select 'Nov 07' as Period, 200711 as PeriodInt, campaign,itemnmbr, totalquantity from

mycampaignitemquantity('COPD','11/1/2007','11/30/2007')

union

select 'Dec 07' as Period, 200712 as PeriodInt, campaign,itemnmbr, totalquantity from

mycampaignitemquantity('COPD','12/1/2007','12/31/2007')

union

select 'Jan 08' as Period, 200801 as PeriodInt, campaign,itemnmbr, totalquantity from

mycampaignitemquantity('COPD','1/1/2007','1/31/2007')

union

select 'Feb 08' as Period, 200802 as PeriodInt, campaign,itemnmbr, totalquantity from

mycampaignitemquantity('COPD','2/1/2007','2/28/2007')

union

select 'Mar 08' as Period, 200903 as PeriodInt, campaign,itemnmbr, totalquantity from

mycampaignitemquantity('COPD','3/1/2007','3/31/2007')

order by itemnmbr, periodint

thanks for the help.

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-03-31 : 15:57:39

i have created function:

i jsut need the values of period and perioint like:
mmm yy, yyyymm

period periodint
----------------
Nov 07 200711
Dec 07 200712
Jan 08 200801
Feb 08 200802
Mar 08 200903
Oct 07 200710

how can i get this both column values like this in output.
plzzzzzz can somebody help me.

thanks in advance.

Create function getQuantityByMonth
(@Campaign varchar(50),
@startdate datetime,
@enddate datetime)
returns table
As
return
(
Select period, periodint, t.campaign,t.itemnmbr, totalquantity
from (
select distinct
c.campaign,
vs.docdate,
vs.itemnmbr,
sum(vs.quantity) as totalquantity,
p.indx

From mySectionPubs P

inner join mycampaign C
on C.indx = P.indx

inner join vwitemnmbrquantity vs
on vs.itemnmbr = p.sku

Where C.Campaign = @Campaign and (vs.docdate between @startdate and @enddate)

group by c.campaign, vs.docdate, vs.itemnmbr, p.indx

) as t

)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-31 : 16:34:27

Maybe something like this?

SELECT
	CONVERT(VARCHAR(3), CAST(CAST(periodint AS VARCHAR(6)) + '01' AS DATETIME), 0)
		+ ' '
		+ RIGHT(CAST(YEAR(CAST(CAST(periodint AS VARCHAR(6)) + '01' AS DATETIME)) AS VARCHAR(4)), 2) AS Period,
	periodint,
	t.campaign,
	t.itemnmbr,
	totalquantity
FROM
	(
		SELECT DISTINCT
			c.campaign,
			CAST(YEAR(vs.docdate AS INT)) * 100 + CAST(MONTH(vs.docdate) AS INT) AS PeriodInt,
			vs.itemnmbr,
			SUM(vs.quantity) AS totalquantity,
			p.indx
		FROM
			mySectionPubs P
		INNER JOIN 
			mycampaign C
			ON C.indx = P.indx
		INNER JOIN 
			vwitemnmbrquantity vs
			ON vs.itemnmbr = p.sku
		WHERE
			C.Campaign = @Campaign
			AND vs.docdate BETWEEN @startdate AND @enddate
		GROUP BY
			c.campaign,
			CAST(YEAR(vs.docdate AS INT)) * 100 + CAST(MONTH(vs.docdate) AS INT),
			vs.itemnmbr,
			p.indx
	) AS t

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-01 : 08:50:15

thanks for ur reply. but i m getting error of syntax near * 100 ) AS

but thanks.

so far i have done like this and i m getting correct results.

Create function getQuantityByMonth
(@Campaign varchar(50),
@startdate datetime,
@enddate datetime)
returns table
As
return
(
Select t.Period, t.PeriodInt, t.Campaign,t.Itemnmbr, sum(t.totalquantity) as TotalQuantity
from (
select distinct
REPLACE(RIGHT(CONVERT(VARCHAR(9), vs.docdate, 6), 6), '', ' ') AS Period,
CONVERT(VARCHAR(6), vs.docdate, 112) as PeriodInt,
c.campaign,
vs.itemnmbr,
sum(vs.quantity) as totalquantity,
p.indx

From mySectionPubs P

inner join mycampaign C
on C.indx = P.indx and C.section_id = p.section_id

inner join vwitemnmbrquantity vs
on vs.itemnmbr = p.sku

Where C.Campaign = @Campaign and (vs.docdate between @startdate and @enddate)

group by c.campaign, vs.docdate, vs.itemnmbr, p.indx

) as t

group by t.Itemnmbr,t.Period, t.PeriodInt, t.Campaign

)

period, periodint, campaign, itemnmbr, totalquantity
--------------------------------------------------------

Dec 07 200712 COPD 06-5840 5652.00000
Jan 08 200801 COPD 06-5840 3879.00000
Feb 08 200802 COPD 06-5840 4305.00000
Mar 08 200803 COPD 06-5840 4777.00000
Dec 07 200712 COPD 06-5841 5863.00000
Jan 08 200801 COPD 06-5841 3605.00000
Feb 08 200802 COPD 06-5841 4508.00000
Mar 08 200803 COPD 06-5841 4602.00000

Jan 08 200801 COPD 06-5845 1.00000
Feb 08 200802 COPD 06-5845 1.00000
Mar 08 200803 COPD 06-5845 5266.00000
Dec 07 200712 COPD KT-048 6.00000
Jan 08 200801 COPD KT-048 60.00000
Feb 08 200802 COPD KT-048 38.00000
Mar 08 200803 COPD KT-048 12.00000

no i want all months results means here in Dec07 for itemnmbr 06-5845 quantity would be 0.0 so its not showing..but still i want in output whole row with quantity-0...can anyone help me to figure it out.

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 09:03:59

Try this

SELECT		p.Period,
		p.PeriodInt,
		p.Campaign,
		p.ItemNmbr,
		SUM(p.Quantity) AS TotalQuantity
FROM		(
			SELECT		RIGHT(CONVERT(VARCHAR(9), vs.DocDate, 6), 6) AS Period,
					CONVERT(CHAR(6), vs.DocDate, 112) AS PeriodInt,
					c.Campaign,
					vs.ItemNmbr,
					vs.Quantity
			FROM		mySectionPubs AS p
			INNER JOIN	myCampaign AS c ON c.Indx = p.Indx
			INNER JOIN	vwItemNmbrQuantity AS vs ON vs.ItemNmbr = p.Sku
			WHERE		c.Campaign = 'COPD'
					AND vs.DocDate >= '20071001'
					AND vs.DocDate < '20080401'
		) AS p
GROUP BY	p.Period,
		p.PeriodInt,
		p.Campaign,
		p.ItemNmbr
ORDER BY	p.PeriodInt

E 12°55'05.25"
N 56°04'39.16"

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-01 : 09:16:46

thanks peso for quick reply. but still i m not getting that row..
i m getting results with ur code like:

Oct 07 200710 COPD 06-5840 53984.00000
Oct 07 200710 COPD 06-5841 53803.00000
Oct 07 200710 COPD 06-5845 1983.00000
Oct 07 200710 COPD KT-048 1008.00000
Nov 07 200711 COPD 06-5840 6592.00000
Nov 07 200711 COPD 06-5841 6488.00000
Nov 07 200711 COPD 06-5845 203.00000
Nov 07 200711 COPD KT-048 118.00000
Dec 07 200712 COPD 06-5840 6155.00000
Dec 07 200712 COPD 06-5841 6165.00000
Dec 07 200712 COPD KT-048 20.00000Jan 08 200801 COPD 06-5840 4080.00000
Jan 08 200801 COPD 06-5841 4007.00000
Jan 08 200801 COPD 06-5845 2.00000
Jan 08 200801 COPD KT-048 70.00000
Feb 08 200802 COPD 06-5840 5006.00000
Feb 08 200802 COPD 06-5841 4908.00000
Feb 08 200802 COPD 06-5845 2.00000
Feb 08 200802 COPD KT-048 56.00000
Mar 08 200803 COPD 06-5840 5377.00000
Mar 08 200803 COPD 06-5841 5302.00000
Mar 08 200803 COPD 06-5845 5266.00000
Mar 08 200803 COPD KT-048 36.00000

if u see RED color..u will know DEC 07 only gets 3rows instead of 4 .
its not showing itemnmbr - 06-485..row

thanks.

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-01 : 12:54:18

can anyone help me plz??

thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-01 : 13:05:53

It might be a good idea to supply some sample data and expected output. Here is a link that can show you what is expected: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-01 : 13:13:45

i have function
Create function getQuantityByMonth
(@Campaign varchar(50),
@startdate datetime,
@enddate datetime)
returns table
As
return
(
Select t.Period, t.PeriodInt, t.Campaign,t.Itemnmbr, sum(t.totalquantity) as TotalQuantity
from (
select distinct
REPLACE(RIGHT(CONVERT(VARCHAR(9), vs.docdate, 6), 6), '', ' ') AS Period,
CONVERT(VARCHAR(6), vs.docdate, 112) as PeriodInt,
c.campaign,
vs.itemnmbr,
sum(vs.quantity) as totalquantity,
p.indx

From mySectionPubs P

inner join mycampaign C
on C.indx = P.indx and C.section_id = p.section_id

inner join vwitemnmbrquantity vs
on vs.itemnmbr = p.sku

Where C.Campaign = @Campaign and (vs.docdate between @startdate and @enddate)

group by c.campaign, vs.docdate, vs.itemnmbr, p.indx

) as t

group by t.Itemnmbr,t.Period, t.PeriodInt, t.Campaign

)

when i m executing it:

select * from getQuantityByMonth('copd','12/1/2007','4/1/2008')
order by itemnmbr, periodint

i m getting 15rows

period, periodint, campaign, itemnmbr, totalquantity
--------------------------------------------------------
Dec 07 200712 COPD 06-5840 5652.00000
Jan 08 200801 COPD 06-5840 3879.00000
Feb 08 200802 COPD 06-5840 4305.00000
Mar 08 200803 COPD 06-5840 4777.00000
Dec 07 200712 COPD 06-5841 5863.00000
Jan 08 200801 COPD 06-5841 3605.00000
Feb 08 200802 COPD 06-5841 4508.00000
Mar 08 200803 COPD 06-5841 4602.00000
Jan 08 200801 COPD 06-5845 1.00000
Feb 08 200802 COPD 06-5845 1.00000
Mar 08 200803 COPD 06-5845 5266.00000
Dec 07 200712 COPD KT-048 6.00000
Jan 08 200801 COPD KT-048 60.00000
Feb 08 200802 COPD KT-048 38.00000
Mar 08 200803 COPD KT-048 12.00000

but i want 16rows:
period, periodint, campaign, itemnmbr, totalquantity
--------------------------------------------------------
Dec 07 200712 COPD 06-5840 5652.00000
Jan 08 200801 COPD 06-5840 3879.00000
Feb 08 200802 COPD 06-5840 4305.00000
Mar 08 200803 COPD 06-5840 4777.00000
Dec 07 200712 COPD 06-5841 5863.00000
Jan 08 200801 COPD 06-5841 3605.00000
Feb 08 200802 COPD 06-5841 4508.00000
Mar 08 200803 COPD 06-5841 4602.00000
Dec 07 200712 COPD 06-5845 0.0
Jan 08 200801 COPD 06-5845 1.00000
Feb 08 200802 COPD 06-5845 1.00000
Mar 08 200803 COPD 06-5845 5266.00000
Dec 07 200712 COPD KT-048 6.00000
Jan 08 200801 COPD KT-048 60.00000
Feb 08 200802 COPD KT-048 38.00000
Mar 08 200803 COPD KT-048 12.00000

i m not getting that single row..
any help would be appreciated.
thanks a lot!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-01 : 13:34:23

Read this as well.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Include create statements for all your tables and sample data to populate them. For example:

DECLARE @myCampaign TABLE
(
period varchar(10)
,periodInt varchar(10)
,Campaign varchar(20)
,itemNmbr varchar(20)
,totalQuantity int
)

INSERT INTO @myCampaign
SELECT 'Dec 07' ,'200712' ,'COPD' ,'06-5840' ,5652.00000 UNION ALL
SELECT 'Jan 08' ,'200801' ,'COPD' ,'06-5840' ,3879.00000 UNION ALL
SELECT 'Jan 08' ,'200801' ,'COPD' ,'06-5840' ,3879.00000 UNION ALL
SELECT 'Feb 08' ,'200802' ,'COPD' ,'06-5840' ,4305.00000 UNION ALL
SELECT 'Mar 08' ,'200803' ,'COPD' ,'06-5840' ,4777.00000 UNION ALL
SELECT 'Dec 07' ,'200712' ,'COPD' ,'06-5841' ,5863.00000 UNION ALL
SELECT 'Jan 08' ,'200801' ,'COPD' ,'06-5841' ,3605.00000 UNION ALL
SELECT 'Feb 08' ,'200802' ,'COPD' ,'06-5841' ,4508.00000 UNION ALL
SELECT 'Mar 08' ,'200803' ,'COPD' ,'06-5841' ,4602.00000 UNION ALL
SELECT 'Dec 07' ,'200712' ,'COPD' ,'06-5845' ,0.0 UNION ALL
SELECT 'Jan 08' ,'200801' ,'COPD' ,'06-5845' ,1.00000 UNION ALL
SELECT 'Feb 08' ,'200802' ,'COPD' ,'06-5845' ,1.00000 UNION ALL
SELECT 'Mar 08' ,'200803' ,'COPD' ,'06-5845', 5266.00000 UNION ALL
SELECT 'Dec 07' ,'200712','COPD' ,'KT-048' ,6.00000 UNION ALL
SELECT 'Jan 08' ,'200801' ,'COPD' ,'KT-048' ,60.00000 UNION ALL
SELECT 'Feb 08' ,'200802' ,'COPD' ,'KT-048' ,38.00000 UNION ALL
SELECT 'Mar 08','200803' ,'COPD' ,'KT-048', 12.00000

Jim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-01 : 13:41:20

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-01 : 13:42:28

thanks but i can't do like this as i have dates from 1999 till currentdate and i can't select all with union all..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 13:47:22

The point is that you DON'T have to do with UNION ALL (do not use UNION, since it is slower because of dupe checking).
Did you try my suggestion?

E 12°55'05.25"
N 56°04'39.16"

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-04-01 : 13:57:56

thanks to all for ur replies guys.

peso, i tried ur suggestion too..but i m notgetting results

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-01 : 14:30:34

Uh oh, here we go again! Funketun's smarter younger brother!

Jim

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-04-01 : 14:39:22

ri16,

When people ask you for additional information and you don't provide it, they can't help.

Post the information you were asked to provide, or quit wasting people's time with nonsense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources