Pleae Help with Variable - if this is possible

Question

Post reply

Pleae Help with Variable - if this is possible

jdamian

SSC Journeyman

Points: 84
More actions
March 9, 2012 at 8:33 am

#265684

So I have a table that gets populated from a VB application (I didn't create it and don't have programming skills unfortunately) and the table has a column named 'Shift'.
This is for work shifts; we are a 24 hour operation so there are 3. Anyway, I am trying to create a view using some columns from this table but I am running into issues with the 'Shift' column. The issue is, I need the records based on the actual times OR the current shift we are in when this view is updated. So for example, if the table can have multiple records for the same production transaction for each shift; I want to be able to ONLY pull the record that is for the current shift whenever I update the view. So if I update the view at night 9:30pm, I only want shift 3, not 1, 2 AND 3 (which is what I get now). If I update the view on 2nd shift, I only want to see the record for 2nd shift and NOT 1st. Hopefully, I explained that well. There is a date/time column BUT the time is always 12:00AM. So I can't use this column for the time unfortunately but this table only holds the data for the current day so I don't think it will be a problem anyway. So the date is always correct but the time stays the same so I can't 'weed' records out. This must be setup in the application or something (I am not that familiar with it yet).
So I was trying to figure out a way I could make this work and I thought about maybe using variables somehow? Maybe IF ELSE logic defining shift times and comparing them to the current server time? I am only a tad bit familiar with this so any ideas to help lead me in the right direction I would greatly appreciate it. I was thinking something along the lines of:
If getdate() between @shift1_start and @shift1_end
select shift 1
elseif getdate() between @shift2_start and @shift2_end
select shift 2
elseif getdate() between @shift3_start and @shift3_end
select shift 3
end if
I'm sure this is not the correct syntax but just to give you an idea of what I was thinking and what I need.
Maybe a CASE statement? Then how would I go about declaring the variables (setting the time values for the shifts, etc.) into a query for my view?
I will be selecting several columns from the table in this query as well but the rest I am good with, its just this time/shift thing. Thanks for any help in advance! 🙂

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

I like the CASE idea... will this work for you?

DECLARE @now TIME

SET @now = GETDATE()

SELECT CASE WHEN @now BETWEEN CONVERT(TIME,'07:00:00') AND CONVERT(TIME,'15:00:00') THEN 'First Shift'

WHEN @now BETWEEN CONVERT(TIME,'15:00:00') AND CONVERT(TIME,'23:00:00') THEN 'Second Shift'

WHEN @now BETWEEN CONVERT(TIME,'23:00:00') AND CONVERT(TIME,'24:59:59') OR

@now BETWEEN CONVERT(TIME,'00:00:00') AND CONVERT(TIME,'07:00:00') THEN 'Third Shift'

ELSE 'ERROR'

END--, Col1, Col2 (other columns) FROM MyTable

_________________________________
seth delconte
http://sqlkeys.com

jdamian SSC Journeyman Points: 84 More actions · Answer 2

Hi, Thank you! This looks like it is what I am looking for. However, I forgot to mention that I am using SQL Server 2005 and I don't believe the TIME data type is valid. So must I use DATETIME or...any other suggestions. Also, thanks again for your help.

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

jdamian (3/12/2012)
Hi, Thank you! This looks like it is what I am looking for. However, I forgot to mention that I am using SQL Server 2005 and I don't believe the TIME data type is valid. So must I use DATETIME or...any other suggestions. Also, thanks again for your help.

You can replace TIME with DATETIME and it should work exactly the same. 🙂

_________________________________
seth delconte
http://sqlkeys.com

jdamian SSC Journeyman Points: 84 More actions · Answer 4

OK, I changed it...but I'm still having an issue and am a bit confused. Excuse my "newbness" but how can I now create such a query that would get the results I'm looking for?

How can I now apply this to a query so that it only selects records that contain a certain Shift number?

I actually have a column named 'Shift' in the table and when @now = first shift, I only want to retrieve the records where the value in the 'Shift' column is 1 and then the same for 2 and 3.

So if @now= second shift, I only want to retrieve records where the value in the 'Shift' column is 2 etc..

Thanks in advance 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

If you would like better answers, please take the time to read the first article I reference below in my signature block regarding asking for help. Follow the instructions on what and how to post. Be sure to include the expected resutls based on the sample data you provide.

Since we can't see what you see, you really need to give us as much as possible to be really helpful. Also, remember that we are volunteers giving of our own time to assist others needing help.

seth delconte SSCertifiable Points: 6388 More actions · Answer 6

jdamian (3/13/2012)
OK, I changed it...but I'm still having an issue and am a bit confused. Excuse my "newbness" but how can I now create such a query that would get the results I'm looking for?
How can I now apply this to a query so that it only selects records that contain a certain Shift number?
I actually have a column named 'Shift' in the table and when @now = first shift, I only want to retrieve the records where the value in the 'Shift' column is 1 and then the same for 2 and 3.
So if @now= second shift, I only want to retrieve records where the value in the 'Shift' column is 2 etc..
Thanks in advance 🙂

In that case, the IF construct will be simpler:

DECLARE @now DATETIME

SET @now = CONVERT(VARCHAR(8),GETDATE(),108)

IF @now BETWEEN '07:00:00' AND '15:00:00'

SELECT * FROM Shift_table WHERE Shift = 1

ELSE IF @now BETWEEN '15:00:00' AND '23:00:00'

SELECT * FROM Shift_table WHERE Shift = 2

ELSE IF @now BETWEEN '23:00:00' AND '24:59:59' OR @now BETWEEN '00:00:00' AND '07:00:00'

SELECT * FROM Shift_table WHERE Shift = 3

Since you are always working with today's date, converting time to varchar should work fine.

_________________________________
seth delconte
http://sqlkeys.com

jdamian SSC Journeyman Points: 84 More actions · Answer 7

Yes, my apologies. Here is the table syntax:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblTransactionDetail](

[ProductionDate] [smalldatetime] NULL,

[Shift] [tinyint] NOT NULL,

[LineNumber] [tinyint] NOT NULL,

[Cart] [smallint] NOT NULL,

[ProgramNumber] [smallint] NOT NULL,

[CavityPosition] [tinyint] NOT NULL,

[PartNumber] [varchar](12) NULL,

[Cavity] [varchar](3) NULL,

[Turns] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_Turns] DEFAULT ((0)),

[Production] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_Production] DEFAULT ((0)),

[LineInhibits] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_LineInhibits] DEFAULT ((0)),

[MI3] [smallint] NULL CONSTRAINT [DF_tblTransactionDetail_MI3] DEFAULT ((0)),

[MI4] [smallint] NULL CONSTRAINT [DF_tblTransactionDetail_MI4] DEFAULT ((0)),

[MI5] [smallint] NULL CONSTRAINT [DF_tblTransactionDetail_MI5] DEFAULT ((0)),

[MI6] [smallint] NULL CONSTRAINT [DF_tblTransactionDetail_MI6] DEFAULT ((0)),

[MI7] [smallint] NULL CONSTRAINT [DF_tblTransactionDetail_MI7] DEFAULT ((0)),

[MI8] [smallint] NULL CONSTRAINT [DF_tblTransactionDetail_MI8] DEFAULT ((0)),

[MI9] [smallint] NULL CONSTRAINT [DF_tblTransactionDetail_MI9] DEFAULT ((0)),

[MI10] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI10] DEFAULT ((0)),

[MI11] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI11] DEFAULT ((0)),

[MI12] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI12] DEFAULT ((0)),

[MI13] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI13] DEFAULT ((0)),

[MI14] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI14] DEFAULT ((0)),

[MI15] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI15] DEFAULT ((0)),

[MI16] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI16] DEFAULT ((0)),

[MI17] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI17] DEFAULT ((0)),

[MI18] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI18] DEFAULT ((0)),

[MI19] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI19] DEFAULT ((0)),

[MI20] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI20] DEFAULT ((0)),

[MI21] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI21] DEFAULT ((0)),

[MI22] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI22] DEFAULT ((0)),

[MI23] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI23] DEFAULT ((0)),

[MI24] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI24] DEFAULT ((0)),

[MI25] [smallint] NOT NULL CONSTRAINT [DF_tblTransactionDetail_MI25] DEFAULT ((0)),

[CarrierEqv_Line] [numeric](5, 4) NULL,

[FBE_PNMain] [float] NULL,

[CarrierEqv_CT] [numeric](5, 4) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

This table is populated from by a VB program. The ProductionDate column includes the date but the time is always 12:00:00AM. So of course, it does me no good. However, the Shift column is correct so I figured it would be best to pull the records by shift.

So what I am wanting to do is the following:

Select

ProductionDate, LineNumber, Cart, ProgramNumber, CavityPosition,

PartNumber, Cavity

from this table

BUT I want the query to only pull the records corresponding to the shift that is current when the query is ran. So if the query is ran during Shift 1, I only want the query to select records where Shift='1' and if the query is ran during Shift 2, I only want to retrieve records where Shift='2' etc..

I am creating this as a view for a new application that is being implemented and whenever the application is triggered to check this view, it should only see the records corresponding to the current shift in which it is when checking. This application will be pulling this data at random times during a 24 hour period. So for example, I don't want it to be 11:00pm and the application looking at records/data from Shift 1. I don't have to see the Shift column (it wouldn't hurt but not necessary), just know that if I were seeing the Shift column in the view that it would be the one for the current shift.

Hopefully, that clarifies a bit and makes it easier to understand what it is I am trying to do.

jdamian SSC Journeyman Points: 84 More actions · Answer 8

Oh yes, this is what I need!! I posted my last reply before I saw your latest IF post Seth. Thank you so much!! I really appreciate your help; very much! You have helped me a bunch, you rock and have made my day! I hope you have a great day!!:-D:-D

seth delconte SSCertifiable Points: 6388 More actions · Answer 9

jdamian (3/13/2012)
Oh yes, this is what I need!! I posted my last reply before I saw your latest IF post Seth. Thank you so much!! I really appreciate your help; very much! You have helped me a bunch, you rock and have made my day! I hope you have a great day!!:-D:-D

Cool, no prob!

_________________________________
seth delconte
http://sqlkeys.com

jdamian SSC Journeyman Points: 84 More actions · Answer 10

I don't know if you will see this but I was wondering if you may be able to help me out again:-D .

So I am now getting the data results I want from that query containing the IF ELSE statements. However, I would like to create a final view from these results with the use of a join on another table.

For example, when I run the query with the IF ELSE (the one in the above post), I want that data to be placed in a view or a table where I can retrieve it by using a whole new query or creating a new view.

I want a final outcome (view) to include data from those results AS WELL as data from a different table so I want to use a join to be able to achieve that. I don't think I can use a join how it is now.

Hopefully, I explained that OK.

seth delconte SSCertifiable Points: 6388 More actions · Answer 11

You already have the data stored in the Shift_table, so you shouldn't need another table or view - is there a join column in this other table that can be joined to a field in Shift_table?

_________________________________
seth delconte
http://sqlkeys.com

seth delconte SSCertifiable Points: 6388 More actions · Answer 12

If you can show me the structure of Shift_date and the table you want to join to, I can give you an example.

_________________________________
seth delconte
http://sqlkeys.com

jdamian SSC Journeyman Points: 84 More actions · Answer 13

OK, this is what I have to work with:

This first table below has the shifts and the data I need but the problem is, there can be multiple records for the same cart but with different programs. Due to the fact that they can change programs throughout the shift. So after I apply that IF ELSE statement, I do get records for only that shift but in order to make sure I get the most recent and accurate records, I want to join to a table that has the "Current Programs" for that shift. So when I view the data, I only get the current cart/program info for that exact time. The table that has the current program information does not include all of the other information that the table with the shifts have. So I wanted to take my results from the IF ELSE query and join them to this tblLPCurrentOnline table on Cart and Program.

So for example, if the results from my IF ELSE statement query gives me the following records:

Cart 1 Program 23 Shift 1

Cart 1 Program 50 Shift 1

I know only one of those programs are current (it could of changed from 23 to 50 15 min ago or 2 hours ago)

So when I join Cart and Program to tblLPCurrentOnline, I will be able to eliminate the 'older' record because tblLPCurrentOnline will only have one record for Cart 1 and will contain the program number that is current. So by doing this, I am verifying and ensuring that I only have the current records at all times.

My ultimate goal is to have a table or view that contains this data all the time.

Also, the tblTransactionDetail table DateTime column data only has the correct date and time is always 12AM so that is why I can't just filter using the date/time. I don't need all the data from tblTransactionDetails though only a few columns.