error sqlserver and repeating fields

Posted by Klbaiju under Sql Server on 7/30/2013 | Points: 10 | Views : 701 | Status : [Member] | Replies : 2
Hi, I want to display booking of one month. following code should display the 31 days of month august. i have added 3 booking

but it shows 33 days .how to avoid this

declare @dte as datetime ='2013-08-01'
declare @StDt as Datetime = DATEADD(dd,-(DAY(GETDATE())-1),@dte)
declare @EnDt as datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dte)+1,0))
Declare @Cnt as int = datepart(dd,@EnDt)
Declare @inc as int = 0
Create table #temp (Month_date datetime)
while @inc < @cnt
begin
insert into #temp
select DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))
set @inc = @inc + 1
end
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(bus_id)
from busmaster
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @Query='SELECT CONVERT(VARCHAR(20), Month_date,106 )as tourdate ,'+ @cols +'
from (
select t.Month_date,b.bus_id,b.tour_date,b.[status],b.fare
from #temp t
left outer join Busdetails b on t.Month_date=b.tour_date
) x
pivot
(
max(status)
for bus_id in (' + @cols + ')
) p
order by Month_date'
exec(@query)
drop table #temp

Regards

Baiju




Responses

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
declare @dte as datetime ='2013-08-01' 

declare @StDt as Datetime = DATEADD(dd,-(DAY(GETDATE())-1),@dte) -- What is the purpose of this line of code...
declare @EnDt as datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dte)+1,0))

Declare @Cnt as int = datepart(dd,@EnDt)
Declare @inc as int = 0
SELECT @StDt, @EnDt, DATEDIFF( DD, @StDt, @EnDt)

Create table #temp (Month_date datetime)
while @inc < @cnt
begin
insert into #temp
select DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))
set @inc = @inc + 1
end
SELECT * FROM #temp
DROP TABLE #temp

By executing above code I got
StartDate	                 EndDate	                    NumberOfDays	

2013-07-03 00:00:00.000 2013-08-31 23:59:59.000 59


#Temp table has 1st August to 31st Aug dates...

Can you tell us the number of records from the following SQL query
select t.Month_date,b.bus_id,b.tour_date,b.[status],b.fare 

from #temp t
left outer join Busdetails b on t.Month_date=b.tour_date

May be you have more than one record in Busdetails for each tour_date

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Use Distinct Keyword to avoid the duplicate records...
SET @Query='SELECT DISTINCT CONVERT(VARCHAR(20), Month_date,106 )as tourdate ,'+ @cols +'
from (
select t.Month_date,b.bus_id,b.tour_date,b.[status],b.fare
from #temp t
left outer join Busdetails b on t.Month_date=b.tour_date
) x
pivot
(
max(status)
for bus_id in (' + @cols + ')
) p
order by Month_date'
exec(@query)
drop table #temp


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response