New Posts  All Forums:Forum Nav:

SQL Query help!

post #1 of 6
Thread Starter 
Hopefully this is in the right section. Please move if not.

So, I need to run a query on one of our databases here at work and I have got it almost to the point that is needed.

This is my query as of right now:


--get TIMEINSECONDS, INPUNCHDTM, OUTPUNCHDTM for the last 15 days

SELECT

PERSONNUM,
PERSONFULLNAME,
TIMEINSECONDS,
LABORLEVELNAME1,
INPUNCHDTM,
OUTPUNCHDTM
FROM DBO.VP_TIMESHEETPUNCH
WHERE LABORLEVELNAME1 = '444'
AND INPUNCHDTM > CONVERT(CHAR(8),GETDATE()-15,1)
ORDER BY PERSONFULLNAME

which outputs:

066454,Posey, Michelle,24300,444,2011-03-15 07:00:00.000,2011-03-15 13:50:00.000

This is where I need help

I need to add quotes around the employees name, but haven't been successful.

EX: 066454,"Posey, Michelle",24300,444,2011-03-15 07:00:00.000,2011-03-15 13:50:00.000

Does anyone have any ideas on what I need to change/add to make this happen?

Thanks for any help!!
My lil monster
(18 items)
 
  
CPUMotherboardGraphicsGraphics
i7 6800k Asus Deluxe ii x99 EVGA 980 ti Hydro Copper EVGA 980 ti FTW 
RAMHard DriveHard DriveOptical Drive
32GB Corsair Dominator  Samsung 840 Pro  2x 850 Pro Sony External 
CoolingOSMonitorMonitor
Custom Water Windows10 Acer x34 Predator Asus PB278Q 
KeyboardPowerCaseMouse
Corsair K70 Corsair AX1200i 900D Logitech G502 
Mouse PadAudio
Corsair Gaming Genius SW-G2.1 2000 
  hide details  
Reply
My lil monster
(18 items)
 
  
CPUMotherboardGraphicsGraphics
i7 6800k Asus Deluxe ii x99 EVGA 980 ti Hydro Copper EVGA 980 ti FTW 
RAMHard DriveHard DriveOptical Drive
32GB Corsair Dominator  Samsung 840 Pro  2x 850 Pro Sony External 
CoolingOSMonitorMonitor
Custom Water Windows10 Acer x34 Predator Asus PB278Q 
KeyboardPowerCaseMouse
Corsair K70 Corsair AX1200i 900D Logitech G502 
Mouse PadAudio
Corsair Gaming Genius SW-G2.1 2000 
  hide details  
Reply
post #2 of 6
I believe what your looking for is a view (if memory serves of sql) What structure of database are you using? Oracle?
Nightshade
(20 items)
 
  
CPUMotherboardGraphicsGraphics
i7-4930k Asus Rampage IV Extreme EVGA - Titan-X Superclocked EVGA - Titan-X 
RAMHard DriveHard DriveHard Drive
64 GB Corsair Vengance DDR3 OCZ Agility 3 Intel 540 240GB 7200 RPM Platter Drive 
Hard DriveCoolingOSMonitor
1TB Mushkin SSD Watercooled Windows 10 Pro x64 ASUS ROG Swift 
KeyboardPowerCaseMouse
Razer Black Widow Chroma Corsair RM1000 Corsair 900D Razer Deathadder Chroma 
Mouse PadAudioOther
Steelseries Soundblaster Z Speakers: Logitech z5500 
  hide details  
Reply
Nightshade
(20 items)
 
  
CPUMotherboardGraphicsGraphics
i7-4930k Asus Rampage IV Extreme EVGA - Titan-X Superclocked EVGA - Titan-X 
RAMHard DriveHard DriveHard Drive
64 GB Corsair Vengance DDR3 OCZ Agility 3 Intel 540 240GB 7200 RPM Platter Drive 
Hard DriveCoolingOSMonitor
1TB Mushkin SSD Watercooled Windows 10 Pro x64 ASUS ROG Swift 
KeyboardPowerCaseMouse
Razer Black Widow Chroma Corsair RM1000 Corsair 900D Razer Deathadder Chroma 
Mouse PadAudioOther
Steelseries Soundblaster Z Speakers: Logitech z5500 
  hide details  
Reply
post #3 of 6
SELECT
PERSONNUM,
('"' + PERSONFULLNAME + '"') as PERSONFULLNAME ,
TIMEINSECONDS,
LABORLEVELNAME1,
INPUNCHDTM,
OUTPUNCHDTM
FROM DBO.VP_TIMESHEETPUNCH
WHERE LABORLEVELNAME1 = '444'
AND INPUNCHDTM > DATEADD("d", -15, GETDATE())
ORDER BY PERSONFULLNAME






Also, what variable type is INPUNCHDTM? If it is a date, why are you converting a date to compare it with another date?

You should also use dateadd to shift dates.


Edited by DuckieHo - 3/29/11 at 11:42am
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #4 of 6
Thread Starter 
Thank you both! I got pulled away for a little bit and haven't had a chance to respond..

Anyways...

Duckie you my friend are awesome. I couldn't get it for the life of me. The variable are all default variables found within a time keeping database. and the conversion is not really being used at this point. I have to add more to this query after I show these results and that conversion will be needed later on.

Thanks again!
My lil monster
(18 items)
 
  
CPUMotherboardGraphicsGraphics
i7 6800k Asus Deluxe ii x99 EVGA 980 ti Hydro Copper EVGA 980 ti FTW 
RAMHard DriveHard DriveOptical Drive
32GB Corsair Dominator  Samsung 840 Pro  2x 850 Pro Sony External 
CoolingOSMonitorMonitor
Custom Water Windows10 Acer x34 Predator Asus PB278Q 
KeyboardPowerCaseMouse
Corsair K70 Corsair AX1200i 900D Logitech G502 
Mouse PadAudio
Corsair Gaming Genius SW-G2.1 2000 
  hide details  
Reply
My lil monster
(18 items)
 
  
CPUMotherboardGraphicsGraphics
i7 6800k Asus Deluxe ii x99 EVGA 980 ti Hydro Copper EVGA 980 ti FTW 
RAMHard DriveHard DriveOptical Drive
32GB Corsair Dominator  Samsung 840 Pro  2x 850 Pro Sony External 
CoolingOSMonitorMonitor
Custom Water Windows10 Acer x34 Predator Asus PB278Q 
KeyboardPowerCaseMouse
Corsair K70 Corsair AX1200i 900D Logitech G502 
Mouse PadAudio
Corsair Gaming Genius SW-G2.1 2000 
  hide details  
Reply
post #5 of 6
Quote:
Originally Posted by OverSightX View Post
Thank you both! I got pulled away for a little bit and haven't had a chance to respond..

Anyways...

Duckie you my friend are awesome. I couldn't get it for the life of me. The variable are all default variables found within a time keeping database. and the conversion is not really being used at this point. I have to add more to this query after I show these results and that conversion will be needed later on.

Thanks again!
With dates and times, never do a simple addition or subtraction.... always use the date functions. Otherwise, you may end up with some funky dates.
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #6 of 6
Thread Starter 
Thanks again for the info Duckie. I have taken that conversion off the query. Im sure ill be back with more questions on this.
My lil monster
(18 items)
 
  
CPUMotherboardGraphicsGraphics
i7 6800k Asus Deluxe ii x99 EVGA 980 ti Hydro Copper EVGA 980 ti FTW 
RAMHard DriveHard DriveOptical Drive
32GB Corsair Dominator  Samsung 840 Pro  2x 850 Pro Sony External 
CoolingOSMonitorMonitor
Custom Water Windows10 Acer x34 Predator Asus PB278Q 
KeyboardPowerCaseMouse
Corsair K70 Corsair AX1200i 900D Logitech G502 
Mouse PadAudio
Corsair Gaming Genius SW-G2.1 2000 
  hide details  
Reply
My lil monster
(18 items)
 
  
CPUMotherboardGraphicsGraphics
i7 6800k Asus Deluxe ii x99 EVGA 980 ti Hydro Copper EVGA 980 ti FTW 
RAMHard DriveHard DriveOptical Drive
32GB Corsair Dominator  Samsung 840 Pro  2x 850 Pro Sony External 
CoolingOSMonitorMonitor
Custom Water Windows10 Acer x34 Predator Asus PB278Q 
KeyboardPowerCaseMouse
Corsair K70 Corsair AX1200i 900D Logitech G502 
Mouse PadAudio
Corsair Gaming Genius SW-G2.1 2000 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming