how to get data from table only date wise? currently it is giving date time wise

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi to all,
Here is a query which is I am using in my stored procedure.. I am fetching values from table date wise but it is giving me date time wise..

SQL

SELECT Last_Date,
 CASE WHEN ParameterId = 3 THEN Result END AS BPLaw,
 CASE WHEN ParameterId = 4 THEN Result END AS BPHigh
        FROM patient_progress_tracking_details
       WHERE     Patient_Id = 132
             AND Tenant_Id = 1
             AND Branch_Id = 1
             AND Last_Date BETWEEN '2012-11-01' AND '2013-11-31' 
             group by  Last_Date ;

the output which I am getting is:
Last_Date BPLaw BPHigh
11/16/2012 10:30:57 AM Null 120
11/16/2012 06:23:57 AM 65 null

I want both the result in a single column datewise...

Posted 10-Jan-13 21:52pm

Avinash_Pathak

Add a Solution

Comments

Om Prakash Pant 11-Jan-13 4:10am

both the result in a single column? not clear
Can you show us what output you need?

Tejal S 11-Jan-13 4:29am

Pls elaborate your quest?

Avinash_Pathak 11-Jan-13 4:29am

I want output like
Last_Date BPLow BPHigh
11/16/2012 65 120

instead of in two columns want in a single column

2 solutions

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)

Bhushan Shah1988 · Answer 1 · 2013-01-10T22:12:00

Solution 1

try something like below :

SQL

SELECT CONVERT(VARCHAR(10),Last_Date,103)as Last_Date,
 CASE WHEN ParameterId = 3 THEN Result END AS BPLaw,
 CASE WHEN ParameterId = 4 THEN Result END AS BPHigh
        FROM patient_progress_tracking_details
       WHERE     Patient_Id = 132
             AND Tenant_Id = 1
             AND Branch_Id = 1
             AND Last_Date BETWEEN '2012-11-01' AND '2013-11-31'
             GROUP BY  CONVERT(VARCHAR(10),Last_Date,103)

Posted 10-Jan-13 22:12pm

Bhushan Shah1988

Comments

Avinash_Pathak 11-Jan-13 4:28am

it is giving me only the first column result not the 2nd...I have already tried this...

Bhushan Shah1988 11-Jan-13 5:00am

please provide me script with some data.

Bhushan Shah1988 11-Jan-13 5:29am

in your previous query, you used patient_progress_tracking_details table and now you are giving structure of progress_tacking_other_details.

Avinash_Pathak 11-Jan-13 23:37pm

it was by mistake...here is the correct table....

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.1.61-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Create schema hms
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ hms;
USE hms;

--
-- Table structure for table `hms`.`patient_progress_tracking_details`
--

DROP TABLE IF EXISTS `patient_progress_tracking_details`;
CREATE TABLE `patient_progress_tracking_details` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`ParameterId` int(11) DEFAULT NULL,
`DiseaseId` int(11) DEFAULT NULL,
`Patient_Id` int(11) DEFAULT NULL,
`Branch_Id` int(11) DEFAULT NULL,
`Tenant_Id` int(11) DEFAULT NULL,
`Last_Date` datetime DEFAULT NULL,
`Result` varchar(20) DEFAULT NULL,
`Next_Due_Date` datetime DEFAULT NULL,
`Remarks` varchar(20) DEFAULT NULL,
`Parameter_Code` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `hms`.`patient_progress_tracking_details`
--

/*!40000 ALTER TABLE `patient_progress_tracking_details` DISABLE KEYS */;
INSERT INTO `patient_progress_tracking_details` (`Id`,`ParameterId`,`DiseaseId`,`Patient_Id`,`Branch_Id`,`Tenant_Id`,`Last_Date`,`Result`,`Next_Due_Date`,`Remarks`,`Parameter_Code`) VALUES
(1,3,1,132,1,1,'2012-11-17 00:00:00','65','2012-03-04 00:00:00','good',4),
(2,3,1,132,1,1,'2012-11-16 17:40:58','68','2012-11-28 17:41:16',NULL,5),
(3,3,1,132,1,1,'2013-11-15 17:41:32','70','2012-11-29 17:41:54',NULL,6),
(4,4,1,132,1,1,'2012-11-17 10:30:02','120',NULL,NULL,6),
(5,4,1,132,1,1,'2012-11-16 10:30:57','122',NULL,NULL,6),
(6,4,1,132,1,1,'2013-11-15 10:31:22','118',NULL,NULL,NULL);
/*!40000 ALTER TABLE `patient_progress_tracking_details` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

VIPR@T · Answer 2 · 2013-01-10T23:08:00

Hi,

you can use the UNION to get your desired result.
you can write the query following way.

SQL

SELECT Last_Date,
 CASE WHEN ParameterId = 3 THEN Result END AS BPLaw,
 '' AS BPHigh
        FROM patient_progress_tracking_details
       WHERE     Patient_Id = 132
             AND Tenant_Id = 1
             AND Branch_Id = 1
             AND Last_Date BETWEEN '2012-11-01' AND '2013-11-31' 
             group by  Last_Date ;

UNION

SELECT Last_Date,
 '' AS BPLaw,
 CASE WHEN ParameterId = 4 THEN Result END AS BPHigh
        FROM patient_progress_tracking_details
       WHERE     Patient_Id = 132
             AND Tenant_Id = 1
             AND Branch_Id = 1
             AND Last_Date BETWEEN '2012-11-01' AND '2013-11-31' 
             group by  Last_Date ;

I hope it will be help you.