error sqlserver and repeating fields

Posted by Klbaiju under Sql Server on 7/30/2013 | Points: 10 | Views : 1187 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

Latest Posts