Date Range for Join Query

Question

Post reply

Date Range for Join Query

Viewing 6 posts - 16 through 20 (of 20 total)

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

paramind SSCrazy Points: 2160 More actions · Answer 1

.... and the tbl_hp_adminusers Table?

_/_/_/ paramind _/_/_/

paramind SSCrazy Points: 2160 More actions · Answer 2

Question: There's a HomeworkerID in both tables (tbl_hp_bookings and tbl_hp_bookings_transaction.) Why? Is it supposed to always be the same?

_/_/_/ paramind _/_/_/

Simon Young SSC Enthusiast Points: 115 More actions · Answer 3

Hi

Yes it exists in both tables - it's always the same so that a separate transaction report can be run apparently... left over from a feature that never quite made it.

Adminuser table:

CREATE TABLE [tbl_hp_adminusers] (

[UserID] [int] IDENTITY (1, 1) NOT NULL ,

[Username] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

[Password] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,

[FailedLogin] [numeric](18, 0) NOT NULL CONSTRAINT [DF_tbl_hp_adminusers_FailedLogin] DEFAULT (0),

[DeletedTag] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_tbl_hp_adminusers_DeletedTag] DEFAULT ('N'),

[BlockedTag] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_tbl_hp_adminusers_BlockedTag] DEFAULT ('N'),

[AdminEMail] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,

[AdminLevel] [int] NOT NULL CONSTRAINT [DF_tbl_hp_adminusers_AdminLevel] DEFAULT (1),

[LastLogin] [datetime] NOT NULL CONSTRAINT [DF_tbl_hp_adminusers_LastLogin] DEFAULT (getdate()),

[AdminTel] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,

[ShowStats] [char] (1) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_tbl_hp_adminusers_ShowStats] DEFAULT ('N'),

[SendAlerts] [char] (1) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_tbl_hp_adminusers_SendAlerts] DEFAULT ('Y'),

CONSTRAINT [PK_tbl_hp_adminusers] PRIMARY KEY CLUSTERED

(

[UserID]

) WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

paramind SSCrazy Points: 2160 More actions · Answer 4

Now that I know the tables it's really getting confusing...

You have different bookings with 1:n transactions per booking. True? OK.

Now you're going to get the whole monthly amount (including many bookings) per EACH booking? Do you really MEAN this?

How about an explanation in plain english? ... because I don't want you to loose your job because of a query I designed

_/_/_/ paramind _/_/_/

Simon Young SSC Enthusiast Points: 115 More actions · Answer 5

Hi

That's correct 1 booking with multiple transactions within that booking.

The reason for requiring the full monthly amount of bookings for the month in which each booking was made is that that is the way the company calculates commission.

For example:

A booking is made on 12 October 2005

This booking is paid for on 01 March 2006

When the report runs (which works out commission on the sale) it needs to work out what percentage of commission to apply to that particular booking.

To do this it takes the booking date (12 October 2005) and works out the total value of all bookings made by the admin user in October 2005. It then compares this amount to the scale and hence knows a figure for the commission percentage rate.

Therefore for each booking in the report (and the bookings retrieved are all retrieved because they have been paid for in the month of the report) it is necessary to get the total bookings from that admin user in the month in which the booking originally occurred.

Does that make sense?

paramind SSCrazy Points: 2160 More actions · Answer 6

Yes, but I still think, that the commision calculation shouldn't be isolated. I understand what you want - please read the private message I sent you!

_/_/_/ paramind _/_/_/