Case statement with time
-
Hello everyone
I hope everyone's ok.
I have this query
and I'm trying to build a case statement like this:
SELECT
DISTINCT(CONCAT(DD.STORE_CODE,'/',DD.PROCESS)) AS PROCESS,
DH.TABLE_ID AS 'Table',
CAST( CONCAT(DH.OPENING_HOUR_H,':',DH.OPENING_HOUR_M) as time) AS 'ENTRY SESSION',
CAST(DATEADD(Minute, DATEDIFF(Minute, CAST( CONCAT(DH.OPENING_HOUR_H,':',DH.OPENING_HOUR_M) as time), CAST( CONCAT(DH.CLOSING_HOUR_H,':',DH.CLOSING_HOUR_M) as time)),0) AS time(0)) AS Duration,
DH.PERSONS AS 'People#',
(CASE
WHEN DH.OPENING_HOUR_H BETWEEN '10:00:00' AND '16:00:00' THEN 'Lunch'
WHEN DH.OPENING_HOUR_H BETWEEN '16:01:00' AND '19:00:00' THEN 'Snack'
ELSE 'Dinner'
END) AS 'Meal Type'
and unfortunately it shows me an error
Can anyone try to explain me what I'm missing here?
Thanks a lot in advance
-
I'm so sorry guys, wrong CASE
(CASE
WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 10 AND 16 THEN 'Lunch'
WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 16 AND 19 THEN 'Snack'
ELSE 'Dinner'
END) AS 'Meal Type
This one is correct now
Sorry for wasting your time
-
Sorry, but a picture of data is useless to us. Please post usable data, that is, a CREATE TABLE and INSERT statement(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
What is the data type of DH.OPENING_HOUR_H?
Please provide samples of the data in the field
- pedroccamara wrote:
I'm so sorry guys, wrong CASE
(CASE WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 10 AND 16 THEN 'Lunch' WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 16 AND 19 THEN 'Snack' ELSE 'Dinner' END) AS 'Meal Type
This one is correct now Sorry for wasting your time
Do you realise that you pasted an image of data which does not even contain some of the columns which you are referring to in the query? I've asked you in the past to post consumable sample data and this was your response:
Your definition of 'correct' is also suspect. Opening_Hour should probably already be an INT.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:pedroccamara wrote:
I'm so sorry guys, wrong CASE
(CASE WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 10 AND 16 THEN 'Lunch' WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 16 AND 19 THEN 'Snack' ELSE 'Dinner' END) AS 'Meal Type
This one is correct now Sorry for wasting your time
Do you realise that you pasted an image of data which does not even contain some of the columns which you are referring to in the query? I've asked you in the past to post consumable sample data and this was your response:
Your definition of 'correct' is also suspect. Opening_Hour should probably already be an INT.
If it's just an hour value, it really should be a tinyint.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:
If it's just an hour value, it really should be a tinyint.
You are dead right.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
Yes this time (like you've ask me) I add all the SQL except the FROM which I believe it was not important. I've also add an image of the data so you can see the result of the query.
Also, I've noticed that I was doing a wrong case statement, wrong column you know?
That's why I said "sorry 4 wasting all your time", you know? And I've showed the right column to be in the case statement.
Should I done it differently? How?
-
Hi Scott
Thank you for answer and although this issue is already solved, let me just tell you that this table (and all of them) I get it only by importing. I don't have any right access to the server, therefore I can't do any CREATE or INSERT. I can only read data...importing it
- pedroccamara wrote:
Should I done it differently? How?
Yes. Provide your sample data in the form of CREATE TABLE / INSERT statements.
If you do that, others can copy the text straight into SSMS and start working on a solution for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil,
I can't create table (nor store procedures for example or views) because I don't have any other database access except for reading. Normally I import data and start working from there...
- pedroccamara wrote:
Phil,
I can't create table (nor store procedures for example or views) because I don't have any other database access except for reading. Normally I import data and start working from there...
Here is a script, what happens if you run it?
DROP TABLE IF EXISTS #t1;
CREATE TABLE #t1
(
Process VARCHAR(10)
,[Table] INT
);
INSERT #t1
(
Process
,[Table]
)
VALUES
('1/88309629', 401);
SELECT t.Process
,t.[Table]
FROM #t1 t;If you do not have rights to run that, I suggest you consider installing Developer Edition locally & some of your development work there.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil
It let me do it
The thing is: I even don't know what I've done with this script. I don't have enough knowledge you know? I've spent these 3 years, only importing tables from the server and doing some ETL in it before starting to do some reports in PBI. I know I cannot do any views or store procedures (I wish) because of the access. I tried before
But, trying to understand you, what do you think I should have done instead of showing you my SQL for importing data? Any advice on this?
-
Well, maybe it's time you learned a bit more. My script is straightforward and does the following things:
- If a temporary table called #t1 exists, drop (delete) it
- Create a temporary table called #t1 with two columns, Process and Table, with string and integer datatypes.
- Insert a row of data to #t1
- Select the data from #t1
When you are doing this yourself, you will need to verify the datatypes allotted in item (2). You should be able to do this from within SSMS, assuming you can see your tables in object explorer.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
In answer to your last question, the best you can do is
a) Describe what you are trying to achieve and what you want help with
b) Provide sample data in the form of CREATE TABLE / INSERT (use temp tables)
c) Show us what you have tried (description and code) (based on the sample data provided)
d) Show us the results you would like (based on the sample data provided) (a screenshot from Excel is fine for this)
This allows others to paste your sample data into SSMS and attempt to come up with some code for you. It should then be easy for you to paste this code back into SSMS, modify it back to referencing your original table names and then hit F5
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply