Calculating Length Of Time A Ticket Was Suspended

Mar 7, 2007

I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").

The data is stored something similar to the following...

Ticket Date Status
------ ---- ------
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"

This is the result i'd like to see....

Ticket Start Hold End Hold Time Suspended
------ ---------- -------- --------------
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...

Has anybody done something along the same lines? If so, could you point me in the right direction?

Thanks in anticipation

Jon

View 6 Replies

Calculating Length Of Service Categories Based On Start Dates

May 1, 2007

Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas :)

Basically, I have the following table structure
Employee(employee_number, continuous_start_date, ...)

The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+).
I can work out the length of service in months with the following code

SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months'
FROM employee

So the first stage is to summarise the length of service into groups as mentioned above.

Then the final stage is working out how many people are in each group!

Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result ;)

Thanks later, and in advance :p
-GeorgeV

View 14 Replies View Related

Calculating COUNTER Physical Disk: AVG. DISK QUEUE LENGTH

Sep 10, 2007

If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.

View 1 Replies View Related

Calculating Time

Aug 27, 2005

Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!

Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

View 13 Replies View Related

Help With Calculating Duration Time

Oct 14, 2005

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?

View 4 Replies View Related

Calculating Time Between Each Record?

Dec 4, 2013

Code:
CREATE TABLE [dbo].[DigiTracker](
[DigitrackID] [int] IDENTITY(1,1) NOT NULL,
[contact_id] [int] NOT NULL,

[Code]....

-- not accurate b/c it returns 0 for all TimePerPageSec
SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec
FROM DigiTracker
GROUP BY contact_id, ZinePageNumber
time spent (in seconds) between each record

View 9 Replies View Related

Calculating Duration Time

Dec 13, 2007

View 2 Replies View Related

Calculating Time Difference

Jul 20, 2005

Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help

View 2 Replies View Related

I Need Help In Calculating Time Duration

Dec 13, 2007

View 6 Replies View Related

Calculating Time Difference Between Two Different Dates

Dec 9, 2011

I have data in which i need to calculate employees working hours for a day...

name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0

How can i calculate time in that two dates as 1 is for entry an 0 is for exit..

View 2 Replies View Related

Calculating The Time Duration For Each Event

Jun 6, 2008

Hi all,

Thanks for setting up such a great site and forum.

Here is my problem:

I have a table like the following in SQL Server 2005:

order | taskid | main_person | temp_person | start_assign_date
1 | 3 | John | John | 2008-01-01 10:20:22
2 | 3 | John | Joe | 2008-02-05 15:20:22
3 | 3 | John | John | 2008-02-07 20:25:20
4 | 6 | Joe | Joe | 2008-01-01 10:20:22
5 | 6 | Joe | Mike | 2008-02-01 10:20:22
6 | 10 | Doug | Doug | 2008-01-01 10:20:22
7 | 7 | Russ | Russ | 2008-02-01 11:20:22
8 | 7 | Russ | Mike | 2008-02-08 12:20:22
9 | 7 | Russ | John | 2008-02-10 20:05:12

It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.

I'd like to generate a report that shows:
- in a specific time period
- which persons were in charge of a specific task and
- for how long

Something like this:

From 2008-##-## to 2008-##-##
Task 3 - John - 15 days
Task 3 - Joe - 5 days
Task 6 - Joe - 18 days
Task 6 - Mike - 2 days
Task 10 - Doug - 20 days

I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.

I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.

Any hint or comment would be appreciated.

Thanks
Sami

View 4 Replies View Related

Calculating Query Running Time...

Dec 19, 2007

Hi all
As you know when you run a piece of TSQL scrypt in Query Analizer
at the bottom of the page , sql-server will show you the Time of completion of your code....is there any way to capture this time
from SQL-Server environment and use it in the Front-End Application
to inform the user?

Kind regards.

View 2 Replies View Related

Calculating 'time Difference' Between Two Records....

Dec 21, 2005

I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.

View 4 Replies View Related

Transact SQL :: Calculating Transaction Time

Nov 23, 2015

select TOP 10 rec_id,trans_id,number_id,card_no,message_id,trans_datetimefrom [dbo].[trans_log]
order by trans_datetime desc101, 1,34343, 99999, 200, 2015-11-23 12:27:25.710101,2,34343,99999,210,2015-11-23
12:27:26.710102,3,43434,88888,200,2015-11-23 12:28:26.714102,4,43434,88888,233,2015-11-23 12:28:27.710expected result:34343,99999,datediff(ss,'2015-11-23 12:27:26.710','2015-11-23 12:27:25.710') --difference between row 2 and row 143434,88888,datediff(ss,'2015-11-23 12:28:26.714','2015-11-23 12:28:27.710') --

difference between row 4 and row 3difference between row 6 and row 5...In the above query, I always want to find the difference in transaction date time between second and first row in a moving window.I have the unique id as rec_id to compare the next row with the previous row.

View 3 Replies View Related

Calculating Time Between Two Date In Sql Server 2005

Mar 17, 2008

Calculating time between two date In Sql Server 2005help me ..

View 3 Replies View Related

Calculating Time Differenc Between Text Fields

Feb 8, 2007

I am using MS SQL Server 2000.
I have 4 text fields
1. event begin date
2. event begin time
3. event end date
4. event end time
I have to find the day and time difference in months days hours and minutes between the event begin date & time and the event end date & time � The event begin date and event end date are char(8) & event begin time and event end time are char(4) � with military time. I would need to combine the event begin date & event begin time to get the event begin date & time and the same for the event end date & time.
If the values of the 4 text fields are
event begin date = �20070207�, event begin time = �2015�, event end date = �20070208�, event end time = �0105�, the difference should be 0 month 0 day 4 hours 50 minutes.
Can somebody please help with MS SQL Server 2000 syntax?

View 1 Replies View Related

T-SQL (SS2K8) :: Calculating Total Outage Time

Jun 17, 2014

I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.

Ex:

3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.

In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.

View 3 Replies View Related

SQL 2012 :: Calculating Elapsed Time Between Records?

Oct 29, 2015

I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,

emp_idstart_date end_date
1001 1998-01-01 2003-06-21
1002 1999-05-23 2008-03-28
1001 2004-08-19 NULL
1003 2004-10-12 2006-07-25
1004 2005-04-28 NULL
1002 2008-11-02 NULL
1003 2009-05-17 NULL

The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it.

View 9 Replies View Related

Calculating Time Difference In Msdb..sysjobhistory

Dec 28, 2006

I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.

select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.

The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.

DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).

What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.

run_date run_time

2006122821510 -- 02:15:10 AM (how to get the minute count?)
2006122821510 -- 02:15:10 AM (same as above)
20061227233014 -- 23:30:14 PM (this is strt forward)
20061227233014 -- 23:30:14 PM (same as above)
200612273016 -- 00:30:16 AM (how to get minute count?)
200612273015 -- 00:30:15 AM (how to get minute count?)

Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.

View 5 Replies View Related

Transact SQL :: Calculating Date And Time For 3rd Shift

Jul 13, 2015

We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.

Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)

[code]...

View 3 Replies View Related

Calculating The Time Required To Update Statistics

Mar 6, 2008

We have a proc that runs nightly which updates stats. For each database it will run dbcc updateusage on each table, then then sp_updatestats on the database itself.

This process normally takes about an hour, but recently the time has gone up dramatically.

I am looking at ways of making the run faster. One suggestion was to split the work and have it run in parrallel, with multiple procs all doing the same job but on different databases. So for example, I could have a proc that would determine which databases would take the longest to process, and then call 3 different copies of our update proc, each getting a different set of databases to process.

If I went this route, how could I determine which databases would take the longest to complete this process? I'm investigating determining this from sysindexes' rowmodctr, or maybe just database size?

Any suggestions on how I could get an estimate of which dbs would be the most costly to update stats? This is for SQL Server 2000.

Thanks

View 3 Replies View Related

SQL 2012 :: Calculating Elapsed Time Between Datetimes For Travel?

May 20, 2014

I have a requirement to be able to calculate the transit time between international locations when I have the flight departure time and flight arrival times and departure and arrival locations.

We have a problem though, in that the datetime stored are local times, and the journeys can involve crossing the international date line in either direction, so it is possible for the arrival time to be earlier than the departure time!

To give a famous example, if you flew Concorde from London to Washington, you could depart at 10am on the 1st of June (London time) and arrive at 8.00am on 1st June (Washington time)

Even worse, you could leave Brisbane at 2.00 am on the 1st June, cross the International Date Line and arrive at San Francisco at 23:00 on 31st May!

View 6 Replies View Related

How To Store Ticket Ages?

Dec 28, 2007

Hello there. I've been debating the best way to store the age of a ticket in a queue. Here's a little overview:

The application has a form that has users enter the current count and oldest ages of tickets in several queues. The format in the application they pull this data from stores it as Day:Hour:Minute (0:04:59)

The first time around, I stored them as datetime. But since you can't store a Day as zero (some tickets may be 0 days, 23:54 Hours old) I stored the day as a year. Well that proved to be stupid, because the averages in reports were way off. Next I stored the age as text, like this: 10459 (1 Day, 04:59 Hours old). Now my problem is when using that number in a report, I have to convert it to decimal and multiply the day by 24 and add it to the hour, just so they will be weighted correctly.

This is a mess... and I've searched all over the internet for examples, but every search result is for storing a persons age, not the age of a ticket.

Any one have any ideas?

View 2 Replies View Related

Ticket Sales For Groups And Random Code ?

May 30, 2007

I need to provide a way to handle groups that buy tickets in bulk lots, say 20 at a time. Users can later register with a "ticket code". I need to tie these two together and provide a way for groups to buy more tickets at a later time.

There should be a code that can be generated for each ticket that a group buys. This ticket is then to be given to a user to register with. A rogue user should not find it easy to guess the code. So, I want to generate a code for each group as they register, that is a random 6 or 7 digit, alphanumeric string and then something similar for a ticket. See table definitions below.

So for a ticket code, we could have something like this:
A23486D_TR34, where A23486D would be the group code

comments/suggestions/ideas welcome

At present Ive built two tables, [groups] and [group_tickets]

[group]
id int(10) unsigned
name varchar(45)
phone varchar(10)
email varchar(45)
contact_namevarchar(100)
signup_datedatetime
last_updatedatetime
group_codevarchar(6)
addr1 varchar(45)
addr2 varchar(45)
city varchar(45)
state char(2)
zip varchar(10)

[group_ticket]
id int(10) unsigned
group_idint(10) unsigned
purchase_datedatetime
fill_datedatetime
ticket_notestext
ticket_code varchar(12)

View 1 Replies View Related

Reporting Services Authentication Ticket Across Domain

Apr 4, 2007

Hi,

I am new to Reporting Services. I want to use Reporting Services 2005 in our application.

My custom web application is on one machine and Reporting Services 2005 is on other machine. I am using Forms Authentication and using Single Sign On for login

If my custom web application and Reporting Services are on same machin I can get "Authentication Ticket" issued by ReportingServices2005 to the Report server.

But in this case I am not able to get "Authentication Ticket" on Report Server since it is on other machine.

I am using ReportViewer control in my web application to display Reports and using LogonUser method to get the Authentication Ticket.

How can I pass CookieAuthentication ticket from my Custom Web Application to Report Server?

Is there any work around to pass Authentication Cookie across Domain or any other solution for this?

Regards

Amit

View 3 Replies View Related

T-SQL (SS2K8) :: Function To List Distinct Pax Name And Ticket Numbers?

Mar 14, 2014

I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.

The data is:

Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ

I somehow need to get a function to pull the data out for each booking ref like so

--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333

I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.

View 4 Replies View Related

Job Suspended.

Jan 22, 2001

Is anyone aware of how to start a scheduled job once it has been marked as "suspended"? (SQL Server 7)

Thanks Brian

View 2 Replies View Related

Database Design For Ticketing System; User Ticket Quotas

Mar 21, 2006

My question takes two parts; firstly, is the new table that I'm proposing going to handle the business logic I describe below, and secondly, if I put the new table in, how in hell do I use it?

Right; present schema attached.

The idea, which I hope is fairly clear from the schema, is that you send it a buch of parameters about the event, admission date, etc, and it will return all tickets matching those parameters. An example stored proc for this is below:

CREATE PROCEDURE [dbo].[getSingleTicketsByParameters]
@eventIdINT,
@standIdINT,
@admissionDateIdINT,
@bookingDateIdINT,
@concessionIdINT,
@bookingMinQuantityIdINT,
@bookingMaxQuantityIdINT,
@membershipIdINT
AS
SET NOCOUNT ON
SELECT
[tblTickets].[id],
[tblTickets].[booking_date_id],
[tblTickets].[booking_min_quantity_id],
[tblTickets].[booking_max_quantity_id],
[tblTickets].[ticket_concession_id],
[tblTickets].[membership_id],
[tblTickets].[event_id],
[tblTickets].[stand_id],
[tblTickets].[admission_date_id],
[tblTickets].[price],
[tblTickets].[availability],
[tblTickets].[description],
[tblTickets].[admin_description],
[tblTickets].[ticket_open],
[tblTickets].[ticket_live],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date],
[tblTicketConcessions].[concession_name],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblAdmissionDates].[description],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblMemberships].[membership_name]
FROM [tblTickets]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
AND ([tblEvents].[id]=@eventId OR @eventId=0)
AND ([tblStands].[id]=@standId OR @standId=0)
AND ([tblTicketConcessions].[id]=@concessionId OR @concessionId=0)
AND ([tblAdmissionDates].[id]=@admissionDateId OR @admissionDateId=0)
AND ([tblBookingDates].[id]=@bookingDateId OR @bookingDateId=0)
AND ([tblBookingMinQuantities].[id]=@bookingMinQuantityId OR @bookingMinQuantityId=0)
AND ([tblBookingMaxQuantities].[id]=@bookingMaxQuantityId OR @bookingMaxQuantityId=0)
AND ([tblMemberships].[id]=@membershipId OR @membershipId=0)
GO

So. It's all about to get horribly, horribly complex (well, it is to me) so take a deep breath.

Tickets are subject to quotas. However, quotas are subject to... well, at the moment they can be based on event, stand, admission date, concession, or any combination of these. They can also be based on an individual ticket. All quotas, however, are annual; they only apply to ticket purchases in the same year. For example:
- you can't buy more than 4 tickets for date A, in stand B at event C, per year.
- you can't buy more than 2 tickets for stand D per year.
- you can't buy more than 4 of ticket number 123.

Now, I'm thinking that all I need to manage this is one table, and it's going to look a little like this:

tblQuotas
id INT PK
ticket_id INT FK
event_id INT FK
stand_id INT FK
admission_date_id INT FK
quota INT

So, if I put a record in there with an event, stand and admission date - and a quota - then I've met the first business rule that I described above. If I put in a record with just a stand id and a quota, then I've met the second sort. If I put in one with just a ticket id and a quota, then I've met the third.

Now we return to that big SQL statement above. The one that says "get me all eligible tickets that match these parameters". And it's got to get a lot bigger because I now need to not only join in tblQuotas, to see if any quotas apply to the ticket I've chosen (or to the event, stand, etc that make it up), but I've also got to join in tblBasket, and tblOrders, and tblUsers, to find out how much of any particular quota they've already used up in previous orders. Although that's only orders placed in the current year, mind. And of course I also now haveto pass the users ID in as a parameter so I can look up their order history.

Your head's hurting too, right?

So... this is where I've got to:

SELECT SUM(ticket_quantity) FROM [tblBasket]
INNER JOIN [tblTickets] ON [tblBasket].[ticket_id] = [tblTickets].[id]
INNER JOIN [tblOrders] ON [tblBasket].[order_id] = [tblOrders].[id]
WHERE
[tblTickets].[stand_id] = @standId
AND [tblOrders].[user_id] = @userId
AND ([tblOrders].[order_date] BETWEEN '2006/01/01' AND '2006/12/31')

What I want to do is incorporate that into the big SQL query up top, in such a way to make it only return tickets that not only match the ticket parameters but that also aren't linked to stands, admission dates or anything else, that the user has reached their quota on.

Oh, and I'm in a bit of a hurry so do try and get a move on won't you? ;)

But seriously - how do I put those two SQL querys together? Do I need one for each paramater that might have a quota attached, or is there a quicker way? All suggestions and advice, up to and including "get an easier job, dude", received gratefully :)

View 2 Replies View Related

T-SQL (SS2K8) :: Procedure Parameter Length Declaration Less Than Column Length?

Jun 30, 2014

is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..

I have a table

CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure
CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) )
AS
BEGIN
SELECT KeyName FROM TEST001
WHERE KeyName = @KeyName
END
KeyName = @KeyName

Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..

IS there any way to find out all such usage on the ALL Procedures in the Database ?

View 2 Replies View Related

BlobColumn.GetBlobData() With Data Length &&> Integer Length (32767)

Mar 27, 2008

For those of you who would like to reference my exact issue, I'm dealing with the RSExecution SSIS package at the "Update Parameters" data flow task, at the Script Component.

The script tries to split parameter data into name and value. Unfortunately, I have several reports that are passing parameters that are very large. One example has over 65,000 characters all in the normal "&paramname=value&parm2=value..." format.

The code in the script works fine until it gets to one of these very large parameter sets. I have figured out what is causing the issue. Here's some code:

Dim paramBlob as Byte()
paramBlob = Row.BlobColumn.GetBlobData(0, Row.BlobColumn.Length)

The second parameter of the .GetBlobData function takes an INTEGER as its count! Therefore, no matter what kind of datatype I pass to the string that the script will later split, it will be limited to 32767 characters.

THIS IS A PROBLEM!!!

Does anyone know a workaround for this issue? I need all of the parameter data to be reported, and I would hate to have to skip over rows like this. Also, if I'm missing something, please fill me in!

Thanks for your help in advance,
LOSTlover

View 6 Replies View Related

SUSPENDED Processes

Apr 1, 2008

Hi All

When i see the result of Sp_who2, i see the status of a process as
"SUSPENDED" for some of the processes like logwriter, checkpoint etc.
and also found the same status for "IF @@TRANCOUNT>0 COMMIT TRAN"

Please let me know what does it mean in the context of SQL 2005

Thanks in advance.

View 8 Replies View Related

Suspended Status

Sep 25, 2007

Hi All,

Using SQL Server 2005 w/ SP2 I have a simple select query with a parameterized WHERE clause that in Management Studio runs nearly instantly, everytime. Using the same login, when I run the same query using SQL RS the results (only 6 rows) takes upwards of 5 minutes. If I look at the Activity Monitor, it shows the process as suspended and indicates a CXPACKET. Everything I have read on the forums about this indicate it is an issue with not having SP2 , which I do. Or a 64 bit version, I have 32. Or for large bulk updates, I am doing a very simple select. Does anyone have any clue on this?

regards
Bill

View 3 Replies View Related

DB In Suspended Mode

Mar 2, 2007

Hi,

I have a database shown a SUSPENDED mode on both side of Principal and Mirror server. What should I do now?

1). Is the breaking the Mirror sesssion my only choice?

2). If I break it, how do I reestablish the Mirror again quickly. What is my potential data loss?

Thanks,

View 1 Replies View Related