SQLServerCentral / Programming / General

Joining Columns

kbnyny — Thu, 24 May 2012 10:58:15 GMT

I have two columns same db, different tables, one is varchar( S 12345) and the other is int (12345). I want to join the tables using these columns I don't need to used the (S)Is there a way to do this if so how can it be done.Joining the tables I get an error Syntax error converting the varchar value 's 12345' to a column of data type int.Thanks

Classic asp, quotation marks and SQL SP

steveg-941384 — Wed, 23 May 2012 15:19:28 GMT

I have a .asp form that uses text and textarea for input. I use SP's to update MS SQL 2005 tables. My problem is that when a user enters text surrounded by quotation marks ("text"), the UPDATE truncates the field beginning just before the first quotation mark.I use Replace(Request.Form("fieldname"),"'", "''") to deal with this, but it's not working. (These are apostophe's between quotation marks.) Suggestions?Thanks in advance.

select distinct?

bjh47960 3345 — Wed, 23 May 2012 08:59:45 GMT

hi all, newb hereI have several records in a view that aren't duplicate records, but have matching column values as other records.I only want to select one of the records along with the other single recordsEX.[u]item[/u] - [u]itemA[/u] - [u]retail[/u]555 - 555L - 9.99555 - 555XL - 9.99555 - 555m - 9.99763 - 763 - 9.99462 - 462 - 9.99222 - 222 - 9.99my current stament for this view is:SELECT top 200item AS Expr1, itema AS Expr2, sdescript AS Expr3, retail AS Expr4, date_created AS Expr5, mfg AS Expr6FROM dboWHERE (mfg <> 'somecompany') ORDER BY date_created DESCIt doesn't matter which of the 555 lines, I just need one of them I want the view to reflect:555 - 555? - 9.99763 - 763 - 9.99462 - 462 - 9.99222 - 222 - 9.99I tried using distinct but couldn't seem to get it to work? please any help appreciated

Rewriting Stored Procedure from Cursor to SET-based

kwe477 — Tue, 22 May 2012 07:41:32 GMT

I'm not a developer but am tasked with rewriting a SP to be set-based.This particular goodie takes about 6 hours to finish, since the underlying tables are huge. (> 1 billion rows)I'd really appreciate help on this one.SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[stat_call] @in_date varchar(6) asdeclare @cmd0 varchar(8000) , @cmd1 varchar(8000) , @where varchar(500) , @region varchar(500) , @in_date_region varchar(8) , @cnt int , @cnt2 int , @ident int , @art varchar(2) , @d varchar(100) , @w_o varchar(100) , @bun varchar(100) , @circle varchar(100) , @jc varchar(100)if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tf_kdu]'))begin create table tf_kdu ( in_date varchar(6) , region varchar(500) , art varchar(2) , ident int , ident2 int , heading varchar(500) , group varchar(70) , device varchar(500) , Ins_BG float , Single_BG float , Allein_1 float , Allein_2 float , couple_1 float , couple_2 float , couple_3 float , couple_4 float , divers float , Ins_P float , P1 float , P2 float , P3 float , P4 float , P5 float , P6Plus float )endif not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[parameters]'))begin create table parameters ( bm varchar(6) , region varchar(50) , art varchar(1) , aktiv smallint )end set @in_date_region = @in_date + '03' DECLARE my_cur_region CURSOR FOR select substring(krs_schl1,1,5) + ' ' + krs_lang1 as knr , 1 as art from [msi_dlp].[dbo].[td_dwh1] , [msi_dlp].[dbo].[td_dwh2] , [msi_dlp].[dbo].[td_dwh3] where td_dwh2.mon1 = td_dwh1.mon1 and td_dwh3.owk_id < 3 and td_dwh3.krs_date <= td_dwh2.mon3 and ( td_dwh3.krs_date2 is null or td_dwh3.krs_date2 >= td_dwh2.mon3) and td_dwh1.mon5 = cast(@in_date_region as int) union select substring(str_1,1,5) + ' ' + str_2 , 2 as art from [msi_dlp].[dbo].[td_dwh1] , [msi_dlp].[dbo].[td_dwh2] , [msi_dlp].[dbo].[td_dwh4] , [msi_dlp].[dbo].[td_dwh5] where td_dwh2.mon1 = td_dwh1.mon1 and ( td_dwh4.str_dat11 is null or td_dwh4.str_dat11 >= td_dwh2.mon3) and td_dwh5.str_id = td_dwh4.str_id and td_dwh5.ort_dat11 <= td_dwh2.mon3 and (td_dwh5.ort_dat22 is null or td_dwh5.ort_dat22 >= td_dwh2.mon3) and td_dwh1.mon5 = cast(@in_date_region as int) OPEN my_cur_region FETCH NEXT FROM my_cur_region INTO @region, @art WHILE @@FETCH_STATUS = 0 BEGIN insert into parameters values(@in_date,@region,@art ,0) if @art = 1 exec( 'STAT_LK.dbo.stat_v3 @in_date = ''' + @in_date + ''' , @d = '''' , @w_o = '''' , @bun = '''' , @circle = ''' + @region + ''' , @jc = ''''') else exec( 'STAT_LK.dbo.stat_v3 @in_date = ''' + @in_date + ''' , @d = '''' , @w_o = '''' , @bun = '''' , @circle = '''' , @jc = ''' + @region + '''') FETCH NEXT FROM my_cur_region INTO @region, @art END CLOSE my_cur_region DEALLOCATE my_cur_region update parameters set aktiv = 1 where bm = @in_date

Version Control Software Recommendations

mreynold — Tue, 22 May 2012 08:13:06 GMT

Any recommendations on a good version control software to manage our Stored Procedure changes and Queries?Thanks,-Michael

OUTPUT issue with sp_executesql

phil.layzell — Sun, 13 May 2012 16:43:10 GMT

Hi,I wonder if anyone can answer this/help. I am trying to select a blob of XML and then put this XML into a variable like so:DECLARE @retval XML DECLARE @sSQL nvarchar(500); DECLARE @ParmDefinition nvarchar(500); DECLARE @tablename nvarchar(50) DECLARE @ApplNbr VARCHAR(20) SET @ApplNbr = 24 SELECT @tablename = N'[myDB].[dbo].[myTable]' SELECT @sSQL = N'SELECT * FROM ' + @tablename + 'WHERE [ApplNbr] = '+ @ApplNbr + ' FOR XML PATH(''myDB.myTable'')'; SET @ParmDefinition = N'@retvalOUT XML OUTPUT'; EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT; SELECT @retval; --<------- Why does this return NULL?However, I want the XML to to set to the @retval var, but this seems to lose its value once the SP has run and OUTPUT. How can I get this var to retain this value as I need to concatenate this into one xml blob? SET @OrigXML = (SELECT @retval ,@retval2 ,@retval3 FOR XML PATH (''), ROOT('Table'))Cheers, Phil

job runs every 1 seconds everyday.

Apscool — Mon, 14 May 2012 04:54:38 GMT

Hi,I have a specific requirement with sql server 2008. Through the sms webservice user will set the date & time for certain commands to be fired on the vehicle unit. The job scheduler should pick up the the dates and time and fire at time specified in the scheduler table. It should pickup date and time from the scheduler and insert command in the commands table. Can anybody help to sort this?

sum, percent do able ?

sabercats — Thu, 10 May 2012 12:51:29 GMT

Example, I need to get all the records weekly start from last 2 Sunday 00:00:00 AM to last Saturday 23:59:59 include any records start before last 2 Sundays but end after last Sunday. Then i sum them and get percentage. [code=sql]CREATE TABLE #Testing ( Location varchar(255), OS varchar(255), Starttime DATETIME, Endtime DATETIME ) INSERT INTO #Testing (Location, Starttime, Endtime)SELECT 'tampa','win7','2012-05-01 12:56:27.000','2012-05-01 13:39:22.170' UNION ALL SELECT 'tampa','win7','2012-05-01 13:39:21.000','2012-05-03 09:26:20.580' UNION ALL SELECT 'tampa','win7','2012-05-03 09:32:09.000','2012-05-03 10:28:01.357' UNION ALL SELECT 'dallas','vista','2012-04-27 00:00:00.000','2012-05-06 23:59:59.000' UNION ALL SELECT 'greenbay','winxp','2012-04-29 00:00:00.000','2012-04-29 4:00:00.000' UNION ALL SELECT 'greenbay','win7','2012-04-30 00:00:00.000','2012-05-05 00:00:00.000' UNION ALL SELECT 'san jose','winxp','2012-04-29 00:00:00.000','2012-05-01 15:22:01.213 UNION ALL SELECT 'san jose','winxp','2012-05-01 15:31:41.000','2012-05-02 11:12:34.300' UNION ALL SELECT 'san jose','winxp','2012-05-02 11:25:27.000','2012-05-04 17:10:41.033' UNION ALL SELECT 'san jose','winxp','2012-05-02 16:11:51.000','2012-05-04 17:10:01.850' UNION ALL SELECT 'san jose','winxp','2012-05-04 17:10:00.000','2012-05-05 23:59:59.000' UNION ALLSELECT 'san jose','winxp','2012-05-04 17:10:59.000','2012-05-05 23:59:59.000' UNION ALL SELECT 'new york','win7','2012-04-29 00:00:00.000','2012-04-30 13:51:30.403' UNION ALL SELECT 'carolina','win7','2012-04-28 00:00:00.000','2012-05-06 03:59:59.000'select * from #Testingdrop table #Testing[/code]Here are my code[code=sql]declare @dow intdeclare @2SundaysAgo datetimedeclare @lastSaturday datetimeselect @dow = datepart(dw, getdate())select @2SundaysAgo = getdate() - (7 + (@dow - 1))select @lastSaturday = getdate() - (@dow) + 1select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))DECLARE @t TABLE([Location] CHAR(20), OS CHAR(10),ID int, [Active User Start] DATETIME, [End Time] DATETIME, [Total Using Time] DECIMAL)INSERT INTO @t SELECT [Table_Control].location, [Table_Control_History].os, CASE WHEN [Table_Control_History].start_time < @2SundaysAgo THEN (@2SundaysAgo) ELSE ([Table_Control_History].start_time) END AS '[Table_Control_History].start_time', CASE WHEN [Table_Control_History].change_datetime > @lastSaturday then @lastSaturday ELSE [Table_Control_History].change_datetime END AS '[Table_Control_History].change_datetime', CASE WHEN [Table_Control_History].start_time < @2SundaysAgo THEN DATEDIFF (SECOND, @2SundaysAgo,MAX([Table_Control_History].change_datetime)) WHEN ([Table_Control_History].start_time > @2SundaysAgo AND [Table_Control_History].change_datetime > @lastSaturday ) THEN DATEDIFF(SECOND, [Table_Control_History].start_time, @lastSaturday) ELSE DATEDIFF (SECOND,[Table_Control_History].start_time,([Table_Control_History].change_datetime)) END AS 'Total Using Time' FROM [Table_Control] join [Table_Control_History] on [Table_Control].id = [Table_Control_History].parent_id WHERE [Table_Control_History].change_datetime BETWEEN @2SundaysAgo AND @lastSaturday OR [Table_Control_History].start_time between @2SundaysAgo and @lastSaturday OR ([Table_Control_History].start_time < @2SundaysAgo AND [Table_Control_History].change_datetime > @lastSaturday) GROUP BY [Table_Control].location,[Table_Control_History].os,[Table_Control_History].parent_id,[Table_Control_History].start_time,[Table_Control_History].change_datetime ORDER BY [Table_Control].placeSELECT [Location],OS,DATEDIFF (SECOND,[Active User Start],MAX([End Time])) As 'Total Time' FROM @tWHERE OS IS NOT NULLAND DATEDIFF(ss,[Active User Start],([End Time])) >= 300GROUP BY [Location],OS,[Active User Start]ORDER BY [Location][/code]I got these[code]Location OS Total Time tampa win7 2575tampa win7 157619tampa win7 3352dallas vista 604799greenbay winxp 14400greenbay win7 518400san jose winxp 228121san jose winxp 70853san jose winxp 193514san jose winxp 176290san jose winxp 110999san jose winxp 110940new york win7 136290carolina win7 604799[/code]How do update the code to get the result like this?which i want to get percentage , how do i get[code]Place OS Total Percenttampa win7 (2575+157619+3352) (2575+157619+3352)/ (7 *24 * 3600) * 100tampa unknown ((7 *24 * 3600) - (2575+157619+3352)) ((7 *24 * 3600) - (2575+157619+3352)) / (7 *24 * 3600) * 100dallas vista 604799 (604799)/(7 *24 * 3600) * 100dallas unknown ((7 *24 * 3600) - 604799) ((7 *24 * 3600)- 604799)) / (7 *24 * 3600) * 100greenbay win7 518400 (518400)/(7 *24 * 3600) * 100greenbay XP 14400 (14400)/(7 *24 * 3600) * 100greebbay unknown ((7 *24 * 3600) - (518400+14400)) ((7 *24 * 3600) - (518400+14400)) / (7 *24 * 3600) * 100....[/code]Thanks for teaching me.

Question about using a cursor or sql in application

w.musch — Thu, 10 May 2012 00:34:41 GMT

I use a dot.net application to do some import and arranging of data. there for I use some stored procedures which contains cursors; even nested cursors. Calling the stored procedures from the application will take about 5 minutes to execute and finish the work.Calling the same stored procedure from within the Management Studio will take up to 40 minutes to execute and finisch.It's the same proecdure and the result is the same. Why the difference?Also, is het better to call a stored procedure from a dot.net application or is it quicker to use plain sql statements in het application? Using stored procedures maker it easier to handle and maintain for me. But is it realy better (in performance)?thanx for the time

Where between and not between?

sabercats — Tue, 08 May 2012 12:37:56 GMT

Hi all,How do i get a report weekly from 00:00:00 Sunday (last 2 Sunday) to 23:59:59 (last saturday)1. If StartTime < last 2 Sunday then Starttime is last 2 Sunday2. If Endtime > last Saturday then Endtime is last SaturdayBut i can not use Where the Endtime is between last 2 Sunday and last Saturday, because it will not get any record which already start before last 2 sunday and end after last saturday. Any idea?Thanks.[code="sql"]declare @dow intdeclare @2SundaysAgo datetimedeclare @lastSaturday datetimeselect @dow = datepart(dw, getdate())select @2SundaysAgo = getdate() - (7 + (@dow - 1))select @lastSaturday = getdate() - (@dow) + 1select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))CREATE TABLE #Testing ( Location varchar(255), Starttime DATETIME, Endtime DATETIME ) INSERT INTO #Testing (Location, Starttime, Endtime)SELECT 'Dallas','2012-04-22 12:00:00.000','2012-04-25 13:00:12.000' UNION ALL SELECT 'Dallas','2012-04-24 12:00:00.000','2012-05-07 13:00:12.000' UNION ALL SELECT 'San Jose','2012-04-28 0:00:00.000','2012-05-06 12:59:39.370' UNION ALL SELECT 'San Jose','2012-04-30 0:00:00.000','2012-05-04 09:51:17.613' UNION ALL SELECT 'San Jose','2012-04-30 12:00:00.000','2012-05-11 09:54:48.483' UNION ALL SELECT 'San Jose','2012-04-28 0:00:00.000','2012-05-11 09:55:12.120' UNION ALL SELECT 'Tampa','2012-04-25 1:00:00.000','2012-04-30 14:23:33.000' UNION ALL SELECT 'Tampa','2012-04-29 1:00:00.000','2012-05-09 14:24:39.000' UNION ALL SELECT 'Tampa','2012-04-08 1:00:00.000','2012-05-05 16:53:44.000'select * from #Testingwhere Endtime Between @2SundayAgo AND @LastSaturdaydrop table #Testing[/code]

Creating a C# replica of the pattern extractor in SSIS2008

stephanie.sullivan — Mon, 11 Jul 2011 12:13:30 GMT

The data profiling task in SSIS has a pattern extractor that returns regex and I'd like to be able to build something similar but is applicable for mysql (ideally it should work on both). I'm quite a newb to C# and everything I've read on c# regex suggests that there is no built-in support for this type of activity.Does anyone have any insight into how the ssis task works, or has any advice on how to go about deriving regex patterns?I may just be using the wrong keywords but google has not been forthcoming on this one.

Accessing SQL Server through a web page

rd_dart — Fri, 04 May 2012 01:47:24 GMT

Hi,I have a query regarding SQL Server. Currently I am using SQL Server 2005 Express and I have a couple of databases for various clients. This database is hosted on a machine locally. What I want is that I will be able to read and update data in this database from a web page on the internet. Is it possible? Maybe it is possible with the use of web services?If someone can help me I would be very grateful. Thanks in advance for your time.

Joining two tables - matching date or previous date before that

Lars Jensen — Wed, 02 May 2012 04:41:21 GMT

I have two tables, one with a lot of cash flows and one containing foreign exchange prices for a number of currencies for almost all dates. I would like to be extract the cash flows in connection with the appropriate Rate for each cash flow. A simple inner join is not an option as there might be a cash flow on a date where the CCYrates table does not have a rate for that day, and in such case it should instead use the latest rate prior to the cash flow date. The CCYrates can only have on rate per currency per day, so no need to worry about doublets for one currency for one date. Running on MS SQL-server. The flow table has about 75k records and the CCY rates has about 36k records.Cash flows ID Date Currency char(3) ……. CCYratesIDDateCurrency char(3)RateOption 1 is to make a script that always makes sure that the CCYrates table is complete and any missing data on dates is copied from previous date. Data is normally imported in the morning, and a such script could be run automatically after the import. This solution is an option but it is not the prettiest as it creates more records in CCYrates that are needed as several records in a row will have same rate but only different rate.I am looking for the pretty way of doing this when data is pulled from the database?Is there a nice way to this with a join or would it be better to build a quick temp table for the range needed filling any blanks temporarily before running select and then joining the Cash Flows table to the temp table?

hi

daran — Tue, 24 Apr 2012 07:52:32 GMT

How do solve this trick?

sabercats — Wed, 18 Apr 2012 12:15:24 GMT

I have a database and try to get duration to make percentage.When i get records, some of them have same Start Time,[code="sql"]declare @dow intdeclare @2SundaysAgo datetimedeclare @lastSaturday datetimeselect @dow = datepart(dw, getdate())select @2SundaysAgo = getdate() - (7 + (@dow - 1))select @lastSaturday = getdate() - (@dow) + 1select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))SELECT Location ,Starttime ,Endtime (DATEDIFF (SECOND, Starttime,Endtime) as Duration FROM Testing WHERE Endtime BETWEEN @2SundaysAgo AND @lastSaturday ORDER BY Location[/code]and get this result[quote]Location Starttime Endtime DurationDallas 2012-04-08 0:00:00.000 2012-04-10 13:00:12.000 1304357San Jose 2012-04-08 0:00:00.000 2012-04-10 12:59:39.370 418234San Jose 2012-04-08 0:00:00.000 2012-04-11 09:51:17.613 493332San Jose 2012-04-08 0:00:00.000 2012-04-11 09:54:48.483 493543San Jose 2012-04-08 0:00:00.000 2012-04-11 09:55:12.120 493567Tampa 2012-04-08 1:00:00.000 2012-04-09 14:23:33.000 336867Tampa 2012-04-08 1:00:00.000 2012-04-09 14:24:39.000 336933Tampa 2012-04-08 1:00:00.000 2012-04-13 16:53:44.000 691479....[/quote]What will you write a query to get record if it has the same Location and same Starttime, then show record with the longest Duration?[code]Dallas 2012-04-08 0:00:00.000 2012-04-10 13:00:12.000 1304357San Jose 2012-04-08 00:00:00.000 2012-04-11 09:55:12.120 493567Tampa 2012-04-08 1:00:00.000 2012-04-13 16:53:44.000 691479[/code]Thanks for teaching me.

How do you count percentage?

sabercats — Tue, 17 Apr 2012 12:50:34 GMT

Hi all,This is just an example, I have a database from SSMS 2008 Express, i want to pull out info to get percentage of each animal run every week. I can get report of to view each animal, duration (Endtime-Starttime in seconds) and location (Location in color)[code="sql"]declare @dow intdeclare @2SundaysAgo datetimedeclare @lastSaturday datetimeselect @dow = datepart(dw, getdate())select @2SundaysAgo = getdate() - (7 + (@dow - 1))select @lastSaturday = getdate() - (@dow) + 1select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))SELECT Animal (DATEDIFF (SECOND, Starttime,Endtime) as Duration ,Place FROM Testing WHERE Endtime BETWEEN @2SundaysAgo AND @lastSaturday ORDER BY Animal[/code][code] Animal Duration Place cat 420761 SanJose10White cat 93332 SanJose10Black cat 893543 SanJose10Yellow dog 493567 Tampa10White dog 331875 Tampa10Black dog 418234 Tampa10Yellow dog 1304357 Dallas10Black rabbit 88341 Dallas10White rabbit 82166 Dallas10Yellow rabbit 76871 SanJose10White rabbit 48969 SanJose10Black rabbit 691479 SanJose10Yellow rabbit 23667 Tampa10White rabbit 61288 Tampa10Black rabbit 85691 Tampa10White zebra 336867 Dallas10Black zebra 336933 Dallas10White zebra 682156 Dallas10Brown zebra 394576 SanJose10Black zebra 206556 Tampa10Black zebra 778354 Tampa10Brown [/code]Now, how to i make a query to get report weekly but it give me percentage of each animal run like:[quote]cat % = total of duration for cat / ( (number of place) * 7*24*3600)) ; --> like above result it should be cat % = ( (420761 + 93332+ 893543) / ( 1 * 7*24*3600)) = 2.32%*** Treat Sanjose10 is one place no matter it is in SanJose10White, or SanJose10Black, or SanJose10Yellow)dog % = total of duration for dog / ( (number of place) * 7*24*3600)) ; like above result it should be dog % = ( (493567 + 331875 + 418234 + 1304357) / ( 2 * 7*24*3600))*** Treat Tampa10 is one place no matter it is in Tampa10White, or Tampa10Black, or Tampa10Yellow and Dallas10Black is one place ; 2 = (Dallas10 + Tampa10)rabbit % = total of duration for rabbit / ( (number of place) * 7*24*3600)) ; --> like above result it should be rabbit% = ( (88341 + 82166 + 76871 + 48969 + 691479 + 23667 + 61288 + 85691 ) / ( 3 * 7*24*3600))*** 3 = (Dallas10 + SanJose10 + Tampa10)zebra % = total of duration for zebra/ ( (number of place) * 7*24*3600)) ;unknown % = ( ((total of places * 7 * 24 * 3600) - (total of all duration))* 100 / (total of places * 7 * 24 * 3600))*** total of places is 3 = (sanJose10 + tampa10 + dallas10 )[/quote]Thanks for replying.Cheers,

I need assistance with inserting records FROM Access TO SQL using Visual Basic

phood — Tue, 10 Apr 2012 05:56:01 GMT

I have some MS Access databases that are still used by another program, but I import this data into SQL for Reporting Services. Right now I have developed a way in Excel using VBA to import the data into spreadsheets and then export it to SQL. It works well, except the code and some information is exposed to the user even if I use Excel Security to lock the code down. I am trying to write a VB application to replicate the process. I have found some example code which uses the CommandBuilder to automagically create the insert statement and it works. However, the user must only select NEW data to insert. If any PK violation occurs, it won't do anything. I want to skip or ignore duplicate rows so I need to custom write an insert statement.What the code does now is read from access and merge that data into into a dataset. Then the commandbuilder takes that dataset and imports it into SQL without any regard to PK violations.Here is the code (three different subs)...[code = "plain"]Private Sub RetrieveDataCMD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RetrieveDataCMD.Click Dim SourceConnectionString As StringDim SourceTableName As StringDim TargetConnectionString As StringDim TargetTableName As StringDim sourcesqlstring As StringDim targetsqlstring As StringDim targetwhere As StringDim sourcewhere As StringDim response As Integertargetwhere = "" '"not exists (select * from meter_data_tablev2 where meter_data_dmd_time = meter_data_table.meter_data_dmd_time and station = meter_data_table.station)" '"meter_data_dmd_time > (select MAX(meter_data_dmd_time)from Meter_Data_Tablev2)"targetsqlstring = "meter_data_dmd_time, meter_data1, Station, meter_data_meter_id"sourcewhere = "meter_data_dmd_time between #03-01-2012# and #04-01-2012#" ' & TextBox2.ToStringsourcesqlstring = "meter_data_dmd_time, meter_data1, (select meter_name from meter_table) as Station, meter_data_meter_id"SourceConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0; " & _"Data Source=C:\Program Files\Electro Industries\Communicator_Ext\Retrieved Logs\Battery Heights.EBM-DB")SourceTableName = "Meter_Data_Table"ExportTableData(SourceConnectionString, SourceTableName, oView, sourcesqlstring, sourcewhere)TargetConnectionString = ("*****")TargetTableName = "Meter_Data_Tablev2"ImportTableData(TargetConnectionString, TargetTableName, oView, targetsqlstring, targetwhere)End SubPublic Sub ExportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _ByRef oView As DataView, _ByVal SelectSQL As String, ByVal WhereSQL As String)' Export Access table to data viewDim strSQL As StringDim oRS As DataSetDim oConnOleDb As OleDbConnectionDim oCmdOleDb As OleDbCommandDim oDAOleDb As OleDbDataAdapterTry strSQL = "" oCmdOleDb = Nothing oDAOleDb = Nothing oConnOleDb = New OleDbConnection(ConnectionStr) oCmdOleDb = oConnOleDb.CreateCommand() If SelectSQL.Length = 0 Then strSQL = "SELECT * FROM " & TableName Else strSQL = "SELECT " & SelectSQL & " FROM " & TableName End If If WhereSQL.Length > 0 Then strSQL = strSQL & " WHERE " & WhereSQL End If ' Execute oCmdOleDb.CommandText = strSQL oDAOleDb = New OleDbDataAdapter(oCmdOleDb) oRS = New DataSet oDAOleDb.Fill(oRS, TableName) oView = New DataView(oRS.Tables(0)) oConnOleDb.Close() oConnOleDb = Nothing oDAOleDb = Nothing oRS = Nothing oCmdOleDb = NothingCatch ex As Exception TextBox1.Text = "Export Error: " & ex.ToStringEnd TryEnd SubPublic Sub ImportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _ByRef oImportView As DataView, _ByVal SelectSQL As String, ByVal WhereSQL As String)' Import table from data viewDim strSQL As StringDim oConn As SqlConnectionDim oCmd As SqlCommandDim oDA As SqlDataAdapterDim oRS As DataSetDim oView As DataViewTry strSQL = "" oConn = New SqlConnection(ConnectionStr) oConn.Open() oCmd = Nothing oDA = Nothing oCmd = oConn.CreateCommand() If SelectSQL.Length = 0 Then strSQL = "SELECT * FROM " & TableName Else strSQL = "SELECT " & SelectSQL & " FROM " & TableName End If If WhereSQL.Length > 0 Then strSQL = strSQL & " WHERE " & WhereSQL End If ' Execute oCmd.CommandText = strSQL oDA = New SqlDataAdapter(oCmd) oRS = New DataSet oDA.Fill(oRS, TableName) oView = New DataView(oRS.Tables(0)) ' Set rowstates so that rows are inserted oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore) ' The trick is to use CommandBuilder to create INSERT statement "automatically" Dim oCmdBuilder As SqlCommandBuilder oCmdBuilder = New SqlCommandBuilder(oDA) ' Finally call update to commit changes to database table oDA.Update(oView.Table) oConn.Close() oConn = Nothing oDA = Nothing oRS = Nothing oCmd = NothingCatch ex As Exception TextBox1.Text = "Import Error: " & ex.ToStringEnd TryEnd Sub[/code]

how to find a Server .. IS Physical machine or Virtual Machine

John Paul-702936 — Mon, 13 Dec 2010 13:58:11 GMT

Is there any way to find the server is Physical machine or Virtual machine ThanksJohn

Tell me the solution Please

iamidress — Sat, 07 Apr 2012 00:54:43 GMT

Hi allI have employees Table which canting 155 Records. I am prepared a advance payment report which is showing the Nett Post ion of payable amount. For this I less all types of loan's installment such as Festival Advance,Company Loan ,PF Loan. Motorcycle loan etc. From Actual Gross Salary . In the following Query I set a check which is when last installment of any type of loan then ignore it,but the case condition just read first condition not others and I want to read all others condition for all 155 Employees in one case. Hope some body help me. Or tell me any other solution for this.case [b]-- when wages in Negative and one loan kind is zero[/b] when (STANDARDGROSS-deductionall)<0 and (coloanBal=0) or (pfloanbal=0) or (eidadvancebal=0) or (Mcloanbal=0) THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+FLATRENTDED+EOBI+UNIONFEE+(DEDUCTIONALl-actualgross)) [b]--- when Wages Amount not in negative but one loan Type is Zero[/b] when (STANDARDGROSS-deductionall)>0 and (pfloanbal=0) and coloanbal=0 and eidadvancebal=0 and Mcloanbal=0 THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+FLATRENTDED+EOBI+UNIONFEE) [b]-- when loan type not zero[/b] when (STANDARDGROSS-deductionall)<0 or pfloanbal>0 or coloanbal>0 or eidadvancebal>0 or Mcloanbal>0 THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross) [b]--when no loan [/b] when(actualgross)-(DEDUCTIONALL)>0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+ PFLOANDED+EIDADVANCEDED+MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)[b]--- when Nett Position coming in negative[/b] when(actualgross)-(DEDUCTIONALL)<0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross) end as POSITION FROM HRMSALARY WHERE EMPCODE between 01 and 155group by HRMSALARY.EMPCODE,HRMSALARY.STANDARDGROSS,HRMSALARY.INCOMETAX,HRMSALARY.PFUNDCONT, HRMSALARY.COLOANDED,HRMSALARY.PFLOANDED,HRMSALARY.EIDADVANCEDED,HRMSALARY.MCLOANDED, HRMSALARY.FLATRENTDED,HRMSALARY.EOBI,HRMSALARY.UNIONFEE,HRMSALARY.DeductionAll,RMSALARY.ActualGross,LastMonthAdj,eobi,HRMSALARY.EidAdvanceBal,HRMSALARY.PFLoanBal, RMSALARY.CoLoanBal,HRMSALARY.MCLoanBal

VBScript: ActiveX Component can't create Object CDONTS.NewMail Code: 800A01AD VBScript

Welsh Corgi — Mon, 02 Apr 2012 11:20:52 GMT

I configured Database Mail on a new server and it goes in the queue but it does not send the message.I have tried all of the troubleshooting steps but no luck.So then I tried sending CDONTS EMail on a Server from SQL Server 2008 R2 x64 on a Server running Windows Server 2008 R2 x64 and I get command successfully completed but I do not receive an email.I do not believe that either problem is with SQL Server and I suspect it may be Windows related.I'm trying to isolate the problem so I ran the following VBScript:[code="plain"]Set objMail = CreateObject("CDONTS.Newmail")objMail.From = "username@mydomain.com"objMail.To = "username@mydomain.com"objMail.Subject = "Test"objMail.Body = "Testing"objMail.SendSet objMail = Nothing[/code]I get the following error:ActiveX Component can't create Object CDONTS.NewMail Code: 800A01AD VBScript

Help Regarding Case

iamidress — Thu, 29 Mar 2012 00:35:35 GMT

Hi All [code="plain"]SELECT EMPCODE,STANDARDGROSS,INCOMETAX,PFUNDCONT,COLOANDED,PFLOANDED,EIDADVANCEDED, MCLOANDED,FLATRENTDED,EOBI,UNIONFEE,case --1 when (DEDUCTIONALL-STANDARDGROSS)>0 then (actualgross- DEDUCTIONALL) end as Negative, case --2 when (STANDARDGROSS-deductionall)<0 and pfloanbal=0 THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+EIDADVANCEDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross) when (STANDARDGROSS-deductionall)<0 and coloanbal=0 THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+PFLOANDED+EIDADVANCEDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross) when (STANDARDGROSS-deductionall)<0 and eidadvancebal=0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross) when (STANDARDGROSS-deductionall)<0 and pfloanbal>0 and coloanbal>0 and eidadvancebal>0 and Mcloanbal>0 THEN (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)+(DEDUCTIONALL-actualgross) when(actualgross)-(DEDUCTIONALL)>0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE) when(actualgross)-(DEDUCTIONALL)<0 then (STANDARDGROSS)- sum(INCOMETAX+PFUNDCONT+COLOANDED+PFLOANDED+EIDADVANCEDED+ MCLOANDED+FLATRENTDED+EOBI+UNIONFEE)-(DEDUCTIONALL-actualgross) end as POSITION FROM HRMSALARY WHERE EMPCODE=2 ------between 51 and 100group by HRMSALARY.EMPCODE,HRMSALARY.STANDARDGROSS,HRMSALARY.INCOMETAX,HRMSALARY.PFUNDCONT, HRMSALARY.COLOANDED,HRMSALARY.PFLOANDED,HRMSALARY.EIDADVANCEDED,HRMSALARY.MCLOANDED, HRMSALARY.FLATRENTDED,HRMSALARY.EOBI,HRMSALARY.UNIONFEE,HRMSALARY.DeductionAll, HRMSALARY.ActualGross,LastMonthAdj,eobi,HRMSALARY.EidAdvanceBal,HRMSALARY.PFLoanBal, HRMSALARY.CoLoanBal,HRMSALARY.MCLoanBal[/code]problem In case -2, it just read first condition after when not others, Is any other Solution for above purpose--? PleaseRegards.

How to take a Hot backup of Oracle database

nathanz — Mon, 26 Mar 2012 13:49:11 GMT

1: put the db in archive log mode2: set the db_sid to correct one3: login to sqlplus4: verify the name of the db that you are connected to select name from v$database;5: check if the db is in archive log made select log_mode from v$database;if not in archive log modeanother command to check archive log list;6: find where on disk oracle writes archive log when it is in archive log mode sql> show parameter log_archive_dest_1; if the value is found to be 0, that means no values will be recorded, so we need to change it sql> alter system set log_archive_dest_1='LOCATION=c:\database\oradata\finance\archived_logs\' scope=spfile;7: shutdown immediate; < this is done just to prepare the db for hot backups >8: startup the db in mount mode startup mount;( 3 startup types : nomount - just starts the instance, mount - locates the control files and open up according to the values, open - finds the datafiles from the control files and opens up the db )9: put the db in archive log mode alter database archivelog;10: open the database alter database open;11: check the status of the db select log_mode from v$database; SQL> archive log list;12: create a directory for archived logcheck if its empty, if empty we need to switch sql> alter system archive log current;run it 5 times < need to put / and enter > , then check the archive log dir , we will find files 13: make a table in the database and insert data in it create table employees (fname varchar(2)); check the table desc employees;insert values insert into employees values ('Mica');14: tablespace must be in hot backup mode check the status select * from v$backup;if found not active, then we need to changewe cannot put the db in hot backup mode, unless it is archive log mode change to hot backup mode alter database begin backup; check the status select * from v$backup;15: now we can only COPY DBF FILES copy *dbf <distination location>16: need to take the db out to hot backup mode alter database end backup;17: need to make another archive log switch alter system archive log current;18: need to copy control files now, need to do a binary bckup alter database backup controlfile to '<location>\controlbackup';19: insert more values to the tableinsert into employess values ('NASH')COMMIT;make another archive log switch : alter system archive log current;do the same process for more values20 : backup all the archive logs to a new location21: shutdown the db and simulate a hw error, delete all the files from the database folder22: try to start the sqlplus and db ::: error23: copy all the backups to the db dir need to copy the control files, rename the binary backup of the control file and make the copies as needed24: try to mount the db, error < must use reset logs or noreset logs >25: need to do a recovering of the databaseshutdownrestore the archive logsstartup mount; recover database until cancel using backup controlfile;it will ask for a log file : yes for recovery cancel for cancelling recovery26: check status: open the database in readonly alter database open read only;check the tables to see the datashutdown immediateshartup mount;recover again : recover database until cancel using backup controlfile;if oracle is asking for a log that do nto exist , all we have to do is type cancel27: open the databasealter database open;need to do reset logsalter database open resetlogs;28: check the db that you are connected, check the tablesthanks and regardsVKNsite admin[url=http://www.nitrofuture.com]http://www.nitrofuture.com[/url]

SQLPASS 2009 and older - online?

virgilrucsandescu — Sun, 25 Mar 2012 22:36:48 GMT

I am sure that I have read a couple of weeks ago that all the SQLPASS summit sessions older than 2010 are online on the SQLPASS website, but I can't find them.Anybody could help me with a link, please?

how to be a comedian and writer

Sandrawmitchell — Fri, 23 Mar 2012 14:29:47 GMT

hi all let me introduce myself i am Sandra from USA. i like this forum in and all topics that's why i also want to share a one interesting topic for all.if anyone want to be a writer or comedian so come visit and join us we will help you to make your dream become in real thanks[url=http://www.adeemi.com]how to be a comedian[/url][url=http://www.adeemi.com]how to be a writer[/url]

Nested CASE statement with in operator

gregtlaughlin — Tue, 20 Mar 2012 12:07:26 GMT

Select PeriodKey, Sum(dbo_nelnet.LNLCUP) AS Balance,Case when BRQSTA in('MA','RI','CT','VT','NH') then 'New England'else case When BRQSTA in('NJ','NY','PA','DE') then 'Mid Atlantic'else case When BRQSTA in('MI','IL','OH') then 'Midwest'else 'OOFP'end end end as RegionFROM dbo_NELNET INNER JOIN dbo_NELNET_BR ON dbo_NELNET.ID = dbo_NELNET_BR.IDwhere periodkey = 1359group by periodkey,Case when BRQSTA in('MA','RI','CT','VT','NH') then 'New England'else case When BRQSTA in('NJ','NY','PA','DE') then 'Mid Atlantic'else case When BRQSTA in('MI','IL','OH') then 'Midwest'else 'OOFP'end end end as RegionThe code works up until the very last line (14 - end end end as Region in the group by statement) when the following error pops up:Msg 156, Level 15, State 1, Line 14Incorrect syntax near the keyword 'as'.What am I missing? Any assistance would be greatly appreciated. Thanks.

Regsrvr.xml Programing

katuil — Mon, 27 Feb 2012 12:35:37 GMT

Hello, this may need to be in the SMO topic since it may deal with using SMO. Anyway, has anyone every looked at the structure of the regsrvr.xml? What I am looking for is the structure of the xml and how new entries are saved into the file. Possibly a MS document on the file (one can always hope). What I want to do is take an empty regsrvr.xml and add resgistered servers on my network to it. I know that thorough SMO this can be done, but I was potentially wanting to use simple IO to ready and write to the file. I know that each entry has serveral key factors and those are what I am interested in. I am using VS2010 C# and this is ASP.NET based application runnig on Server 2008. Let me know if I have not made clear what I am wanting to do or if someone else has done something similar. In searching the net I did not find much aside from using SMO. Thanks

SSIS

zznw4m — Wed, 14 Mar 2012 10:03:00 GMT

Hi,I've managed to create a C# program to programmatically create and execute an SSIS package. However, I have no control of the Users who sometimes do strange things like change the case of the column names. Because SSIS is case sensitive this will cause the package to fail when it tries to validate the SQL select against the metadata. The solution to this when creating a package in the designer is to set the ValidateExternalMetaData property on the Data Flow components to 'false'. I've tried doing this in the code but it doesn't seem to have any effect.Has anyone any idea how to do this?I can post the code if anybody wants to see it.Regards,Terry

Macro like function in SQL

arpost — Wed, 22 Feb 2012 15:42:24 GMT

I had some familiarty with Macros before when I used some SAS, but now that I do SQL exclusively I struggle with performing functions that a macro would be ideal for.Can anyone show me a good example of how to simplify this code to create a table? I took a good bit out of it since my [Days to Contract] field goes all the way up to 30 and I have 10+ LOBs. Just want something that maybe can do maybe a do while loop that can count [Days to Contract] all the way up to 30 and give me a simplified way to add additional LOBs.Thanks in advance for any help. IF OBJECT_ID('TempDB..#TEMP','U') IS NOT NULL DROP TABLE #TEMP CREATE TABLE #TEMP ( LOB VARCHAR(25), [Days Since App] INT, [Days to Contract] VARCHAR(6) )INSERT INTO #TEMP (LOB, [Days Since App], [Days to Contract]) SELECT 'CHR','0','00' UNION ALL SELECT 'CHR','1','00' UNION ALL SELECT 'CHR','2','00' UNION ALL SELECT 'CHR','3','00' UNION ALL SELECT 'CHR','4','00' UNION ALL SELECT 'CHR','5','00' UNION ALL SELECT 'CHR','6','00' UNION ALL SELECT 'CHR','7','00' UNION ALL SELECT 'CHR','8','00' UNION ALL SELECT 'CHR','9','00' UNION ALL SELECT 'CHR','10','00' UNION ALL SELECT 'CHR','11','00' UNION ALL SELECT 'CHR','12','00' UNION ALL SELECT 'CHR','13','00' UNION ALL SELECT 'CHR','14','00' UNION ALL SELECT 'CHR','15','00' UNION ALL SELECT 'CHR','16','00' UNION ALL SELECT 'CHR','17','00' UNION ALL SELECT 'CHR','18','00' UNION ALL SELECT 'CHR','19','00' UNION ALL SELECT 'CHR','20','00' UNION ALL SELECT 'CHR','21','00' UNION ALL SELECT 'CHR','22','00' UNION ALL SELECT 'CHR','23','00' UNION ALL SELECT 'CHR','24','00' UNION ALL SELECT 'CHR','25','00' UNION ALL SELECT 'CHR','26','00' UNION ALL SELECT 'CHR','27','00' UNION ALL SELECT 'CHR','28','00' UNION ALL SELECT 'CHR','29','00' UNION ALL SELECT 'CHR','30','00' UNION ALL SELECT 'CHR','31','00' UNION ALL SELECT 'CHR','32','00' UNION ALL SELECT 'CHR','33','00' UNION ALL SELECT 'CHR','34','00' UNION ALL SELECT 'CHR','35','00' UNION ALL SELECT 'CHR','36','00' UNION ALL SELECT 'CHR','37','00' UNION ALL SELECT 'CHR','38','00' UNION ALL SELECT 'CHR','39','00' UNION ALL SELECT 'CHR','40','00' UNION ALL SELECT 'CHR','41','00' UNION ALL SELECT 'CHR','42','00' UNION ALL SELECT 'CHR','43','00' UNION ALL SELECT 'CHR','44','00' UNION ALL SELECT 'CHR','45','00' UNION ALL SELECT 'RL Retail','0','00' UNION ALL SELECT 'RL Retail','1','00' UNION ALL SELECT 'RL Retail','2','00' UNION ALL SELECT 'RL Retail','3','00' UNION ALL SELECT 'RL Retail','4','00' UNION ALL SELECT 'RL Retail','5','00' UNION ALL SELECT 'RL Retail','6','00' UNION ALL SELECT 'RL Retail','7','00' UNION ALL SELECT 'RL Retail','8','00' UNION ALL SELECT 'RL Retail','9','00' UNION ALL SELECT 'RL Retail','10','00' UNION ALL SELECT 'RL Retail','11','00' UNION ALL SELECT 'RL Retail','12','00' UNION ALL SELECT 'RL Retail','13','00' UNION ALL SELECT 'RL Retail','14','00' UNION ALL SELECT 'RL Retail','15','00' UNION ALL SELECT 'RL Retail','16','00' UNION ALL SELECT 'RL Retail','17','00' UNION ALL SELECT 'RL Retail','18','00' UNION ALL SELECT 'RL Retail','19','00' UNION ALL SELECT 'RL Retail','20','00' UNION ALL SELECT 'RL Retail','21','00' UNION ALL SELECT 'RL Retail','22','00' UNION ALL SELECT 'RL Retail','23','00' UNION ALL SELECT 'RL Retail','24','00' UNION ALL SELECT 'RL Retail','25','00' UNION ALL SELECT 'RL Retail','26','00' UNION ALL SELECT 'RL Retail','27','00' UNION ALL SELECT 'RL Retail','28','00' UNION ALL SELECT 'RL Retail','29','00' UNION ALL SELECT 'RL Retail','30','00' UNION ALL SELECT 'RL Retail','31','00' UNION ALL SELECT 'RL Retail','32','00' UNION ALL SELECT 'RL Retail','33','00' UNION ALL SELECT 'RL Retail','34','00' UNION ALL SELECT 'RL Retail','35','00' UNION ALL SELECT 'RL Retail','36','00' UNION ALL SELECT 'RL Retail','37','00' UNION ALL SELECT 'RL Retail','38','00' UNION ALL SELECT 'RL Retail','39','00' UNION ALL SELECT 'RL Retail','40','00' UNION ALL SELECT 'RL Retail','41','00' UNION ALL SELECT 'RL Retail','42','00' UNION ALL SELECT 'RL Retail','43','00' UNION ALL SELECT 'RL Retail','44','00' UNION ALL SELECT 'RL Retail','45','00' UNION ALL SELECT 'SAF','0','00' UNION ALL SELECT 'SAF','1','00' UNION ALL SELECT 'SAF','2','00' UNION ALL SELECT 'SAF','3','00' UNION ALL SELECT 'SAF','4','00' UNION ALL SELECT 'SAF','5','00' UNION ALL SELECT 'SAF','6','00' UNION ALL SELECT 'SAF','7','00' UNION ALL SELECT 'SAF','8','00' UNION ALL SELECT 'SAF','9','00' UNION ALL SELECT 'SAF','10','00' UNION ALL SELECT 'SAF','11','00' UNION ALL SELECT 'SAF','12','00' UNION ALL SELECT 'SAF','13','00' UNION ALL SELECT 'SAF','14','00' UNION ALL SELECT 'SAF','15','00' UNION ALL SELECT 'SAF','16','00' UNION ALL SELECT 'SAF','17','00' UNION ALL SELECT 'SAF','18','00' UNION ALL SELECT 'SAF','19','00' UNION ALL SELECT 'SAF','20','00' UNION ALL SELECT 'SAF','21','00' UNION ALL SELECT 'SAF','22','00' UNION ALL SELECT 'SAF','23','00' UNION ALL SELECT 'SAF','24','00' UNION ALL SELECT 'SAF','25','00' UNION ALL SELECT 'SAF','26','00' UNION ALL SELECT 'SAF','27','00' UNION ALL SELECT 'SAF','28','00' UNION ALL SELECT 'SAF','29','00' UNION ALL SELECT 'SAF','30','00' UNION ALL SELECT 'SAF','31','00' UNION ALL SELECT 'SAF','32','00' UNION ALL SELECT 'SAF','33','00' UNION ALL SELECT 'SAF','34','00' UNION ALL SELECT 'SAF','35','00' UNION ALL SELECT 'SAF','36','00' UNION ALL SELECT 'SAF','37','00' UNION ALL SELECT 'SAF','38','00' UNION ALL SELECT 'SAF','39','00' UNION ALL SELECT 'SAF','40','00' UNION ALL SELECT 'SAF','41','00' UNION ALL SELECT 'SAF','42','00' UNION ALL SELECT 'SAF','43','00' UNION ALL SELECT 'SAF','44','00' UNION ALL SELECT 'SAF','45','00' UNION ALL SELECT 'CHR','0','01' UNION ALL SELECT 'CHR','1','01' UNION ALL SELECT 'CHR','2','01' UNION ALL SELECT 'CHR','3','01' UNION ALL SELECT 'CHR','4','01' UNION ALL SELECT 'CHR','5','01' UNION ALL SELECT 'CHR','6','01' UNION ALL SELECT 'CHR','7','01' UNION ALL SELECT 'CHR','8','01' UNION ALL SELECT 'CHR','9','01' UNION ALL SELECT 'CHR','10','01' UNION ALL SELECT 'CHR','11','01' UNION ALL SELECT 'CHR','12','01' UNION ALL SELECT 'CHR','13','01' UNION ALL SELECT 'CHR','14','01' UNION ALL SELECT 'CHR','15','01' UNION ALL SELECT 'CHR','16','01' UNION ALL SELECT 'CHR','17','01' UNION ALL SELECT 'CHR','18','01' UNION ALL SELECT 'CHR','19','01' UNION ALL SELECT 'CHR','20','01' UNION ALL SELECT 'CHR','21','01' UNION ALL SELECT 'CHR','22','01' UNION ALL SELECT 'CHR','23','01' UNION ALL SELECT 'CHR','24','01' UNION ALL SELECT 'CHR','25','01' UNION ALL SELECT 'CHR','26','01' UNION ALL SELECT 'CHR','27','01' UNION ALL SELECT 'CHR','28','01' UNION ALL SELECT 'CHR','29','01' UNION ALL SELECT 'CHR','30','01' UNION ALL SELECT 'CHR','31','01' UNION ALL SELECT 'CHR','32','01' UNION ALL SELECT 'CHR','33','01' UNION ALL SELECT 'CHR','34','01' UNION ALL SELECT 'CHR','35','01' UNION ALL SELECT 'CHR','36','01' UNION ALL SELECT 'CHR','37','01' UNION ALL SELECT 'CHR','38','01' UNION ALL SELECT 'CHR','39','01' UNION ALL SELECT 'CHR','40','01' UNION ALL SELECT 'CHR','41','01' UNION ALL SELECT 'CHR','42','01' UNION ALL SELECT 'CHR','43','01' UNION ALL SELECT 'CHR','44','01' UNION ALL SELECT 'CHR','45','01' UNION ALL SELECT 'RL Retail','0','01' UNION ALL SELECT 'RL Retail','1','01' UNION ALL SELECT 'RL Retail','2','01' UNION ALL SELECT 'RL Retail','3','01' UNION ALL SELECT 'RL Retail','4','01' UNION ALL SELECT 'RL Retail','5','01' UNION ALL SELECT 'RL Retail','6','01' UNION ALL SELECT 'RL Retail','7','01' UNION ALL SELECT 'RL Retail','8','01' UNION ALL SELECT 'RL Retail','9','01' UNION ALL SELECT 'RL Retail','10','01' UNION ALL SELECT 'RL Retail','11','01' UNION ALL SELECT 'RL Retail','12','01' UNION ALL SELECT 'RL Retail','13','01' UNION ALL SELECT 'RL Retail','14','01' UNION ALL SELECT 'RL Retail','15','01' UNION ALL SELECT 'RL Retail','16','01' UNION ALL SELECT 'RL Retail','17','01' UNION ALL SELECT 'RL Retail','18','01' UNION ALL SELECT 'RL Retail','19','01' UNION ALL SELECT 'RL Retail','20','01' UNION ALL SELECT 'RL Retail','21','01' UNION ALL SELECT 'RL Retail','22','01' UNION ALL SELECT 'RL Retail','23','01' UNION ALL SELECT 'RL Retail','24','01' UNION ALL SELECT 'RL Retail','25','01' UNION ALL SELECT 'RL Retail','26','01' UNION ALL SELECT 'RL Retail','27','01' UNION ALL SELECT 'RL Retail','28','01' UNION ALL SELECT 'RL Retail','29','01' UNION ALL SELECT 'RL Retail','30','01' UNION ALL SELECT 'RL Retail','31','01' UNION ALL SELECT 'RL Retail','32','01' UNION ALL SELECT 'RL Retail','33','01' UNION ALL SELECT 'RL Retail','34','01' UNION ALL SELECT 'RL Retail','35','01' UNION ALL SELECT 'RL Retail','36','01' UNION ALL SELECT 'RL Retail','37','01' UNION ALL SELECT 'RL Retail','38','01' UNION ALL SELECT 'RL Retail','39','01' UNION ALL SELECT 'RL Retail','40','01' UNION ALL SELECT 'RL Retail','41','01' UNION ALL SELECT 'RL Retail','42','01' UNION ALL SELECT 'RL Retail','43','01' UNION ALL SELECT 'RL Retail','44','01' UNION ALL SELECT 'RL Retail','45','01' UNION ALL SELECT 'SAF','0','01' UNION ALL SELECT 'SAF','1','01' UNION ALL SELECT 'SAF','2','01' UNION ALL SELECT 'SAF','3','01' UNION ALL SELECT 'SAF','4','01' UNION ALL SELECT 'SAF','5','01' UNION ALL SELECT 'SAF','6','01' UNION ALL SELECT 'SAF','7','01' UNION ALL SELECT 'SAF','8','01' UNION ALL SELECT 'SAF','9','01' UNION ALL SELECT 'SAF','10','01' UNION ALL SELECT 'SAF','11','01' UNION ALL SELECT 'SAF','12','01' UNION ALL SELECT 'SAF','13','01' UNION ALL SELECT 'SAF','14','01' UNION ALL SELECT 'SAF','15','01' UNION ALL SELECT 'SAF','16','01' UNION ALL SELECT 'SAF','17','01' UNION ALL SELECT 'SAF','18','01' UNION ALL SELECT 'SAF','19','01' UNION ALL SELECT 'SAF','20','01' UNION ALL SELECT 'SAF','21','01' UNION ALL SELECT 'SAF','22','01' UNION ALL SELECT 'SAF','23','01' UNION ALL SELECT 'SAF','24','01' UNION ALL SELECT 'SAF','25','01' UNION ALL SELECT 'SAF','26','01' UNION ALL SELECT 'SAF','27','01' UNION ALL SELECT 'SAF','28','01' UNION ALL SELECT 'SAF','29','01' UNION ALL SELECT 'SAF','30','01' UNION ALL SELECT 'SAF','31','01' UNION ALL SELECT 'SAF','32','01' UNION ALL SELECT 'SAF','33','01' UNION ALL SELECT 'SAF','34','01' UNION ALL SELECT 'SAF','35','01' UNION ALL SELECT 'SAF','36','01' UNION ALL SELECT 'SAF','37','01' UNION ALL SELECT 'SAF','38','01' UNION ALL SELECT 'SAF','39','01' UNION ALL SELECT 'SAF','40','01' UNION ALL SELECT 'SAF','41','01' UNION ALL SELECT 'SAF','42','01' UNION ALL SELECT 'SAF','43','01' UNION ALL SELECT 'SAF','44','01' UNION ALL SELECT 'SAF','45','01' UNION ALL SELECT 'CHR','0','02' UNION ALL SELECT 'CHR','1','02' UNION ALL SELECT 'CHR','2','02' UNION ALL SELECT 'CHR','3','02' UNION ALL SELECT 'CHR','4','02' UNION ALL SELECT 'CHR','5','02' UNION ALL SELECT 'CHR','6','02' UNION ALL SELECT 'CHR','7','02' UNION ALL SELECT 'CHR','8','02' UNION ALL SELECT 'CHR','9','02' UNION ALL SELECT 'CHR','10','02' UNION ALL SELECT 'CHR','11','02' UNION ALL SELECT 'CHR','12','02' UNION ALL SELECT 'CHR','13','02' UNION ALL SELECT 'CHR','14','02' UNION ALL SELECT 'CHR','15','02' UNION ALL SELECT 'CHR','16','02' UNION ALL SELECT 'CHR','17','02' UNION ALL SELECT 'CHR','18','02' UNION ALL SELECT 'CHR','19','02' UNION ALL SELECT 'CHR','20','02' UNION ALL SELECT 'CHR','21','02' UNION ALL SELECT 'CHR','22','02' UNION ALL SELECT 'CHR','23','02' UNION ALL SELECT 'CHR','24','02' UNION ALL SELECT 'CHR','25','02' UNION ALL SELECT 'CHR','26','02' UNION ALL SELECT 'CHR','27','02' UNION ALL SELECT 'CHR','28','02' UNION ALL SELECT 'CHR','29','02' UNION ALL SELECT 'CHR','30','02' UNION ALL SELECT 'CHR','31','02' UNION ALL SELECT 'CHR','32','02' UNION ALL SELECT 'CHR','33','02' UNION ALL SELECT 'CHR','34','02' UNION ALL SELECT 'CHR','35','02' UNION ALL SELECT 'CHR','36','02' UNION ALL SELECT 'CHR','37','02' UNION ALL SELECT 'CHR','38','02' UNION ALL SELECT 'CHR','39','02' UNION ALL SELECT 'CHR','40','02' UNION ALL SELECT 'CHR','41','02' UNION ALL SELECT 'CHR','42','02' UNION ALL SELECT 'CHR','43','02' UNION ALL SELECT 'CHR','44','02' UNION ALL SELECT 'CHR','45','02' UNION ALL SELECT 'RL Retail','0','02' UNION ALL SELECT 'RL Retail','1','02' UNION ALL SELECT 'RL Retail','2','02' UNION ALL SELECT 'RL Retail','3','02' UNION ALL SELECT 'RL Retail','4','02' UNION ALL SELECT 'RL Retail','5','02' UNION ALL SELECT 'RL Retail','6','02' UNION ALL SELECT 'RL Retail','7','02' UNION ALL SELECT 'RL Retail','8','02' UNION ALL SELECT 'RL Retail','9','02' UNION ALL SELECT 'RL Retail','10','02' UNION ALL SELECT 'RL Retail','11','02' UNION ALL SELECT 'RL Retail','12','02' UNION ALL SELECT 'RL Retail','13','02' UNION ALL SELECT 'RL Retail','14','02' UNION ALL SELECT 'RL Retail','15','02' UNION ALL SELECT 'RL Retail','16','02' UNION ALL SELECT 'RL Retail','17','02' UNION ALL SELECT 'RL Retail','18','02' UNION ALL SELECT 'RL Retail','19','02' UNION ALL SELECT 'RL Retail','20','02' UNION ALL SELECT 'RL Retail','21','02' UNION ALL SELECT 'RL Retail','22','02' UNION ALL SELECT 'RL Retail','23','02' UNION ALL SELECT 'RL Retail','24','02' UNION ALL SELECT 'RL Retail','25','02' UNION ALL SELECT 'RL Retail','26','02' UNION ALL SELECT 'RL Retail','27','02' UNION ALL SELECT 'RL Retail','28','02' UNION ALL SELECT 'RL Retail','29','02' UNION ALL SELECT 'RL Retail','30','02' UNION ALL SELECT 'RL Retail','31','02' UNION ALL SELECT 'RL Retail','32','02' UNION ALL SELECT 'RL Retail','33','02' UNION ALL SELECT 'RL Retail','34','02' UNION ALL SELECT 'RL Retail','35','02' UNION ALL SELECT 'RL Retail','36','02' UNION ALL SELECT 'RL Retail','37','02' UNION ALL SELECT 'RL Retail','38','02' UNION ALL SELECT 'RL Retail','39','02' UNION ALL SELECT 'RL Retail','40','02' UNION ALL SELECT 'RL Retail','41','02' UNION ALL SELECT 'RL Retail','42','02' UNION ALL SELECT 'RL Retail','43','02' UNION ALL SELECT 'RL Retail','44','02' UNION ALL SELECT 'RL Retail','45','02' UNION ALL SELECT 'SAF','0','02' UNION ALL SELECT 'SAF','1','02' UNION ALL SELECT 'SAF','2','02' UNION ALL SELECT 'SAF','3','02' UNION ALL SELECT 'SAF','4','02' UNION ALL SELECT 'SAF','5','02' UNION ALL SELECT 'SAF','6','02' UNION ALL SELECT 'SAF','7','02' UNION ALL SELECT 'SAF','8','02' UNION ALL SELECT 'SAF','9','02' UNION ALL SELECT 'SAF','10','02' UNION ALL SELECT 'SAF','11','02' UNION ALL SELECT 'SAF','12','02' UNION ALL SELECT 'SAF','13','02' UNION ALL SELECT 'SAF','14','02' UNION ALL SELECT 'SAF','15','02' UNION ALL SELECT 'SAF','16','02' UNION ALL SELECT 'SAF','17','02' UNION ALL SELECT 'SAF','18','02' UNION ALL SELECT 'SAF','19','02' UNION ALL SELECT 'SAF','20','02' UNION ALL SELECT 'SAF','21','02' UNION ALL SELECT 'SAF','22','02' UNION ALL SELECT 'SAF','23','02' UNION ALL SELECT 'SAF','24','02' UNION ALL SELECT 'SAF','25','02' UNION ALL SELECT 'SAF','26','02' UNION ALL SELECT 'SAF','27','02' UNION ALL SELECT 'SAF','28','02' UNION ALL SELECT 'SAF','29','02' UNION ALL SELECT 'SAF','30','02' UNION ALL SELECT 'SAF','31','02' UNION ALL SELECT 'SAF','32','02' UNION ALL SELECT 'SAF','33','02' UNION ALL SELECT 'SAF','34','02' UNION ALL SELECT 'SAF','35','02' UNION ALL SELECT 'SAF','36','02' UNION ALL SELECT 'SAF','37','02' UNION ALL SELECT 'SAF','38','02' UNION ALL SELECT 'SAF','39','02' UNION ALL SELECT 'SAF','40','02' UNION ALL SELECT 'SAF','41','02' UNION ALL SELECT 'SAF','42','02' UNION ALL SELECT 'SAF','43','02' UNION ALL SELECT 'SAF','44','02' UNION ALL SELECT 'SAF','45','02' UNION ALL SELECT 'CHR','0','03' UNION ALL SELECT 'CHR','1','03' UNION ALL SELECT 'CHR','2','03' UNION ALL SELECT 'CHR','3','03' UNION ALL SELECT 'CHR','4','03' UNION ALL SELECT 'CHR','5','03' UNION ALL SELECT 'CHR','6','03' UNION ALL SELECT 'CHR','7','03' UNION ALL SELECT 'CHR','8','03' UNION ALL SELECT 'CHR','9','03' UNION ALL SELECT 'CHR','10','03' UNION ALL SELECT 'CHR','11','03' UNION ALL SELECT 'CHR','12','03' UNION ALL SELECT 'CHR','13','03' UNION ALL SELECT 'CHR','14','03' UNION ALL SELECT 'CHR','15','03' UNION ALL SELECT 'CHR','16','03' UNION ALL SELECT 'CHR','17','03' UNION ALL SELECT 'CHR','18','03' UNION ALL SELECT 'CHR','19','03' UNION ALL SELECT 'CHR','20','03' UNION ALL SELECT 'CHR','21','03' UNION ALL SELECT 'CHR','22','03' UNION ALL SELECT 'CHR','23','03' UNION ALL SELECT 'CHR','24','03' UNION ALL SELECT 'CHR','25','03' UNION ALL SELECT 'CHR','26','03' UNION ALL SELECT 'CHR','27','03' UNION ALL SELECT 'CHR','28','03' UNION ALL SELECT 'CHR','29','03' UNION ALL SELECT 'CHR','30','03' UNION ALL SELECT 'CHR','31','03' UNION ALL SELECT 'CHR','32','03' UNION ALL SELECT 'CHR','33','03' UNION ALL SELECT 'CHR','34','03' UNION ALL SELECT 'CHR','35','03' UNION ALL SELECT 'CHR','36','03' UNION ALL SELECT 'CHR','37','03' UNION ALL SELECT 'CHR','38','03' UNION ALL SELECT 'CHR','39','03' UNION ALL SELECT 'CHR','40','03' UNION ALL SELECT 'CHR','41','03' UNION ALL SELECT 'CHR','42','03' UNION ALL SELECT 'CHR','43','03' UNION ALL SELECT 'CHR','44','03' UNION ALL SELECT 'CHR','45','03' UNION ALL SELECT 'RL Retail','0','03' UNION ALL SELECT 'RL Retail','1','03' UNION ALL SELECT 'RL Retail','2','03' UNION ALL SELECT 'RL Retail','3','03' UNION ALL SELECT 'RL Retail','4','03' UNION ALL SELECT 'RL Retail','5','03' UNION ALL SELECT 'RL Retail','6','03' UNION ALL SELECT 'RL Retail','7','03' UNION ALL SELECT 'RL Retail','8','03' UNION ALL SELECT 'RL Retail','9','03' UNION ALL SELECT 'RL Retail','10','03' UNION ALL SELECT 'RL Retail','11','03' UNION ALL SELECT 'RL Retail','12','03' UNION ALL SELECT 'RL Retail','13','03' UNION ALL SELECT 'RL Retail','14','03' UNION ALL SELECT 'RL Retail','15','03' UNION ALL SELECT 'RL Retail','16','03' UNION ALL SELECT 'RL Retail','17','03' UNION ALL SELECT 'RL Retail','18','03' UNION ALL SELECT 'RL Retail','19','03' UNION ALL SELECT 'RL Retail','20','03' UNION ALL SELECT 'RL Retail','21','03' UNION ALL SELECT 'RL Retail','22','03' UNION ALL SELECT 'RL Retail','23','03' UNION ALL SELECT 'RL Retail','24','03' UNION ALL SELECT 'RL Retail','25','03' UNION ALL SELECT 'RL Retail','26','03' UNION ALL SELECT 'RL Retail','27','03' UNION ALL SELECT 'RL Retail','28','03' UNION ALL SELECT 'RL Retail','29','03' UNION ALL SELECT 'RL Retail','30','03' UNION ALL SELECT 'RL Retail','31','03' UNION ALL SELECT 'RL Retail','32','03' UNION ALL SELECT 'RL Retail','33','03' UNION ALL SELECT 'RL Retail','34','03' UNION ALL SELECT 'RL Retail','35','03' UNION ALL SELECT 'RL Retail','36','03' UNION ALL SELECT 'RL Retail','37','03' UNION ALL SELECT 'RL Retail','38','03' UNION ALL SELECT 'RL Retail','39','03' UNION ALL SELECT 'RL Retail','40','03' UNION ALL SELECT 'RL Retail','41','03' UNION ALL SELECT 'RL Retail','42','03' UNION ALL SELECT 'RL Retail','43','03' UNION ALL SELECT 'RL Retail','44','03' UNION ALL SELECT 'RL Retail','45','03' UNION ALL SELECT 'SAF','0','03' UNION ALL SELECT 'SAF','1','03' UNION ALL SELECT 'SAF','2','03' UNION ALL SELECT 'SAF','3','03' UNION ALL SELECT 'SAF','4','03' UNION ALL SELECT 'SAF','5','03' UNION ALL SELECT 'SAF','6','03' UNION ALL SELECT 'SAF','7','03' UNION ALL SELECT 'SAF','8','03' UNION ALL SELECT 'SAF','9','03' UNION ALL SELECT 'SAF','10','03' UNION ALL SELECT 'SAF','11','03' UNION ALL SELECT 'SAF','12','03' UNION ALL SELECT 'SAF','13','03' UNION ALL SELECT 'SAF','14','03' UNION ALL SELECT 'SAF','15','03' UNION ALL SELECT 'SAF','16','03' UNION ALL SELECT 'SAF','17','03' UNION ALL SELECT 'SAF','18','03' UNION ALL SELECT 'SAF','19','03' UNION ALL SELECT 'SAF','20','03' UNION ALL SELECT 'SAF','21','03' UNION ALL SELECT 'SAF','22','03' UNION ALL SELECT 'SAF','23','03' UNION ALL SELECT 'SAF','24','03' UNION ALL SELECT 'SAF','25','03' UNION ALL SELECT 'SAF','26','03' UNION ALL SELECT 'SAF','27','03' UNION ALL SELECT 'SAF','28','03' UNION ALL SELECT 'SAF','29','03' UNION ALL SELECT 'SAF','30','03' UNION ALL SELECT 'SAF','31','03' UNION ALL SELECT 'SAF','32','03' UNION ALL SELECT 'SAF','33','03' UNION ALL SELECT 'SAF','34','03' UNION ALL SELECT 'SAF','35','03' UNION ALL SELECT 'SAF','36','03' UNION ALL SELECT 'SAF','37','03' UNION ALL SELECT 'SAF','38','03' UNION ALL SELECT 'SAF','39','03' UNION ALL SELECT 'SAF','40','03' UNION ALL SELECT 'SAF','41','03' UNION ALL SELECT 'SAF','42','03' UNION ALL SELECT 'SAF','43','03' UNION ALL SELECT 'SAF','44','03' UNION ALL SELECT 'SAF','45','03' UNION ALL SELECT 'CHR','0','04' UNION ALL SELECT 'CHR','1','04' UNION ALL SELECT 'CHR','2','04' UNION ALL SELECT 'CHR','3','04' UNION ALL SELECT 'CHR','4','04' UNION ALL SELECT 'CHR','5','04' UNION ALL SELECT 'CHR','6','04' UNION ALL SELECT 'CHR','7','04' UNION ALL SELECT 'CHR','8','04' UNION ALL SELECT 'CHR','9','04' UNION ALL SELECT 'CHR','10','04' UNION ALL SELECT 'CHR','11','04' UNION ALL SELECT 'CHR','12','04' UNION ALL SELECT 'CHR','13','04' UNION ALL SELECT 'CHR','14','04' UNION ALL SELECT 'CHR','15','04' UNION ALL SELECT 'CHR','16','04' UNION ALL SELECT 'CHR','17','04' UNION ALL SELECT 'CHR','18','04' UNION ALL SELECT 'CHR','19','04' UNION ALL SELECT 'CHR','20','04' UNION ALL SELECT 'CHR','21','04' UNION ALL SELECT 'CHR','22','04' UNION ALL SELECT 'CHR','23','04' UNION ALL SELECT 'CHR','24','04' UNION ALL SELECT 'CHR','25','04' UNION ALL SELECT 'CHR','26','04' UNION ALL SELECT 'CHR','27','04' UNION ALL SELECT 'CHR','28','04' UNION ALL SELECT 'CHR','29','04' UNION ALL SELECT 'CHR','30','04' UNION ALL SELECT 'CHR','31','04' UNION ALL SELECT 'CHR','32','04' UNION ALL SELECT 'CHR','33','04' UNION ALL SELECT 'CHR','34','04' UNION ALL SELECT 'CHR','35','04' UNION ALL SELECT 'CHR','36','04' UNION ALL SELECT 'CHR','37','04' UNION ALL SELECT 'CHR','38','04' UNION ALL SELECT 'CHR','39','04' UNION ALL SELECT 'CHR','40','04' UNION ALL SELECT 'CHR','41','04' UNION ALL SELECT 'CHR','42','04' UNION ALL SELECT 'CHR','43','04' UNION ALL SELECT 'CHR','44','04' UNION ALL SELECT 'CHR','45','04' UNION ALL SELECT 'RL Retail','0','04' UNION ALL SELECT 'RL Retail','1','04' UNION ALL SELECT 'RL Retail','2','04' UNION ALL SELECT 'RL Retail','3','04' UNION ALL SELECT 'RL Retail','4','04' UNION ALL SELECT 'RL Retail','5','04' UNION ALL SELECT 'RL Retail','6','04' UNION ALL SELECT 'RL Retail','7','04' UNION ALL SELECT 'RL Retail','8','04' UNION ALL SELECT 'RL Retail','9','04' UNION ALL SELECT 'RL Retail','10','04' UNION ALL SELECT 'RL Retail','11','04' UNION ALL SELECT 'RL Retail','12','04' UNION ALL SELECT 'RL Retail','13','04' UNION ALL SELECT 'RL Retail','14','04' UNION ALL SELECT 'RL Retail','15','04' UNION ALL SELECT 'RL Retail','16','04' UNION ALL SELECT 'RL Retail','17','04' UNION ALL SELECT 'RL Retail','18','04' UNION ALL SELECT 'RL Retail','19','04' UNION ALL SELECT 'RL Retail','20','04' UNION ALL SELECT 'RL Retail','21','04' UNION ALL SELECT 'RL Retail','22','04' UNION ALL SELECT 'RL Retail','23','04' UNION ALL SELECT 'RL Retail','24','04' UNION ALL SELECT 'RL Retail','25','04' UNION ALL SELECT 'RL Retail','26','04' UNION ALL SELECT 'RL Retail','27','04' UNION ALL SELECT 'RL Retail','28','04' UNION ALL SELECT 'RL Retail','29','04' UNION ALL SELECT 'RL Retail','30','04' UNION ALL SELECT 'RL Retail','31','04' UNION ALL SELECT 'RL Retail','32','04' UNION ALL SELECT 'RL Retail','33','04' UNION ALL SELECT 'RL Retail','34','04' UNION ALL SELECT 'RL Retail','35','04' UNION ALL SELECT 'RL Retail','36','04' UNION ALL SELECT 'RL Retail','37','04' UNION ALL SELECT 'RL Retail','38','04' UNION ALL SELECT 'RL Retail','39','04' UNION ALL SELECT 'RL Retail','40','04' UNION ALL SELECT 'RL Retail','41','04' UNION ALL SELECT 'RL Retail','42','04' UNION ALL SELECT 'RL Retail','43','04' UNION ALL SELECT 'RL Retail','44','04' UNION ALL SELECT 'RL Retail','45','04' UNION ALL SELECT 'SAF','0','04' UNION ALL SELECT 'SAF','1','04' UNION ALL SELECT 'SAF','2','04' UNION ALL SELECT 'SAF','3','04' UNION ALL SELECT 'SAF','4','04' UNION ALL SELECT 'SAF','5','04' UNION ALL SELECT 'SAF','6','04' UNION ALL SELECT 'SAF','7','04' UNION ALL SELECT 'SAF','8','04' UNION ALL SELECT 'SAF','9','04' UNION ALL SELECT 'SAF','10','04' UNION ALL SELECT 'SAF','11','04' UNION ALL SELECT 'SAF','12','04' UNION ALL SELECT 'SAF','13','04' UNION ALL SELECT 'SAF','14','04' UNION ALL SELECT 'SAF','15','04' UNION ALL SELECT 'SAF','16','04' UNION ALL SELECT 'SAF','17','04' UNION ALL SELECT 'SAF','18','04' UNION ALL SELECT 'SAF','19','04' UNION ALL SELECT 'SAF','20','04' UNION ALL SELECT 'SAF','21','04' UNION ALL SELECT 'SAF','22','04' UNION ALL SELECT 'SAF','23','04' UNION ALL SELECT 'SAF','24','04' UNION ALL SELECT 'SAF','25','04' UNION ALL SELECT 'SAF','26','04' UNION ALL SELECT 'SAF','27','04' UNION ALL SELECT 'SAF','28','04' UNION ALL SELECT 'SAF','29','04' UNION ALL SELECT 'SAF','30','04' UNION ALL SELECT 'SAF','31','04' UNION ALL SELECT 'SAF','32','04' UNION ALL SELECT 'SAF','33','04' UNION ALL SELECT 'SAF','34','04' UNION ALL SELECT 'SAF','35','04' UNION ALL SELECT 'SAF','36','04' UNION ALL SELECT 'SAF','37','04' UNION ALL SELECT 'SAF','38','04' UNION ALL SELECT 'SAF','39','04' UNION ALL SELECT 'SAF','40','04' UNION ALL SELECT 'SAF','41','04' UNION ALL SELECT 'SAF','42','04' UNION ALL SELECT 'SAF','43','04' UNION ALL SELECT 'SAF','44','04' UNION ALL SELECT 'SAF','45','04' UNION ALL SELECT 'CHR','0','05' UNION ALL SELECT 'CHR','1','05' UNION ALL SELECT 'CHR','2','05' UNION ALL SELECT 'CHR','3','05' UNION ALL SELECT 'CHR','4','05' UNION ALL SELECT 'CHR','5','05' UNION ALL SELECT 'CHR','6','05' UNION ALL SELECT 'CHR','7','05' UNION ALL SELECT 'CHR','8','05' UNION ALL SELECT 'CHR','9','05' UNION ALL SELECT 'CHR','10','05' UNION ALL SELECT 'CHR','11','05' UNION ALL SELECT 'CHR','12','05' UNION ALL SELECT 'CHR','13','05' UNION ALL SELECT 'CHR','14','05' UNION ALL SELECT 'CHR','15','05' UNION ALL SELECT 'CHR','16','05' UNION ALL SELECT 'CHR','17','05' UNION ALL SELECT 'CHR','18','05' UNION ALL SELECT 'CHR','19','05' UNION ALL SELECT 'CHR','20','05' UNION ALL SELECT 'CHR','21','05' UNION ALL SELECT 'CHR','22','05' UNION ALL SELECT 'CHR','23','05' UNION ALL SELECT 'CHR','24','05' UNION ALL SELECT 'CHR','25','05' UNION ALL SELECT 'CHR','26','05' UNION ALL SELECT 'CHR','27','05' UNION ALL SELECT 'CHR','28','05' UNION ALL SELECT 'CHR','29','05' UNION ALL SELECT 'CHR','30','05' UNION ALL SELECT 'CHR','31','05' UNION ALL SELECT 'CHR','32','05' UNION ALL SELECT 'CHR','33','05' UNION ALL SELECT 'CHR','34','05' UNION ALL SELECT 'CHR','35','05' UNION ALL SELECT 'CHR','36','05' UNION ALL SELECT 'CHR','37','05' UNION ALL SELECT 'CHR','38','05' UNION ALL SELECT 'CHR','39','05' UNION ALL SELECT 'CHR','40','05' UNION ALL SELECT 'CHR','41','05' UNION ALL SELECT 'CHR','42','05' UNION ALL SELECT 'CHR','43','05' UNION ALL SELECT 'CHR','44','05' UNION ALL SELECT 'CHR','45','05' UNION ALL SELECT 'RL Retail','0','05' UNION ALL SELECT 'RL Retail','1','05' UNION ALL SELECT 'RL Retail','2','05' UNION ALL SELECT 'RL Retail','3','05' UNION ALL SELECT 'RL Retail','4','05' UNION ALL SELECT 'RL Retail','5','05' UNION ALL SELECT 'RL Retail','6','05' UNION ALL SELECT 'RL Retail','7','05' UNION ALL SELECT 'RL Retail','8','05' UNION ALL SELECT 'RL Retail','9','05' UNION ALL SELECT 'RL Retail','10','05' UNION ALL SELECT 'RL Retail','11','05' UNION ALL SELECT 'RL Retail','12','05' UNION ALL SELECT 'RL Retail','13','05' UNION ALL SELECT 'RL Retail','14','05' UNION ALL SELECT 'RL Retail','15','05' UNION ALL SELECT 'RL Retail','16','05' UNION ALL SELECT 'RL Retail','17','05' UNION ALL SELECT 'RL Retail','18','05' UNION ALL SELECT 'RL Retail','19','05' UNION ALL SELECT 'RL Retail','20','05' UNION ALL SELECT 'RL Retail','21','05' UNION ALL SELECT 'RL Retail','22','05' UNION ALL SELECT 'RL Retail','23','05' UNION ALL SELECT 'RL Retail','24','05' UNION ALL SELECT 'RL Retail','25','05' UNION ALL SELECT 'RL Retail','26','05' UNION ALL SELECT 'RL Retail','27','05' UNION ALL SELECT 'RL Retail','28','05' UNION ALL SELECT 'RL Retail','29','05' UNION ALL SELECT 'RL Retail','30','05' UNION ALL SELECT 'RL Retail','31','05' UNION ALL SELECT 'RL Retail','32','05' UNION ALL SELECT 'RL Retail','33','05' UNION ALL SELECT 'RL Retail','34','05' UNION ALL SELECT 'RL Retail','35','05' UNION ALL SELECT 'RL Retail','36','05' UNION ALL SELECT 'RL Retail','37','05' UNION ALL SELECT 'RL Retail','38','05' UNION ALL SELECT 'RL Retail','39','05' UNION ALL SELECT 'RL Retail','40','05' UNION ALL SELECT 'RL Retail','41','05' UNION ALL SELECT 'RL Retail','42','05' UNION ALL SELECT 'RL Retail','43','05' UNION ALL SELECT 'RL Retail','44','05' UNION ALL SELECT 'RL Retail','45','05' UNION ALL SELECT 'SAF','0','05' UNION ALL SELECT 'SAF','1','05' UNION ALL SELECT 'SAF','2','05' UNION ALL SELECT 'SAF','3','05' UNION ALL SELECT 'SAF','4','05' UNION ALL SELECT 'SAF','5','05' UNION ALL SELECT 'SAF','6','05' UNION ALL SELECT 'SAF','7','05' UNION ALL SELECT 'SAF','8','05' UNION ALL SELECT 'SAF','9','05' UNION ALL SELECT 'SAF','10','05' UNION ALL SELECT 'SAF','11','05' UNION ALL SELECT 'SAF','12','05' UNION ALL SELECT 'SAF','13','05' UNION ALL SELECT 'SAF','14','05' UNION ALL SELECT 'SAF','15','05' UNION ALL SELECT 'SAF','16','05' UNION ALL SELECT 'SAF','17','05' UNION ALL SELECT 'SAF','18','05' UNION ALL SELECT 'SAF','19','05' UNION ALL SELECT 'SAF','20','05' UNION ALL SELECT 'SAF','21','05' UNION ALL SELECT 'SAF','22','05' UNION ALL SELECT 'SAF','23','05' UNION ALL SELECT 'SAF','24','05' UNION ALL SELECT 'SAF','25','05' UNION ALL SELECT 'SAF','26','05' UNION ALL SELECT 'SAF','27','05' UNION ALL SELECT 'SAF','28','05' UNION ALL SELECT 'SAF','29','05' UNION ALL SELECT 'SAF','30','05' UNION ALL SELECT 'SAF','31','05' UNION ALL SELECT 'SAF','32','05' UNION ALL SELECT 'SAF','33','05' UNION ALL SELECT 'SAF','34','05' UNION ALL SELECT 'SAF','35','05' UNION ALL SELECT 'SAF','36','05' UNION ALL SELECT 'SAF','37','05' UNION ALL SELECT 'SAF','38','05' UNION ALL SELECT 'SAF','39','05' UNION ALL SELECT 'SAF','40','05' UNION ALL SELECT 'SAF','41','05' UNION ALL SELECT 'SAF','42','05' UNION ALL SELECT 'SAF','43','05' UNION ALL SELECT 'SAF','44','05' UNION ALL SELECT 'SAF','45','05' UNION ALL SELECT 'CHR','0','06' UNION ALL SELECT 'CHR','1','06' UNION ALL SELECT 'CHR','2','06' UNION ALL SELECT 'CHR','3','06' UNION ALL SELECT 'CHR','4','06' UNION ALL SELECT 'CHR','5','06' UNION ALL SELECT 'CHR','6','06' UNION ALL SELECT 'CHR','7','06' UNION ALL SELECT 'CHR','8','06' UNION ALL SELECT 'CHR','9','06' UNION ALL SELECT 'CHR','10','06' UNION ALL SELECT 'CHR','11','06' UNION ALL SELECT 'CHR','12','06' UNION ALL SELECT 'CHR','13','06' UNION ALL SELECT 'CHR','14','06' UNION ALL SELECT 'CHR','15','06' UNION ALL SELECT 'CHR','16','06' UNION ALL SELECT 'CHR','17','06' UNION ALL SELECT 'CHR','18','06' UNION ALL SELECT 'CHR','19','06' UNION ALL SELECT 'CHR','20','06' UNION ALL SELECT 'CHR','21','06' UNION ALL SELECT 'CHR','22','06' UNION ALL SELECT 'CHR','23','06' UNION ALL SELECT 'CHR','24','06' UNION ALL SELECT 'CHR','25','06' UNION ALL SELECT 'CHR','26','06' UNION ALL SELECT 'CHR','27','06' UNION ALL SELECT 'CHR','28','06' UNION ALL SELECT 'CHR','29','06' UNION ALL SELECT 'CHR','30','06' UNION ALL SELECT 'CHR','31','06' UNION ALL SELECT 'CHR','32','06' UNION ALL SELECT 'CHR','33','06' UNION ALL SELECT 'CHR','34','06' UNION ALL SELECT 'CHR','35','06' UNION ALL SELECT 'CHR','36','06' UNION ALL SELECT 'CHR','37','06' UNION ALL SELECT 'CHR','38','06' UNION ALL SELECT 'CHR','39','06' UNION ALL SELECT 'CHR','40','06' UNION ALL SELECT 'CHR','41','06' UNION ALL SELECT 'CHR','42','06' UNION ALL SELECT 'CHR','43','06' UNION ALL SELECT 'CHR','44','06' UNION ALL SELECT 'CHR','45','06' UNION ALL SELECT 'RL Retail','0','06' UNION ALL SELECT 'RL Retail','1','06' UNION ALL SELECT 'RL Retail','2','06' UNION ALL SELECT 'RL Retail','3','06' UNION ALL SELECT 'RL Retail','4','06' UNION ALL SELECT 'RL Retail','5','06' UNION ALL SELECT 'RL Retail','6','06' UNION ALL SELECT 'RL Retail','7','06' UNION ALL SELECT 'RL Retail','8','06' UNION ALL SELECT 'RL Retail','9','06' UNION ALL SELECT 'RL Retail','10','06' UNION ALL SELECT 'RL Retail','11','06' UNION ALL SELECT 'RL Retail','12','06' UNION ALL SELECT 'RL Retail','13','06' UNION ALL SELECT 'RL Retail','14','06' UNION ALL SELECT 'RL Retail','15','06' UNION ALL SELECT 'RL Retail','16','06' UNION ALL SELECT 'RL Retail','17','06' UNION ALL SELECT 'RL Retail','18','06' UNION ALL SELECT 'RL Retail','19','06' UNION ALL SELECT 'RL Retail','20','06' UNION ALL SELECT 'RL Retail','21','06' UNION ALL SELECT 'RL Retail','22','06' UNION ALL SELECT 'RL Retail','23','06' UNION ALL SELECT 'RL Retail','24','06' UNION ALL SELECT 'RL Retail','25','06' UNION ALL SELECT 'RL Retail','26','06' UNION ALL SELECT 'RL Retail','27','06' UNION ALL SELECT 'RL Retail','28','06' UNION ALL SELECT 'RL Retail','29','06' UNION ALL SELECT 'RL Retail','30','06' UNION ALL SELECT 'RL Retail','31','06' UNION ALL SELECT 'RL Retail','32','06' UNION ALL SELECT 'RL Retail','33','06' UNION ALL SELECT 'RL Retail','34','06' UNION ALL SELECT 'RL Retail','35','06' UNION ALL SELECT 'RL Retail','36','06' UNION ALL SELECT 'RL Retail','37','06' UNION ALL SELECT 'RL Retail','38','06' UNION ALL SELECT 'RL Retail','39','06' UNION ALL SELECT 'RL Retail','40','06' UNION ALL SELECT 'RL Retail','41','06' UNION ALL SELECT 'RL Retail','42','06' UNION ALL SELECT 'RL Retail','43','06' UNION ALL SELECT 'RL Retail','44','06' UNION ALL SELECT 'RL Retail','45','06' UNION ALL SELECT 'SAF','0','06' UNION ALL SELECT 'SAF','1','06' UNION ALL SELECT 'SAF','2','06' UNION ALL SELECT 'SAF','3','06' UNION ALL SELECT 'SAF','4','06' UNION ALL SELECT 'SAF','5','06' UNION ALL SELECT 'SAF','6','06' UNION ALL SELECT 'SAF','7','06' UNION ALL SELECT 'SAF','8','06' UNION ALL SELECT 'SAF','9','06' UNION ALL SELECT 'SAF','10','06' UNION ALL SELECT 'SAF','11','06' UNION ALL SELECT 'SAF','12','06' UNION ALL SELECT 'SAF','13','06' UNION ALL SELECT 'SAF','14','06' UNION ALL SELECT 'SAF','15','06' UNION ALL SELECT 'SAF','16','06' UNION ALL SELECT 'SAF','17','06' UNION ALL SELECT 'SAF','18','06' UNION ALL SELECT 'SAF','19','06' UNION ALL SELECT 'SAF','20','06' UNION ALL SELECT 'SAF','21','06' UNION ALL SELECT 'SAF','22','06' UNION ALL SELECT 'SAF','23','06' UNION ALL SELECT 'SAF','24','06' UNION ALL SELECT 'SAF','25','06' UNION ALL SELECT 'SAF','26','06' UNION ALL SELECT 'SAF','27','06' UNION ALL SELECT 'SAF','28','06' UNION ALL SELECT 'SAF','29','06' UNION ALL SELECT 'SAF','30','06' UNION ALL SELECT 'SAF','31','06' UNION ALL SELECT 'SAF','32','06' UNION ALL SELECT 'SAF','33','06' UNION ALL SELECT 'SAF','34','06' UNION ALL SELECT 'SAF','35','06' UNION ALL SELECT 'SAF','36','06' UNION ALL SELECT 'SAF','37','06' UNION ALL SELECT 'SAF','38','06' UNION ALL SELECT 'SAF','39','06' UNION ALL SELECT 'SAF','40','06' UNION ALL SELECT 'SAF','41','06' UNION ALL SELECT 'SAF','42','06' UNION ALL SELECT 'SAF','43','06' UNION ALL SELECT 'SAF','44','06' UNION ALL SELECT 'SAF','45','06' UNION ALL SELECT 'CHR','0','07' UNION ALL SELECT 'CHR','1','07' UNION ALL SELECT 'CHR','2','07' UNION ALL SELECT 'CHR','3','07' UNION ALL SELECT 'CHR','4','07' UNION ALL SELECT 'CHR','5','07' UNION ALL SELECT 'CHR','6','07' UNION ALL SELECT 'CHR','7','07' UNION ALL SELECT 'CHR','8','07' UNION ALL SELECT 'CHR','9','07' UNION ALL SELECT 'CHR','10','07' UNION ALL SELECT 'CHR','11','07' UNION ALL SELECT 'CHR','12','07' UNION ALL SELECT 'CHR','13','07' UNION ALL SELECT 'CHR','14','07' UNION ALL SELECT 'CHR','15','07' UNION ALL SELECT 'CHR','16','07' UNION ALL SELECT 'CHR','17','07' UNION ALL SELECT 'CHR','18','07' UNION ALL SELECT 'CHR','19','07' UNION ALL SELECT 'CHR','20','07' UNION ALL SELECT 'CHR','21','07' UNION ALL SELECT 'CHR','22','07' UNION ALL SELECT 'CHR','23','07' UNION ALL SELECT 'CHR','24','07' UNION ALL SELECT 'CHR','25','07' UNION ALL SELECT 'CHR','26','07' UNION ALL SELECT 'CHR','27','07' UNION ALL SELECT 'CHR','28','07' UNION ALL SELECT 'CHR','29','07' UNION ALL SELECT 'CHR','30','07' UNION ALL SELECT 'CHR','31','07' UNION ALL SELECT 'CHR','32','07' UNION ALL SELECT 'CHR','33','07' UNION ALL SELECT 'CHR','34','07' UNION ALL SELECT 'CHR','35','07' UNION ALL SELECT 'CHR','36','07' UNION ALL SELECT 'CHR','37','07' UNION ALL SELECT 'CHR','38','07' UNION ALL SELECT 'CHR','39','07' UNION ALL SELECT 'CHR','40','07' UNION ALL SELECT 'CHR','41','07' UNION ALL SELECT 'CHR','42','07' UNION ALL SELECT 'CHR','43','07' UNION ALL SELECT 'CHR','44','07' UNION ALL SELECT 'CHR','45','07' UNION ALL SELECT 'RL Retail','0','07' UNION ALL SELECT 'RL Retail','1','07' UNION ALL SELECT 'RL Retail','2','07' UNION ALL SELECT 'RL Retail','3','07' UNION ALL SELECT 'RL Retail','4','07' UNION ALL SELECT 'RL Retail','5','07' UNION ALL SELECT 'RL Retail','6','07' UNION ALL SELECT 'RL Retail','7','07' UNION ALL SELECT 'RL Retail','8','07' UNION ALL SELECT 'RL Retail','9','07' UNION ALL SELECT 'RL Retail','10','07' UNION ALL SELECT 'RL Retail','11','07' UNION ALL SELECT 'RL Retail','12','07' UNION ALL SELECT 'RL Retail','13','07' UNION ALL SELECT 'RL Retail','14','07' UNION ALL SELECT 'RL Retail','15','07' UNION ALL SELECT 'RL Retail','16','07' UNION ALL SELECT 'RL Retail','17','07' UNION ALL SELECT 'RL Retail','18','07' UNION ALL SELECT 'RL Retail','19','07' UNION ALL SELECT 'RL Retail','20','07' UNION ALL SELECT 'RL Retail','21','07' UNION ALL SELECT 'RL Retail','22','07' UNION ALL SELECT 'RL Retail','23','07' UNION ALL SELECT 'RL Retail','24','07' UNION ALL SELECT 'RL Retail','25','07' UNION ALL SELECT 'RL Retail','26','07' UNION ALL SELECT 'RL Retail','27','07' UNION ALL SELECT 'RL Retail','28','07' UNION ALL SELECT 'RL Retail','29','07' UNION ALL SELECT 'RL Retail','30','07' UNION ALL SELECT 'RL Retail','31','07' UNION ALL SELECT 'RL Retail','32','07' UNION ALL SELECT 'RL Retail','33','07' UNION ALL SELECT 'RL Retail','34','07' UNION ALL SELECT 'RL Retail','35','07' UNION ALL SELECT 'RL Retail','36','07' UNION ALL SELECT 'RL Retail','37','07' UNION ALL SELECT 'RL Retail','38','07' UNION ALL SELECT 'RL Retail','39','07' UNION ALL SELECT 'RL Retail','40','07' UNION ALL SELECT 'RL Retail','41','07' UNION ALL SELECT 'RL Retail','42','07' UNION ALL SELECT 'RL Retail','43','07' UNION ALL SELECT 'RL Retail','44','07' UNION ALL SELECT 'RL Retail','45','07' UNION ALL SELECT 'SAF','0','07' UNION ALL SELECT 'SAF','1','07' UNION ALL SELECT 'SAF','2','07' UNION ALL SELECT 'SAF','3','07' UNION ALL SELECT 'SAF','4','07' UNION ALL SELECT 'SAF','5','07' UNION ALL SELECT 'SAF','6','07' UNION ALL SELECT 'SAF','7','07' UNION ALL SELECT 'SAF','8','07' UNION ALL SELECT 'SAF','9','07' UNION ALL SELECT 'SAF','10','07' UNION ALL SELECT 'SAF','11','07' UNION ALL SELECT 'SAF','12','07' UNION ALL SELECT 'SAF','13','07' UNION ALL SELECT 'SAF','14','07' UNION ALL SELECT 'SAF','15','07' UNION ALL SELECT 'SAF','16','07' UNION ALL SELECT 'SAF','17','07' UNION ALL SELECT 'SAF','18','07' UNION ALL SELECT 'SAF','19','07' UNION ALL SELECT 'SAF','20','07' UNION ALL SELECT 'SAF','21','07' UNION ALL SELECT 'SAF','22','07' UNION ALL SELECT 'SAF','23','07' UNION ALL SELECT 'SAF','24','07' UNION ALL SELECT 'SAF','25','07' UNION ALL SELECT 'SAF','26','07' UNION ALL SELECT 'SAF','27','07' UNION ALL SELECT 'SAF','28','07' UNION ALL SELECT 'SAF','29','07' UNION ALL SELECT 'SAF','30','07' UNION ALL SELECT 'SAF','31','07' UNION ALL SELECT 'SAF','32','07' UNION ALL SELECT 'SAF','33','07' UNION ALL SELECT 'SAF','34','07' UNION ALL SELECT 'SAF','35','07' UNION ALL SELECT 'SAF','36','07' UNION ALL SELECT 'SAF','37','07' UNION ALL SELECT 'SAF','38','07' UNION ALL SELECT 'SAF','39','07' UNION ALL SELECT 'SAF','40','07' UNION ALL SELECT 'SAF','41','07' UNION ALL SELECT 'SAF','42','07' UNION ALL SELECT 'SAF','43','07' UNION ALL SELECT 'SAF','44','07' UNION ALL SELECT 'SAF','45','07' UNION ALL SELECT 'CHR','0','08' UNION ALL SELECT 'CHR','1','08' UNION ALL SELECT 'CHR','2','08' UNION ALL SELECT 'CHR','3','08' UNION ALL SELECT 'CHR','4','08' UNION ALL SELECT 'CHR','5','08' UNION ALL SELECT 'CHR','6','08' UNION ALL SELECT 'CHR','7','08' UNION ALL SELECT 'CHR','8','08' UNION ALL SELECT 'CHR','9','08' UNION ALL SELECT 'CHR','10','08' UNION ALL SELECT 'CHR','11','08' UNION ALL SELECT 'CHR','12','08' UNION ALL SELECT 'CHR','13','08' UNION ALL SELECT 'CHR','14','08' UNION ALL SELECT 'CHR','15','08' UNION ALL SELECT 'CHR','16','08' UNION ALL SELECT 'CHR','17','08' UNION ALL SELECT 'CHR','18','08' UNION ALL SELECT 'CHR','19','08' UNION ALL SELECT 'CHR','20','08' UNION ALL SELECT 'CHR','21','08' UNION ALL SELECT 'CHR','22','08' UNION ALL SELECT 'CHR','23','08' UNION ALL SELECT 'CHR','24','08' UNION ALL SELECT 'CHR','25','08' UNION ALL SELECT 'CHR','26','08' UNION ALL SELECT 'CHR','27','08' UNION ALL SELECT 'CHR','28','08' UNION ALL SELECT 'CHR','29','08' UNION ALL SELECT 'CHR','30','08' UNION ALL SELECT 'CHR','31','08' UNION ALL SELECT 'CHR','32','08' UNION ALL SELECT 'CHR','33','08' UNION ALL SELECT 'CHR','34','08' UNION ALL SELECT 'CHR','35','08' UNION ALL SELECT 'CHR','36','08' UNION ALL SELECT 'CHR','37','08' UNION ALL SELECT 'CHR','38','08' UNION ALL SELECT 'CHR','39','08' UNION ALL SELECT 'CHR','40','08' UNION ALL SELECT 'CHR','41','08' UNION ALL SELECT 'CHR','42','08' UNION ALL SELECT 'CHR','43','08' UNION ALL SELECT 'CHR','44','08' UNION ALL SELECT 'CHR','45','08' UNION ALL SELECT 'RL Retail','0','08' UNION ALL SELECT 'RL Retail','1','08' UNION ALL SELECT 'RL Retail','2','08' UNION ALL SELECT 'RL Retail','3','08' UNION ALL SELECT 'RL Retail','4','08' UNION ALL SELECT 'RL Retail','5','08' UNION ALL SELECT 'RL Retail','6','08' UNION ALL SELECT 'RL Retail','7','08' UNION ALL SELECT 'RL Retail','8','08' UNION ALL SELECT 'RL Retail','9','08' UNION ALL SELECT 'RL Retail','10','08' UNION ALL SELECT 'RL Retail','11','08' UNION ALL SELECT 'RL Retail','12','08' UNION ALL SELECT 'RL Retail','13','08' UNION ALL SELECT 'RL Retail','14','08' UNION ALL SELECT 'RL Retail','15','08' UNION ALL SELECT 'RL Retail','16','08' UNION ALL SELECT 'RL Retail','17','08' UNION ALL SELECT 'RL Retail','18','08' UNION ALL SELECT 'RL Retail','19','08' UNION ALL SELECT 'RL Retail','20','08' UNION ALL SELECT 'RL Retail','21','08' UNION ALL SELECT 'RL Retail','22','08' UNION ALL SELECT 'RL Retail','23','08' UNION ALL SELECT 'RL Retail','24','08' UNION ALL SELECT 'RL Retail','25','08' UNION ALL SELECT 'RL Retail','26','08' UNION ALL SELECT 'RL Retail','27','08' UNION ALL SELECT 'RL Retail','28','08' UNION ALL SELECT 'RL Retail','29','08' UNION ALL SELECT 'RL Retail','30','08' UNION ALL SELECT 'RL Retail','31','08' UNION ALL SELECT 'RL Retail','32','08' UNION ALL SELECT 'RL Retail','33','08' UNION ALL SELECT 'RL Retail','34','08' UNION ALL SELECT 'RL Retail','35','08' UNION ALL SELECT 'RL Retail','36','08' UNION ALL SELECT 'RL Retail','37','08' UNION ALL SELECT 'RL Retail','38','08' UNION ALL SELECT 'RL Retail','39','08' UNION ALL SELECT 'RL Retail','40','08' UNION ALL SELECT 'RL Retail','41','08' UNION ALL SELECT 'RL Retail','42','08' UNION ALL SELECT 'RL Retail','43','08' UNION ALL SELECT 'RL Retail','44','08' UNION ALL SELECT 'RL Retail','45','08' UNION ALL SELECT 'SAF','0','08' UNION ALL SELECT 'SAF','1','08' UNION ALL SELECT 'SAF','2','08' UNION ALL SELECT 'SAF','3','08' UNION ALL SELECT 'SAF','4','08' UNION ALL SELECT 'SAF','5','08' UNION ALL SELECT 'SAF','6','08' UNION ALL SELECT 'SAF','7','08' UNION ALL SELECT 'SAF','8','08' UNION ALL SELECT 'SAF','9','08' UNION ALL SELECT 'SAF','10','08' UNION ALL SELECT 'SAF','11','08' UNION ALL SELECT 'SAF','12','08' UNION ALL SELECT 'SAF','13','08' UNION ALL SELECT 'SAF','14','08' UNION ALL SELECT 'SAF','15','08' UNION ALL SELECT 'SAF','16','08' UNION ALL SELECT 'SAF','17','08' UNION ALL SELECT 'SAF','18','08' UNION ALL SELECT 'SAF','19','08' UNION ALL SELECT 'SAF','20','08' UNION ALL SELECT 'SAF','21','08' UNION ALL SELECT 'SAF','22','08' UNION ALL SELECT 'SAF','23','08' UNION ALL SELECT 'SAF','24','08' UNION ALL SELECT 'SAF','25','08' UNION ALL SELECT 'SAF','26','08' UNION ALL SELECT 'SAF','27','08' UNION ALL SELECT 'SAF','28','08' UNION ALL SELECT 'SAF','29','08' UNION ALL SELECT 'SAF','30','08' UNION ALL SELECT 'SAF','31','08' UNION ALL SELECT 'SAF','32','08' UNION ALL SELECT 'SAF','33','08' UNION ALL SELECT 'SAF','34','08' UNION ALL SELECT 'SAF','35','08' UNION ALL SELECT 'SAF','36','08' UNION ALL SELECT 'SAF','37','08' UNION ALL SELECT 'SAF','38','08' UNION ALL SELECT 'SAF','39','08' UNION ALL SELECT 'SAF','40','08' UNION ALL SELECT 'SAF','41','08' UNION ALL SELECT 'SAF','42','08' UNION ALL SELECT 'SAF','43','08' UNION ALL SELECT 'SAF','44','08' UNION ALL SELECT 'SAF','45','08' UNION ALL SELECT 'CHR','0','09' UNION ALL SELECT 'CHR','1','09' UNION ALL SELECT 'CHR','2','09' UNION ALL SELECT 'CHR','3','09' UNION ALL SELECT 'CHR','4','09' UNION ALL SELECT 'CHR','5','09' UNION ALL SELECT 'CHR','6','09' UNION ALL SELECT 'CHR','7','09' UNION ALL SELECT 'CHR','8','09' UNION ALL SELECT 'CHR','9','09' UNION ALL SELECT 'CHR','10','09' UNION ALL SELECT 'CHR','11','09' UNION ALL SELECT 'CHR','12','09' UNION ALL SELECT 'CHR','13','09' UNION ALL SELECT 'CHR','14','09' UNION ALL SELECT 'CHR','15','09' UNION ALL SELECT 'CHR','16','09' UNION ALL SELECT 'CHR','17','09' UNION ALL SELECT 'CHR','18','09' UNION ALL SELECT 'CHR','19','09' UNION ALL SELECT 'CHR','20','09' UNION ALL SELECT 'CHR','21','09' UNION ALL SELECT 'CHR','22','09' UNION ALL SELECT 'CHR','23','09' UNION ALL SELECT 'CHR','24','09' UNION ALL SELECT 'CHR','25','09' UNION ALL SELECT 'CHR','26','09' UNION ALL SELECT 'CHR','27','09' UNION ALL SELECT 'CHR','28','09' UNION ALL SELECT 'CHR','29','09' UNION ALL SELECT 'CHR','30','09' UNION ALL SELECT 'CHR','31','09' UNION ALL SELECT 'CHR','32','09' UNION ALL SELECT 'CHR','33','09' UNION ALL SELECT 'CHR','34','09' UNION ALL SELECT 'CHR','35','09' UNION ALL SELECT 'CHR','36','09' UNION ALL SELECT 'CHR','37','09' UNION ALL SELECT 'CHR','38','09' UNION ALL SELECT 'CHR','39','09' UNION ALL SELECT 'CHR','40','09' UNION ALL SELECT 'CHR','41','09' UNION ALL SELECT 'CHR','42','09' UNION ALL SELECT 'CHR','43','09' UNION ALL SELECT 'CHR','44','09' UNION ALL SELECT 'CHR','45','09' UNION ALL SELECT 'RL Retail','0','09' UNION ALL SELECT 'RL Retail','1','09' UNION ALL SELECT 'RL Retail','2','09' UNION ALL SELECT 'RL Retail','3','09' UNION ALL SELECT 'RL Retail','4','09' UNION ALL SELECT 'RL Retail','5','09' UNION ALL SELECT 'RL Retail','6','09' UNION ALL SELECT 'RL Retail','7','09' UNION ALL SELECT 'RL Retail','8','09' UNION ALL SELECT 'RL Retail','9','09' UNION ALL SELECT 'RL Retail','09','09' UNION ALL SELECT 'RL Retail','11','09' UNION ALL SELECT 'RL Retail','12','09' UNION ALL SELECT 'RL Retail','13','09' UNION ALL SELECT 'RL Retail','14','09' UNION ALL SELECT 'RL Retail','15','09' UNION ALL SELECT 'RL Retail','16','09' UNION ALL SELECT 'RL Retail','17','09' UNION ALL SELECT 'RL Retail','18','09' UNION ALL SELECT 'RL Retail','19','09' UNION ALL SELECT 'RL Retail','20','09' UNION ALL SELECT 'RL Retail','21','09' UNION ALL SELECT 'RL Retail','22','09' UNION ALL SELECT 'RL Retail','23','09' UNION ALL SELECT 'RL Retail','24','09' UNION ALL SELECT 'RL Retail','25','09' UNION ALL SELECT 'RL Retail','26','09' UNION ALL SELECT 'RL Retail','27','09' UNION ALL SELECT 'RL Retail','28','09' UNION ALL SELECT 'RL Retail','29','09' UNION ALL SELECT 'RL Retail','30','09' UNION ALL SELECT 'RL Retail','31','09' UNION ALL SELECT 'RL Retail','32','09' UNION ALL SELECT 'RL Retail','33','09' UNION ALL SELECT 'RL Retail','34','09' UNION ALL SELECT 'RL Retail','35','09' UNION ALL SELECT 'RL Retail','36','09' UNION ALL SELECT 'RL Retail','37','09' UNION ALL SELECT 'RL Retail','38','09' UNION ALL SELECT 'RL Retail','39','09' UNION ALL SELECT 'RL Retail','40','09' UNION ALL SELECT 'RL Retail','41','09' UNION ALL SELECT 'RL Retail','42','09' UNION ALL SELECT 'RL Retail','43','09' UNION ALL SELECT 'RL Retail','44','09' UNION ALL SELECT 'RL Retail','45','09' UNION ALL SELECT 'SAF','0','09' UNION ALL SELECT 'SAF','1','09' UNION ALL SELECT 'SAF','2','09' UNION ALL SELECT 'SAF','3','09' UNION ALL SELECT 'SAF','4','09' UNION ALL SELECT 'SAF','5','09' UNION ALL SELECT 'SAF','6','09' UNION ALL SELECT 'SAF','7','09' UNION ALL SELECT 'SAF','8','09' UNION ALL SELECT 'SAF','9','09' UNION ALL SELECT 'SAF','10','09' UNION ALL SELECT 'SAF','11','09' UNION ALL SELECT 'SAF','12','09' UNION ALL SELECT 'SAF','13','09' UNION ALL SELECT 'SAF','14','09' UNION ALL SELECT 'SAF','15','09' UNION ALL SELECT 'SAF','16','09' UNION ALL SELECT 'SAF','17','09' UNION ALL SELECT 'SAF','18','09' UNION ALL SELECT 'SAF','19','09' UNION ALL SELECT 'SAF','20','09' UNION ALL SELECT 'SAF','21','09' UNION ALL SELECT 'SAF','22','09' UNION ALL SELECT 'SAF','23','09' UNION ALL SELECT 'SAF','24','09' UNION ALL SELECT 'SAF','25','09' UNION ALL SELECT 'SAF','26','09' UNION ALL SELECT 'SAF','27','09' UNION ALL SELECT 'SAF','28','09' UNION ALL SELECT 'SAF','29','09' UNION ALL SELECT 'SAF','30','09' UNION ALL SELECT 'SAF','31','09' UNION ALL SELECT 'SAF','32','09' UNION ALL SELECT 'SAF','33','09' UNION ALL SELECT 'SAF','34','09' UNION ALL SELECT 'SAF','35','09' UNION ALL SELECT 'SAF','36','09' UNION ALL SELECT 'SAF','37','09' UNION ALL SELECT 'SAF','38','09' UNION ALL SELECT 'SAF','39','09' UNION ALL SELECT 'SAF','40','09' UNION ALL SELECT 'SAF','41','09' UNION ALL SELECT 'SAF','42','09' UNION ALL SELECT 'SAF','43','09' UNION ALL SELECT 'SAF','44','09' UNION ALL SELECT 'SAF','45','09' UNION ALL SELECT 'CHR','0','10' UNION ALL SELECT 'CHR','1','10' UNION ALL SELECT 'CHR','2','10' UNION ALL SELECT 'CHR','3','10' UNION ALL SELECT 'CHR','4','10' UNION ALL SELECT 'CHR','5','10' UNION ALL SELECT 'CHR','6','10' UNION ALL SELECT 'CHR','7','10' UNION ALL SELECT 'CHR','8','10' UNION ALL SELECT 'CHR','9','10' UNION ALL SELECT 'CHR','10','10' UNION ALL SELECT 'CHR','11','10' UNION ALL SELECT 'CHR','12','10' UNION ALL SELECT 'CHR','13','10' UNION ALL SELECT 'CHR','14','10' UNION ALL SELECT 'CHR','15','10' UNION ALL SELECT 'CHR','16','10' UNION ALL SELECT 'CHR','17','10' UNION ALL SELECT 'CHR','18','10' UNION ALL SELECT 'CHR','19','10' UNION ALL SELECT 'CHR','20','10' UNION ALL SELECT 'CHR','21','10' UNION ALL SELECT 'CHR','22','10' UNION ALL SELECT 'CHR','23','10' UNION ALL SELECT 'CHR','24','10' UNION ALL SELECT 'CHR','25','10' UNION ALL SELECT 'CHR','26','10' UNION ALL SELECT 'CHR','27','10' UNION ALL SELECT 'CHR','28','10' UNION ALL SELECT 'CHR','29','10' UNION ALL SELECT 'CHR','30','10' UNION ALL SELECT 'CHR','31','10' UNION ALL SELECT 'CHR','32','10' UNION ALL SELECT 'CHR','33','10' UNION ALL SELECT 'CHR','34','10' UNION ALL SELECT 'CHR','35','10' UNION ALL SELECT 'CHR','36','10' UNION ALL SELECT 'CHR','37','10' UNION ALL SELECT 'CHR','38','10' UNION ALL SELECT 'CHR','39','10' UNION ALL SELECT 'CHR','40','10' UNION ALL SELECT 'CHR','41','10' UNION ALL SELECT 'CHR','42','10' UNION ALL SELECT 'CHR','43','10' UNION ALL SELECT 'CHR','44','10' UNION ALL SELECT 'CHR','45','10' UNION ALL SELECT 'RL Retail','0','10' UNION ALL SELECT 'RL Retail','1','10' UNION ALL SELECT 'RL Retail','2','10' UNION ALL SELECT 'RL Retail','3','10' UNION ALL SELECT 'RL Retail','4','10' UNION ALL SELECT 'RL Retail','5','10' UNION ALL SELECT 'RL Retail','6','10' UNION ALL SELECT 'RL Retail','7','10' UNION ALL SELECT 'RL Retail','8','10' UNION ALL SELECT 'RL Retail','9','10' UNION ALL SELECT 'RL Retail','10','10' UNION ALL SELECT 'RL Retail','11','10' UNION ALL SELECT 'RL Retail','12','10' UNION ALL SELECT 'RL Retail','13','10' UNION ALL SELECT 'RL Retail','14','10' UNION ALL SELECT 'RL Retail','15','10' UNION ALL SELECT 'RL Retail','16','10' UNION ALL SELECT 'RL Retail','17','10' UNION ALL SELECT 'RL Retail','18','10' UNION ALL SELECT 'RL Retail','19','10' UNION ALL SELECT 'RL Retail','20','10' UNION ALL SELECT 'RL Retail','21','10' UNION ALL SELECT 'RL Retail','22','10' UNION ALL SELECT 'RL Retail','23','10' UNION ALL SELECT 'RL Retail','24','10' UNION ALL SELECT 'RL Retail','25','10' UNION ALL SELECT 'RL Retail','26','10' UNION ALL SELECT 'RL Retail','27','10' UNION ALL SELECT 'RL Retail','28','10' UNION ALL SELECT 'RL Retail','29','10' UNION ALL SELECT 'RL Retail','30','10' UNION ALL SELECT 'RL Retail','31','10' UNION ALL SELECT 'RL Retail','32','10' UNION ALL SELECT 'RL Retail','33','10' UNION ALL SELECT 'RL Retail','34','10' UNION ALL SELECT 'RL Retail','35','10' UNION ALL SELECT 'RL Retail','36','10' UNION ALL SELECT 'RL Retail','37','10' UNION ALL SELECT 'RL Retail','38','10' UNION ALL SELECT 'RL Retail','39','10' UNION ALL SELECT 'RL Retail','40','10' UNION ALL SELECT 'RL Retail','41','10' UNION ALL SELECT 'RL Retail','42','10' UNION ALL SELECT 'RL Retail','43','10' UNION ALL SELECT 'RL Retail','44','10' UNION ALL SELECT 'RL Retail','45','10' UNION ALL SELECT 'SAF','0','10' UNION ALL SELECT 'SAF','1','10' UNION ALL SELECT 'SAF','2','10' UNION ALL SELECT 'SAF','3','10' UNION ALL SELECT 'SAF','4','10' UNION ALL SELECT 'SAF','5','10' UNION ALL SELECT 'SAF','6','10' UNION ALL SELECT 'SAF','7','10' UNION ALL SELECT 'SAF','8','10' UNION ALL SELECT 'SAF','9','10' UNION ALL SELECT 'SAF','10','10' UNION ALL SELECT 'SAF','11','10' UNION ALL SELECT 'SAF','12','10' UNION ALL SELECT 'SAF','13','10' UNION ALL SELECT 'SAF','14','10' UNION ALL SELECT 'SAF','15','10' UNION ALL SELECT 'SAF','16','10' UNION ALL SELECT 'SAF','17','10' UNION ALL SELECT 'SAF','18','10' UNION ALL SELECT 'SAF','19','10' UNION ALL SELECT 'SAF','20','10' UNION ALL SELECT 'SAF','21','10' UNION ALL SELECT 'SAF','22','10' UNION ALL SELECT 'SAF','23','10' UNION ALL SELECT 'SAF','24','10' UNION ALL SELECT 'SAF','25','10' UNION ALL SELECT 'SAF','26','10' UNION ALL SELECT 'SAF','27','10' UNION ALL SELECT 'SAF','28','10' UNION ALL SELECT 'SAF','29','10' UNION ALL SELECT 'SAF','30','10' UNION ALL SELECT 'SAF','31','10' UNION ALL SELECT 'SAF','32','10' UNION ALL SELECT 'SAF','33','10' UNION ALL SELECT 'SAF','34','10' UNION ALL SELECT 'SAF','35','10' UNION ALL SELECT 'SAF','36','10' UNION ALL SELECT 'SAF','37','10' UNION ALL SELECT 'SAF','38','10' UNION ALL SELECT 'SAF','39','10' UNION ALL SELECT 'SAF','40','10' UNION ALL SELECT 'SAF','41','10' UNION ALL SELECT 'SAF','42','10' UNION ALL SELECT 'SAF','43','10' UNION ALL SELECT 'SAF','44','10' UNION ALL SELECT 'SAF','45','10' UNION ALL SELECT 'CHR','0','Total' UNION ALL SELECT 'CHR','1','Total' UNION ALL SELECT 'CHR','2','Total' UNION ALL SELECT 'CHR','3','Total' UNION ALL SELECT 'CHR','4','Total' UNION ALL SELECT 'CHR','5','Total' UNION ALL SELECT 'CHR','6','Total' UNION ALL SELECT 'CHR','7','Total' UNION ALL SELECT 'CHR','8','Total' UNION ALL SELECT 'CHR','9','Total' UNION ALL SELECT 'CHR','10','Total' UNION ALL SELECT 'CHR','11','Total' UNION ALL SELECT 'CHR','12','Total' UNION ALL SELECT 'CHR','13','Total' UNION ALL SELECT 'CHR','14','Total' UNION ALL SELECT 'CHR','15','Total' UNION ALL SELECT 'CHR','16','Total' UNION ALL SELECT 'CHR','17','Total' UNION ALL SELECT 'CHR','18','Total' UNION ALL SELECT 'CHR','19','Total' UNION ALL SELECT 'CHR','20','Total' UNION ALL SELECT 'CHR','21','Total' UNION ALL SELECT 'CHR','22','Total' UNION ALL SELECT 'CHR','23','Total' UNION ALL SELECT 'CHR','24','Total' UNION ALL SELECT 'CHR','25','Total' UNION ALL SELECT 'CHR','26','Total' UNION ALL SELECT 'CHR','27','Total' UNION ALL SELECT 'CHR','28','Total' UNION ALL SELECT 'CHR','29','Total' UNION ALL SELECT 'CHR','30','Total' UNION ALL SELECT 'CHR','31','Total' UNION ALL SELECT 'CHR','32','Total' UNION ALL SELECT 'CHR','33','Total' UNION ALL SELECT 'CHR','34','Total' UNION ALL SELECT 'CHR','35','Total' UNION ALL SELECT 'CHR','36','Total' UNION ALL SELECT 'CHR','37','Total' UNION ALL SELECT 'CHR','38','Total' UNION ALL SELECT 'CHR','39','Total' UNION ALL SELECT 'CHR','40','Total' UNION ALL SELECT 'CHR','41','Total' UNION ALL SELECT 'CHR','42','Total' UNION ALL SELECT 'CHR','43','Total' UNION ALL SELECT 'CHR','44','Total' UNION ALL SELECT 'CHR','45','Total' UNION ALL SELECT 'RL Retail','0','Total' UNION ALL SELECT 'RL Retail','1','Total' UNION ALL SELECT 'RL Retail','2','Total' UNION ALL SELECT 'RL Retail','3','Total' UNION ALL SELECT 'RL Retail','4','Total' UNION ALL SELECT 'RL Retail','5','Total' UNION ALL SELECT 'RL Retail','6','Total' UNION ALL SELECT 'RL Retail','7','Total' UNION ALL SELECT 'RL Retail','8','Total' UNION ALL SELECT 'RL Retail','9','Total' UNION ALL SELECT 'RL Retail','10','Total' UNION ALL SELECT 'RL Retail','11','Total' UNION ALL SELECT 'RL Retail','12','Total' UNION ALL SELECT 'RL Retail','13','Total' UNION ALL SELECT 'RL Retail','14','Total' UNION ALL SELECT 'RL Retail','15','Total' UNION ALL SELECT 'RL Retail','16','Total' UNION ALL SELECT 'RL Retail','17','Total' UNION ALL SELECT 'RL Retail','18','Total' UNION ALL SELECT 'RL Retail','19','Total' UNION ALL SELECT 'RL Retail','20','Total' UNION ALL SELECT 'RL Retail','21','Total' UNION ALL SELECT 'RL Retail','22','Total' UNION ALL SELECT 'RL Retail','23','Total' UNION ALL SELECT 'RL Retail','24','Total' UNION ALL SELECT 'RL Retail','25','Total' UNION ALL SELECT 'RL Retail','26','Total' UNION ALL SELECT 'RL Retail','27','Total' UNION ALL SELECT 'RL Retail','28','Total' UNION ALL SELECT 'RL Retail','29','Total' UNION ALL SELECT 'RL Retail','30','Total' UNION ALL SELECT 'RL Retail','31','Total' UNION ALL SELECT 'RL Retail','32','Total' UNION ALL SELECT 'RL Retail','33','Total' UNION ALL SELECT 'RL Retail','34','Total' UNION ALL SELECT 'RL Retail','35','Total' UNION ALL SELECT 'RL Retail','36','Total' UNION ALL SELECT 'RL Retail','37','Total' UNION ALL SELECT 'RL Retail','38','Total' UNION ALL SELECT 'RL Retail','39','Total' UNION ALL SELECT 'RL Retail','40','Total' UNION ALL SELECT 'RL Retail','41','Total' UNION ALL SELECT 'RL Retail','42','Total' UNION ALL SELECT 'RL Retail','43','Total' UNION ALL SELECT 'RL Retail','44','Total' UNION ALL SELECT 'RL Retail','45','Total' UNION ALL SELECT 'SAF','0','Total' UNION ALL SELECT 'SAF','1','Total' UNION ALL SELECT 'SAF','2','Total' UNION ALL SELECT 'SAF','3','Total' UNION ALL SELECT 'SAF','4','Total' UNION ALL SELECT 'SAF','5','Total' UNION ALL SELECT 'SAF','6','Total' UNION ALL SELECT 'SAF','7','Total' UNION ALL SELECT 'SAF','8','Total' UNION ALL SELECT 'SAF','9','Total' UNION ALL SELECT 'SAF','10','Total' UNION ALL SELECT 'SAF','11','Total' UNION ALL SELECT 'SAF','12','Total' UNION ALL SELECT 'SAF','13','Total' UNION ALL SELECT 'SAF','14','Total' UNION ALL SELECT 'SAF','15','Total' UNION ALL SELECT 'SAF','16','Total' UNION ALL SELECT 'SAF','17','Total' UNION ALL SELECT 'SAF','18','Total' UNION ALL SELECT 'SAF','19','Total' UNION ALL SELECT 'SAF','20','Total' UNION ALL SELECT 'SAF','21','Total' UNION ALL SELECT 'SAF','22','Total' UNION ALL SELECT 'SAF','23','Total' UNION ALL SELECT 'SAF','24','Total' UNION ALL SELECT 'SAF','25','Total' UNION ALL SELECT 'SAF','26','Total' UNION ALL SELECT 'SAF','27','Total' UNION ALL SELECT 'SAF','28','Total' UNION ALL SELECT 'SAF','29','Total' UNION ALL SELECT 'SAF','30','Total' UNION ALL SELECT 'SAF','31','Total' UNION ALL SELECT 'SAF','32','Total' UNION ALL SELECT 'SAF','33','Total' UNION ALL SELECT 'SAF','34','Total' UNION ALL SELECT 'SAF','35','Total' UNION ALL SELECT 'SAF','36','Total' UNION ALL SELECT 'SAF','37','Total' UNION ALL SELECT 'SAF','38','Total' UNION ALL SELECT 'SAF','39','Total' UNION ALL SELECT 'SAF','40','Total' UNION ALL SELECT 'SAF','41','Total' UNION ALL SELECT 'SAF','42','Total' UNION ALL SELECT 'SAF','43','Total' UNION ALL SELECT 'SAF','44','Total' UNION ALL SELECT 'SAF','45','Total' SELECT * FROM #TEMP

tsql experts, please help

SQLEnthus — Wed, 07 Mar 2012 17:34:05 GMT

--Need sourcetable converted to target table format--Also attached a spreadsheet with color coding, please helpDECLARE @SourceTable TABLE([order] INT, Student INT, Teacher INT, student_type VARCHAR(100),color VARCHAR(100),begin_date DATETIME, end_date DATETIME, bookvalue INT, accvalue INT, begin_place VARCHAR(100), end_place VARCHAR(100)) INSERT INTO @SourceTable select 1, 123, 1 ,'asian' ,'brown', '1/1/2001', '1/2/2001', 1, 1, 'chicago', 'newjersey' union allselect 2, 234, 1, 'african', 'black' ,'1/2/2001', '1/3/2001', 2, 2 ,'seattle', 'buffalo' union allselect 3, 356, 1, 'asian' ,'brown', '1/3/2001', '1/4/2001', 3, 3, 'newyork', 'maine' union allselect 4, 420, 1, 'asian', 'brown', '1/4/2001', '1/5/2001', 4, 4, 'newjersey', 'dells' union allselect 5, 521, 1, 'african', 'black', '1/5/2001', '1/6/2001', 5 ,5 ,'buffalo', 'seattle' union allselect 6, 678, 1, 'indian', 'brown', '1/6/2001', '1/7/2001', 6, 6, 'maine', 'newyork' union allselect 7, 740, 1, 'indian', 'brown', '1/7/2001', '1/8/2001', 7, 7, 'vegas', 'fremont' union allselect 8, 880, 1, 'mexican', 'black', '1/9/2001', '1/10/2001' ,8, 8, 'dells', 'chicago'select * from @SourceTableDECLARE @TargetTable TABLE([order] INT, Student INT, Teacher INT, student_type VARCHAR(100),color VARCHAR(100),begin_date DATETIME, end_date DATETIME, bookvalue INT, accvalue INT, begin_place VARCHAR(100), end_place VARCHAR(100)) INSERT INTO @TargetTable select 1, 123, 1, 'asian' ,'brown', '1/1/2001', '1/5/2001', 10, 10, 'chicago',' dells' union allselect 2, 420, 1, 'african', 'black', '1/5/2001', '1/6/2001', 5, 5, 'buffalo', 'seattle' union allselect 3, 678, 1, 'indian', 'brown', '1/6/2001', '1/8/2001', 13, 13, 'maine' , 'fremont' union allselect 4, 880, 1, 'mexican' ,'black', '1/9/2001', '1/10/2001', 8, 8 ,'dells', 'chicago' select * from @TargetTable

Virtual Cooperation (mughal888)

mughal872 — Mon, 05 Mar 2012 23:22:05 GMT

Virtual Instruments helps IT organizations maximize the performance, utilization, and availability of Fiber Channel Storage Area Networks (SANs) and other Online Services. [url=http://www.virtualonlinejobs.net]http://www.virtualonlinejobs.net [/url]

If ... select ...else then ??

sabercats — Wed, 29 Feb 2012 11:15:00 GMT

Hi all,I am a newbie in SQL, i stuck with my query, can you help me to solve it?Here is my query:[code="sql"]SELECT [System].[dbo].[Machine_Control_History].parent_id ,[System].[dbo].[Machine_Control_History].start_datetime ,[System].[dbo].[Machine_Control_History].end_datetime ,[System].[dbo].[Machine_Control_History].machine_status ,[System].[dbo].[Machine_Control_History].active_user ,[System].[dbo].[Machine_Control_History].notesFROM [System].[dbo].[Machine_Control_History][/code]How do you write WHERE ? to getI want a report from 2/19- 2/26 with all the system running from this time, not between this time, because it will not get start time before it and end in that week.1. If start_datetime is before 2/19 but end_datetime is between 2/19-2/26 then start_datetime is 2/19 00:00:00 0002. If start time is after 2/19 but endtime is between 2/19-2/26 then starttime is that start time.How you do that? this is auto generate at midnight around 00:30AM every Sunday to get this report for every week. and how we get every 2 weeks too? Thanks.

which Condition apply?

iamidress — Sat, 25 Feb 2012 00:22:13 GMT

I have the Employees Table which canting the following data [code="plain"]Empcode Basic Pay H.Rent65 800 800 89 800 80096 750 1000 [/code] I want if the empcode is differ and Basic Pay and House Rent Amount Same then “CHECK IT” Else “OK” somebody help me which condition is apply here Please

TSQL Help

SQLEnthus — Thu, 23 Feb 2012 14:24:01 GMT

I have table data like this (duration needs to be aggregated, and max id_date needs to be grabbed)Rule 1 : If checksum of first row is not equal to second row, keep that row, if second row is not equal to third, keep that row .ROW_NO id CheckSum duration id_date1 85036024 21504 10 1/1/20112 85036024 4337731 10 1/2/20113 85036024 269731 10 1/5/20114 85036024 269731 10 1/6/20115 85036024 269731 10 1/9/20116 85036024 21504 10 1/10/20117 85036024 21504 10 1/11/20118 85036024 21566 10 1/12/20119 85036024 21506 10 1/13/201110 85036024 21506 10 1/14/201111 97393064 21504 11 1/15/201112 97393064 21504 10 1/19/2011Result 1 85036024 21504 10 1/1/20112 85036024 4337731 10 1/2/20113 85036024 269731 30 1/9/20114 85036024 21504 20 1/11/20115 85036024 21566 10 1/12/20116 85036024 21506 20 1/14/20111 97393064 21504 21 1/19/2011Any ideas how to achieve this?

Linked server coding issue

Andeavour — Wed, 22 Feb 2012 05:40:59 GMT

I'm trying to create a stored procedure to rotate encryption keys on databases at a given time. One part of which checks (in the case of a mirrored database) that the appropriate certificate exists on the mirror server to prevent the mirrors failing:[code="sql"]-- Check that the chosen certificate exists on the Mirror. -- If not, then cycle through until we come to one that does and use thatif (@Mirrored = 1) begin while not exists (select 1 from MIRROR.master.sys.certificates where name = @NewCert) begin set @CurrentCert = @CurrentCert + 1 set @NewCert = (select CertName from CertList where CertID = case when @CurrentCert < @MaxCert then @CurrentCert + 1 else 1 end) end end[/code]The problem is that if I try to add this procedure to a server where the databases are not mirrored, then even though none of them will ever pass the (@Mirrored = 1) check, it won't even create the stored procedure because the MIRROR linked server doesn't exist (current company policy creates a linked server called MIRROR on the principal pointing to the mirror server).Unfortunately this stored proc would live in a generic Admin database that is repeated acros a number of servers, so having different versions for mirrored and non-mirrored servers is not really an option (except of last resort). Does anyone know a way I can get the proc to create and run, since on a non-mirrored server it will never reach this code anyway.

IF in Select

iamidress — Tue, 21 Feb 2012 23:20:48 GMT

Hi allfollowing Query SELECT EmpName As EName IF (BasicPay>5000, "OVER","lOW"), AS Result FROM EmpSalaryThe Result is following in Error[b]Line 2: Incorrect syntax near ','.[/b]hope somebody help meThanks

Suspending task

tshad — Tue, 21 Feb 2012 14:38:21 GMT

I have a piece of code that is getting suspended and I don't know why.This Insert is inside of a Transaction but when when it gets to this insert it freezes. It is only using one table.All I am doing is inserting one row into the BatchRevision table but I need to set the BatchRevisionNumber to either 0 or one more then the largest BatchRevisionNumber.Shouldn't be a problem but it is.It seems to have SPIDs running with one suspended. The other one seems to be increasing cpuTime.I have to kill it to stop it. [code] INSERT INTO [Requirement].[BatchRevision] ( BatchID , BatchName , BatchRevisionNumber , [FileName] ) SELECT @BatchID , @BatchName , BatchRevisionNumber = ( SELECT CASE WHEN MAX(BatchRevisionNumber) IS NULL THEN 0 ELSE MAX(BatchRevisionNumber) + 1 END FROM [Requirement].[BatchRevision] WHERE BatchID = @BatchID ) , @FileName[/code]Why is there 2 SPIDs? I assume 1 for the insert and one for the SELECT and the insert is waiting for the SELECT. There aren't that many rows in the table.Thanks,Tom

Create identity column using dynamic seed value

M Chabot — Thu, 28 Jul 2005 13:06:00 GMT

I am trying to create a temporary table with a seed value pulled from a stored procedure and I can't get it to work. I cannot seem to use a variable for the seed value in the create table statement. The alternative using dynamic SQL does not work either, I believe because the temporary table disappears instead of being passed back to the code that called the sp_ExecuteSQL statement. The same restrictions seem to be in place for table variables. What can I do? Thank you.

Sample code that does not actually end up creating a table:

DECLARE @SQLStr nvarchar(4000)DECLARE @RecNum intSET @RecNum = 1

SET @SQLStr = N'DECLARE @tblTemp TABLE (fldRecNum INT IDENTITY('+ CAST(@RecNum AS varchar(10))+',1) PRIMARY KEY'

EXECUTE sp_ExecuteSQL @SQLStr

There must be an easier way of doing this...

jarid.lawson — Wed, 15 Feb 2012 14:29:20 GMT

...I just don't see it yet.For the last 3 years I've been focused exclusively on SQL. I spent most of this time working in T-SQL, and some in P-SQL, but I did not jump into SSIS or SSRS during that time. I was primarily an analyst, and this numbed my memory to how much I relied on VB6 when I used to do full development in the past.My new position has shown that I will need to get back into a more complete development mind set, and I was caught off guard by this realization. I dusted off my old VB skills with some work, and came up with the attached code (modified only to remove client specific data). This was completed under the gun, and I will be the first to say it is far from the best I could do. In the end it works, but there must be a better way to do it.The project sounded relatively simple. The client would load 3 to 4 files every 1.5 hours to our SFTP server. These data files would show authorized transactions (AD), checks actually printed (CI), payments received (PM), and a list of voided checks (CV). Since each of these files have a unique name (including both date and time sections) I cannot just kick off a saved Import Tool SSIS package. These files need to be up and ready to go by 8:00 AM every day, and even loading them while I'm at the house before I come in they would not be ready until around 9:00 AM.The solution was to create a command line program which can be launched from a scheduler program. The program copies everything from the FTP folder that is not in the Archive folder into a Staging folder. Once in the staging folder the program edits each file (one by one) to remove the final line (a check sum row), and adds the SourceFileName and BatchDate fields (values found in data file's name). It then looks at the last 2 characters of the file name before the .csv to determine what kind of file this is (AD, CI, CV, or PM), and directs the flow control to the correct sub function. In the sub functions there are a couple of the file types that must account for multiple format possibilities (the client changed it on us a couple of times).I ran into an issue that took me some time to identify and find a solution for. Originally I did not have a 5 second pause after after calling the DTSX package. Without the pause the program moves on almost immediately, and tries to delete the file in the staging folder. If the DTSX package is still loading the file then errors begin to occur.I also ran into an issue while looking up how to add the timer (I couldn't use the standard Visual Studio timer object since this is a command line program...I had nothing to attach the object to - at least that I knew of). There is a VB tool called Sleep() which is found in a kernel32 library. The problem is that I am on a 64-bit system, and it seems that the Sleep() function is based out of an old x86 32-bit architecture.Now that all of the back story is out, here are my questions. Does SSIS have a way of accounting for multiple file names? Does SSIS allow for a file rename step like what I've done here? Is there a better way of calling the DTSX package then using the Shell(Call string) option? Is there a way to avoid the timer option, and just detect a return value from the DTSX package (with this return value I should also be able to kick off an error control step if the package failed)? Finally, was there a simpler way of doing this? If yes, then what is it?Visual Studio 2010 ProfessionalSQL Server 2008 R2

SQL Server 2000 check for duplicate rows

rheinhardtp — Wed, 15 Feb 2012 01:38:00 GMT

Hi All, I am very new to databases so please bare with me. I have the following Stored Procedure.[code="sql"]CREATE PROCEDURE VRWatsysProcASINSERT INTO dbo.VRWATSYSTABLESELECT *FROM [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable]ORDER BY DateTimeGO[/code]MY problem is there are records in my source table [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable] that do not change, in this case i want to ignore these rows and not transfer them into my destination table dbo.VRWATSYSTABLE. What is the best method to do this??DO i first transfer the complete table and then check for duplicates and delete them or can i prevent the duplicates in my select query from being inserted into the table.Rheinhardt

Help Reqarding Query

iamidress — Tue, 14 Feb 2012 23:59:49 GMT

I wrote the following Query for Controlling of Purchase Order Qty. through Goods Receipt Notes, I have used four Table for it which are 1-dcpurcordno(Purchase Order # ) 2- dcgrnno(GRNNO) 3- dcgrndt (GRNDATE) 4- patadvno (PAYMENT ADVICE #) Problem is when last Qty of Purchase order pass via GRNNO Balance showoing minus of last Qty Instead of Zero. Some body help me where I am doing the mistake Pls.[b]Following is the Query.[/b]select b.dcpurcordno, b.dcgrnno,b.dcgrndt ,h.patadvno , sum(TPOQtyOrd) POQty, sum((c.DCTRecqty-c.DCTRejqty-c.DCTShortqty+c.dctaccqty)) Recqty , sum(TPOQtyOrd)- (select sum(g.DCTRecqty-g.DCTRejqty-g.DCTShortqty+g.dctaccqty) from dctrans g ,dcmast f where f.DCDelvchdt = g.DCTDelvchdt AND f.dcpart = g.dctpart AND f.DCDummygrnno = g.DCtDummygrnno AND f.DCDelvchno = g.DCTDelvchno and f.DCPurcorddt = b.dcpurcorddt AND f.DCPurcordno = b.dcpurcordno and g.dctitemcode =c.dctitemcode and g.dctdummygrnno<= c.dctdummygrnno ) BalQty from dcmast b ,dctrans c ,PurOrdMast d, PurOrdTrans e ,purpadtrans h Where b.DCDelvchdt = c.DCTDelvchdt AND b.dcpart = c.dctpart AND b.DCDummygrnno = c.DCtDummygrnno AND b.DCDelvchno = c.DCTDelvchno and b.DCPurcorddt = d.MPoPoDate AND b.DCPurcordno = d.MPoPoNoAct and d.MPoPoDate = e.TPoPoDt and d.MPoPoNoAct = e.TPoPoNoAct and b.dcgrnno*=h.patgrnno and b.dcadviceno*= h. patadvno and b.dcgrndt*= h.patgrndt and c.dctitemcode*= h.patitmcode and h.patstaxrate=0 and b.DCgrnno is not null and b.DCDchcancel <>'Y' and b.DCcmpcode =1 --- and TPoPoNoAct= 'KSD/H/C /001-A/01' and tpopodt ='Jan 02 2012' and c.dctitemcode=e.tpoitemcode and c.dctitemcode*= h.patitmcode and TPoPoNoAct= 'KSD/H/PI /011-A/01' and tpopodt ='Jan 02 2012' and c.dctitemcode=e.tpoitemcode and c.dctitemcode*= h.patitmcode and dctitemcode=13782 --- and TPoPoNoAct= 'KSD/H/PI /010-A/12' and tpopodt ='dEC 01 2011' and c.dctitemcode=e.tpoitemcode and c.dctitemcode= h.patitmcode group by c.dctitemcode,b.dcpurcordno,b.dcpurcorddt,b.dcdelvchdt,b.DCDelvchno,b.DCDummygrnno,b.dcgrnno,b.dcgrndt ,c.dctdummygrnno,h.patadvno,PATADVDT