sql query for Retrieving based on date and month from two tables.

Question

0.00/5 (No votes)

See more:

TABLE1

                                          (Parsing Data)
          Packet               a    S_ID    c      yy   MM  DD  Hr  Min sec 
1080200062412220130505170801  1080  20006  24122  2013  05  05  17  08  01 
1080200062412320130505171628  1080  20006  24123  2013  05  05  17  16  28 
1080200062412420130505172121  1080  20006  24124  2013  05  05  17  21  21 
1080200062412520130508172512  1080  20006  24125  2013  05  08  17  25  12 
1080200062412620130508173013  1080  20006  24126  2013  05  08  17  30  13 
1080200062412720130509174002  1080  20006  24127  2013  05  09  17  40  02 
1080200062412820130509174935  1080  20006  24128  2013  05  09  17  49  35 
1080200062412920130510180010  1080  20006  24129  2013  05  10  18  00  10 
1080200072413020130510180627  1080  20007  24130  2013  05  10  18  06  27 
1080200072413120130510181255  1080  20007  24131  2013  05  10  18  12  55 
1080200072413220130510181948  1080  20007  24132  2013  05  10  18  19  48 
1080200072413320130510182836  1080  20007  24133  2013  05  10  18  28  36 
1080200072413420130511183826  1080  20007  24134  2013  05  11  18  38  26 
1080200072413520130511184803  1080  20007  24135  2013  05  11  18  48  03 
1080200072413620130511185616  1080  20007  24136  2013  05  11  18  56  16 
1080200082413620130511185616  1080  20008  24136  2013  05  11  18  56  16 
1080200072413620130603185616  1080  20007  24136  2013  06  03  18  56  16 
1080200082413620130604185616  1080  20008  24136  2013  06  04  18  56  16

TABLE2

s_Id      Center                   District
20005	Tirupati Municpal          Office	
20006	Madanapalli	           Chittoor	
20007	Bowenpally	           Hyderabad	
20008	MR Palli                   Chittoor 
20010	Sree Kalahathi             Chittoor

i want report based on MM(Month) and DD(Date).O/P Like Bellow Format

Center       District   S_ID             May                         June     
                                Su  Mon  Th  We  Th  Fr  Sa    Su  Mon  Th  We  Th  Fr  Sa
                                5   6    7   8   9   10  11    2   3    4   5   6   7   8 

Madanapalli  Chittoor   20006   3   0    0   5   2   1   0         
Bowenpally   Hyderabad  20007   0   0    0   0   0   4   3     0   1    0   0   0   0   0
MR Palli     Chittoor   20008   0   0    0   0   0   0   1     0   0    1   0   0   0   0

Posted 5-Jun-13 19:28pm

subbu a

Updated 5-Jun-13 20:22pm

Sunasara Imdadhusen

v3

Add a Solution

Comments

Maciej Los 6-Jun-13 1:51am

What have you done till now?
Where are you stuck?

What you mean: parsing data? Is it 1 or more columns?

Zoltán Zörgő 6-Jun-13 2:01am

As you mentioned yourself, you need a report. The reporting tool you are using is the key to your problem. The output you want cant't be/should not be built directly in t-sql.

Maciej Los 6-Jun-13 2:09am

It's almost possible to do it in T-SQL (using pivot table) ;) but with headers as a date ;)

Zoltán Zörgő 6-Jun-13 2:45am

Yes, almost :)
The most problematic seems to me the calendar header. I have no idea what the numbers below the weekdays could mean.

subbu a 6-Jun-13 2:11am

parsing data means:yes more columns.
i know retrieving two tables data using join condition. but i don't know how can i retrieving bellow format
May June
Su Mon Th We Th Fr Sa Su Mon Th We Th Fr Sa
5 6 7 8 9 10 11 2 3 4 5 6 7 8

and how can i displaying count.

Zoltán Zörgő 6-Jun-13 2:47am

Count? What count? In the output you posted, you have May/Su/5, but below you have 3/0/0 and so on. What count is that 3+0+0=5?

Maciej Los 6-Jun-13 3:59am

Using clear T-SQL it's not possible, but i'll share with you with similar solution. Please, be patient.

Sunasara Imdadhusen 6-Jun-13 2:21am

I think you have to do this job in reporting format e.g Crystal Report or DataReport or you have to write custom logic for the same

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-06-05T22:20:00

Please, read my comments to the question.

There is possible to achieve that using clear T-SQL language, but with a liitle bit different output. Have a look:

SQL

--declare temporary table for data -> table1
CREATE TABLE #tbl1 (Packet VARCHAR(255), a INT, S_ID INT, c INT, [yy] INT, [MM] INT, [DD] INT, [Hr] INT, [Min] INT, [sec] INT)
--insert data
INSERT INTO #tbl1 (Packet, a, S_ID, c, [yy], [MM], [DD], [Hr], [Min], [sec])
SELECT '1080200062412220130505170801' AS Packet, 1080 AS a, 20006 AS S_ID, 24122 AS c, 2013 AS [yy], 05 AS [MM], 05 AS [DD], 17 AS [Hr], 08 AS [Min], 01 AS [sec]
UNION ALL SELECT '1080200062412320130505171628', 1080, 20006, 24123, 2013, 05, 05, 17, 16, 28 
UNION ALL SELECT '1080200062412420130505172121', 1080, 20006, 24124, 2013, 05, 05, 17, 21, 21 
UNION ALL SELECT '1080200062412520130508172512', 1080, 20006, 24125, 2013, 05, 08, 17, 25, 12 
UNION ALL SELECT '1080200062412620130508173013', 1080, 20006, 24126, 2013, 05, 08, 17, 30, 13 
UNION ALL SELECT '1080200062412720130509174002', 1080, 20006, 24127, 2013, 05, 09, 17, 40, 02 
UNION ALL SELECT '1080200062412820130509174935', 1080, 20006, 24128, 2013, 05, 09, 17, 49, 35 
UNION ALL SELECT '1080200062412920130510180010', 1080, 20006, 24129, 2013, 05, 10, 18, 00, 10 
UNION ALL SELECT '1080200072413020130510180627', 1080, 20007, 24130, 2013, 05, 10, 18, 06, 27 
UNION ALL SELECT '1080200072413120130510181255', 1080, 20007, 24131, 2013, 05, 10, 18, 12, 55 
UNION ALL SELECT '1080200072413220130510181948', 1080, 20007, 24132, 2013, 05, 10, 18, 19, 48 
UNION ALL SELECT '1080200072413320130510182836', 1080, 20007, 24133, 2013, 05, 10, 18, 28, 36 
UNION ALL SELECT '1080200072413420130511183826', 1080, 20007, 24134, 2013, 05, 11, 18, 38, 26 
UNION ALL SELECT '1080200072413520130511184803', 1080, 20007, 24135, 2013, 05, 11, 18, 48, 03 
UNION ALL SELECT '1080200072413620130511185616', 1080, 20007, 24136, 2013, 05, 11, 18, 56, 16 
UNION ALL SELECT '1080200082413620130511185616', 1080, 20008, 24136, 2013, 05, 11, 18, 56, 16 
UNION ALL SELECT '1080200072413620130603185616', 1080, 20007, 24136, 2013, 06, 03, 18, 56, 16 
UNION ALL SELECT '1080200082413620130604185616', 1080, 20008, 24136, 2013, 06, 04, 18, 56, 16 
--declare temporary table for table2
CREATE TABLE #tbl2 (s_Id INT, Center VARCHAR(30), District VARCHAR(30))
--insert data
INSERT INTO #tbl2 (s_Id, Center, District)
SELECT 20005 AS s_Id, 'Tirupati Municpal' AS Center, 'Office' AS District
UNION ALL SELECT 20006, 'Madanapalli', 'Chittoor'
UNION ALL SELECT 20007, 'Bowenpally', 'Hyderabad'	
UNION ALL SELECT 20008, 'MR Palli', 'Chittoor'
UNION ALL SELECT 20010, 'Sree Kalahathi', 'Chittoor'
--declare table variable to store dates (to create dynamic columns)
CREATE TABLE #dates (mydate DATETIME)
--insert dates
INSERT INTO #dates (mydate)
SELECT DISTINCT CONVERT(DATETIME, CONVERT(VARCHAR,t1.yy) + '/' + CONVERT(VARCHAR,t1.MM) + '/' + CONVERT(VARCHAR,t1.DD))
FROM #tbl1 AS t1
ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR,t1.yy) + '/' + CONVERT(VARCHAR,t1.MM) + '/' + CONVERT(VARCHAR,t1.DD))
--declare variable to store column names
DECLARE @cols NVARCHAR(200)
--get columns from dates
SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(VARCHAR(10),mydate,120) 
			FROM #dates
			FOR XML PATH('')),1,2,'') + ']'
--declare variable:
--temporary table to store data 
DECLARE @dt VARCHAR(2000)
--temporrary table for pivot ;)
DECLARE @pt VARCHAR(MAX)
--build query 
SET @dt = 'SELECT t2.Center, t2.District, t1.c, t1.S_Id, CONVERT(DATETIME, CONVERT(VARCHAR,t1.yy) + ''/'' + CONVERT(VARCHAR,t1.MM) + ''/'' + CONVERT(VARCHAR,t1.DD)) AS mydate ' +
		'FROM #tbl1 AS t1 INNER JOIN #tbl2 AS t2 ON t1.S_Id = t2.s_Id'
--EXEC(@dt)
--build pivot table
SET @pt = 'SELECT Center, District, S_Id, ' + @cols + ' ' +
			'FROM (' + @dt + ') AS DT ' +
			'PIVOT(COUNT(c) FOR [mydate] IN(' + @cols + ')) AS PT'
--exec pivot 
EXEC(@pt)
--clean up ;)
DROP TABLE #tbl1
DROP TABLE #tbl2
DROP TABLE #dates

Result:

SQL

Center          District        S_Id    [2013-05-05]	[2013-05-08]	[2013-05-09]	[2013-05-10]	[2013-05-11]	[2013-06-03]	[2013-06-04]
Bowenpally	Hyderabad	20007	0		0		0		4		3		1		0
Madanapalli	Chittoor	20006	3		2		2		1		0		0		0
MR Palli	Chittoor	20008	0		0		0		0		1		0		1