January 12, 2009 at 2:52 pm
Hi...
i have a table #tbl1
i want the count of records from that #tbl1 for yesterday,day before yesterday and the day before that using a single Query is it possible to get that way ...
like
01/11/09----43
01/10/09----98
01/09/09----47
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
January 12, 2009 at 3:43 pm
NEI (Not Enough Information). What does the data look like in your table? How about some sample data and the DDL for the table. What have you done so far to answer your question (what does your code look like at the moment)?
January 12, 2009 at 3:43 pm
Something like
select convert(varchar(10), yourdate, 101), count(*)
from #tbl1
group by convert(varchar(10), yourdate, 101)
should get you what you need. Not the most efficient code but it should work to get data grouped by day.
January 12, 2009 at 4:19 pm
Yeah tats right but all i want is to pivot that data....
when i am querying it ..i am getting
select convert(varchar(12),cr_date,101),count(*) from #tbl1
where cr_date >= convert(varchar(12),getdate()-7)
group by convert(varchar(12),cr_date,101)
i am getting the output as
01/11----43
01/10----34
01/09----31
01/08----49
01/07---50
01/06----432
01/05----67
but all i need is 01/11 01/10 01/09 01/08 01/07 01/06 01/015
43 34 31 49 50 432 67
i need to pivot it i tried but i coudnt do it i should do it daily so i cound thard code the value splease do let me know...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
January 12, 2009 at 4:25 pm
First, you just changed your requirements from your initial post. Nothing there indicated a pivot. As the more data is added for additional days, does the number of columns created in the pivot change or is the query constrained some how to limit the number of days being displayed?
Second, try searching this site for dynamic pivot. I seem to recall an article discussing how to do that, as it appears from first glance that this may be what you are going to need.
January 12, 2009 at 4:40 pm
Lynn.. I have posted sample data this is the way i need it...
create table #temp2
(
crdate datetime,
Ban int
)
insert into #temp2 values ( '01/10/2009',1432)
insert into #temp2 values ( '01/11/2009',4134)
insert into #temp2 values ( '01/11/2009',4321)
insert into #temp2 values ( '01/05/2009',2432)
insert into #temp2 values ( '01/12/2009',1343)
insert into #temp2 values ( '01/12/2009',3421)
insert into #temp2 values ( '01/05/2009',1323)
insert into #temp2 values ( '01/07/2009',1432)
insert into #temp2 values ( '01/06/2009',1324)
insert into #temp2 values ( '01/06/2009',1234)
insert into #temp2 values ( '01/12/2009',4341)
insert into #temp2 values ( '01/05/2009',3241)
insert into #temp2 values ( '01/12/2009',1867)
insert into #temp2 values ( '01/12/2009',8741)
insert into #temp2 values ( '01/07/2009',7681)
insert into #temp2 values ( '01/07/2009',6591)
insert into #temp2 values ( '01/06/2009',8681)
insert into #temp2 values ( '01/12/2009',6581)
insert into #temp2 values ( '01/11/2009',6591)
insert into #temp2 values ( '01/05/2009',6591)
insert into #temp2 values ( '01/07/2009',5681)
insert into #temp2 values ( '01/12/2009',5681)
insert into #temp2 values ( '01/09/2009',6551)
insert into #temp2 values ( '01/09/2009',8551)
select convert(varchar(12),crdate,101),count(*) from #temp2
where crdate >= convert(varchar(12),getdate()-7,101)
group by convert(varchar(12),crdate,101)
-- this would be my ouput
01/05/20094
01/06/20093
01/07/20094
01/09/20092
01/10/20091
01/11/20093
01/12/20097
--so every day i need to run this and i need to pivot this data and my desired output is
01/12/200901/11/200901/10/200901/09/200901/08/200901/07/200901/06/200901/05/2009
7 3 1 2 0 4 3 4
--How should i do it ....for tomorrow i should get the date from
01/13/2009 01/12/200901/11/200901/10/200901/09/200901/08/200901/07/200901/06/2009
any idea please do help me out
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
January 12, 2009 at 5:53 pm
Chris (1/12/2009)
Lynn.. I have posted sample data this is the way i need it...create table #temp2
(
crdate datetime,
Ban int
)
insert into #temp2 values ( '01/10/2009',1432)
insert into #temp2 values ( '01/11/2009',4134)
insert into #temp2 values ( '01/11/2009',4321)
insert into #temp2 values ( '01/05/2009',2432)
insert into #temp2 values ( '01/12/2009',1343)
insert into #temp2 values ( '01/12/2009',3421)
insert into #temp2 values ( '01/05/2009',1323)
insert into #temp2 values ( '01/07/2009',1432)
insert into #temp2 values ( '01/06/2009',1324)
insert into #temp2 values ( '01/06/2009',1234)
insert into #temp2 values ( '01/12/2009',4341)
insert into #temp2 values ( '01/05/2009',3241)
insert into #temp2 values ( '01/12/2009',1867)
insert into #temp2 values ( '01/12/2009',8741)
insert into #temp2 values ( '01/07/2009',7681)
insert into #temp2 values ( '01/07/2009',6591)
insert into #temp2 values ( '01/06/2009',8681)
insert into #temp2 values ( '01/12/2009',6581)
insert into #temp2 values ( '01/11/2009',6591)
insert into #temp2 values ( '01/05/2009',6591)
insert into #temp2 values ( '01/07/2009',5681)
insert into #temp2 values ( '01/12/2009',5681)
insert into #temp2 values ( '01/09/2009',6551)
insert into #temp2 values ( '01/09/2009',8551)
select convert(varchar(12),crdate,101),count(*) from #temp2
where crdate >= convert(varchar(12),getdate()-7,101)
group by convert(varchar(12),crdate,101)
-- this would be my ouput
01/05/20094
01/06/20093
01/07/20094
01/09/20092
01/10/20091
01/11/20093
01/12/20097
--so every day i need to run this and i need to pivot this data and my desired output is
01/12/200901/11/200901/10/200901/09/200901/08/200901/07/200901/06/200901/05/2009
7 3 1 2 0 4 3 4
--How should i do it ....for tomorrow i should get the date from
01/13/2009 01/12/200901/11/200901/10/200901/09/200901/08/200901/07/200901/06/2009
any idea please do help me out
Okay, I'm braindead today. You can read more about cross tabs and pivots by reading the two articles linked below at the bottom of my signature block. The second one discusses dynamic pivots, but I'd read both of them.
If you have questions after that, let us know.
January 13, 2009 at 1:10 am
Chris (1/12/2009)
Lynn.. I have posted sample data this is the way i need it...
You could use the RAC utility to generate the columns.
Exec Rac
@transform='count(*) as cnt',
@rows='case when Ban>0 then 1 end as dummy',
@pvtcol='convert(varchar(12),crdate,101) as newdate',
@from='#temp2',
@defaults1='y',@racheck='y',@shell='n',@datelen='12',@style='101',
-- Rac will fill in all dates in the range (in descending order)
@forcerange='01/12/2009 & 01/05/2009',
@select='select _pvtcols_ from rac'
01/12/2009 01/11/2009 01/10/2009 01/09/2009 01/08/2009 01/07/2009 01/06/2009 01/05/2009
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7 3 1 2 4 3 4
You can create a simple procedure. Just pass in the starting date.
declare @d1 datetime,@d2 datetime
set @d1='01/13/2009'
set @d2=convert(varchar(12),@d1-7,101)
Exec Rac
@transform='count(*) as cnt',@user1=@d1,@user2=@d2,
@rows='case when Ban>0 then 1 end as dummy',
@pvtcol='convert(varchar(12),crdate,101) as newdate',
@from='#temp2',
@defaults1='y',@racheck='y',@shell='n',@datelen='12',@style='101',
@forcerange='@user1 & @user2',
@select='select _pvtcols_ from rac'
01/13/2009 01/12/2009 01/11/2009 01/10/2009 01/09/2009 01/08/2009 01/07/2009 01/06/2009
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7 3 1 2 4 3
visit RAC @
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply