February 8, 2010 at 3:49 am
Dear All,
I am very quite new to sql please help me in this would be very kindfull to them.;-)
1. I have a table of data which has values ( in_createddate,in_catid) where in_catid can be 1,2,3,4
2. when i pass the year and month it should give me values in tabular fomat with count of cat and each day of month
eg.
input parameter are year 2010 and month would be 1 i.e. January
then expecting an output like this
DayCat1Cat2Cat3Cat4
11230
28200
32210
40000
50000
.
.
3011020
the days should be as per month as per provided per year.
Regards,
Suhail
Please help me
February 8, 2010 at 4:57 am
So, you wanted to convert rows into columns and do a COUNT on the rows. This type of requirement/concept is called CrossTab or PIVOT.
Generally, this can done using two common methods.
1. Using dynamic SQL to generate conditional or case based columns.
2. Using dynamic SQL and SQL 2005 feature PIVOT operator.
These concepts are explained very well in the following articles.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
Please read through it and get back in case of any questions.
--Ramesh
February 8, 2010 at 5:42 am
No , the ouptut should be some like that..
one idea is to have for loop with each day in month and get count of each category with function but i want optimized way of doing this. can you please help. it is not pivoting
February 8, 2010 at 6:20 am
Did you really read through the articles? If yes, can you post what exactly did you tried?
Though I can write the query for you, but I really want you to know and understand the concepts behind it.
And when you post your query, make sure to add CREATE TABLE and INSERT TABLE statements of the relevant tables.
--Ramesh
February 9, 2010 at 12:17 am
I got result from this querry but need missing days also
select day(createdate) as days,count(cat1)as Cat1,count(cat2)as cat2,count(cat3)as cat3 from in_catidview
where year(createdate)=2010
group by day(createdate)
order by day(createdate)asc
cat1,cat2,cat3,cat4 have values of 1,2,3,4.
It will return output in grid where counter of cards created as per their categories on perticular day only available
Output (Available)
input year = 2010
month = 2
day cat1 cat2 cat3 cat4
3 1 2 2 4
25 2 0 4 6
I need the days where no card is added
Expecting output
day cat1 cat2 cat3 cat4
1 0 0 0 0
2 0 0 0 0
3 1 2 2 4
.
.
25 2 0 4 6
.
.
30 0 0 0 0
[/b]
can any body help me in this
February 9, 2010 at 12:56 am
You'll first need to generate all the necessary dates. You can either to this dynamically (e.g. using a CTE - see BOL) or use a calendar table. For the solution below I used the latter to keep things simple.
I called your table table1.
The calendar table is called table2 and looks like this:
CREATE TABLE table2 (d date);
Insert into table2 all the dates you will need.
if I understood your requirements correctly, the query you need to solve your problem is:
SELECT [t2].[d] AS [Day]
,SUM(CASE WHEN [t1].[in_catid] = 1 THEN 1
ELSE 0
END) AS Cat1
,SUM(CASE WHEN [t1].[in_catid] = 2 THEN 1
ELSE 0
END) AS Cat2
,SUM(CASE WHEN [t1].[in_catid] = 3 THEN 1
ELSE 0
END) AS Cat3
,SUM(CASE WHEN [t1].[in_catid] = 4 THEN 1
ELSE 0
END) AS Cat4
FROM [dbo].[table2] AS t2
LEFT JOIN [dbo].[table1] AS t1
ON [t2].[d] = [t1].[in_createddate]
WHERE MONTH([t2].[d]) = 1
AND YEAR([t2].[d]) = 2010
GROUP BY [t2].[d] ;
February 9, 2010 at 1:52 am
No.
I am getting output perfectly with the querry i have provided but i need the missing days for example if cards are not added on some days like 6,7,8,9, 10 of particular month it is not displaying in the output.
how can i get those days visible but as cat values are 0 for them it should display in row with such values.
it is possible?
February 9, 2010 at 2:52 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply