ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,671 through 2,685 (of 10,143 total)

RE: query help SUM

ayh1 (1/28/2015)
I tried altering the third line to:
SUM(CASE WHEN a.AttendanceStatus = 2,3,4,5,6,7 THEN 1 ELSE 0 END) AS [Did not attend]
and I tried
replacing the commas with OR
SUM(CASE WHEN...
^{“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

January 28, 2015 at 4:57 am

#1773638
RE: Select Alternate number of rows

GilaMonster (1/28/2015)
Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0
That doesn't return alternate rows, it returns rows with odd values of BusinessEntityID. There's 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

January 28, 2015 at 4:56 am

#1773637
RE: Can't figure out a case selection

Here's another way:
;WITH Preaggregate AS (
SELECT Grp, [Result] = SUM(numLow) + SUM(numMedium) + SUM(numHigh) + SUM(numZeroScore)
FROM historyTable h
CROSS APPLY (
SELECT [Grp] = CASE
WHEN h.asAtDate > DATEADD(DAY,-7,CAST(GETDATE() AS DATE)) THEN...
^{“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

January 28, 2015 at 4:53 am

#1773636
RE: query help SUM

ayh1 (1/28/2015)
yep did not attend can correspond to 2,3,4,5,6,7
Good...so what do you think the query should look like?
^{“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

January 28, 2015 at 4:30 am

#1773630
RE: Select Alternate number of rows

Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0
Almost...
SELECT n, n%1, n%2
FROM (VALUES (1),(2),(3),(4),(5)) d (n)
^{“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

January 28, 2015 at 4:23 am

#1773628
RE: query help SUM

ayh1 (1/28/2015)
thanks seems to work if i wanted to add attendance status = 2,3,4,5,6,7 to the second line
SUM(CASE WHEN a.AttendanceStatus = 2 THEN 1 ELSE 0 END) AS...
^{“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

January 28, 2015 at 4:06 am

#1773623
RE: Select Alternate number of rows

Bhushan Kulkarni (1/28/2015)
Check this sample query without using % or mod
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
(SELECT *,ROW_NUMBER() OVER(Order by BusinessEntityId) AS RID FROM Person.Person )P
INNER JOIN (SELECT ROW_NUMBER() OVER(Order by BusinessEntityId) *...
^{“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

January 28, 2015 at 4:03 am

#1773622
RE: query help SUM

ayh1 (1/28/2015)
Hi
I have a query which works:
SELECT dbo.tblGP_Practices.OrganisationCode, SUM(dbo.tblAppointments.AttendanceStatus) AS [Total Attended], dbo.tblAppointments.AttendanceStatus
FROM dbo.tblGP_Practices INNER JOIN
...
^{“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

January 28, 2015 at 3:57 am

#1773620
RE: Select Alternate number of rows

satishkmr538 (1/28/2015)
A simple Query to select alternate rows from a table ?
Use ROW_NUMBER() and select either even or odd numbers.
^{“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

January 28, 2015 at 3:46 am

#1773618
RE: Are the posted questions getting worse?

Lynn Pettis (1/27/2015)
... Mark one off, 31 days on the calendar to go. 31 days on the calendar to go, 31 days to go, ...
A standard month. It will be...
^{“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

January 28, 2015 at 3:45 am

#1773617
RE: unpivot sorting error

cyberdaemon (1/27/2015)
Good day,
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i...
^{“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

January 28, 2015 at 2:33 am

#1773600
RE: Calculate quarters and year between two dates

-- You may need to extend the range of the virtual tally table.
SELECT [QuarterName] = 'Q' + DATENAME(qq,DATEADD(QQ,n,startdate)) + ' ' + CAST(YEAR(DATEADD(QQ,n,startdate)) AS VARCHAR(4))
FROM (SELECT startdate = '01/JUN/2011', enddate...
^{“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

January 27, 2015 at 6:06 am

#1773361
RE: Repeat contact cursor

Jeff Moden (1/26/2015)
Details are in the code...
--===== Conditionally drop the working table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#QU','U') IS NOT NULL
...
^{“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

January 27, 2015 at 1:57 am

#1773323
RE: All Monday date of each month.

Gosh :blush: thanks Koen! I like what Jeff's done with 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

January 27, 2015 at 1:47 am

#1773322
RE: Are the posted questions getting worse?

Grant Fritchey (1/26/2015)
ChrisM@Work (1/26/2015)
Grant Fritchey (1/26/2015)
ChrisM@Work (1/26/2015)
Your wish etc etc PM me if you're sure and we'll fix it up.
Thanks! But I've already spent the money to build the other...
^{“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

January 26, 2015 at 6:43 am

#1773082

Viewing 15 posts - 2,671 through 2,685 (of 10,143 total)