Solved

RE : SQL Server query 2008

Posted on 2013-07-01
6
283 Views
Last Modified: 2013-07-09
Hi,

I am facing challenge in creating SQL query in optimized way.

Find the attached sample data in excel sheet. I would like the date as per Output 1 and Output 2 sheet.

Do let me know if any question regarding the same.

Data requirement is that i have the consumption data now i want to place the cancellation data against each consumption. Attached the transactional data in excel.

Facing to
0
Comment
Question by:vikalgupta
  • 2
  • 2
  • 2
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39289746
Hi,

I think you have forgotten to attach the example data.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39289804
and please post your existing SQL query (or include it in the spreadsheet)
0
 

Author Comment

by:vikalgupta
ID: 39290008
find the attached sheet.
rpt.xlsx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39290128
Your not providing all the test data. You have not populated the shop master table. Although this does look like it will be a simple join.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39292018
The following result is produced by either of the SQL options below. Please note the date format used is an arbitrary choice which you can amend.
MERCHANTID	VOUCHERNUMBER	CONSUMEDSHOPNAME	CONSUMEDSHOPCODE	CONSUMEDDATE	REQUESTJOBNO	CANCELLEDSHOPNAME	CANCELLEDSHOPCODE	CANCELLEDDATE
3		BV34145412	Karol Bagh		BR-GP001	2013-07-01 14:48:54	23		Green Park		BR-GP000	2013-07-01 14:49:48
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:49:24	78		Rohini			BR-GP002	2013-07-01 14:49:33
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:48:32	12		Karol Bagh		BR-GP001	2013-07-01 14:49:16
3		BV61731976	Rohini			BR-GP002	2013-07-01 14:49:03	34		(null)			(null)		(null)
3		BV74939357	Green Park		BR-GP000	2013-07-01 12:57:33	ADMIN_EVOUCHER	Green Park		BR-GP000	2013-07-01 12:58:15

Open in new window

There does not appear to be a tidy method within the source transactions to relate a cancellation to a consumption and to overcome this I have used the row_number() function to produce sequence numbers to tie cancellation to consumption. This works in the sample provided but suggest you test this on a broader set of data.

These 2 SQL options are functionally the same, they just differ in technique (using CTEs or using nested subquery)
-- using CTEs
;WITH
consume AS (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
            ),
cancel  AS (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
            )
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM consume
LEFT JOIN cancel ON consume.VoucherNumber = cancel.VoucherNumber
                AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

-- using nested subqueries
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
     ) AS consume
LEFT JOIN (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
          ) AS cancel ON consume.VoucherNumber = cancel.VoucherNumber
                     AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

Open in new window

also note you didn't specify the actual table names, and I removed spaces from any column labels in the spreadsheet.

these queries and the assumed shopmaster table may be seen in operation at:
http://sqlfiddle.com/#!3/84505/3
0
 

Author Closing Comment

by:vikalgupta
ID: 39313199
Thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question