Date Range
-
Hi All,
Can an one help me out in writing the below mentioned queries in a better way:
1) I have a query with a UNION of 2 select statements as mentioned below:
SELECT Empno, Sal FROM TABLE1
UNION
SELECT Empno, Sal FROM TABLE2
From the above query if i want to get the sum of Sal by grouping employee, how can i write it without using a JOIN ?
2) If i need to filter the data within a date range like for eg:
Select * from Sales where SellDate BETWEEN startDate AND endDate.
In the above query i should get the data starting from StartDate + 3days and EndDate - 3days.
Thanks in Advance.
Subhash
-
Subhash,
For the first problem, use the UNION query you made as if it were a table and do the GROUP BY on that... like this...
- SELECT d.EmpNo, SUM(d.Sal) AS TotalSal
(--Derived table "d" unions the required data
SELECT Empno, Sal FROM TABLE1
UNION ALL
SELECT Empno, Sal FROM TABLE2
) d End derived table "d"
GROUP BY d.EmpNo
ORDER BY TotalSal DESC
Also notice, I changed UNION to UNION ALL just in case the employee is making the same sales in both tables.
So far as the dates go, don't forget that there may be times on the sell date... so if you want all the sales on EndDate-3 to be included, you would actually have to do something like this...
SELECT *
FROM Sales
WHERE SellDate >= startDate+3
AND SellDate < endDate-2
The reason why you shouldn't use BETWEEN for this is that midnight on endDate-2 would be included in the date range.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - SELECT d.EmpNo, SUM(d.Sal) AS TotalSal
-
Thanks Jeff,
But was there any syntactical error in the first query, when i attempt the same its giving error :
"Incorrect syntax near 'D' "
Please find below my query too, i verified the relevant syntax in BOL also but i could not resolve it
SELECT D.CUSTNMBR,sum(d.SLSAMNT) AS Total
(
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'A%'
UNION ALL
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'B%'
 
D End derived table "D"GROUP BY D.CUSTNMBR
ORDER BY Total DESC
Can you please help me where i went wrong?
Regarding my second query:
I need the data which exist with Start Range and the consecutive next 2days and also End Range including prev 2 days.
For eg:
If Start Date is 01/08/2005, then i need to have data with StartDate exist from 1st to 3rd August and also assume EndDate is 15th August' 05 then data with 13th, 14th and 15th August.
Hope i explained correctly 🙁
Regards,
Subhash
-
The piece End derived table "D" should be preceeded by a -- (it's a comment, not part of the query)
SELECT D.CUSTNMBR,sum(d.SLSAMNT) AS Total
(
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'A%'
UNION ALL
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'B%'
 
D -- End derived table "D"GROUP BY D.CUSTNMBR
ORDER BY Total DESC
There's no need for a union here. Your initial request indicated that there were two tables involved. The above query only has one, with two different filters. The above query would be better as
SELECT CUSTNMBR,sum(SLSAMNT) AS Total
FROM RM20101
WHERE CUSTNMBR LIKE 'A%' OR CUSTNMBR LIKE 'B%'
GROUP BY CUSTNMBR
ORDER BY Total DESC
As for your date range, remember that dates in SQL include the times as well unless you take care to strip the time off when you're inserting the data.
That said, try this and see how it works.
SELECT * FROM tbl WHERE dt BETWEEN StartDate AND DATEADD(dd,3,StartDate) OR dt BETWEEN DATEADD(dd,-3,EndDate) AND EndDate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi There,
Yes i did consider it as a comment and also executed, but still gives the same error 🙁
Just for showing as an example i have used the same table name in the mentioned script, sorry for that. But in reality the data comes from two different tables and i'm using UNION for combining the data from both the select statements.
For more clarity, i am pasting the query below once again with different tables:
SELECT D.CUSTNMBR,sum(d.SLSAMNT)
(
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'A%'
UNION ALL
SELECT CUSTNMBR,SLSAMNT FROM PM20101
WHERE CUSTNMBR LIKE 'B%'
 
'D' GROUP BY D.CUSTNMBR
ORDER BY 2 DESC
Thanks in Advance.
Subhash
-
Subhash - you do not need the single quote around the D - also not sure why the 'end paranthesis' shows up as a "wink" but hopefully that's what you have there... a ')' - ??!
SELECT D.CUSTNMBR,sum(D.SLSAMNT)
(
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'A%'
UNION ALL
SELECT CUSTNMBR,SLSAMNT FROM PM20101
WHERE CUSTNMBR LIKE 'B%'
)D
GROUP BY D.CUSTNMBR
ORDER BY 2 DESC
**ASCII stupid question, get a stupid ANSI !!!** -
Hi All,
Thanks a lot for all the inputs.
Yes, 'FROM' is missing in the query and after correcting its executing.
The same is :
SELECT D.CUSTNMBR,sum(d.SLSAMNT)'Total' FROM
(
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'A%'
UNION ALL
SELECT CUSTNMBR,SLSAMNT FROM RM20101
WHERE CUSTNMBR LIKE 'B%'
 
D GROUP BY D.CUSTNMBR
ORDER BY 2 DESC
Regards,
Subhash
-
LOL - the evils of "copying and pasting"....
**ASCII stupid question, get a stupid ANSI !!!** -
I must be going blind. I stared at that statement for several minutes and didn't even notice the from was missing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Just to reassure you...you're not going blind...I stared at it for precisely one second because my focus was on the earlier post about including the comment line & getting an error....didn't even dream about looking at any other part of the sql statement...I think the focus is so much towards the "problem area" that we miss all else....
**ASCII stupid question, get a stupid ANSI !!!** -
Subhash,
I'm sorry I left out the the "--" on the comment
. It sure did make a mess of things. You all set now?--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply