ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,166 through 3,180 (of 10,143 total)

RE: How to combine two codes

The output columns of #header (and #body?) in this new post are completely different to earlier posts. Are you simply describing the data types of these columns or proposing a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 8, 2014 at 3:03 am

#1727682
RE: getting null values while imporing from excel

visu.viswanath (7/8/2014)
Thanks for replay, i have one more doubt ...
Is there any limitation for file size through query not from SSIS package.
it gives error message as
"server is not responding...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 8, 2014 at 2:18 am

#1727670
RE: Inserting a dash between some values AND Adding a leading zero if only 5 characters

C-kweldude (7/7/2014)
...I have a column with integer records of 5 and 6 characters....
What datatype is the column? It's not clear if you are describing the datatype or the content.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 8, 2014 at 1:59 am

#1727662
RE: very slow performance

Here's an alternative using APPLY:
SELECT s.Sheet_ID, s.Sheet_Month, s.Sheet_Year, s.Sheet_Period,
s.Date_Start, s.Date_End, s.Date_Submit, s.Date_Approved, s.PD,
s.Emp_ID, s.Dept_ID, s.Emp_Status, s.Comments, s.Supervisor_ID,
s.Sheet_Status, s.Submit_ID, s.Approve_ID,
TOTAL_Sheet = SUM(r.Record_Hours) OVER(PARTITION BY r.Sheet_ID),
sp1.Supervisor_Name,
sp2.Submit_Name,
sp2.Submit_Email,
sp3.Approve_Name,
sp3.Approve_Email,
DAY(r.Record_Date)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 9:29 am

#1727448
RE: How to combine two codes

That's why I put the query which had the most columns first in the UNION. The NULL placeholders in query 2 don't need datatyping. You only have to investigate the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 8:02 am

#1727372
RE: How to combine two codes

hoseam (7/7/2014)
So what about the data types from #Header and #Body since we going to Union both queries?
Quite. I could guess them for you, or you could investigate what they...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 7:46 am

#1727362
RE: Minimum datetime value

Cadavre (7/7/2014)
ChrisM@Work (7/7/2014)
SELECT TOP 1 * FROM ( SELECT *, rn = ROW_NUMBER() OVER(ORDER BY c1) FROM #Test ) d ORDER BY CASE WHEN c3 = 1 THEN 0 ELSE 1 END, rn
Why does it...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 7:29 am

#1727352
RE: Display row even if data is null

You're welcome, no worries 🙂
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 6:13 am

#1727323
RE: Display row even if data is null

SELECT mc.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CD
FROM MG_COUNTRY mc
LEFT JOIN (VALUES
('Germany'),('Austria'),('Netherlands'),('Belgium'),('France'),
('Ireland'),('Sweden'),('Finland'),('Denmark'),('Norway')
) d (COUNTRY_DSC)
ON d.COUNTRY_DSC = mc.COUNTRY_DSC
LEFT JOIN MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 5:42 am

#1727318
RE: Display row even if data is null

SELECT d.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CD
FROM (VALUES
('Germany'),('Austria'),('Netherlands'),('Belgium'),('France'),
('Ireland'),('Sweden'),('Finland'),('Denmark'),('Norway')
) d (COUNTRY_DSC)
LEFT JOIN MG_COUNTRY mc
ON mc.COUNTRY_DSC = d.COUNTRY_DSC
LEFT JOIN MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 4:59 am

#1727311
RE: Display row even if data is null

SELECT alias.COUNTRY_DSC, alias.POL_LOCATION_CD, alias.TEU, mcc.TYPE_CD
FROM MG_COUNTRY mc
LEFT join MG_OFFICE mo
ON mo.COUNTRY_CD = mc.COUNTRY_CD
LEFT JOIN MG_BOOKING_DATASET mgd
on mgd.BOOKING_OFFICE_CD = mo.OFFICE_CD
AND alias.Booking_status_cd in ('H','F','I','P')
LEFT join MG_BOOKING_EQUIPMENT_REQ ber
on mgd.BOOKING_ID = ber.BOOKING_ID
LEFT join MG_CONTAINER_CODE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 4:42 am

#1727308
RE: Minimum datetime value

SELECT TOP 1 *
FROM (
SELECT *, rn = ROW_NUMBER() OVER(ORDER BY c1)
FROM #Test
) d
ORDER BY CASE WHEN c3 = 1 THEN 0 ELSE 1 END, rn
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 4:07 am

#1727306
RE: How to combine two codes

hoseam (7/7/2014)
I'm running the Code in Toad Oracle, so #temp doesn't work there.
Are you working with SQL Server or Oracle? This is the SQL Server 2008 forum section. Answers provided...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 3:02 am

#1727295
RE: Inserting value for identity column in a temporary table

kishore1a1216 (7/6/2014)
If the FROM table ALSO have identity Column then what we do ?
I am Facing this Problem ..
SELECT IDENTITY(int, 1, 1) AS 'RowID',* ,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 2:59 am

#1727294
RE: How to combine two codes

You could almost certainly do all of this work in a single query if you switched from old-style joins to ANSI-standard joins and learned how to use outer joins. Wikipedia...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 7, 2014 at 2:49 am

#1727291

Viewing 15 posts - 3,166 through 3,180 (of 10,143 total)