Multiple Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Select Query

Author

Topic

manosgovind
Starting Member

12 Posts

Posted - 2013-01-04 : 00:42:45

Hi there,

I have three Select Queries each of which returns two rows. I associate this in a single Query as follows...

SELECT t1.Today, t2.Yest,t3.Earlier FROM
(SELECT Sum(EstimateMins) as Today from ProductionTbl
WHERE CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10),GETDATE(), 103)
union
Select ISNULL(Sum(EstimateMins),0) as Today_Comp from ProductionTbl EP
LEFT JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID
WHERE (CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10),GETDATE(), 103))
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed') )as t1 ,

(SELECT Sum(EstimateMins) as Yest from ProductionTbl
where CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103)
union
Select ISNULL( Sum(EstimateMins) ,0)as Yest_Comp from ProductionTbl EP
inner JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID
WHERE ( CONVERT(varchar(10),requiredBy,103) = CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103))
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed')) as t2,

(SELECT Sum(EstimateMins) as Earlier from ProductionTbl
where CONVERT(varchar(10),requiredBy,103) < CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103)
union
Select ISNULL( Sum(EstimateMins) ,0)as Yest_Comp from ProductionTbl EP
inner JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID
WHERE CONVERT(varchar(10),requiredBy,103) < CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 103)
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed')
)as t3
group by t1.Today, t2.Yest,t3.Earlier
order by t1.Today desc

When running this It returns 6 rows(4 rows return null values) . Iwant to get only 2 rows()

PLease help me to solve this.. Other way of wrting query for this output is also welcomed..

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-04 : 02:10:49

Can you post the result of above query and also expected output?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 02:33:30

there's lot of redundancy in posted code

you need only this


SELECT Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Today, 
Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) AND requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)  THEN EstimateMins ELSE 0 END) as Yest,
Sum(CASE WHEN requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Earlier
from ProductionTbl 
UNION ALL
SELECT Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Today_Comp, 
Sum(CASE WHEN requiredBy >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) AND requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)  THEN EstimateMins ELSE 0 END) as Yest_comp,
Sum(CASE WHEN requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN EstimateMins ELSE 0 END) as Earlier_Comp
from ProductionTbl 
INNER JOIN JobsTbl SJ ON SJ.JOB_ID=EP.ROWID 
AND (EP.retouchstatus='Completed' OR SJ.Job_Status='Completed')
WHERE requiredBy < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

If you want to identify rows as Comp and total values then return one more category column too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 02:34:24

also see this to understand the date logic used

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manosgovind
Starting Member

12 Posts

Posted - 2013-01-04 : 22:46:18

Thanks for ur reply visakh..

The Field "Requiredby" is a varchar field and I get an Exception as "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

I used Cast funtion to convert it into datetime, but still the error occurs..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-05 : 01:03:50

quote:
Originally posted by manosgovind

Thanks for ur reply visakh..

The Field "Requiredby" is a varchar field and I get an Exception as "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

I used Cast funtion to convert it into datetime, but still the error occurs..

the reason is simple. you don't have date values in unambiguos format. That's why its important to use proper datatype for all columns.
The best option is to make field datetime or date if possible.

Otherwise read this

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

and make sure all the values in field is in unambiguos format or consistent format before you apply casting over it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manosgovind
Starting Member

12 Posts

Posted - 2013-01-05 : 01:34:10

hi visakh..

It Works. Thanks for the information. The blog was very helpful to me.

Now i want to get the records in which the required by date is with in a time limit of the particular day.
For Ex. Get all the records of requireddate is with in 2012-01-04 02.00PM.

Thanks in Advance

manosgovind
Starting Member

12 Posts

Posted - 2013-01-05 : 01:37:01

quote:
Originally posted by bandi

Can you post the result of above query and also expected output?

--
Chandu

Thanks Chandu for the Interest. It worked

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-05 : 02:33:44

quote:
Originally posted by manosgovind

hi visakh..

It Works. Thanks for the information. The blog was very helpful to me.

Now i want to get the records in which the required by date is with in a time limit of the particular day.
For Ex. Get all the records of requireddate is with in 2012-01-04 02.00PM.

Thanks in Advance

are you looking at only records that happened in that single day upto 2 PM or do you want all records that happened from earlier days as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manosgovind
Starting Member

12 Posts

Posted - 2013-01-05 : 03:20:06

Actually the Requirement is , by the end of every day , A report should be genereated which shows the total works hours and Completed work hours for that particular day. There are 2 Shifts/ day 1st one ends by 2pm and 2nd one ends by 10 pm.
So we want to show the completed hours from morning 6 Am to 2pm and 2pm to 10 pm sepaerately. This for that particular day

Hope u r clear with this description

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-05 : 04:38:20

ok then you can use something like


SELECT SUM(CASE WHEN Datefield <= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN TotalHours ELSE 0 END) AS TotalHours1stShift,
SUM(CASE WHEN Datefield > DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN TotalHours ELSE 0 END) AS TotalHours2ndShift,
SUM(CASE WHEN Datefield <= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN CompletedHours ELSE 0 END) AS CompletedHours1stShift,
SUM(CASE WHEN Datefield > DATEADD(dd,DATEDIFF(dd,0,GETDATE()),'14:00') THEN CompletedHours ELSE 0 END) AS CompletedHours2ndShift
from Table
WHERE DateField >= DATEADD(dd,DATEDIF(dd,0,GETDATE()),0)
AND DateField < DATEADD(dd,DATEDIF(dd,0,GETDATE()),1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manosgovind
Starting Member

12 Posts

Posted - 2013-01-07 : 04:43:52

ok Visakh.. but the time in the date field is in 12 hr format.

The data may look like this 2012-12-01 03:00:00 PM.

could this be a problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 09:39:59

then that means datatype is not datetime. a field of type datetime will not store time part in 12 hr format.
If you've values in 12 hr format you need to first convert them to datetime datatype using CONVERT() function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manosgovind
Starting Member

12 Posts

Posted - 2013-01-08 : 04:16:55

ok Visakh.

It works... Once again Thanks for the replies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 04:20:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources