June 11, 2003 at 10:34 am
Please help!!!!! Confused and not sure how to write the code
An invoice was issued from 1/15/2002 through 3/9/2002.
I need to calculate number of days for each month and display final results as:
Inv# Jan02 Feb02 Mar02 Apr02
198 15 28 9 0
Do I need to use cursor in my logic?
As the same time would like to know in which months this invoice appeared.
Inv# Jan02 Feb02 Mar02 Apr02
198 1 1 1 0
June 11, 2003 at 11:13 am
Please post the structure of your Invoice table...it will help in determining the sql for your problem...
Thanks
June 11, 2003 at 1:24 pm
Table structure
Create Table #FB_Totals (Year char(4),Client# varchar(6),Invoice varchar(20),Jan dec(9,2),Feb dec(9,2),Mar dec(9,2),Apr dec(9,2),May dec(9,2),Jun dec(9,2),Jul dec(9,2),Aug dec(9,2),Sep dec(9,2),Oct dec(9,2),Nov dec(9,2),
Dec dec(9,2))
Output should be as follows for TotalDays
2002,123456,Days,15 , 28, 9, 0,0,0,0,0,0,0,0,0
Output should be as follows for Totalinvoices
2002,123456,Totals ,1 , 1, 1, 0,0,0,0,0,0,0,0,0
2002,1245676,Totals ,0 , 10, 15, 0,0,0,0,20,0,0,0,0
June 11, 2003 at 2:56 pm
This is a utility that I use to help with dates.
Basically I create a temp table...
create table #dateBuilder (date datetime, monthID int, month varchar(25),...
Then insert into the temp table:
insert into #DateBuilder
exec usp_functions_DateBuilder @startDate = '1/1/2003', @numberofDays=365
From there you could do all sorts of group by's and where clauses.
It might help...
create procedure usp_functions_DateBuilder
@StartDate datetime = null,
@numberOfDays int = 365,
@isOnlyWeekdays int = 0
AS
if @startDate is null
BEGIN
set @startDate = getDate()
END
set @startDate = convert(varchar,month(@startDate)) + '/' + convert(varchar,day(@startDate)) + '/' + convert(varchar,year(@startDate))
select a.ones + b.tens + c.hundreds + d.thousands id, dateadd(dd, a.ones + b.tens + c.hundreds + d.thousands, @startDate) as [Date],
datepart(m,dateadd(dd, a.ones + b.tens + c.hundreds, @startDate)) as [monthid],
datename(m,dateadd(dd, a.ones + b.tens + c.hundreds, @startDate)) as [month],
datepart(dw,dateadd(dd, a.ones + b.tens + c.hundreds, @startDate)) as [dayOfWeekid],
datename(dw,dateadd(dd, a.ones + b.tens + c.hundreds, @startDate)) as [dayOfWeek],
datename(q,dateadd(dd, a.ones + b.tens + c.hundreds, @startDate)) as [quarter],
datename(dy,dateadd(dd, a.ones + b.tens + c.hundreds, @startDate)) as [dayOfYear],
datename(wk,dateadd(dd, a.ones + b.tens + c.hundreds, @startDate)) as [Week]
from
(select 0 as ones UNION select 1 UNION select 2 UNION select 3 UNION select 4 UNION select 5 UNION select 6 UNION select 7 UNION select 8 UNION select 9 ) as a
join (select 0 as tens UNION select 10 UNION select 20 UNION select 30 UNION select 40 UNION select 50 UNION select 60 UNION select 70 UNION select 80 UNION select 90) as b on 1=1
join (select 0 as hundreds UNION select 100 UNION select 200 UNION select 300 UNION select 400 UNION select 500 UNION select 600 UNION select 700 UNION select 800 UNION select 900) as c on 1=1
join (select 0 as thousands UNION select 1000 UNION select 2000 UNION select 3000) as d on 1=1
where @numberOfDays > (a.ones + b.tens + c.hundreds + d.thousands)
and
'Saturday' != case when @isOnlyWeekDays = 1 then datename(dw,dateadd(dd, a.ones + b.tens + c.hundreds + d.thousands, @startDate))
else ''
end
and
'Sunday' != case when @isOnlyWeekDays = 1 then datename(dw,dateadd(dd, a.ones + b.tens + c.hundreds + d.thousands, @startDate))
else ''
end
order by id
June 12, 2003 at 1:50 pm
Thank you very much for your help.
I tried to go couple grouping in the where clause, but for some reason the results were not as expected and the date span is from more than a month.
An invoice was issued from 1/15/2002 through 3/9/2002."
First created a temp table and inserted only ID and date from the sp provided.
Secondly, tried to do calculate only for Jan2002 number of days by count(Date) from #datebuilder and was stuck.
Can this be done without using cursor?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply